Preprocessing data with the skrub Cleaner

Introduction

Once a table has been explored, the next step is transforming it into something that can be handled more easily.

This can be described broadly as “cleaning” or “sanitizing” the data.

An example dataframe

import pandas as pd

df = pd.DataFrame(
    {
        "numerical_1": [1, 2, 3, 4, 5],
        "numerical_2": pd.Series([10.5, 20.3, "?", 40.1, 50.2]).astype(str),
        "string_column": ["apple", "?", "banana", "cherry", "?"],
        "datetime_column": [
            "03 Jan 2020",
            "04 Jan 2020",
            "05 Jan 2020",
            "06 Jan 2020",
            "07 Jan 2020",
        ],
        "all_none": ["?", "?", "?", "?", "?"],
    }
)

There are some issues!

• Null values in various columns
• "?" used as null value marker
• The datetime column has an unusual format
• There is a column that is fully empty

Cleaning with pandas

First, let’s look at the datatypes detected by pandas:

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 5 columns):
 #   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   numerical_1      5 non-null      int64 
 1   numerical_2      5 non-null      object
 2   string_column    5 non-null      object
 3   datetime_column  5 non-null      object
 4   all_none         5 non-null      object
dtypes: int64(1), object(4)
memory usage: 332.0+ bytes

• Missing values were not detected properly: "?" was treated as a string
• One of the numerical columns has string dtype
• The all_none column shows no null values because of “?”
• The datetime is still treated as object

Cleaning with pandas

import numpy as np

# Parse the datetime strings with a specific format
df['datetime_column'] = pd.to_datetime(df['datetime_column'], format='%d %b %Y')

# Drop columns with only a single unique value
df_clean = df.loc[:, df.nunique(dropna=True) > 1]

# Replace "?" with np.nan in all string columns
df_clean = df_clean.replace("?", np.nan)

Cleaning with pandas

# Function to drop columns with only missing values or empty strings
def drop_empty_columns(df):
    # Drop columns with only missing values
    df_clean = df.dropna(axis=1, how='all')
    # Drop columns with only empty strings
    empty_string_cols = df_clean.columns[df_clean.eq('').all()]
    df_clean = df_clean.drop(columns=empty_string_cols)
    return df_clean

# Apply the function to the DataFrame
df_clean = drop_empty_columns(df_clean)
df_clean

	numerical_1	numerical_2	string_column	datetime_column
0	1	10.5	apple	2020-01-03
1	2	20.3	NaN	2020-01-04
2	3	NaN	banana	2020-01-05
3	4	40.1	cherry	2020-01-06
4	5	50.2	NaN	2020-01-07

The alternative: skrub.Cleaner

from skrub import Cleaner
df_clean = Cleaner().fit_transform(df)
df_clean

	numerical_1	numerical_2	string_column	datetime_column
0	1	10.5	apple	2020-01-03
1	2	20.3	None	2020-01-04
2	3	None	banana	2020-01-05
3	4	40.1	cherry	2020-01-06
4	5	50.2	None	2020-01-07

The alternative: skrub.Cleaner

The cleaned version of the dataframe is now marking missing values correctly, and the datetime column has been parsed as such:

df_clean.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 4 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   numerical_1      5 non-null      int64         
 1   numerical_2      4 non-null      object        
 2   string_column    3 non-null      object        
 3   datetime_column  5 non-null      datetime64[ns]
dtypes: datetime64[ns](1), int64(1), object(2)
memory usage: 292.0+ bytes

What does the Cleaner do in practice?

1. It replaces strings used to represent null values with None
2. It removes uninformative columns
3. It tries to parse datetime columns
4. It parses strings like "1234.6" to numbers

Note on intended usage

Tip

The Cleaner is intended to be used early on when pre-processing, so it avoids altering the input dataframe too much, and tries to move from less-informative dtypes (e.g., generic strings) to more-informative types (e.g., datetimes).

The assumption is that complex transformations are done in later steps by the user.

Data cleaning parameters:

cleaner = Cleaner(
    drop_null_fraction=0.9,  # Drop columns 90% null
    drop_if_constant=True,   # Drop constant columns
    datetime_format="%Y-%m-%d",  # Specific datetime format
    null_strings=["UKN", "unknown", "missing"] # Custom strings to mark null values
)

Bonus: convert numeric columns with European separators

from skrub import ToFloat
s = pd.Series(["4 567,89", "12 567,89"], name="x")
print(s)
d = ToFloat(decimal=",", thousand=" ").fit_transform(s)
print(d)

0     4 567,89
1    12 567,89
Name: x, dtype: object
0     4567.890137
1    12567.889648
Name: x, dtype: float32

What we have seen in this chapter

• The Cleaner automates common data cleaning steps.
• It parses numbers and datetimes, and replaces null sentinels with actual null values.
• It detects and possibly removes uninformative columns.
• It can be applied directly to any dataframe.