Dedup by Key — Keep Latest - Data Cleaning Step by Step

Keep the most-recent row per business key. Given parallel lists of ids, dates, and values — where the same id appears more than once — return the record with the largest date for each id. By hand, scan the rows and overwrite a dict entry whenever a newer date is found. With pandas, sort by date then call drop_duplicates(keep='last') so the last survivor per id is the most recent.

By hand

Walk every row with a running latest dict keyed by id. For each row, compare the incoming date to the stored date (ISO strings compare lexicographically, which matches chronological order). Overwrite the entry whenever the new date is strictly later. At the end, sort by id and extract the value for each key. The trace shows latest updating on each overwrite: the second A row overwrites the first because '2024-01-15' > '2024-01-10', and the second B row overwrites the first because '2024-01-20' > '2024-01-05'. The C row is written once and never overwritten.

                            naive.py
                        
Replay: real traced execution (multi-file project)

                            ids    = ['A', 'B', 'A', 'B', 'C']
dates  = ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']
values = [100, 200, 110, 220, 300]
latest = {}
for i in range(len(ids)):
    rid, d, v = ids[i], dates[i], values[i]
    if rid not in latest or d > latest[rid][0]:
        latest[rid] = (d, v)
result = sorted((k, v[1]) for k, v in latest.items())
print('RESULT:', result)

                        
1ids ← ['A', 'B', 'A', 'B', 'C']1ids    = ['A', 'B', 'A', 'B', 'C']2dates  = ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']⋯values this step['A', 'B', 'A', 'B', 'C']ids
2dates ← ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']1ids    = ['A', 'B', 'A', 'B', 'C']2dates  = ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']3values = [100, 200, 110, 220, 300]⋯values this step['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']dates
3values ← [100, 200, 110, 220, 300]⋯2dates  = ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']3values = [100, 200, 110, 220, 300]4latest = {}⋯values this step[100, 200, 110, 220, 300]values
4latest ← {}⋯3values = [100, 200, 110, 220, 300]4latest = {}5for i in range(len(ids)):⋯values this step{}latest
5i ← 0⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯values this step0i
6d ← '2024-01-10', rid ← 'A', v ← 100⋯5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]7    if rid not in latest or d > latest[rid][0]:⋯values this step'2024-01-10'd'A'rid100v
7if rid not in latest or d > latest[rid][0]:⋯6rid, d, v = ids[i], dates[i], values[i]7if rid not in latest or d > latest[rid][0]:8    latest[rid] = (d, v)⋯
8latest ← {'A': ('2024-01-10', 100)}⋯7    if rid not in latest or d > latest[rid][0]:8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())⋯values this step{} → {'A': ('2024-01-10', 100)}latest
9i ← 1⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯values this step0 → 1i
10d ← '2024-01-05', rid ← 'B', v ← 200⋯5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]7    if rid not in latest or d > latest[rid][0]:⋯values this step'2024-01-10' → '2024-01-05'd'A' → 'B'rid100 → 200v
11if rid not in latest or d > latest[rid][0]:⋯6rid, d, v = ids[i], dates[i], values[i]7if rid not in latest or d > latest[rid][0]:8    latest[rid] = (d, v)⋯
12latest ← {'A': ('2024-01-10', 100), 'B': ('2024-01-05', 200)}⋯7    if rid not in latest or d > latest[rid][0]:8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())⋯values this step{'A': ('2024-01-10', 100)} → {'A': ('2024-01-10', 100), 'B': ('2024-01-05', 200)}latest
13i ← 2⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯values this step1 → 2i
14d ← '2024-01-15', rid ← 'A', v ← 110⋯5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]7    if rid not in latest or d > latest[rid][0]:⋯values this step'2024-01-05' → '2024-01-15'd'B' → 'A'rid200 → 110v
15if rid not in latest or d > latest[rid][0]:⋯6rid, d, v = ids[i], dates[i], values[i]7if rid not in latest or d > latest[rid][0]:8    latest[rid] = (d, v)⋯
16latest ← {'A': ('2024-01-15', 110), 'B': ('2024-01-05', 200)}⋯7    if rid not in latest or d > latest[rid][0]:8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())⋯values this step{'A': ('2024-01-10', 100), 'B': ('2024-01-05', 200)} → {'A': ('2024-01-15', 110), 'B': ('2024-01-05', 200)}latest
17i ← 3⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯values this step2 → 3i
18d ← '2024-01-20', rid ← 'B', v ← 220⋯5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]7    if rid not in latest or d > latest[rid][0]:⋯values this step'2024-01-15' → '2024-01-20'd'A' → 'B'rid110 → 220v
19if rid not in latest or d > latest[rid][0]:⋯6rid, d, v = ids[i], dates[i], values[i]7if rid not in latest or d > latest[rid][0]:8    latest[rid] = (d, v)⋯
20latest ← {'A': ('2024-01-15', 110), 'B': ('2024-01-20', 220)}⋯7    if rid not in latest or d > latest[rid][0]:8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())⋯values this step{'A': ('2024-01-15', 110), 'B': ('2024-01-05', 200)} → {'A': ('2024-01-15', 110), 'B': ('2024-01-20', 220)}latest
21i ← 4⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯values this step3 → 4i
22d ← '2024-01-08', rid ← 'C', v ← 300⋯5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]7    if rid not in latest or d > latest[rid][0]:⋯values this step'2024-01-20' → '2024-01-08'd'B' → 'C'rid220 → 300v
23if rid not in latest or d > latest[rid][0]:⋯6rid, d, v = ids[i], dates[i], values[i]7if rid not in latest or d > latest[rid][0]:8    latest[rid] = (d, v)⋯
24latest ← {'A': ('2024-01-15', 110), 'B': ('2024-01-20', 220), 'C': ('2024-01-08', 300)}⋯7    if rid not in latest or d > latest[rid][0]:8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())⋯values this step{'A': ('2024-01-15', 110), 'B': ('2024-01-20', 220)} → {'A': ('2024-01-15', 110), 'B': ('2024-01-20', 220), 'C': ('2024-01-08', 300)}latest
25for i in range(len(ids)):⋯4latest = {}5for i in range(len(ids)):6    rid, d, v = ids[i], dates[i], values[i]⋯
26result ← [('A', 110), ('B', 220), ('C', 300)]⋯8        latest[rid] = (d, v)9result = sorted((k, v[1]) for k, v in latest.items())10print('RESULT:', result)values this step[('A', 110), ('B', 220), ('C', 300)]result
27stdout ← RESULT: [('A', 110), ('B', 220), ('C', 300)]⋯9result = sorted((k, v[1]) for k, v in latest.items())10print('RESULT:', result)values this stepRESULT: [('A', 110), ('B', 220), ('C', 300)]stdout

