๐Ÿ‘ฉโ€๐Ÿ’ผ
Meet Aisha
Fintech analyst, Nairobi

It's Monday morning. Aisha logs into the M-Pesa pipeline dashboard and sees "Weekend transactions: 8,432." But her operations team just reported 5,803 unique transactions for the same weekend. That's a 45% overcount.

She digs into the raw export: some transaction records have been ingested twice. Same `txn_id`, same customer, amount, and merchant โ€” but captured at two different timestamps because the pipeline retried the ingestion. The database shows every copy. Her analysis will be wrong until she deduplicates.

8,432
Reported count
5,803
Actual unique txns
2,629
Duplicates (31%)

The duplicates problem

Weekend transaction count: before and after deduplication
Before and after deduplication row counts
What this shows: Aisha's weekend dataset had 12,847 rows but only 10,392 were unique. Exact duplicates (rows where every column is identical) accounted for 2,455 rows โ€” 19% of the data.

Sample duplicate rows

#txn_idcustomer_idmerchantamount_kshtimestamp
2TXN20260411002C-1002Carrefour28002026-04-11 19:45
3TXN20260411002C-1002Carrefour28002026-04-11 19:45
5TXN20260411005C-1005Chandarana Foodplus8902026-04-11 22:10
6TXN20260411005C-1005Chandarana Foodplus8902026-04-11 22:10
Key insight: Rows 2 and 3 are exact duplicates โ€” every column is identical. These are easy to spot. But notice later in the dataset you'll find "Java House" vs "JAVA HOUSE" vs "Java House " (with trailing space) โ€” these are near-duplicates that require normalization before dedup.

What counts as a duplicate?

Not all duplicates look the same. Here are the four kinds Aisha encountered:

TypeExampleHow to catchSeverity
Exact row duplicate Same txn_id, customer, amount, timestamp df.duplicated() โœ… Easy
Duplicate by key Same txn_id but different amounts (data error) df.duplicated(['txn_id']) โš ๏ธ Investigate
Near-duplicate (case/space) "Java House", "java house", "JAVA HOUSE" Normalize first, then dedup โš ๏ธ Common
Semantic duplicate Same txn_id, different ingest timestamps (pipeline retry) drop_duplicates(['txn_id'], keep='first') ๐Ÿ”ด High

Finding duplicates โ€” the 4 commands

Pandas gives you four ways to find duplicates. Let's walk through each with Aisha's weekend data.

1. Count total duplicates

Start by seeing how many rows are duplicated:

Total duplicate count
Duplicate count by column subset
๐Ÿ“ count_duplicates.py
How it works: df.duplicated() returns a boolean Series marking each row as True (duplicate) or False (first occurrence). .sum() counts the True values.

2. See every duplicate row

To inspect what's duplicated, show all copies (original + duplicates):

๐Ÿ“ see_all_duplicates.py
Key detail: keep=False marks all copies as duplicates (including the first). Without it, only the 2nd+ copies are marked. This is useful for inspection but not for removal.

3. Check by specific columns

Sometimes you only care about duplicates in certain columns (e.g., same transaction ID, regardless of merchant name typos):

Duplicates by column subset
Duplicate count by column subset combination
๐Ÿ“ duplicates_by_subset.py
Why subsets matter: If two rows have the same txn_id but different amounts, that's a data quality bug. By checking subsets, you can spot these issues before they corrupt your analysis.

Aisha's real problem: same txn, 2 pipeline ingests

The weekend double-count wasn't caused by copy-paste errors in the database. It was caused by the data pipeline retrying ingestion. One transaction got stored twice with slightly different timestamps.

The problem: same txn_id, different ingest_timestamp

txn_idcustomer_idmerchantamount_kshtimestampingest_timestamp
TXN20260411020C-1020Tuskys22002026-04-13 08:152026-04-13 08:15:00
TXN20260411020C-1020Tuskys22002026-04-13 08:152026-04-13 08:18:00
The issue: Same transaction (TXN20260411020) ingested at 08:15 and retried at 08:18. The database stored both. Aisha needs to keep only one โ€” typically the earliest ingest.

