pandas for data preparation: what developers should know

Why pandas matters for machine learning

Before your data ever reaches a model, it lives in tables. Most real production data arrives as CSV exports, database queries, or Parquet files—not clean, numeric arrays ready for training. Pandas is the bridge between raw tabular data and machine learning pipelines, and mastering pandas for data preparation: what developers should know separates engineers who ship ML systems from those who ship broken ones.

This guide walks you through the core operations we use every day: loading data from multiple sources, inspecting what you're actually working with, cleaning the mess, and building features that models can learn from. We'll focus on patterns that work in production, where datasets are messy and your time is valuable.

Loading data: choosing the right format and method

You'll encounter three main ways to ingest tabular data: CSV files, Parquet, and direct database queries. Each has trade-offs in speed, storage, and ease of use.

CSV is everywhere. It's human-readable, easy to share, and works on any platform. The downside: pandas has to parse text, which slows down large files and wastes disk space.

import pandas as pd

# Basic CSV load
df = pd.read_csv('orders.csv')

# For large files, specify dtypes to speed up parsing
dtypes = {
    'order_id': 'int64',
    'user_id': 'int64',
    'amount': 'float32',
    'status': 'category'
}
df = pd.read_csv('orders.csv', dtype=dtypes)

Parquet is a columnar binary format. It's faster to read, compresses better, and preserves types automatically. If you're processing the same dataset repeatedly, convert to Parquet once.

# Read Parquet (much faster for large files)
df = pd.read_parquet('orders.parquet')

# Write to Parquet for future use
df.to_parquet('orders.parquet', compression='snappy')

Databases let you query only what you need. This is crucial when your full dataset is 100 GB but you only need a training slice.

import sqlalchemy

engine = sqlalchemy.create_engine('postgresql://user:pass@localhost/mydb')

# Query directly into a DataFrame
query = """
SELECT order_id, user_id, amount, created_at, status
FROM orders
WHERE created_at >= '2024-01-01'
"""
df = pd.read_sql(query, engine)

In production, we often chain these: pull from the database, convert to Parquet for caching, then reload the Parquet file for analysis. This minimizes database load and keeps your pipeline fast across multiple runs.

Inspect first: understand your data before touching it

The biggest mistake developers make is jumping straight to cleaning. You can't fix data you don't understand. Before writing any transformation, run these three commands.

df.info() shows you the shape, column names, types, and null counts. This is your first reality check.

df.info()

# Output:
# RangeIndex: 10000 entries, 0 to 9999
# Data columns (total 5 columns):
#  #   Column      Non-Null Count  Dtype
# ---  ------      --------------  -----
#  0   order_id    10000 non-null  int64
#  1   user_id     9950 non-null   int64
#  2   amount      10000 non-null  float64
#  3   created_at  10000 non-null  object
#  4   status      10000 non-null  object
# dtypes: int64(2), float64(1), object(2)

Notice that user_id has only 9,950 non-null values out of 10,000. That's a red flag. Also, created_at is object (string), not datetime—we'll fix that later.

df.describe() gives you statistics: min, max, mean, quartiles. It catches outliers and impossible values.

df.describe()

#            order_id       user_id        amount
# count    10000.000000  9950.000000  10000.000000
# mean     5000.500000  5010.230000    156.782000
# std      2886.896543  2891.456543     98.456000
# min        1.000000     1.000000      0.010000
# 25%     2500.750000  2505.573000     78.450000
# 50%     5000.500000  5010.230000    156.890000
# 75%     7500.250000  7515.887000    235.120000
# max    10000.000000 10000.000000   1250.000000

The amount column ranges from 0.01 to 1250. Is that realistic? If your orders should be between $5 and $500, those outliers matter.

df.isna().sum() breaks down null counts by column, giving you a clear picture of data quality.

df.isna().sum()

# order_id        0
# user_id        50
# amount          0
# created_at      0
# status          0
# dtype: int64

Now you know exactly what you're dealing with: 50 missing user IDs, everything else complete. You can decide: drop those rows, fill them with a default, or investigate why they're missing.

Clean: fix types, handle missing values, remove duplicates

Data cleaning is where most ML projects spend their time. A few common operations handle 80% of real-world mess.

Fix types. Dates almost always arrive as strings. Convert them early, before any filtering or aggregation.

# Convert strings to datetime
df["created_at"] = pd.to_datetime(df["created_at"])

# Now you can do time-based operations
df = df[df["created_at"] >= "2024-01-01"]
df["day_of_week"] = df["created_at"].dt.day_name()

