mlessentials/lab_guides/Lab_11.md

Lab 11. Data Preparation

Overview

By the end of this lab you will be able to filter DataFrames with specific conditions; remove duplicate or irrelevant records or columns; convert variables into different data types; replace values in a column and handle missing values and outlier observations.

This lab will introduce you to the main techniques you can use to handle data issues in order to achieve high quality for your dataset prior to modeling it.

Handling Row Duplication

Let's use the example that we saw in Lab 10, Analyzing a Dataset.

Start by importing the dataset into a DataFrame:

import pandas as pd
file_url = 'https://github.com/fenago/'\
           'data-science/blob/'\
           'master/Lab10/dataset/'\
           'Online%20Retail.xlsx?raw=true'
df = pd.read_excel(file_url)

The duplicated() method from pandas checks whether any of the rows are duplicates and returns a boolean value for each row, True if the row is a duplicate and False if not:

df.duplicated()

You should get the following output:

Caption: Output of the duplicated() method

In Python, the True and False binary values correspond to the numerical values 1 and 0, respectively. To find out how many rows have been identified as duplicates, you can use the sum() method on the output of duplicated(). This will add all the 1s (that is, True values) and gives us the count of duplicates:

df.duplicated().sum()

You should get the following output:

5268

The first API subsets the DataFrame by row or column. To filter specific columns, you can provide a list that contains their names. For instance, if you want to keep only the variables, that is, InvoiceNo, StockCode, InvoiceDate, and CustomerID, you need to use the following code:

df[['InvoiceNo', 'StockCode', 'InvoiceDate', 'CustomerID']]

You should get the following output:

Caption: Subsetting columns

If you only want to filter the rows that are considered duplicates, you can use the same API call with the output of the duplicated() method. It will only keep the rows with True as a value:

df[df.duplicated()]

You should get the following output:

Caption: Subsetting the duplicated rows

If you want to subset the rows and columns at the same time, you must use one of the other two available APIs: .loc or .iloc. These APIs do the exact same thing but .loc uses labels or names while .iloc only takes indices as input. You will use the .loc API to subset the duplicated rows and keep only the selected four columns, as shown in the previous example:

df.loc[df.duplicated(), ['InvoiceNo', 'StockCode', \
                         'InvoiceDate', 'CustomerID']]

You should get the following output:

Caption: Subsetting the duplicated rows and selected columns using .loc

This preceding output shows that the first few duplicates are row numbers 517, 527, 537, and so on. By default, pandas doesn't mark the first occurrence of duplicates as duplicates: all the same, duplicates will have a value of True except for the first occurrence. You can change this behavior by specifying the keep parameter. If you want to keep the last duplicate, you need to specify keep='last':

df.loc[df.duplicated(keep='last'), ['InvoiceNo', 'StockCode', \
                                    'InvoiceDate', 'CustomerID']]

You should get the following output:

Caption: Subsetting the last duplicated rows

As you can see from the previous outputs, row 485 has the same value as row 539. As expected, row 539 is not marked as a duplicate anymore. If you want to mark all the duplicate records as duplicates, you will have to use keep=False:

df.loc[df.duplicated(keep=False), ['InvoiceNo', 'StockCode',\
                                   'InvoiceDate', 'CustomerID']]

You should get the following output:

Caption: Subsetting all the duplicated rows

You can use the drop_duplicates() method from pandas. It has the same keep parameter as duplicated(), which specifies which duplicated record you want to keep or if you want to remove all of them. In this case, we want to keep at least one duplicate row. Here, we want to keep the first occurrence:

df.drop_duplicates(keep='first')

You should get the following output:

Caption: Dropping duplicate rows with keep='first'

The drop_duplicates() and duplicated() methods also have another very useful parameter: subset. This parameter allows you to specify the list of columns to consider while looking for duplicates. By default, all the columns of a DataFrame are used to find duplicate rows. Let's see how many duplicate rows there are while only looking at the InvoiceNo, StockCode, invoiceDate, and CustomerID columns:

df.duplicated(subset=['InvoiceNo', 'StockCode', 'InvoiceDate',\
                      'CustomerID'], keep='first').sum()

You should get the following output:

10677

Exercise 11.01: Handling Duplicates in a Breast Cancer Dataset

In this exercise, you will learn how to identify duplicate records and how to handle such issues so that the dataset only contains unique records. Let's get started:

1. Open a new Jupyter notebook.

