Rebuild Your SQL Report in Python with pandas

Last updated: August 2026

We are going to take one real analyst report, a monthly revenue summary by region built from a SQL JOIN plus GROUP BY, and rebuild it end to end in Python with pandas. By the end you will have a script that loads two CSV files, joins them, aggregates revenue by region and month, sorts the result, and writes a clean report to disk. Every SQL snippet is paired with the pandas line that produces the same rows, so you can check your output against the query you already trust. The proof it works: run the final script and the eight orders collapse into one row per region per month, with revenue totals you can reconcile against the SQL query by eye. Nothing here needs a database, a cloud account, or more than a few minutes.

What you need before starting

• Python 3.11 or newer installed, with pip working from your terminal
• pandas installed: run pip install pandas once
• Comfort writing SELECT, JOIN, and GROUP BY in SQL (this guide assumes that, not Python)
• Two sample CSV files, orders.csv and regions.csv (we create them in Step 1 so you can run everything as-is)
• A text editor or Jupyter notebook to run the code

Step 1: Create the sample data

So the whole tutorial runs without a database, we will generate the two tables as CSV files first. In SQL these would be tables you already have; here they are flat files. Save this as make_data.py and run it once.

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "region_id": [1, 1, 2, 2, 3, 1, 3, 2],
    "order_date": [
        "2026-01-04", "2026-01-18", "2026-01-22", "2026-02-03",
        "2026-02-11", "2026-02-19", "2026-03-02", "2026-03-15",
    ],
    "amount": [120.0, 80.0, 200.0, 50.0, 300.0, 90.0, 150.0, 60.0],
})

regions = pd.DataFrame({
    "region_id": [1, 2, 3],
    "region_name": ["North", "South", "West"],
})

orders.to_csv("orders.csv", index=False)
regions.to_csv("regions.csv", index=False)
print("Wrote orders.csv and regions.csv")

Running python make_data.py should print one line and drop two files in your folder.

Checkpoint: orders.csv exists and opening it shows 8 rows with an amount column.

Step 2: Load the tables into DataFrames

In SQL the data already lives in the engine, typed and ready. In pandas you load each table into a DataFrame, which is the in-memory equivalent of a result set you can keep poking at without re-running anything. read_csv parses the file into a DataFrame; passing parse_dates tells pandas to read that column as real timestamps instead of plain text, which matters the moment we extract a month in Step 4. Loading both tables up front mirrors how a SQL query has every table available before the JOIN runs.

-- The SQL equivalent is just having the tables available:
SELECT * FROM orders;
SELECT * FROM regions;

import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
regions = pd.read_csv("regions.csv")

print(orders.head())
print(orders.dtypes)

The dtypes line should show order_date as a datetime64 type, not object. If it shows object, the parse did not happen and the month extraction later will fail.

Checkpoint: print(orders.head()) shows 5 rows and order_date is a real datetime, not a string.

Step 3: Join orders to regions

The report needs the region name, which lives in the regions table, so we join on region_id. In pandas the join verb is merge, and how="left" keeps every order even if a region were missing, the same behavior as a SQL LEFT JOIN. The official pandas docs map these verbs one to one on their Comparison with SQL page, which is worth bookmarking.

SELECT o.order_id, o.order_date, o.amount, r.region_name
FROM orders o
LEFT JOIN regions r ON o.region_id = r.region_id;

joined = orders.merge(regions, on="region_id", how="left")

print(joined[["order_id", "order_date", "amount", "region_name"]].head())

If your join keys had different names, you would write left_on and right_on instead of on, exactly like naming both sides of an ON clause.

Checkpoint: joined has the same row count as orders (8) and now carries a region_name column with no nulls.

Step 4: Add a month column to group on

The SQL report groups by month, so we need a month value to group on. In SQL you would call DATE_TRUNC or strftime. In pandas, because order_date is already a datetime, you can pull a monthly period straight off the .dt accessor. This is the kind of step that has no clean one-liner in standard SQL and shows why analysts reach for pandas.

-- Postgres style: truncate the date down to the month
SELECT order_id, DATE_TRUNC('month', order_date) AS order_month, amount
FROM orders;

joined["order_month"] = joined["order_date"].dt.to_period("M").astype(str)

print(joined[["order_id", "order_month", "amount"]].head())

