Pandas for Real-World Data : Cleaning, Joins, GroupBy, and Time Series

Cleaning, Joins, GroupBy, and Time Series

A practical, code-first guide for data science and AI engineering · updated for pandas 3.0

Introduction

Real-world data rarely arrives ready for analysis or machine learning. It contains missing values, duplicate records, inconsistent text, wrong data types, and timestamps in different formats or time zones. Before an analytics or AI system can use it, the data must be inspected, cleaned, combined, summarized, and validated.

pandas is the standard Python library for labelled, tabular, and time-indexed data. Its core structures are the Series (one labelled column) and the DataFrame (a table of columns sharing an index). It builds on NumPy and, optionally, Apache Arrow.

All examples run on pandas 3.0 (released January 2026), which requires Python 3.11+. Install with python -m pip install "pandas>=3.0" pyarrow. PyArrow is optional but recommended: it powers fast string storage, Parquet I/O, and the pyarrow CSV engine. Two pandas 3.0 changes matter throughout this article:

Copy-on-Write is always on. Every indexing result behaves as a copy, SettingWithCopyWarning is gone, and chained assignment such as df["a"][mask] = 0 never modifies df. Assign in one step with df.loc[mask, "a"] = 0.

Text columns default to the `str` dtype instead of object, and missing text is NaN. Code that tests dtype == object to find text columns must change to pd.api.types.is_string_dtype().

1. Fundamentals: Load and Inspect

Inspect a dataset before changing it: its dimensions, column types, missing values, and unusual values tell you which cleaning rules are needed.

import numpy as np
import pandas as pd

students = pd.DataFrame({
    "student": ["Asha", "Ravi", "Mina"],
    "score": [82, 91, 76],
    "passed": [True, True, False],
})
print(students.shape)    # (3, 3)
print(students.dtypes)   # student: str (pandas 3), score: int64, passed: bool
df = pd.read_csv("orders.csv")
df.info()                    # info() prints itself and returns None
print(df.head())
print(df.describe(include="all"))
print(df.isna().sum())

Note that info() prints its report and returns None, so call it directly rather than inside print(). Avoid printing whole DataFrames; head(), sample(), and summaries are enough. For files that exceed memory, see Section 6.

2. Cleaning Real-World Data

2.1 Standardize column names and text

df = pd.DataFrame({
    " Customer ID ": [101, 102, 103],
    "Customer Name": ["  Asha Rao", "RAVI DAS ", "Mina Sen"],
    "City": ["Bhubaneswar", " CUTTACK ", "bhubaneswar"],
})
df.columns = (
    df.columns.str.strip()
    .str.lower()
    .str.replace(r"[^0-9a-z]+", "_", regex=True)
    .str.strip("_")
)
df["customer_name"] = df["customer_name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
print(df)

The regex replaces any run of non-alphanumeric characters (spaces, hyphens, %, /) with an underscore, which is more robust than replacing whitespace only. Do not title-case every field: product codes, emails, and identifiers have their own rules. Keep the raw value when the source must stay auditable.

2.2 Handle missing values

Missing data may appear as NaN, None, NaT, or pd.NA. Measure missingness first, then decide per field whether to drop, fill, or flag.

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "customer": ["Asha", "Ravi", None, "Mina"],
    "amount": [1200.0, np.nan, 800.0, 1500.0],
    "city": ["Cuttack", "Puri", "Cuttack", None],
})
print(orders.isna().sum())

# Flag BEFORE filling, so the information is not lost.
orders["amount_was_missing"] = orders["amount"].isna()
orders["customer_missing"] = orders["customer"].isna()

# In an ML pipeline, compute this on the training split only.
amount_median = orders["amount"].median()
orders["amount"] = orders["amount"].fillna(amount_median)
orders["city"] = orders["city"].fillna("Unknown")
print(orders)

