Data Cleaning in Pandas: A Step-by-Step Tutorial With Code and Examples

What is data cleaning, and why does the order matter?

Data cleaning is fixing the errors in raw data before you analyse it, and the order matters because each fix makes the next problem easier to see. For example, “Priya Sharma” and “ priya sharma” look like two different people until you fix spaces and capital letters. Only then can pandas see that two orders are the same order. So duplicates come after text cleaning, and dates must be converted before you can check whether they are in range.

In this tutorial you will clean one file from start to finish. The file is messy_orders.csv: 23 rows and 13 columns of made-up orders from a small online shop. It is small on purpose, so you can see every row on screen. It contains the problems you will meet in real files. Already have several files? First merge multiple CSV files into one DataFrame, then clean the result here.

Before you start. You need Python, pandas and Jupyter Notebook (or any editor that runs Python). Install pandas with the command below.

pip install pandas notebook

 Order ID,Customer Name ,Email,Phone,City,Order Date,Product,Category,Quantity,Unit Price (INR),Discount,Payment Status,Rating
1001,Rohit Das,[email protected],90000 00001,Kolkata,2026-01-05,Wireless Mouse,Accessories,2,"₹1,200",10%,Paid,5
1002, priya sharma,[email protected],9000000002,kolkata,05/01/2026,USB Keyboard,accessories,1,Rs. 850,0%,paid,4
1003,ANKIT MEHTA,ankit.mehta@example,+91 90000 00003,Bangalore,"Jan 7, 2026",Laptop Stand,Accessories,3,"2,499.00",5%,PAID ,3
1004,Sneha Roy,[email protected],090000-00004,Bengaluru,2026/01/09,Monitor 24 inch,Electronics,1,"₹12,500",10%,Pending,N/A
1005,Rahul Verma,[email protected],90000 00005,Delhi,13-01-2026,Webcam,Electronics,2 pcs,3200,-,Paid,4
1006,Meera Iyer,meera.iyer@@example.com,90000 00006,Chennai,15/01/2026,Headphones,Electronics,-1,"₹2,800",15%,Failed,2
1007,Arjun Nair,[email protected],12345,Kochi,31/02/2026,Phone Cover,Accessories,4,299,0%,Paid,5
1008,Kavya Reddy,[email protected],90000 00008,Hyderabad,2026-01-20,Laptop,Electronics,500,58000,10%,Paid,11
1009,Vikram Singh,unknown,90000 00009,Calcutta,22/01/2026,Wireless Mouse,Accessories,,"₹1,200",5%,Pending,4
1010,Neha Gupta,[email protected],90000 00010,Mumbai,2026-01-24,USB Keyboard,Accessories,2,N/A,0%,paid,3
1011,Sourav Ghosh,[email protected],90000 00011,KOLKATA,26/01/2026,Monitor 24 inch,Electronics,1,12500,10%,Paid,5
1012,Isha Kapoor,[email protected],90000 00012,bangalore,28-01-2026,Webcam,,1,"₹3,200",5%,Pending,4
1013,Tanmay Joshi,[email protected],90000 00013,Pune,2026-02-01,Headphones,Electronics,2,250000,0%,Paid,4
1014,Divya Menon,[email protected],90000 00014,Chennai ,03/02/2026,Phone Cover,Accessories,3,299,10%,PAID,0
1015,Aman Khan,[email protected],90000 00015,Delhi,05/02/2026,Laptop Stand,accessories,1,"2,499",5%,Paid,4
1001,Rohit Das,[email protected],90000 00001,Kolkata,2026-01-05,Wireless Mouse,Accessories,2,"₹1,200",10%,Paid,5
1002,Priya Sharma,[email protected],9000000002,Kolkata,05/01/2026,USB Keyboard,Accessories,1,"₹850",0%,Paid,4
1016,Rohan Bose,[email protected],90000 00016,Kolkata,10/02/2026,Laptop,Electronics,1,"₹58,000",10%,Pending,5
1017,Pooja Patil,[email protected],90000 00017,Pune,"Feb 12, 2026",Wireless Mouse,Accessories,2,1200,0%,Paid,3
1018,Siddharth Rao,,90000 00018,Hyderabad,14/02/2026,Webcam,Electronics,1,3200,5%,Failed,
1019,Anita Desai,[email protected],90000 00019,Mumbai,2026-02-16,Headphones,Electronics,1,2800,15%,Paid,4
1020,Karan Malhotra,[email protected],90000 00020,Delhi,,Monitor 24 inch,Electronics,2,12500,10%,pending,5
1021,,[email protected],90000 00021,,2026-02-18,Phone Cover,Accessories,2,299,0%,Paid,4

Step 1: How do you load a messy CSV file safely?

Load the file with pd.read_csv, tell pandas which extra words mean “missing”, and work on a copy.

import numpy as np
import pandas as pd

pd.set_option("display.width", 250)
pd.set_option("display.max_columns", 20)
print("pandas version:", pd.__version__)

pandas version: 3.0.2

na_tokens = ["-", "--", "unknown", "Unknown"]      # extra placeholders that really mean "missing"
df_raw = pd.read_csv("messy_orders.csv", na_values=na_tokens)
df = df_raw.copy()                                  # clean a copy, keep the raw data untouched
df.head()

    Order ID Customer Name                      Email            Phone       City   Order Date          Product     Category Quantity Unit Price (INR) Discount Payment Status  Rating
