Fixing Types, Duplicates & Text

In Inspecting Data you found problems in the practice files: marks stored as text, cities written in several ways, payment modes in mixed case and orders exported twice. None of these cause an error. They just quietly give wrong answers, which makes them dangerous.

In this lesson you will fix all three kinds of problem: wrong types, duplicate rows and inconsistent text. At the end you will put the fixes into one reusable cleaning function.

Fixing types

A column's dtype decides what you can do with it. Numbers stored as text cannot be averaged; dates stored as text cannot be grouped by month. These are the main conversion tools:

import pandas as pd

students = pd.read_csv("students.csv")
print(students["maths"].dtype, students["maths"].head(3).tolist())

try:
    students["maths"].astype(float)
except ValueError as err:
    print("astype failed:", err)

subjects = ["maths", "science", "english", "hindi", "social_science"]
for col in subjects:
    students[col] = pd.to_numeric(students[col], errors="coerce")

print(students[subjects].dtypes.unique())
print(students[subjects].isna().sum().sum(), "absent marks are now NaN")
print(students[subjects].mean().round(1))

str ['50', '52', '64']
astype failed: could not convert string to float: 'AB'
[dtype('float64') dtype('int64')]
9 absent marks are now NaN
maths             58.2
science           59.1
english           62.1
hindi             66.2
social_science    59.8
dtype: float64

astype(float) refuses because of the AB values. pd.to_numeric(..., errors="coerce") converts everything it can and turns the rest into NaN. That is exactly right here, because AB means "no mark". (english and social_science had no AB values, so they were already int64.) But be careful: coerce also hides genuine typing mistakes, so always count how many NaN values it created and check that the number makes sense.

Category dtype

A column with a few repeated text values, such as city or school_type, can be stored as a category. pandas then keeps each distinct text once and stores small codes for the rows. This saves memory and makes grouping faster.

import pandas as pd

sales = pd.read_csv("sales.csv")
before = sales["state"].memory_usage(deep=True)
sales["state"] = sales["state"].astype("category")
after = sales["state"].memory_usage(deep=True)

print(sales["state"].dtype, "| categories:", len(sales["state"].cat.categories))
print(f"{before:,} bytes -> {after:,} bytes")

category | categories: 9
41,716 bytes -> 1,597 bytes

Convert to category after cleaning the text, otherwise Delhi and DELHI become two separate categories.

Removing duplicate rows

duplicated() marks rows that repeat an earlier row exactly, and drop_duplicates() removes them. Use subset to decide what counts as "the same", and keep to choose which copy survives.

import pandas as pd

sales = pd.read_csv("sales.csv")
print("Rows:", len(sales))
print("Exact duplicates:", sales.duplicated().sum())
print("Repeated order IDs:", sales.duplicated(subset=["order_id"]).sum())

sales = sales.drop_duplicates()
print("Rows after drop_duplicates():", len(sales))
print("Order IDs unique now?", sales["order_id"].is_unique)

log = pd.DataFrame({
    "student": ["Isha", "Rohan", "Isha"],
    "attempt": [1, 1, 2],
    "score": [62, 71, 78],
})
print(log.drop_duplicates(subset=["student"], keep="last"))

Rows: 1217
Exact duplicates: 8
Repeated order IDs: 8
Rows after drop_duplicates(): 1209
Order IDs unique now? True
  student  attempt  score
1   Rohan        1     71
2    Isha        2     78

In sales.csv every repeated order ID is also a fully identical row, so both checks agree and dropping exact duplicates fixes the problem. Checking is_unique afterwards confirms it. The small log example shows subset and keep="last": only the student name decides what a duplicate is, and the latest attempt is kept.

Cleaning text with .str methods

Text columns have a .str accessor with vectorised versions of Python's string methods. The most useful for cleaning are strip() (remove spaces at both ends), lower(), upper() and title() (Capital First Letters), and replace().

import pandas as pd

sales = pd.read_csv("sales.csv")
print(sales["city"].nunique(), "city spellings before")

sales["city"] = (
    sales["city"]
    .str.strip()
    .str.title()
    .replace({"Bangalore": "Bengaluru", "Cochin": "Kochi"})
)
print(sales["city"].nunique(), "city spellings after")
print(sales["city"].value_counts().head(4))

pay_map = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery",
           "cash on delivery": "Cash on Delivery", "net banking": "Net Banking",
           "netbanking": "Net Banking", "wallet": "Wallet"}
sales["payment_mode"] = sales["payment_mode"].str.strip().str.lower().map(pay_map)
print(sales["payment_mode"].value_counts(dropna=False))

20 city spellings before
10 city spellings after
city
Delhi        190
Mumbai       185
Bengaluru    156
Chennai      124
Name: count, dtype: int64
payment_mode
UPI                 617
Card                210
Cash on Delivery    187
Wallet              102
Net Banking          90
NaN                  11
Name: count, dtype: int64

