<jupyter_start><jupyter_text>Data Cleaning with Pandas======================   Overview    Questions                    What does 'clean data' mean?            How can I drop unnecessary data from my dataframe?            How can I change column or row names in a dataframe?            How can I cast columns to the correct data type?            Objectives:                    Use pandas to drop unnecessary data from our dataframe.            Learn how to rename pandas columns.            Use pandas string methods to correct characters.            Learn how to cast columns to the correct data type.            Keypoints:                    Data cleaning prepares data for analysis.            Pandas has built-in methods for handling data cleaning, particular missing data.                In this section, we'll read in the data we extracted in the last lesson. You may have noticed in the last session that the data in these dataframes didn't look great. There were columns that appeared to have no values. Once we start working with the data, we are going to see some additional problems.<jupyter_code>import os
import pandas as pd
fpath = os.path.join("data", "potts_table1.csv")
fpath2 = os.path.join("data", "potts_table2.csv")
table1 = pd.read_csv(fpath)
table2 = pd.read_csv(fpath2)
table1.head()<jupyter_output><empty_output><jupyter_text>Dropping unneccessary dataIn some cases, we might have data in our dataframe that we don't need. We will want to discard or "drop" this data from the dataframe. For the dataframe we just loaded, for example, we can see that the data in columns 0, 1, 4, 12 appear to not have any values.Check your understanding    What pandas method can you use to see how many non-null values you have in each column?    ```{admonition} Solution:class: dropdown```pythontable1.info()```  There are two methods you might use to drop data from a dataframe. These are `drop`, and `dropna`. Drop is used when you have specific rows or columns you want to remove from the dataframe, while `dropna` is used when you want to drop columns or rows which contain `NaN` or "not a number" values. This occurs when there are no values in a data cell.In the output of `info` above, we can see that there are two columns which contain 0 non-null values. This means that all of the values in these columns are `NaN`. We can safely discard these columns. We'll use the `dropna` function to get rid of them.<jupyter_code>help(table1.dropna)<jupyter_output>Help on method dropna in module pandas.core.frame:

dropna(axis=0, how='any', thresh=None, subset=None, inplace=False) method of pandas.core.frame.DataFrame instance
    Remove missing values.
    
    See the :ref:`User Guide <missing_data>` for more on which values are
    considered missing, and how to work with missing data.
    
    Parameters
    ----------
    axis : {0 or 'index', 1 or 'columns'}, default 0
        Determine if rows or columns which contain missing values are
        removed.
    
        * 0, or 'index' : Drop rows which contain missing values.
        * 1, or 'columns' : Drop columns which contain missing value.
    
        .. versionchanged:: 1.0.0
    
           Pass tuple or list to drop on multiple axes.
           Only a single axis is allowed.
    
    how : {'any', 'all'}, default 'any'
        Determine if row or column is removed from DataFrame, when we have
        at least one NA or all NA.
    
        * 'any' : If any NA values are present, dro[...]<jupyter_text>Before saving the dataframe, we'll look at and and discuss output from this function. By default, the function `dropna` will work on `axis 0` or the rows of the dataframe, and will drop any row which contains a `NaN`. You will see this results in a dataframe with no data.<jupyter_code>table1.dropna()<jupyter_output><empty_output><jupyter_text>Notice that `dropna` returns a dataframe and does not overwrite the original.<jupyter_code>table1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 14 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   Unnamed: 0    37 non-null     int64  
 1   Unnamed: 0.1  1 non-null      object 
 2   Compound      37 non-null     object 
 3   log P         37 non-null     object 
 4   Unnamed: 1    0 non-null      float64
 5   II            37 non-null     float64
 6   Hy            37 non-null     float64
 7   H,            37 non-null     float64
 8   MV            37 non-null     object 
 9   R,            37 non-null     float64
 10  log Kou       37 non-null     object 
 11  log Kyex      31 non-null     object 
 12  Unnamed: 2    0 non-null      float64
 13  log Kpep      25 non-null     object 
dtypes: float64(6), int64(1), object(7)
memory usage: 4.2+ KB<jupyter_text>We can switch to dropping columns which have `NaN` values by adding the argument `axis=1`.<jupyter_code>table1.dropna(axis=1).info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 9 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   Unnamed: 0  37 non-null     int64  
 1   Compound    37 non-null     object 
 2   log P       37 non-null     object 
 3   II          37 non-null     float64
 4   Hy          37 non-null     float64
 5   H,          37 non-null     float64
 6   MV          37 non-null     object 
 7   R,          37 non-null     float64
 8   log Kou     37 non-null     object 
