Python for Data Analysis: A Pandas Handbook

Why Pandas Is the Data Analyst's Core Python Tool

Pandas is the foundational Python library for data manipulation and analysis. Built on top of NumPy, it introduces the DataFrame — a two-dimensional labelled data structure that behaves like a spreadsheet or SQL table but with the full power of a programming language behind it. Almost every Python-based data workflow begins with Pandas: loading data, cleaning it, transforming it, aggregating it, and preparing it for visualisation or modelling.

This guide covers the Pandas operations that appear in real analytical work every day, with practical examples throughout.

Loading and Inspecting Data

import pandas as pd
import numpy as np

# Load from various sources
df_csv   = pd.read_csv("orders.csv", parse_dates=["order_date"])
df_excel = pd.read_excel("report.xlsx", sheet_name="Sales")
df_sql   = pd.read_sql("SELECT * FROM orders WHERE status = 'PAID'", con=engine)
df_json  = pd.read_json("events.json")

# Basic inspection
print(df_csv.shape)          # (rows, columns)
print(df_csv.dtypes)         # data types per column
print(df_csv.head())         # first 5 rows
print(df_csv.describe())     # stats for numeric columns
print(df_csv.info())         # non-null counts + dtypes

# Check nulls
print(df_csv.isnull().sum())

# Value counts for a column
print(df_csv["status"].value_counts(normalize=True).mul(100).round(1))

Selecting, Filtering, and Slicing

# Select columns
df["order_value"]                        # single column (Series)
df[["user_id", "order_value", "status"]] # multiple columns (DataFrame)

# Filter rows
df[df["order_value"] > 100]
df[(df["status"] == "PAID") & (df["order_value"] > 50)]
df[df["country"].isin(["US", "UK", "DE"])]
df[df["email"].str.contains("@gmail.com", na=False)]

# loc: label-based selection
df.loc[df["status"] == "PAID", ["user_id", "order_value"]]

# iloc: position-based selection
df.iloc[0:10, 0:4]   # first 10 rows, first 4 columns

# query: readable string syntax
df.query("order_value > 100 and status == 'PAID'")

Aggregation and GroupBy

GroupBy is the Pandas equivalent of SQL's GROUP BY — it splits the DataFrame into groups, applies a function to each, and combines the results.

# Simple aggregation
df.groupby("country")["order_value"].sum()
df.groupby("country")["order_value"].agg(["sum", "mean", "count"])

# Multiple columns in groupby
df.groupby(["country", "status"])["order_value"].sum().reset_index()

# Named aggregations (pandas >= 0.25)
summary = df.groupby("country").agg(
    total_revenue = ("order_value", "sum"),
    order_count   = ("order_id",   "nunique"),
    avg_order     = ("order_value", "mean"),
    first_order   = ("order_date",  "min"),
    last_order    = ("order_date",  "max"),
).reset_index()

# Transform: add group aggregate back to original rows
df["country_avg"] = df.groupby("country")["order_value"].transform("mean")
df["pct_of_country"] = df["order_value"] / df["country_avg"] * 100

Merging and Joining DataFrames

Pandas merge mirrors SQL joins and is one of the most frequently used operations in analytical pipelines.

orders    = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")
products  = pd.read_csv("products.csv")

# Inner join: only rows with matches in both tables
df = orders.merge(customers, on="user_id", how="inner")

# Left join: all orders, customer info where available
df = orders.merge(customers, on="user_id", how="left")

# Join on different column names
df = orders.merge(products, left_on="product_id", right_on="id", how="left")

# Chain multiple joins
df = (orders
      .merge(customers, on="user_id",    how="left")
      .merge(products,  on="product_id", how="left"))

# Check for join quality: rows lost/gained
print("Before:", len(orders))
print("After: ", len(df))
print("Unmatched:", df["customer_name"].isnull().sum())

Reshaping: Pivot Tables and Melt

Reshaping converts data between wide and long formats — essential for preparing data for visualisation libraries and for computing cross-tabulations.