Data Cleaning & Transformation

Overview

Clean datasets by handling null values, removing duplicates, mapping columns, typecasting, and parsing dates.

Learning Objectives

• Manage null values using fillna() and dropna().
• Rename columns, cast types using astype(), and map values.
• Parse date strings into datetime objects using to_datetime().

Concept Explanation

Cleaning and transforming are core ETL operations. Pandas provides fillna() to replace null cells, dropna() to delete rows with missing data, and drop_duplicates() to remove duplicate entries. Use astype() to convert types, rename() to standardize headers, and pd.to_datetime() to parse timestamps.

Code Examples

Example 1 — Basics

This example introduces the fundamental syntax and concepts.

import pandas as pd
# df['rating'] = df['rating'].fillna(0.0)
# df['date'] = pd.to_datetime(df['date'])

Example 2 — Everyday Usage

This example demonstrates a realistic scenario handling business parameters.

# Rename columns, drop duplicates, and convert data type
data = {
    'User ID': [101, 102, 101],
    'score': ['95', '80', '95']
}
df = pd.DataFrame(data)
# Rename columns
df = df.rename(columns={'User ID': 'user_id'})
# Cast text column to float
df['score'] = df['score'].astype(float)
# Drop duplicate rows
df = df.drop_duplicates()
print(df)

Example 3 — Practical Data Engineering Example

This example shows clean, production-grade code structure following senior development standards.

# Complete data cleaning, value mapping, and date parsing sequence
data = {
    'join_date': ['2026-01-10', '2026-02-15'],
    'dept_code': ['HR', 'IT'],
    'salary': [50000, None]
}
df = pd.DataFrame(data)
# Convert date text to datetime and extract year
df['join_date'] = pd.to_datetime(df['join_date'])
df['join_year'] = df['join_date'].dt.year
# Map department code to description
df['department'] = df['dept_code'].map({'HR': 'Human Resources', 'IT': 'Information Technology'})
# Fill missing salary values with department average
df['salary'] = df['salary'].fillna(45000)
print(df)

Visual Flow

The following execution flow represents the step-by-step evaluation inside the interpreter:

Messy DataFrame → Rename columns → Convert datatypes (astype) → Parse dates → Fill null cells (fillna)

Common Mistakes

Review these common pitfalls when working with this topic:

• Using inplace=True (deprecated; assign to variables instead, e.g. df = df.dropna()).
• Applying maps that do not specify translations for all keys (missing keys become NaN).
• Using the .dt accessor on columns that are still stored as text strings (convert to datetime first).
• Using apply() for simple mathematical updates that could be vectorized.

Best Practices

Enforce these Pythonic best practices in your codebase:

df['double'] = df['price'].apply(lambda x: x * 2) # Slow row apply

df['double'] = df['price'] * 2 # Fast vectorized column math

Performance Notes

Keep these optimization guidelines in mind for performance-sensitive hotpaths:

• Avoid using inplace=True; it is deprecated because it does not improve memory efficiency and can cause bugs.
• apply() runs slow Python functions on every row. Use vectorized math or map() for better performance.

Quick Revision

Use these key summaries for last-minute revision:

• fillna() substitutes missing NaN cells.
• dropna() deletes rows with null cells.
• rename() maps headers using dictionaries.
• astype() converts data types (e.g. float to int).
• pd.to_datetime() parses text dates into datetime structures.
• Never use inplace=True.