Python Programming for Data Science · Session 07

PYTHON PROGRAMMING FOR DATA SCIENCE

Session 07 · Tutorial

Data cleaning & Wrangling

1. Anatomy of messy data
Before fixing anything, let's see what we're dealing with. We'll work with messy_sales.csv — an
extended version of the Session 6 dataset, deliberately corrupted to mirror what you see in
industry.
Python
import pandas as pd
import numpy as np

# Load the messy dataset

df = pd.read_csv('messy_sales.csv')

print([Link])
print([Link]())
[Link]()
Run this on your own data and you will almost certainly see at least six categories of issues:
• Missing values — NaN, empty cells, or sentinels like -1 or 9999
• Duplicate rows — exact or near-duplicates that inflate counts
• Outliers — extreme values from typos or genuine rare events
• Wrong types — numbers stored as strings, dates as text
• Inconsistent strings — 'HCM' vs 'hcm' vs 'Ho Chi Minh ' for the same value
• Mixed date formats — three different formats in one column

2. Missing values
2.1 Detecting missing values
Pandas represents missing values with NaN (Not a Number). The .isnull() method returns a
boolean mask of the same shape as your data — True where values are missing.
Python
# Boolean mask: True where NaN
[Link]().head()

# Count missing per column (the most useful first check)

[Link]().sum()

Page 1 of 12
 Python Programming for Data Science · Session 07

# Percentage missing per column

([Link]().mean() * 100).round(2)

# Rows containing any missing value

df[[Link]().any(axis=1)].head()
Output
order_id 0
customer_id 47
product_name 0
price 82
quantity 3
order_date 0
region 0
dtype: int64
Tip
Always inspect missing data by column, not by total count. A column missing 90% of values
tells a very different story from 1% spread evenly across all columns. The first case suggests
a broken data pipeline; the second suggests random data entry errors.

2.2 Three strategies: DROP, FILL, FLAG

Every missing value forces a decision. There is no universal right answer — it depends on how
much is missing, why it's missing, and what you plan to do with the data.

Strategy When to use

DROP Missing is rare (< 5%) and appears random. The lost rows won't bias your
analysis.
FILL You have a sensible default value (median for numeric, mode for categorical,
0 for counts).
FLAG The fact that a value is missing is itself informative. Create an is_missing
column.

2.3 .dropna() in practice

Python
# Default: drop ANY row containing a NaN — almost never what you want
[Link]()

# Drop rows where a specific column is NaN

[Link](subset=['price'])

# Drop columns with more than 50% missing

[Link](thresh=len(df) * 0.5, axis=1)
Watch out
Calling [Link]() with no arguments drops a row if any cell is NaN. On a dataset with 10
columns and 1% missing per column independently, you can lose nearly 10% of rows. Always
use subset=[...] to be explicit about which columns matter.

Page 2 of 12
 Python Programming for Data Science · Session 07

2.4 .fillna() and imputation

Filling — also called imputation — replaces NaN with a chosen value. The choice of value
matters more than people realize.
Python
# Fill with a constant
df['region'] = df['region'].fillna('Unknown')

# Fill numeric column with median (robust to outliers)

df['price'] = df['price'].fillna(df['price'].median())

# Fill different columns with different values in one call

df = [Link]({
'price': df['price'].median(),
'quantity': 1,
'region': 'Unknown',
})

# Forward-fill (carries last valid value forward — useful for time series)
df['price'] = df['price'].fillna(method='ffill')

Choosing the right fill value

Strategy When
Mean Numeric, roughly symmetric distribution (age, height)
Median Numeric, skewed or with outliers (income, price)
Mode Categorical or discrete (region, color, category)
Forward-fill Time series with gaps (sensor readings, daily prices)
Group mean Subgroups have different scales (salary by job title)
Domain value Missing has a meaningful default (discount = 0)

2.5 Flagging missingness

Sometimes the absence of a value is itself a signal. If customers who don't fill in their income
field are systematically different from those who do, throwing away that signal is a mistake.
Python
# Add a flag column BEFORE imputing
df['price_missing'] = df['price'].isnull()
df['price'] = df['price'].fillna(df['price'].median())

# Now downstream models can use price_missing as a feature

Tip
This pattern preserves both the information that the value was missing AND a reasonable
estimate of what it might have been. It often improves model performance.