2. Import the pandas package:

   import pandas as pd
   

3. Assign the link to the Breast Cancer dataset to a variable called file_url:

   file_url = 'https://raw.githubusercontent.com/'\
              'fenago/data-science/'\
              'master/Lab11/dataset/'\
              'breast-cancer-wisconsin.data'
   

4. Using the read_csv() method from the pandas package, load the dataset into a new variable called df with the header=None parameter. We're doing this because this file doesn't contain column names:

   df = pd.read_csv(file_url, header=None)
   

5. Create a variable called col_names that contains the names of the columns: Sample code number, Clump Thickness, Uniformity of Cell Size, Uniformity of Cell Shape, Marginal Adhesion, Single Epithelial Cell Size, Bare Nuclei, Bland Chromatin, Normal Nucleoli, Mitoses, and Class:

   col_names = ['Sample code number','Clump Thickness',\
                'Uniformity of Cell Size',\
                'Uniformity of Cell Shape',\
                'Marginal Adhesion','Single Epithelial Cell Size',\
                'Bare Nuclei','Bland Chromatin',\
                'Normal Nucleoli','Mitoses','Class'] 
   

6. Assign the column names of the DataFrame using the columns attribute:

   df.columns = col_names
   

7. Display the shape of the DataFrame using the .shape attribute:

   df.shape
   

   You should get the following output:

   (699, 11)
   

   This DataFrame contains 699 rows and 11 columns.

8. Display the first five rows of the DataFrame using the head() method:

   df.head()
   

   You should get the following output:

Caption: The first five rows of the Breast Cancer dataset

All the variables are numerical. The Sample code number column is an
identifier for the measurement samples.

9. Find the number of duplicate rows using the duplicated() and sum() methods:

   df.duplicated().sum()
   

   You should get the following output:

   8
   

   Looking at the 11 columns in this dataset, we can see that there are 8 duplicate rows.

10. Display the duplicate rows using the loc() and duplicated() methods:

    df.loc[df.duplicated()]
    

    You should get the following output:

Caption: Duplicate records

The following rows are duplicates: `208`, `253`,
`254`, `258`, `272`, `338`,
`561`, and `684`.

11. Display the duplicate rows just like we did in Step 9, but with the keep='last' parameter instead:

    df.loc[df.duplicated(keep='last')]
    

    You should get the following output:

Caption: Duplicate records with keep=\'last\'

By using the `keep='last'` parameter, the following rows
are considered duplicates: `42`, `62`,
`168`, `207`, `267`, `314`,
`560`, and `683`. By comparing this output to
the one from the previous step, we can see that rows 253 and 42 are
identical.

12. Remove the duplicate rows using the drop_duplicates() method along with the keep='first' parameter and save this into a new DataFrame called df_unique:

    df_unique = df.drop_duplicates(keep='first')
    

13. Display the shape of df_unique with the .shape attribute:

    df_unique.shape
    

    You should get the following output:

    (691, 11)
    

    Now that we have removed the eight duplicate records, only 691 rows remain. Now, the dataset only contains unique observations.

In this exercise, you learned how to identify and remove duplicate records from a real-world dataset.

Converting Data Types

Another problem you may face in a project is incorrect data types being inferred for some columns. As we saw in Lab 10, Analyzing a Dataset, the pandas package provides us with a very easy way to display the data type of each column using the .dtypes attribute. You may be wondering, when did pandas identify the type of each column? The types are detected when you load the dataset into a pandas DataFrame using methods such as read_csv(), read_excel(), and so on.

When you've done this, pandas will try its best to automatically find the best type according to the values contained in each column. Let's see how this works on the Online Retail dataset.

First, you must import pandas:

import pandas as pd

Then, you need to assign the URL to the dataset to a new variable:

file_url = 'https://github.com/fenago/'\
           'data-science/blob/'\
           'master/Lab10/dataset/'\
           'Online%20Retail.xlsx?raw=true'

Let's load the dataset into a pandas DataFrame using read_excel():

df = pd.read_excel(file_url)

Finally, let's print the data type of each column:

df.dtypes

You should get the following output:

Caption: The data type of each column of the Online Retail dataset

The preceding output shows the data types that have been assigned to each column. Quantity, UnitPrice, and CustomerID have been identified as numerical variables (int64, float64), InvoiceDate is a datetime variable, and all the other columns are considered text (object). This is not too bad. pandas did a great job of recognizing non-text columns.

