Mathew K Analytics

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,…

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.

📓 Full notebook

Download .ipynb

Data 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))
(1500, 8)
  InvoiceNo StockCode                         Description  Quantity  \
0    536365    85123A  WHITE HANGING HEART T-LIGHT HOLDER         6   
1    536365     71053                 WHITE METAL LANTERN         6   
2    536365    84406B      CREAM CUPID HEARTS COAT HANGER         8   

           InvoiceDate  UnitPrice  CustomerID         Country  
0  2010-12-01 08:26:00       2.55     17850.0  United Kingdom  
1  2010-12-01 08:26:00       3.39     17850.0  United Kingdom  
2  2010-12-01 08:26:00       2.75     17850.0  United Kingdom  
print(df.dtypes)
print(df.info())
InvoiceNo       object
StockCode       object
Description     object
Quantity         int64
InvoiceDate     object
UnitPrice      float64
CustomerID     float64
Country         object
dtype: object
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1500 entries, 0 to 1499
Data columns (total 8 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   InvoiceNo    1500 non-null   object 
 1   StockCode    1500 non-null   object 
 2   Description  1499 non-null   object 
 3   Quantity     1500 non-null   int64  
 4   InvoiceDate  1500 non-null   object 
 5   UnitPrice    1500 non-null   float64
 6   CustomerID   1442 non-null   float64
 7   Country      1500 non-null   object 
dtypes: float64(2), int64(1), object(5)
memory usage: 93.9+ KB
None
print(df.isna().sum())
InvoiceNo       0
StockCode       0
Description     1
Quantity        0
InvoiceDate     0
UnitPrice       0
CustomerID     58
Country         0
dtype: int64
print(df.duplicated().sum())
print(df[df.duplicated(keep=False)].head(4))
36
    InvoiceNo StockCode                    Description  Quantity  \
485    536409     22111   SCOTTIE DOG HOT WATER BOTTLE         1   
489    536409     22866  HAND WARMER SCOTTY DOG DESIGN         1   
494    536409     21866    UNION JACK FLAG LUGGAGE TAG         1   
517    536409     21866    UNION JACK FLAG LUGGAGE TAG         1   

             InvoiceDate  UnitPrice  CustomerID         Country  
485  2010-12-01 11:45:00       4.95     17908.0  United Kingdom  
489  2010-12-01 11:45:00       2.10     17908.0  United Kingdom  
494  2010-12-01 11:45:00       1.25     17908.0  United Kingdom  
517  2010-12-01 11:45:00       1.25     17908.0  United Kingdom  

Part 2: Handling Missing Values#

print(df['CustomerID'].isna().mean())
df_dropped = df.dropna(subset=['CustomerID'])
print(df_dropped.shape)
0.03866666666666667
(1442, 8)
df['Description'] = df['Description'].fillna('UNKNOWN ITEM')
print(df['Description'].isna().sum())
0

Part 3: Removing Duplicates#

before = df.shape[0]
df = df.drop_duplicates()
after = df.shape[0]
print(f'Removed {before - after} real duplicate rows')
Removed 36 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))
Int64
0    17850
1    17850
2    17850
Name: CustomerID, dtype: Int64
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df['InvoiceDate'].dtype)
print(df['InvoiceDate'].dt.day_name().head(3))
datetime64[ns]
0    Wednesday
1    Wednesday
2    Wednesday
Name: InvoiceDate, dtype: object

Part 5: Cleaning Text Columns#

has_whitespace = (df['Description'].str.strip() != df['Description']).sum()
print(has_whitespace)
df['Description'] = df['Description'].str.strip()
270
print(df['Country'].str.upper().value_counts().head(5))
Country
UNITED KINGDOM    1261
NORWAY              73
EIRE                21
FRANCE              20
GERMANY             15
Name: count, dtype: int64

Part 6: Filtering Invalid or Suspicious Rows#

returns = df[df['Quantity'] < 0]
print(returns.shape[0])
print(returns[['InvoiceNo', 'Description', 'Quantity']].head(3))
12
    InvoiceNo                      Description  Quantity
141   C536379                         Discount        -1
154   C536383  SET OF 3 COLOURED  FLYING DUCKS        -1
235   C536391    PLASTERS IN TIN CIRCUS PARADE       -12
sales_only = df[(df['Quantity'] > 0) & (df['UnitPrice'] > 0)]
print(f'{df.shape[0]} total rows, {sales_only.shape[0]} genuine sales rows')
1406 total rows, 1394 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.

  1. What does df.isna().sum() tell you?

  2. Which call removes only the rows where CustomerID is missing?

  3. Why convert InvoiceDate with pd.to_datetime?

  4. What does this code do?

    df['Description'] = df['Description'].str.strip()
    
  5. How would you keep only genuine sales (positive quantity and price)?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. 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)
    
  2. Given a DataFrame with a messy Description (extra spaces, missing value) and Country in 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())
    
  3. 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.