Pandas ↔ SQL Cheat Sheet
Python · Reshaping & Workflow
Theory
Most SQL clauses map directly to a pandas method. SELECT cols is df[['a', 'b']]; WHERE is a boolean mask or df.query(); ORDER BY is sort_values(); LIMIT n is head(n); DISTINCT is drop_duplicates(); CASE WHEN is np.where or np.select; GROUP BY with aggregates is groupby().agg(); HAVING filters the aggregated result; JOIN is merge(how=...); UNION ALL is pd.concat() and UNION adds drop_duplicates(); COALESCE is fillna() or combine_first().
Window functions follow one pattern - groupby(partition) then a per-row method: SUM() OVER (PARTITION BY ...) is transform('sum'); ROW_NUMBER, RANK and DENSE_RANK are cumcount() + 1 and rank(method='first' / 'min' / 'dense'); LAG/LEAD are shift(1)/shift(-1); running totals are cumsum(); moving averages are rolling(n).mean(). The main difference to remember is order: SQL runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, while in pandas you write the steps in the order you want them to happen.
Why we use it
Many learners already think in SQL. A direct mapping lets you translate any query you know into pandas, and back, which speeds up learning and helps in interviews that ask for both.
When to use it
Use this sheet when translating a query into pandas or checking which method matches a SQL idea. For data already in a database, push heavy filtering and joining into SQL and use pandas for the final analysis and charts.
How it works
- FROM / JOIN: start with df or df.merge(...)
- WHERE: df[mask] or .query(...)
- GROUP BY + aggregates: .groupby(...).agg(...)
- HAVING: filter the aggregated result
- SELECT / window functions: assign, transform, rank, shift
- ORDER BY / LIMIT: .sort_values(...).head(n)
- Same answer as the SQL query
Syntax
WHERE -> df[mask] / df.query()
GROUP BY -> df.groupby(k).agg(...)
HAVING -> filter the agg result
JOIN -> df.merge(other, on=k, how=...)
UNION ALL -> pd.concat([a, b])
OVER() -> groupby(k)[c].transform / rank / shift / cumsumExample
# SELECT city, SUM(amount) AS total FROM orders
# WHERE amount > 500 GROUP BY city HAVING SUM(amount) > 2000
# ORDER BY total DESC LIMIT 2
t = df[df['amount'] > 500].groupby('city', as_index=False).agg(total=('amount', 'sum'))
print(t[t['total'] > 2000].sort_values('total', ascending=False).head(2))Key points
- WHERE = mask/query; GROUP BY = groupby().agg(); HAVING = filter the aggregate
- JOIN = merge(how=...); UNION ALL = concat; UNION = concat + drop_duplicates
- OVER (PARTITION BY k) = groupby(k) + transform / rank / shift / cumsum
- ORDER BY = sort_values; LIMIT = head(n); DISTINCT = drop_duplicates
- CASE WHEN = np.where / np.select; COALESCE = fillna / combine_first
- pandas runs steps in the order you write them; SQL has a fixed logical order