PART 6 · PYTHON FOR DATA ENGINEERING · CHAPTER 30

pandas essentials, mapped to SQL

FreeLesson 67 of 78

If you know SQL, you already know what pandas does. The work is learning which method maps to which clause.

READ, FILTER, GROUP, AGGREGATEPYTHON 3.12

import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
print(orders.shape)
print(orders.dtypes.to_string())

monthly = (orders
    .query("status == 'delivered'")              # WHERE
    .assign(month=lambda d: d.order_date.dt.to_period("M"))
    .groupby("month", as_index=False)             # GROUP BY
    .agg(orders=("order_id", "count"),
         revenue=("amount", "sum"),
         avg=("amount", "mean"))
    .round(1))
print(monthly.to_string(index=False))

Output

(15, 5)
order_id                int64
customer_id             int64
order_date     datetime64[us]
status                    str
amount                float64
  month  orders  revenue    avg
2025-01       2   1994.0  997.0
2025-02       2   2847.0 1423.5
2025-03       4   7291.0 1822.8
2025-04       4   6840.0 1710.0

MERGE AND PIVOTPYTHON 3.12

import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items = pd.read_csv("order_items.csv")
products = pd.read_csv("products.csv")

lines = (items
    .merge(products, on="product_id", how="left",
           validate="many_to_one")              # JOIN + a check
    .merge(orders[["order_id", "order_date", "status"]],
           on="order_id")
    .query("status == 'delivered'")
    .assign(revenue=lambda d: d.qty * d.unit_price,
            month=lambda d: d.order_date.dt.month))

print(lines.pivot_table(index="category", columns="month",
                        values="revenue", aggfunc="sum",
                        fill_value=0))

Output

month          1     2     3     4
category
Electronics  799  1499  5096  4396
Home         399  1199  1199  1997
Stationery   796   149   996   447

SQL	pandas	Note
`WHERE status = 'delivered'`	`df.query("status == 'delivered'")` or `df[df.status == "delivered"]`	Boolean mask under the hood
`GROUP BY ... SUM()`	`df.groupby(k).agg(total=("amount", "sum"))`	NaN keys dropped by default (page 70)
`JOIN ... ON`	`a.merge(b, on=k, how="left")`	how = inner / left / right / outer
`SUM() OVER (PARTITION BY k)`	`df.groupby(k)["amount"].transform("sum")`	transform keeps every row
`LAG(x) OVER (...)`	`df.groupby(k)["x"].shift(1)`	Sort first
`ORDER BY ... LIMIT n`	`df.sort_values(c).head(n)` / `df.nlargest(n, c)`

Practice before you move on

With pandas, compute the delivered revenue per customer and sort it from highest to lowest.
Write the SQL query that gives the same result next to your pandas code.

Watch the free lesson

The same topic in the Free Lessons library, free with a sign-in.