But what if you want to change the types of some columns? You have two ways to achieve this.

The first way is to reload the dataset, but this time, you will need to specify the data types of the columns of interest using the dtype parameter. This parameter takes a dictionary with the column names as keys and the correct data types as values, such as {'col1': np.float64, 'col2': np.int32}, as input. Let's try this on CustomerID. We know this isn't a numerical variable as it contains a unique identifier (code). Here, we are going to change its type to object:

df = pd.read_excel(file_url, dtype={'CustomerID': 'category'})
df.dtypes

You should get the following output:

Caption: The data types of each column after converting CustomerID

As you can see, the data type for CustomerID has effectively changed to a category type.

Now, let's look at the second way of converting a single column into a different type. In pandas, you can use the astype() method and specify the new data type that it will be converted into as its parameter. It will return a new column (a new pandas series, to be more precise), so you need to reassign it to the same column of the DataFrame. For instance, if you want to change the InvoiceNo column into a categorical variable, you would do the following:

df['InvoiceNo'] = df['InvoiceNo'].astype('category')
df.dtypes

You should get the following output:

Caption: The data types of each column after converting InvoiceNo

As you can see, the data type for InvoiceNo has changed to a categorical variable. The difference between object and category is that the latter has a finite number of possible values (also called discrete variables). Once these have been changed into categorical variables, pandas will automatically list all the values. They can be accessed using the .cat.categories attribute:

df['InvoiceNo'].cat.categories

You should get the following output:

Caption: List of categories (possible values) for the InvoiceNo categorical variable

pandas has identified that there are 25,900 different values in this column and has listed all of them. Depending on the data type that's assigned to a variable, pandas provides different attributes and methods that are very handy for data transformation or feature engineering (this will be covered in Lab 12, Feature Engineering).

As a final note, you may be wondering when you would use the first way of changing the types of certain columns (while loading the dataset). To find out the current type of each variable, you must load the data first, so why will you need to reload the data again with new data types? It will be easier to change the type with the astype() method after the first load. There are a few reasons why you would use it. One reason could be that you have already explored the dataset on a different tool, such as Excel, and already know what the correct data types are.

The second reason could be that your dataset is big, and you cannot load it in its entirety. As you may have noticed, by default, pandas use 64-bit encoding for numerical variables. This requires a lot of memory and may be overkill.

For example, the Quantity column has an int64 data type, which means that the range of possible values is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. However, in Lab 10, Analyzing a Dataset while analyzing the distribution of this column, you learned that the range of values for this column is only from -80,995 to 80,995. You don't need to use so much space. By reducing the data type of this variable to int32 (which ranges from -2,147,483,648 to 2,147,483,647), you may be able to reload the entire dataset.

Exercise 11.02: Converting Data Types for the Ames Housing Dataset

In this exercise, you will prepare a dataset by converting its variables into the correct data types.

You will use the Ames Housing dataset to do this, which we also used in Lab 10, Analyzing a Dataset. For more information about this dataset, refer to the following note. Let's get started:

1. Open a new Jupyter notebook.

2. Import the pandas package:

   import pandas as pd
   

3. Assign the link to the Ames dataset to a variable called file_url:

   file_url = 'https://raw.githubusercontent.com/'\
              'fenago/data-science/'\
              'master/Lab10/dataset/ames_iowa_housing.csv'
   

4. Using the read_csv method from the pandas package, load the dataset into a new variable called df:

   df = pd.read_csv(file_url)
   

5. Print the data type of each column using the dtypes attribute:

   df.dtypes
   

   You should get the following output:

6. Using the astype() method, convert the 'Id' column into a categorical variable, as shown in the following code snippet:

   df['Id'] = df['Id'].astype('category')
   

7. Convert the 'MSSubClass', 'OverallQual', and 'OverallCond' columns into categorical variables, like we did in the previous step:

   df['MSSubClass'] = df['MSSubClass'].astype('category')
   df['OverallQual'] = df['OverallQual'].astype('category')
   df['OverallCond'] = df['OverallCond'].astype('category')
   

8. Create a for loop that will iterate through the four categorical columns ('Id', 'MSSubClass', 'OverallQual', and 'OverallCond') and print their names and categories using the .cat.categories attribute:

   for col_name in ['Id', 'MSSubClass', 'OverallQual', \
                    'OverallCond']:
       print(col_name)
       print(df[col_name].cat.categories)
   

   You should get the following output:

