You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
In Period 2, we loaded data and checked its structure. Today, we look more closely at data quality. We will measure missing values, check repeated records, count distinct values, and examine category labels. We will also test simple business rules. Our goal is to create a small quality report before changing the data. The report tells us what needs attention and gives us a baseline for checking later cleaning steps.
By the end of this lesson you can:
duplicated() with different subset and keep settings and explain what each count means.nunique() and value_counts() to find inconsistent category labels.Key idea: A quality check needs both a number and a clear definition of what that number counts. Measure and flag problems first; cleaning decisions come later.
The lecture uses six rows from a simulated sales table. transaction_id is the expected business key; the row numbers on the left are only positions. Rows 2 and 3 are identical, row 4 has no region, row 5 has a negative quantity, and row 6 uses lowercase north.
df.isna() returns a table of True/False values where True means missing. Because True counts as 1:
df.isna().sum() gives the number of missing cells per column;df.isna().mean() gives the missing fraction; multiply by 100 for a percentage.Region has 1 missing value out of 6 rows, so the missing rate is 16.67%. Always use all original input rows as the denominator. Note that empty strings and words such as unknown are not automatically missing; they need a separate check (Period 7 standardizes them).
Three duplicate checks answer three different questions:
| Code | Question | Result in the example |
|---|---|---|
df.duplicated().sum() | How many later copies of a complete row? | 1 |
df.duplicated(subset=["transaction_id"]).sum() | How many later rows repeat the key? | 1 |
df.duplicated(subset=["transaction_id"], keep=False).sum() | How many rows are in a repeated-key group? | 2 |
With the default keep="first", the first occurrence is not marked. A repeated key may also hide conflicting values, so inspect the rows before removing anything. In a table of order items, the key may need more than one column.
nunique() counts distinct values. Here transaction_id has 5 distinct values in 6 rows. For region, the default result is 3 (North, South, north) because missing values are excluded; nunique(dropna=False) gives 4. This does not mean the business has four regions. It means the raw column contains these different entries, and case differences inflate the count.
value_counts(dropna=False) shows how often each value appears: North 2, South 2, north 1 and one missing entry, adding up to 6. Compare the labels with the data dictionary before merging any of them. Do not merge labels just because they look similar; confirm the allowed categories first and document the rule later.
In this example the table contains completed sales and excludes returns, so quantity must be greater than 0 and discount_rate must be between 0 and 1, including both limits. A discount of 1.2 means 120%, which breaks the rule. Transaction T04 fails both checks. Combining the conditions with | selects rows that fail either rule, so T04 is flagged once, although it has two violations. (In real data, negative quantities may be valid if returns are included.)
The quality report gives one row per original column: missing count, missing percentage and number of distinct non-missing values. It is compact but cannot describe everything: duplicate checks belong at the record level, and rules may involve several columns. Keep those results beside the profile and record the number of input rows.
The quantities, discounts and labels below follow the lecture's description; values not mentioned in the lecture are illustrative.
import pandas as pd
df = pd.DataFrame({
"transaction_id": ["T01", "T02", "T02", "T03", "T04", "T05"],
"region": ["North", "South", "South", None, "North", "north"],
"quantity": [2, 3, 3, 1, -1, 4],
"discount_rate": [0.0, 0.1, 0.1, 0.05, 1.2, 0.0],
})
quality = pd.DataFrame({
"missing_count": df.isna().sum(),
"missing_pct": (df.isna().mean() * 100).round(2),
"n_distinct": df.nunique(),
})
print(quality)
print(df.duplicated().sum()) # 1
print(df.duplicated(subset=["transaction_id"], keep=False).sum()) # 2
print(df["region"].value_counts(dropna=False))
rule_fail = (df["quantity"] <= 0) | ~df["discount_rate"].between(0, 1)
print(df.loc[rule_fail, "transaction_id"].tolist()) # ['T04']
Expected output: only region has a missing value (count 1, 16.67%) and 3 distinct non-missing labels; 1 exact duplicate; 2 rows in the repeated T02 group; and one flagged row, T04. Next steps from the report: review T04 because it affects revenue, check the repeated T02 records before totals, investigate the missing region, and confirm whether north should match North.
isna() to catch every marker. Blanks, N/A written as text or unknown are ordinary strings until you standardize them.keep) and "rows involved" (keep=False) are different numbers; say which one you report.isna().sum() counts missing cells; isna().mean() * 100 gives the missing percentage.duplicated() marks later copies; subset limits the comparison; keep=False marks every row in a group.nunique() excludes missing values by default; value_counts(dropna=False) shows each label and its frequency.| to flag rows that break at least one rule; one row can have several violations.Flashcards · Measuring Data Quality: Missing Values, Duplicates and Categories
9 wordsReturns True where a value is a real missing value (None, NaN, NA) and False elsewhere.
The share of rows with a missing value, using all input rows as the denominator.
Marks rows that repeat an earlier row; by default the first occurrence is not marked.
Option of `duplicated()` that marks every row in a repeated group, including the first.
Limits the duplicate comparison to selected columns, such as the business key.
Counts distinct values; missing values are excluded unless `dropna=False`.
Lists each distinct value with how often it appears.
A condition derived from the business meaning of a column, used to flag invalid rows.
A table summarizing missing counts, percentages and distinct values per column before cleaning.