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?