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.
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.
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) |
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 |
- 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:
SELECT store, SUM(qty) AS units FROM orders WHERE price > 5 GROUP BY store HAVING SUM(qty) > 3orders[orders.price > 5].groupby('store', as_index=False).agg(units=('qty', 'sum')).query('units > 3')| store | units |
|---|---|
| S01 | 7 |
| WEB | 5 |
4. Your turn
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
- SQL
SELECT store_id, COUNT(*) FROM orders GROUP BY store_idin pandas. - Excel COUNTIFS(store, “WEB”, qty, “>3”).
- Why does
HAVINGbecome a filter aftergroupby?
Answers
orders.groupby('store_id').size()(ororders['store_id'].value_counts()).((orders.store_id == 'WEB') & (orders.qty > 3)).sum().- 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
Sources
- pandas documentation, User Guide: Comparison with SQL https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html
- pandas documentation, User Guide: Comparison with spreadsheets https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_spreadsheets.html
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.