Đạ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 7 · Handling Missing Values

Print / Save as PDF
Lecture video — Period 7: Handling Missing Values

Recognising missing markers, choosing between keeping, removing and imputing, and checking how each choice changes counts, totals and variation.

Lesson overview

Now we will work with missing values in sales data. A missing value can change a summary even when the code runs without an error. By the end of this lesson, you should be able to recognize different missing markers, choose a suitable treatment, and explain how that choice changes the result. We will combine the concepts with a small Python example.

Study notes

By the end of this lesson you can:

  • Separate a missing value from a real zero, and recognize different missing markers.
  • Standardize text markers (blank, ?, N/A) into real missing values in pandas.
  • Measure missingness overall and by group.
  • Apply and compare three treatments: keep and flag, remove (dropna), and median imputation (fillna).
  • Write a decision log entry that gives the rule, the reason and the effect on the results.

Key idea: A missing value is unknown, not zero. Whatever treatment you choose, keep it visible, explain it, and compare row counts, totals and spread, not only the mean.

Missing is not zero

If a customer received no discount, a recorded discount of 0 is valid. If the discount field is empty, we do not know the discount; replacing it with 0 creates an assumption. Missing values can appear as blank text, question marks or words such as N/A. But a code may have a valid meaning in a specific column, so check the data dictionary before replacing it.

Standardizing missing markers

The lecture uses six transactions: three in North, three in South. Four quantities are available (2, 4, 6, 8); the other two are text markers, one with spaces around N/A. Before standardization, these markers are ordinary strings, so isna() does not see them.

On a copy of raw, the lecture:

  1. converts the column to the pandas string dtype (which supports missing text);
  2. uses str.strip() to remove spaces at both ends and str.upper() to make the comparison consistent;
  3. maps the agreed markers to pd.NA;
  4. converts quantity to the nullable Float64 dtype, which holds numbers and missing values together.

After this, quantity has 2 missing values, one third of the rows. Each region has 1 missing quantity out of 3. In a larger dataset, uneven missingness could make one region less visible in the analysis, so always check by group.

Three treatments

TreatmentRowsQuantities usedSumMeanStd (ddof=0)
Keep and flag642052.24
Remove: dropna(subset=["quantity"])442052.24
Median fill: fillna(5)663051.83
  • Keep and flag. Preserve all six transactions and add a Boolean flag where quantity is unavailable. count() returns 4 non-missing values; len() returns 6 rows. Report the mean as "based on four observed transactions". Other complete fields stay usable.
  • Remove. dropna(subset=["quantity"]) removes only rows missing quantity; without subset, a missing value in an unrelated column would also remove a transaction. The cost is losing two transactions, and in other data deletion may hit one group much more than another. Record how many rows you exclude.
  • Median imputation. The median of 2, 4, 6, 8 is (4 + 6) / 2 = 5. Create the flag before filling, because afterwards you can no longer see which values were missing. The two fives are estimates, not verified sales. Median filling is less sensitive to extreme values than mean filling, but it reduces variation, can weaken relationships, and fails if the whole column is missing.

Both methods give a mean of 5, so the mean alone suggests no difference. But the median-filled sum is 30, and 10 of those units come entirely from the assumption, while the standard deviation falls from about 2.24 to 1.83.

One rule does not fit every column

  • An identifier carries identity; inventing one creates a false record.
  • A missing discount can become 0 only if the source confirms no discount was given.
  • For a category, an explicit Unknown label can help, though it does not recover the true category.
  • For quantity, a group median may be more relevant than a global one if the groups are meaningful and large enough.

Worked example

import pandas as pd

raw = pd.DataFrame({
    "transaction_id": ["T1", "T2", "T3", "T4", "T5", "T6"],
    "region": ["North", "North", "North", "South", "South", "South"],
    "quantity": ["2", "?", "4", "6", " N/A ", "8"],
})

df = raw.copy()
q = df["quantity"].astype("string").str.strip().str.upper()
q = q.replace({"": pd.NA, "?": pd.NA, "N/A": pd.NA})
df["quantity"] = q.astype("Float64")
print(df["quantity"].isna().sum(), df.groupby("region")["quantity"].apply(lambda s: s.isna().sum()).to_dict())

dropped = df.dropna(subset=["quantity"])
filled = df.assign(quantity_imputed=df["quantity"].isna())     # flag first
filled["quantity"] = filled["quantity"].fillna(df["quantity"].median())

for name, d in [("dropna", dropped), ("median fill", filled)]:
    s = d["quantity"]
    print(name, len(d), s.sum(), s.mean(), round(s.std(ddof=0), 2))
print(filled["quantity_imputed"].sum())

Expected output: 2 missing values, one in each region; dropna gives 4 rows, sum 20, mean 5, std 2.24; median fill gives 6 rows, sum 30, mean 5, std 1.83; and the flag marks exactly 2 imputed values. Decision log entry: "quantity; median fill (5); reason: needed for a count of units; effect: 2 rows imputed, total +10 units, spread reduced."

Common mistakes

  • Filling missing discounts with 0. That claims no discount was given; it needs evidence from the source.
  • Running dropna() without subset. You may delete transactions because of an unrelated column.
  • Filling before flagging. Once filled, estimated values look exactly like real ones.
  • Comparing only the mean. Check row counts, totals and spread; a treatment can change the total by many units while the mean stays the same.

Summary

  • Missing means unknown; zero is a real value. Check the data dictionary before replacing markers.
  • Standardize markers on a copy with strip, upper, replace and a nullable dtype such as Float64.
  • count() counts observed values; len() counts rows; report which one a statistic uses.
  • Keep-and-flag, deletion and imputation each have costs; choose by the column's meaning and the question.
  • Record the column, rule, reason and effect for every treatment.

Flashcards · Handling Missing Values

9 words
WordMeaningExample
  • Missing marker

    A text value such as blank, `?` or `N/A` used in a file to mean the value is unknown.

    `" N/A "` must be stripped before it can be recognized.

  • pd.NA

    The pandas missing-value marker used by nullable dtypes such as `string` and `Float64`.

    `q.replace({"?": pd.NA, "N/A": pd.NA})`

  • Float64 (nullable)

    A pandas numeric dtype that can store decimal numbers and missing values together.

    `df["quantity"] = q.astype("Float64")`

  • str.strip()

    Removes spaces and other whitespace from both ends of each text value.

    `" N/A "` becomes `"N/A"`.

  • count() vs len()

    `count()` gives the number of non-missing values; `len()` gives the number of rows.

    count 4, len 6

  • dropna(subset=...)

    Removes rows with missing values only in the listed columns.

    `df.dropna(subset=["quantity"])`

  • Median imputation

    Filling missing values with the median of the observed values; it reduces variation.

    `df["quantity"].fillna(df["quantity"].median())`

  • Imputation flag

    A Boolean column, created before filling, that marks which values were estimated.

    `df.assign(quantity_imputed=df["quantity"].isna())`

  • Keep and flag

    Keeping rows with missing values and marking them, so other fields stay usable without guessing.

    Mean 5 reported as based on 4 observed transactions.

Practice for Session 3

Open notebooks/Session3_Clean.ipynb from the practice pack (dc_practice_pack.zip, attached to the Course Overview lesson) together with sales_transactions_raw.csv. Run it in Google Colab or Jupyter from top to bottom and complete the Your turn exercises.