dtypes: float64(4), int64(1), object(4)
memory usage: 2.7+ KB<jupyter_text>This is closer to what we want. However, you'll notice that this has dropped some columns which have data. By default, pandas will drop a column which contains **any** `NaN` values. This may not be what we want in many cases because some values may simply be missing rather than incorrect.We can add an additional argument, `how=all`, to drop only columns whose values are **all** `NaN`. By default, this function argument is `how=any`. Once we are sure we would like to keep this as our dataframe, we can add `inplace=True` to the function call to overwrite the dataframe.<jupyter_code>table1.dropna(axis=1, how="all")<jupyter_output><empty_output><jupyter_text>The output above looks like something to keep, so we will add `inplace=True` to overwrite the original dataframe.<jupyter_code>table1.dropna(axis=1, how="all", inplace=True)
table1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 12 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   Unnamed: 0    37 non-null     int64  
 1   Unnamed: 0.1  1 non-null      object 
 2   Compound      37 non-null     object 
 3   log P         37 non-null     object 
 4   II            37 non-null     float64
 5   Hy            37 non-null     float64
 6   H,            37 non-null     float64
 7   MV            37 non-null     object 
 8   R,            37 non-null     float64
 9   log Kou       37 non-null     object 
 10  log Kyex      31 non-null     object 
 11  log Kpep      25 non-null     object 
dtypes: float64(4), int64(1), object(7)
memory usage: 3.6+ KB<jupyter_text>We can drop the final two columns using the `drop` function. You can use this when you have specific rows or columns you would like to discard. Again, we use `axis=1` to drop columns, then we pass the column name.<jupyter_code>table1.drop(axis=1, columns=["Unnamed: 0.1", "Unnamed: 0"], inplace=True)<jupyter_output><empty_output><jupyter_text>Changing column namesOur column names are still incorrect. You will likely want to change them to make the table more legible.<jupyter_code>table1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 10 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   Compound  37 non-null     object 
 1   log P     37 non-null     object 
 2   II        37 non-null     float64
 3   Hy        37 non-null     float64
 4   H,        37 non-null     float64
 5   MV        37 non-null     object 
 6   R,        37 non-null     float64
 7   log Kou   37 non-null     object 
 8   log Kyex  31 non-null     object 
 9   log Kpep  25 non-null     object 
dtypes: float64(4), object(6)
memory usage: 3.0+ KB<jupyter_text>We might now want to clean up the column names and make sure they are descriptive. You can see the column names using `table1.columns`. You can either rename the columns by setting `table1.columns` to a list of the appropriate length, or you can use `table1.rename`. In the `.rename` method, you put the argument `columns` and set it equal to a dictionary (curly brackets) where you use the syntax```python"current_column_name": "new_column_name"```<jupyter_code>table1.columns
table1.rename(inplace=True, columns={
    "II": "pi", 
    "Hy": "Hd", 
    "H,": "Ha", 
    "R,": "R_2",
    "log Kou": "log K_oct",
    "log Kyex": "log K_hex",
    "log Kpep": "log K_hep"
                      
                      })
table1.head()<jupyter_output><empty_output><jupyter_text>Fixing Data Types When examining `.info` , you'll notice that a lot of our columns which should be numbers are still 'objects' or strings. We would like `log P`, for example to be numeric. Typically if a column appears that it should be numeric, but pandas does not automatically cast it as such, it is because there are some non-numeric characters in the column which pandas could not decide what to do with. We will need to examine these, decide what to do with them, then cast the column as numeric.There are a few ways to do this, but we'll use the pandas function `to_numeric`.<jupyter_code>table1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 10 columns):
 #   Column     Non-Null Count  Dtype  
---  ------     --------------  -----  
 0   Compound   37 non-null     object 
 1   log P      37 non-null     object 
 2   pi         37 non-null     float64
 3   Hd         37 non-null     float64
 4   Ha         37 non-null     float64
 5   MV         37 non-null     object 
 6   R_2        37 non-null     float64
 7   log K_oct  37 non-null     object 
 8   log K_hex  31 non-null     object 
 9   log K_hep  25 non-null     object 