The solution: drop_duplicates with keep='first'

Sort by ingest timestamp, then drop duplicates keeping only the first (earliest) ingest:

๐Ÿ“ fix_double_ingest.py

The result: weekend count now matches operations

Dashboard count vs operations count: now aligned
Data source discrepancy comparison
Result: After deduplication, Aisha's dashboard and operations team now agree: 5,803 unique weekend transactions. The pipeline retry duplicates are gone.

Near-duplicates: normalize before deduping

Exact duplicates are easy to catch. But Aisha also found merchant names spelled differently: "Java House", "JAVA HOUSE", "Java House " (with trailing space), and "javahouse". These are the same merchant but stored as different strings.

The problem: merchants with inconsistent case and spacing

customer_idmerchantamount_kshdate
C-1001Java House4502026-04-11
C-1007java house5002026-04-12
C-1031JAVA HOUSE4902026-04-13
C-1048JAVA HOUSE 5102026-04-14
The issue: All four rows are the same merchant but Python sees them as 4 different strings. This breaks groupby and pivot operations downstream.

The solution: normalize strings before dedup

Use .str.lower() and .str.strip() to normalize case and whitespace:

๐Ÿ“ normalize_merchants.py

After normalization: 4 rows collapse to 1 unique value

customer_idmerchantamount_kshdate
C-1001java house4502026-04-11
C-1007java house5002026-04-12
C-1031java house4902026-04-13
C-1048java house5102026-04-14
Now they match: All four rows show "java house" (lowercase, no spaces). Now if Aisha groups by merchant, all Java House transactions will be counted together.

Dedup within groups: latest per customer

Sometimes you don't want to remove all duplicates โ€” you want to keep one row per group. For example, Aisha wants the latest transaction per customer this week:

๐Ÿ“ latest_per_customer.py

Result: latest transaction per customer

customer_idmerchantamount_kshtimestamp
C-1050Quickmart12002026-04-15 08:30
C-1049Carrefour27702026-04-14 22:30
C-1048JAVA HOUSE5102026-04-14 21:10
C-1047Tuskys21702026-04-14 20:15
C-1046Chandarana Foodplus8752026-04-14 19:30
Key trick: drop_duplicates(subset=['customer_id'], keep='first') combined with a sort on timestamp descending gives you the latest per group. Use keep='last' if you want the earliest instead.
๐Ÿ“„
Sample data to follow along
Save next to your .py file so pd.read_csv('mpesa_weekend.csv') works.

Cheatsheet: 10 duplicate commands

Task Command Notes
Count duplicates df.duplicated().sum() Returns count of duplicate rows (not including first)
See all duplicates df[df.duplicated(keep=False)] Shows all copies, including the first
Duplicates in one column df.duplicated(['txn_id']) Find rows with duplicate txn_id, ignore other columns
Duplicates in multiple columns df.duplicated(['txn_id', 'amount']) Find rows with same txn_id AND amount
Remove exact duplicates df.drop_duplicates() Keep first occurrence of each row
Remove by column, keep last df.drop_duplicates(['txn_id'], keep='last') For each txn_id, keep the last row only
Remove by column, keep first df.drop_duplicates(['txn_id'], keep='first') For each txn_id, keep the first row only
Normalize strings before dedup df['col'] = df['col'].str.strip().str.lower() Remove whitespace and make lowercase
Get duplicate report df.value_counts() Count occurrences of each row combination
Drop duplicates in-place df.drop_duplicates(inplace=True) Modifies df directly; returns None

Common pitfalls

โŒ Forgetting to sort before keep='last': If you want the latest timestamp, always df.sort_values('timestamp') first. Otherwise "last" means last in file order, not last chronologically.
โŒ Dropping all duplicates instead of excess: df.drop_duplicates() keeps the first, which is correct. But if you use keep=False (meant for inspection), you'll delete all copies including the valid one.
โŒ Case-sensitive string matching: "Java House" โ‰  "java house" in Python. Always normalize with .str.lower() before grouping or deduping by merchant/customer name.
โŒ Forgetting the subset parameter: df.drop_duplicates(['txn_id']) removes the entire row if txn_id is duplicated. If you only want to check one column, use subset=.
โŒ Dedup without checking for data quality: Before removing duplicates by a key, verify the duplicates have identical values in other columns. If not, you might be deleting valid records with a data quality issue.

