Module 5 Lesson: Cleaning Messy Data

Let us begin

Messy data is normal.

Missing values, duplicate rows, inconsistent labels, broken dates, and impossible numbers are not unusual. The goal is not to pretend the data is perfect. The goal is to make careful decisions and record them.

Cleaning is part of the evidence.

Start with a copy

Keep raw data unchanged.

raw = pd.read_csv("data/messy_course_records.csv")
cleaned = raw.copy()

The copy gives you a working table while preserving the original source.

Missing values

Count missing values:

cleaned.isna().sum()

Also calculate percentages:

cleaned.isna().mean().sort_values(ascending=False)

Do not automatically fill or drop. Ask what missing means.

Possible meanings:

• not collected;
• not applicable;
• delayed;
• unknown;
• data entry error.

Common actions:

cleaned = cleaned.dropna(subset=["learner_id"])
cleaned["rating"] = cleaned["rating"].fillna("not_submitted")

Each action needs a reason.

Duplicates

Exact duplicate rows:

cleaned.duplicated().sum()

Remove exact duplicates only after checking:

cleaned = cleaned.drop_duplicates()

Suspicious duplicates are different. Two rows may share the same learner, course, and date but have different values. Investigate before removing.

Invalid values

Some values are impossible or outside the expected range.

Examples:

• negative practice minutes;
• rating of 8 on a 1 to 5 scale;
• completion date before start date;
• unknown category spelling.

Check ranges:

cleaned["practice_minutes"].describe()

Filter suspicious records:

cleaned[cleaned["practice_minutes"] < 0]

Type conversion

Numbers may arrive as text. Dates may arrive as strings.

cleaned["practice_minutes"] = pd.to_numeric(
    cleaned["practice_minutes"],
    errors="coerce"
)

errors="coerce" turns invalid values into missing values. That is useful, but it must be logged.

Date parsing

cleaned["start_date"] = pd.to_datetime(
    cleaned["start_date"],
    errors="coerce"
)

After parsing, check how many dates failed.

cleaned["start_date"].isna().sum()

Dates are important because later time analysis depends on them.

Text and category cleaning

Text may contain extra spaces or inconsistent case.

cleaned["course_name"] = (
    cleaned["course_name"]
    .str.strip()
    .str.lower()
)

Categories may need mapping:

topic_map = {
    "tech issue": "technical",
    "technical issue": "technical",
    "payment query": "payment",
}

cleaned["support_topic"] = cleaned["support_topic"].replace(topic_map)

Always inspect category counts before and after.

Outliers

An outlier is unusual. It is not automatically wrong.

If a learner has 900 minutes recorded in one day, it could be:

• a recording error;
• a real long study session;
• a paused timer;
• repeated rows;
• a batch import problem.

Investigate first. Then decide.

Cleaning log

A cleaning log records:

• the problem;
• the action;
• the reason;
• rows affected;
• follow-up check.

This makes the analysis trustworthy.

Mini worked example

before_rows = len(cleaned)

cleaned["practice_minutes"] = pd.to_numeric(
    cleaned["practice_minutes"],
    errors="coerce"
)

cleaned = cleaned[cleaned["practice_minutes"] >= 0]

after_rows = len(cleaned)
removed_rows = before_rows - after_rows

Cleaning log note:

> Converted practice_minutes to numeric. Invalid text values became missing. Removed rows with negative practice minutes because time spent cannot be negative. Rows removed: removed_rows.

Takeaway

Cleaning is not a secret technical step. It is a set of visible decisions. In the next module, we summarize cleaned data with grouping, aggregation, pivoting, and reshaping.