Cleaning Data with Pandas

Level: Intermediate Prerequisites: Intermediate Python, basic machine learning workflow concepts

Table of Contents

1. Course Overview
2. Introduction to Data Cleaning with Pandas
   • What Is Data Cleaning?
   • The Data Cleaning Process
   • Problem and Dataset Introduction
   • Environment Setup
   • Importing the Dataset and Basic Exploration
   • Missing Values (Missing Data)
   • Handling Duplicates and Datetime Values
3. Correlation Analysis and Data Preparation
   • What Is Correlation Analysis?
   • Correlation and Data Cleaning
   • Handling Categorical Features
   • Encoding Categorical Features
   • Data Filtering with Correlation Analysis
4. Reference Diagrams
5. Pandas Method Reference Tables

1. Course Overview

In the real world, data is rarely organized in clean tables ready to be used directly in a machine learning model or for data analysis. Data found in practice is often messy, containing many missing values and other issues to resolve before drawing meaningful inferences.

Course objectives:

• Use the Pandas library in Python to clean and manipulate a real dataset
• Understand the importance of data cleaning
• Handle missing values and duplicates
• Encode categorical features
• Perform correlation analysis

Dataset used: Hotel Booking Demand (Kaggle) — ~120,000 rows, 32 columns

2. Introduction to Data Cleaning with Pandas

What Is Data Cleaning?

Data cleaning is the process of correcting or removing incorrect, missing, duplicate, and corrupted data from a given dataset.

Fundamental rule: Garbage in = Garbage out

If you work with poor quality data, the quality of your results will also be poor. You should never make decisions based on poor quality data.

Benefits of data cleaning:

The Data Cleaning Process

flowchart TD
    A[Import & Merge Data] --> B[Basic Exploration]
    B --> C{Data Issues?}
    C -->|Yes| D[Data Filtering]
    D --> E[Data Cleaning & Transformation]
    E --> C
    C -->|No| F[Clean Dataset]
    F --> G[Analysis / ML / Visualization]

    style A fill:#4A90D9,color:#fff
    style F fill:#27AE60,color:#fff
    style G fill:#8E44AD,color:#fff

The 3 main steps:

1. Data importing, merging & exploration — Import data from different sources, merge if necessary, and perform light exploration (row/column count, column types)
2. Data filtering — Remove elements that don’t help the analysis or could be harmful
3. Data cleaning & transformation — Handle missing values, duplicates, correct formats, normalization, correlation analysis

Note: Steps 2 and 3 have no particular order and can be alternated constantly.

Problem and Dataset Introduction

The Hotel Booking Demand dataset (available on Kaggle) contains booking information for a city hotel and a resort hotel.

Available information:

• When the booking was made
• Length of stay
• Number of adults, children, and/or babies
• Number of available parking spaces
• And much more…

Dataset overview:

• ~120,000 rows
• 32 columns
• Key columns: hotel, is_canceled, lead_time, reservation_status_date, etc.

Environment Setup

Required packages:

# Installation via Anaconda (recommended)
# Anaconda includes Python, NumPy, Pandas, Jupyter Notebook and more

# Or manual installation
pip install numpy pandas jupyter scikit-learn

Main libraries:

Launching Jupyter Notebook:

# Virtual environment activation (optional)
source venv/bin/activate  # Linux/macOS
.\venv\Scripts\activate   # Windows

# Launch
jupyter notebook

Importing the Dataset and Basic Exploration

import pandas as pd

# Read the CSV dataset
data_df = pd.read_csv('hotel_bookings.csv')

# Number of rows and columns
print(data_df.shape)  # (119390, 32)

# Display first 5 rows
data_df.head()

# DataFrame info (column types, non-null values)
data_df.info()

# Descriptive statistics
data_df.describe()

# Column list
print(data_df.columns.tolist())

Basic exploration pipeline:

flowchart LR
    A[read_csv] --> B[shape]
    B --> C[head / tail]
    C --> D[info]
    D --> E[describe]
    E --> F[value_counts]
    F --> G[isnull]

    style A fill:#2980B9,color:#fff
    style G fill:#E74C3C,color:#fff

Missing Values (Missing Data)

Definition: Missing data occurs when values are simply absent or contain NaN (Not a Number) for any feature or column in a dataset.

Possible causes:

• Corrupted data
• Human errors during data entry
• Faulty sensor
• Bug in the data processing pipeline

Options for Handling Missing Values

Demo: Handling Missing Values

# Find the number of missing values per column
missing_values = data_df.isnull().sum()
print(missing_values)

# Percentage of missing values
missing_pct = (data_df.isnull().sum() / len(data_df)) * 100
print(missing_pct)

# Example output:
# company    94.307473  (94% missing values!)
# agent       13.686...
# children     0.003...

# Delete the 'company' column (94% missing values)
data_df.drop('company', axis=1, inplace=True)

# For 'children' column, see unique values
print(data_df['children'].value_counts())

