pandas: from raw data to a clean table

In a real project, you spend far more time preparing data than training the model. pandas is the tool for that preparation: reading, cleaning, transforming, aggregating. A model trained on dirty data gives wrong results, with great confidence.

Series and DataFrame

A Series is a labelled column, a DataFrame a table of columns sharing the same index. Each column is a NumPy array: everything from the previous step applies.

import pandas as pd

df = pd.read_csv("sales.csv", parse_dates=["date"])
df.shape          # (rows, columns)
df.head()         # the first 5 rows
df.info()         # types and missing values, column by column
df.describe()     # statistics for numeric columns

info() and describe() are the first two commands to run on any dataset. They reveal half the problems.

Selecting and filtering

df["amount"]                              # one column (Series)
df[["customer", "amount"]]                # several columns (DataFrame)
df.loc[df["amount"] > 100, "customer"]    # filter + column, by labels
df.iloc[:10, :3]                          # by positions

big_sales = df.query("amount > 100 and country == 'BF'")

loc works with labels, iloc with positions. Mixing them up is a classic source of silent bugs.

Cleaning

df.isna().sum()                               # missing values per column
df = df.drop_duplicates()
df["amount"] = df["amount"].fillna(df["amount"].median())
df["country"] = df["country"].str.strip().str.upper()
df = df[df["amount"] >= 0]                    # outliers

For missing values, there’s no universal rule: drop the row, fill with the median, or create a “was missing” column. The right choice depends on why the value is missing, and it’s a real decision, not a technical detail.

Transforming: creating features

This is where domain knowledge makes the difference between an average model and a good one.

df["month"] = df["date"].dt.month
df["weekday"] = df["date"].dt.day_name()
df["avg_basket"] = df["amount"] / df["item_count"]
df["big_customer"] = df["amount"] > df["amount"].quantile(0.9)

Aggregating: groupby

by_country = (
    df.groupby("country")
      .agg(revenue=("amount", "sum"), sales=("amount", "size"), average=("amount", "mean"))
      .sort_values("revenue", ascending=False)
)

The split / apply / combine trio answers most business questions: which country, which month, which customer?

Joining tables

customers = pd.read_csv("customers.csv")
df = df.merge(customers, on="customer_id", how="left")

After every merge, check that the row count hasn’t exploded: a duplicated key on one side multiplies the rows on the other.

Visualising to understand

import matplotlib.pyplot as plt

df["amount"].hist(bins=50)
df.groupby("month")["amount"].sum().plot(kind="bar")
plt.show()

A histogram shows in one second a highly skewed distribution or impossible values that no mean will reveal.

Project to validate the step

Take the Titanic dataset on Kaggle and produce a clean notebook that:

1. describes each column and its missing values;
2. justifies a filling strategy for age;
3. creates at least three new features (family size, title extracted from the name…);
4. answers with groupby: who survived, by class, sex, age?

This notebook will be reused as is in the next step, to train the first model.

Resources

• 10 minutes to pandas, the official documentation.
• The “Pandas” course on Kaggle Learn.