groupby, agg and pivot_table
Change sum to mean in the first groupby and predict the Pune value before running.
groupby splits a table into groups and summarises each, merge joins two tables on a shared key like a SQL join, concat stacks tables, and pivot_table turns rows into a grid. Together they answer most business questions about a table.
Change the code and press Run (Ctrl+Enter). Predict what will happen first, then break it on purpose and read what changes. Your edits are saved and match the lesson page.
Ready for more? This phase has 6 hands-on exercises with automatic checks.
Practice questions
Write the code in the editor, run it, then open the model answer to compare.
Given sales with shop and amount columns, compute the average amount per shop.
Left-merge a students frame (id, name) with marks (id, mark) and list who has no mark.
Write a chain that keeps rows where amount > 10, sorts by amount descending and keeps the first row.
Given sales with columns shop (A, B, A, B) and amount (10, 20, 30, 60), add a column share holding each row's amount as a fraction of its own shop's total, using transform.
Explain it without notes
Explain split-apply-combine and write a groupby that gives the total revenue per city.
What is the difference between inner, left and outer merges? Give a case for each.
When do you use concat and when merge?
What does a pivot table give you that a groupby does not?
After a left merge your row count grows from 1,000 to 1,400 and revenue totals jump. What happened and how do you detect and prevent it?
What is the difference between groupby(...).agg(...) and groupby(...).transform(...), and between count and size?
Expected output
city
Delhi 77.5
Mumbai 10.0
Pune 76.0
Name: revenue, dtype: float64
orders units revenue
city
Delhi 2 12 77.5
Pune 3 19 76.0
Mumbai 1 4 10.0
product book pen
city
Delhi 5 7
Mumbai 0 4
Pune 3 16