mirror of
https://github.com/fenago/data-science.git
synced 2026-05-29 12:50:51 +00:00
fenago
1631 lines
41 KiB
Markdown
1631 lines
41 KiB
Markdown
|
||
<img align="right" src="./logo.png">
|
||
|
||
|
||
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.
|