Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Cleaning and Preprocessing with Python

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 2 — Clean the Data

Period 8 · Duplicates and Inconsistent Labels

Print / Save as PDF
Lecture video — Period 8: Duplicates and Inconsistent Labels

Defining a transaction key, finding exact and key duplicates, standardising labels, and keeping a cleaning log.

Lesson overview

In this period, we will clean duplicate records and inconsistent labels. Both problems can change a sales report. A repeated transaction can increase the total quantity. Different names for the same region can split one group into several groups. We will work through a small sales example and keep the original data. By the end, you should be able to explain what makes a record a duplicate, standardize text using clear rules, and check the result. Our main question is simple: do these rows describe the same business event?

Study notes

By the end of this lesson you can:

  • Explain what makes two rows a duplicate, based on the business key.
  • Detect exact duplicates and repeated keys and explain what each count means.
  • Standardize region text with strip, casefold and a regular expression, then apply an approved mapping.
  • Find labels that are not in the mapping dictionary.
  • Separate clean rows from conflicting rows and validate the result with count checks.

Key idea: Ask "do these rows describe the same business event?" Clean labels first, then check duplicates again, and keep any conflict you cannot resolve with evidence in a review table.

The example and the key

The lecture uses six rows, A to F (the letters are only for discussion). Each transaction ID should appear once:

  • Rows A and B are identical.
  • Row C has the same ID and quantity as A, but the region is written in a different format.
  • Rows D and E share ID T02 but have different quantities.
  • Row F is a separate transaction.

customer_id would be a poor key, because one customer can buy many times. For order lines you would need a more detailed key.

Exact duplicates and repeated keys

df.duplicated() compares column values (it ignores the DataFrame index). With the default setting it marks later copies, so it finds 1 row (B). With keep=False, both A and B are marked, giving 2. One count is "removable copies under an agreed rule"; the other is "rows involved in duplicate groups". Inspect the rows and confirm that the copy came from duplicate recording before deleting anything.

Checking only transaction_id with keep=False gives 5 rows to inspect: three for T01 and two for T02. A repeated key tells you that the rule "one row per transaction" is broken, but not which row is correct. Keeping the first or the last row would simply follow the file order. For T02, you need evidence, such as a verified source record or an agreed version rule. Until then, keep the conflicting rows in a review table.

Standardizing labels

Work on a copy, using the pandas string dtype so missing values stay missing:

  1. str.strip() removes spaces at both ends;
  2. str.casefold() gives a consistent letter case (a stronger form of lower case);
  3. str.replace(r"\s+", " ", regex=True) turns any run of whitespace inside the text into a single space.

After these steps, " North " and "NORTH" both become north. But the abbreviation N. becomes n.: formatting rules cannot decide what an abbreviation means. That needs a mapping dictionary confirmed by the data owner, such as the course's REGION_MAP = {"north": "North", "n.": "North", "south": "South", "s.": "South", "central": "Central"}.

map() converts each known form to its standard label. A value outside the dictionary becomes missing in the result, so identify it separately: the unknown mask is "present before mapping and missing after mapping". This avoids confusing an unknown label with an original missing value. Keep unknown labels for review and update the dictionary only after verification.

Check again, separate and validate

Formatting differences can hide duplicates, so check duplicates again on the cleaned business columns (not the raw label or a source row number). Now A, B and C are identical; keep A and log B and C as removed copies. Four rows remain. T02 still conflicts, so both T02 rows go to the pending table; T01 and T03 form the provisional clean subset. Report the limitation: without T02, the clean subset cannot support a complete sales total. Never choose a T02 quantity or add the two quantities together without evidence.

Final checks: 6 input rows = 2 removed copies + 2 pending + 2 clean; clean IDs are non-missing and unique; all regions are approved labels.

Worked example

Labels and quantities are illustrative but follow the lecture's six-row structure.

import pandas as pd

raw = pd.DataFrame({
    "row": list("ABCDEF"),
    "transaction_id": ["T01", "T01", "T01", "T02", "T02", "T03"],
    "region": ["North", "North", " NORTH ", "South", "south", "N."],
    "quantity": [2, 2, 2, 3, 5, 4],
})
cols = ["transaction_id", "region", "quantity"]
work = raw.copy()
print(work.duplicated(subset=cols).sum())                          # 1 (row B)

region = (work["region"].astype("string").str.strip().str.casefold()
          .str.replace(r"\s+", " ", regex=True))
REGION_MAP = {"north": "North", "n.": "North", "south": "South", "central": "Central"}
work["region_clean"] = region.map(REGION_MAP)
unknown = region.notna() & work["region_clean"].isna()
print(unknown.sum())                                               # 0

business = ["transaction_id", "region_clean", "quantity"]
copies = work.duplicated(subset=business)
work = work[~copies]
conflict = work.duplicated(subset=["transaction_id"], keep=False)
clean, pending = work[~conflict], work[conflict]
print(int(copies.sum()), len(pending), len(clean))                 # 2 2 2
assert int(copies.sum()) + len(pending) + len(clean) == len(raw)
assert clean["transaction_id"].is_unique
print(clean["row"].tolist(), pending["row"].tolist())              # ['A', 'F'] ['D', 'E']

Expected output: 1 exact duplicate before cleaning, 0 unknown labels, then 2 removed copies (B and C), 2 pending rows (D and E for T02) and 2 clean rows (A and F). Log the label changes, the removal of B and C, and the unresolved T02 conflict.

Common mistakes

  • Deduplicating before cleaning labels. North and " NORTH " look different, so copies stay hidden; standardize first, then check again.
  • Using keep="first" to solve a conflict. That only follows the file order; conflicting rows need evidence or review.
  • Letting formatting decide meaning. n. needs an approved mapping, not a guess.
  • Including audit columns in the duplicate check. A raw-label column or row number makes true copies look different.

Summary

  • Define the key from what one row represents; customer_id is not a transaction key.
  • duplicated() marks later copies; keep=False marks every row in a group; the counts answer different questions.
  • Standardize text with strip, casefold and \s+ replacement; add meaning with an approved map dictionary.
  • Detect unknown labels as present-before and missing-after mapping.
  • Re-check duplicates on cleaned business columns, keep conflicts in review, and validate with count checks and a cleaning log.

Flashcards · Duplicates and Inconsistent Labels

9 words
WordMeaningExample
  • Exact duplicate

    A row whose values are identical to an earlier row in every compared column.

    Rows A and B: T01, North, 2.

  • Key conflict

    Two or more rows share the business key but disagree on other values.

    T02 recorded with quantity 3 and quantity 5.

  • drop_duplicates()

    Removes rows marked as duplicates, keeping the first occurrence by default.

    `work.drop_duplicates(subset=business)`

  • str.casefold()

    Converts text to a consistent case for comparison, more thoroughly than `lower()`.

    `"NORTH"` becomes `"north"`.

  • Regular expression \s+

    A pattern matching one or more whitespace characters, used to collapse inner spaces.

    `str.replace(r"\s+", " ", regex=True)`

  • Mapping dictionary

    An approved lookup that turns each known label form into one standard label.

    `{"north": "North", "n.": "North"}`

  • Series.map()

    Replaces each value using a dictionary; values not in the dictionary become missing.

    `region.map(REGION_MAP)`

  • Unknown mask

    Flags labels that were present before mapping but have no approved match after mapping.

    `region.notna() & work["region_clean"].isna()`

  • Review table

    A separate table that keeps rows you cannot resolve without evidence, instead of deleting them.

    Both T02 rows wait for a verified source record.