3. Duplicates

Page 3 of 12
 Python Programming for Data Science · Session 07

Duplicate rows silently double your counts. A 'top customer by total spend' report becomes
wrong, a forecast trained on duplicated rows over-weights certain patterns, and an audit
becomes embarrassing.
Python
# How many duplicates?
[Link]().sum()

# Flag duplicates (True = this row was seen before)

[Link]().head()

# Remove exact duplicates

df = df.drop_duplicates()

# Check duplicates by a subset of columns

[Link](subset=['order_id']).sum()

# Keep first occurrence (default), last, or drop all

df.drop_duplicates(subset=['order_id'], keep='first')
df.drop_duplicates(subset=['order_id'], keep='last')
df.drop_duplicates(subset=['order_id'], keep=False) # drop ALL duplicates
Tip
If order_id is supposed to be unique, df['order_id'].duplicated().any() should return
False. If it returns True you have an upstream data problem, not a deduplication problem.
Investigate before you delete.

4. Outliers
An outlier is a value that sits far from the rest of the data. It might be a typo (someone typed 999
instead of 99), a genuine rare event (one whale customer who spent a million dollars), or a
sentinel value (-999 used to mean 'unknown').

4.1 Detecting outliers with IQR

The interquartile range (IQR) method is robust and easy to explain to non-technical
stakeholders.
1. Compute Q1 (the 25th percentile) and Q3 (the 75th percentile).
2. Compute IQR = Q3 − Q1.
3. Define bounds: lower = Q1 − 1.5 × IQR, upper = Q3 + 1.5 × IQR.
4. Anything outside [lower, upper] is an outlier candidate.
Python
Q1 = df['price'].quantile(0.25)
Q3 = df['price'].quantile(0.75)
IQR = Q3 - Q1

Page 4 of 12
 Python Programming for Data Science · Session 07

lower = Q1 - 1.5 * IQR

upper = Q3 + 1.5 * IQR

# Flag outliers as a new column

mask = (df['price'] < lower) | (df['price'] > upper)
df['is_price_outlier'] = mask

# Inspect them BEFORE deleting

df[df['is_price_outlier']].head(10)

# Filter them out (only after you've confirmed they're errors)

clean = df[~mask]
Watch out
An outlier is not automatically an error. Before removing anything, ask: is this a typo, a
sentinel, or a genuine rare event? If your dataset contains one customer who bought 1,000
units while everyone else bought 5, you might be looking at your most important customer.
Investigate first; delete second.

5. Type conversion
CSV files have no notion of data types — everything is text. Pandas guesses types when
reading, and the guesses are usually right, but not always. Numbers with currency symbols
become strings. Dates become objects. Booleans become 'Y'/'N' text.
Python
# Always check first
[Link]

# Simple cast (raises an error on bad values)

df['price'] = df['price'].astype(float)

# Safe numeric conversion (bad values become NaN, no exception)

df['price'] = pd.to_numeric(df['price'], errors='coerce')

# Categorical type — saves memory on low-cardinality string columns

df['region'] = df['region'].astype('category')

# Boolean from yes/no strings

df['active'] = df['active'].map({'Y': True, 'N': False})

5.1 The errors= parameter

pd.to_numeric() and pd.to_datetime() both accept an errors= argument that decides what
happens when conversion fails.

Value Behavior on bad input

Page 5 of 12
 Python Programming for Data Science · Session 07

errors='raise' Default. Throws an exception and stops.

errors='coerce' Returns NaN (or NaT for dates) instead of raising. Most common choice.
errors='ignore' Returns the input unchanged. Rarely useful.

5.2 Datetime parsing

Date columns are special because they often arrive in inconsistent formats. messy_sales.csv
has dates written three different ways in the same column.
Python
# Auto-parse most formats (slower but flexible)
df['order_date'] = pd.to_datetime(df['order_date'])

# Be explicit when the format is consistent (much faster on large data)

df['order_date'] = pd.to_datetime(df['order_date'], format='%d/%m/%Y')

# Bad values become NaT (datetime version of NaN)

df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')

# Extract components via the .dt accessor

