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.

Data Classes and Pydantic for Robust Applications

Python’s dataclasses (introduced in Python 3.7) and Pydantic (a third-party library) both reduce boilerplate code when defining data containers, but they serve different purposes. Dataclasses auto-generate __init__, __repr__, __eq__, and __hash__ methods based on class annotations. Pydantic goes further by adding runtime type validation, serialization, and parsing — making it the backbone of FastAPI and the go-to choice for any application that handles external data.

Python Dataclasses

Dataclasses are ideal for internal data structures where you want concise, readable code without writing boilerplate. The @dataclass decorator inspects the class’s type annotations and automatically generates the standard dunder methods. You can control behavior with parameters like frozen=True (immutable instances), order=True (sortable), and slots=True (Python 3.10+, memory efficient).

from dataclasses import dataclass, field

@dataclass(frozen=True, order=True)
class Point:
    x: float
    y: float
    label: str = field(default="", compare=False)

p1 = Point(1.0, 2.0, "start")
p2 = Point(1.0, 2.0, "end")
print(p1)          # Point(x=1.0, y=2.0, label='start')
print(p1 == p2)    # True (label excluded from comparison)
# p1.x = 3.0       # Error: frozen instance

Pydantic BaseModel

Pydantic’s BaseModel adds validation and serialization on top of the type annotation system. When you create an instance, Pydantic validates every field against its type annotation — coercing values when safe (e.g., "123" becomes 123 for an int field) and raising a detailed validation error when coercion is not possible. This catches data quality issues at the boundary of your application rather than deep in your logic.

from pydantic import BaseModel, EmailStr, Field
from datetime import datetime

class User(BaseModel):
    id: int
    name: str = Field(..., min_length=1, max_length=100)
    email: EmailStr
    age: int = Field(ge=0, le=150, default=0)
    created_at: datetime = Field(default_factory=datetime.now)
    tags: list[str] = Field(default_factory=list)

# Valid data — works
user = User(id=1, name="Alice", email="alice@example.com", age=30, tags=["admin"])
print(user.model_dump_json(indent=2))

# Invalid data — raises ValidationError with clear message
try:
    User(id="not-a-number", name="", email="not-an-email", age=200)
except Exception as e:
    print(e)
    # Shows all validation errors at once, not just the first one

The Field() function lets you add constraints (min_length, ge for greater-or-equal, le for less-or-equal), default values, and metadata. Pydantic V2, powered by a Rust core (pydantic-core), is significantly faster than V1 and supports complex types like UUID, IPv4Address, Decimal, and Path out of the box.

Nested Models and Validation

Pydantic models can be nested to represent complex hierarchies. Each nested model is validated recursively. You can also define custom validators using @field_validator and @model_validator decorators to enforce business rules that go beyond simple type checks.

from pydantic import BaseModel, field_validator

class Address(BaseModel):
    street: str
    city: str
    zip_code: str

class Employee(BaseModel):
    name: str
    address: Address
    salary: float

    @field_validator('salary')
    @classmethod
    def salary_must_be_positive(cls, v):
        if v <= 0:
            raise ValueError('Salary must be positive')
        return v

emp = Employee(
    name="Bob",
    address={"street": "123 Main St", "city": "NYC", "zip_code": "10001"},
    salary=75000.0
)
print(emp.model_dump())
# {'name': 'Bob', 'address': {'street': '123 Main St', ...}, 'salary': 75000.0}

Serialization and Parsing

Pydantic makes it easy to convert between Python objects and JSON/dict representations. This is essential for API development: parse incoming request bodies with model_validate() and serialize responses with model_dump() or model_dump_json(). The model_config lets you control serialization behavior like excluding unset values, using aliases, or populating by name vs by position.

# Parse from JSON string
json_data = '{"name": "Charlie", "email": "charlie@example.com", "age": 25}'
user = User.model_validate_json(json_data)

# Parse from dict
user = User.model_validate(dict(name="Diana", email="diana@example.com", age=28))

# Serialize to dict (exclude unset fields)
data = user.model_dump(exclude_unset=True)

# Serialize to JSON with formatting
print(user.model_dump_json(indent=2))
Settings Management with Pydantic
Pydantic's BaseSettings (from pydantic-settings) extends BaseModel to read configuration from environment variables, .env files, and secret stores. This is the recommended way to manage application configuration in Python. Define a settings class with typed fields and default values, and Pydantic automatically reads from the environment — validating types, providing clear error messages for missing required values, and supporting nested settings for complex configurations.
from pydantic_settings import BaseSettings

class Settings(BaseSettings):
    database_url: str
    redis_url: str = 'redis://localhost:6379'
    debug: bool = False
    api_key: str
    max_connections: int = 10

    class Config:
        env_file = '.env'
        env_file_encoding = 'utf-8'

settings = Settings()
# Reads DATABASE_URL, REDIS_URL, API_KEY from environment or .env file

This pattern eliminates the need for ad-hoc environment variable parsing, type coercion bugs, and inconsistent configuration handling across your application. When combined with FastAPI, Pydantic provides end-to-end type safety from the HTTP request boundary through your application logic to the response serialization.
Use dataclasses for simple internal data holding in your own codebase where validation is not critical. Use Pydantic whenever data enters or leaves your application — API requests/responses, configuration files, database records — because the validation layer prevents corrupted data from propagating through your system.
Advanced Pydantic Features
Pydantic V2, rewritten in Rust with pydantic-core, is significantly faster than V1. Advanced features include: discriminated unions for parsing polymorphic JSON, strict mode (no implicit string-to-int coercion), computed fields, model validators with before/after/wrap modes, and serialization aliases. Pydantic's integration with FastAPI is seamless: request bodies and response models automatically validate and serialize. BaseModel.model_dump() and model_validate() replace V1 methods. Pydantic supports JSON Schema generation for OpenAPI documentation. For enterprise applications, StrictBool, PaymentCardNumber, and EmailStr provide domain-specific validation out of the box.