Create missing-value indicators before filling; once a value is imputed, you can no longer tell it was missing, and the indicator is often a useful model feature. Drop rows only when the loss is small and the missingness is not systematic. For ML, compute fill values on the training split and reuse them for validation, test, and inference (for example with scikit-learn's SimpleImputer inside a Pipeline) to prevent leakage.

2.3 Remove duplicates and validate business keys

data = pd.DataFrame({
    "order_id": [1001, 1002, 1002, 1003],
    "customer_id": [10, 11, 11, 12],
    "amount": [500, 700, 700, -50],
})
print(data.duplicated().sum())                 # exact duplicate rows: 1
data = data.drop_duplicates()

# Business-key check: any order_id still repeated has conflicting values.
conflicts = data[data.duplicated(subset=["order_id"], keep=False)]
assert conflicts.empty, f"{len(conflicts)} conflicting rows for order_id"

invalid_amounts = data[data["amount"] < 0]     # route to review, don't delete
print(invalid_amounts)

An exact duplicate row is not the same as a duplicate business event. After removing exact copies, any order_id that still repeats means two different versions of the same order, and that should stop the pipeline rather than be silently resolved. If a rule is agreed (for example, keep the latest version), apply it explicitly with sort_values("updated_at").drop_duplicates("order_id", keep="last").

2.4 Convert data types safely

df = pd.DataFrame({
    "amount": ["1200.50", "850", "unknown"],
    "order_date": ["2026-01-05", "2026-01-07", "not-a-date"],
})
amount = pd.to_numeric(df["amount"], errors="coerce")
order_date = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")

# Measure what coercion turned into missing values.
bad = (amount.isna() & df["amount"].notna()) | (order_date.isna() & df["order_date"].notna())
print(f"{bad.sum()} rows failed parsing")
rejected = df[bad]

df["amount"], df["order_date"] = amount, order_date
df["quantity"] = pd.array([2, None, 1], dtype="Int64")   # nullable integer
print(df.dtypes)

With errors="coerce", unparseable values become missing, so always count and log them. Pass an explicit format= (or format="ISO8601") to to_datetime: without one, pandas infers the format from the first value, and rows in any other format silently become NaT. In pandas 3.0, parsed datetimes default to microsecond resolution (datetime64[us]) rather than nanoseconds. Use nullable types such as Int64 and boolean when integer or boolean columns must hold missing values.

2.5 Filter and derive columns

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "amount": [500, 1500, 2500, 900],
    "status": ["paid", "pending", "paid", "cancelled"],
})
paid_high_value = orders.loc[
    orders["status"].eq("paid") & orders["amount"].ge(1000)
]
orders["amount_band"] = pd.cut(
    orders["amount"],
    bins=[-np.inf, 1000, 2000, np.inf],
    labels=["low", "medium", "high"],
    right=False,               # [0, 1000) low, [1000, 2000) medium, >= 2000 high
)
print(paid_high_value)
print(orders)
# Never chain: orders["amount"][mask] = 0 silently does nothing in pandas 3.
orders.loc[orders["status"].eq("cancelled"), "amount"] = 0

Use .loc for explicit selection and assignment. Under Copy-on-Write, the old .copy() after filtering is no longer needed to avoid warnings; edits to paid_high_value never affect orders. pd.cut is clearer than chained conditions for banding and returns an ordered categorical; right=False makes each band include its lower edge.

3. Combining Data: Concatenation and Joins

3.1 Concatenation: stacking similar datasets

jan = pd.DataFrame({"order_id": [1, 2], "amount": [500, 700]})
feb = pd.DataFrame({"order_id": [3, 4], "amount": [900, 600]})
all_orders = pd.concat(
    [jan.assign(batch="2026-01"), feb.assign(batch="2026-02")],
    ignore_index=True,          # fresh 0..n-1 index; keep lineage in a column
)
print(all_orders)
pd.concat() stacks records; it does not match keys. Columns are aligned by name and any column missing from one input is filled with missing values, which can quietly change dtypes (integers become floats). Check that inputs share a schema before stacking, and record lineage in a column.

3.2 Merge on keys and validate cardinality

A merge is a SQL-style join. The most common production bug is a lookup table whose key is not actually unique: a many-to-many match multiplies rows and inflates totals. Use validate= to assert the expected relationship and indicator=True to see unmatched rows.