The order_month column should read like 2026-01, 2026-02, and so on. Casting to str keeps it readable in the final CSV instead of printing a pandas Period object.

Checkpoint: every row has an order_month like 2026-01, and there are three distinct months in the data.

Step 5: Group by region and month, then aggregate

This is the heart of the report: total revenue and order count per region per month. The pandas pattern is split-apply-combine, documented in the Group by user guide. We group on two keys, then name each output column with a tuple of (source_column, function), which is the modern, readable way to write SUM(amount) AS revenue and COUNT(*) AS order_count at once.

SELECT r.region_name, DATE_TRUNC('month', o.order_date) AS order_month,
       SUM(o.amount) AS revenue,
       COUNT(*)      AS order_count
FROM orders o
LEFT JOIN regions r ON o.region_id = r.region_id
GROUP BY r.region_name, order_month;

report = (
    joined
    .groupby(["region_name", "order_month"])
    .agg(
        revenue=("amount", "sum"),
        order_count=("order_id", "count"),
    )
    .reset_index()
)

print(report)

The .reset_index() matters more than it looks: groupby puts the grouping keys into the DataFrame index rather than leaving them as ordinary columns, and resetting turns them back into plain columns so the output reads like a SQL result set. Skip it and your region_name and order_month vanish from the columns and become an index you have to fight later. Your report should have one row per region-and-month combination that actually has orders, the same rows a GROUP BY would return. Note one real difference from SQL: pandas, by default, only produces rows for combinations that exist in the data, so empty months simply do not appear, which is usually what an analyst wants.

Checkpoint: report has columns region_name, order_month, revenue, order_count, and the revenue values are positive floats.

Step 6: Sort and write the final report

Last, we order the rows and write the report to disk, the equivalent of ORDER BY plus an export step. Sorting by region then month gives a readable report; descending revenue would answer a different question. to_csv with index=False keeps the row numbers out of the file.

-- ...same query as Step 5, with:
ORDER BY region_name, order_month;

report = report.sort_values(["region_name", "order_month"]).reset_index(drop=True)

report.to_csv("revenue_by_region.csv", index=False)
print(report)
print("Wrote revenue_by_region.csv")

Open revenue_by_region.csv and you have the same report your SQL query produces, now in a script you can re-run on next month's data, parameterize by date range, or extend with charts, rolling averages, and forecasts that SQL alone cannot reach without contortion. That last part is the real payoff of the migration: the report you already trust becomes a starting point rather than a dead end, and everything downstream stays in one language.

Checkpoint: revenue_by_region.csv exists, rows are ordered by region then month, and the totals match what your SQL query returns.

Where this breaks

The join silently drops or duplicates rows. In SQL a bad join key blows up loudly or returns nulls. In pandas a merge on a key that is not unique on the right side will fan out your rows, inflating every total. Before you trust a merge, check regions["region_id"].is_unique and compare len(joined) to len(orders). If the counts differ, your key is not what you think it is. This is the single most common bug I watch analysts hit in their first week of pandas.

groupby hides your keys in the index. Forget .reset_index() and your "columns" are actually a MultiIndex, so the next merge or to_csv behaves strangely and you cannot reference region_name as a column. When something downstream cannot find a column you know exists, print report.columns and report.index first. Nine times out of ten the value is sitting in the index.

Dates that look right are actually strings. If you skip parse_dates, order_date comes in as text, .dt.to_period raises an error, and string sorting puts 2026-10 before 2026-2. Always check df.dtypes right after loading. SQL hands you typed columns for free; pandas makes you ask.

NaN is not NULL and does not compare like it. A missing region in a LEFT JOIN becomes NaN in pandas, and NaN == NaN is False, so filtering with == quietly misses those rows. Use .isna() to find them and .fillna() to handle them, rather than the equality checks you would write in SQL.

If you have decided pandas is your next step but want the broader plan, our Python for Adults guide lays out the full path for working professionals, including the analyst-specific 90-day sequence. If you have been leaning on AI to learn, can ChatGPT teach me Python is an honest look at where that helps and where it stalls.

If you want to build this with someone watching your screen and catching the join-fan-out and index bugs in real time, that is literally what my sessions are. Book a free Discovery Call and bring one of your own SQL reports to it.

These tutorials come from the actual curriculum I teach 1-on-1. Every code block is tested before it ships.