0       1001      Rohit Das     [email protected]      90000 00001    Kolkata   2026-01-05   Wireless Mouse  Accessories        2           ₹1,200      10%           Paid     5.0
1       1002   priya sharma  [email protected]       9000000002    kolkata   05/01/2026     USB Keyboard  accessories        1          Rs. 850       0%           paid     4.0
2       1003    ANKIT MEHTA       ankit.mehta@example  +91 90000 00003  Bangalore  Jan 7, 2026     Laptop Stand  Accessories        3         2,499.00       5%          PAID      3.0
3       1004      Sneha Roy     [email protected]     090000-00004  Bengaluru   2026/01/09  Monitor 24 inch  Electronics        1          ₹12,500      10%        Pending     NaN
4       1005    Rahul Verma   [email protected]      90000 00005      Delhi   13-01-2026           Webcam  Electronics    2 pcs             3200      NaN           Paid     4.0

• na_values lists extra words that should become missing values. pandas already treats blanks, NA, N/A, n/a and null as missing. Our file also uses -, -- and unknown, so we add them.
• df_raw is the untouched original. df = df_raw.copy() is the DataFrame we will change.
• df.head() shows the first five rows. Always look at real rows before you write any cleaning code.

If you see strange characters instead of the ₹ symbol, add encoding="utf-8" to read_csv.

Step 2: How do you inspect a DataFrame before cleaning it?

Run a few quick commands (shape, info, describe, isna and value_counts) and write down every problem you see. Inspecting first saves time, because you clean what is actually wrong instead of what you assume is wrong.

df.shape and df.info(): how big is it and what types does it have?

df.shape

(23, 13)

df.info()

<class 'pandas.DataFrame'>
RangeIndex: 23 entries, 0 to 22
Data columns (total 13 columns):
 #   Column            Non-Null Count  Dtype  
---  ------            --------------  -----  
 0    Order ID         23 non-null     int64  
 1   Customer Name     22 non-null     str    
 2   Email             21 non-null     str    
 3   Phone             23 non-null     str    
 4   City              22 non-null     str    
 5   Order Date        22 non-null     str    
 6   Product           23 non-null     str    
 7   Category          22 non-null     str    
 8   Quantity          22 non-null     str    
 9   Unit Price (INR)  22 non-null     str    
 10  Discount          22 non-null     str    
 11  Payment Status    23 non-null     str    
 12  Rating            21 non-null     float64
dtypes: float64(1), int64(1), str(11)
memory usage: 2.5 KB

There are 23 rows and 13 columns. Look at the column names: Order ID starts with a space, and names have capital letters and brackets. Look at the types: Quantity, Unit Price (INR) and Order Date are text (str), but they should be a number, a number and a date.

df.describe(): do the numbers look possible?

df.describe()

          Order ID     Rating
count    23.000000  21.000000
mean   1010.173913   4.190476
std       6.520263   1.965173
min    1001.000000   0.000000
25%    1004.500000   4.000000
50%    1010.000000   4.000000
75%    1015.500000   5.000000
max    1021.000000  11.000000

Ratings should run from 1 to 5, but the minimum is 0 and the maximum is 11. Those are errors. describe() only shows number columns, which is why it is a good first test.

df.isna().sum(): what is missing?

df.isna().sum()

 Order ID           0
Customer Name       1
Email               2
Phone               0
City                1
Order Date          1
Product             0
Category            1
Quantity            1
Unit Price (INR)    1
Discount            1
Payment Status      0
Rating              2
dtype: int64

value_counts(): how many different spellings are there?

df["City"].value_counts(dropna=False)

City
Kolkata      4
Delhi        3
Hyderabad    2
Mumbai       2
Pune         2
kolkata      1
Bangalore    1
Bengaluru    1
Chennai      1
Kochi        1
Calcutta     1
KOLKATA      1
bangalore    1
Chennai      1
NaN          1
Name: count, dtype: int64

df["Payment Status"].value_counts(dropna=False)

Payment Status
Paid       12
Pending     4
paid        2
Failed      2
PAID        1
PAID        1
pending     1
Name: count, dtype: int64

Kolkata appears as Kolkata, kolkata, KOLKATA and Calcutta. Chennai appears twice because one copy has a trailing space. Payment status has Paid, paid and PAID. A computer treats each spelling as a different value.

Now you can write the problem list. This is your plan for the rest of the tutorial.

Step 3: How do you clean column names?

Strip the spaces, make everything lowercase, and replace spaces and symbols with underscores, so every column name is easy to type. Names like df["Unit Price (INR)"] are hard to type and easy to get wrong. A name like df["unit_price"] is not.

df.columns = (
    df.columns.str.strip()                          # " Order ID"  -> "Order ID"
    .str.lower()                                    # "Order ID"   -> "order id"
    .str.replace(r"\(inr\)", "", regex=True)      # drop the unit in brackets
    .str.strip()
    .str.replace(r"[^a-z0-9]+", "_", regex=True)    # spaces and symbols -> underscore
    .str.strip("_")
)
print(list(df.columns))

