Opening notes

Loading…

Data CleaningTopic 39 of 837 min read

End-to-End Cleaning: Messy IPL Data

Put every cleaning tool together on a messy IPL matches table: audit, fill, drop, standardise, fix types, de-duplicate and add date features.

Watch the lesson

1 video on CodeWithMunnaX

This topic follows the same journey as Munna's IPL (2008–2020) notebook and video 12, on a small, deliberately messy copy of the matches table so you can see every step's output. The real dataset had the same problems: missing cities, missing winners, an almost-empty method column, the date stored as text, "Bengaluru" vs "Bangalore", and renamed franchises.

Definition

Data cleaning is the process of detecting and correcting errors, gaps and inconsistencies in a dataset — missing values, duplicates, wrong types, inconsistent labels and impossible values — so that analysis results are accurate and reproducible.

Detecting comes first: audit before you change anything ("taste the food before adding salt").
Correcting follows rules you can explain: fill, drop, replace, convert.
Reproducible — every step is code, applied to a copy, so you can re-run it on next season's data.

The workflow

text

 1. Audit        shape, dtypes, isna().sum(), duplicated().sum(), value_counts()
 2. Missing      fill what has meaning, drop rows missing essential fields
 3. Text         strip spaces, fix case, unify spellings (replace)
 4. Types        to_datetime, to_numeric(errors="coerce"), astype
 5. Duplicates   drop_duplicates (exact + key columns)
 6. Validity     impossible values / outliers
 7. Features     year, month, season … from the clean date
 8. Finish       reset_index, final audit

Step 0 — the messy data

In a real project this comes from pd.read_csv("matches.csv"). Here we simulate the CSV text with io.StringIO so the example runs anywhere:

python

import io

