Pandas Data Cleaning

Data cleaning is the process of handling useless data.

Many datasets contain missing data, incorrect data formats, wrong data, or duplicate data. To make data analysis more accurate, you need to process this useless data.

Common steps in data cleaning and preprocessing:

1. Missing value handling: Identify and fill missing values, or delete rows/columns containing missing values.

2. Duplicate data handling: Check and remove duplicate data to ensure each record is unique.

3. Outlier handling: Identify and handle outliers, such as extreme values and erroneous values.

4. Data format conversion: Convert data types or perform unit conversions, such as date format conversion.

5. Standardization and normalization: Standardize (e.g., Z-score) or normalize (e.g., Min-Max) numerical data.

6. Categorical data encoding: Convert categorical variables into numerical form. Common methods include One-Hot encoding and label encoding.

7. Text processing: Clean text data, such as removing stop words, stemming, tokenization, etc.

8. Data sampling: Draw samples from the dataset, or handle class imbalance through oversampling/undersampling.

9. Feature engineering: Create new features, remove irrelevant features, select important features, etc.

In this tutorial, we will use the Pandas package for data cleaning.

The test data used in this articleproperty-data.csvis as follows:

The table above contains four types of null data:

• n/a
• NA
• —
• na

Pandas Cleaning Null Values

If we want to delete rows containing empty fields, we can use thedropna()method, the syntax format is as follows:

DataFrame.dropna(axis=0, how='any', thresh=None, subset=None, inplace=False)

Parameter description:

• axis: by default,0means removing the entire row when a null value is encountered; if the parameter is set toaxis＝1it means removing the entire column when a null value is encountered.
• how: by default,'any'if any data in a row (or column) contains NA, the entire row is removed; if set tohow='all'only when all values in a row (or column) are NA is the entire row removed.
• thresh: set how many non-null values are required for the data to be kept.
• subset: set the columns you want to check. If there are multiple columns, you can use a list of column names as the parameter.
• inplace: if set to True, directly overwrites the previous value with the calculated value and returns None, modifying the source data.

We can use theisnull()method to determine whether each cell is empty.

The output result of the above example is as follows:

In the above example, we see that Pandas treats n/a and NA as null data, while na is not null data and does not meet our requirement. We can specify the null data type:

The output result of the above example is as follows:

The next example demonstrates deleting rows that contain null data.

The output result of the above example is as follows:

Note:By default, the dropna() method returns a new DataFrame and does not modify the source data.

If you want to modify the source DataFrame, you can use theinplace = Trueparameter:

The output result of the above example is as follows:

We can also remove rows where a specified column has null values:

The output result of the above example is as follows:

We can also use thefillna()method to replace some empty fields:

The output result of the above example is as follows:

We can also specify a particular column to replace data:

The output result of the above example is as follows:

A common method for replacing empty cells is to calculate the mean, median, or mode of the column.

Pandas uses themean()、median()andmode()method to calculate the mean (the average of all values), the median (the middle value after sorting), and the mode (the most frequently occurring value) of a column.

The output result of the above example is as follows, the red box is the calculated mean that replaced the empty cells:

The output result of the above example is as follows, the red box is the calculated median that replaced the empty cells:

The output result of the above example is as follows, the red box is the calculated mode that replaced the empty cells:

Pandas Cleaning Incorrectly Formatted Data

Cells with incorrect data formats make data analysis difficult, or even impossible.

We can handle rows containing empty cells, or convert all cells in a column to data in the same format.

The following example formats the date:

The output result of the above example is as follows:

           Date  duration
day1 2020-12-01        50
day2 2020-12-02        40
day3 2020-12-26        45

Pandas Cleaning Incorrect Data

Data errors are also a very common situation. We can replace or remove incorrect data.

The following example replaces data with incorrect ages:

The output result of the above example is as follows:

     name  age
0  Google   50
1  Example   40
2  Taobao   30

You can also set conditional statements:

The output result of the above example is as follows:

     name  age
0  Google   50
1  Example  120
2  Taobao  120

You can also delete rows with incorrect data:

The output result of the above example is as follows:

     name  age
0  Google   50
1  Example   40

Pandas Cleaning Duplicate Data

If we want to clean duplicate data, we can use theduplicated()anddrop_duplicates()method.

If the corresponding data is duplicate,duplicated()it will return True, otherwise it will return False.

The output result of the above example is as follows:

0    False
1    False
2     True
3    False
dtype: bool

To remove duplicate data, you can directly use thedrop_duplicates()method.

The output result of the above example is as follows:

     name  age
0  Google   50
1  Example   40
3  Taobao   23

Common Methods and Descriptions

Common methods for data cleaning and preprocessing:

Fill missing values:

Output:

      Name  Age           City
0    Alice   25.0       New York
1      Bob   30.0    Los Angeles
2  Charlie   30.0        Chicago
3     None   35.0        Houston

One-Hot Encoding:

Output:

   City_Chicago  City_Houston  City_Los Angeles  City_New York
0             0             0                 0              1
1             0             0                 1              0
2             1             0                 0              0
3             0             1                 0              0

Standardization:

Output:

[[-1.41421356 -1.41421356]
 [-0.70710678 -0.70710678]
 [ 0.          0.        ]
 [ 0.70710678  0.70710678]
 [ 1.41421356  1.41421356]]