Skip to main content

Data Cleaning & Transformation with Pandas

Introduction​

Real business data is almost always messy. Data cleaning is consistently reported as consuming 60–80% of a data analyst's time, so Pandas provides a complete toolkit for identifying and resolving data quality issues before any analysis can be trusted. This chapter covers cleaning (missing values, duplicates, wrong data types, outliers) and transformation (derived columns, custom row-wise logic, sorting/ranking) — turning the raw RetailEdge_Sales.csv from the previous chapter into something safe to analyze.

Concepts​

Missing values​

  • df.isnull().sum() counts missing values per column; dividing by len(df) gives a percentage.
  • df.dropna(subset=[...]) removes rows missing a critical column (one you can't sensibly fill in).
  • df['col'].fillna(...) fills gaps: .median() for numeric columns (robust to outliers), .mode()[0] for categorical columns (most frequent value), method='ffill' to carry the last known value forward (useful for ordered/time-series data), or a fixed business-logic value (e.g. missing discount really means 0).

Duplicates​

  • df.duplicated() flags fully duplicate rows; df.duplicated(subset=['OrderID']) flags duplicates on a column that should be unique.
  • df.drop_duplicates(subset=[...], keep='first') removes them, keeping the first occurrence.

Data type corrections​

  • pd.to_datetime(df['Date']) converts a string column to a real datetime — unlocking .dt.year, .dt.month, .dt.quarter, .dt.day_name().
  • .astype('category') for columns with a small number of repeated string values (e.g. Store) — saves memory and speeds up grouping.
  • .str.strip() removes stray leading/trailing whitespace, a common artifact of hand-edited CSV files.

Outliers​

  • The IQR (interquartile range) method: IQR = Q3 - Q1; anything below Q1 - 1.5*IQR or above Q3 + 1.5*IQR is a statistical outlier.
  • Three common strategies once found: remove them, cap/winsorize them (.clip(lower=, upper=)), or flag them in a new column for a human to investigate — which one is right depends on whether the outlier is a data error or a genuine (if unusual) business event.

Transformation​

  • df['new_col'] = ... creates a derived column from existing ones (a profit margin, a revenue band).
  • .apply(func) on a single column applies func to each value; .apply(func, axis=1) applies func to each row, letting it read multiple columns at once.
  • .map(dict) replaces values using a lookup dictionary — simpler and faster than .apply() when you just need value substitution.
  • pd.cut(series, bins=[...], labels=[...]) bins a continuous column into named categories.
  • .rank() ranks values; .groupby('col')['other_col'].rank() ranks within each group rather than across the whole dataset.

Examples​

Missing values:

missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
print(pd.DataFrame({'Missing Count': missing, 'Missing %': missing_pct}))

df_clean = df.dropna(subset=['Revenue'])                                   # critical column
df_clean['Profit'] = df_clean['Profit'].fillna(df_clean['Profit'].median())
df_clean['SalesRep'] = df_clean['SalesRep'].fillna(df_clean['SalesRep'].mode()[0])
df_clean['Discount'] = df_clean['Discount'].fillna(0.0)                    # business rule

Duplicates:

print('Duplicate OrderIDs:', df.duplicated(subset=['OrderID']).sum())
df_clean = df.drop_duplicates(subset=['OrderID'], keep='first')

Type corrections:

df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')
df['Year'], df['Month'], df['Quarter'] = df['Date'].dt.year, df['Date'].dt.month, df['Date'].dt.quarter

if df['Discount'].max() > 1:              # e.g. stored as 10 instead of 0.10
    df['Discount'] = df['Discount'] / 100

for col in ['Store', 'Region', 'Category']:
    df[col] = df[col].astype('category')

Outliers:

Q1, Q3 = df['Revenue'].quantile(0.25), df['Revenue'].quantile(0.75)
IQR = Q3 - Q1
lower_bound, upper_bound = Q1 - 1.5 * IQR, Q3 + 1.5 * IQR

outliers = df[(df['Revenue'] < lower_bound) | (df['Revenue'] > upper_bound)]
df['Revenue_Capped'] = df['Revenue'].clip(lower=lower_bound, upper=upper_bound)
df['is_outlier'] = ((df['Revenue'] < lower_bound) | (df['Revenue'] > upper_bound)).astype(int)

Derived columns and row-wise logic:

df['Profit_Margin_Pct'] = (df['Profit'] / df['Revenue'] * 100).round(2)

df['Revenue_Tier'] = pd.cut(
    df['Revenue'],
    bins=[0, 1000, 10000, 50000, float('inf')],
    labels=['Small', 'Medium', 'Large', 'Enterprise'],
    right=False,
)

def effective_price(row):
    return row['Unit_Price'] * (1 - row['Discount'])

df['Effective_Price'] = df.apply(effective_price, axis=1)

region_map = {'Mumbai': 'West', 'Pune': 'West', 'Delhi': 'North', 'Jaipur': 'North'}
df['Region_Derived'] = df['Store'].map(region_map)

Sorting and ranking:

top_orders = df.sort_values('Revenue', ascending=False).head(10)
df['Revenue_Rank'] = df['Revenue'].rank(ascending=False, method='dense').astype(int)
df['Store_Revenue_Rank'] = df.groupby('Store')['Revenue'].rank(ascending=False, method='dense').astype(int)

Challenges​

1. A cleaning pipeline​

Build a complete cleaning function for RetailEdge_Sales.csv:

  • Print a missing value report showing count and % for every column.
  • Drop rows where both Revenue AND Profit are missing.
  • Fill missing Discount with 0.0, missing SalesRep with 'Unknown'.
  • Remove exact duplicate orders (same OrderID).
  • Parse the Date column and extract Month, Quarter, and day of week.
  • Flag orders where Profit is negative as 'Loss Orders' in a new column.
  • Report final shape and missing value count after all cleaning steps.
💡 Show Answer
import pandas as pd

def clean_data(df):
    missing = df.isnull().sum()
    missing_pct = (missing / len(df) * 100).round(2)
    print(pd.DataFrame({'Missing Count': missing, 'Missing %': missing_pct}))

    df = df.dropna(subset=['Revenue', 'Profit'], how='all')
    df['Discount'] = df['Discount'].fillna(0.0)
    df['SalesRep'] = df['SalesRep'].fillna('Unknown')
    df = df.drop_duplicates(subset=['OrderID'], keep='first')

    df['Date'] = pd.to_datetime(df['Date'])
    df['Month'] = df['Date'].dt.month
    df['Quarter'] = df['Date'].dt.quarter
    df['DayOfWeek'] = df['Date'].dt.day_name()

    df['Order_Type'] = df['Profit'].apply(lambda p: 'Loss Orders' if p < 0 else 'Profitable')

    print('Final shape:', df.shape)
    print('Remaining missing values:', df.isnull().sum().sum())
    return df

df = pd.read_csv('RetailEdge_Sales.csv', parse_dates=['Date'])
df_clean = clean_data(df)

2. Data transformation​

  • Create a Profit_Category column: 'Loss' (Profit < 0), 'Low' (< 5000), 'Medium' (< 20000), 'High' (>= 20000).
  • Compute Effective_Revenue = Revenue * (1 - Discount) as a new column.
  • Using .apply() with axis=1, create Order_Score = (Revenue*0.6 + Profit*0.4) / 1000.
  • Rank all orders by Profit within each Profit_Category group (groupby + rank).
  • Create a Season column: Dec–Feb=Winter, Mar–May=Spring, Jun–Aug=Monsoon, Sep–Nov=Autumn.
💡 Show Answer
def profit_category(p):
    if p < 0:
        return 'Loss'
    elif p < 5000:
        return 'Low'
    elif p < 20000:
        return 'Medium'
    return 'High'

df['Profit_Category'] = df['Profit'].apply(profit_category)
df['Effective_Revenue'] = df['Revenue'] * (1 - df['Discount'])
df['Order_Score'] = df.apply(lambda row: (row['Revenue'] * 0.6 + row['Profit'] * 0.4) / 1000, axis=1)
df['Profit_Rank_In_Category'] = df.groupby('Profit_Category')['Profit'].rank(ascending=False, method='dense')

season_map = {12: 'Winter', 1: 'Winter', 2: 'Winter',
              3: 'Spring', 4: 'Spring', 5: 'Spring',
              6: 'Monsoon', 7: 'Monsoon', 8: 'Monsoon',
              9: 'Autumn', 10: 'Autumn', 11: 'Autumn'}
df['Season'] = df['Date'].dt.month.map(season_map)