You open a pandas tutorial and the first thing you see is df.groupby(...).agg(...), then something called .loc, then a reset_index() that nobody explained.
And you think, "I already know how to do this. I just know it in SQL."
Good news: you do. Most of pandas is SQL wearing different clothes. Once you see the mapping, you stop memorising pandas and start translating.
So let's do exactly that. Same small data, SQL on the left, pandas on the right, and the real output. At the end, we'll look at the four places where they behave differently, because that's where beginners lose marks.
I ran everything below with Python 3.14, pandas 3.0.2 and SQLite 3.53. The SQL results come from SQLite and the pandas results come from pandas, so when I say they match, I actually compared them.
The data
Two tables: customers and orders.
import pandas as pd
import numpy as np
customers = pd.DataFrame({
"id": [1, 2, 3, 4],
"name": ["Asha", "Ravi", "Meera", "Kiran"],
"city": ["Pune", "Delhi", "Pune", "Mumbai"],
})
orders = pd.DataFrame({
"order_id": [101, 102, 103, 104, 105, 106],
"customer_id": [1, 1, 2, 3, 3, 5],
"amount": [500, 1200, 300, 800, None, 150],
"status": ["paid", "paid", "refunded", "paid", "pending", "paid"],
})
order_id customer_id amount status
101 1 500.0 paid
102 1 1200.0 paid
103 2 300.0 refunded
104 3 800.0 paid
105 3 NaN pending
106 5 150.0 paid
Two things are planted on purpose. Order 105 has a missing amount. Order 106 belongs to customer 5, who doesn't exist in customers. Real data is always like this.
(If you want to run the SQL side too, orders.to_sql("orders", conn) with a sqlite3 connection loads these tables in one line.)
1. SELECT columns
SELECT order_id, amount FROM orders;
orders[["order_id", "amount"]]
Two square brackets. The outer one is "get from the DataFrame", the inner one is a list of column names. If you write one bracket with one name, you get a Series (a single column) instead of a table.
2. WHERE
SELECT * FROM orders WHERE status = 'paid' AND amount >= 500;
orders[(orders["status"] == "paid") & (orders["amount"] >= 500)]
Output, same for both:
order_id customer_id amount status
101 1 500.0 paid
102 1 1200.0 paid
104 3 800.0 paid
Three changes to remember:
-
=becomes== -
AND/ORbecome&/| - every condition goes inside its own parentheses
That last one is not optional. Try it without the brackets:
orders[orders["status"] == "paid" & orders["amount"] >= 500]
TypeError: Cannot perform 'rand_' with a dtyped [float64] array and scalar of type [bool]
Python evaluates & before ==, so it tries to AND a string with a column. The error message makes no sense until you know why. Now you know.
3. ORDER BY and LIMIT
SELECT * FROM orders ORDER BY amount DESC LIMIT 3;
orders.sort_values("amount", ascending=False).head(3)
order_id customer_id amount status
102 1 1200.0 paid
104 3 800.0 paid
101 1 500.0 paid
4. GROUP BY
SELECT status,
COUNT(*) AS n,
COUNT(amount) AS n_amount,
SUM(amount) AS total,
AVG(amount) AS avg_amt
FROM orders
GROUP BY status;
orders.groupby("status").agg(
n=("order_id", "size"),
n_amount=("amount", "count"),
total=("amount", "sum"),
avg_amt=("amount", "mean"),
).reset_index()
Output from pandas:
status n n_amount total avg_amt
paid 4 4 2650.0 662.5
pending 1 0 0.0 NaN
refunded 1 1 300.0 300.0
The pattern name=("column", "function") is called named aggregation, and it's the closest thing pandas has to SUM(amount) AS total. Learn this one form and you can skip a lot of confusing older syntax.
.reset_index() turns the group keys back into a normal column. Without it, status becomes the row label. Think of it as "give me a plain table again".
Notice the pending row: total is 0.0 in pandas. Hold that thought, because SQL says something different. We'll get to it.
5. HAVING
pandas has no HAVING. You group first, then filter the grouped result, which is what HAVING was doing all along.
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
t = orders.groupby("customer_id", as_index=False).agg(total=("amount", "sum"))
t[t["total"] > 1000]
customer_id total
1 1700.0
as_index=False does the same job as reset_index() here.
6. JOIN
SELECT c.name, o.order_id, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;
orders.merge(customers, left_on="customer_id", right_on="id", how="inner")[
["name", "order_id", "amount"]
]
name order_id amount
Asha 101 500.0
Asha 102 1200.0
Ravi 103 300.0
Meera 104 800.0
Meera 105 NaN
Order 106 is gone, because customer 5 doesn't exist. That's an inner join: only rows that match on both sides. Change how="inner" to how="left" and the order comes back:
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;
orders.merge(customers, left_on="customer_id", right_on="id", how="left")[
["order_id", "name"]
]
order_id name
101 Asha
102 Asha
103 Ravi
104 Meera
105 Meera
106 NaN
how takes "inner", "left", "right" and "outer". That's INNER, LEFT, RIGHT and FULL OUTER.
"Customers who never ordered"
A favourite interview question. In SQL it's a LEFT JOIN and WHERE ... IS NULL:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;
Same idea in pandas:
m = customers.merge(orders, left_on="id", right_on="customer_id", how="left")
m[m["order_id"].isna()][["name"]]
name
Kiran
IS NULL becomes .isna().
7. CASE WHEN
SELECT order_id, amount,
CASE WHEN amount >= 1000 THEN 'big'
WHEN amount >= 300 THEN 'mid'
ELSE 'small' END AS size
FROM orders;
o = orders.copy()
o["size"] = np.select(
[o["amount"] >= 1000, o["amount"] >= 300],
["big", "mid"],
default="small",
)
o[["order_id", "amount", "size"]]
order_id amount size
101 500.0 mid
102 1200.0 big
103 300.0 mid
104 800.0 mid
105 NaN small
106 150.0 small
np.select takes a list of conditions, a list of results, and a default. Just like WHEN ... THEN ... ELSE. Order matters in both: the first match wins.
Notice order 105 (missing amount) landed in small. Comparing NaN to anything is false, so it fell through to ELSE. SQL does the same, since a comparison with NULL is never true. Same result, same reason.
8. COALESCE
SELECT order_id, COALESCE(amount, 0) AS amount FROM orders;
orders.assign(amount=orders["amount"].fillna(0))
fillna(0) is COALESCE(x, 0). Row 105 now shows 0.0.
9. Window functions
SELECT order_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders;
o = orders.copy()
o["rn"] = (
o.sort_values("amount", ascending=False)
.groupby("customer_id")
.cumcount() + 1
)
o[["order_id", "rn"]]
order_id rn
101 2
102 1
103 1
104 1
105 2
106 1
Sort first (that's the ORDER BY), group (that's the PARTITION BY), then cumcount() numbers the rows inside each group starting from 0. We add 1 to match ROW_NUMBER. This one is the least obvious translation, so it's worth saving.
One small thing to know: SQLite puts NULLs last in a descending sort, and pandas also puts missing values last by default, so both agree that order 104 gets rn = 1 for customer 3. Other databases can place NULLs differently, so check before you trust a window function across engines.
Where pandas and SQL disagree
This is the part that costs people marks. All of these I ran on the data above.
1. Summing nothing
Order 105 is the only pending order, and its amount is missing.
SELECT SUM(amount) FROM orders WHERE status = 'pending';
SQL returns NULL. pandas:
orders[orders["status"] == "pending"]["amount"].sum()
returns 0.0. SQL says "I don't know", pandas says "zero". If you export that to a report, the two numbers mean different things. If you want SQL's behaviour, pass min_count=1: .sum(min_count=1) returns NaN when there is nothing to add.
2. Not-equal and missing values
SELECT COUNT(*) FROM orders WHERE amount != 500;
4
len(orders[orders["amount"] != 500])
5
SQL gives 4 because NULL != 500 is neither true nor false, so the row is dropped. pandas gives 5 because NaN != 500 is True. Same question, different counts. If missing values matter, filter them out explicitly with .notna().
3. COUNT vs size
orders.groupby("status")["amount"].count() # paid 4, pending 0, refunded 1
orders.groupby("status").size() # paid 4, pending 1, refunded 1
.count() skips missing values, like COUNT(amount). .size() counts rows, like COUNT(*). Beginners reach for .count() and quietly lose rows.
4. Checking for missing values
(orders["amount"] == None).sum() # 0
orders["amount"].isna().sum() # 1
== None finds nothing. It's the same mistake as writing = NULL in SQL, just in a different place. Use .isna(), your IS NULL.
Cheat sheet
| SQL | pandas |
|---|---|
SELECT a, b |
df[["a", "b"]] |
WHERE x = 1 AND y > 2 |
df[(df["x"] == 1) & (df["y"] > 2)] |
ORDER BY a DESC LIMIT 3 |
df.sort_values("a", ascending=False).head(3) |
GROUP BY ... SUM(x) AS t |
df.groupby("g").agg(t=("x", "sum")) |
HAVING |
filter the grouped result |
JOIN / LEFT JOIN
|
df.merge(other, on=..., how="inner" / "left") |
IS NULL |
.isna() |
COALESCE(x, 0) |
.fillna(0) |
CASE WHEN |
np.select(conditions, results, default=...) |
COUNT(*) / COUNT(x)
|
.size() / .count()
|
COUNT(DISTINCT x) |
.nunique() |
ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) |
sort_values("x").groupby("g").cumcount() + 1 |
Try it yourself
If you'd like to practise this translation, I've put some pandas problems on LakeBench, a platform I'm building. Two free ones that match this article: GroupBy with Multiple Aggregations and Merge DataFrames with Different Join Types.
A good exercise is to take any SQL query you've already written and translate it line by line, then compare the output. Which one felt most awkward in pandas, the window function or the join?
Top comments (0)