rosetta stone

The same operation, in SQL and in pandas

Every common data operation written twice — once in SQL, once in pandas — side by side, with the gotchas that trip people up when they switch. 32 translations, free for everyone.

32 shown

Selecting & filtering

The everyday clauses: columns, row filters, ordering, limits, distinct values.

Pick columns

Keep a subset of columns, in a chosen order.

sql

SELECT customer, price
FROM sales;

pandas

sales[['customer', 'price']]

Double brackets in pandas: the inner list is the column selection.

Filter rows

Keep rows matching a condition.

sql

SELECT *
FROM sales
WHERE country = 'UK';

pandas

sales[sales['country'] == 'UK']
# or: sales.query("country == 'UK'")

Multiple conditions

Combine conditions with AND / OR.

sql

SELECT *
FROM sales
WHERE country = 'UK' AND price > 200;

pandas

sales[(sales['country'] == 'UK') & (sales['price'] > 200)]

pandas needs parentheses around each condition, and & / | instead of and / or.

Value in a list

Match against a set of values.

sql

SELECT *
FROM sales
WHERE category IN ('audio', 'hardware');

pandas

sales[sales['category'].isin(['audio', 'hardware'])]

Missing values

Find rows where a value is absent.

sql

SELECT *
FROM sales
WHERE rating IS NULL;

pandas

sales[sales['rating'].isna()]

SQL NULL and pandas NaN both refuse equality checks — never write `= NULL` or `== nan`.

Sort and limit

Top rows by a column.

sql

SELECT *
FROM sales
ORDER BY price DESC
LIMIT 3;

pandas

sales.sort_values('price', ascending=False).head(3)
# or: sales.nlargest(3, 'price')

Distinct values

Unique values of a column.

sql

SELECT DISTINCT country
FROM sales;

pandas

sales['country'].drop_duplicates()
# as an array: sales['country'].unique()

Computed column

Add a column derived from others.

sql

SELECT *, units * price AS revenue
FROM sales;

pandas

sales.assign(revenue=sales['units'] * sales['price'])

assign returns a copy; `sales['revenue'] = …` mutates in place.

Conditional value

Bucket rows into labels.

sql

SELECT customer,
       CASE WHEN price >= 300 THEN 'high' ELSE 'low' END AS band
FROM sales;

pandas

import numpy as np
sales.assign(band=np.where(sales['price'] >= 300, 'high', 'low'))

More than two branches: np.select(conditions, choices, default=…) or pd.cut for numeric bins.

Rename a column

Alias output names.

sql

SELECT customer AS buyer
FROM sales;

pandas

sales.rename(columns={'customer': 'buyer'})[['buyer']]

Aggregating

Counting, summing, grouping, and filtering groups after the fact.

Count rows

How many rows are there?

sql

SELECT COUNT(*) FROM sales;

pandas

len(sales)

Aggregate the whole table

One number for the whole dataset.

sql

SELECT SUM(price) AS total, AVG(price) AS avg_price
FROM sales;

pandas

sales['price'].agg(['sum', 'mean'])

Group and aggregate

One row per group.

sql

SELECT category, SUM(price) AS total
FROM sales
GROUP BY category;

pandas

sales.groupby('category', as_index=False).agg(total=('price', 'sum'))

as_index=False (or .reset_index()) keeps the grouping key as a normal column, like SQL does.

Group by several keys

Grouping on a composite key.

sql

SELECT country, category, COUNT(*) AS orders
FROM sales
GROUP BY country, category;

pandas

sales.groupby(['country', 'category']).size().reset_index(name='orders')

Filter groups

Keep only groups meeting a condition.

sql

SELECT category, SUM(price) AS total
FROM sales
GROUP BY category
HAVING SUM(price) > 500;

pandas

g = sales.groupby('category', as_index=False).agg(total=('price', 'sum'))
g[g['total'] > 500]

pandas has no HAVING — you filter the aggregated frame afterwards.

Count distinct

Unique values per group.

sql

SELECT country, COUNT(DISTINCT customer) AS buyers
FROM sales
GROUP BY country;

pandas

sales.groupby('country')['customer'].nunique().reset_index(name='buyers')

Several aggregates at once

Different measures in one pass.

sql

SELECT category,
       COUNT(*) AS orders,
       AVG(price) AS avg_price
FROM sales
GROUP BY category;

pandas