df['year'] = df['order_date'].[Link]
df['month'] = df['order_date'].[Link]
df['weekday'] = df['order_date'].dt.day_name()
df['quarter'] = df['order_date'].[Link]
Tip
After parsing, check df['order_date'].isnull().sum(). If you started with no missing
dates but now have some, the parser failed on those rows. Look at them with
df[df['order_date'].isnull()] to see what went wrong.

6. String cleaning with .str

Pandas Series have a .str accessor that applies string methods element-wise to an entire
column — no loops needed. This is one of the most powerful and underused features of
Pandas.
Python
# Remove leading and trailing whitespace
df['region'] = df['region'].[Link]()

# Normalize case
df['region'] = df['region'].[Link]()

# Replace patterns
df['region'] = df['region'].[Link]('tp.', '', regex=False)

# Conditional filter via .[Link]

mask = df['product_name'].[Link]('mouse', case=False, na=False)

Page 6 of 12
 Python Programming for Data Science · Session 07

df[mask]

# Chain operations
df['region'] = (df['region']
.[Link]()
.[Link]()
.replace({'hcm': 'ho chi minh', 'hn': 'hanoi'}))

6.1 Before and after

Here's what a single inconsistent column can look like before and after cleaning:

Before After
'HCM ' 'ho chi minh'
'hcm' 'ho chi minh'
'Ho Chi Minh' 'ho chi minh'
'[Link]' 'ho chi minh'
' Hanoi' 'hanoi'
'HN' 'hanoi'

Tip
Always lowercase before comparing. 'HCM'.lower() == 'hcm' is the simplest way to
avoid case-sensitivity bugs in grouping and joining.

7. Combining dataFrames
Real analyses almost always require combining tables. Sales data lives in one table, customer
information in another, product catalog in a third. Pandas gives you two main tools: concat and
merge.

7.1 concat — stacking

Use concat when the DataFrames have the same columns (stacking rows) or the same rows
(stacking columns).
Python
# Stack rows (concatenate Jan and Feb sales)
all_sales = [Link]([jan_sales, feb_sales], axis=0)

# Reset the index after stacking — duplicates are a common source of bugs
all_sales = [Link]([jan_sales, feb_sales], ignore_index=True)

# Stack columns side by side (same number of rows)

combined = [Link]([df_features, df_target], axis=1)

# Keep only columns present in both (inner join on columns)

all_sales = [Link]([jan_sales, feb_sales], join='inner')

Page 7 of 12
 Python Programming for Data Science · Session 07

7.2 merge — joining on a key

Use merge when two DataFrames share a key column (like customer_id) and you want to bring
in extra columns from the second DataFrame.
Python
# Bring in customer info from a second table
orders_with_customers = [Link](
orders,
customers,
on='customer_id',
how='left'
)

The four join types

how= Meaning
'inner' Only keys that appear in BOTH tables. Safest for analysis.
'left' All keys from the left table. Most common in practice.
'right' All keys from the right table. Less common.
'outer' All keys from either table. Useful for diagnostics.

Watch out
After every merge, check the row count. If you joined orders (10,000 rows) with customers
and now have 12,000 rows, your customer table has duplicate keys and you've inadvertently
created phantom orders. merge(..., validate='m:1') will raise an exception if this
happens — use it.

8. Groupby and Aggregation

Groupby is the workhorse of data analysis. The pattern is always the same: split rows into
groups, apply a function to each group, then combine the results back into one DataFrame.

8.1 The split-apply-combine pattern

Python
# Total revenue per region
[Link]('region')['revenue'].sum()

# Multi-level grouping
[Link](['region', 'product_category'])['revenue'].sum()

# The result is a Series with a MultiIndex

# Convert to a flat DataFrame with reset_index
[Link]('region')['revenue'].sum().reset_index()
Output
region
Da Nang 11,350

Page 8 of 12
 Python Programming for Data Science · Session 07

Hanoi 20,370
Ho Chi Minh 32,060
Name: revenue, dtype: int64

8.2 Multiple aggregations at once

Use .agg() to compute several statistics in a single pass. There are several syntaxes — the
named aggregation form is the cleanest.
Python
# Multiple aggregations on the same column
[Link]('region')['revenue'].agg(['sum', 'mean', 'count'])

# Different aggregations on different columns

[Link]('region').agg({
'revenue': 'sum',
'quantity': 'mean',
'customer_id': 'nunique',
})

