Pandas for Data Analysis: Beyond the Basics

Pandas is the most widely used data analysis library in Python. While many tutorials cover loading a CSV and viewing basic statistics, real-world data analysis requires more advanced operations: grouping, aggregating, merging datasets, reshaping with pivot tables, and working with time series. This article walks through each of these techniques with practical examples that you can adapt to your own datasets.

Grouping and Aggregating Data

The groupby operation splits your data into groups based on one or more columns, applies a function to each group independently, and combines the results. This is the SQL GROUP BY equivalent in Pandas. For example, to calculate total revenue and unique order count per region and product combination, you pass a list of grouping columns and a dictionary mapping output column names to aggregation functions.

import pandas as pd

# Load sales data
df = pd.read_csv("sales.csv")
print(df.head())
print(df.info())

# Group by region and product, aggregate multiple metrics
summary = df.groupby(["region", "product"]).agg(
    total_revenue=("revenue", "sum"),
    order_count=("order_id", "nunique"),
    avg_quantity=("quantity", "mean"),
    first_sale=("date", "min")
).reset_index()

print(summary.head(10))

The agg method accepts a dictionary where keys are new column names and values are tuples of (source_column, function). You can use any Pandas or NumPy function: sum, mean, nunique, min, max, std, or even custom lambda functions. The reset_index() call converts the grouped index back into regular columns, which is usually more convenient for further analysis. Without it, the grouping columns become part of a MultiIndex, which can be harder to work with.

Merging Datasets

Data often lives in multiple tables that need to be joined. Pandas provides merge() for SQL-style joins and concat() for stacking tables vertically or horizontally. The merge() function accepts how parameter values like inner, left, right, and outer, matching SQL JOIN semantics. Always specify the key columns explicitly with on, left_on, and right_on to avoid ambiguity.

# Load related tables
orders = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")
payments = pd.read_csv("payments.csv")

# Left join: all orders, with customer info where available
merged = pd.merge(orders, customers, on="customer_id", how="left")

# Inner join: only orders that have matching payments
paid_orders = pd.merge(orders, payments, on="order_id", how="inner")

# Merge on different column names
merged2 = pd.merge(orders, customers,
                   left_on="cust_id", right_on="id",
                   how="left")

# Concatenate monthly reports vertically
jan = pd.read_csv("sales_jan.csv")
feb = pd.read_csv("sales_feb.csv")
mar = pd.read_csv("sales_mar.csv")
q1 = pd.concat([jan, feb, mar], ignore_index=True)

When merging, watch out for many-to-many relationships — they produce Cartesian products that can explode your DataFrame size. Always inspect the shape before and after: print(len(orders), len(merged)). If the merged result is much larger than expected, you may have duplicate keys in one of the tables. Use validate='one_to_one' or validate='many_to_one' to raise an error if the relationship is not what you expect.

Pivot Tables

A pivot table reshapes data from a long format (one row per observation) to a wide format (one row per group, with columns for each category). This is the Pandas equivalent of Excel pivot tables and is invaluable for creating summary matrices, heatmaps, and cross-tabulations.

# Create a pivot table: regions as rows, quarters as columns
pivot = df.pivot_table(
    values="revenue",
    index="region",
    columns="quarter",
    aggfunc="sum",
    margins=True,
    fill_value=0
)

print(pivot)

# Multiple aggregation functions
pivot2 = df.pivot_table(
    values="revenue",
    index="region",
    columns="quarter",
    aggfunc=["sum", "mean", "count"],
    margins=True
)

# Cross-tabulation (frequency counts)
crosstab = pd.crosstab(df["region"], df["product_category"],
                       margins=True, normalize="index")
print(crosstab)

The margins=True parameter adds a “All” row and column with totals, similar to Excel’s Grand Total. fill_value=0 replaces missing combinations with zero instead of NaN. pd.crosstab is a specialized pivot table for frequency counts and is useful for understanding the distribution of categorical variables. Setting normalize='index' converts counts to percentages within each row, making it easy to compare category distributions across regions.

Time Series Analysis

Pandas has excellent support for time series data. Converting a date column to a DatetimeIndex enables powerful resampling, rolling windows, and time-based filtering. Always parse dates at load time with parse_dates=['date'] to avoid working with string columns.

# Parse dates and set as index
df = pd.read_csv("sales.csv", parse_dates=["date"])
df.index = pd.to_datetime(df["date"])

# Resample: aggregate by week
weekly = df.resample("W").agg({
    "revenue": "sum",
    "order_id": "nunique"
})

# Rolling average (4-week window)
weekly["revenue_ma4"] = weekly["revenue"].rolling(window=4).mean()

# Resample by month with multiple metrics
monthly = df.resample("ME").agg({
    "revenue": ["sum", "mean", "std"],
    "order_id": "nunique"
})

# Time-based filtering
q1_2026 = df["2026-01":"2026-03"]
last_30_days = df[df.index >= pd.Timestamp.now() - pd.DateOffset(days=30)]

# Shift for period-over-period comparison
weekly["revenue_prev"] = weekly["revenue"].shift(1)
weekly["change_pct"] = (weekly["revenue"] / weekly["revenue_prev"] - 1) * 100

print(weekly.head(10))

The resample method is like a time-based groupby. The "W" string stands for weekly (ISO weeks, ending Sunday). Other common aliases include "D" (daily), "ME" (month-end), "MS" (month-start), "QE" (quarter-end), and "YE" (year-end). The rolling() method creates a moving window for smoothing or computing trailing statistics — the window size is the number of periods, not a time duration. For irregularly sampled time series, use rolling(window=4, min_periods=2) to handle gaps gracefully.

Performance Tips

For large datasets (millions of rows), avoid iterative row-by-row operations. Use vectorized operations, avoid apply with slow functions, and prefer built-in aggregation methods. The query() method is faster than boolean indexing for complex filters. For very large data that does not fit in memory, consider dask.dataframe or polars as alternatives to Pandas.

# Fast filtering with query()
fast_filter = df.query("region == 'East' and revenue > 1000")

# Vectorized column creation
df["discounted_price"] = df["price"] * (1 - df["discount"])

# Avoid: df.apply(lambda row: row["price"] * (1 - row["discount"]), axis=1)
# Always prefer vectorized operations over apply

With these techniques — grouped aggregations, merges, pivot tables, and time series resampling — you can handle the vast majority of real-world data analysis tasks efficiently and expressively.