{"course":{"slug":"python-for-ai","title":"Python, from Zero, for AI","url":"https://addaly.com/learn/python-for-ai","level":"beginner","track":"build"},"lesson":{"slug":"groupby-merge-and-reshape","title":"groupby, merge and pivot: getting from rows to the table a question needs","url":"https://addaly.com/learn/python-for-ai/groupby-merge-and-reshape","position":77,"of":89,"minutes":9,"answer":"groupby splits by key and aggregates per group in one expression; merge joins two tables on a key and multiplies rows when the key repeats on both sides.","keyIdea":"groupby splits by key and aggregates per group in one expression; merge joins two tables on a key and multiplies rows when the key repeats on both sides, so check the row count before and after every join."},"body":{"format":"markdown","text":"## Split, apply, combine\n\nEvery \"per something\" question is a groupby: revenue per month, average score per model, count of messages per language.\n\n```python\ndf.groupby(\"model\")[\"cost_usd\"].sum()\n```\n\nRead it as three steps. **Split** the rows into groups by `model`. **Apply** `sum` to the `cost_usd` column of each group. **Combine** the results into a Series indexed by model. Several statistics at once:\n\n```python\ndf.groupby(\"model\").agg(\n    calls=(\"cost_usd\", \"size\"),\n    total=(\"cost_usd\", \"sum\"),\n    mean_tokens=(\"output_tokens\", \"mean\"),\n    p95_latency=(\"latency_s\", lambda s: s.quantile(0.95)),\n)\n```\n\nNamed aggregation (`new_column=(source_column, function)`) gives readable output columns. `\"size\"` counts rows including missing; `\"count\"` counts non-missing. A lambda works for anything without a built-in name, at the cost of speed.\n\nGroup by several keys and the result has a multi-level index:\n\n```python\nby = df.groupby([\"month\", \"model\"])[\"cost_usd\"].sum()\nby.reset_index()          # back to ordinary columns\nby.unstack(\"model\")       # months as rows, models as columns\n```\n\n`reset_index()` is the move you will make constantly: a groupby result is indexed by its keys, and turning the index back into columns makes it a plain table again for plotting, merging or saving.\n\n`transform` is groupby's other face: it returns a value per *original row*, aligned, so you can add a group statistic as a column:\n\n```python\ndf[\"share_of_month\"] = df[\"cost_usd\"] / df.groupby(\"month\")[\"cost_usd\"].transform(\"sum\")\n```\n\n## Merge: joining two tables\n\n```python\norders.merge(customers, on=\"customer_id\", how=\"left\")\n```\n\n`merge` is SQL's join. `on=` names the key; `left_on=`/`right_on=` when the names differ. `how=` decides what happens to rows without a match:\n\n- `inner` (default): keep only rows whose key appears in both.\n- `left`: keep every row of the left table; unmatched right columns become `NaN`.\n- `right`: the mirror.\n- `outer`: keep everything from both.\n\nChoose `left` when the left table is \"the thing you are describing\" and the right is lookup data. An `inner` join silently drops orders whose customer is missing from the customer file, and you discover it as a total that is lower than it should be.\n\n## The multiplication trap\n\nA join matches every row on the left with every row on the right that shares the key. If the key is unique on the right, each left row appears once. If it is not (a customer file with a row per address, a product file with a row per supplier) each left row appears once **per match**, and the total quietly balloons. A revenue sum over the result is then wrong by a factor nobody notices until the finance team does.\n\nDefend against it with two habits. Print the row count before and after:\n\n```python\nn = len(orders)\njoined = orders.merge(customers, on=\"customer_id\", how=\"left\")\nassert len(joined) == n, f\"join changed row count {n} → {len(joined)}\"\n```\n\nAnd tell pandas what you expect:\n\n```python\norders.merge(customers, on=\"customer_id\", how=\"left\", validate=\"many_to_one\")\n```\n\n`validate=\"many_to_one\"` raises if the right key is not unique. `\"one_to_one\"` checks both sides. It is one argument, and it turns a silent error into a loud one.\n\n`indicator=True` adds a `_merge` column saying `both`, `left_only` or `right_only` for each row: the fastest way to find out which orders had no customer, and why.\n\nKeys must match in type. `int64` on one side and `object` on the other match nothing, which the pandas lesson warned about; `df[\"customer_id\"].astype(str)` on both sides is the usual repair. Leading and trailing spaces in a string key have the same effect.\n\n## Concat: stacking tables\n\n`pd.concat([jan, feb, mar])` stacks tables with the same columns on top of each other, the operation for \"twelve monthly files into one\". `ignore_index=True` renumbers the rows. A column present in one file and absent in another becomes `NaN` where absent, silently, so compare `df.columns` across files first.\n\n## Reshape: wide and long\n\nA table can be **long** (one row per (month, model, value)) or **wide**: months as rows, one column per model. Long is the shape groupby produces and the shape most analysis wants; wide is what a person reads.\n\n```python\nwide = long.pivot_table(index=\"month\", columns=\"model\", values=\"cost_usd\", aggfunc=\"sum\", fill_value=0)\nlong = wide.reset_index().melt(id_vars=\"month\", var_name=\"model\", value_name=\"cost_usd\")\n```\n\n`pivot_table` goes long to wide, aggregating where several rows land in one cell. `melt` goes back. When a spreadsheet arrives with a column per year, `melt` is the first thing to do to it, because a column per year cannot be grouped or filtered by year.\n\n## Sorting and ranking\n\n```python\ndf.sort_values([\"month\", \"cost_usd\"], ascending=[True, False])\ndf[\"rank\"] = df.groupby(\"month\")[\"cost_usd\"].rank(ascending=False)\ndf.nlargest(10, \"cost_usd\")\n```\n\n`nlargest` is `sort_values(...).head(n)` done efficiently. `rank` within groups gives \"this model's position within its month\" without a loop.\n\n## Try this now\n\nMake an `orders` table and a `customers` table where one customer appears twice. Merge them, watch the row count, then add `validate=\"many_to_one\"` and read the error. Fix the duplicate, redo it with `indicator=True`, and count the `left_only` rows. Then groupby month and model, `unstack`, and `melt` it back."},"check":{"question":"`orders` has 10,000 rows and `customers` has 2,000. After `orders.merge(customers, on=\"customer_id\")` the result has 31,000 rows. Which explanation fits?","options":[{"text":"The merge defaulted to an outer join and added a row for every customer with no orders, plus their combinations.","correct":false,"tempting":"The default is an inner join, and an outer join could add at most 2,000 rows, not 21,000."},{"text":"pandas merges on the index as well as the key, and mismatched indexes triple the output.","correct":false,"tempting":"`on=` merges on the named column only; the index is not consulted."},{"text":"`customer_id` is not unique in `customers`, so each order matched several customer rows and was repeated once per match.","correct":true},{"text":"Merging tables of different sizes always produces a row count between the sum and the product of the two, which 31,000 is, so nothing is wrong and the result should be trusted.","correct":false,"tempting":"An inner join on a unique key produces at most the smaller side's matches; there is no such range rule."}]},"prev":{"slug":"cleaning-data-in-pandas","title":"Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical","url":"https://addaly.com/learn/python-for-ai/cleaning-data-in-pandas"},"next":{"slug":"plotting-with-matplotlib","title":"matplotlib: a plot you can read, saved to a file, from a script or a server","url":"https://addaly.com/learn/python-for-ai/plotting-with-matplotlib"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Source and assessment review continues. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'groupby, merge and pivot: getting from rows to the table a question needs', in Python, from Zero, for AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/python-for-ai/groupby-merge-and-reshape (Accessed: 21 November 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"groupby, merge and pivot: getting from rows to the table a question needs","courseTitle":"Python, from Zero, for AI","module":{"index":8,"slug":"numbers-in-bulk","title":"Numbers in bulk: NumPy, pandas and the vector under every model"},"answer":null,"keyIdea":"groupby splits by key and aggregates per group in one expression; merge joins two tables on a key and multiplies rows when the key repeats on both sides, so check the row count before and after every join.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"split-apply-combine","runs":[{"text":"Split, apply, combine"}]},{"t":"p","runs":[{"text":"Every “per something” question is a groupby: revenue per month, average score per model, count of messages per language."}]},{"t":"code","lang":"python","label":"Python","text":"df.groupby(\"model\")[\"cost_usd\"].sum()"},{"t":"p","runs":[{"text":"Read it as three steps. "},{"text":"Split","b":true},{"text":" the rows into groups by "},{"text":"model","code":true},{"text":". "},{"text":"Apply","b":true},{"text":" "},{"text":"sum","code":true},{"text":" to the "},{"text":"cost_usd","code":true},{"text":" column of each group. "},{"text":"Combine","b":true},{"text":" the results into a Series indexed by model. Several statistics at once:"}]},{"t":"code","lang":"python","label":"Python","text":"df.groupby(\"model\").agg(\n    calls=(\"cost_usd\", \"size\"),\n    total=(\"cost_usd\", \"sum\"),\n    mean_tokens=(\"output_tokens\", \"mean\"),\n    p95_latency=(\"latency_s\", lambda s: s.quantile(0.95)),\n)"},{"t":"p","runs":[{"text":"Named aggregation ("},{"text":"new_column=(source_column, function)","code":true},{"text":") gives readable output columns. "},{"text":"\"size\"","code":true},{"text":" counts rows including missing; "},{"text":"\"count\"","code":true},{"text":" counts non-missing. A lambda works for anything without a built-in name, at the cost of speed."}]},{"t":"p","runs":[{"text":"Group by several keys and the result has a multi-level index:"}]},{"t":"code","lang":"python","label":"Python","text":"by = df.groupby([\"month\", \"model\"])[\"cost_usd\"].sum()\nby.reset_index()          # back to ordinary columns\nby.unstack(\"model\")       # months as rows, models as columns"},{"t":"p","runs":[{"text":"reset_index()","code":true},{"text":" is the move you will make constantly: a groupby result is indexed by its keys, and turning the index back into columns makes it a plain table again for plotting, merging or saving."}]},{"t":"p","runs":[{"text":"transform","code":true},{"text":" is groupby’s other face: it returns a value per "},{"text":"original row","i":true},{"text":", aligned, so you can add a group statistic as a column:"}]},{"t":"code","lang":"python","label":"Python","text":"df[\"share_of_month\"] = df[\"cost_usd\"] / df.groupby(\"month\")[\"cost_usd\"].transform(\"sum\")"},{"t":"h2","id":"merge-joining-two-tables","runs":[{"text":"Merge: joining two tables"}]},{"t":"code","lang":"python","label":"Python","text":"orders.merge(customers, on=\"customer_id\", how=\"left\")"},{"t":"p","runs":[{"text":"merge","code":true},{"text":" is SQL’s join. "},{"text":"on=","code":true},{"text":" names the key; "},{"text":"left_on=","code":true},{"text":"/"},{"text":"right_on=","code":true},{"text":" when the names differ. "},{"text":"how=","code":true},{"text":" decides what happens to rows without a match:"}]},{"t":"ul","items":[[{"text":"inner","code":true},{"text":" (default): keep only rows whose key appears in both."}],[{"text":"left","code":true},{"text":": keep every row of the left table; unmatched right columns become "},{"text":"NaN","code":true},{"text":"."}],[{"text":"right","code":true},{"text":": the mirror."}],[{"text":"outer","code":true},{"text":": keep everything from both."}]]},{"t":"p","runs":[{"text":"Choose "},{"text":"left","code":true},{"text":" when the left table is “the thing you are describing” and the right is lookup data. An "},{"text":"inner","code":true},{"text":" join silently drops orders whose customer is missing from the customer file, and you discover it as a total that is lower than it should be."}]},{"t":"h2","id":"the-multiplication-trap","runs":[{"text":"The multiplication trap"}]},{"t":"p","runs":[{"text":"A join matches every row on the left with every row on the right that shares the key. If the key is unique on the right, each left row appears once. If it is not (a customer file with a row per address, a product file with a row per supplier) each left row appears once "},{"text":"per match","b":true},{"text":", and the total quietly balloons. A revenue sum over the result is then wrong by a factor nobody notices until the finance team does."}]},{"t":"p","runs":[{"text":"Defend against it with two habits. Print the row count before and after:"}]},{"t":"code","lang":"python","label":"Python","text":"n = len(orders)\njoined = orders.merge(customers, on=\"customer_id\", how=\"left\")\nassert len(joined) == n, f\"join changed row count {n} → {len(joined)}\""},{"t":"p","runs":[{"text":"And tell pandas what you expect:"}]},{"t":"code","lang":"python","label":"Python","text":"orders.merge(customers, on=\"customer_id\", how=\"left\", validate=\"many_to_one\")"},{"t":"p","runs":[{"text":"validate=\"many_to_one\"","code":true},{"text":" raises if the right key is not unique. "},{"text":"\"one_to_one\"","code":true},{"text":" checks both sides. It is one argument, and it turns a silent error into a loud one."}]},{"t":"p","runs":[{"text":"indicator=True","code":true},{"text":" adds a "},{"text":"_merge","code":true},{"text":" column saying "},{"text":"both","code":true},{"text":", "},{"text":"left_only","code":true},{"text":" or "},{"text":"right_only","code":true},{"text":" for each row: the fastest way to find out which orders had no customer, and why."}]},{"t":"p","runs":[{"text":"Keys must match in type. "},{"text":"int64","code":true},{"text":" on one side and "},{"text":"object","code":true},{"text":" on the other match nothing, which the pandas lesson warned about; "},{"text":"df[\"customer_id\"].astype(str)","code":true},{"text":" on both sides is the usual repair. Leading and trailing spaces in a string key have the same effect."}]},{"t":"h2","id":"concat-stacking-tables","runs":[{"text":"Concat: stacking tables"}]},{"t":"p","runs":[{"text":"pd.concat([jan, feb, mar])","code":true},{"text":" stacks tables with the same columns on top of each other, the operation for “twelve monthly files into one”. "},{"text":"ignore_index=True","code":true},{"text":" renumbers the rows. A column present in one file and absent in another becomes "},{"text":"NaN","code":true},{"text":" where absent, silently, so compare "},{"text":"df.columns","code":true},{"text":" across files first."}]},{"t":"h2","id":"reshape-wide-and-long","runs":[{"text":"Reshape: wide and long"}]},{"t":"p","runs":[{"text":"A table can be "},{"text":"long","b":true},{"text":" (one row per (month, model, value)) or "},{"text":"wide","b":true},{"text":": months as rows, one column per model. Long is the shape groupby produces and the shape most analysis wants; wide is what a person reads."}]},{"t":"code","lang":"python","label":"Python","text":"wide = long.pivot_table(index=\"month\", columns=\"model\", values=\"cost_usd\", aggfunc=\"sum\", fill_value=0)\nlong = wide.reset_index().melt(id_vars=\"month\", var_name=\"model\", value_name=\"cost_usd\")"},{"t":"p","runs":[{"text":"pivot_table","code":true},{"text":" goes long to wide, aggregating where several rows land in one cell. "},{"text":"melt","code":true},{"text":" goes back. When a spreadsheet arrives with a column per year, "},{"text":"melt","code":true},{"text":" is the first thing to do to it, because a column per year cannot be grouped or filtered by year."}]},{"t":"h2","id":"sorting-and-ranking","runs":[{"text":"Sorting and ranking"}]},{"t":"code","lang":"python","label":"Python","text":"df.sort_values([\"month\", \"cost_usd\"], ascending=[True, False])\ndf[\"rank\"] = df.groupby(\"month\")[\"cost_usd\"].rank(ascending=False)\ndf.nlargest(10, \"cost_usd\")"},{"t":"p","runs":[{"text":"nlargest","code":true},{"text":" is "},{"text":"sort_values(...).head(n)","code":true},{"text":" done efficiently. "},{"text":"rank","code":true},{"text":" within groups gives “this model’s position within its month” without a loop."}]},{"t":"h2","id":"try-this-now","runs":[{"text":"Try this now"}],"role":"try"},{"t":"p","runs":[{"text":"Make an "},{"text":"orders","code":true},{"text":" table and a "},{"text":"customers","code":true},{"text":" table where one customer appears twice. Merge them, watch the row count, then add "},{"text":"validate=\"many_to_one\"","code":true},{"text":" and read the error. Fix the duplicate, redo it with "},{"text":"indicator=True","code":true},{"text":", and count the "},{"text":"left_only","code":true},{"text":" rows. Then groupby month and model, "},{"text":"unstack","code":true},{"text":", and "},{"text":"melt","code":true},{"text":" it back."}]}],"check":{"question":[{"text":"orders","code":true},{"text":" has 10,000 rows and "},{"text":"customers","code":true},{"text":" has 2,000. After "},{"text":"orders.merge(customers, on=\"customer_id\")","code":true},{"text":" the result has 31,000 rows. Which explanation fits?"}],"options":[{"text":[{"text":"The merge defaulted to an outer join and added a row for every customer with no orders, plus their combinations."}],"correct":false,"tempting":[{"text":"The default is an inner join, and an outer join could add at most 2,000 rows, not 21,000."}]},{"text":[{"text":"pandas merges on the index as well as the key, and mismatched indexes triple the output."}],"correct":false,"tempting":[{"text":"on=","code":true},{"text":" merges on the named column only; the index is not consulted."}]},{"text":[{"text":"customer_id","code":true},{"text":" is not unique in "},{"text":"customers","code":true},{"text":", so each order matched several customer rows and was repeated once per match."}],"correct":true,"tempting":null},{"text":[{"text":"Merging tables of different sizes always produces a row count between the sum and the product of the two, which 31,000 is, so nothing is wrong and the result should be trusted."}],"correct":false,"tempting":[{"text":"An inner join on a unique key produces at most the smaller side’s matches; there is no such range rule."}]}],"order":[0,2,3,1],"why":null},"prev":{"slug":"cleaning-data-in-pandas","title":"Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical","minutes":9},"next":{"slug":"plotting-with-matplotlib","title":"matplotlib: a plot you can read, saved to a file, from a script or a server","minutes":8}}}