Preparing Data with pandas

Real data is messy. A used-car website will have prices typed with commas, the same car listed twice, "petrol" and "Petrol" as two different fuels, and a few empty cells. A model cannot learn from that, and many scikit-learn models simply crash when they meet text or missing values.

Data scientists often say that most of a project's time goes into preparing data. In this lesson you will use pandas to inspect a messy dataset, fix it step by step, and fill missing values in a way that does not cheat.

Inspect before you change anything

Our example is ten used-car listings from Pune. In a real project you would load them with pd.read_csv("cars.csv"); here the CSV text is written inside the code (with io.StringIO) so the example runs anywhere.

import io
import pandas as pd

csv = """model,year,km_driven,fuel,price_lakh
Swift,2018,45000,Petrol,5.2
City ,2016,"62,000",petrol,6.8
Creta,2021,18000,Diesel,14.5
Nexon,2020,30000,Electric,9.9
Creta,2021,18000,Diesel,14.5
Alto,2015,unknown,CNG,2.1
nexon,,27000,Petrol,8.7
Baleno,2019,38000,Petrol,95.0
Brezza,2017,52000,petrol,4.9
Venue,2022,,Diesel,10.4
"""
cars = pd.read_csv(io.StringIO(csv))
print(cars.shape)
print(cars.dtypes)
print("Missing per column:", cars.isna().sum().to_dict())
print("Duplicate rows:", cars.duplicated().sum())

(10, 5)
model             str
year          float64
km_driven         str
fuel              str
price_lakh    float64
dtype: object
Missing per column: {'model': 0, 'year': 1, 'km_driven': 1, 'fuel': 0, 'price_lakh': 0}
Duplicate rows: 1

These four checks already reveal several problems:

• km_driven should be a number, but pandas read it as text (str) because of "62,000" and "unknown".
• year became float64 instead of a whole number because one value is missing; pandas stores a missing number as NaN (not a number), which is a float.
• One row is an exact duplicate (the second Creta).
• Some values are missing, and "unknown" is a missing value in disguise that isna() cannot see yet.

Also look at a few rows with cars.head() and at summary numbers with cars.describe(); the summary would show a maximum price of 95 lakh, far above the others.

Fix duplicates, text and types

Work through the problems one at a time and check the result after each step.

import io
import pandas as pd

csv = """model,year,km_driven,fuel,price_lakh
Swift,2018,45000,Petrol,5.2
City ,2016,"62,000",petrol,6.8
Creta,2021,18000,Diesel,14.5
Nexon,2020,30000,Electric,9.9
Creta,2021,18000,Diesel,14.5
Alto,2015,unknown,CNG,2.1
nexon,,27000,Petrol,8.7
Baleno,2019,38000,Petrol,95.0
Brezza,2017,52000,petrol,4.9
Venue,2022,,Diesel,10.4
"""
cars = pd.read_csv(io.StringIO(csv))

cars = cars.drop_duplicates().reset_index(drop=True)
cars["model"] = cars["model"].str.strip().str.title()   # "City " -> "City", "nexon" -> "Nexon"
cars["fuel"] = cars["fuel"].str.lower()                  # one spelling per fuel
km_text = cars["km_driven"].str.replace(",", "")         # "62,000" -> "62000"
cars["km_driven"] = pd.to_numeric(km_text, errors="coerce")  # "unknown" -> NaN

print(cars)
print(cars["fuel"].value_counts().to_dict())

    model    year  km_driven      fuel  price_lakh
0   Swift  2018.0    45000.0    petrol         5.2
1    City  2016.0    62000.0    petrol         6.8
2   Creta  2021.0    18000.0    diesel        14.5
3   Nexon  2020.0    30000.0  electric         9.9
4    Alto  2015.0        NaN       cng         2.1
5   Nexon     NaN    27000.0    petrol         8.7
6  Baleno  2019.0    38000.0    petrol        95.0
7  Brezza  2017.0    52000.0    petrol         4.9
8   Venue  2022.0        NaN    diesel        10.4
{'petrol': 5, 'diesel': 2, 'electric': 1, 'cng': 1}

errors="coerce" is the key trick: anything that cannot become a number turns into NaN, so hidden missing values become visible. Without it, pd.to_numeric would stop with an error at "unknown".

Missing values and outliers

You have three common choices for missing values:

An outlier is a value far from the rest. A common rule flags values more than 1.5 times the interquartile range (IQR, the spread of the middle half of the data) below the first quartile or above the third. Outliers are not always errors: a ₹95 lakh luxury car is possible, but a Baleno at that price is almost certainly a typo for 9.5. Check each one before you delete or fix it.

import pandas as pd