['order_id', 'customer_name', 'email', 'phone', 'city', 'order_date', 'product', 'category', 'quantity', 'unit_price', 'discount', 'payment_status', 'rating']

Read the chain from top to bottom. Each line takes the result of the line before it.

• str.strip() removes spaces at the start and end, so Order ID becomes Order ID.
• str.lower() makes all letters lowercase.
• str.replace(r"\(inr\)", "", regex=True) removes the unit in brackets. The \( is needed because a bracket has a special meaning in a regex (a pattern for matching text).
• str.replace(r"[^a-z0-9]+", "_", regex=True) turns every run of characters that is not a letter or digit into one underscore.
• The last str.strip("_") removes any underscore left at the start or end.

To rename just one or two columns by hand, use df.rename(columns={"old_name": "new_name"}) instead.

Step 4: How do you clean text columns in pandas?

Remove extra spaces with str.strip, fix capital letters with str.lower or str.title, and map different spellings to one name with replace.

text_cols = ["customer_name", "email", "city", "product", "category", "payment_status"]
for col in text_cols:
    df[col] = df[col].str.strip()                   # remove spaces at the start and end

df["customer_name"] = df["customer_name"].str.title()
df["email"] = df["email"].str.lower()
df["category"] = df["category"].str.title()
df["payment_status"] = df["payment_status"].str.title()
df["city"] = df["city"].str.title().replace({"Calcutta": "Kolkata", "Bangalore": "Bengaluru"})

df["city"].value_counts(dropna=False)

city
Kolkata      7
Bengaluru    3
Delhi        3
Chennai      2
Hyderabad    2
Mumbai       2
Pune         2
Kochi        1
NaN          1
Name: count, dtype: int64

• The for loop strips spaces in all six text columns, so you do not repeat the same line six times.
• str.title() turns “ANKIT MEHTA” and “priya sharma” into “Ankit Mehta” and “Priya Sharma”. It is not perfect for names like “D’Souza” or “McDonald”, so check the result.
• str.lower() is right for emails, because email addresses are not case sensitive in practice.
• replace({"Calcutta": "Kolkata", "Bangalore": "Bengaluru"}) maps old spellings to one standard name. The dictionary is your own rulebook. Add a line to it whenever you find a new spelling.

Check the result with value_counts() again. Seven city spellings have become one clean list, and payment status has three values instead of seven.

df["payment_status"].value_counts(dropna=False)

payment_status
Paid       16
Pending     5
Failed      2
Name: count, dtype: int64

Step 5: How do you fix wrong data types?

Convert text to numbers with pd.to_numeric and text to dates with pd.to_datetime, then check how many values failed to convert. A column of text cannot be added, averaged or sorted by date.

Quantity: pull the number out of “2 pcs”

# "2 pcs" -> 2, "-1" -> -1 ; Int64 keeps whole numbers AND allows missing values
df["quantity"] = pd.to_numeric(df["quantity"].str.extract(r"(-?\d+)")[0]).astype("Int64")
df["quantity"].tolist()

[2, 1, 3, 1, 2, -1, 4, 500, <NA>, 2, 1, 1, 2, 3, 1, 2, 1, 1, 2, 1, 1, 2, 2]

str.extract(r"(-?\d+)") finds the first whole number in each text value, including a minus sign. pd.to_numeric turns it into a number. astype("Int64") (with a capital I) keeps whole numbers and also allows missing values. A normal int column cannot hold a missing value.

Unit price: remove ₹, “Rs.” and commas

Beginners often write one regex that keeps only digits and dots. Test it first on a few values.

test = pd.Series(["₹1,200", "Rs. 850", "2,499.00"])
test.str.replace(r"[^\d.]", "", regex=True).tolist()     # naive: keep only digits and dots

['1200', '.850', '2499.00']

Look at the second value. “Rs. 850” became .850, because the dot after “Rs” was kept. That would later turn into 0.85 rupees, with no error message. So remove the currency words first, then the commas.

price_text = df["unit_price"].str.replace(r"(?i)rs\.?|inr|₹|,", "", regex=True).str.strip()
df["unit_price"] = pd.to_numeric(price_text, errors="coerce")
print(df["unit_price"].tolist())

[1200.0, 850.0, 2499.0, 12500.0, 3200.0, 2800.0, 299.0, 58000.0, 1200.0, nan, 12500.0, 3200.0, 250000.0, 299.0, 2499.0, 1200.0, 850.0, 58000.0, 1200.0, 3200.0, 2800.0, 12500.0, 299.0]

errors="coerce" tells pandas to put NaN (missing) where a value cannot be converted, instead of stopping with an error. Use it carefully: always count missing values before and after, so that bad data does not disappear without you noticing.

Discount: remove the % sign

df["discount_pct"] = pd.to_numeric(df["discount"].str.rstrip("%"), errors="coerce")
df = df.drop(columns="discount")
print(df["discount_pct"].tolist())

[10.0, 0.0, 5.0, 10.0, nan, 15.0, 0.0, 10.0, 5.0, 0.0, 10.0, 5.0, 0.0, 10.0, 5.0, 10.0, 0.0, 10.0, 0.0, 5.0, 15.0, 10.0, 0.0]

Order date: convert five formats into one

The dates use five formats: 2026-01-05, 05/01/2026, 13-01-2026, Jan 7, 2026 and 2026/01/09. In India, 05/01/2026 means 5 January. We parse in two passes.

raw_dates = df["order_date"]

# Pass 1: year-first dates (2026-01-05, 2026/01/09) are unambiguous
iso = pd.to_datetime(raw_dates, format="ISO8601", errors="coerce")

# Pass 2: everything else is day-first in India (05/01/2026, 13-01-2026, Jan 7, 2026)
rest = pd.to_datetime(raw_dates.where(iso.isna()), format="mixed", dayfirst=True, errors="coerce")

df["order_date"] = iso.fillna(rest)
pd.DataFrame({"raw": raw_dates, "parsed": df["order_date"]})

             raw     parsed
0     2026-01-05 2026-01-05
1     05/01/2026 2026-01-05
2    Jan 7, 2026 2026-01-07
3     2026/01/09 2026-01-09
4     13-01-2026 2026-01-13
5     15/01/2026 2026-01-15
6     31/02/2026        NaT
7     2026-01-20 2026-01-20
8     22/01/2026 2026-01-22
9     2026-01-24 2026-01-24
10    26/01/2026 2026-01-26
11    28-01-2026 2026-01-28
12    2026-02-01 2026-02-01
13    03/02/2026 2026-02-03
14    05/02/2026 2026-02-05
15    2026-01-05 2026-01-05
16    05/01/2026 2026-01-05
17    10/02/2026 2026-02-10
18  Feb 12, 2026 2026-02-12
19    14/02/2026 2026-02-14
20    2026-02-16 2026-02-16
21           NaN        NaT
22    2026-02-18 2026-02-18

• Pass 1 uses format="ISO8601", which reads year-first dates such as 2026-01-05 and 2026/01/09. Everything else becomes NaT (not a time, the date version of NaN).
• Pass 2 reads only the dates that failed in pass 1, using format="mixed" and dayfirst=True so that 05/01/2026 means 5 January.
• iso.fillna(rest) combines the two results. The impossible date 31/02/2026 and the blank date correctly become NaT.

one_call = pd.to_datetime(raw_dates, format="mixed", dayfirst=True, errors="coerce")
pd.DataFrame({"raw": raw_dates, "one_call": one_call, "two_pass": df["order_date"]}).head(5)

           raw   one_call   two_pass
0   2026-01-05 2026-05-01 2026-01-05
1   05/01/2026 2026-01-05 2026-01-05
2  Jan 7, 2026 2026-01-07 2026-01-07
3   2026/01/09 2026-09-01 2026-01-09
4   13-01-2026 2026-01-13 2026-01-13

Step 6: How do you handle missing values in pandas?

First count what is missing, then decide for each column whether to drop the rows, fill the gaps, or leave them missing. There is no single right answer. The right choice depends on what the column means.

Count the missing values

missing = df.isna().sum().to_frame("missing")
missing["percent"] = (df.isna().mean() * 100).round(1)
missing[missing["missing"] > 0]

               missing  percent
customer_name        1      4.3
email                2      8.7
city                 1      4.3
order_date           2      8.7
category             1      4.3
quantity             1      4.3
unit_price           1      4.3
rating               2      8.7
discount_pct         1      4.3

What does dropna do?

print("rows now:                       ", len(df))
print("dropna() on all columns:        ", len(df.dropna()))
print("dropna(subset=['order_date']):  ", len(df.dropna(subset=["order_date"])))
print("dropna(thresh=12):              ", len(df.dropna(thresh=12)))

rows now:                        23
dropna() on all columns:         14
dropna(subset=['order_date']):   21
dropna(thresh=12):               20

Dropping every row that has any missing value would remove 9 of our 23 rows, even though most of those rows are mostly fine. subset limits the check to the columns you name. thresh=12 keeps a row if it has at least 12 values that are not missing. Dropping is safe only when very few rows are affected, or when a row without that value is useless to you.

How do you fill missing values?

# 1) Text: use a clear label
df["customer_name"] = df["customer_name"].fillna("Not provided")
df["city"] = df["city"].fillna("Unknown")

# 2) Category: copy it from other rows that have the same product
df["category"] = df["category"].fillna(df.groupby("product")["category"].transform("first"))

# 3) Price: use the typical (median) price of the same product
df["unit_price"] = df["unit_price"].fillna(df.groupby("product")["unit_price"].transform("median"))

# 4) Discount: blank almost always means "no discount" (confirm with the data owner)
df["discount_pct"] = df["discount_pct"].fillna(0)

# Email, rating, quantity and date: leave missing for now
df.isna().sum()

order_id          0
customer_name     0
email             2
phone             0
city              0
order_date        2
product           0
category          0
quantity          1
unit_price        0
payment_status    0
rating            2
discount_pct      0
dtype: int64

• Text columns: fill with a clear label such as “Not provided”, so you can still count them.
• Category: groupby("product")["category"].transform("first") gives each row the first known category of its product. A webcam with a blank category gets “Electronics” from the other webcam rows.
• Price: the median price of the same product is a fair guess. The median is better than the mean, because one extreme value does not pull it.
• Discount: a blank almost always means no discount, but confirm this with the person who owns the data.
• Email, rating, quantity, date: we leave these missing. Inventing an email is wrong, and filling ratings with an average would create opinions nobody gave.

Forward fill, back fill and interpolate

For ordered data such as temperature readings or daily stock levels, you can fill a gap from its neighbours. This does not suit our orders, so here is a small separate example.

s = pd.Series([10, np.nan, np.nan, 16, np.nan, 20], name="temperature")
pd.DataFrame({"original": s, "ffill": s.ffill(), "bfill": s.bfill(), "interpolate": s.interpolate()})

   original  ffill  bfill  interpolate
0      10.0   10.0   10.0         10.0
1       NaN   10.0   16.0         12.0
2       NaN   10.0   16.0         14.0
3      16.0   16.0   16.0         16.0
4       NaN   16.0   20.0         18.0
5      20.0   20.0   20.0         20.0

ffill copies the last known value forward. bfill copies the next known value backward. interpolate draws a straight line between the known values.

Step 7: How do you find and remove duplicate rows?

Use df.duplicated() to count repeated rows and df.drop_duplicates() to remove them, after cleaning the text so that near-duplicates look identical.

print("duplicates in the raw file:  ", df_raw.duplicated().sum())
print("duplicates after cleaning:   ", df.duplicated().sum())

repeated = df[df.duplicated(subset="order_id", keep=False)].sort_values("order_id")
repeated[["order_id", "customer_name", "email", "unit_price", "payment_status"]]

duplicates in the raw file:   1
duplicates after cleaning:    2
    order_id customer_name                     email  unit_price payment_status
0       1001     Rohit Das     [email protected]      1200.0           Paid
15      1001     Rohit Das     [email protected]      1200.0           Paid
1       1002  Priya Sharma  [email protected]       850.0           Paid
16      1002  Priya Sharma  [email protected]       850.0           Paid

The raw file has 1 exact duplicate. After cleaning, there are 2, because order 1002 was typed once as “ priya sharma” in capitals and once as “Priya Sharma”. This is why we cleaned text first. The table shows both pairs so you can check them with your own eyes before deleting anything.

df = df.drop_duplicates().reset_index(drop=True)
print("rows now:", len(df))

rows now: 21

• drop_duplicates() with no arguments compares all columns and keeps the first copy.
• drop_duplicates(subset="order_id") compares only the columns you name. Use it when one ID should appear once, but look at the repeated rows first.
• keep="last" keeps the last copy instead of the first. keep=False removes every copy.
• reset_index(drop=True) renumbers the rows 0, 1, 2 and so on, so there are no gaps in the row numbers.

Step 8: How do you check that values make sense?

Write one rule for each column, find the rows that break it, and set those values to missing instead of guessing the correct value. A value can have the right type and still be wrong. A rating of 11 is a number, but it is not a valid rating.

Emails: a pattern check

EMAIL_PATTERN = r"[\w.+-]+@[\w-]+(\.[\w-]+)+"
valid_email = df["email"].str.fullmatch(EMAIL_PATTERN, na=False)
bad_email = df["email"].notna() & ~valid_email

print(df.loc[bad_email, ["order_id", "email"]])
df.loc[bad_email, "email"] = np.nan

   order_id                    email
2      1003      ankit.mehta@example
5      1006  meera.iyer@@example.com

The pattern [\w.+-]+@[\w-]+(\.[\w-]+)+ means: some letters, digits or symbols, then one @, then a domain with at least one dot. str.fullmatch checks that the whole text fits the pattern. na=False treats missing emails as “not checked”. This catches obvious mistakes such as @@ and a missing .com. It cannot tell you whether the mailbox exists. Only sending a message can.

Phone numbers: keep only the 10 digits

digits = df["phone"].str.replace(r"\D", "", regex=True)     # keep digits only
last10 = digits.str[-10:]                                   # drop +91 and the leading 0
valid_phone = last10.str.fullmatch(r"[6-9]\d{9}", na=False) # Indian mobile: 10 digits, starts with 6-9
bad_phone = df["phone"].notna() & ~valid_phone

print(df.loc[bad_phone, ["order_id", "phone"]])
df["phone"] = last10.where(valid_phone)
df[["order_id", "phone"]].head(6)

   order_id  phone
6      1007  12345
   order_id       phone
0      1001  9000000001
1      1002  9000000002
2      1003  9000000003
3      1004  9000000004
4      1005  9000000005
5      1006  9000000006

We remove everything that is not a digit, keep the last 10 digits (this drops +91 and a leading 0), and accept the result only if it looks like an Indian mobile number: 10 digits starting with 6, 7, 8 or 9. The value “12345” fails and becomes missing.

Ratings and quantities: range rules

bad_rating = df["rating"].notna() & ~df["rating"].between(1, 5)
bad_qty = df["quantity"].notna() & (df["quantity"] <= 0)

print(df.loc[bad_rating | bad_qty, ["order_id", "quantity", "rating"]])
df.loc[bad_rating, "rating"] = np.nan
df.loc[bad_qty, "quantity"] = pd.NA

    order_id  quantity  rating
5       1006        -1     2.0
7       1008       500    11.0
13      1014         3     0.0

between(1, 5) checks a range. A rating of 11 and a rating of 0 are both outside it. A quantity of -1 is also impossible. We keep each bad mask (bad_email, bad_phone, bad_rating, bad_qty), because we will use them in Step 10 to flag these rows for review.

Step 9: How do you find and handle outliers in pandas?

Screen for outliers with the IQR rule, check each candidate against a business rule, then decide whether to remove, cap, replace or flag it. An outlier is a value far away from the others. It may be an error, or it may be a real and important order.

The IQR rule

q1, q3 = df["quantity"].quantile([0.25, 0.75])
upper = q3 + 1.5 * (q3 - q1)
print("Q1 =", q1, "| Q3 =", q3, "| upper fence =", upper)

df.loc[df["quantity"] > upper, ["order_id", "product", "quantity"]]

Q1 = 1 | Q3 = 2 | upper fence = 3.5
   order_id      product  quantity
6      1007  Phone Cover         4
7      1008       Laptop       500

Q1 and Q3 are the values below which 25% and 75% of the data fall. The IQR is Q3 minus Q1. Anything above Q3 + 1.5 × IQR (or below Q1 − 1.5 × IQR) is a candidate. Here the fence is 3.5, and two rows cross it: a quantity of 500 and a quantity of 4.

The 500 is clearly wrong. But four phone covers is a normal order. So the IQR rule gives you candidates to review, not rows to delete. On a small file, it is very sensitive. You can also use a z-score (how many standard deviations a value is from the mean), but it works badly on small or very skewed data.

Compare each price with the same product

A single price fence would flag every laptop, because laptops cost more than mouse pads. So compare each price with the typical price of the same product.

median_price = df.groupby("product")["unit_price"].transform("median")
price_ratio = df["unit_price"] / median_price

df.loc[price_ratio > 5, ["order_id", "product", "unit_price"]].assign(typical_price=median_price)

    order_id     product  unit_price  typical_price
12      1013  Headphones    250000.0         2800.0

A pair of headphones at ₹2,50,000 is about 89 times the usual ₹2,800. It is probably a typing error (an extra zero or two).

Remove, cap, replace or flag?

df["quantity"].clip(upper=int(upper)).tolist()     # capping (winsorising): shown only, not applied

[2, 1, 3, 1, 2, <NA>, 3, 3, <NA>, 2, 1, 1, 2, 3, 1, 1, 2, 1, 1, 2, 2]

We use a business rule: no order in this shop has more than 20 units. Quantity 500 becomes missing. The extreme price is replaced with the product’s median price. Both rows will be flagged in the next step.

big_qty = df["quantity"].notna() & (df["quantity"] > 20)       # business rule: no order has more than 20 units
bad_price = price_ratio > 5

df.loc[big_qty, "quantity"] = pd.NA
df.loc[bad_price, "unit_price"] = np.nan
df["unit_price"] = df["unit_price"].fillna(median_price)       # refill with the product's typical price
print("quantity rows set to missing:", int(big_qty.sum()), "| prices replaced:", int(bad_price.sum()))

quantity rows set to missing: 1 | prices replaced: 1

Step 10: How do you run final checks and save the clean file?

Add a needs_review flag, set the final data types, test your assumptions with assert, then save with to_csv(index=False).

df["needs_review"] = bad_email | bad_phone | bad_rating | bad_qty | big_qty | bad_price | df["order_date"].isna()

df["rating"] = df["rating"].astype("Int64")
for col in ["city", "category", "payment_status"]:
    df[col] = df[col].astype("category")

print("rows before:", len(df_raw), "| rows after:", len(df), "| rows to review:", int(df["needs_review"].sum()))
df[df["needs_review"]][["order_id", "quantity", "unit_price", "rating", "order_date"]]

rows before: 23 | rows after: 21 | rows to review: 7
    order_id  quantity  unit_price  rating order_date
2       1003         3      2499.0       3 2026-01-07
5       1006      <NA>      2800.0       2 2026-01-15
6       1007         4       299.0       5        NaT
7       1008      <NA>     58000.0    <NA> 2026-01-20
12      1013         2      2800.0       4 2026-02-01
13      1014         3       299.0    <NA> 2026-02-03
19      1020         2     12500.0       5        NaT

We flag seven rows instead of deleting them, so a person can look at each one. We also convert city, category and payment_status to the category type, which stores repeated labels efficiently, and rating to Int64.

assert df["order_id"].is_unique
assert df["quantity"].dropna().between(1, 20).all()
assert df["rating"].dropna().between(1, 5).all()
assert df["order_date"].dropna().between("2026-01-01", "2026-02-28").all()
print("All checks passed")
df.info()

All checks passed
<class 'pandas.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 14 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   order_id        21 non-null     int64         
 1   customer_name   21 non-null     str           
 2   email           17 non-null     str           
 3   phone           20 non-null     str           
 4   city            21 non-null     category      
 5   order_date      19 non-null     datetime64[us]
 6   product         21 non-null     str           
 7   category        21 non-null     category      
 8   quantity        18 non-null     Int64         
 9   unit_price      21 non-null     float64       
 10  payment_status  21 non-null     category      
 11  rating          17 non-null     Int64         
 12  discount_pct    21 non-null     float64       
 13  needs_review    21 non-null     boolean       
dtypes: Int64(2), boolean(1), category(3), datetime64[us](1), float64(2), int64(1), str(4)
memory usage: 2.0 KB

assert stops the program with an error if a statement is false. If someone changes the data next month and a rating of 9 appears, the check fails loudly instead of letting bad data through. The date check also confirms that no date falls outside January and February 2026.

df.to_csv("clean_orders.csv", index=False, encoding="utf-8-sig")
# A CSV does not remember types, so tell pandas again when you read it back
pd.read_csv("clean_orders.csv", dtype={"phone": "str"}, parse_dates=["order_date"]).head()

   order_id customer_name                     email       phone       city order_date          product     category  quantity  unit_price payment_status  rating  discount_pct  needs_review
0      1001     Rohit Das     [email protected]  9000000001    Kolkata 2026-01-05   Wireless Mouse  Accessories       2.0      1200.0           Paid     5.0          10.0         False
1      1002  Priya Sharma  [email protected]  9000000002    Kolkata 2026-01-05     USB Keyboard  Accessories       1.0       850.0           Paid     4.0           0.0         False
2      1003   Ankit Mehta                       NaN  9000000003  Bengaluru 2026-01-07     Laptop Stand  Accessories       3.0      2499.0           Paid     3.0           5.0          True
3      1004     Sneha Roy     [email protected]  9000000004  Bengaluru 2026-01-09  Monitor 24 inch  Electronics       1.0     12500.0        Pending     NaN          10.0         False
4      1005   Rahul Verma   [email protected]  9000000005      Delhi 2026-01-13           Webcam  Electronics       2.0      3200.0           Paid     4.0           0.0         False

• index=False stops pandas from writing the row numbers as an extra column.
• encoding="utf-8-sig" makes Excel open the file with the correct characters.
• A CSV file does not remember data types. When you read it back, tell pandas what you know: dtype={"phone": "str"} and parse_dates=["order_date"].

Step 11: How do you put all the steps into one reusable function?

Turn each step into a small function and chain them with .pipe, so you can clean next month’s file with one line.

NA_TOKENS = ["-", "--", "unknown", "Unknown"]
EMAIL_PATTERN = r"[\w.+-]+@[\w-]+(\.[\w-]+)+"


def standardise_columns(d):
    d = d.copy()
    d.columns = (d.columns.str.strip().str.lower()
                 .str.replace(r"\(inr\)", "", regex=True).str.strip()
                 .str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_"))
    return d


def clean_text(d):
    d = d.copy()
    for col in ["customer_name", "email", "city", "product", "category", "payment_status"]:
        d[col] = d[col].str.strip()
    d["customer_name"] = d["customer_name"].str.title()
    d["email"] = d["email"].str.lower()
    d["category"] = d["category"].str.title()
    d["payment_status"] = d["payment_status"].str.title()
    d["city"] = d["city"].str.title().replace({"Calcutta": "Kolkata", "Bangalore": "Bengaluru"})
    return d


def fix_types(d):
    d = d.copy()
    d["quantity"] = pd.to_numeric(d["quantity"].str.extract(r"(-?\d+)")[0]).astype("Int64")
    price_text = d["unit_price"].str.replace(r"(?i)rs\.?|inr|₹|,", "", regex=True).str.strip()
    d["unit_price"] = pd.to_numeric(price_text, errors="coerce")
    d["discount_pct"] = pd.to_numeric(d["discount"].str.rstrip("%"), errors="coerce")
    d = d.drop(columns="discount")
    iso = pd.to_datetime(d["order_date"], format="ISO8601", errors="coerce")
    rest = pd.to_datetime(d["order_date"].where(iso.isna()), format="mixed", dayfirst=True, errors="coerce")
    d["order_date"] = iso.fillna(rest)
    return d


def fill_missing(d):
    d = d.copy()
    d["customer_name"] = d["customer_name"].fillna("Not provided")
    d["city"] = d["city"].fillna("Unknown")
    d["category"] = d["category"].fillna(d.groupby("product")["category"].transform("first"))
    d["unit_price"] = d["unit_price"].fillna(d.groupby("product")["unit_price"].transform("median"))
    d["discount_pct"] = d["discount_pct"].fillna(0)
    return d


def validate_and_flag(d):
    d = d.drop_duplicates().reset_index(drop=True)

    bad_email = d["email"].notna() & ~d["email"].str.fullmatch(EMAIL_PATTERN, na=False)
    d.loc[bad_email, "email"] = np.nan

    last10 = d["phone"].str.replace(r"\D", "", regex=True).str[-10:]
    valid_phone = last10.str.fullmatch(r"[6-9]\d{9}", na=False)
    bad_phone = d["phone"].notna() & ~valid_phone
    d["phone"] = last10.where(valid_phone)

    bad_rating = d["rating"].notna() & ~d["rating"].between(1, 5)
    bad_qty = d["quantity"].notna() & (d["quantity"] <= 0)
    big_qty = d["quantity"].notna() & (d["quantity"] > 20)
    median_price = d.groupby("product")["unit_price"].transform("median")
    bad_price = (d["unit_price"] / median_price) > 5

    d.loc[bad_rating, "rating"] = np.nan
    d.loc[bad_qty | big_qty, "quantity"] = pd.NA
    d.loc[bad_price, "unit_price"] = np.nan
    d["unit_price"] = d["unit_price"].fillna(median_price)

    d["needs_review"] = (bad_email | bad_phone | bad_rating | bad_qty | big_qty
                         | bad_price | d["order_date"].isna())
    d["rating"] = d["rating"].astype("Int64")
    for col in ["city", "category", "payment_status"]:
        d[col] = d[col].astype("category")
    return d


def clean_orders(path):
    return (pd.read_csv(path, na_values=NA_TOKENS)
            .pipe(standardise_columns)
            .pipe(clean_text)
            .pipe(fix_types)
            .pipe(fill_missing)
            .pipe(validate_and_flag))

• Each function takes a DataFrame, works on a copy and returns a DataFrame. This keeps the steps independent and easy to test.
• .pipe(function) passes the DataFrame into the function. The chain reads in the same order as Steps 3 to 10.
• pd.testing.assert_frame_equal(result, df) proves that the function gives exactly the same answer as our step-by-step version.

result = clean_orders("messy_orders.csv")
pd.testing.assert_frame_equal(result, df)       # same answer as the step-by-step version
print("The function gives exactly the same result as Steps 1-10")
result.head()

The function gives exactly the same result as Steps 1-10
   order_id customer_name                     email       phone       city order_date          product     category  quantity  unit_price payment_status  rating  discount_pct  needs_review
0      1001     Rohit Das     [email protected]  9000000001    Kolkata 2026-01-05   Wireless Mouse  Accessories         2      1200.0           Paid       5          10.0         False
1      1002  Priya Sharma  [email protected]  9000000002    Kolkata 2026-01-05     USB Keyboard  Accessories         1       850.0           Paid       4           0.0         False
2      1003   Ankit Mehta                       NaN  9000000003  Bengaluru 2026-01-07     Laptop Stand  Accessories         3      2499.0           Paid       3           5.0          True
3      1004     Sneha Roy     [email protected]  9000000004  Bengaluru 2026-01-09  Monitor 24 inch  Electronics         1     12500.0        Pending    <NA>          10.0         False
4      1005   Rahul Verma   [email protected]  9000000005      Delhi 2026-01-13           Webcam  Electronics         2      3200.0           Paid       4           0.0         False

When a new file arrives, the whole job is clean_orders("new_file.csv"). When you find a new problem, add one line to the right function.

Which pandas method fixes which problem?

Match the problem to the method in the table below, then check the result with value_counts(), isna().sum() or describe().

What mistakes do beginners make when cleaning data in pandas?

Most cleaning mistakes are silent: the code runs without an error and the data is quietly wrong.

When is this approach not enough?

This step-by-step pandas approach works well for files that fit in memory and are cleaned by one person, and it is the wrong tool in the cases below.

Cleaning also has a limit that no code fixes: if the same mistake keeps arriving, the real fix is at the source, such as a form that validates emails when someone types them.

Frequently asked questions

How do I remove duplicate rows in pandas?

Use df.drop_duplicates() to remove rows that are identical in every column. Add subset=["order_id"] to compare only some columns, and keep="last" to keep the last copy. Clean the text first, so that “Priya” and “ priya” are treated as the same value.

How do I handle missing values in pandas?

First count them with df.isna().sum(). Then drop rows with dropna(subset=[...]), fill gaps with fillna(), or leave them missing and flag the rows. Fill with a median or a group median rather than 0, unless 0 is truly what a blank means.

How do I convert mixed date formats to datetime in pandas?

Parse the year-first dates with pd.to_datetime(s, format="ISO8601", errors="coerce"), then parse the rest with format="mixed", dayfirst=True and combine the two with fillna. Always print the raw and parsed dates side by side, because a day and month can swap without an error.

How do I remove currency symbols and commas from a column in pandas?

Use str.replace with a regex, then convert with pd.to_numeric. Remove the currency words first (for example r"(?i)rs\.?|inr|₹|,"), because a pattern that keeps only digits and dots turns “Rs. 850” into “.850”.

How do I detect and handle outliers in pandas?

Compute Q1 and Q3 with quantile, then flag values outside Q1 − 1.5 × IQR and Q3 + 1.5 × IQR. Treat the result as a list of candidates and check each one against a business rule. Then remove, cap with clip, replace or flag it.

How do I clean column names in pandas?

Chain string methods on df.columns: str.strip(), str.lower() and str.replace(r"[^a-z0-9]+", "_", regex=True). Use df.rename(columns={...}) when you want to rename only a few columns.

Sources

• pandas development team. “User Guide”: Working with missing data, Working with text data, Categorical data, Nullable integer data type, and the migration guide for the new string data type (pandas 3.0). https://pandas.pydata.org/docs/user_guide/

Related tutorials

• How to Merge Multiple CSV Files into One DataFrame in Python: A Step-by-Step Tutorial
• Polars Library
• Automating Excel Reports Using Python and OpenPyXL: From Manual Drudgery to One-Click Reports