9. Create a new DataFrame called obj_df that will only contain variables of the object type using the select_dtypes method along with the include='object' parameter:

   obj_df = df.select_dtypes(include='object')
   

10. Create a new variable called obj_cols that contains a list of column names from the obj_df DataFrame using the .columns attribute and display its content:

    obj_cols = obj_df.columns
    obj_cols
    

    You should get the following output:

Caption: List of variables of the \'object\' type

11. Like we did in Step 8, create a for loop that will iterate through the column names contained in obj_cols and print their names and unique values using the unique() method:

    for col_name in obj_cols:
        print(col_name)
        print(df[col_name].unique())
    

    You should get the following output:

Caption: List of unique values for each variable of the
\'object\' type

As you can see, all these columns have a finite number of unique
values that are composed of text, which shows us that they are
categorical variables.

12. Now, create a for loop that will iterate through the column names contained in obj_cols and convert each of them into a categorical variable using the astype() method:

    for col_name in obj_cols:
        df[col_name] = df[col_name].astype('category')
    

13. Print the data type of each column using the dtypes attribute:

    df.dtypes
    

    You should get the following output:

Handling Incorrect Values

Let's learn how to detect such issues in real life by using the Online Retail dataset.

First, you need to load the data into a pandas DataFrame:

import pandas as pd
file_url = 'https://github.com/fenago/'\
           'data-science/blob/'\
           'master/Lab10/dataset/'\
           'Online%20Retail.xlsx?raw=true'
df = pd.read_excel(file_url)

In this dataset, there are two variables that seem to be related to each other: StockCode and Description. The first one contains the identifier code of the items sold and the other one contains their descriptions. However, if you look at some of the examples, such as StockCode 23131, the Description column has different values:

df.loc[df['StockCode'] == 23131, 'Description'].unique()

You should get the following output

Let's focus on the misspelling issue. What we need to do here is modify the incorrect spelling and replace it with the correct value. First, let's create a new column called StockCodeDescription, which is an exact copy of the Description column:

df['StockCodeDescription'] = df['Description']

You will use this new column to fix the misspelling issue. To do this, use the subsetting technique you learned about earlier in this lab. You need to use .loc and filter the rows and columns you want, that is, all rows with StockCode == 21131 and Description == MISELTOE HEART WREATH CREAM and the Description column:

df.loc[(df['StockCode'] == 23131) \
        & (df['StockCodeDescription'] \
           == 'MISELTOE HEART WREATH CREAM'), \
        'StockCodeDescription'] = 'MISTLETOE HEART WREATH CREAM'

If you reprint the value for this issue, you will see that the misspelling value has been fixed and is not present anymore:

df.loc[df['StockCode'] == 23131, 'StockCodeDescription'].unique()

You should get the following output:

Caption: List of unique values for the Description column and StockCode 23131 after fixing the first misspelling issue

As you can see, there are still five different values for this product, but for one of them, that is, MISTLETOE, has been spelled incorrectly: MISELTOE.

This time, rather than looking at an exact match (a word must be the same as another one), we will look at performing a partial match (part of a word will be present in another word). In our case, instead of looking at the spelling of MISELTOE, we will only look at MISEL. The pandas package provides a method called .str.contains() that we can use to look for observations that partially match with a given expression.

Let's use this to see if we have the same misspelling issue (MISEL) in the entire dataset. You will need to add one additional parameter since this method doesn't handle missing values. You will also have to subset the rows that don't have missing values for the Description column. This can be done by providing the na=False parameter to the .str.contains() method:

df.loc[df['StockCodeDescription']\
  .str.contains('MISEL', na=False),]

You should get the following output:

Caption: Displaying all the rows containing the misspelling 'MISELTOE'

This misspelling issue (MISELTOE) is not only related to StockCode 23131, but also to other items. You will need to fix all of these using the str.replace() method. This method takes the string of characters to be replaced and the replacement string as parameters:

df['StockCodeDescription'] = df['StockCodeDescription']\
                             .str.replace\
                             ('MISELTOE', 'MISTLETOE')

Now, if you print all the rows that contain the misspelling of MISEL, you will see that no such rows exist anymore:

df.loc[df['StockCodeDescription']\
  .str.contains('MISEL', na=False),]

You should get the following output

Exercise 11.03: Fixing Incorrect Values in the State Column

