Count or rate? How to compare groups of different sizes

One question, three answers: compare branches by count and by rate, and decide which one answers what you need.

Free course. Your Maestro, an AI, .

Imagine you ask your assistant "which branch has the most cancellations?" With the hardware store's file there are at least three honest answers, and all three come from the same rows:

Zapopan, with 8, if it counts canceled rows.
Centro, with 4, if it counts canceled tickets.
Tonalá, with 25%, if it divides canceled tickets by each branch's tickets.

You already know to rule out the first: it counts product lines, and a canceled ticket with four products counts four times. The other two are correct and answer different questions. "More cancellations" can mean more in total or more often.

Centro is the branch with the most tickets in the month, 40. It is normal for it to have more of almost everything: more sales, more returns, more cancellations. Tonalá has 12 tickets and 3 were canceled: one in four. If you want to know where a process is breaking down, the rate is what tells you. Go through the chart:

Step 1 of 4

Three ways of counting

Each panel answers "which branch has the most cancellations?" with a different count. Before going on, find the longest bar in each panel.

Count or rate?

It depends on what you are going to decide with the number:

A count is for volume decisions: which branch gets the most return work, where the most money is lost in total.
A rate is for comparing groups of different sizes: where something happens more often, where a process fails more.

When the AI gives you a "most," ask yourself which of the two you need. If it doesn't say, ask it which one it calculated: a "most" with no base could be either.

A warning about rates with few cases. Tonalá has 12 tickets. If one fewer had been canceled, its rate would be 16.7%; one more, 33.3%. A rate with a small base jumps around a lot, so report it with its numbers: "3 of 12 (25%)." That way readers can judge how much weight to give it. How do I know if my base is too small?

The rate, in pandas

For each branch you need two counts at the same level, tickets, and then a division:

Python

total = data.groupby("sucursal")["ticket"].nunique()        # tickets per branch
cancelados = data[data["estado"] == "cancelado"]           # only the canceled rows
cancelados = cancelados.groupby("sucursal")["ticket"].nunique()  # canceled tickets per branch
tasa = cancelados / total * 100                            # percentage per branch

On top and on the bottom you count distinct tickets, never rows. If you count rows on top and tickets on the bottom, the division mixes two grains.

You already know everything here: groupby groups by branch, nunique counts distinct tickets, and the filter keeps only the canceled ones. What is new is that pandas divides branch by branch: Centro's 4 over Centro's 40, Tonalá's 3 over Tonalá's 12.

Try it

The starter code prints what an AI that only counts would give you: canceled tickets per branch. Add the total tickets per branch and the rate. You are done when you see Tonalá on top with 25.0.

Add the total tickets per branch and calculate the percentage of canceled tickets. Print the rate rounded to one decimal and sorted from highest to lowest with .sort_values(ascending=False). You are done when Tonalá appears first, with 25.0.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: ventas-marzo, 312 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "ventas-marzo". Your edits run with your code.
Row											Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 16

Run it yourself

Count or rate? How to compare groups of different sizes

Count or rate?

The rate, in pandas

Try it

Check yourself: You want to know in which branch a sale is most likely to be canceled. Which number answers that?