Grouping and summarizing: pandas groupby for quick business answers

Which region really performs best?. Picture a Monday morning. Your manager pings you: which region brings in the most money, and is our new checkout page converting better? You open the orders export and see hundreds of rows. In Excel you might filter by North, copy the total, filter by South, copy again, and hope you didn't miss one. What if one line of code could give you every region's total, count and average at once, with no copying? That's what grouping does. It's the single most useful move in everyday analysis, and once you have it, questions like these stop being chores and become quick answers you can trust.

What you will learn. Here's the plan for this lesson. First, you'll learn the idea behind grouping, called split, apply, combine, so the code never feels like magic. Second, you'll use pandas groupby to summarize real shop questions: revenue per region, conversion rate in an A B test, and support ticket times per team. You'll use sum, count, mean and median along the way. Third, you'll learn the most common trap with group summaries, comparing groups of very different sizes, and how to guard against it with one extra column.

Split, apply, combine. Every group summary follows three steps. Think of sorting a pile of paper receipts on your desk. First you split them into piles, one pile per region. Then you apply a calculation to each pile, like adding up its amounts. Finally you combine the answers into a short list: North, this much; South, that much. That's it. In pandas, the split step is groupby, the apply step is a function like sum or mean, and pandas does the combine step for you automatically. Keep this picture in mind, because every example today is just these three steps with a different calculation.

Your first groupby. Here are eight small orders, each with a region and an amount in dollars. The key line reads: orders dot groupby region, then pick the amount column in square brackets, then dot sum. Split by region, apply sum to amount, combine. And there's the answer. East has eighty dollars, North has one hundred twenty, and South has one hundred five. Notice pandas sorted the regions alphabetically and put the group names on the left as labels, called the index. The index is simply the row labels of a table, like the row headers in a pivot table. Next, we'll check those totals by hand.

Checking the totals by hand. Let's check pandas by adding up each pile ourselves. The North total in dollars: forty plus sixty plus twenty equals one hundred twenty. The South total in dollars: twenty-five plus thirty-five plus forty-five equals one hundred five. The East total in dollars: thirty plus fifty equals eighty. So North brings in the most: one hundred twenty dollars. That's the answer to your manager's question, and it matches what pandas printed.

Several summaries at once. One number per group is rarely enough. The agg method, short for aggregate, lets you ask for several at once. An aggregate is any calculation that squeezes many values into one, like a total or an average. Here we pass a list: count, sum and mean. Mean is the ordinary average: add the values up and divide by how many there are. Now one line gives a mini report. East has two orders, eighty dollars in total, and an average of forty. North has three orders, one hundred twenty, average forty. South has three orders, one hundred five, average thirty-five.

Checking the averages by hand. Each average is a total divided by the number of orders. The North average: divide by three, so one hundred twenty divided by three equals forty. The South average: divide by three, so one hundred five divided by three equals thirty-five. The East average: divide by two, so eighty divided by two equals forty. So East and North both average forty dollars per order, even though their totals are very different. Remember that; it comes back later.

Clear names, sorted results. Column names like count and sum are fine for you, but your manager wants plain labels. Named aggregation fixes that. Inside agg, each new column gets a name, then a pair: which column to use and which calculation to apply. So orders equals amount with count, and revenue equals amount with sum. Then sort values by revenue, with ascending set to False, which puts the biggest first. Now the table reads like a finished answer: North leads with three orders and one hundred twenty dollars, then South with one hundred five, then East with eighty. This is ready to paste into an email.

Conversion rate in an A/B test. An A B test shows two versions of a page, A and B, to different visitors and compares results. Here each row is a visitor, and converted is one if they bought and zero if they didn't. Here's a handy shortcut: the mean of a zero and one column is the share of ones, which is exactly the conversion rate. So group by variant and take the mean of converted. Pandas prints one rate per variant. Let's work both out by hand to see where they come from.

Working out the conversion rates. For variant A, add the ones and divide by four: one plus zero plus zero plus one, over four, equals two fourths. Two fourths equals zero point five, so A converts at fifty percent. For variant B, add the ones: one plus one plus zero plus one, over four, equals three fourths. Three fourths equals zero point seven five, so B converts at seventy-five percent. So B converts better: seventy-five percent against fifty percent. With only four visitors each, though, this is a demo, not proof.

Ticket times: mean or median?. Now support tickets: how many hours each team took to resolve them. We ask for the mean and the median. The median is the middle value once you line the numbers up, like the person standing in the middle of a height-ordered queue. With an even count there are two middle values, so the median is the average of those two. Look at Chat: pandas gives a mean of eight point seven five hours, but a median of just two. One stuck ticket of thirty hours drags the average up. Email's mean and median are both six point five, because its times are evenly spread, with no extreme value. We'll work both teams out by hand next.

Chat tickets by hand. First the Chat total: one plus two plus two plus thirty equals thirty-five hours. For the mean, divide by four tickets: thirty-five divided by four equals eight point seven five hours. For the median, line the times up in order: one, two, two, thirty. There are four values, so average the two middle ones. Two plus two, divided by two, equals two, so the Chat median is two hours. The single thirty-hour ticket pulls the mean far above what a typical customer waits.

Email tickets by hand. Now the Email total: five plus six plus seven plus eight equals twenty-six hours. For the mean, divide by four tickets: twenty-six divided by four equals six point five hours. The times are already in order, so for the median take the two middle values: six plus seven, divided by two, equals six point five hours. The mean and median agree because there is no extreme value. For times and prices, where one huge value is common, report the median alongside the mean.

From summary to chart. A group summary is small, so it turns into a clear chart. From our revenue table, three bars appear: North at one hundred twenty, South at one hundred five, East at eighty. Sorting them from biggest to smallest makes the ranking obvious without anyone reading numbers. Then highlight the bar the question is about, so North stands out for your manager at a glance. In pandas you'd get a quick version of this by adding dot plot, with kind set to bar, on the end of your summary. Keep the chart as simple as the question.

Common mistake: ignoring group size. Here's the trap. Looking only at revenue, East seems weakest. But East had only two orders, while North and South had three each. Per order, East averages forty dollars, the same as North. So is East bad, or just small? Totals mostly tell you which group had more rows. The opposite trap is just as common: a group with two visitors and a hundred percent conversion looks amazing, but two visitors prove almost nothing. The fix is simple: always put a count column next to any total or average, so the size of each group is visible before anyone draws a conclusion.

Recap. Let's recap. First, groupby is split, apply, combine: sort rows into piles, calculate one number per pile, and stack the answers. Second, agg with named columns and sort values turns that into a report your manager can read straight away, and the mean of a zero and one column gives you a conversion rate for free. Third, a group summary can mislead. Show a count beside every total or average, and use the median when a few extreme values, like a stuck support ticket, pull the mean around.

Your exercise. Now it's your turn. Build a small DataFrame of twelve orders with three columns: region, device and amount, where device is either mobile or desktop. Group by device and use named aggregation to get the order count, total revenue, mean amount and median amount. Sort by revenue, largest first. Then write one plain sentence you'd send to a manager, and check it against the counts before you trust it. For a bonus, try grouping by two columns at once by passing a list: region and device. In the next lesson, you'll put everything together in a project.