Pandas for Data Engineers

Overview

Build a complete ETL pipeline in Pandas: extract, clean, transform, aggregate, and load data.

Learning Objectives

• Construct a structured ETL pipeline.
• Clean null values, cast schemas types, and calculate aggregates.
• Export final table columns cleanly as index-free CSV files.

Concept Explanation

Data Engineers build ETL (Extract, Transform, Load) pipelines to move data from raw files to clean tables. The pipeline extracts raw data, fills missing cells, standardizes columns names, typecasts data types, adds calculated features, groups aggregates, and exports the output file.

Code Examples

Example 1 — Basics

This example introduces the fundamental syntax and concepts.

import pandas as pd
# ETL cycle: read_csv -> clean nulls -> group aggregates -> to_csv

Example 2 — Everyday Usage

This example demonstrates a realistic scenario handling business parameters.

# Build a simple ETL script processing raw logs string
import io
raw_data = """store,sales,returns
store_a,150,10
store_b,nan,5
store_a,200,nan
"""
# Extract
df = pd.read_csv(io.StringIO(raw_data))
# Clean
df['sales'] = df['sales'].fillna(0)
df['returns'] = df['returns'].fillna(0)
# Transform
df['net_sales'] = df['sales'] - df['returns']
# Aggregate
summary = df.groupby('store')['net_sales'].sum().reset_index()
# Load (export)
print(summary.to_csv(index=False))

Example 3 — Practical Data Engineering Example

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

# End-to-end data pipeline script
import io
raw_csv_data = """id,user_name,score,join_date
101,alex,90.5,2026-01-10
102,bob,nan,2026-02-15
103,charlie,85.0,2026-03-20
"""
# 1. Load
df = pd.read_csv(io.StringIO(raw_csv_data))

# 2. Clean missing values
df['score'] = df['score'].fillna(0.0)

# 3. Transform: rename columns and cast types
df = df.rename(columns={'user_name': 'username'})
df['score'] = df['score'].astype(float)
df['join_date'] = pd.to_datetime(df['join_date'])

# 4. Feature calculations
df['join_month'] = df['join_date'].dt.month

# 5. Export processed data
print(df.to_json(orient='records', indent=2))

Visual Flow

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

Extract CSV Source → Handle nulls & cast dtypes → Generate month fields → Sum scores → Load JSON

Common Mistakes

Review these common pitfalls when working with this topic:

• Iterating rows using loops (like iterrows()) inside pipelines, which is extremely slow.
• Not validating data types before running math transformations.
• Loading large files without checking memory constraints.
• Exporting tables with index columns in file logs.

Best Practices

Enforce these Pythonic best practices in your codebase:

for idx, row in df.iterrows():
    df.at[idx, 'clean_score'] = fill_logic(row) # Slow loop updates

df['clean_score'] = df['score'].fillna(0.0) # Fast vectorized updates

Performance Notes

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

• Avoid using loops like iterrows() inside data pipelines. Use vectorized operations for better performance.

Quick Revision

Use these key summaries for last-minute revision:

• ETL stands for Extract, Transform, Load.
• Build pipelines using clean, step-by-step methods.
• Avoid loops inside pipelines.
• Validate dataset shapes and schemas.
• Always use index=False when exporting datasets.