dtypes: float64(4), object(6)
memory usage: 3.0+ KB<jupyter_text>Using the `to_numeric` function without any additional inputs will fail on this data set.<jupyter_code>pd.to_numeric(table1["log P"])<jupyter_output><empty_output><jupyter_text>Scrolling to the bottom of this message and reading the error, you will see it is having a problem reading the value `"— 6.85"`. It may not seem obvious what this problem is at first. When we run into a problem like this we have a few options. You could choose to handle the errors differently. Pandas will let you set what you would like for it to do when it is unable to cast a value. By default, it will fail (which is what se wee above). For example, you could also set errors to be ignored (which would result in the column being unchanged, there would just be no error raised) or to "coerce" the values. Choosing "coerce" means that anything that can't be cast as numeric will be put as `NaN`.Let's see what happens when we set errors to coerce.<jupyter_code>pd.to_numeric(table1["log P"], errors="coerce")<jupyter_output><empty_output><jupyter_text>This unfortunately results in no numeric characters being recognized.We have to do a little bit more processing to the values for this to work. If you examine the columns, you may notice that the negative sign is a little off. It is `—` when it should be `-`. This is very slight, and might be hard to see, but it is important to change for this data set.We will want to replace all `—` with `-`. We could accomplish this using the string method `replace`. Strings in Python have a number of methods. The `replace` method allows us to replace a substring within a string.<jupyter_code>test_string = "Hello world."
test_string.replace(".", "!")<jupyter_output><empty_output><jupyter_text>The split command is another string method you are probably familiar with:<jupyter_code>test_string.split()<jupyter_output><empty_output><jupyter_text>Pandas string methods If we want to use these on a column in a pandas dataframe, you might think to use `apply`, which we learned about in the last session. However, you will notice that the `replace` method acts on a the string and doesn't fit into `apply`.Luckily, when pandas columns are strings, we can use string methods on the whole column by adding `.str.function`. For example, to replace the minus signs<jupyter_code>table1["log P"].str.replace("—", "-")
table1["log P"] = table1["log P"].str.replace("—", "-")

# We still need to get rid of spaces
table1["log P"] = table1["log P"].str.replace(" ", "")
table1["log P"] =  pd.to_numeric(table1["log P"], errors="coerce")
table1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 37 entries, 0 to 36
Data columns (total 10 columns):
 #   Column     Non-Null Count  Dtype  
---  ------     --------------  -----  
 0   Compound   37 non-null     object 
 1   log P      34 non-null     float64
 2   pi         37 non-null     float64
 3   Hd         37 non-null     float64
 4   Ha         37 non-null     float64
 5   MV         37 non-null     object 
 6   R_2        37 non-null     float64
 7   log K_oct  37 non-null     object 
 8   log K_hex  31 non-null     object 
 9   log K_hep  25 non-null     object 
dtypes: float64(5), object(5)
memory usage: 3.0+ KB<jupyter_text>We actually need to change this character on all of our columns. However `str` methods only work on pandas series. If we want to replace a string across all of our DataFrame, we will use the `.replace` method. In order for it to recognize substrings, set the option `regex=True`. We will discuss `regex` more in the next session, but this is all you need to know about regex for the moment.<jupyter_code>table1.replace("—", "-", regex=True, inplace=True)
table1.replace(" ", "", regex=True, inplace=True)<jupyter_output><empty_output><jupyter_text>Changing the data type of multiple columns To change the data type of multiple columns, we will want to use the `pd.to_numeric` function on all of those columns. There are several ways you might choose to do this. For example, you might just choose to call the function for each column.We can also accomplish this by using the `apply` operator which we learned about in the last session. The `apply` operator should be used whenever you want to apply a function to a row or column. In this case, we want to apply the `pd.to_numeric` function to each column.Because we want to apply to the columns, we add the argument `axis=1`.<jupyter_code>table1.apply(pd.to_numeric, axis=1)<jupyter_output><empty_output><jupyter_text>When we try this code, we immediately see an error. We do not want to try to convert the first column to a number. We can use the `iloc` function to exclude the first column:<jupyter_code>table1.iloc[:, 1:].apply(pd.to_numeric, axis=1)<jupyter_output><empty_output><jupyter_text>An error again! This time, we see failure because a string was incorrectly read from the pdf and could not be converted to a number. You could choose to handle this differently, but for this workshop we are just going to discard values like these. If we were using `to_numeric` on a pandas series, we would use the option `errors="coerce"`. You may not see immediately how to use this with the `apply` function, but fortunately, pandas allows us to pass additional arguments with `apply`:<jupyter_code>table1.iloc[:, 1:] = table1.iloc[:, 1:].apply(pd.to_numeric, axis=1, errors="coerce")
table1.info()
table1.to_csv("data/potts_table1_clean.csv", index=False)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup