Pandas Data Cleaning: Fixing the Messy Orders File

Textbook datasets are tidy. Real datasets are not. They arrive from several systems, typed by several people, with Bombay in one row and Mumbai in another, amounts written as ₹1,787.76, Rs. 2353 and NA, dates in four formats, and the same order entered twice. Analysts regularly report that 60 to 80 percent of their time goes into cleaning data. The quality of every chart and model afterwards depends on it, so this is a skill worth mastering properly.

In this lesson we take messy_orders.csv, a 438-row export that contains typical real-world problems, and clean it one problem at a time. Each step follows the same discipline: look at the problem, fix it with a small piece of vectorised code, check that it worked. You will leave with a repeatable cleaning recipe and a reusable clean_orders function that turns the whole file into an analysis-ready table.

Worked examples

Example 1: Read everything as text and audit

Question: What does the raw file look like, and what is wrong with it?

import pandas as pd
import numpy as np

raw = pd.read_csv("messy_orders.csv", dtype=str, keep_default_na=False)
print(raw.shape)
print(raw.head(4).to_string())

print(raw["city"].nunique(), raw["payment_method"].nunique(), raw["status"].nunique())
print(raw["order_id"].duplicated().sum(), raw.duplicated().sum())
print((raw == "").sum()[lambda s: s > 0])

(438, 9)
  order_id   customer_name       city    order_date   amount payment_method     status delivery_minutes          phone
0    M0128     Divya Verma  hyderabad    06/05/2025      462           Card   Returned               17     9337065511
1    M0028    Vivaan Joshi      Delhi  Dec 31, 2025       NA            cod  delivered                     96248-30641
2    M0238      Pranav Rao      DELHI    2025-03-07  1427.92            upi  cancelled               18  +919987924801
3    M0156       Amit Bhat  hyderabad     08-Apr-25       NA            upi  Delivered               30     9662511008
27 11 6
18 18
amount              21
delivery_minutes    80
dtype: int64

How it works: dtype=str stops pandas from guessing types, and keep_default_na=False stops it from converting text like NA into missing values behind your back. Reading everything as text lets you see the real mess and decide how to treat each problem. The audit counts distinct values per column, repeated order ids, fully duplicated rows, and empty strings per column.

Reading the result: The file has 438 rows, but 27 different city spellings, 11 payment spellings and 6 status spellings. There are 18 repeated order ids. The right number of cities is 6, so there is work to do.

Example 2: Trim and normalise text

Question: Remove stray whitespace and make capitalisation consistent.

df = raw.copy()

for col in ["customer_name", "city", "payment_method", "status"]:
    df[col] = df[col].str.strip()

df["customer_name"] = df["customer_name"].str.replace(r"\s+", " ", regex=True).str.title()
df["city_key"] = df["city"].str.lower()
df["status"] = df["status"].str.lower()

print(df["city_key"].nunique())
print(df["status"].value_counts().to_dict())
print(df["customer_name"].head(5).tolist())

13
{'delivered': 193, 'cancelled': 162, 'returned': 83}
['Divya Verma', 'Vivaan Joshi', 'Pranav Rao', 'Amit Bhat', 'Vivaan Sharma']

How it works: .str.strip() removes leading and trailing spaces. A regular expression collapses runs of spaces inside names, and .title() fixes PRIYA GUPTA into Priya Gupta. Lower-casing a copy of the city gives a key for the mapping step while keeping the original around for comparison. After lower-casing, statuses collapse to three values.

Reading the result: Whitespace and case alone reduced the city variants from 27 to 13 and the statuses from 6 to 3. The remaining differences are real spelling variants such as Bombay, Poona and Hyd.

Example 3: Map variants to canonical values

Question: Convert every city spelling to one standard name.

city_map = {
    "bengaluru": "Bengaluru", "bangalore": "Bengaluru", "bengalure": "Bengaluru",
    "mumbai": "Mumbai", "bombay": "Mumbai",
    "delhi": "Delhi", "new delhi": "Delhi",
    "hyderabad": "Hyderabad", "hyd": "Hyderabad",
    "chennai": "Chennai", "madras": "Chennai",
    "pune": "Pune", "poona": "Pune",
}

df["city_clean"] = df["city_key"].map(city_map)
print(df["city_clean"].isna().sum())
print(df["city_clean"].value_counts())

unmapped = df.loc[df["city_clean"].isna(), "city_key"].unique()
print(unmapped)

0
city_clean
Hyderabad    82
Chennai      77
Pune         77
Bengaluru    73
Delhi        72
Mumbai       57
Name: count, dtype: int64
<StringArray>
[]
Length: 0, dtype: str

How it works: Series.map(dictionary) looks up every value and returns NaN for keys that are not in the dictionary. Checking isna().sum() after mapping is the essential safety test: if it is not zero, the unmapped values tell you which variants you missed. A mapping dictionary also documents your business rules in one place.

Reading the result: All variants were covered (0 unmapped), leaving exactly 6 cities. The busiest is Hyderabad with 82 rows.

Example 4: Standardise payment methods and status

Question: Handle the other categorical columns the same way.

pay_map = {
    "upi": "UPI", "card": "Card", "credit card": "Card",
    "cod": "COD", "cash on delivery": "COD", "wallet": "Wallet",
}
df["payment_clean"] = df["payment_method"].str.lower().map(pay_map)
df["status_clean"] = df["status"].str.title()

print(df["payment_clean"].value_counts(dropna=False).to_dict())
print(df["status_clean"].value_counts().to_dict())
assert df["payment_clean"].notna().all()

{'UPI': 126, 'COD': 123, 'Card': 111, 'Wallet': 78}
{'Delivered': 193, 'Cancelled': 162, 'Returned': 83}

How it works: The same lower-case-then-map approach resolves Upi, upi and UPI into UPI, and Credit Card into Card. For statuses, lower-casing and title() already gives the three standard labels, so no mapping is needed. The assert stops the notebook loudly if a new, unmapped value ever appears.

Reading the result: There are now 4 payment methods (UPI, Card, COD, Wallet) and 3 statuses. The most used method is UPI.

Example 5: Clean currency amounts

Question: Turn ₹1,787.76, Rs. 2353, 1,159 and NA into numbers.

print(raw["amount"].sample(8, random_state=1).tolist())

def to_amount(s):
    s = s.str.replace("₹", "", regex=False)
    s = s.str.replace("Rs.", "", regex=False).str.replace(",", "", regex=False)
    s = s.str.strip()
    s = s.replace({"NA": np.nan, "": np.nan, "N/A": np.nan})
    return pd.to_numeric(s, errors="coerce")

df["amount_clean"] = to_amount(df["amount"])
print(df["amount_clean"].isna().sum())
print(df["amount_clean"].describe().round(2))
print((df["amount_clean"] <= 0).sum())

['₹1,787.76', '₹502.52', '1,159', '1,920', 'Rs. 2353', '1371.19', '744.44', '1214.68']
57
count     381.00
mean     1225.17
std       674.46
min       130.00
25%       614.53
50%      1203.13
75%      1813.62
max      2399.75
Name: amount_clean, dtype: float64
0

How it works: We strip currency symbols and thousand separators, convert known placeholders to NaN, and call pd.to_numeric(errors="coerce"), which turns anything still unparseable into NaN instead of raising. After conversion the column is numeric, so statistics work. Always check the count of NaNs afterwards and look at a few of the rows that failed.

Reading the result: 57 amounts are missing (the NA entries). The remaining 381 range from ₹130 to ₹2,400 with a median of ₹1,203.

Example 6: Parse dates in mixed formats

Question: Handle 2025-01-11, 17/04/2025, 20-Aug-25 and Dec 01, 2025.

print(raw["order_date"].sample(6, random_state=4).tolist())

formats = ["%Y-%m-%d", "%d/%m/%Y", "%d-%b-%y", "%b %d, %Y"]

def parse_dates(s):
    out = pd.Series(pd.NaT, index=s.index, dtype="datetime64[ns]")
    for fmt in formats:
        parsed = pd.to_datetime(s, format=fmt, errors="coerce")
        out = out.fillna(parsed)
    return out

df["date_clean"] = parse_dates(df["order_date"].str.strip())
print(df["date_clean"].isna().sum())
print(df["date_clean"].min().date(), df["date_clean"].max().date())
print(df["date_clean"].dt.year.value_counts().to_dict())

['2025-04-17', '28/11/2025', '2025-05-21', '2025-05-16', 'Oct 22, 2025', 'Apr 16, 2025']
0
2025-01-01 2025-12-31
{2025: 438}

How it works: Each row is tried against every known format in turn; errors="coerce" turns non-matching rows into NaT ("not a time"), and fillna keeps the first successful parse. We specify explicit formats instead of guessing because 03/04/2025 is ambiguous (3 April or March 4?). In this file slashes are day first, as confirmed by values such as 17/04/2025.

Reading the result: All 438 dates were parsed (0 failures), spanning 2025-01-01 to 2025-12-31. Never trust automatic date guessing on mixed data without checking the range.

Example 7: Impossible values

Question: Fix delivery times like -5, and check ranges.

dm = pd.to_numeric(df["delivery_minutes"], errors="coerce")
print(dm.describe().round(1))
print((dm < 0).sum(), (dm > 120).sum(), dm.isna().sum())

df["delivery_clean"] = dm.where((dm >= 0) & (dm <= 180))
print(df["delivery_clean"].isna().sum())

by_status = df.groupby("status_clean")["delivery_clean"].agg(["count", "size"])
print(by_status)

count    358.0
mean     177.4
std      362.0
min       -5.0
25%       17.0
50%       25.0
75%       32.0
max      999.0
Name: delivery_minutes, dtype: float64
62 58 80
200
              count  size
status_clean             
Cancelled        88   162
Delivered       107   193
Returned         43    83

How it works: We convert to numbers, then use Series.where(condition), which keeps values where the condition is true and sets everything else to NaN. A delivery time must be between 0 and a plausible maximum. The final table compares non-missing counts with row counts for each status, a quick consistency check: cancelled orders should have no delivery time.

Reading the result: 62 negative delivery times were invalid and became missing. Cancelled orders have 88 real delivery times; that is a data-quality question to raise with whoever owns the source system.

Example 8: Normalise phone numbers

Question: Convert +91 94971 96235, 91821-35135 and 9548841346 to one format.

print(raw["phone"].sample(6, random_state=3).tolist())

digits = df["phone"].str.replace(r"\D", "", regex=True)
last10 = digits.str[-10:]
valid = last10.str.fullmatch(r"[6-9]\d{9}")

df["phone_clean"] = np.where(valid, "+91" + last10, None)
print(df["phone_clean"].head(4).tolist())
print(valid.sum(), (~valid).sum())
print(df.loc[~valid, "phone"].head(5).tolist())

['+919538537428', '+91 94971 96235', '91821-35135', '+919542143018', '+919867667568', '94685-18576']
['+919337065511', '+919624830641', '+919987924801', '+919662511008']
438 0
[]

How it works: \D matches any non-digit, so replacing it with nothing leaves only digits. Taking the last ten digits drops a leading 91 country code. An Indian mobile number starts with 6, 7, 8 or 9, which fullmatch(r"[6-9]\d{9}") checks. Invalid numbers become None rather than a wrong number. The regex lesson explains this syntax fully.

Reading the result: 438 phones normalised to the +91XXXXXXXXXX format, and 0 could not be recognised and were left empty. Throwing away a doubtful value is often safer than keeping a wrong one.

Example 9: Remove duplicates

Question: Detect and remove repeated orders.

print(df.duplicated(subset="order_id").sum())

dups = df[df["order_id"].duplicated(keep=False)].sort_values("order_id")
print(dups[["order_id", "city_clean", "amount_clean", "date_clean"]].head(4).to_string())

same_content = df.duplicated(subset=["order_id", "city_clean", "amount_clean", "date_clean"]).sum()
print(same_content)

clean = df.drop_duplicates(subset="order_id", keep="first").reset_index(drop=True)
print(len(df), len(clean), clean["order_id"].is_unique)

18
    order_id city_clean  amount_clean date_clean
147    M0031    Chennai       2033.00 2025-04-10
318    M0031    Chennai       2033.00 2025-04-10
69     M0057  Hyderabad        199.65 2025-11-26
57     M0057  Hyderabad        199.65 2025-11-26
18
438 420 True

How it works: duplicated(subset=...) marks the second and later occurrences of the same key; keep=False marks all copies so you can inspect them side by side. We compare a content subset to confirm that the repeated rows are true duplicates (not different orders sharing an id) and then use drop_duplicates to keep the first. Always inspect before deleting.

Reading the result: 18 duplicate rows were removed, leaving 420 unique orders, and the id is now unique (True).

Example 10: One reusable cleaning function

Question: Combine all the steps and test the result.

def clean_orders(path):
    raw = pd.read_csv(path, dtype=str, keep_default_na=False)
    d = raw.copy()
    for col in ["customer_name", "city", "payment_method", "status"]:
        d[col] = d[col].str.strip()
    out = pd.DataFrame({
        "order_id": d["order_id"],
        "customer_name": d["customer_name"].str.replace(r"\s+", " ", regex=True).str.title(),
        "city": d["city"].str.lower().map(city_map),
        "order_date": parse_dates(d["order_date"].str.strip()),
        "amount": to_amount(d["amount"]),
        "payment_method": d["payment_method"].str.lower().map(pay_map),
        "status": d["status"].str.title(),
    })
    mins = pd.to_numeric(d["delivery_minutes"], errors="coerce")
    out["delivery_minutes"] = mins.where(mins.between(0, 180))
    digits = d["phone"].str.replace(r"\D", "", regex=True).str[-10:]
    out["phone"] = np.where(digits.str.fullmatch(r"[6-9]\d{9}"), "+91" + digits, None)
    return out.drop_duplicates("order_id").reset_index(drop=True)

final = clean_orders("messy_orders.csv")
print(final.shape)
print(final.dtypes.to_dict())
print(final.isna().sum().to_dict())
assert final["order_id"].is_unique and final["city"].notna().all() and final["payment_method"].notna().all()

final.to_csv("orders_clean.csv", index=False)
print(final.head(3).to_string())

(420, 9)
{'order_id': <StringDtype(storage='python', na_value=nan)>, 'customer_name': <StringDtype(storage='python', na_value=nan)>, 'city': <StringDtype(storage='python', na_value=nan)>, 'order_date': dtype('<M8[ns]'), 'amount': dtype('float64'), 'payment_method': <StringDtype(storage='python', na_value=nan)>, 'status': <StringDtype(storage='python', na_value=nan)>, 'delivery_minutes': dtype('float64'), 'phone': <StringDtype(storage='python', na_value=nan)>}
{'order_id': 0, 'customer_name': 0, 'city': 0, 'order_date': 0, 'amount': 55, 'payment_method': 0, 'status': 0, 'delivery_minutes': 190, 'phone': 0}
  order_id customer_name       city order_date   amount payment_method     status  delivery_minutes          phone
0    M0128   Divya Verma  Hyderabad 2025-05-06   462.00           Card   Returned              17.0  +919337065511
1    M0028  Vivaan Joshi      Delhi 2025-12-31      NaN            COD  Delivered               NaN  +919624830641
2    M0238    Pranav Rao      Delhi 2025-03-07  1427.92            UPI  Cancelled              18.0  +919987924801

How it works: Wrapping the cleaning in a function makes it repeatable: next month's export goes through the identical steps. The assert line documents what "clean" means and fails loudly if a future file breaks it. Finally the cleaned table is saved for later lessons.

Reading the result: The cleaned table has 420 rows and 9 columns. Remaining missing values are real gaps: 55 amounts, 190 delivery times and 0 phones. The average order is worth ₹1,231.

Common mistakes

1. Letting pandas guess types on a messy file, so NA silently becomes missing.

2. Mapping without checking for unmapped values, which creates a column full of NaN.

3. Parsing ambiguous dates without choosing day-first or month-first deliberately.

4. Dropping duplicates without inspecting them first.

5. Cleaning by hand in a spreadsheet, which cannot be repeated. Always code the cleaning.

pd.to_numeric(pd.Series(["₹120", "95"]))

Traceback (most recent call last):
  File "example.py", line 2478, in pandas._libs.lib.maybe_convert_numeric
ValueError: Unable to parse string "₹120"

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    pd.to_numeric(pd.Series(["₹120", "95"]))
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 235, in to_numeric
    values, new_mask = lib.maybe_convert_numeric(  # type: ignore[call-overload]
                       ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        values,
        ^^^^^^^
    ...<6 lines>...
        ),
        ^^
    )
    ^
  File "example.py", line 2521, in pandas._libs.lib.maybe_convert_numeric
ValueError: Unable to parse string "₹120" at position 0

Practice questions

Practice 1 (Easy): Clean one text column

Convert the customer_name values in the raw data to proper case with single spaces, and count how many distinct names remain.

names = raw["customer_name"].str.strip().str.replace(r"\s+", " ", regex=True).str.title()
print(names.nunique(), raw["customer_name"].nunique())

295 392

Practice 2 (Medium): Valid amounts by city

Using the cleaned table, compute the mean amount per city, ignoring missing amounts, sorted from highest to lowest.

by_city = final.groupby("city")["amount"].mean().sort_values(ascending=False).round(0)
print(by_city)

city
Chennai      1309.0
Mumbai       1293.0
Delhi        1267.0
Bengaluru    1198.0
Pune         1184.0
Hyderabad    1150.0
Name: amount, dtype: float64

Practice 3 (Hard): Flag rows that need review

Add a needs_review column to final that is True when the amount is missing, the phone is missing, or a cancelled order has a delivery time. Report the share of rows flagged.

cancelled_with_time = (final["status"] == "Cancelled") & final["delivery_minutes"].notna()
final["needs_review"] = final["amount"].isna() | final["phone"].isna() | cancelled_with_time
print(final["needs_review"].sum(), f"{final['needs_review'].mean():.1%}")

130 31.0%

Key takeaways

• Read messy files as text, audit first, then convert deliberately.
• Normalise text with .str.strip(), .str.lower() and .str.title(), then map variants with a dictionary and check for unmapped values.
• Convert currency with string replacements plus pd.to_numeric(errors="coerce").
• Parse dates with explicit formats and verify the resulting range.
• Use where or masks to turn impossible values into NaN.
• Inspect duplicates before dropping them.
• Wrap cleaning in a function with assert checks so it is repeatable.