Module 4 of 6 · 35 minutes

Working with data in pandas

pandas turns Python into a powerful, repeatable spreadsheet. It's the tool you'll use to prepare data for — and analyse results from — AI.

Learning objectives

Create and inspect DataFrames
Select columns and filter rows
Handle missing values and add columns
Group and summarise data
Plot and save results

4.1 What pandas is for

pandas is the standard Python library for working with tables of data — like a programmable spreadsheet. Its main object is the DataFrame: rows and named columns.

import pandas as pd

df = pd.DataFrame({
    "order_id": ["A1", "A2", "A3", "A4", "A5", "A6"],
    "product":  ["board", "oil", "board", "utensils", "board", "oil"],
    "region":   ["UK", "UK", "IE", "UK", "IE", None],
    "qty":      [2, 1, 1, 3, None, 2],
    "price":    [24.99, 6.50, 24.99, 12.00, 24.99, 6.50],
})
df.head()          # first rows
df.info()          # columns, types and missing values
df.describe()      # summary statistics for numeric columns

To load real data: pd.read_csv("file.csv") or pd.read_excel("file.xlsx").

Anatomy of a DataFrame: an index labels the rows, named columns hold one kind of value each.

4.2 Selecting and filtering

df["product"]                         # one column
df[["product", "price"]]              # several columns
df[df["region"] == "UK"]              # rows where region is UK
df[(df["product"] == "board") & (df["price"] > 20)]   # combine with & (and), | (or)

4.3 New columns and missing values

df.isna().sum()                       # count missing values per column
df["qty"] = df["qty"].fillna(1)       # a business decision: assume 1 if missing
df["region"] = df["region"].fillna("Unknown")
df["revenue"] = df["qty"] * df["price"]

Missing values are decisions

Filling, dropping or leaving gaps changes your results. Write down what you chose and why — the Data Quality course goes deeper.

4.4 Grouping and summarising

by_product = df.groupby("product")["revenue"].sum().sort_values(ascending=False)
print(by_product)

summary = df.groupby("region").agg(orders=("order_id", "count"),
                                   revenue=("revenue", "sum"))
print(summary)

df.groupby("product")["revenue"].sum(): split rows into groups, apply a calculation to each, combine the results.

4.5 A quick chart

import matplotlib.pyplot as plt

by_product.plot(kind="bar", title="Revenue by product")
plt.ylabel("£")
plt.tight_layout()
plt.show()

4.6 Saving results

df.to_csv("orders_clean.csv", index=False)
summary.to_excel("summary.xlsx")      # may need: pip install openpyxl

Exercise: best-selling region

Using the DataFrame above (after filling missing values and adding revenue), find the region with the highest total revenue and the average order quantity per product.

Show a solution

best_region = df.groupby("region")["revenue"].sum().idxmax()
avg_qty = df.groupby("product")["qty"].mean().round(2)
print(best_region)
print(avg_qty)

Key takeaways

A DataFrame is a table with named columns.
head(), info() and describe() give a quick overview.
Filter with conditions combined by & and |.
Treat missing-value handling as a documented decision.
groupby + agg answers most summary questions.