cars = pd.DataFrame({
    "model": ["Swift", "City", "Creta", "Nexon", "Alto", "Nexon", "Baleno", "Brezza", "Venue"],
    "year": [2018, 2016, 2021, 2020, 2015, None, 2019, 2017, 2022],
    "km_driven": [45000, 62000, 18000, 30000, None, 27000, 38000, 52000, None],
    "price_lakh": [5.2, 6.8, 14.5, 9.9, 2.1, 8.7, 95.0, 4.9, 10.4],
})

q1, q3 = cars["price_lakh"].quantile([0.25, 0.75])
iqr = q3 - q1
high = q3 + 1.5 * iqr
print(f"Prices above {high:.1f} lakh are suspicious:")
print(cars[cars["price_lakh"] > high])

cars.loc[cars["model"] == "Baleno", "price_lakh"] = 9.5   # confirmed typo, fix it
print("Rows with any missing value:", cars.isna().any(axis=1).sum())

Prices above 18.2 lakh are suspicious:
    model    year  km_driven  price_lakh
6  Baleno  2019.0    38000.0        95.0
Rows with any missing value: 3

Three of nine rows have a gap. Dropping them would throw away a third of the data, so filling is better here. But how you fill matters, as the next section shows.

Fill missing values without cheating

If you calculate the median from all rows and then split into training and test sets, information from the test rows has leaked into training. This is called data leakage, and it makes test scores look better than real-world performance. The safe way is to split first, learn the fill values from the training rows only, and reuse them on the test rows. scikit-learn's SimpleImputer does exactly that with fit and transform.

import pandas as pd
from sklearn.impute import SimpleImputer
from sklearn.model_selection import train_test_split

cars = pd.DataFrame({
    "year": [2018, 2016, 2021, 2020, 2015, None, 2019, 2017, 2022],
    "km_driven": [45000, 62000, 18000, 30000, None, 27000, 38000, 52000, None],
    "price_lakh": [5.2, 6.8, 14.5, 9.9, 2.1, 8.7, 9.5, 4.9, 10.4],
})
X = cars.drop(columns="price_lakh")   # features
y = cars["price_lakh"]                # target
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.33, random_state=42)

imputer = SimpleImputer(strategy="median").set_output(transform="pandas")
X_train_filled = imputer.fit_transform(X_train)   # learns medians from training rows
X_test_filled = imputer.transform(X_test)         # reuses the same medians

print("Medians learned:", imputer.statistics_)
print(X_test_filled)

Medians learned: [ 2019.5 34000. ]
     year  km_driven
7  2017.0    52000.0
1  2016.0    62000.0
5  2019.5    27000.0

set_output(transform="pandas") makes the imputer return a DataFrame with column names instead of a bare NumPy array. Other strategies are "mean", "most_frequent" (works for text too) and "constant". With add_indicator=True, the imputer also adds a column that records which values were missing, which can itself be useful information. In Pipelines & ColumnTransformer you will let a pipeline do these steps for you automatically.

Common mistakes

• Filling or scaling before splitting. Any statistic learned from the full dataset leaks test information. Split first, then fit on the training set only.
• Missing hidden gaps. Values such as "unknown", "-", 0 or 999 often mean "missing". Convert them to NaN (for example with errors="coerce" or replace) so pandas can see them.
• Deleting every outlier. Some extreme values are real and important (a very expensive car, a genuine fraud). Investigate before you remove anything.
• Forgetting reset_index after dropping rows. The old row numbers stay, which can confuse later code that expects 0, 1, 2 and so on.

Key points

• Start with shape, dtypes, isna().sum() and duplicated().sum() to find problems quickly.
• Clean text with .str.strip(), .str.lower() and .str.title(); convert numbers with pd.to_numeric(..., errors="coerce").
• Missing values can be dropped or filled; the median is a safe fill for skewed numbers.
• Use the IQR rule to flag outliers, then decide case by case.
• Learn fill values from the training set only, for example with SimpleImputer, to avoid data leakage.
• pandas 3.0 shows text columns as str and uses copy-on-write, so use .loc to change values.

import pandas as pd

df = pd.DataFrame({
    "name": ["Aarav", "Priya", "Kabir", "Priya", "Meera", "Rohan"],
    "city": ["Rohtak", "rohtak ", "Jind", "rohtak ", "ROHTAK", "Jind"],
    "marks": ["78", "85", "absent", "85", "91", "66"],
})
df = df.drop_duplicates().reset_index(drop=True)
df["city"] = df["city"].str.strip().str.title()
df["marks"] = pd.to_numeric(df["marks"], errors="coerce")
df["marks"] = df["marks"].fillna(df["marks"].median())
print(df)
print(df.groupby("city")["marks"].mean().round(1).to_dict())

    name    city  marks
0  Aarav  Rohtak   78.0
1  Priya  Rohtak   85.0
2  Kabir    Jind   81.5
3  Meera  Rohtak   91.0
4  Rohan    Jind   66.0
{'Jind': 73.8, 'Rohtak': 84.7}