Comparing Groups: Grouping, Shares and Small Samples
Split a table into groups, summarise each one, and learn why a group of four deserves caution.
In this lesson you will
- Group a table and summarise each group
- Work out shares of a total
- Spot groups too small to trust
- Avoid over-slicing
About 28 minutes
Which repair is the busy one?
Leila has the overall picture. Now she wants to compare. Are some repairs much more common than others? Which take the longest? Which bring in the most money? Comparing means splitting the table into groups, summarising each group, and putting the results side by side. In pandas, this is called grouping. Spreadsheet users know it as a pivot table.
Split, summarise, combine
Grouping works in three steps. Split the rows by one column, here the type of repair. Summarise each group with the same measures, such as a count and a mean. Combine the group results into one small table. The program below does this for repairs, shows the share of visits for two of them, and finishes with the most common bike type.
import pandas as pd
df = pd.read_csv("clean.csv")
g = df.groupby("repair").agg(visits=("visit_id", "count"), minutes=("minutes", "mean"), cost=("cost", "mean"))
for name, row in g.sort_values("visits", ascending=False).iterrows():
note = " (few)" if row["visits"] < 5 else ""
print(name + ": " + str(int(row["visits"])) + " visits, " + str(round(row["minutes"])) + " min, " + str(round(row["cost"], 2)) + note)
share = (g["visits"] / g["visits"].sum() * 100).round(1)
print("share of visits, puncture:", share["puncture"], "percent")
print("share of visits, wheel:", share["wheel"], "percent")
by_bike = df["bike"].value_counts()
print("most common bike:", by_bike.index[0], "(" + str(by_bike.iloc[0]) + ")")
gears: 10 visits, 41 min, 19.35
brakes: 8 visits, 31 min, 15.69
chain: 8 visits, 22 min, 9.56
puncture: 8 visits, 16 min, 6.38
wheel: 4 visits, 50 min, 17.25 (few)
share of visits, puncture: 21.1 percent
share of visits, wheel: 10.5 percent
most common bike: hybrid (12)
Read down the list, which is sorted by the number of visits. Gears is the busiest repair, with 10 visits, taking 41 minutes on average and costing 19.35 in sample money. Punctures are quick, at 16 minutes, and cheap. Wheels are the slowest, at 50 minutes. Notice that the pattern is sensible: longer repairs cost more. A table that contradicted common sense would be a reason to go back and check the data.
Shares and the size of the group
A share compares a group with the whole. Punctures are 21.1 percent of all visits, and wheels are 10.5 percent. Shares are often easier to talk about than raw counts, but they hide how big the groups are, which matters a lot.
Look at the wheel line in the output, marked few. Only 4 visits were wheel repairs. An average based on four values can swing a lot if one more visit is added: a single unusual wheel repair would change the average by a quarter. The program marks any group with fewer than five rows, and that five is a rough teaching rule, not a law. The honest approach is to show the number of rows next to every group average, and to be cautious about comparing a group of four with a group of ten.
Turning a table into a sentence
A table is not yet a finding. A finding is a sentence that someone could repeat to a friend. Compare these two. “Gears: 10, 41, 19.35” is a row of numbers. “Gear repairs were the most common job, and they took about forty minutes each” is a finding: it says what, how big, and what it means. Write the sentence, then check it against the table. A finding that you cannot trace to a number should not be in your report.
Also check that the groups add up. The visit counts of the five repairs should sum to the total number of visits. If they do not, rows have been lost or counted twice, perhaps because of a missing value in the grouping column. That quick sum is one of the cheapest and most powerful checks in analysis.
Do not over-slice
It is tempting to keep splitting: repairs by bike, then by source, then by week. Each extra split makes the groups smaller, until every group holds one or two rows and the averages mean nothing. With 38 visits you can reasonably look at one or two splits. A larger table supports more. As a habit, ask of every group number, “How many rows is this?”.
Comparing group averages without looking at group sizes. An average of four values and an average of forty are not equally trustworthy, even if they sit side by side in a table.
The visits are made up, and costs are sample money. The five-row warning is a teaching rule of thumb. Different questions need different minimum group sizes, and a bigger table allows more splits.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What are the three steps of grouping?
- Copy, paste, delete
- Sort, save, share
- Open, close, print
- Split, summarise, combine
Rows are split by repair, each group is summarised, and results are combined.
Why mark groups with few rows?
- Their averages can swing a lot if one value changes
- They are always wrong
- They cannot be counted
- They are duplicates
The wheel group had only four visits.
What does a share compare?
- Two dates
- Two people
- A group with the whole
- Two files
Punctures were a share of all visits.
What is the problem with over-slicing?
- It deletes data
- It makes files larger
- It changes dates
- Groups become so small that averages mean little
Each extra split shrinks the groups.
Which table would be a reason to recheck the data?
- One sorted by visits
- One where longer repairs cost much less than short ones
- One with counts
- One with shares
Results that contradict common sense deserve a second look.
Summary
- Grouping is split, summarise, combine; spreadsheets call it a pivot table.
- A share compares a group with the whole.
- Show the number of rows next to every group average.
- Too many splits leave groups too small to mean anything.
Questions
What is the difference between grouping and filtering?
Filtering keeps some rows and discards the others. Grouping keeps every row and summarises it by category.
How big should a group be?
It depends on how much the values vary and how precise you need to be. A group of five is a warning level here, but a medical study would demand far more.
Can I group by two columns at once?
Yes, but the groups get smaller quickly. Check the row counts after every extra split.