customers = pd.DataFrame({
    "customer_id": [10, 11, 12],
    "customer_name": ["Asha", "Ravi", "Mina"],
    "city": ["Cuttack", "Puri", "Bhubaneswar"],
})
orders = pd.DataFrame({
    "order_id": [501, 502, 503, 504],
    "customer_id": [10, 11, 10, 99],
    "amount": [1200, 800, 500, 300],
})
enriched = orders.merge(
    customers, on="customer_id", how="left",
    validate="many_to_one",    # raises MergeError if customer_id repeats in customers
    indicator=True,
)
print(enriched["_merge"].value_counts())
unmatched = enriched[enriched["_merge"].eq("left_only")]
assert len(enriched) == len(orders)          # a left m:1 join must not add rows
print(unmatched)

validate="many_to_one" raises MergeError if the right-hand key repeats. The other options are one_to_one and one_to_many; many_to_many performs no check at all. Validation checks key uniqueness only; it does not check that every key matched, which is why the indicator and row-count assertion are also needed. Customer 99 has no profile and stays visible as left_only.

3.3 Differently named keys and overlapping columns

orders = pd.DataFrame({"customer_id": [10, 11], "amount": [1200, 800]})
profiles = pd.DataFrame({"id": [10, 11], "segment": ["premium", "standard"]})
result = (
    orders.merge(profiles, left_on="customer_id", right_on="id",
                 how="left", validate="many_to_one")
    .drop(columns="id")        # both key columns are kept; drop the duplicate
)
print(result)

When key names differ, both key columns are kept, so drop one. Use left_index=True/right_index=True to join on an index, and suffixes=("_order", "_customer") instead of the default _x/_y when non-key columns share a name.

4. GroupBy: Summarizing Data

GroupBy follows split–apply–combine: split rows into groups, apply an aggregation or transformation, and combine the results. It drives sales summaries, customer metrics, and feature generation.

4.1 Aggregation with named outputs

sales = pd.DataFrame({
    "city": ["Cuttack", "Puri", "Cuttack", "Puri", "Cuttack"],
    "product": ["A", "A", "B", "B", "A"],
    "revenue": [1000, 800, 1500, 1200, 700],
    "quantity": [2, 1, 3, 2, 1],
})
summary = (
    sales.groupby("city", as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        avg_revenue_per_line=("revenue", "mean"),
        line_count=("revenue", "size"),
        units_sold=("quantity", "sum"),
    )
)
city_product = sales.groupby(["city", "product"], as_index=False).agg(
    revenue=("revenue", "sum"), units=("quantity", "sum")
)
print(summary)
print(city_product)

Named aggregation (new_name=(column, func)) produces clean, flat column names. Name metrics for what they measure: each row here is a sales line, so the mean is revenue per line, not per order. as_index=False keeps grouping keys as ordinary columns.

4.2 Transform, filter, and missing keys

Aggregation returns one row per group; transform() returns a result aligned to the original rows, which is ideal for group-level features.

sales = pd.DataFrame({
    "city": ["Cuttack", "Puri", "Cuttack", "Puri", "Cuttack"],
    "revenue": [1000, 800, 1500, 1200, 700],
})
city_total = sales.groupby("city")["revenue"].transform("sum")
sales["share_of_city"] = sales["revenue"] / city_total

# Vectorized group filter (avoids a Python lambda per group).
large_cities = sales[city_total >= 2000]
summary_with_na = sales.groupby("city", dropna=False)["revenue"].sum()
print(sales, large_cities, summary_with_na, sep="\n")

Reusing the transformed total as a boolean mask filters whole groups without calling a Python function per group, as groupby(...).filter(lambda g: ...) does. GroupBy drops rows whose key is missing by default (dropna=True); set dropna=False whenever those rows still carry revenue or counts you must report. Prefer built-in aggregations (sum, mean, size, count, nunique) over custom Python functions.

5. Time-Series Data

5.1 Parse, index, and slice

events = pd.DataFrame({
    "timestamp": ["2026-01-01 09:00", "2026-01-01 10:00",
                  "2026-01-02 09:00", "2026-01-02 10:00"],
    "requests": [120, 150, 180, 160],
})
events["timestamp"] = pd.to_datetime(events["timestamp"], format="%Y-%m-%d %H:%M")
events = events.set_index("timestamp").sort_index()
assert events.index.is_unique

