Lesson 8 · Data analytics zero to hero
Pandas Data Cleaning: Missing Values & Duplicates | Data Analytics #8
Finding and fixing missing values, duplicates, bad types, and messy text in a real dataset. We're using a real extract of the UCI Online Retail dataset,…
- CourseData analytics zero to hero
- Lesson8 of 30
- Video13 min
- FormatJupyter notebook · 13 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- online_retail_sample.csv preview132.5 KB
📓 Full notebook
Download .ipynbData Analytics Zero to Hero, Video 8: Pandas Data Cleaning#
- Video eight of the 30-part series: finding and fixing missing values, duplicates, bad types, and messy text in a real dataset.
- We're using a real extract of the UCI Online Retail dataset, actual 2010-2011 UK e-commerce transactions, genuinely messy exactly the way real data is.
- Let's jump straight in.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- Place online_retail_sample.csv in the same folder as this notebook.
Part 1: Loading and Spotting Problems#
import pandas as pd
df = pd.read_csv('online_retail_sample.csv')
print(df.shape)
print(df.head(3))
print(df.dtypes)
print(df.info())
print(df.isna().sum())
print(df.duplicated().sum())
print(df[df.duplicated(keep=False)].head(4))
Part 2: Handling Missing Values#
print(df['CustomerID'].isna().mean())
df_dropped = df.dropna(subset=['CustomerID'])
print(df_dropped.shape)
df['Description'] = df['Description'].fillna('UNKNOWN ITEM')
print(df['Description'].isna().sum())
Part 3: Removing Duplicates#
before = df.shape[0]
df = df.drop_duplicates()
after = df.shape[0]
print(f'Removed {before - after} real duplicate rows')
Part 4: Fixing Data Types#
df = df.dropna(subset=['CustomerID'])
df['CustomerID'] = df['CustomerID'].astype('Int64')
print(df['CustomerID'].dtype)
print(df['CustomerID'].head(3))
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df['InvoiceDate'].dtype)
print(df['InvoiceDate'].dt.day_name().head(3))
Part 5: Cleaning Text Columns#
has_whitespace = (df['Description'].str.strip() != df['Description']).sum()
print(has_whitespace)
df['Description'] = df['Description'].str.strip()
print(df['Country'].str.upper().value_counts().head(5))
Part 6: Filtering Invalid or Suspicious Rows#
returns = df[df['Quantity'] < 0]
print(returns.shape[0])
print(returns[['InvoiceNo', 'Description', 'Quantity']].head(3))
sales_only = df[(df['Quantity'] > 0) & (df['UnitPrice'] > 0)]
print(f'{df.shape[0]} total rows, {sales_only.shape[0]} genuine sales rows')
Wrap-Up: What You Learned#
- Spotting problems fast: isna, duplicated, dtypes, and info.
- Handling missing values with dropna and fillna, and choosing between them based on context.
- Removing exact duplicate rows with drop_duplicates.
- Fixing data types: nullable integers with Int64, and real dates with to_datetime.
- Cleaning text columns at scale with the str accessor: strip and upper.
- Filtering out invalid or out-of-scope rows, like returns and zero-price entries, based on business logic.
- All of this on a real, genuinely messy retail dataset. Video nine covers pandas data wrangling: reshaping, combining, and transforming cleaned data like this. Subscribe so it lands automatically see you there.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
What does
df.isna().sum()tell you?isna() marks missing cells as True, and sum() counts them per column.
Which call removes only the rows where
CustomerIDis missing?Passing subset limits dropna to the listed columns. Plain dropna() would drop rows with a missing value in any column.
Why convert
InvoiceDatewithpd.to_datetime?Dates read from CSV are text. After to_datetime you can use the .dt accessor for year, month, day names and more.
What does this code do?
df['Description'] = df['Description'].str.strip()
str.strip() trims leading and trailing whitespace from every string in the column.
How would you keep only genuine sales (positive quantity and price)?
Combine boolean conditions with & and parentheses. Negative quantities are returns.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Create a small DataFrame with a missing value and a duplicate row. Print the missing counts, then drop duplicates and print the result.
💡 Hint
Use isna().sum() and drop_duplicates().
✅ Show solution
import pandas as pd import numpy as np df = pd.DataFrame({ 'Item': ['Mug', 'Pen', 'Pen', 'Cup'], 'Price': [3.5, 1.2, 1.2, np.nan] }) print(df.isna().sum()) df = df.drop_duplicates() print(df)
Given a DataFrame with a messy
Description(extra spaces, missing value) andCountryin mixed case, fill missing descriptions with 'UNKNOWN ITEM', strip the spaces, and count countries after making them uppercase.💡 Hint
Chain fillna and str.strip; use str.upper().value_counts().
✅ Show solution
import pandas as pd import numpy as np df = pd.DataFrame({ 'Description': [' MUG ', 'PEN', np.nan, ' CUP'], 'Country': ['France', 'france', 'Germany', 'FRANCE'] }) df['Description'] = df['Description'].fillna('UNKNOWN ITEM').str.strip() print(df['Description'].tolist()) print(df['Country'].str.upper().value_counts())
Clean this tiny sales table: drop rows with missing CustomerID, convert CustomerID to the nullable 'Int64' type, convert InvoiceDate to datetime, and keep only rows with Quantity > 0. Print the cleaned frame and the day name of each date.
💡 Hint
Use dropna(subset=...), astype('Int64'), pd.to_datetime and .dt.day_name().
✅ Show solution
import pandas as pd import numpy as np df = pd.DataFrame({ 'CustomerID': [17850.0, np.nan, 13047.0, 12583.0], 'InvoiceDate': ['2010-12-01 08:26', '2010-12-02 09:00', '2010-12-03 10:30', '2010-12-04 11:15'], 'Quantity': [6, 3, -2, 8] }) df = df.dropna(subset=['CustomerID']) df['CustomerID'] = df['CustomerID'].astype('Int64') df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate']) df = df[df['Quantity'] > 0] print(df) print(df['InvoiceDate'].dt.day_name())
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



