How do you calculate the average ticket when one ticket spans several rows?

The AI in the example said "average ticket of $378.83." You already know where it came from: it is the average of the importe column, and in this file every row is a product. So $378.83 is what one product line costs on average. What your boss asked for is what one person at the register pays on average.

Think of a grocery run. The average price of the items in your cart is not what you pay on average per cart. If you buy twenty items at $30 each, the average per item is $30 and the cart costs $600. Same cart, two averages, two questions.

From lines to tickets

For the average ticket you have to change level before averaging:

Group the rows of each ticket
All the lines of ticket T-1025 go together, all the lines of T-1038 go together, and so on for every ticket.
Add up the importe of each ticket
Now you have a new table with one row per ticket and its total. You changed the grain: from product to ticket.
Average those totals
The average of the totals per ticket is the average ticket. It is what one person at the register pays, on average.

In pandas, the first two steps are a single line with groupby, which means "group by." If you use Excel, it is the same as a PivotTable with the ticket in Rows and the sum of importe in Values:

Python · line 1

por_ticket = data.groupby("ticket")["importe"].sum()   # one row per ticket, with its total
len(por_ticket)                                         # how many tickets there are
por_ticket.mean()                                       # the average ticket

groupby doesn't delete or change anything in data: it builds a new, more summarized table.

Read line 1 from left to right: "group data by ticket, take the importe column, and add it up." The result has one row per ticket. On that table, .mean() is already the average per ticket.

When the error changes the winner

An average at the wrong level doesn't just give a smaller number. It can also change who wins a comparison. Imagine you ask your assistant "which branch has the highest average ticket?" and it answers: "Tlaquepaque, with $864.50." The bars compare both averages for each branch, calculated in the sandbox on the file as exported. Go through it step by step:

Step 1 of 4

Two averages per branch

Each branch has two bars built from the same data: the average per product line and the average per ticket. Before going on, guess which of the two the AI used.

The assistant's math wasn't wrong. It calculated the wrong average correctly, and that put Tlaquepaque in first place when, per ticket, it comes third. If that answer reached a meeting, someone might copy at Chapalita what "works" at Tlaquepaque.

Try it

Calculate the month's average ticket with groupby, and then Chapalita's. The first number should land well above the AI's $378.83. I'm not clear on what groupby does.

The first part prints the average per line, the one the AI gave. Complete the second: group by ticket, add up the importe, and average. Then calculate the average ticket for Chapalita only. You are done when you see the three numbers: per line, per ticket for the month, and per ticket for Chapalita.

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

You should see an average ticket of $960.94 for the month: more than double what the AI said. And $2,449.11 in Chapalita. It is not yet the final number for your boss, because the file has canceled tickets and repeated rows, and module 4 is about that. But the biggest error is already fixed: now you are averaging tickets.

One more caution: an average ticket also has a base. Tonalá's comes from 12 tickets and Chapalita's from 18, so a single very large ticket moves a branch's average a lot. When you report an average ticket per branch, say how many tickets it comes from.

Common mistake: The average trap

In a table with several rows per thing (products per ticket, visits per patient, pay periods per employee), the average of a column is the average of the rows. If the question is about the thing, first add up per thing and then average.

Check yourself: You review the AI's code for "the average ticket per branch" and see `data.groupby("sucursal")["importe"].mean()`, on a table with one row per product. What happened?

Reflect: Think of a table from your work that has several rows for each thing you care about (orders, patients, employees, customers). What average have you been given on it, and at what level do you think it was calculated?

Only you and your Maestro read this. It's kept on your account and deleted with it.

Write your answer