Tutorial 10: Advanced Data Cleaning and Transformation with Pandas -

Introduction

In real-world projects, data is rarely clean. You’ll routinely encounter the same underlying value written in multiple inconsistent ways: " mumbai ", "MUMBAI", "mumbai", "₹75,000", "75000", "Yes", "Y", "TRUE". To a human these are obviously equivalent — but to a computer, they’re all different values unless you standardize them.

Data transformation converts messy data into a consistent, usable format:

Raw Data → Inspect → Clean → Transform → Create New Features → Analysis-Ready Data

You’ll learn to: clean text with string methods, convert data types, replace inconsistent values, map categorical values, create new columns, apply custom functions, and use apply(), map(), replace(), and assign() together as a complete transformation workflow.

1. Create a Messy Dataset

import pandas as pd

data = {
    "Name": [" Amit ", "PRIYA", "rahul", " Sneha ", "VIKRAM"],
    "City": ["mumbai", "MUMBAI", " Mumbai ", "Delhi", "DELHI"],
    "Salary": ["₹75,000", "₹60,000", "85000", "₹90,000", "65000"],
    "Experience": ["5 years", "3 years", "7 years", "8 years", "4 years"],
    "Status": ["Y", "N", "Yes", "No", "Y"]
}

df = pd.DataFrame(data)
df

This dataset intentionally has: extra spaces, inconsistent capitalization, currency symbols, commas, text mixed with numbers, and multiple representations of the same category.

2. String Manipulation with `.str`

Pandas provides the .str accessor for applying text operations across an entire column at once:

Text Column → .str → String Operations

3. Remove Extra Spaces

Current values: " Amit ", " Sneha ". Clean them:

df["Name"] = df["Name"].str.strip()

Result: Amit, Sneha (no leading/trailing spaces).

4. Convert Text to Lowercase / Uppercase / Title Case

df["Name"] = df["Name"].str.lower()

df["Name"] = df["Name"].str.upper()

For names and city names, title case is usually the most presentable choice:

df["Name"] = df["Name"].str.title()
df["City"] = df["City"].str.strip().str.title()

Now:

Name	City
Amit	Mumbai
Priya	Mumbai
Rahul	Mumbai
Sneha	Delhi
Vikram	Delhi

5. Replace Text

Replace “Mumbai” with “Bombay”:

df["City"] = df["City"].replace("Mumbai", "Bombay")

Multiple replacements at once, using a dictionary:

df["City"] = df["City"].replace({
    "Mumbai": "Bombay",
    "Delhi": "New Delhi"
})

6. Use `.str.replace()` for Characters Inside Strings

text = pd.Series(["₹75,000", "₹60,000", "₹90,000"])

# Remove the currency symbol
text = text.str.replace("₹", "")

# Remove commas
text = text.str.replace(",", "")

Now: 75000, 60000, 90000.

(Note: .replace() on a Series replaces whole values; .str.replace() replaces substrings/characters within each value — an important distinction.)

7. Convert Salary to Numeric

df["Salary"] = (
    df["Salary"]
    .str.replace("₹", "", regex=False)
    .str.replace(",", "", regex=False)
)

df["Salary"] = pd.to_numeric(df["Salary"])

Check: df.dtypes should now show Salary int64.

(The regex=False flag tells Pandas to treat "₹" and "," as literal characters rather than regular-expression patterns — safer here since neither is a regex special character that needs escaping, but it’s good practice to be explicit.)

8. Handling Invalid Numeric Data

data = pd.Series(["50000", "60000", "unknown", "75000"])

pd.to_numeric(data, errors="coerce")

The invalid value "unknown" becomes NaN instead of crashing the whole conversion — extremely useful for real-world data with occasional garbage entries.

9. Clean Experience Values

Current values: 5 years, 3 years, 7 years. Remove the " years" suffix:

df["Experience"] = df["Experience"].str.replace(" years", "", regex=False)

Convert to integer:

df["Experience"] = df["Experience"].astype(int)

Now: 5, 3, 7, 8, 4

10. Using `astype()`

astype() converts a column’s data type directly. Common conversions:

.astype(int)
.astype(float)
.astype(str)
.astype(bool)

Example:

df["Salary"] = df["Salary"].astype(float)

