You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
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?
By the end of this lesson you can:
strip, casefold and a regular expression, then apply an approved mapping.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 lecture uses six rows, A to F (the letters are only for discussion). Each transaction ID should appear once:
customer_id would be a poor key, because one customer can buy many times. For order lines you would need a more detailed key.
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.
Work on a copy, using the pandas string dtype so missing values stay missing:
str.strip() removes spaces at both ends;str.casefold() gives a consistent letter case (a stronger form of lower case);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.
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.
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.
North and " NORTH " look different, so copies stay hidden; standardize first, then check again.keep="first" to solve a conflict. That only follows the file order; conflicting rows need evidence or review.n. needs an approved mapping, not a guess.customer_id is not a transaction key.duplicated() marks later copies; keep=False marks every row in a group; the counts answer different questions.strip, casefold and \s+ replacement; add meaning with an approved map dictionary.Flashcards · Duplicates and Inconsistent Labels
9 wordsA row whose values are identical to an earlier row in every compared column.
Two or more rows share the business key but disagree on other values.
Removes rows marked as duplicates, keeping the first occurrence by default.
Converts text to a consistent case for comparison, more thoroughly than `lower()`.
A pattern matching one or more whitespace characters, used to collapse inner spaces.
An approved lookup that turns each known label form into one standard label.
Replaces each value using a dictionary; values not in the dictionary become missing.
Flags labels that were present before mapping but have no approved match after mapping.
A separate table that keeps rows you cannot resolve without evidence, instead of deleting them.