csv = """id,city,date,team1,winner,result_margin,method
335982,Bangalore,2008-04-18,Royal Challengers Bangalore,Kolkata Knight Riders,140,
335983, chandigarh ,2008-04-19,Kings XI Punjab,Chennai Super Kings,33,
335984,Delhi,2008-04-19,Delhi Daredevils,Delhi Daredevils,9,
335985,Mumbai,2008-04-20,Mumbai Indians,Royal Challengers Bangalore,5,
335985,Mumbai,2008-04-20,Mumbai Indians,Royal Challengers Bangalore,5,
419108,,2010-03-13,Kings XI Punjab,Delhi Daredevils,5,
501265,Delhi,2011-05-21,Delhi Daredevils,,,
829811,MUMBAI,2015-05-16,Rajasthan Royals,Rajasthan Royals,9,D/L
1082591,Bengaluru,2017-04-14,Royal Challengers Bangalore,Mumbai Indians,4,
1216523,Abu Dhabi,2020-10-10,Kolkata Knight Riders,Kolkata Knight Riders,2,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.to_string())

text

        id          city        date                        team1                       winner  result_margin method
0   335982     Bangalore  2008-04-18  Royal Challengers Bangalore        Kolkata Knight Riders          140.0    NaN
1   335983   chandigarh   2008-04-19              Kings XI Punjab          Chennai Super Kings           33.0    NaN
2   335984         Delhi  2008-04-19             Delhi Daredevils             Delhi Daredevils            9.0    NaN
3   335985        Mumbai  2008-04-20               Mumbai Indians  Royal Challengers Bangalore            5.0    NaN
4   335985        Mumbai  2008-04-20               Mumbai Indians  Royal Challengers Bangalore            5.0    NaN
5   419108           NaN  2010-03-13              Kings XI Punjab             Delhi Daredevils            5.0    NaN
6   501265         Delhi  2011-05-21             Delhi Daredevils                          NaN            NaN    NaN
7   829811        MUMBAI  2015-05-16             Rajasthan Royals             Rajasthan Royals            9.0    D/L
8  1082591     Bengaluru  2017-04-14  Royal Challengers Bangalore               Mumbai Indians            4.0    NaN
9  1216523     Abu Dhabi  2020-10-10        Kolkata Knight Riders        Kolkata Knight Riders            2.0    NaN

Step 1 — audit (look, don't touch)

python

print(raw.shape)
print(raw.dtypes)
print(raw.isna().sum())
print("exact duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

text

(10, 7)
id                 int64
city                 str
date                 str
team1                str
winner               str
result_margin    float64
method               str
dtype: object
id               0
city             1
date             0
team1            0
winner           1
result_margin    1
method           9
dtype: int64
exact duplicates: 1
city
Delhi           2
Mumbai          2
Bangalore       1
 chandigarh     1
NaN             1
MUMBAI          1
Bengaluru       1
Abu Dhabi       1
Name: count, dtype: int64

What the audit tells us:

Problem	Evidence
`date` is text, not a date	dtype `str`
one city missing, cities in mixed case / spaces, `Bengaluru` vs `Bangalore`	`value_counts`
one match with no `winner` / `result_margin` (abandoned)	`isna().sum()`
`method` is 90 % empty — it is only filled for D/L games	`isna().sum()`
match 335985 appears twice	`duplicated()`
old team names (`Delhi Daredevils`)	domain knowledge

Step 2 — handle missing values

python

ipl = raw.copy()                                   # never edit the raw data
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"})
ipl = ipl.dropna(subset=["winner"])                # abandoned match: cannot be analysed
print(ipl.isna().sum().sum(), "missing values left")
print(len(raw), "->", len(ipl), "rows")

text

0 missing values left
10 -> 9 rows

Why: method is not really "missing" — empty means a normal result, so we give it a label (exactly like Munna's fillna({'method': ...})). An abandoned match has no winner, so it is dropped with subset — not with a blanket dropna() that would also have removed every row with an empty method.

Step 3 — standardise text

python

ipl["city"] = ipl["city"].str.strip().str.title()
ipl = ipl.replace({
    "Bengaluru": "Bangalore",
    "Delhi Daredevils": "Delhi Capitals",
})
print(ipl["city"].value_counts())

text

city
Mumbai        3
Bangalore     2
Chandigarh    1
Delhi         1
Unknown       1
Abu Dhabi     1
Name: count, dtype: int64

Step 4 — fix data types

python

ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
print(ipl.dtypes)

text

id                        int64
city                        str
date             datetime64[us]
team1                       str
winner                      str
result_margin             int64
method                      str
dtype: object

Note (pandas 3): in Munna's original notebook, ipl2['date'] = pd.to_datetime(...) printed a SettingWithCopyWarning, because ipl2 was created from df by dropna(). With Copy-on-Write in pandas 3 that warning no longer exists — ipl2 behaves as an independent DataFrame, and the assignment simply works.

Step 5 — remove duplicates

python

print("exact duplicates:", ipl.duplicated().sum())
print("duplicate ids   :", ipl.duplicated(subset="id").sum())
ipl = ipl.drop_duplicates(subset="id")

text

exact duplicates: 1
duplicate ids   : 1

Step 6 — validity check

python

print(ipl["result_margin"].describe())
print("impossible margins:", (ipl["result_margin"] <= 0).sum())

text

count      8.000000
mean      25.875000
std       47.154571
min        2.000000
25%        4.750000
50%        7.000000
75%       15.000000
max      140.000000
Name: result_margin, dtype: float64
impossible margins: 0

A margin of 140 runs looks extreme but is real (McCullum's 158* in the very first IPL match), so we keep it — domain knowledge beats blind outlier deletion.

Step 7 — new features, and finish

python

ipl["year"] = ipl["date"].dt.year
ipl["month"] = ipl["date"].dt.month
ipl = ipl.reset_index(drop=True)
print(ipl[["id", "city", "date", "winner", "method", "year", "month"]].to_string())
ipl.info()

text

        id        city       date                       winner  method  year  month
0   335982   Bangalore 2008-04-18        Kolkata Knight Riders  Normal  2008      4
1   335983  Chandigarh 2008-04-19          Chennai Super Kings  Normal  2008      4
2   335984       Delhi 2008-04-19               Delhi Capitals  Normal  2008      4
3   335985      Mumbai 2008-04-20  Royal Challengers Bangalore  Normal  2008      4
4   419108     Unknown 2010-03-13               Delhi Capitals  Normal  2010      3
5   829811      Mumbai 2015-05-16             Rajasthan Royals     D/L  2015      5
6  1082591   Bangalore 2017-04-14               Mumbai Indians  Normal  2017      4
7  1216523   Abu Dhabi 2020-10-10        Kolkata Knight Riders  Normal  2020     10
<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 9 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   id             8 non-null      int64
 1   city           8 non-null      str
 2   date           8 non-null      datetime64[us]
 3   team1          8 non-null      str
 4   winner         8 non-null      str
 5   result_margin  8 non-null      int64
 6   method         8 non-null      str
 7   year           8 non-null      int32
 8   month          8 non-null      int32
dtypes: datetime64[us](1), int32(2), int64(2), str(4)
memory usage: 644.0 bytes

The final info() is the proof: 8 unique matches, no missing values, a real datetime64 date, integer margins, and two new feature columns (dt.year / dt.month return compact int32 integers). Compare it with the audit in Step 1.

Wrap it in a reusable function

Real projects get new data every season. Put the steps in one function so cleaning is repeatable:

python

def clean_matches(df):
    out = df.copy()
    out = out.fillna({"method": "Normal", "city": "Unknown"})
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].str.strip().str.title()
    out = out.replace({"Bengaluru": "Bangalore", "Delhi Daredevils": "Delhi Capitals"})
    out["date"] = pd.to_datetime(out["date"])
    out["result_margin"] = out["result_margin"].astype(int)
    out = out.drop_duplicates(subset="id")
    out["year"] = out["date"].dt.year
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean.shape)
print(clean["winner"].value_counts())

text

(8, 8)
winner
Kolkata Knight Riders          2
Delhi Capitals                 2
Chennai Super Kings            1
Royal Challengers Bangalore    1
Rajasthan Royals               1
Mumbai Indians                 1
Name: count, dtype: int64

Common mistakes

❌ Cleaning raw directly. ✅ Work on raw.copy() so you can always compare and restart.
❌ A blanket dropna() first — removes every normal match because method is empty. ✅ Fill meaningful gaps first, then dropna(subset=[...]).
❌ Dropping duplicates before fixing text. ✅ Standardise text, then de-duplicate.
❌ Deleting real extreme values as "outliers". ✅ Use domain knowledge.
❌ Not re-auditing at the end. ✅ Finish with info(), isna().sum() and duplicated().sum().

Checklist

Check	Code
shape & types	`df.shape`, `df.info()`
missing	`df.isna().sum()`, `df.isna().mean()*100`
duplicates	`df.duplicated().sum()`, `duplicated(subset=key)`
categories	`df[col].value_counts()` / `.unique()`
numbers	`df.describe()`

Interview tip

Q: Describe your data cleaning process. A: Audit first (shape, dtypes, missing %, duplicates, value counts), then fix missing values by meaning (fill or drop by subset), standardise categories, convert types, remove duplicates, check impossible values/outliers with domain knowledge, and wrap it all in a reusable function working on a copy.

Key points to remember

Always audit first: shape, dtypes, isna().sum(), duplicated().sum(), value_counts().
Work on a copy (raw.copy()) so the original data is never lost.
Fill gaps that have meaning (method → 'Normal'); drop rows only when an essential field (winner) is missing, using subset.
Standardise text (strip, title case, replace old names) before counting or de-duplicating.
Convert types: pd.to_datetime for dates, to_numeric/astype for numbers.
Extreme values are not always errors — use domain knowledge before deleting outliers.
Wrap the steps in a function so the same cleaning can be re-run on new data.

Try it yourself

Audit a messy IPL table

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.dtypes)
print(raw.isna().sum())
print("duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.dtypes)
print(raw.isna().sum())
print("duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

Clean it step by step

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
ipl = pd.read_csv(io.StringIO(csv))
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"}).dropna(subset=["winner"])
ipl["city"] = ipl["city"].str.strip().str.title().replace({"Bengaluru": "Bangalore"})
ipl = ipl.replace({"DD": "DC"})
ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
ipl = ipl.drop_duplicates(subset="id").reset_index(drop=True)
ipl["year"] = ipl["date"].dt.year
print(ipl)
print(ipl.dtypes)

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
ipl = pd.read_csv(io.StringIO(csv))
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"}).dropna(subset=["winner"])
ipl["city"] = ipl["city"].str.strip().str.title().replace({"Bengaluru": "Bangalore"})
ipl = ipl.replace({"DD": "DC"})
ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
ipl = ipl.drop_duplicates(subset="id").reset_index(drop=True)
ipl["year"] = ipl["date"].dt.year
print(ipl)
print(ipl.dtypes)

Practice: build your own clean_matches()

# 🏏 Your turn: add a step that maps short codes to full team names, then Run.
import pandas as pd
import numpy as np

raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4],
    "city":   [" pune", "Delhi", "Delhi", None, "HYDERABAD"],
    "date":   ["2017-04-06", "2017-04-08", "2017-04-08", "2017-04-09", "2017-04-10"],
    "winner": ["RPS", "DD", "DD", None, "SRH"],
    "margin": ["7", "51", "51", "", "15"],
})

def clean_matches(df):
    out = df.copy()
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].fillna("Unknown").str.strip().str.title()
    out["date"] = pd.to_datetime(out["date"])
    out["margin"] = pd.to_numeric(out["margin"], errors="coerce")
    out = out.drop_duplicates(subset="id")
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean)
print(clean.dtypes)

# 🏏 Your turn: add a step that maps short codes to full team names, then Run.
import pandas as pd
import numpy as np

raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4],
    "city":   [" pune", "Delhi", "Delhi", None, "HYDERABAD"],
    "date":   ["2017-04-06", "2017-04-08", "2017-04-08", "2017-04-09", "2017-04-10"],
    "winner": ["RPS", "DD", "DD", None, "SRH"],
    "margin": ["7", "51", "51", "", "15"],
})

def clean_matches(df):
    out = df.copy()
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].fillna("Unknown").str.strip().str.title()
    out["date"] = pd.to_datetime(out["date"])
    out["margin"] = pd.to_numeric(out["margin"], errors="coerce")
    out = out.drop_duplicates(subset="id")
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean)
print(clean.dtypes)

Revision Flashcards

1 / 5

Tap a card to flip it and see the answer.

Opening notes

Notes PandasData Cleaning

Data CleaningTopic 39 of 837 min read

End-to-End Cleaning: Messy IPL Data

Put every cleaning tool together on a messy IPL matches table: audit, fill, drop, standardise, fix types, de-duplicate and add date features.

Watch the lesson

1 video on CodeWithMunnaX

Definition

Data cleaning is the process of detecting and correcting errors, gaps and inconsistencies in a dataset — missing values, duplicates, wrong types, inconsistent labels and impossible values — so that analysis results are accurate and reproducible.

Detecting comes first: audit before you change anything ("taste the food before adding salt").
Correcting follows rules you can explain: fill, drop, replace, convert.
Reproducible — every step is code, applied to a copy, so you can re-run it on next season's data.

The workflow

text

 1. Audit        shape, dtypes, isna().sum(), duplicated().sum(), value_counts()
 2. Missing      fill what has meaning, drop rows missing essential fields
 3. Text         strip spaces, fix case, unify spellings (replace)
 4. Types        to_datetime, to_numeric(errors="coerce"), astype
 5. Duplicates   drop_duplicates (exact + key columns)
 6. Validity     impossible values / outliers
 7. Features     year, month, season … from the clean date
 8. Finish       reset_index, final audit

Step 0 — the messy data

In a real project this comes from pd.read_csv("matches.csv"). Here we simulate the CSV text with io.StringIO so the example runs anywhere:

python

import io

csv = """id,city,date,team1,winner,result_margin,method
335982,Bangalore,2008-04-18,Royal Challengers Bangalore,Kolkata Knight Riders,140,
335983, chandigarh ,2008-04-19,Kings XI Punjab,Chennai Super Kings,33,
335984,Delhi,2008-04-19,Delhi Daredevils,Delhi Daredevils,9,
335985,Mumbai,2008-04-20,Mumbai Indians,Royal Challengers Bangalore,5,
335985,Mumbai,2008-04-20,Mumbai Indians,Royal Challengers Bangalore,5,
419108,,2010-03-13,Kings XI Punjab,Delhi Daredevils,5,
501265,Delhi,2011-05-21,Delhi Daredevils,,,
829811,MUMBAI,2015-05-16,Rajasthan Royals,Rajasthan Royals,9,D/L
1082591,Bengaluru,2017-04-14,Royal Challengers Bangalore,Mumbai Indians,4,
1216523,Abu Dhabi,2020-10-10,Kolkata Knight Riders,Kolkata Knight Riders,2,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.to_string())