# Named aggregations — produces clean column names

[Link]('region').agg(
total_revenue=('revenue', 'sum'),
avg_order=('revenue', 'mean'),
unique_customers=('customer_id', 'nunique'),
)

8.3 Pivot tables

A pivot table reshapes long data into a wide cross-tab — like a pivot table in Excel, but
reproducible and version-controlled.
Python
# Region × product matrix of total revenue
df.pivot_table(
index='region',
columns='product_category',
values='revenue',
aggfunc='sum',
fill_value=0,
)

# Multiple aggregations in one pivot

df.pivot_table(
index='region',
values='revenue',
aggfunc=['sum', 'mean', 'count'],
)

# The inverse: melt() turns wide data back into long

Page 9 of 12
 Python Programming for Data Science · Session 07

[Link](id_vars=['region'], var_name='category', value_name='revenue')

Output
product_category Electronics Apparel Books
region
Da Nang 6,780 3,150 1,420
Hanoi 12,450 5,820 2,100
Ho Chi Minh 18,330 9,210 4,520

9. Putting it together — A full cleaning pipeline

Let's combine everything into one realistic pipeline on messy_sales.csv. This is the pattern you
should follow for any cleaning task.
Python
import pandas as pd
import numpy as np

# 1. Load
df = pd.read_csv('messy_sales.csv')
print(f"Loaded: {[Link][0]} rows, {[Link][1]} columns")

# 2. Inspect
print([Link])
print([Link]().sum())
print(f"Duplicates: {[Link]().sum()}")

# 3. Clean strings (normalize region)

df['region'] = (df['region']
.[Link]()
.[Link]()
.replace({'hcm': 'ho chi minh',
'[Link]': 'ho chi minh',
'hn': 'hanoi'}))
df['product_name'] = df['product_name'].[Link]()

# 4. Fix types
df['price'] = pd.to_numeric(df['price'], errors='coerce')
df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce')
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')

# 5. Handle missing values (decided per column)

df['price'] = df['price'].fillna(df['price'].median())
df['quantity'] = df['quantity'].fillna(1)
df['region'] = df['region'].fillna('unknown')
df = [Link](subset=['order_date']) # can't analyze without a date

# 6. Drop exact duplicates

df = df.drop_duplicates()
df = df.drop_duplicates(subset=['order_id'], keep='first')

Page 10 of 12
 Python Programming for Data Science · Session 07

# 7. Remove obvious outliers (price > $10,000 in our context)

df = df[df['price'].between(0, 10000)]

# 8. Add derived columns

df['revenue'] = df['price'] * df['quantity']
df['month'] = df['order_date'].[Link]

# 9. Merge with customer data

customers = pd.read_csv('[Link]')
df = [Link](df, customers, on='customer_id', how='left')

# 10. Save the cleaned version

df.to_csv('sales_cleaned.csv', index=False)
print(f"Cleaned: {[Link][0]} rows")
Tip
Save the cleaned DataFrame to a new file rather than overwriting the original. The original
messy data is your audit trail — if something looks wrong later, you can re-run the pipeline
and trace the bug.

10. In-class Exercises

Exercise 10.1
Profile the messy_sales.csv dataset:
• Print the shape and column dtypes.
• Compute missing value counts and percentages per column.
• Count exact duplicate rows.
• Find at least 3 inconsistencies in the region column.
Write a 5-line summary of what is wrong with this dataset.
Exercise 10.2
Clean messy_sales.csv step by step:
• Normalize the region column to lowercase with consistent naming.
• Convert price and quantity to numeric using errors='coerce'.
• Parse order_date and handle bad rows explicitly.
• Choose and justify your missing-value strategy per column (write a comment
explaining each choice).
• Drop exact duplicates and report how many were removed.
• Save the result as sales_cleaned.csv.

Page 11 of 12
 Python Programming for Data Science · Session 07

Exercise 10.3 — Combine and analyze

Using your cleaned dataset and [Link]:
1. Merge sales with customers on customer_id (use validate='m:1').
2. Answer: which region has the highest total revenue? Show with a groupby.
3. Answer: what is the average revenue per customer in each region? Use named
aggregation.
4. Build a pivot table of region × product_category showing total revenue.
5. Identify the top 3 customers by total spend across all orders.

Page 12 of 12