All lessons

Module 8: Analyses employers ask for · Lesson 8.7 · about 20 min

pandas for SQL, Excel and DAX users

Translate what you already know: SQL SELECT, WHERE, GROUP BY, JOIN and window functions, Excel filters, VLOOKUP, SUMIFS and pivot tables, and DAX CALCULATE and measures, each with its pandas line side by side.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 20 minutes · uses Modules 2 to 6. You’ll be able to move between SQL, Excel, DAX and pandas without relearning the ideas.

SQL, Excel, DAX and pandas share the same few ideas: filter, group, summarise, join, reshape. Learn the mapping once and you can move between them.

In everyday terms: the same recipe written in four languages: “boil water” is the same step whatever the words.

1. SQL to pandas

SQL pandas
SELECT a, b FROM t t[['a', 'b']]
WHERE qty > 2 AND store_id = 'S01' t.query("qty > 2 and store_id == 'S01'")
ORDER BY qty DESC LIMIT 5 t.nlargest(5, 'qty')
GROUP BY store_id + SUM(qty) t.groupby('store_id')['qty'].sum()
HAVING SUM(qty) > 1000 filter the grouped result
COUNT(DISTINCT customer_id) .nunique()
LEFT JOIN p ON t.pid = p.pid t.merge(p, on='pid', how='left')
UNION ALL pd.concat([t1, t2])
CASE WHEN ... END np.select(...) or np.where(...)
ROW_NUMBER() OVER (PARTITION BY c ORDER BY d) t.sort_values('d').groupby('c').cumcount() + 1
SUM(x) OVER (PARTITION BY c) t.groupby('c')['x'].transform('sum')
LAG(x) OVER (PARTITION BY c ORDER BY d) t.sort_values('d').groupby('c')['x'].shift(1)
Pythonorders.csv, products.csv

2. Excel to pandas

Excel pandas
Filter button df[df['store_id'] == 'S01']
VLOOKUP / XLOOKUP df.merge(lookup, on='key', how='left') or df['key'].map(...)
SUMIFS(qty, store, “S01”, month, 3) df.loc[(df.store_id == 'S01') & (df.date.dt.month == 3), 'qty'].sum()
COUNTIFS the same with .size or (mask).sum()
Pivot table df.pivot_table(index=..., columns=..., values=..., aggfunc=...)
Fill down df['col'].ffill()
Remove duplicates df.drop_duplicates()
IFERROR pd.to_numeric(df.x, errors='coerce').fillna(0)

The big difference: Excel formulas run cell by cell; pandas runs a whole column at once, and every step is written down, so it can be rerun next month.

3. DAX to pandas

DAX pandas
A measure Revenue = SUM(orders[revenue]) a function of a filtered table: def revenue(d): return d['revenue'].sum()
CALCULATE([Revenue], stores[province] = "Ontario") revenue(df[df.province == 'Ontario'])
A visual sliced by month df.groupby('month').apply(revenue)
DIVIDE([Revenue], [Orders]) build ratios from totals, after grouping (Lesson 8.2)
SAMEPERIODLASTYEAR shift(12) on a monthly series, or a merge on month − 12
ALL(stores) (ignore a filter) compute on the unfiltered table and join it back
Pythonorders.csv, stores.csv
WHERE / filter
df[cond] or df.query(...).
GROUP BY + SUM / pivot / SUMMARIZE
groupby(...).agg(...) or pivot_table.
HAVING
A filter after the groupby.
JOIN / XLOOKUP / RELATED
merge or map.
Physical meaning
The order of steps matters the same way everywhere: WHERE filters rows before grouping, HAVING filters groups after. Swap them and you answer a different question.

“pandas does things differently, so I have to relearn analysis.” The thinking is identical; only the syntax changes. Write the SQL in a comment, then translate it line by line.

Watch it happen, one step at a time:

The SQL. SELECT store, SUM(qty) AS units FROM orders WHERE price > 5 GROUP BY store HAVING SUM(qty) > 3
WHERE. orders[orders.price > 5]
GROUP BY + SUM. .groupby('store', as_index=False).agg(units=('qty', 'sum'))
HAVING. .query('units > 3')
Result.
storeunits
S017
WEB5
Same answer as the database.

4. Your turn

Your turnorders.csv
How it works

Principle: SQL, Excel, DAX and pandas all do the same few things to tables: filter rows, choose columns, group and aggregate, join, reshape, and compute along a window. Only the spelling differs. Learn the six ideas once and each new tool is a new dictionary, not a new subject.

One real difference: a DAX measure is evaluated again for every cell of a visual under its filters, while a pandas result is computed once. To get measure-like behaviour in pandas, write the measure as a function of a table and apply it per group.

5. Practice

  1. SQL SELECT store_id, COUNT(*) FROM orders GROUP BY store_id in pandas.
  2. Excel COUNTIFS(store, “WEB”, qty, “>3”).
  3. Why does HAVING become a filter after groupby?
Answers
  1. orders.groupby('store_id').size() (or orders['store_id'].value_counts()).
  2. ((orders.store_id == 'WEB') & (orders.qty > 3)).sum().
  3. HAVING filters groups by their aggregate, which exists only after grouping.

New words: window function, transform, SUMIFS, measure, filter context.

  • WHERE = filter rows; HAVING = filter after groupby.
  • GROUP BY / pivot / SUMMARIZE = groupby().agg() or pivot_table.
  • JOIN / XLOOKUP / RELATED = merge or map.
  • Translate step by step, keeping the order.

Check yourself

1Which pandas line matches SQL's GROUP BY store_id HAVING SUM(qty) > 1000?
2Excel's VLOOKUP is closest to...
3What is the pandas version of SUM(qty) OVER (PARTITION BY store_id)?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.