Join safety: duplicate keys, validate, indicator and transform
A lookup table with a repeated key silently inflates the revenue total from 370 to 660. validate turns that into an error, and indicator=True labels every row of an outer join as both, left_only or right_only so you can audit the match. transform gives each row its group's mean, which is the base for 'difference from the city average' features.
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
orders: 3 after merge: 5
total before: 370.0 after: 660.0
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
order cust amount name _merge
0 1.0 10 250.0 Asha both
1 3.0 10 40.0 Asha both
2 2.0 11 80.0 Ben both
3 NaN 12 NaN Chen right_only
{'both': 3, 'right_only': 1, 'left_only': 0}
city sales city_avg vs_avg
0 Pune 10.0 20.0 -10.0
1 Pune 30.0 20.0 10.0
2 Delhi 50.0 50.0 0.0
count size sum
g
a 1 2 1.0
b 1 1 3.0