Mini challenge

Load the mpesa_weekend.csv file and complete the following:

  1. Find the total number of exact duplicate rows.
  2. Show all duplicate rows where txn_id is duplicated (use keep=False).
  3. Normalize the merchant column (lowercase + strip) and find how many near-duplicates collapse into unique merchants.
  4. Remove duplicates by txn_id keeping the first ingest, then report the final row count.
  5. Calculate the final weekend transaction count and compare to Aisha's ops report (5,803).
๐Ÿ“ challenge_solution.py

Interview questions

Easy What's the difference between df.duplicated() and df.duplicated(keep=False)? +
df.duplicated() marks every row except the first occurrence as True (default keep='first'). df.duplicated(keep=False) marks all copies (including the first) as True. Use the default for counting/removing; use keep=False for inspection when you want to see all related rows.
Easy How do you remove exact duplicate rows? +
df.drop_duplicates() removes all rows that are exact duplicates of a previous row, keeping the first occurrence by default. For example: df_clean = df.drop_duplicates(). This works on all columns; you can also specify a subset.
Medium Your data has case and whitespace inconsistencies in a merchant column. How would you normalize before deduping? +
Use df['merchant'] = df['merchant'].str.strip().str.lower() to remove leading/trailing whitespace and convert to lowercase. Then apply df.drop_duplicates(subset=['merchant']) to find unique merchants after normalization. The string methods .str.strip() and .str.lower() chain together to clean the entire column.
Medium What's the difference between keep='first' and keep='last' in drop_duplicates()? +
keep='first' keeps the first occurrence and removes later duplicates. keep='last' keeps the last occurrence and removes earlier ones. Combined with a sort, keep='last' lets you keep the most recent row: df.sort_values('date').drop_duplicates(['id'], keep='last') gives you the latest date for each ID.
Hard You have a dataset with duplicate transactions where the same txn_id appears twice with different ingest_timestamps (pipeline retries). How would you deduplicate, and why is the sort order important? +
Sort by ingest_timestamp ascending (earliest first), then use df.drop_duplicates(subset=['txn_id'], keep='first'). This keeps the first (earliest) ingest. The sort order is crucial because drop_duplicates() keeps by position in the DataFrame, not by value. Without sorting, you'd randomly keep whichever row happens to come first in the file, losing the semantic "earliest ingest" logic. Always sort before deduping when the "which copy to keep" decision depends on a column value (timestamp, date, version).
๐Ÿค– Why AI/ML cares about duplicates: Machine learning models assume each training example is independent and appears once. Duplicates bias the model (the duplicated class gets over-weighted). Deduplication is often the first step before splitting train/test sets. In production, duplicate detection can also catch data pipeline bugs before they corrupt model training.

Used in: 3 domains

๐Ÿฆ Fintech

Detecting pipeline double-ingests (like Aisha's M-Pesa case) and reconciling transaction counts across systems.

๐Ÿ›’ E-commerce

Removing duplicate order records, customer signup duplicates, and merging near-duplicate product listings.

๐Ÿ“Š Telecom

Deduplicating call detail records (CDRs) when network equipment logs the same call twice, or billing records with retry ingests.

Quiz: test your understanding

5 questions
1. Which method counts the total number of duplicate rows (not including first occurrences)?
2. You want to see all rows with duplicate txn_id values, including the first. Which code works?
3. To normalize merchant names before deduping, which two string methods should you chain?
4. You want to keep the latest transaction per customer. How should you order the code?
5. Your dataset has 10,000 rows. After df.drop_duplicates(subset=['txn_id']), you have 8,500 rows. How many rows had duplicate txn_id values (including the first)?
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Ready to go further?

Join the Best AI Program: The AI-Era Analyst โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst โ†’