sales.groupby('category').agg(
    orders=('order_id', 'count'),
    avg_price=('price', 'mean'),
).reset_index()

Combining tables

Inner and outer joins, anti-joins, and stacking rows.

Inner join

Only rows matching on both sides.

sql

SELECT s.*, c.segment
FROM sales s
JOIN customers c ON c.customer = s.customer;

pandas

sales.merge(customers, on='customer', how='inner')

Left join

Keep all rows from the left table.

sql

SELECT s.*, c.segment
FROM sales s
LEFT JOIN customers c ON c.customer = s.customer;

pandas

sales.merge(customers, on='customer', how='left')

Unmatched rows become NULL in SQL and NaN in pandas.

Anti join

Rows with no match on the other side.

sql

SELECT c.*
FROM customers c
LEFT JOIN sales s ON s.customer = c.customer
WHERE s.customer IS NULL;

pandas

m = customers.merge(sales, on='customer', how='left', indicator=True)
m[m['_merge'] == 'left_only']

Different key names

Join when the columns are named differently.

sql

SELECT *
FROM sales s
JOIN customers c ON c.customer = s.buyer;

pandas

sales.merge(customers, left_on='buyer', right_on='customer')

Stack rows

Append one result under another.

sql

SELECT customer FROM sales
UNION ALL
SELECT customer FROM customers;

pandas

import pandas as pd
pd.concat([sales[['customer']], customers[['customer']]], ignore_index=True)

UNION (without ALL) also de-duplicates — in pandas add .drop_duplicates().

Window functions

Per-row calculations that look at neighbouring rows.

Rank within a group

Number rows inside each partition.

sql

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY country ORDER BY price DESC) AS rn
FROM sales;

pandas

sales.assign(rn=sales.sort_values('price', ascending=False)
                    .groupby('country').cumcount() + 1)

Top N per group

The biggest row in each group.

sql

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY price DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

pandas

sales.sort_values('price', ascending=False).groupby('country').head(1)

Group total on every row

Compare a row against its group.

sql

SELECT *, SUM(price) OVER (PARTITION BY country) AS country_total
FROM sales;

pandas

sales.assign(country_total=sales.groupby('country')['price'].transform('sum'))

transform is the pandas equivalent of a partitioned aggregate — it returns one value per row.

Previous row

Compare each row to the one before it.

sql

SELECT *, LAG(price) OVER (PARTITION BY customer ORDER BY order_id) AS prev_price
FROM sales;

pandas

sales.assign(prev_price=sales.sort_values('order_id')
                          .groupby('customer')['price'].shift(1))

Running total

Cumulative sum in order.

sql

SELECT *, SUM(price) OVER (ORDER BY order_id
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;

pandas

sales.sort_values('order_id').assign(running=lambda d: d['price'].cumsum())

Reshaping & dates

Pivoting, unpivoting, and slicing by time.

Pivot to wide

Turn values into columns.

sql

SELECT country,
       SUM(CASE WHEN category = 'audio' THEN price END) AS audio,
       SUM(CASE WHEN category = 'hardware' THEN price END) AS hardware
FROM sales
GROUP BY country;

pandas

sales.pivot_table(index='country', columns='category',
                  values='price', aggfunc='sum')

Unpivot to long

Turn columns back into rows.

sql

SELECT country, 'audio' AS category, audio AS total FROM wide
UNION ALL
SELECT country, 'hardware', hardware FROM wide;

pandas

wide.melt(id_vars='country', var_name='category', value_name='total')

Group by month

Bucket timestamps into periods.

sql

SELECT strftime('%Y-%m', order_date) AS month, SUM(price) AS total
FROM sales
GROUP BY month;

pandas

sales.groupby(sales['order_date'].dt.to_period('M'))['price'].sum()
# or: sales.set_index('order_date').resample('MS')['price'].sum()

SQLite uses strftime; Postgres uses date_trunc('month', …).

Difference between dates

Days between two timestamps.

sql

SELECT julianday(shipped_at) - julianday(order_date) AS days
FROM sales;

pandas

(sales['shipped_at'] - sales['order_date']).dt.days

Latest row per key

Deduplicate keeping the newest record.

sql

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) AS rn
  FROM sales
) WHERE rn = 1;

pandas

sales.sort_values('order_date').drop_duplicates('customer', keep='last')

Ready to use it? practice problems →