In this exercise, you will clean the State variable in a modified version of a dataset by listing all the finance officers in the USA. We are doing this because the dataset contains some incorrect values. Let's get started:

1. Open a new Jupyter notebook.

2. Import the pandas package:

   import pandas as pd
   

3. Assign the link to the dataset to a variable called file_url:

   file_url = 'https://raw.githubusercontent.com/'\
              'fenago/data-science/'\
              'master/Lab11/dataset/officers.csv'
   

4. Using the read_csv() method from the pandas package, load the dataset into a new variable called df:

   df = pd.read_csv(file_url)
   

5. Print the first five rows of the DataFrame using the .head() method:

   df.head()
   

   You should get the following output:

Caption: The first five rows of the finance officers dataset

6. Print out all the unique values of the State variable:

   df['State'].unique()
   

   You should get the following output:

Caption: List of unique values in the State column

7. Print out the rows that have the il value in the State column using the pandas .str.contains() method and the subsetting API, that is, DataFrame [condition]. You will also have to set the na parameter to False in str.contains() in order to exclude observations with missing values:

   df[df['State'].str.contains('il', na=False)]
   

   You should get the following output:

Caption: Observations with a value of il

8. Now, create a for loop that will iterate through the following values in the State column: Il, iL, Il. Then, print out the values of the City and State variables using the pandas method for subsetting, that is, .loc(): DataFrame.loc[row_condition, column condition]. Do this for each observation:

   for state in ['Il', 'iL', 'Il']:
       print(df.loc[df['State'] == state, ['City', 'State']])
   

   You should get the following output:

Caption: Observations with the il value

9. Create a condition mask (il_mask) to subset all the rows that contain the four incorrect values (il, Il, iL, and Il) by using the isin() method and a list of these values as a parameter. Then, save the result into a variable called il_mask:

   il_mask = df['State'].isin(['il', 'Il', 'iL', 'Il'])
   

10. Print the number of rows that match the condition we set in il_mask using the .sum() method. This will sum all the rows that have a value of True (they match the condition):

    il_mask.sum()
    

    You should get the following output:

    672
    

11. Using the pandas .loc() method, subset the rows with the il_mask condition mask and replace the value of the State column with IL:

    df.loc[il_mask, 'State'] = 'IL'
    

12. Print out all the unique values of the State variable once more:

    df['State'].unique()
    

    You should get the following output:

Caption: List of unique values for the \'State\' column

As you can see, the four incorrect values are not present anymore.
Let\'s have a look at the other remaining incorrect values:
`II`, `I`, `8I`, and `60`.
We will look at dealing `II` in the next step.

Print out the rows that have a value of `II` into the
`State` column using the `pandas` subsetting
API, that is, DataFrame.loc\[row\_condition, column\_condition\]:

```
df.loc[df['State'] == 'II',]
```


You should get the following output:

Caption: Subsetting the rows with a value of IL in the State
column

There are only two cases where the `II` value has been
used for the `State` column and both have
`Bloomington` as the city, which is in Illinois. Here, the
correct `State` value should be `IL`.

13. Now, create a for loop that iterates through the three incorrect values (I, 8I, and 60) and print out the subsetted rows using the same logic that we used in Step 12. Only display the City and State columns:

    for val in ['I', '8I', '60']:
        print(df.loc[df['State'] == val, ['City', 'State']])
    

    You should get the following output:

Caption: Observations with incorrect values (I, 8I, and 60)

All the observations that have incorrect values are cities in
Illinois. Let\'s fix them now.

14. Create a for loop that iterates through the four incorrect values (II, I, 8I, and 60) and reuse the subsetting logic from Step 12 to replace the value in State with IL:

    for val in ['II', 'I', '8I', '60']:
        df.loc[df['State'] == val, 'State'] = 'IL'
    

15. Print out all the unique values of the State variable:

    df['State'].unique()
    

    You should get the following output:

Caption: List of unique values for the State column

You fixed the issues for the state of Illinois. However, there are
two more incorrect values in this column: `In` and
`ng`.

16. Repeat Step 13, but iterate through the In and ng values instead:

    for val in ['In', 'ng']:
        print(df.loc[df['State'] == val, ['City', 'State']])
    

    You should get the following output:

Caption: Observations with incorrect values (In, ng)

The rows that have the `ng` value in `State` are
missing values. We will cover this topic in the next section. The
observation that has `In` as its `State` is a
city in Indiana, so the correct value should be `IN`.
Let\'s fix this.

