Why doesn't the AI's table add up to your file's total?
Reconcile the total of a table the AI gave you against the file and find the rows that were left out.
Free course. Your Maestro, an AI, .
Imagine you ask your assistant for "March sales by branch" and it answers with a table:
| Branch | Amount |
|---|---|
| Centro | $22,228 |
| Chapalita | $44,084 |
| Tlaquepaque | $19,019 |
| Tonalá | $4,713 |
| Zapopan | $27,644 |
Every line is correctly calculated. Now add them up: $117,688. On the calculator page you summed the whole importe column and got $118,196. That is $508 missing. No branch is wrong, and still the table doesn't match the file.
This is the second way something gets left out: rows that were in the file and didn't make it to the final table. It happens for two reasons. One is an empty cell in exactly the column you group by. The other is a filter the AI applied without saying so. You catch both with the same habit: compare the answer's total against the file's total. In accounting that is called Glossary term, and you don't need to be an accountant to do it.
First, a warm-up with four rows.
Four sales, and one has no branch. Write what you think each program prints, and why, before you run it.
Four sales, and one has no branch. Write what you think each program prints, and why, before you run it.
The total and the sum by branch
Example 1 of 2
import pandas as pd
tabla = pd.DataFrame({
"sucursal": ["Centro", "Centro", None, "Zapopan"],
"importe": [100, 200, 50, 300],
})
print(tabla["importe"].sum())
print(tabla.groupby("sucursal")["importe"].sum().sum())
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 2, opens once you explain this one
Why they get lost
groupby is the pandas version of a PivotTable: it groups the rows by the value of a column and calculates something for each group. What it doesn't tell you is that, by default, it skips the rows where that column is empty. The pandas documentation explains it under its dropna parameter: it is True unless you say otherwise, and groups with an empty value are dropped. If the AI's code uses groupby with its defaults, those rows get lost without warning.
In the hardware store's file, ticket T-1058 has three products and no branch entered. It adds up to $508. That is why the table by branch doesn't match. Why does groupby skip the rows with no branch?
Losing those rows is not always an error. If you are asked to compare branches, a ticket with no branch doesn't help you compare. What you can't do is report the table as if it were all of March. The honest sentence is something like "sales by branch; $508 from a ticket with no branch are not shown."
total = data["importe"].sum() # the file's total
por_sucursal = data.groupby("sucursal")["importe"].sum() # the AI's table
print(total - por_sucursal.sum()) # what didn't make it
data[data["sucursal"].isna()] # the rows with no branch
data.groupby("sucursal", dropna=False)["importe"].sum() # the table with a line for the empty onesGet the file's total
Add up the whole column, with no filters or grouping:
data["importe"].sum().Add up the answer
Add up the lines of the table the AI gave you, by hand or with
.sum().Look at the difference
If it is zero, it adds up. If not, something was left out and you already know how much.
Look for the rows
First, empty cells in the column it grouped by. Then, filters: statuses, dates, categories.
The filter nobody announced
Now imagine another answer, to the question "how much did we sell in March?": "In March you sold $108,567." It isn't $118,196. This time no branch is missing: the AI left out the rows with estado equal to cancelado, 27 lines from 11 tickets. That may be exactly what you wanted, because a canceled sale is not a sale. But the AI decided it for you and didn't say so.
A filter is a decision, not a technical detail. That is why, when a number doesn't add up, the question that pays off most is "did you filter anything?" If the AI showed you the code, look for the lines with brackets and ==, like the Zapopan filter from module 1. How do I find a hidden filter in the code the AI gave me?
Try it
The code gets the file's total, the sum by branch, and the difference. Run it with Run and confirm the $508. Then, in the "Your turn" lines, show the rows with no branch and calculate the total of paid sales, to find the $108,567 from the second answer.
Run the code and confirm that 508.0 is missing. Then print the rows with no branch (columns ticket, producto, and importe) and the total of the rows with estado "pagado". You are done when you see the three rows of ticket T-1058 and the paid total 108567.0.
Run the code and confirm that 508.0 is missing. Then print the rows with no branch (columns ticket, producto, and importe) and the total of the rows with estado "pagado". You are done when you see the three rows of ticket T-1058 and the paid total 108567.0.
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.
| Row | Remove | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | |||||||||||
| 2 | |||||||||||
| 3 | |||||||||||
| 4 | |||||||||||
| 5 | |||||||||||
| 6 | |||||||||||
| 7 | |||||||||||
| 8 | |||||||||||
| 9 | |||||||||||
| 10 | |||||||||||
| 11 | |||||||||||
| 12 | |||||||||||
| 13 | |||||||||||
| 14 | |||||||||||
| 15 | |||||||||||
| 16 | |||||||||||
| 17 | |||||||||||
| 18 | |||||||||||
| 19 | |||||||||||
| 20 |