Cleaning messy data

Real data is never tidy. Names have extra spaces, dates come in two formats, a price has a rupee sign in it, the same order is entered twice. If you analyse such data without cleaning it, your totals are wrong and nobody notices. Analysts often spend more time cleaning than analysing, so being good at it is a real advantage.

In this tutorial you will find and fix seven typical problems in a small messy Brew & Bean export, first by looking, then with Python and pandas. The same ideas work in Excel and Google Sheets, and the equivalents are listed too. You need Python 3 with pandas installed (pip install pandas) and a text editor. If you have not used Python, read the steps with the spreadsheet equivalents and come back later.

Save the text below as messy.csv (encoding UTF-8) in a new folder. Read it carefully first. Before you run any code, try to spot the problems yourself: that habit is what makes an analyst good.

order_id,customer_name,order_date,item,qty,price,city
1001, Asha ,01/10/2026,Cappuccino,2,180,Bengaluru
1002,ravi,02/10/2026,cappuccino,1,"₹180",bangalore
1003,Meena,03/10/2026,Cold Brew,,220,Bengaluru
1004,Kiran,04-10-2026,masala chai,2,90,Bengaluru
1004,Kiran,04-10-2026,masala chai,2,90,Bengaluru
1005,Divya,05/10/2026,Espresso,-1,120,BENGALURU
1006,Asha,06/10/2026,Banana Bread,1,130,

7 data rows. Problems: extra spaces around Asha; lower-case names and items; a rupee sign in a price; Bangalore and Bengaluru and BENGALURU for the same city; two date formats; an exact duplicate (order 1004); a missing qty (1003); a negative qty (1005); a missing city (1006).

import pandas as pd

df = pd.read_csv("messy.csv", encoding="utf-8")

df.info()                    # column types and non-null counts
print(df.isna().sum())       # missing values per column
print(df.duplicated().sum()) # number of exact duplicate rows

info() shows 7 rows, qty has 6 non-null values and city has 6, and price and order_date are text, not numbers or dates. isna().sum() shows 1 missing in qty and 1 in city. duplicated().sum() prints 1. In an older pandas the type may be called object instead of str.

The script below does every fix in order and explains each choice in a comment. Keep it as a script (not manual edits) so you can rerun it when a new file arrives, and so a colleague can see exactly what you changed. Save it as clean.py next to messy.csv.

import pandas as pd

df = pd.read_csv("messy.csv", encoding="utf-8")

# 1. Remove exact duplicate rows
df = df.drop_duplicates()

# 2. Fix text: trim spaces and make capitalisation consistent
df["customer_name"] = df["customer_name"].str.strip().str.title()
df["item"] = df["item"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})

# 3. Fix types: price has a rupee sign in one row
df["price"] = pd.to_numeric(
    df["price"].astype(str).str.replace("₹", "", regex=False), errors="coerce"
)

# 4. Parse dates written in two different formats
d1 = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
d2 = pd.to_datetime(df["order_date"], format="%d-%m-%Y", errors="coerce")
df["order_date"] = d1.fillna(d2)

# 5. Missing and impossible values
df["qty"] = df["qty"].fillna(1)           # assumption: a missing qty means 1
df = df[df["qty"] > 0].copy()             # drop the row with qty -1
df["qty"] = df["qty"].astype(int)
df["city"] = df["city"].fillna("Unknown")

# 6. Add a calculated column and save
df["line_total"] = df["qty"] * df["price"]
df.to_csv("clean.csv", index=False)
print(df.to_string(index=False))

Prints 5 clean rows (orders 1001, 1002, 1003, 1004 and 1006) with columns order_id, customer_name, order_date, item, qty, price, city, line_total. Line totals are 360, 180, 220, 180 and 130. Order 1006 has city Unknown.

There is no single right way to treat missing values. Dropping rows loses information and can bias results if the missing rows are different from the rest. Filling with a guess can invent facts. A safer habit: keep a flag column such as qty_was_missing, mention the choice in your report, and compare results with and without those rows. If only a few percent of rows are affected, the choice rarely matters. If many are, fix the source of the problem.

I am cleaning a dataset of [describe it, e.g. coffee shop orders]. Here are the column names and 5 sample rows (no personal data):

[paste columns and rows]

List the data quality problems you can see, ranked by how much they could change totals. For each, suggest a pandas fix and tell me which assumptions I must confirm with the data owner. Do not invent values.

What to expect: A good answer lists concrete issues from your sample (spaces, formats, duplicates) and flags assumptions such as how to treat blanks. Do not paste real customer names, phone numbers or other personal data into AI tools; use fake or anonymised rows.