11. Mapping Values

Suppose we want Y → Yes and N → No:

status_mapping = {"Y": "Yes", "N": "No"}
df["Status"] = df["Status"].map(status_mapping)

But our data also contains "Yes" and "No" directly — so first normalize the casing:

df["Status"] = df["Status"].str.strip().str.upper()

Now everything is: Y, N, YES, NO, Y. Use a fuller mapping:

status_mapping = {
    "Y": "Yes",
    "N": "No",
    "YES": "Yes",
    "NO": "No"
}
df["Status"] = df["Status"].map(status_mapping)

12. `map()` vs. `replace()`

map() — best for transforming values via a dictionary:

df["Status"].map({"Y": "Yes", "N": "No"})

replace() — also replaces values, but behaves differently:

df["Status"].replace({"Y": "Yes", "N": "No"})

Important difference: if a value isn’t found in the dictionary, .map() turns it into NaN, whereas .replace() leaves unmatched values unchanged. This matters a lot in practice — .map() is stricter (good for catching unexpected categories), .replace() is more forgiving.

13. Creating New Features

A feature is a new column derived from existing data — for example, turning a raw Salary number into a Salary Category label (High / Medium / Low).

14. Create a Feature with `apply()`

def salary_category(salary):
    if salary >= 80000:
        return "High"
    elif salary >= 60000:
        return "Medium"
    else:
        return "Low"

df["Salary_Category"] = df["Salary"].apply(salary_category)

Example result:

Salary	Salary Category
75000	Medium
60000	Medium
85000	High
90000	High
65000	Medium

15. Use a Lambda Function

For simple, one-line logic, a lambda (a short anonymous function) can replace a full def:

df["Salary_Level"] = df["Salary"].apply(
    lambda salary: "High" if salary >= 80000 else "Standard"
)

16. Create a Feature from Multiple Columns

Calculate annual salary from a monthly figure:

df["Annual_Salary"] = df["Salary"] * 12

Or salary per year of experience:

df["Salary_Per_Year"] = df["Salary"] / df["Experience"]

17. Apply a Function Row by Row

To combine logic across multiple columns for each row:

def employee_level(row):
    if row["Salary"] >= 80000 and row["Experience"] >= 7:
        return "Senior"
    elif row["Experience"] >= 5:
        return "Mid-Level"
    else:
        return "Junior"

df["Employee_Level"] = df.apply(employee_level, axis=1)

Important: axis=0 applies a function down each column; axis=1 applies it across each row — this is one of the most commonly mixed-up parameters in all of Pandas, so it’s worth memorizing: 1 = across a row.

18. Conditional Transformation with `np.where()`

import numpy as np

df["High_Earner"] = np.where(df["Salary"] >= 80000, "Yes", "No")

This means: IF Salary ≥ 80000 THEN “Yes” ELSE “No” — a compact, vectorized alternative to writing a full function for a single condition.

19. Multiple Conditions with `np.select()`

conditions = [
    df["Salary"] >= 85000,
    df["Salary"] >= 70000,
    df["Salary"] < 70000
]

choices = ["High", "Medium", "Low"]

df["Salary_Category"] = np.select(conditions, choices, default="Unknown")

np.select() checks each condition in order and assigns the matching choice — useful when you have more than two outcomes (where np.where() alone would get awkward).

20. String Feature Creation

Build a composite identifier from two columns:

df["Employee_ID"] = (
    df["Name"].str.upper()
    + "_"
    + df["City"].str.upper()
)

Example result: AMIT_MUMBAI

21. Extract Information from Text

emails = pd.Series([
    "amit@gmail.com",
    "priya@yahoo.com",
    "rahul@company.com"
])

emails.str.split("@").str[1]

Output: gmail.com, yahoo.com, company.com

Alternative approach using a regular expression:

emails.str.extract(r"@(.+)")

22. Regular Expressions

Regular expressions (“regex”) describe text patterns — useful when simple splitting isn’t enough.

phone_numbers = pd.Series(["9876543210", "9123456789"])

phone_numbers.str.match(r"^\d{10}$")

This checks whether each value is exactly 10 digits. Other useful string-pattern methods: .str.contains(), .str.startswith(), .str.endswith(), .str.match(), .str.extract().