# Impute missing values of 'children' with 0
data_df['children'].fillna(0, inplace=True)

# For 'agent' column, impute with mode
data_df['agent'].fillna(data_df['agent'].mode()[0], inplace=True)

# Final verification
print(data_df.isnull().sum())

Important drop() parameters:

Handling Duplicates and Datetime Values

Handling Duplicates

# Check for duplicates
num_duplicates = data_df.duplicated().sum()
print(f"Number of duplicate rows: {num_duplicates}")  # ~32,000 duplicates

# Inspect duplicate rows
duplicated_rows = data_df.loc[data_df.duplicated()]
print(duplicated_rows)

# Delete duplicates
# keep='first': keep first occurrence (default)
# keep='last':  keep last occurrence
data_df.drop_duplicates(inplace=True, keep='first')

# Verification
print(data_df.shape)

Handling Datetime Values

# Check the column type
print(data_df['reservation_status_date'].dtype)  # object (string)

# Convert to datetime
data_df['reservation_status_date'] = pd.to_datetime(
    data_df['reservation_status_date']
)

# Verification
print(data_df['reservation_status_date'].dtype)  # datetime64[ns]

# Extract date components
data_df['year'] = data_df['reservation_status_date'].dt.year
data_df['month'] = data_df['reservation_status_date'].dt.month
data_df['day'] = data_df['reservation_status_date'].dt.day

Useful dt methods for datetime columns:

3. Correlation Analysis and Data Preparation

What Is Correlation Analysis?

Correlation analysis is a statistical technique used to examine the strength and direction of the relationship between two or more variables. It uses correlation coefficients to quantify this relationship.

Comparison of Correlation Coefficients

Monotonic relationship: a mathematical relationship where variables increase or decrease together, but not necessarily at a constant rate.

Correlation and Data Cleaning

Correlation analysis can identify highly correlated variables, which may indicate that a variable is redundant and can be removed from the dataset.

Multicollinearity problem:

Multicollinearity is a problem that arises when there is a high correlation between two or more independent variables in a regression model. It can:

• Make it difficult to estimate the coefficients of the independent variables
• Lead to unstable and unreliable regression models

Solution: If two or more independent variables are highly correlated, it may be necessary to remove one or more to reduce noise and redundancy.

Handling Categorical Features

Machine learning models cannot directly process non-numeric features. Two encoding methods exist:

Label Encoding

Each unique category in a categorical variable is assigned a numeric label (0, 1, 2, etc.).

Country     →  Encoding
India       →  1
UK          →  2
Australia   →  3

Advantage: Simple, suitable for variables with a natural order (ordinal)
Disadvantage: Introduces an artificial ordering between categories that have none

One-Hot Encoding

A new binary feature is created for each category.

Country      India  UK  Australia
India      →   1     0      0
UK         →   0     1      0
Australia  →   0     0      1

Advantage: No artificial ordering
Disadvantage: Creates many new columns if the variable has many unique categories (e.g., country)

Encoding Categorical Features

# Identify all categorical columns (object type)
categorical_cols = [col for col in data_df.columns
                    if data_df[col].dtype == 'object']
print(categorical_cols)

# Create a DataFrame with only categorical features
cat_df = data_df[categorical_cols].copy()
cat_df.head()

# See unique values for each categorical feature
for col in categorical_cols:
    print(f"\n{col}:")
    print(cat_df[col].unique())

# -------------------------
# Manual encoding with map() — arrival_date_month
# -------------------------
month_map = {
    'January': 1, 'February': 2, 'March': 3, 'April': 4,
    'May': 5, 'June': 6, 'July': 7, 'August': 8,
    'September': 9, 'October': 10, 'November': 11, 'December': 12
}
data_df['arrival_date_month'] = data_df['arrival_date_month'].map(month_map)

# -------------------------
# Label Encoding — country, hotel (few unique values)
# -------------------------
from sklearn.preprocessing import LabelEncoder

le = LabelEncoder()

# Encode the 'country' column
data_df['country'] = le.fit_transform(data_df['country'].astype(str))

# Encode the 'hotel' column (City Hotel=0, Resort Hotel=1)
data_df['hotel'] = le.fit_transform(data_df['hotel'])

# -------------------------
# One-Hot Encoding — meal, distribution_channel, etc.
# -------------------------
data_df = pd.get_dummies(data_df, columns=['meal', 'market_segment'],
                          drop_first=True)

# Verification
data_df.head()

Encoding decision summary:

flowchart TD
    A[Categorical Feature] --> B{Number of unique values?}
    B -->|Many e.g. country| C[Label Encoding]
    B -->|Few e.g. hotel, meal| D{Natural order?}
    D -->|Yes e.g. size S/M/L| E[Label / Ordinal Encoding]
    D -->|No| F[One-Hot Encoding]

    style C fill:#E67E22,color:#fff
    style E fill:#27AE60,color:#fff
    style F fill:#2980B9,color:#fff

