Data Cleaning and Preparation

Learn the essentials of data cleaning and preparation in Pandas, including handling missing data, data transformation, and data filtering for effective data analysis.

Overview

Real data is messy: values go missing, rows get duplicated and text comes in different shapes. In this lesson we’ll clean up the robot sensor data from the last lesson.

import pandas as pd

df = pd.DataFrame({
    'robot':   ['BurgerBot', 'SMARS', 'BurgerBot', 'SMARS', 'Quad', 'Quad'],
    'sensor':  ['distance', 'distance', 'line', 'distance', 'imu', 'distance'],
    'reading': [23.5, 41.0, 1.0, 12.2, None, 30.1],
    'battery': [87, 64, 85, 60, 92, 90],
})
print(df)

Handling Missing Data

Identifying Missing Data

Missing values show up as NaN (Not a Number). To find them:

print(df.isna())          # True wherever a value is missing
print(df.isna().sum())    # how many are missing in each column

isnull() does exactly the same as isna().

Filling Missing Data

You can fill missing data with a fixed value, a calculated value, or by interpolating between neighbours:

# Fill with zero
df_zero = df.fillna({'reading': 0})

# Fill with the column average
df_mean = df.fillna({'reading': df['reading'].mean()})

# Estimate from the values either side
df['reading_estimated'] = df['reading'].interpolate()

Interpolating works best when rows are in a meaningful order, such as readings over time.

Dropping Missing Data

Alternatively, you can drop rows or columns with missing values:

# Drop rows with any missing data
df_dropped_rows = df.dropna()

# Drop columns with any missing data
df_dropped_columns = df.dropna(axis=1)

Removing duplicates

To remove rows that are exact copies of another row, use drop_duplicates():

df = df.drop_duplicates()

Pass subset=['robot', 'sensor'] to treat rows as duplicates when only those columns match.

Data Transformation

Applying Functions

apply() runs a function on every value in a column:

# Convert distance readings from cm to inches
df['inches'] = df['reading'].apply(lambda cm: cm / 2.54)

For simple maths like this, operating on the whole column is shorter and much faster, and gives the same result:

df['inches'] = df['reading'] / 2.54

Replacing Values

Replace specific values in a column:

df['sensor'] = df['sensor'].replace('imu', 'motion')

Tidying text

The .str accessor applies string methods to a whole column:

df['robot'] = df['robot'].str.strip().str.lower()

This removes stray spaces and makes the names lower case, so 'SMARS ' and 'smars' count as the same robot.

Changing data types

Columns sometimes load with the wrong type, such as numbers stored as text. Use astype() to convert them:

df['battery'] = df['battery'].astype(float)
print(df.dtypes)

Try it yourself

Fill the Quad’s missing reading with the average of the other distance readings, then check df.isna().sum() shows no missing values.

Summary

This lesson covered essential techniques in data cleaning and preparation using Pandas: finding and handling missing data, removing duplicates, transforming values and fixing data types.

< Previous Next >

You can use the arrows  ← → on your keyboard to navigate between lessons.