With pandas

Sort the full DataFrame by date ascending, then call drop_duplicates('id', keep='last'). After sorting, the last occurrence of each id is the one with the latest date, so keep='last' retains exactly the right row. A final sort_values('id') makes the output order deterministic.

                            library.py
                        

                            import pandas as pd
from dalib.display import set_display
set_display()

ids    = ['A', 'B', 'A', 'B', 'C']
dates  = ['2024-01-10', '2024-01-05', '2024-01-15', '2024-01-20', '2024-01-08']
values = [100, 200, 110, 220, 300]
df = pd.DataFrame({'id': ids, 'date': dates, 'value': values})
clean = df.sort_values('date').drop_duplicates('id', keep='last')
clean = clean.sort_values('id')
result = list(zip(clean['id'].tolist(), clean['value'].tolist()))
print('columns:', clean.columns.tolist())
print('shape before:', df.shape)
print('shape after:', clean.shape)
print('RESULT:', result)

                        

columns: ['id', 'date', 'value']
shape before: (5, 3)
shape after: (3, 3)
RESULT: [('A', 110), ('B', 220), ('C', 300)]

Implementation notes

ISO date strings (YYYY-MM-DD) sort lexicographically in the same order as chronologically, so string comparison is valid for date ordering without parsing.
drop_duplicates(subset, keep='last') relies on row order; always sort_values by the date column first so "last" really means "latest".
The two-step pattern (sort_values → drop_duplicates(keep='last')) is the standard pandas idiom for latest-per-key deduplication.
Cross-reference: drop-duplicates-keep-first (this chapter) for the simpler case where any occurrence is equally valid and you just need one.