How do you find duplicate rows that inflate a total?

The previous two pages were about what wasn't counted. This one is about the opposite: what was counted twice.

Imagine you ask your assistant "how much did Zapopan sell in March?" and it answers "$27,644." You recalculate it with the filter from module 1 and get $27,644. It matches. And yet it is inflated: in the file, three Zapopan tickets have products entered twice, in rows that are identical. Your calculation and the AI's match because both added up the same duplicate rows.

Duplicate rows come from very ordinary office mishaps: someone exported the report twice and pasted one under the other, a sync ran twice, or two people entered the same ticket. An AI may not look for them unless you ask: to the code, every row is just one more data point.

Finding them takes three lines.

Python

data.duplicated().sum()                  # how many rows are an exact copy of an earlier one
data[data.duplicated(keep=False)]        # all the rows that have a copy, the original included
limpio = data.drop_duplicates()          # a new table, without the copies

duplicated() compares all the columns: a row counts as a duplicate only if it is identical in every column.

How to read them:

data.duplicated() answers, for each row, is it identical to one that already appeared above? The first time a row appears it says no; the copy says yes. With .sum() you count how many copies there are.
keep=False changes the question to does this row have a twin?, so it marks both the original and the copy. It is useful for seeing the pairs side by side.
drop_duplicates() builds a new table without the copies and keeps the first of each pair. It doesn't touch data: the original stays as it was in case you need to go back.

The pandas documentation describes these two parameters. subset lets you compare only some columns, and keep decides which of the duplicates stays. What is the difference between duplicated() and drop_duplicates()?

pandas.DataFrame.drop_duplicates (pandas documentation): subset and keep

How much the duplicates inflate Zapopan's total

Worked example · 4 steps

The AI said Zapopan sold $27,644 in March. You want to know whether there are duplicate rows in the file and how much they change that number. Try each step before opening it.

Work out the next step yourself, then open it to compare.

You find identical rows
Can they really repeat?
For example, two scans of the same product on one ticket
- Path 1 of 2: No
  1. Remove them with drop_duplicates
  Continues at: Report how many rows and how much money
- Path 2 of 2: Maybe
  1. Ask whoever enters the data
    If they can repeat, keep them
  Continues at: Report how many rows and how much money
Report how many rows and how much money

Whether you remove them or keep them, what matters is saying how many rows you touched and how much the total changed.

Identical doesn't always mean a copy

Before deleting anything, go back to the first question of the course: what is a row? In this file a row is a product within a ticket. If the register records each scan separately, someone who buys two identical brushes in two scans generates two identical rows, and both are real sales. If the register merges identical products into a single row with a quantity, two identical rows really are a copy.

The file doesn't tell you; whoever knows the system does. Here, ticket T-1033 is duplicated in full, as if it had been pasted twice. In T-1011, T-1093, and T-1079 only one or two products repeat, and on its own that could be a copy or two scans. Imagine you ask the IT folks and they tell you the register merges identical products into one row with a quantity: then two identical rows can't be two sales, and all six are copies. Even so, in a report write down what you did: "I removed 6 identical rows, $937; without them the total is $117,259." That way anyone who reads it can undo your decision if they know something you don't. How do I know whether two identical rows are a copy or two sales?

Try it

This time almost none of the code is written for you. Put together the three pieces you already know: removing copies, filtering by branch, and filtering by status.

Calculate what Zapopan sold in March counting only paid sales and without duplicate rows. Print how many rows are left, what they add up to, and how many distinct tickets they are. You are done when the total is 24340.0 and 28 tickets are left.

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

From $27,644 to $24,340. The difference doesn't come from one thing: $653 is copies and the rest is canceled sales. If you had checked only one of the two, the number would still be inflated. That is why the three questions go together, and in module 5 you ask them of a complete answer. Help me work out how much comes from copies and how much from canceled sales.

How do you find duplicate rows that inflate a total?

Identical doesn't always mean a copy

Try it

Check yourself: In a sales file you find two identical rows: same ticket, same product, same quantity, and same amount. What do you do?