Notebook 4 — Data Cleaning for CA Professionals

import pandas as pd
import numpy as np

inv = pd.read_csv("data/invoices_dirty.csv")
print("Shape:", inv.shape)
inv.head()

Shape: (206, 11)

# True wherever a cell is missing
inv.isna().head()

# How many missing per column?
inv.isna().sum()

Invoice_No         0
Date               0
Customer           0
City               8
Product            0
Quantity           0
Unit_Price         0
Amount_NPR        13
VAT_NPR            0
Total_NPR          0
Payment_Status     0
dtype: int64

# Your turn:

# Method 1 — drop any row that has a missing value
clean1 = inv.dropna()
print("Rows before:", len(inv), "→ after drop:", len(clean1))

Rows before: 206 → after drop: 185

# Method 2 — fill missing Amount_NPR with the median (more robust than mean)
clean2 = inv.copy()
median_amt = clean2["Amount_NPR"].median()
clean2["Amount_NPR"] = clean2["Amount_NPR"].fillna(median_amt)

# Fill missing City with the string "Unknown"
clean2["City"] = clean2["City"].fillna("Unknown")

print("Missing after fill:")
print(clean2.isna().sum())

Missing after fill:
Invoice_No        0
Date              0
Customer          0
City              0
Product           0
Quantity          0
Unit_Price        0
Amount_NPR        0
VAT_NPR           0
Total_NPR         0
Payment_Status    0
dtype: int64

# Your turn:

# Total duplicate rows
print("Duplicate rows:", inv.duplicated().sum())

# Drop them, keep the first occurrence
inv_dedup = inv.drop_duplicates()
print("After dedup    :", len(inv_dedup))

Duplicate rows: 6
After dedup    : 200

# Often we only care about duplicates in a key column like Invoice_No
print("Duplicate Invoice_No:", inv.duplicated(subset=["Invoice_No"]).sum())
inv_unique_inv = inv.drop_duplicates(subset=["Invoice_No"])
print("After dedup by Invoice_No:", len(inv_unique_inv))

Duplicate Invoice_No: 6
After dedup by Invoice_No: 200

# Your turn:

# Look — same customer, three different versions
print(inv["Customer"].value_counts().head(8))

Customer
Janakpur Wholesale       27
Biratnagar Enterprise    22
Himalayan Traders        22
Pokhara Mart             20
Annapurna Stores         20
Nepalgunj Goods          20
Birgunj Cargo            18
Everest Suppliers        18
Name: count, dtype: int64

# Standard recipe: strip spaces + put in title-case
inv["Customer"] = inv["Customer"].str.strip().str.title()
print(inv["Customer"].value_counts().head(8))

Customer
Janakpur Wholesale       27
Himalayan Traders        27
Biratnagar Enterprise    22
Everest Suppliers        21
Pokhara Mart             20
Annapurna Stores         20
Nepalgunj Goods          20
Birgunj Cargo            18
Name: count, dtype: int64

# Your turn:

print(inv.dtypes)

Invoice_No            str
Date                  str
Customer              str
City                  str
Product               str
Quantity            int64
Unit_Price        float64
Amount_NPR        float64
VAT_NPR           float64
Total_NPR         float64
Payment_Status        str
dtype: object

# Convert Date column from string → real datetime
inv["Date"] = pd.to_datetime(inv["Date"])

# Now we can extract year, month, weekday, etc.
inv["Year"]    = inv["Date"].dt.year
inv["Month"]   = inv["Date"].dt.month
inv["Weekday"] = inv["Date"].dt.day_name()

inv[["Date", "Year", "Month", "Weekday"]].head()

# Your turn:

amt = inv["Amount_NPR"].dropna()

q1, q3 = amt.quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr

print(f"Q1 = {q1:,.0f}   Q3 = {q3:,.0f}   IQR = {iqr:,.0f}")
print(f"Acceptable range: {low:,.0f}  →  {high:,.0f}")

outliers = inv[(inv["Amount_NPR"] < low) | (inv["Amount_NPR"] > high)]
print(f"\nOutlier rows: {len(outliers)}")
outliers[["Invoice_No", "Customer", "Amount_NPR"]].head()

Q1 = 33,024   Q3 = 138,120   IQR = 105,096
Acceptable range: -124,620  →  295,764

Outlier rows: 7

# Your turn:

inv = inv.rename(columns={
    "Amount_NPR": "Amount",
    "VAT_NPR":    "VAT",
    "Total_NPR":  "Total",
})
inv.columns

Index(['Invoice_No', 'Date', 'Customer', 'City', 'Product', 'Quantity',
       'Unit_Price', 'Amount', 'VAT', 'Total', 'Payment_Status', 'Year',
       'Month', 'Weekday'],
      dtype='str')

# Your code here
df = pd.read_csv("data/invoices_dirty.csv")
print("Before:", df.shape)

Before: (206, 11)