<jupyter_start><jupyter_text>Cleaning data with Pandas- toc:false- branch: master- badges: true- comments: false- categories: [data, python]- hide: true Questions:- How can I import and clean tabular data in Python?Objectives:- Read tabular data from a file into a program using `pandas`.- Remove unwanted data columns- Swap row and column data (transpose)Keypoints:- Use the `pandas` library to work with tabular data in Python- The `read_csv` function is used to read in .csv data- The `read_csv` function has several keywords which can simplify data parsing- The `dataframe` object has a `transpose` method which will transpose rows and columns- The `dataframe` object has a `to_csv` method which will write the dataframe to a file  Use the pandas library to work with tabular data in Python<jupyter_code>import pandas<jupyter_output><empty_output><jupyter_text>Importing a library is like getting a piece of lab equipment out of a storage locker and setting itup on the bench. Libraries provide additional functionality to the basic Python package, much likea new piece of equipment adds functionality to a lab space. Just like in the lab, importing toomany libraries can sometimes complicate and slow down your programs - so we only import what weneed for each program. Once we've imported the library, we can ask the library to read our datafile for us:  The `read_csv` function is used to read in .csv data<jupyter_code>pandas.read_csv("../data/UVVis-01.csv")<jupyter_output><empty_output><jupyter_text>`pandas.read_csv` has one argument: the pathname of the file we want to read.Since we haven't told it to do anything else with the function's output,the notebook displays it.In this case,that output is the data we just loaded.By default,only a few rows and columns are shown(with `...` to omit elements when displaying big arrays).To save space,Python displays numbers as `1.` instead of `1.0`when there's nothing interesting after the decimal point.> Note: the pandas `read_csv` function can also read `.txt` files  The `read_csv` function has several keywords which can simplify data parsingThis data we have read in is from a UV-Vis experiment. The rows are the data for each wavelength,and the columns are the individual samples.We will analyse the data using the `numpy` Python library, but before we do this we must clean up the dataframe. There are three things we would like to do:* Delete the repeated wavelength columns* Set the correct column headings (numpy has automatically set the first row of data as the column headings)* Transpose the data (swap rows and columns; so each row corresponds to a different sample)We are able to do all the first two things on this list with the following command:<jupyter_code>pandas.read_csv("../data/UVVis-01.csv",usecols=[1,3,5,7,9,11,13,15,17,19],header=None)<jupyter_output><empty_output><jupyter_text>`pandas.read_csv` now has two parameters: the `usecols` keyword which specifies the columns to read in and the `header` keyword which, when we set to `False`, tells the `read_csv` function that there is no heading data in the file and that the headers should be set to an integer range. Our call to `pandas.read_csv` read our filebut didn't save the data in memory.To do that,we need to assign the array to a variable. Just as we can assign a single value to a variable, wecan also assign an array of values to a variable using the same syntax.  Let's re-run`pandas.read_csv` and save the returned data:<jupyter_code>data = pandas.read_csv("../data/UVVis-01.csv",usecols=[1,3,5,7,9,11,13,15,17,19],header=None)<jupyter_output><empty_output><jupyter_text>This statement doesn't produce any output because we've assigned the output to the variable `data`.If we want to check that the data have been loaded,we can print the variable's value:<jupyter_code>print(data)<jupyter_output>1         3         5         7         9         11        13  \
0      0.000447 -0.003662  0.002233  0.006085  0.006157  0.012101  0.012068   
1      0.000656 -0.003497  0.002297  0.006312  0.006317  0.012318  0.012270   
2      0.000864 -0.003343  0.002475  0.006419  0.006479  0.012420  0.012395   
3      0.001073 -0.003682  0.002223  0.006161  0.006196  0.012130  0.012126   
4      0.001281 -0.004053  0.002251  0.005537  0.006273  0.012027  0.012201   
...         ...       ...       ...       ...       ...       ...       ...   
1296  10.000000 -0.182566  0.183470 -0.398002  0.366597 -0.039593  0.138509   
1297   0.614484 -0.770955  0.306131  0.651660  0.294760  0.120459  0.110172   
1298  10.000000 -0.122420  0.331976  0.048607 -0.006551  0.223929  0.245652   
1299   1.296677 -0.007074  0.377199  0.894186  0.422728  0.123843  0.134249   
1300   1.666697 -0.182474  0.035342  0.568853  0.383505  0.240436  0.016508   

            15        17        19  
0     0.012077[...]<jupyter_text>Now that the data are in memory,we can manipulate them.First,let's ask what type of thing `data` refers to:<jupyter_code>print(type(data))<jupyter_output><class 'pandas.core.frame.DataFrame'><jupyter_text>The output tells us that `data` currently refers toan pandas DataFrame, the functionality for which is provided by the `pandas` library. > Note: A Pandas DataFrame is a two-dimensional labeled data structure with columns of potentially different types. In this case, all the columns are floats, but one column could, for example, contain strings.  The `dataframe` object has a `transpose` method which will transpose rows and columnsWe can now use the `transpose` method to swap the rows and columns of `data`, and assign this to the variable `data`.<jupyter_code>data = data.transpose()
print(data)<jupyter_output>0         1         2         3         4         5         6     \
1   0.000447  0.000656  0.000864  0.001073  0.001281  0.001489  0.001698   
3  -0.003662 -0.003497 -0.003343 -0.003682 -0.004053 -0.003248 -0.003364   
5   0.002233  0.002297  0.002475  0.002223  0.002251  0.002604  0.002554   
7   0.006085  0.006312  0.006419  0.006161  0.005537  0.006554  0.006481   
9   0.006157  0.006317  0.006479  0.006196  0.006273  0.006091  0.006526   
11  0.012101  0.012318  0.012420  0.012130  0.012027  0.012618  0.012511   
13  0.012068  0.012270  0.012395  0.012126  0.012201  0.012195  0.012277   
15  0.012077  0.012277  0.012400  0.012140  0.012225  0.012387  0.012464   
17  0.003982  0.004222  0.004328  0.004078  0.004151  0.004266  0.004383   
19  0.004210  0.004369  0.004388  0.004146  0.004150  0.004467  0.004354   

        7         8         9     ...       1291      1292      1293  \
1   0.001906  0.002115  0.002323  ...  10.000000  1.599422  0.799607   
3  -0.003756 -0.003[...]<jupyter_text>The `dataframe` object has a `to_csv` method which will write the dataframe to a fileThe final thing left to do is print our cleaned dataset to a file for analysing later. To do this we can use the `to_csv` DataFrame method. We set the `header` and `index` parameters to `False` as we do not want to print these to the file.<jupyter_code>data.to_csv('../data/UVVis-01-cleaned.csv', header=False, index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08091024020227561, 'fraction_numerical': 0.16350611040876528, 'mean_word_length': 3.2634730538922154, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '287664', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 2987, 'n_tokens_neox': 2348, 'n_words': 902, 'path': '_notebooks/2021-08-02-Cleaning-Data.ipynb', 'repo_name': 'charlo66609/CompPhys', 'size': '7119'}