If You Know SQL, You Already Know Most of pandas

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 / OR become & / |
• 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

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?