Data Filtering with Correlation Analysis

import numpy as np

# Create correlation matrix (absolute values)
# Absolute values are used to capture both negative and positive
# correlations with equal importance
corr_matrix = data_df.corr().abs()

# Define the high correlation threshold
threshold = 0.8

# Create a mask to identify highly correlated features
# np.ones: creates an array of 1s with the same shape as corr_matrix
# np.triu: returns the upper triangular part
# k=1: excludes the main diagonal (a variable's correlation with itself)
upper_triangular = np.triu(np.ones(corr_matrix.shape), k=1).astype(bool)
upper = corr_matrix.where(upper_triangular)

# Identify columns to drop
cols_to_drop = [col for col in upper.columns
                if any(upper[col] > threshold)]

print(f"Highly correlated columns to drop: {cols_to_drop}")

# Drop redundant columns
data_df.drop(columns=cols_to_drop, inplace=True)

print(f"Final DataFrame dimensions: {data_df.shape}")

Visualizing the correlation matrix:

import matplotlib.pyplot as plt
import seaborn as sns

plt.figure(figsize=(12, 10))
sns.heatmap(
    corr_matrix,
    annot=False,
    cmap='coolwarm',
    center=0,
    square=True,
    linewidths=0.5
)
plt.title('Correlation Matrix')
plt.tight_layout()
plt.show()

4. Reference Diagrams

Complete Data Cleaning Pipeline

flowchart TD
    A[Data Source\nCSV / DB / API] --> B[read_csv / read_sql\nread_json / read_excel]
    B --> C[Initial exploration\nshape, info, describe, head]

    C --> D{Missing\nvalues?}
    D -->|gt 60%| E[drop axis=1\nDelete column]
    D -->|Numeric| F[fillna mean / median]
    D -->|Categorical| G[fillna mode]
    D -->|Temporal| H[fillna ffill / bfill]

    E --> I[Duplicates]
    F --> I
    G --> I
    H --> I

    I --> J{duplicated?}
    J -->|Yes| K[drop_duplicates\ninplace=True]
    J -->|No| L[Data types]
    K --> L

    L --> M{Datetime\ncorrect?}
    M -->|No| N[pd.to_datetime]
    M -->|Yes| O[Categorical features]
    N --> O

    O --> P{Nb unique\nvalues?}
    P -->|Many| Q[LabelEncoder\nsklearn]
    P -->|Few without order| R[pd.get_dummies\nOne-Hot Encoding]
    P -->|Few with order| S[map / replace\nOrdinal Encoding]

    Q --> T[Correlation analysis]
    R --> T
    S --> T

    T --> U[corr abs\nCorrelation matrix]
    U --> V{Correlation\ngt threshold?}
    V -->|Yes| W[drop redundant\ncolumns]
    V -->|No| X[Clean dataset\nready for ML]
    W --> X

    style A fill:#2C3E50,color:#fff
    style X fill:#27AE60,color:#fff
    style B fill:#2980B9,color:#fff

5. Pandas Method Reference Tables

Exploration Methods

Missing Value Cleaning Methods

Duplicate Management Methods

Transformation Methods

Correlation Analysis Methods

Data Selectors

inplace Parameter — Summary

Workflow Summary — Cheatsheet

import pandas as pd
import numpy as np
from sklearn.preprocessing import LabelEncoder

# ---- 1. IMPORT ----
df = pd.read_csv('data.csv')

# ---- 2. EXPLORE ----
print(df.shape)
df.info()
df.describe()
df.head()

# ---- 3. MISSING VALUES ----
print(df.isnull().sum())
print((df.isnull().sum() / len(df)) * 100)

df.drop('col_high_missing', axis=1, inplace=True)           # >60% missing
df['num_col'].fillna(df['num_col'].median(), inplace=True)  # numeric
df['cat_col'].fillna(df['cat_col'].mode()[0], inplace=True) # categorical

# ---- 4. DUPLICATES ----
print(df.duplicated().sum())
df.drop_duplicates(inplace=True, keep='first')

# ---- 5. DATETIME ----
df['date_col'] = pd.to_datetime(df['date_col'])

# ---- 6. ENCODING ----
le = LabelEncoder()
df['cat_col_many'] = le.fit_transform(df['cat_col_many'].astype(str))
df = pd.get_dummies(df, columns=['cat_col_few'], drop_first=True)

# ---- 7. CORRELATION ----
corr_matrix = df.corr().abs()
upper = corr_matrix.where(
    np.triu(np.ones(corr_matrix.shape), k=1).astype(bool)
)
cols_to_drop = [col for col in upper.columns if any(upper[col] > 0.8)]
df.drop(columns=cols_to_drop, inplace=True)

print(f"Final dataset: {df.shape}")

Search Terms

cleaning · data · pandas · python · foundations · analysis · engineering · analytics · correlation · handling · methods · values · missing · encoding · categorical · dataset · datetime · duplicates · exploration · features · reference