23. `assign()` for Transformation

Instead of creating columns one at a time:

df["Salary_Category"] = ...
df["Annual_Salary"] = ...

You can create several at once with .assign():

df = df.assign(
    Annual_Salary=df["Salary"] * 12,
    Salary_Category=df["Salary"].apply(salary_category)
)

This can make longer transformation pipelines easier to read at a glance.

24. Method Chaining

Pandas lets you chain multiple operations together into a single readable pipeline:

clean_df = (
    df
    .assign(Name=lambda x: x["Name"].str.strip().str.title())
    .assign(City=lambda x: x["City"].str.strip().str.title())
)

Using lambda x: ... inside .assign() lets each step refer to the DataFrame as it exists at that point in the chain — useful when later steps depend on earlier ones.

25. Complete Cleaning Workflow

Starting again from the messy data:

data = {
    "Name": [" Amit ", "PRIYA", "rahul", " Sneha ", "VIKRAM"],
    "City": ["mumbai", "MUMBAI", " Mumbai ", "Delhi", "DELHI"],
    "Salary": ["₹75,000", "₹60,000", "85000", "₹90,000", "65000"],
    "Experience": ["5 years", "3 years", "7 years", "8 years", "4 years"],
    "Status": ["Y", "N", "Yes", "No", "Y"]
}
df = pd.DataFrame(data)

Clean names:

df["Name"] = df["Name"].str.strip().str.title()

Clean cities:

df["City"] = df["City"].str.strip().str.title()

Clean salary:

df["Salary"] = (
    df["Salary"]
    .str.replace("₹", "", regex=False)
    .str.replace(",", "", regex=False)
)
df["Salary"] = pd.to_numeric(df["Salary"])

Clean experience:

df["Experience"] = (
    df["Experience"]
    .str.replace(" years", "", regex=False)
    .astype(int)
)

Clean status:

df["Status"] = (
    df["Status"]
    .str.upper()
    .map({"Y": "Yes", "N": "No", "YES": "Yes", "NO": "No"})
)

26. Create New Features

df["Annual_Salary"] = df["Salary"] * 12

df["Salary_Category"] = np.select(
    [
        df["Salary"] >= 85000,
        df["Salary"] >= 70000,
        df["Salary"] < 70000
    ],
    ["High", "Medium", "Low"]
)

27. Final Clean Dataset

df

Name	City	Salary	Experience	Status	Annual Salary	Salary Category
Amit	Mumbai	75000	5	Yes	900000	Medium
Priya	Mumbai	60000	3	No	720000	Low
Rahul	Mumbai	85000	7	Yes	1020000	High
Sneha	Delhi	90000	8	No	1080000	High
Vikram	Delhi	65000	4	Yes	780000	Low

Transformation Workflow

Raw Data → Inspect
        → Clean Text (strip, lower, upper, replace)
        → Convert Types (astype, to_numeric, to_datetime)
        → Standardize Categories (map, replace)
        → Create Features (apply, lambda, np.where, np.select)
        → Final Clean Dataset

Important Functions

Function	Purpose
`.str.strip()`	Remove spaces
`.str.lower()` / `.str.upper()` / `.str.title()`	Change text case
`.str.replace()`	Replace text
`.str.contains()`	Search text
`.str.extract()`	Extract patterns
`.astype()`	Convert data type
`pd.to_numeric()`	Convert to numbers
`.map()` / `.replace()`	Map/replace values
`.apply()`	Apply a custom function
`np.where()`	Simple if/else conditions
`np.select()`	Multiple conditions
`.assign()`	Create columns

Practice Exercise

data = {
    "Product": [" Laptop ", "laptop", "LAPTOP", " Phone ", "phone"],
    "Price": ["₹50,000", "₹55,000", "60000", "₹30,000", "35000"],
    "Rating": ["4.5", "4.2", "4.8", "3.9", "4.1"]
}
df = pd.DataFrame(data)

Perform: clean and standardize product names to title case, remove the ₹ symbol and commas, convert Price to numeric, convert Rating to float, create a Price_Category column (Price ≥ 50000 → “Premium”, else “Standard”), and calculate the average price by product.

Key Takeaway: Advanced data cleaning transforms inconsistent raw data into structured, standardized, analysis-ready data.