How do I keep the latest entry for each folio in pandas?

With the folio as the key you know which rows are the same sale. What's left is deciding which copy stays. In the table from the previous page, drop_duplicates(subset="folio") kept the 5-kilo V-4, the wrong entry, because by default it keeps the first one it finds.

In the cooperative, Marisol confirms the rule: when a folio appears twice, the most recent entry is the correction and it's the one that counts. In pandas that sounds like keep="last". Predict whether that's enough.

keep="last" without sorting

Example 1 of 2

example.py

import pandas as pd

ventas = pd.DataFrame({
    "captura": [6, 4, 1],
    "folio": ["V-4", "V-4", "V-1"],
    "kilos": [50, 5, 40],
})

print(ventas.drop_duplicates(subset="folio", keep="last"))

The table is not in entry order. How many kilos does V-4 end up with?

Why?

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

Make your prediction

One rule, written in the code

The complete pattern for the sales sheet looks like this:

Python

# Regla de Marisol: si un folio se repite, vale la captura más reciente.
antes = len(ventas)
ventas = (ventas
          .sort_values("captura")
          .drop_duplicates(subset="folio", keep="last"))
anota("folios repetidos", antes, len(ventas), "queda la última captura de cada folio")

The rule goes in a comment, the order is explicit and the log counts what went away.

Three things make this step defensible:

The rule is written down. Whoever reads the code knows why one copy stayed and not the other, and knows who to ask if the rule changes.
The order doesn't depend on luck. In the March sheet the captura column already comes in order, so today keep="last" would work without sorting. But if in April someone sorts the sheet by customer before sending it to you, the same code would keep the wrong copies with no error at all. sort_values costs one line and removes that possibility.
The log counts. The step should drop from 60 to 58, exactly the 2 repeated folios from the inventory. If it drops more or less, the key or the order isn't what you think.

Does your data have a modification date or a version number to decide which copy counts? Talk it over with your Maestro.

When there's no rule: decide case by case, in code

In the member list there's no captura column. Juana Martínez Cruz appears as 1008, with no phone, and as 1029, with a phone. Marisol confirms she's the same person, who registered twice, and that her member number is 1008. There's no general rule to apply: it's a decision about one case. Even so, it isn't fixed by hand in the sheet. It's written in the code, with the case named, so the next time you run the script the decision applies itself. Open the steps one by one.

Juana, registered twice

Worked example · 4 steps

The member list has Juana Martínez Cruz as 1008 (no phone) and 1029 (with a phone). Marisol confirms she's the same member and that her number is 1008. You want a single row, with her phone, and for the decision to stay in the code.

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

Try it

Now the full sales sheet. Remove the repeated folios with Marisol's rule and check the sales that matter. If you're not sure which row should have stayed, ask your Maestro.

Sort by captura, keep the last entry for each folio and count the rows before and after. Then print sales V-0017, V-0031, V-0040 and V-0041. You're done when 58 sales are left, V-0031 has 53 kilos and both V-0040 and V-0041 are still there.

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-cooperativa, 60 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-cooperativa". 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 3

Run it yourself

The code above already leaves 58 sales, and it's still wrong: it keeps the 5-kilo V-0031, entry 32. The row count doesn't warn you about this; what warns you is looking at the sales you know. With the complete rule, V-0031 ends up with 53 kilos (entry 36), V-0017 with its entry 18, and Hotel Mirador's two orders are still there.

Keep this habit for the rest of the course: after removing duplicates, besides counting, look up two or three cases you know by heart and see which copy they kept. It costs one line of code and catches errors that the count doesn't see.

Check yourself: A coworker writes `ventas.drop_duplicates(subset="folio", keep="last")` and tells you it already applies the rule "the most recent entry counts". The sheet they received was sorted by customer. What do you tell them?

pandas: DataFrame.drop_duplicates (subset and keep)