jan_1 = events.loc["2026-01-01"]                    # partial-string selection
first_two_days = events.loc["2026-01-01":"2026-01-02"]  # inclusive range
events["hour"] = events.index.hour
events["weekday"] = events.index.day_name()
print(jan_1, events, sep="\n")

Sort the index and check it for duplicates before slicing or resampling. Partial-string selection such as loc["2026-01-01"] returns every row on that day, and string ranges include both endpoints. Datetime columns expose the same components through .dt (for example .dt.month, .dt.dayofweek).

5.2 Resampling

hits = pd.DataFrame({
    "timestamp": pd.to_datetime([
        "2026-01-01 09:05", "2026-01-01 09:40",
        "2026-01-01 10:10", "2026-01-01 10:45",
    ]),
    "requests": [100, 120, 140, 160],
}).set_index("timestamp")
hourly = hits["requests"].resample("h").agg(["sum", "mean", "count"])
print(hourly)

Resampling is grouping by time buckets. Common aliases in current pandas are min, h, D, W, MS/ME (month start/end), and QS/QE; the old H, T, M, Q, and Y aliases were removed in pandas 3.0 and now raise ValueError. Empty buckets return 0 for sum but NaN for mean. Choose the aggregation that matches the question: sum counts, average measurements.

5.3 Lag and rolling features without leakage

daily = pd.DataFrame(
    {"sales": [10, 12, 9, 15, 18, 14, 20]},
    index=pd.date_range("2026-01-01", periods=7, freq="D", name="date"),
)
# Features for predicting day t may only use data up to t-1.
past = daily["sales"].shift(1)
daily["lag_1"] = past
daily["rolling_3d_mean"] = past.rolling("3D", min_periods=3).mean()
print(daily)

A rolling window includes the current row. If the target is today's sales, a rolling mean that includes today leaks the answer, so shift first and roll on the shifted series. A time-based window ("3D") respects calendar gaps, whereas window=3 counts rows. Requiring min_periods equal to the window avoids features computed from a partial window. Never use center=True for forecasting features, and split train and test by time, not at random.

5.4 Time zones and daylight saving

utc = pd.to_datetime(
    pd.Series(["2026-01-01T04:30:00Z", "2026-01-01T08:00:00+05:30"]),
    utc=True,                                  # normalise mixed offsets to UTC
)
ist = utc.dt.tz_convert("Asia/Kolkata")        # same instants, IST display

# Naive local times from a US source: localise, handling DST edge cases.
naive = pd.Series(pd.to_datetime(["2026-03-08 02:30", "2026-11-01 01:30"]))
ny = naive.dt.tz_localize(
    "America/New_York",
    nonexistent="shift_forward",   # 02:30 on spring-forward day does not exist
    ambiguous="NaT",               # 01:30 on fall-back day occurs twice
)
print(ist, ny, sep="\n")

Store event instants in UTC and convert only for presentation. Localization (tz_localize) attaches a zone to a naive time; conversion (tz_convert) changes the displayed zone while preserving the instant. Do not assume naive timestamps are UTC; confirm what the source intended. India has no daylight saving time, but data from many other regions has local times that are skipped or repeated, so decide explicitly how to handle them.

6. End-to-End Example

This workflow cleans orders, joins a customer lookup with checks, computes customer metrics, and summarizes daily revenue. It includes an exact duplicate, inconsistent status text, and a customer with no lookup record.

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 4, 5],
    "customer_id": [10, 11, 10, 12, 12, 99],
    "order_date": ["2026-01-01", "2026-01-01", "2026-01-02",
                   "2026-01-02", "2026-01-02", "2026-01-03"],
    "amount": ["1200", "800", "500", "1500", "1500", "300"],
    "status": [" Paid", "paid", "PAID", "pending", "pending", "paid"],
})
customers = pd.DataFrame({
    "customer_id": [10, 11, 12],
    "city": ["Cuttack", "Puri", "Bhubaneswar"],
    "segment": ["premium", "standard", "premium"],
})

