1 of 34

“Tidy datasets are all alike, but every messy dataset is messy in its own way.”

— Hadley Wickham

CMSC 320: Intro to Data Science

Data Cleaning (P1)

Understanding and fixing messy data

Instructor: Fardina F. Alam

https://r4ds.had.co.nz/tidy-data.html

2 of 34

What We Will Learn

01

What is Data Cleaning

02

How to Clean Data

03

Duplicated Records

04

Outlier Detection & Z-score

05

Missing Data (MAR / MNAR)

• Types of imputation

06

Incorrect Data

https://r4ds.had.co.nz/tidy-data.html

3 of 34

Data Cleaning

Data cleaning is the process of fixing or removing incorrect, corrupted, incorrectly formatted, duplicate, or incomplete data within a dataset

4 of 34

Why do we need Data Cleaning?

We often get data that is “messy”

Missing values
Weird outliers
Columns that need to be combined
Multiple tables need to be joined
Inconsistency:

Ex. Mixed date format usage

"MM/DD/YYYY," "YYYY-MM-DD," and "DD-Mon-YY"
01/15/2023, 2023-01-15, Jan-15-23

These can impede and affect our analysis + results!

5 of 34

Our First Step Is Cleaning!

Looking for Issues

Simple & Obvious

Errors that are straightforward to spot during initial data inspection.

Common Example:

Misspelled words in categorical columns.

Fix with: apply() in 🐼 Pandas

Non-Obvious

Hidden data quality issues that require deeper statistical checks and domain knowledge.

Common Example:

Unrealistic outliers or conflicting values (e.g., Age = 250).

Key Challenge:

Not all data cleaning issues are immediately apparent or easy to solve.

Requires exploratory analysis & validation rules.

6 of 34

Link:https://docs.google.com/spreadsheets/d/1D7y7LfBG-owIkXOiabRfFG0XcEzQS_8Lz5BsnRkVT3E/edit#gid=845159259

7 of 34

Where to start

Look to see what you have. 🐼 Pandas has some nice features to help us do that!

8 of 34

Slide Courtesy: Karl Broman, UWisconsin–Madison

9 of 34

10 of 34

Fig: A genetics project where almost 20% of the DNA samples had been mislabeled.

The dots indicate the correct DNA was placed in the well, but the arrows point from where a sample should have been to where it actually was placed.

11 of 34

Focus on the labels (which are more likely correct), rather than the position of variables in a file (which are more likely to change).

12 of 34

Data Typing

The Easy Stuff

Sometimes the data is in the wrong format!

What to do:

Date-Time data (very common)

🐼 Pandas offers a robust datetime library

Other data types

Use df[COLUMN].astype(SOME_TYPE) for straightforward conversions

More complex issues

Utilize df[COLUMN].apply(conversion_function)

13 of 34

Check if All IDs Are Unique df['ID'].is_unique: return T/F

Check the total number of unique IDs: df['ID'].nunique()

14 of 34

Oftentimes you may need to merge various tables.

Ensure file formats match before merging:

check column names and data types are consistent�
fix formatting differences and resolve data conflicts�
choose the appropriate join type (inner, outer, etc.)�
always sanity check the final result.

Combining and Merging Data Sources

15 of 34

Combining and Merging Data Sources

Check Column Names And Data Types

# Load datasets

df1 = pd.read_csv('file1.csv')

df2 = pd.read_csv('file2.csv')

# Check for matching column names

common_columns = df1.columns.intersection(df2.columns)

print("Common columns:", common_columns)

# Check for inconsistent data types in common columns

for column in common_columns:

if df1[column].dtype != df2[column].dtype:

print(f"Column '{column}' has inconsistent types: df1 is

{df1[column].dtype}, df2 is {df2[column].dtype}")

Pandas code to check for consistent data types in common columns across multiple DataFrames

# Convert a column to a consistent type (e.g., string)

df1['column'] = df1['column'].astype(str)

df2['column'] = df2['column'].astype(str)

(b) Resolve Data Type Inconsistencies

16 of 34

Combining & Merging Data Sources

(c) Check for Missing Columns

Decide how to handle missing columns before merging

1. Detect Missing Columns

Use difference() to identify column names present in one DataFrame but absent in another.

2. Standardize Schemas

Iterate over missing columns and fill them with default values (e.g. np.nan) so both DataFrames have matching structures.

check_missing_columns.py

17 of 34

Combining & Merging Data Sources

Resolve Data Conflicts

If the same column has different values in df1 and df2, decide how to resolve the conflict:

1. Prioritize Values

Prioritize values from one DataFrame (e.g., df1), keeping them unless missing or null.

2. Combine Values

Use non-null values across DataFrames. Use combine_first() to fill missing values in df1 with values from df2.

18 of 34

visdat (https://docs.ropensci.org/visdat/) provides a heatmap indicating which data points are missing, and also the variable types.

Naniar (http://naniar.njtierney.com/) provides a scatterplot that includes the cases that are missing one or both variables.

19 of 34

Data cleaning is not a single step in the analysis chain; rather, it is an ongoing process that you will need to continually revisit as you delve deeper into the data. Keep an eye out for hints of problems, and arrange your work with the expectation that you’ll need to re-run everything at some point.

20 of 34

21 of 34

Evaluating Labeling Schemes

Checking whether the categories/labels in a dataset are appropriate, consistent, and useful for the analysis.

\

Example: Product Data

Key Question

Are these labels:

Consistent?
Meaningful?
Useful for analysis?

Evaluating labeling quality ensures data consistency and reliability before deeper analytical modeling.

22 of 34

Common Problems with Labeling Schemes

PROBLEM 01

Too many categories

EXAMPLE

Laptop, Notebook, Ultrabook

WHY IT MATTERS

Creates small/sparse groups that dilute statistical power and complicate model training.

PROBLEM 02

Overlapping labels

EXAMPLE

Electronics vs. Computer Accessories

WHY IT MATTERS

Same item may fit multiple groups, leading to inconsistent tagging and categorization ambiguity.

PROBLEM 03

Imbalanced categories

EXAMPLE

95% Electronics, 5% Other

WHY IT MATTERS

Comparisons may be dominated by one group, masking trends in minority categories.

Good labeling schemes should be consistent, distinct, useful, and stable—or have a clear mapping when they change.

PROBLEM 04

Evolving labels

EXAMPLE

Electronics → Consumer Electronics

WHY IT MATTERS

Categories change over time, creating inconsistencies and tracking issues across historical data.

23 of 34

Cleaning & Aligning Labeling Schemes

COMMON ALIGNMENT ACTIONS

Infer and Apply mapping

# Define mapping dict

label_map = {

"PC Accessories": "Accessories",

"Computer Accessories": "Accessories",

"Electronic": "Electronics"

}

# Apply replacement

old_df["category"] = \

old_df["category"].replace(label_map)

Mapping legacy labels to unified schema definitions ensures consistency across evolving datasets.

24 of 34

Dropping Columns in a DataFrame

Not all the categories of data in a dataset are useful!

Remove unwanted columns: examples:

to_drop = ['Edition Statement',

... 'Corporate Author',

... 'Corporate Contributors',

... 'Former owner',

... 'Engraver',

... 'Contributors',

... 'Issuance type',

... 'Shelfmarks']

>>> df.drop(to_drop, inplace=True, axis=1)

df.drop(columns=to_drop, inplace=True)

25 of 34

Duplicate Records

26 of 34

Duplicated Records

Example: Duplicated Records:

Sometimes people will put a bunch of duplicate records in your system!

If they are exact duplicates: just do df.drop_duplicates()
If they differ slightly → identify the correct records and remove the rest

ALWAYS CHECK FOR THIS

27 of 34

Example: Solution to the Duplicated Records

28 of 34

Outlier Detection

Outliers are data points that differ significantly from most values in a dataset.

They are often identified as values several standard deviations away from the mean.
They may represent: errors, anomalies, rare but real events�

Detecting outliers is important because they can skew statistical analyses and machine learning models.

29 of 34

Outlier Detection: Some common approaches

You can find outliers multiple ways. Looking for extreme z-scores is one way

A simple box and whisker plot is another

A z-score measures how far a data point is from the mean in units of standard deviations.

Large positive or negative z-scores indicate values far from the mean (Common rule: ∣z∣>2 or ∣z∣>3 → possible outlier)
Such values may be potential outliers

30 of 34

Z-Score Example: Detecting an Outlier

Data: Test Scores = [80, 85, 88, 90, 92, 95, 98, 100, 150]

Step 1:Compute statistics

Mean = 100.44
Standard deviation ≈ 21.11�

Step 2: Compute z-score

For score 150: z=(150 - 100.44) / 21.11 ≈ 2.35

Step 3 : Identify outlier

Common rule: |z| > 2∣ → potential outlier�
Since 2.35 > 2 → 150 is a potential outlier

|z| > 2 → possible outlier (moderate rule)�

|z| > 3 → strong outlier (more conservative, very common in statistics)

Here, the z-score for the score of 150 is approximately 2.35, which is greater than 2.

Indicates that the score of 150 is significantly above the mean and is a potential outlier.

Test Scores: [80, 85, 88, 90, 92, 95, 98, 100, 150]

Step 01: Calculate the Mean and Standard Deviation:

Mean (μ) = (80 + 85 + 88 + 90 + 92 + 95 + 98 + 100 + 150) / 9 = 100.44

Standard Deviation (σ) ≈ 21.11 (rounded for simplicity)

Step 02: Calculate Z-scores for each test score using the formula:

For the test score of 150:

Z = (150 - 100.44) / 21.11 ≈ 2.35

For the other scores, calculate their respective z-scores.

Step 03: Identify Extreme Z-scores:

Common threshold used for an extreme z-score: “greater than 2 or less than -2”, suggests that a data point is significantly far from the mean.

Here, the z-score for the score of 150 is approximately 2.35, which is greater than 2.

Indicates that the score of 150 is significantly above the mean and is a potential outlier.

make one concsie slide

31 of 34

Pandas code for detecting outlier

32 of 34

IsolationForest

Anomaly Detection

Algorithm

33 of 34

It depends!

Should we remove an outlier always?

Ultimately, when doing a data science project, you have some goal in mind. Remove the outlier if it hurts that goal. Consider:

whether to remove an outlier depends on how it affects your project's objectives and if it's an essential or unusual part of your data.

Context

If it’s a rare, one-time event (e.g., eclipse data), removal may be appropriate.

Model Impact

Remove it if it distorts or harms model performance.

Relevance

Keep it if it represents a meaningful or expected part of the data (e.g., sale spikes, traffic delays).

Context� If it’s a rare, one-time event, removal may be appropriate.� Example: Light intensity spike during a solar eclipse.

Model Impact� Remove it if it distorts or harms model performance.� Example: Extremely high income (e.g., Jeff Bezos) skewing average customer spending.

Relevance� Keep it if it represents a meaningful or expected part of the data.� Example: Large sales during holiday promotions or long wait times during rush hour.

—--------------

The Context of the Outlier: Is the outlier an unusual event that will likely never happen again? (Something like “light intensity during an eclipse”). →If it is, removing the outlier may be appropriate.

Evaluate Impact on Model Performance: Is the outlier something that will actually hurt your model? (You don’t really need a model factoring in Jeff Bezos when doing financial transactions.

Assess Relevance: Is the outlier something that is a core element of your data? (Profits during a sale, wait times during traffic); We should not be remove without careful considerations.

34 of 34

Next Class:

Missing Data

Check this Article: https://realpython.com/python-data-cleaning-numpy-pandas/