text

        id          city        date                        team1                       winner  result_margin method
0   335982     Bangalore  2008-04-18  Royal Challengers Bangalore        Kolkata Knight Riders          140.0    NaN
1   335983   chandigarh   2008-04-19              Kings XI Punjab          Chennai Super Kings           33.0    NaN
2   335984         Delhi  2008-04-19             Delhi Daredevils             Delhi Daredevils            9.0    NaN
3   335985        Mumbai  2008-04-20               Mumbai Indians  Royal Challengers Bangalore            5.0    NaN
4   335985        Mumbai  2008-04-20               Mumbai Indians  Royal Challengers Bangalore            5.0    NaN
5   419108           NaN  2010-03-13              Kings XI Punjab             Delhi Daredevils            5.0    NaN
6   501265         Delhi  2011-05-21             Delhi Daredevils                          NaN            NaN    NaN
7   829811        MUMBAI  2015-05-16             Rajasthan Royals             Rajasthan Royals            9.0    D/L
8  1082591     Bengaluru  2017-04-14  Royal Challengers Bangalore               Mumbai Indians            4.0    NaN
9  1216523     Abu Dhabi  2020-10-10        Kolkata Knight Riders        Kolkata Knight Riders            2.0    NaN

Step 1 — audit (look, don't touch)

python

print(raw.shape)
print(raw.dtypes)
print(raw.isna().sum())
print("exact duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

text

(10, 7)
id                 int64
city                 str
date                 str
team1                str
winner               str
result_margin    float64
method               str
dtype: object
id               0
city             1
date             0
team1            0
winner           1
result_margin    1
method           9
dtype: int64
exact duplicates: 1
city
Delhi           2
Mumbai          2
Bangalore       1
 chandigarh     1
NaN             1
MUMBAI          1
Bengaluru       1
Abu Dhabi       1
Name: count, dtype: int64

What the audit tells us:

Problem	Evidence
`date` is text, not a date	dtype `str`
one city missing, cities in mixed case / spaces, `Bengaluru` vs `Bangalore`	`value_counts`
one match with no `winner` / `result_margin` (abandoned)	`isna().sum()`
`method` is 90 % empty — it is only filled for D/L games	`isna().sum()`
match 335985 appears twice	`duplicated()`
old team names (`Delhi Daredevils`)	domain knowledge

Step 2 — handle missing values

python

ipl = raw.copy()                                   # never edit the raw data
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"})
ipl = ipl.dropna(subset=["winner"])                # abandoned match: cannot be analysed
print(ipl.isna().sum().sum(), "missing values left")
print(len(raw), "->", len(ipl), "rows")

text

0 missing values left
10 -> 9 rows

Step 3 — standardise text

python

ipl["city"] = ipl["city"].str.strip().str.title()
ipl = ipl.replace({
    "Bengaluru": "Bangalore",
    "Delhi Daredevils": "Delhi Capitals",
})
print(ipl["city"].value_counts())

text

city
Mumbai        3
Bangalore     2
Chandigarh    1
Delhi         1
Unknown       1
Abu Dhabi     1
Name: count, dtype: int64

Step 4 — fix data types

python

ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
print(ipl.dtypes)

text

id                        int64
city                        str
date             datetime64[us]
team1                       str
winner                      str
result_margin             int64
method                      str
dtype: object

Note (pandas 3): in Munna's original notebook, ipl2['date'] = pd.to_datetime(...) printed a SettingWithCopyWarning, because ipl2 was created from df by dropna(). With Copy-on-Write in pandas 3 that warning no longer exists — ipl2 behaves as an independent DataFrame, and the assignment simply works.

Step 5 — remove duplicates

python

print("exact duplicates:", ipl.duplicated().sum())
print("duplicate ids   :", ipl.duplicated(subset="id").sum())
ipl = ipl.drop_duplicates(subset="id")

text

exact duplicates: 1
duplicate ids   : 1

Step 6 — validity check

python

print(ipl["result_margin"].describe())
print("impossible margins:", (ipl["result_margin"] <= 0).sum())

text

count      8.000000
mean      25.875000
std       47.154571
min        2.000000
25%        4.750000
50%        7.000000
75%       15.000000
max      140.000000
Name: result_margin, dtype: float64
impossible margins: 0

A margin of 140 runs looks extreme but is real (McCullum's 158* in the very first IPL match), so we keep it — domain knowledge beats blind outlier deletion.

Step 7 — new features, and finish

python

ipl["year"] = ipl["date"].dt.year
ipl["month"] = ipl["date"].dt.month
ipl = ipl.reset_index(drop=True)
print(ipl[["id", "city", "date", "winner", "method", "year", "month"]].to_string())
ipl.info()

text

        id        city       date                       winner  method  year  month
0   335982   Bangalore 2008-04-18        Kolkata Knight Riders  Normal  2008      4
1   335983  Chandigarh 2008-04-19          Chennai Super Kings  Normal  2008      4
2   335984       Delhi 2008-04-19               Delhi Capitals  Normal  2008      4
3   335985      Mumbai 2008-04-20  Royal Challengers Bangalore  Normal  2008      4
4   419108     Unknown 2010-03-13               Delhi Capitals  Normal  2010      3
5   829811      Mumbai 2015-05-16             Rajasthan Royals     D/L  2015      5
6  1082591   Bangalore 2017-04-14               Mumbai Indians  Normal  2017      4
7  1216523   Abu Dhabi 2020-10-10        Kolkata Knight Riders  Normal  2020     10
<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 9 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   id             8 non-null      int64
 1   city           8 non-null      str
 2   date           8 non-null      datetime64[us]
 3   team1          8 non-null      str
 4   winner         8 non-null      str
 5   result_margin  8 non-null      int64
 6   method         8 non-null      str
 7   year           8 non-null      int32
 8   month          8 non-null      int32
dtypes: datetime64[us](1), int32(2), int64(2), str(4)
memory usage: 644.0 bytes

Wrap it in a reusable function

Real projects get new data every season. Put the steps in one function so cleaning is repeatable:

python

def clean_matches(df):
    out = df.copy()
    out = out.fillna({"method": "Normal", "city": "Unknown"})
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].str.strip().str.title()
    out = out.replace({"Bengaluru": "Bangalore", "Delhi Daredevils": "Delhi Capitals"})
    out["date"] = pd.to_datetime(out["date"])
    out["result_margin"] = out["result_margin"].astype(int)
    out = out.drop_duplicates(subset="id")
    out["year"] = out["date"].dt.year
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean.shape)
print(clean["winner"].value_counts())

text

(8, 8)
winner
Kolkata Knight Riders          2
Delhi Capitals                 2
Chennai Super Kings            1
Royal Challengers Bangalore    1
Rajasthan Royals               1
Mumbai Indians                 1
Name: count, dtype: int64

Common mistakes

❌ Cleaning raw directly. ✅ Work on raw.copy() so you can always compare and restart.
❌ A blanket dropna() first — removes every normal match because method is empty. ✅ Fill meaningful gaps first, then dropna(subset=[...]).
❌ Dropping duplicates before fixing text. ✅ Standardise text, then de-duplicate.
❌ Deleting real extreme values as "outliers". ✅ Use domain knowledge.
❌ Not re-auditing at the end. ✅ Finish with info(), isna().sum() and duplicated().sum().

Checklist

Check	Code
shape & types	`df.shape`, `df.info()`
missing	`df.isna().sum()`, `df.isna().mean()*100`
duplicates	`df.duplicated().sum()`, `duplicated(subset=key)`
categories	`df[col].value_counts()` / `.unique()`
numbers	`df.describe()`

Interview tip

Key points to remember

Always audit first: shape, dtypes, isna().sum(), duplicated().sum(), value_counts().
Work on a copy (raw.copy()) so the original data is never lost.
Fill gaps that have meaning (method → 'Normal'); drop rows only when an essential field (winner) is missing, using subset.
Standardise text (strip, title case, replace old names) before counting or de-duplicating.
Convert types: pd.to_datetime for dates, to_numeric/astype for numbers.
Extreme values are not always errors — use domain knowledge before deleting outliers.
Wrap the steps in a function so the same cleaning can be re-run on new data.

Try it yourself

Audit a messy IPL table

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.dtypes)
print(raw.isna().sum())
print("duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
raw = pd.read_csv(io.StringIO(csv))
print(raw.dtypes)
print(raw.isna().sum())
print("duplicates:", raw.duplicated().sum())
print(raw["city"].value_counts(dropna=False))

Clean it step by step

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
ipl = pd.read_csv(io.StringIO(csv))
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"}).dropna(subset=["winner"])
ipl["city"] = ipl["city"].str.strip().str.title().replace({"Bengaluru": "Bangalore"})
ipl = ipl.replace({"DD": "DC"})
ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
ipl = ipl.drop_duplicates(subset="id").reset_index(drop=True)
ipl["year"] = ipl["date"].dt.year
print(ipl)
print(ipl.dtypes)

import pandas as pd
import io

csv = """id,city,date,team1,winner,result_margin,method
1,Bangalore,2008-04-18,RCB,KKR,140,
2, chandigarh ,2008-04-19,KXIP,CSK,33,
3,Mumbai,2008-04-20,MI,RCB,5,
3,Mumbai,2008-04-20,MI,RCB,5,
4,,2010-03-13,KXIP,DD,5,
5,Bengaluru,2011-05-21,DD,RCB,7,
6,MUMBAI,2015-05-16,RR,RR,9,D/L
7,Delhi,2012-05-01,DD,,,
"""
ipl = pd.read_csv(io.StringIO(csv))
ipl = ipl.fillna({"method": "Normal", "city": "Unknown"}).dropna(subset=["winner"])
ipl["city"] = ipl["city"].str.strip().str.title().replace({"Bengaluru": "Bangalore"})
ipl = ipl.replace({"DD": "DC"})
ipl["date"] = pd.to_datetime(ipl["date"])
ipl["result_margin"] = ipl["result_margin"].astype(int)
ipl = ipl.drop_duplicates(subset="id").reset_index(drop=True)
ipl["year"] = ipl["date"].dt.year
print(ipl)
print(ipl.dtypes)

Practice: build your own clean_matches()

# 🏏 Your turn: add a step that maps short codes to full team names, then Run.
import pandas as pd
import numpy as np

raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4],
    "city":   [" pune", "Delhi", "Delhi", None, "HYDERABAD"],
    "date":   ["2017-04-06", "2017-04-08", "2017-04-08", "2017-04-09", "2017-04-10"],
    "winner": ["RPS", "DD", "DD", None, "SRH"],
    "margin": ["7", "51", "51", "", "15"],
})

def clean_matches(df):
    out = df.copy()
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].fillna("Unknown").str.strip().str.title()
    out["date"] = pd.to_datetime(out["date"])
    out["margin"] = pd.to_numeric(out["margin"], errors="coerce")
    out = out.drop_duplicates(subset="id")
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean)
print(clean.dtypes)

# 🏏 Your turn: add a step that maps short codes to full team names, then Run.
import pandas as pd
import numpy as np

raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4],
    "city":   [" pune", "Delhi", "Delhi", None, "HYDERABAD"],
    "date":   ["2017-04-06", "2017-04-08", "2017-04-08", "2017-04-09", "2017-04-10"],
    "winner": ["RPS", "DD", "DD", None, "SRH"],
    "margin": ["7", "51", "51", "", "15"],
})

def clean_matches(df):
    out = df.copy()
    out = out.dropna(subset=["winner"])
    out["city"] = out["city"].fillna("Unknown").str.strip().str.title()
    out["date"] = pd.to_datetime(out["date"])
    out["margin"] = pd.to_numeric(out["margin"], errors="coerce")
    out = out.drop_duplicates(subset="id")
    return out.reset_index(drop=True)

clean = clean_matches(raw)
print(clean)
print(clean.dtypes)

Revision Flashcards

1 / 5

Tap a card to flip it and see the answer.