For cities, strip() and title() fix spaces and capital letters, and replace() with a dictionary maps old or alternative names (Bangalore, Cochin) to the official ones. For payment modes, the pattern is different: first make everything lowercase so there are fewer variants, then use map() with a dictionary that lists every known spelling. Any spelling not in the dictionary becomes NaN, which is useful: a new unexpected value shows up as missing instead of slipping through. Here the 11 NaN values are only the orders that had no payment mode to begin with, so no spelling was missed.

Pattern-based text fixes

When values follow a pattern, regular expressions help. str.replace() can use a regex (a text pattern), str.extract() pulls out the part that matches, and str.split(expand=True) splits text into columns.

import pandas as pd

products = pd.Series(["Basmati Rice 5kg", "Atta 10kg", "Masala Chai 250g", "Ghee 1L", "Almonds 500g"])

parts = products.str.extract(r"^(.*) (\d+)(kg|g|L)$")
parts.columns = ["item", "amount", "unit"]
parts["amount"] = parts["amount"].astype(int)
print(parts)

phones = pd.Series(["98765 43210", "+91-98123-45678", "(0) 99887 76655"])
print(phones.str.replace(r"\D", "", regex=True).str[-10:])

names = pd.Series(["  priya SHARMA", "Rohan  verma "])
clean = names.str.split().str.join(" ").str.title()
print(clean.str.split(" ", expand=True))

           item  amount unit
0  Basmati Rice       5   kg
1          Atta      10   kg
2   Masala Chai     250    g
3          Ghee       1    L
4       Almonds     500    g
0    9876543210
1    9812345678
2    9988776655
dtype: str
       0       1
0  Priya  Sharma
1  Rohan   Verma

r"^(.*) (\d+)(kg|g|L)$" reads: any text, a space, some digits, then kg, g or L at the end. Each pair of brackets becomes one column. \D means "any character that is not a digit", so removing them and keeping the last 10 digits gives clean Indian mobile numbers. Splitting and re-joining with one space collapses double spaces inside names.

A reusable cleaning function

Put your cleaning steps into a function so you can rerun them on next month's file, and finish with assertions: checks that stop the program if something is still wrong.

import pandas as pd

def clean_sales(path):
    df = pd.read_csv(path).drop_duplicates()
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    pay_map = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery",
               "cash on delivery": "Cash on Delivery", "netbanking": "Net Banking",
               "net banking": "Net Banking", "wallet": "Wallet"}
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(pay_map).fillna("Unknown")
    df["order_date"] = pd.to_datetime(df["order_date"], format="%Y-%m-%d")
    df["quantity"] = df["quantity"].astype("Int64")
    return df

sales = clean_sales("sales.csv")
assert sales["order_id"].is_unique
assert sales["city"].nunique() == 10, sales["city"].unique()
print(sales.shape)
print(sales.dtypes)

(1209, 10)
order_id                   str
order_date      datetime64[us]
city                       str
state                      str
category                   str
product                    str
quantity                 Int64
unit_price               int64
discount                 int64
payment_mode               str
dtype: object

If a future file contains a new spelling such as Gurgaon, the assertion about 10 cities fails and prints the city list, so the problem is caught before it reaches a report. The quantity outliers (-1 and 120) are still there; they are the topic of the next lesson, Finding & Handling Outliers.

Common mistakes

• Using errors="coerce" blindly. It turns every unexpected value into NaN without telling you. Count the new NaN values.
• Converting to category before cleaning text. Each spelling becomes its own category.
• Forgetting strip(). "Pune " and "Pune" look identical in a printout but are different values.
• map() without a complete dictionary. Values you did not list become NaN. That is useful as a check, but only if you look for them.

Key points

• pd.to_numeric(errors="coerce") and pd.to_datetime() fix types; astype() converts clean data.
• Category dtype stores repeated text compactly; apply it after cleaning.
• duplicated() and drop_duplicates(subset=..., keep=...) find and remove repeated rows.
• .str.strip(), .str.lower(), .str.title(), replace() and map() standardise text.
• Regex methods str.replace(), str.extract() and str.split(expand=True) handle patterns.
• Wrap cleaning in a function and finish with assert checks.

import pandas as pd

students = pd.read_csv("students.csv").drop_duplicates()
students["name"] = students["name"].str.split().str.join(" ").str.title()
students["gender"] = students["gender"].str.upper()

school = students["school_type"].str.strip().str.lower().str.rstrip(".")
students["school_type"] = school.map({"government": "Government", "govt": "Government", "private": "Private"})

print(len(students))
print(students["gender"].value_counts())
print(students["school_type"].value_counts(dropna=False))

480
gender
M    261
F    219
Name: count, dtype: int64
school_type
Government    241
Private       239
Name: count, dtype: int64