17. Subset the rows containing the In value in State using the .loc() and .str.contains() methods and replace the state value with IN. Don't forget to specify the na=False parameter as .str.contains():

    df.loc[df['State']\
      .str.contains('In', na=False), 'State'] = 'IN'
    

    Print out all the unique values of the State variable:

    df['State'].unique()
    

    You should get the following output:

Caption: List of unique values for the State column

You just fixed all the incorrect values for the State variable using the methods provided by the pandas package. In the next section, we are going to look at handling missing values.

Handling Missing Values

The pandas package provides a method that we can use to identify missing values in a DataFrame: .isna(). Let's see it in action on the Online Retail dataset. First, you need to import pandas and load the data into a DataFrame:

import pandas as pd
file_url = 'https://github.com/fenago/'\
           'data-science/blob/'\
           'master/Lab10/dataset/'\
           'Online%20Retail.xlsx?raw=true'
df = pd.read_excel(file_url)

The .isna() method returns a pandas series with a binary value for each cell of a DataFrame and states whether it is missing a value (True) or not (False):

df.isna()

You should get the following output:

Caption: Output of the .isna() method

As we saw previously, we can give the output of a binary variable to the .sum() method, which will add all the True values together (cells that have missing values) and provide a summary for each column:

df.isna().sum()

You should get the following output:

Caption: Summary of missing values for each variable

As you can see, there are 1454 missing values in the Description column and 135080 in the CustomerID column. Let's have a look at the missing value observations for Description. You can use the output of the .isna() method to subset the rows with missing values:

df[df['Description'].isna()]

You should get the following output:

Caption: Subsetting the rows with missing values for Description

The pandas package provides a method that we can use to easily remove missing values: .dropna(). This method returns a new DataFrame without all the rows that have missing values. By default, it will look at all the columns. You can specify a list of columns for it to look for with the subset parameter:

df.dropna(subset=['Description'])

This method returns a new DataFrame with no missing values for the specified columns. If you want to replace the original dataset directly, you can use the inplace=True parameter:

df.dropna(subset=['Description'], inplace=True)

Now, look at the summary of the missing values for each variable:

df.isna().sum()

You should get the following output:

Caption: Summary of missing values for each variable

As you can see, there are no more missing values in the Description column. Let's have a look at the CustomerID column:

df[df['CustomerID'].isna()]

You should get the following output:

Most algorithms require a value for each observation, so you need to provide one for these cases. We will use the .fillna() method from pandas to do this. Provide the value to be imputed as Missing and use inplace=True as a parameter:

df['CustomerID'].fillna('Missing', inplace=True)
df[1443:1448]

You should get the following output:

Caption: Examples of rows where missing values for CustomerID have been replaced with Missing

Let's see if we have any missing values in the dataset:

df.isna().sum()

You should get the following output:

Exercise 11.04: Fixing Missing Values for the Horse Colic Dataset

In this exercise, you will be cleaning out all the missing values for all the numerical variables in the Horse Colic dataset.

Colic is a painful condition that horses can suffer from, and this dataset contains various pieces of information related to specific cases of this condition. You can use the link provided in the Note section if you want to find out more about the dataset's attributes. Let's get started:

1. Open a new Jupyter notebook.

2. Import the pandas package:

   import pandas as pd
   

3. Assign the link to the dataset to a variable called file_url:

   file_url = 'http://raw.githubusercontent.com/'\
              'fenago/data-science/'\
              'master/Lab11/dataset/horse-colic.data'
   

4. Using the .read_csv() method from the pandas package, load the dataset into a new variable called df and specify the header=None, sep='\s+', and prefix='X' parameters:

   df = pd.read_csv(file_url, header=None, \
                    sep='\s+', prefix='X')
   

5. Print the first five rows of the DataFrame using the .head() method:

   df.head()
   

   You should get the following output:

Caption: The first five rows of the Horse Colic dataset

6. Reload the dataset into a pandas DataFrame using the .read_csv() method, but this time, add the na_values='?' parameter in order to specify that this value needs to be treated as a missing value:

   df = pd.read_csv(file_url, header=None, sep='\s+', \
                    prefix='X', na_values='?')
   

7. Print the first five rows of the DataFrame using the .head() method:

   df.head()
   

   You should get the following output:

Caption: The first five rows of the Horse Colic dataset

Now, you can see that `pandas` have converted all the
`?` values into missing values.