# Convert low-cardinality columns to category
df["status"] = df["status"].astype("category")

Handle missing values. You have a few options: drop rows, forward-fill for time series, or fill with a default. Choose based on why the data is missing.

# Drop rows where critical columns are null
df = df.dropna(subset=["order_id", "user_id", "amount"])

# For user_id, fill with a special value (e.g., -1 for "unknown")
df["user_id"].fillna(-1, inplace=True)

# For time series, forward-fill (carry last known value forward)
df["price"].fillna(method="ffill", inplace=True)

Remove duplicates. In production systems, the same transaction can arrive twice—from retries, batch replays, or sync errors. Check your unique identifier.

# Keep first occurrence of each order
df = df.drop_duplicates(subset="order_id", keep="first")

# Or keep the most recent
df = df.sort_values("created_at").drop_duplicates(subset="order_id", keep="last")

Here's a complete cleaning workflow for a typical e-commerce dataset:

# Load raw data
df = pd.read_csv("orders.csv")

# Step 1: Inspect
print(f"Shape: {df.shape}")
print(df.info())
print(df.describe())

# Step 2: Type conversion
df["created_at"] = pd.to_datetime(df["created_at"])
df["status"] = df["status"].astype("category")

# Step 3: Remove duplicates (before handling nulls)
df = df.drop_duplicates(subset="order_id", keep="first")

# Step 4: Handle nulls
df = df.dropna(subset=["order_id", "user_id", "amount"])
df["region"].fillna("unknown", inplace=True)

# Step 5: Filter impossible values
df = df[df["amount"] > 0]
df = df[df["created_at"] >= "2023-01-01"]

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

Group and aggregate: building features from raw rows

A raw transaction table is useful, but models learn from aggregated features. GroupBy + agg turns rows into insights.

Say you're building a fraud detector. You don't feed raw transactions to the model; you build features like "orders per user per day" or "average transaction amount."

# Count orders per user
user_order_counts = df.groupby("user_id")["order_id"].count().reset_index()
user_order_counts.columns = ["user_id", "order_count"]

# Average order amount per user
user_avg_amount = df.groupby("user_id")["amount"].mean().reset_index()
user_avg_amount.columns = ["user_id", "avg_order_amount"]

# Merge features back onto original data
df = df.merge(user_order_counts, on="user_id", how="left")
df = df.merge(user_avg_amount, on="user_id", how="left")

Use agg() to compute multiple statistics at once:

# Multiple aggregations in one step
user_features = df.groupby("user_id").agg({
    "order_id": "count",           # Count of orders
    "amount": ["sum", "mean", "std"],  # Total, average, volatility
    "created_at": "max"            # Most recent order
}).reset_index()

# Flatten multi-level columns
user_features.columns = [
    "user_id", "order_count", "total_amount", 
    "avg_amount", "std_amount", "last_order_date"
]

For time-based features (common in production), use groupby with a time window:

# Orders in the last 30 days
thirty_days_ago = pd.Timestamp.now() - pd.Timedelta(days=30)
df_recent = df[df["created_at"] >= thirty_days_ago]

recent_orders = df_recent.groupby("user_id")["order_id"].count().reset_index()
recent_orders.columns = ["user_id", "orders_last_30d"]

Common mistakes

• Inspecting after cleaning: You've already lost visibility into what was broken. Always run df.info() and df.describe() on raw data first.
• Ignoring data types: Dates as strings, IDs as floats, and categories as strings all break downstream operations. Convert types explicitly.
• Handling nulls without understanding them: Don't blindly drop or fill. Ask: why are these values missing? Are they random or systematic?
• Forgetting to reset_index() after groupby: GroupBy returns a Series or DataFrame with the grouping column as the index. You'll lose it if you don't reset.
• Building features on the full dataset: If you're building time-based features (like "orders in the last 30 days"), ensure your time window matches your train/test split.

Key takeaways

• Load data with the right tool: CSV for small files, Parquet for repeated reads, SQL for selective queries.
• Always inspect with df.info(), df.describe(), and df.isna().sum() before writing any transformation.
• Convert datetime and categorical columns explicitly; treat type fixing as a separate, documented step.
• Remove duplicates by your unique identifier, not all columns; order matters when keeping the "first" or "last."
• GroupBy + agg is your feature factory; most ML models learn from aggregated statistics, not raw rows.
• In production, data cleaning is 60-70% of the work. Budget time and test your pipeline on fresh data regularly.