# 1. Normalise types and text FIRST, so equal values compare equal.
orders = orders.assign(
    order_date=pd.to_datetime(orders["order_date"], format="%Y-%m-%d", errors="coerce"),
    amount=pd.to_numeric(orders["amount"], errors="coerce"),
    status=orders["status"].str.strip().str.lower(),
)

# 2. Deduplicate, then enforce the business key.
orders = orders.drop_duplicates()
if orders["order_id"].duplicated().any():
    raise ValueError("order_id is not unique after removing exact duplicates")

# 3. Split valid paid orders from rejects (keep rejects for logging).
valid = orders["order_date"].notna() & orders["amount"].notna() & orders["amount"].ge(0)
rejected = orders[~valid]
paid = orders[valid & orders["status"].eq("paid")]

# 4. Enrich with a cardinality check and make unmatched keys visible.
enriched = paid.merge(customers, on="customer_id", how="left",
                      validate="many_to_one", indicator=True)
n_unmatched = enriched["_merge"].eq("left_only").sum()
print(f"rejected={len(rejected)} unmatched_customers={n_unmatched}")
enriched = enriched.drop(columns="_merge")

# 5. Customer metrics. dropna=False keeps orders whose lookup failed;
#    the default (dropna=True) would silently drop their revenue.
customer_metrics = (
    enriched.groupby(["customer_id", "city", "segment"], as_index=False, dropna=False)
    .agg(order_count=("order_id", "nunique"),
         total_spend=("amount", "sum"),
         average_order=("amount", "mean"))
)

# 6. Daily revenue; days with no orders appear as 0.
daily_revenue = enriched.set_index("order_date")["amount"].resample("D").sum()

assert customer_metrics["total_spend"].sum() == paid["amount"].sum()
print(customer_metrics)
print(daily_revenue)

Two ordering decisions matter. Types and text are normalized before deduplication, so " Paid" and "paid" compare equal. And dropna=False in the groupby keeps customer 99: with the default, its 300 in revenue would disappear from the metrics without any error, which the final reconciliation assert would catch. In production, also enforce a schema (for example with Pandera), log rejected rows with reasons, store money as integer minor units or Decimal where rounding matters, and cover each rule with tests.

7. Performance and Production Practices

Load only what you need: usecols=, explicit dtype=, and engine="pyarrow" for faster multithreaded CSV parsing.

Prefer Parquet for repeated workloads: it preserves types and supports column selection and filter pushdown (pd.read_parquet(path, columns=..., filters=...)).

Filter early, before expensive joins and aggregations.

Use vectorized string, datetime, and numeric operations instead of apply(axis=1) or loops.

Use category for repeated low-cardinality text to reduce memory and speed up groupby.

Assert row counts and key uniqueness around every join, and reconcile totals after aggregation.

For data larger than memory, use chunksize= or push work into a database or an out-of-core engine such as DuckDB, Polars, or Spark.

Make pipelines reproducible: pin the pandas version, version transformation code, and test edge cases (empty input, all-missing columns, DST days).

8. Common Pitfalls

Conclusion

Cleaning makes source data trustworthy, joins bring related information together, GroupBy turns records into metrics, and time-series tools reveal patterns over time. Used with explicit validation, these are the building blocks of reliable pipelines. For AI engineers, the goal is not a DataFrame that looks right but transformations that are explainable, reproducible, validated, and safe to reuse in training and production inference.

References

pandas documentation: https://pandas.pydata.org/docs/

What's new in pandas 3.0.0: https://pandas.pydata.org/docs/whatsnew/v3.0.0.html

Copy-on-Write: https://pandas.pydata.org/docs/user_guide/copy_on_write.html

Merge, join, and concatenate: https://pandas.pydata.org/docs/user_guide/merging.html

GroupBy: https://pandas.pydata.org/docs/user_guide/groupby.html

Time series: https://pandas.pydata.org/docs/user_guide/timeseries.html

Scaling to large datasets: https://pandas.pydata.org/docs/user_guide/scale.html

Back to all blogs