8. Print the data type of each column using the dtypes attribute:

   df.dtypes
   

   You should get the following output:

Caption: Data type of each column

9. Print the number of missing values for each column by combining the .isna() and .sum() methods:

   df.isna().sum()
   

   You should get the following output:

Caption: Number of missing values for each column

10. Create a condition mask called x0_mask so that you can find the missing values in the X0 column using the .isna() method:

    x0_mask = df['X0'].isna()
    

11. Display the number of missing values for this column by using the .sum() method on x0_mask:

    x0_mask.sum()
    

    You should get the following output:

    1
    

    Here, you got the exact same number of missing values for X0 that you did in Step 9.

12. Extract the mean of X0 using the .median() method and store it in a new variable called x0_median. Print its value:

    x0_median = df['X0'].median()
    print(x0_median)
    

    You should get the following output:

    1.0
    

    The median value for this column is 1. You will replace all the missing values with this value in the X0 column.

13. Replace all the missing values in the X0 variable with their median using the .fillna() method, along with the inplace=True parameter:

    df['X0'].fillna(x0_median, inplace=True)
    

14. Print the number of missing values for X0 by combining the .isna() and .sum() methods:

    df['X0'].isna().sum()
    

    You should get the following output:

    0
    

    There are no more missing values in the variables.

15. Create a for loop that will iterate through all the columns of the DataFrame. In the for loop, calculate the median for each and save them into a variable called col_median. Then, impute missing values with this median value using the .fillna() method, along with the inplace=True parameter, and print the name of the column and its median value:

    for col_name in df.columns:
        col_median = df[col_name].median()
        df[col_name].fillna(col_median, inplace=True)
        print(col_name)
        print(col_median)
    

    You should get the following output:

Caption: Median values for each column

16. Print the number of missing values for each column by combining the .isna() and .sum() methods:

    df.isna().sum()
    

    You should get the following output:

Caption: Number of missing values for each column

You have successfully fixed the missing values for all the numerical variables using the methods provided by the pandas package: .isna() and .fillna().

Activity 11.01: Preparing the Speed Dating Dataset

As an entrepreneur, you are planning to launch a new dating app into the market. The key feature that will differentiate your app from other competitors will be your high performing user-matching algorithm. Before building this model, you have partnered with a speed dating company to collect data from real events. You just received the dataset from your partner company but realized it is not as clean as you expected; there are missing and incorrect values. Your task is to fix the main data quality issues in this dataset.

The following steps will help you complete this activity:

1. Download and load the dataset into Python using .read_csv().

2. Print out the dimensions of the DataFrame using .shape.

3. Check for duplicate rows by using .duplicated() and .sum() on all the columns.

4. Check for duplicate rows by using .duplicated() and .sum() for the identifier columns (iid, id, partner, and pid).

5. Check for unexpected values for the following numerical variables: 'imprace', 'imprelig', 'sports', 'tvsports', 'exercise', 'dining', 'museums', 'art', 'hiking', 'gaming', 'clubbing', 'reading', 'tv', 'theater', 'movies', 'concerts', 'music', 'shopping', and 'yoga'.

6. Replace the identified incorrect values.

7. Check the data type of the different columns using .dtypes.

8. Change the data types to categorical for the columns that don't contain numerical values using .astype().

9. Check for any missing values using .isna() and .sum() for each numerical variable.

10. Replace the missing values for each numerical variable with their corresponding mean or median values using .fillna(), .mean(), and .median().

You should get the following output. The figure represents the number of rows with unexpected values for imprace and a list of unexpected values:

The following figure illustrates the number of rows with unexpected values and a list of unexpected values for each column:

The following figure illustrates a list of unique values for gaming:

The following figure displays the data types of each column:

The following figure displays the updated data types of each column:

The following figure displays the number of missing values for numerical variables:

The following figure displays the list of unique values for int_corr:

The following figure displays the list of unique values for numerical variables:

The following figure displays the number of missing values for numerical variables:

Summary

In this lab, you learned how important it is to prepare any given dataset and fix the main quality issues it has. This is critical because the cleaner a dataset is, the easier it will be for any machine learning model to easily learn about the relevant patterns. On top of this, most algorithms can't handle issues such as missing values, so they must be handled prior to the modeling phase. In this lab, you covered the most frequent issues that are faced in data science projects: duplicate rows, incorrect data types, unexpected values, and missing values.