Data analysis with pandas
Load, clean, filter, group and summarise tables of data with pandas DataFrames, merges and dates.
In this lesson you will
- Create and inspect DataFrames
- Add columns and filter rows
- Summarise with groupby and pivot_table
- Clean data and merge tables
About 25 minutes
A spreadsheet that you control with code
Your shop has a year of sales in a spreadsheet: ten thousand rows. You want the total per city, the best-selling product, the average order, and a chart. In Excel that is a lot of clicking, repeated every month. With pandas, the most popular data library in Python, it takes a few lines you can re-run in a second. Pandas is the foundation of data analysis, data science and most reporting automation.
Install it with pip install pandas. Every example below was run for real with pandas 3.
The DataFrame: a table in memory
import pandas as pd
sales = pd.DataFrame({
"order": [1, 2, 3, 4, 5, 6, 7, 8],
"city": ["Lahore", "Karachi", "Lahore", "Multan", "Karachi", "Lahore", "Multan", "Karachi"],
"product": ["Pen", "Bag", "Book", "Pen", "Book", "Bag", "Book", "Pen"],
"qty": [10, 2, 3, 25, 4, 1, 6, 12],
"price": [45.0, 1250.0, 320.0, 45.0, 320.0, 1250.0, 320.0, 45.0],
})
print(sales)
print()
print(sales.shape) # (rows, columns)
print(sales.dtypes)
order city product qty price
0 1 Lahore Pen 10 45.0
1 2 Karachi Bag 2 1250.0
2 3 Lahore Book 3 320.0
3 4 Multan Pen 25 45.0
4 5 Karachi Book 4 320.0
5 6 Lahore Bag 1 1250.0
6 7 Multan Book 6 320.0
7 8 Karachi Pen 12 45.0
(8, 5)
order int64
city str
product str
qty int64
price float64
dtype: object
A DataFrame is a table: named columns, numbered rows. A single column is a Series. You almost always create a DataFrame by reading a file, for example pd.read_csv("sales.csv") or pd.read_excel("sales.xlsx").
Looking at the data
import pandas as pd
sales = pd.DataFrame({"city": ["Lahore", "Karachi", "Lahore", "Multan"],
"qty": [10, 2, 3, 25], "price": [45.0, 1250.0, 320.0, 45.0]})
print(sales.head(2)) # first rows (tail() gives the last)
print()
print(sales.describe()) # quick statistics for the numbers
print()
print(sales["city"].unique())
print(sales["qty"].sum(), sales["qty"].mean())
city qty price
0 Lahore 10 45.0
1 Karachi 2 1250.0
qty price
count 4.000000 4.000000
mean 10.000000 415.000000
std 10.614456 571.562187
min 2.000000 45.000000
25% 2.750000 45.000000
50% 6.500000 182.500000
75% 13.750000 552.500000
max 25.000000 1250.000000
<StringArray>
['Lahore', 'Karachi', 'Multan']
Length: 3, dtype: str
40 10.0
Adding columns and filtering rows
import pandas as pd
sales = pd.DataFrame({
"city": ["Lahore", "Karachi", "Lahore", "Multan", "Karachi"],
"product": ["Pen", "Bag", "Book", "Pen", "Book"],
"qty": [10, 2, 3, 25, 4],
"price": [45.0, 1250.0, 320.0, 45.0, 320.0],
})
sales["total"] = sales["qty"] * sales["price"] # a new column, for every row at once
print(sales)
print()
big = sales[sales["total"] > 1000] # keep rows where the test is True
print(big)
print()
print(sales[(sales["city"] == "Karachi") & (sales["qty"] > 2)])
print()
print(sales.sort_values("total", ascending=False).head(3))
city product qty price total
0 Lahore Pen 10 45.0 450.0
1 Karachi Bag 2 1250.0 2500.0
2 Lahore Book 3 320.0 960.0
3 Multan Pen 25 45.0 1125.0
4 Karachi Book 4 320.0 1280.0
city product qty price total
1 Karachi Bag 2 1250.0 2500.0
3 Multan Pen 25 45.0 1125.0
4 Karachi Book 4 320.0 1280.0
city product qty price total
4 Karachi Book 4 320.0 1280.0
city product qty price total
1 Karachi Bag 2 1250.0 2500.0
4 Karachi Book 4 320.0 1280.0
3 Multan Pen 25 45.0 1125.0
When combining conditions use & for and, | for or, and put each condition in brackets. Using the words and/or raises an error.
Group, summarise, repeat
import pandas as pd
sales = pd.DataFrame({
"city": ["Lahore", "Karachi", "Lahore", "Multan", "Karachi", "Lahore", "Multan", "Karachi"],
"product": ["Pen", "Bag", "Book", "Pen", "Book", "Bag", "Book", "Pen"],
"qty": [10, 2, 3, 25, 4, 1, 6, 12],
"price": [45.0, 1250.0, 320.0, 45.0, 320.0, 1250.0, 320.0, 45.0],
})
sales["total"] = sales["qty"] * sales["price"]
print(sales.groupby("city")["total"].sum())
print()
print(sales.groupby("product").agg(orders=("total", "count"), revenue=("total", "sum")))
print()
best = sales.groupby("product")["total"].sum().idxmax()
print("Best product by revenue:", best)
print()
print(pd.pivot_table(sales, values="total", index="city", columns="product", aggfunc="sum", fill_value=0))
city
Karachi 4320.0
Lahore 2660.0
Multan 3045.0
Name: total, dtype: float64
orders revenue
product
Bag 2 3750.0
Book 3 4160.0
Pen 3 2115.0
Best product by revenue: Book
product Bag Book Pen
city
Karachi 2500.0 1280.0 540.0
Lahore 1250.0 960.0 450.0
Multan 0.0 1920.0 1125.0
groupby splits the table into groups, applies a calculation to each, and combines the answers. It is the same idea as an Excel pivot table. pivot_table builds the cross-tab directly.
Cleaning messy data
import pandas as pd
import numpy as np
raw = pd.DataFrame({
"name": [" sara ", "ALI", "Mina", "ali", None],
"age": ["21", "abc", "25", "30", "28"],
"score": [90, np.nan, 85, 70, 88],
})
df = raw.copy()
df["name"] = df["name"].str.strip().str.title()
df["age"] = pd.to_numeric(df["age"], errors="coerce") # bad values become NaN
print(df)
print()
print(df.isna().sum()) # how many missing per column
print()
df["score"] = df["score"].fillna(df["score"].mean()) # fill gaps
df = df.dropna(subset=["name"]) # drop rows with no name
df = df.drop_duplicates(subset=["name"], keep="first")
print(df)
name age score
0 Sara 21.0 90.0
1 Ali NaN NaN
2 Mina 25.0 85.0
3 Ali 30.0 70.0
4 NaN 28.0 88.0
name 1
age 1
score 1
dtype: int64
name age score
0 Sara 21.0 90.00
1 Ali NaN 83.25
2 Mina 25.0 85.00
Real data is untidy: extra spaces, wrong types, blanks, duplicates. Analysts spend much of their time cleaning, and these half-dozen methods handle most cases.
Reading and writing files, joining tables
import pandas as pd
pd.DataFrame({"id": [1, 2, 3], "name": ["Sara", "Ali", "Mina"]}).to_csv("people.csv", index=False)
people = pd.read_csv("people.csv")
scores = pd.DataFrame({"id": [1, 2, 4], "mark": [90, 72, 55]})
print(people.merge(scores, on="id")) # only ids in both
print()
print(people.merge(scores, on="id", how="left")) # keep everyone from people
print(open("people.csv").read())
id name mark
0 1 Sara 90
1 2 Ali 72
id name mark
0 1 Sara 90.0
1 2 Ali 72.0
2 3 Mina NaN
id,name
1,Sara
2,Ali
3,Mina
Dates and time series
import pandas as pd
days = pd.DataFrame({
"date": pd.to_datetime(["2026-01-05", "2026-01-20", "2026-02-03", "2026-02-18", "2026-03-09"]),
"amount": [100, 150, 80, 120, 200],
})
days["month"] = days["date"].dt.to_period("M")
print(days.groupby("month")["amount"].sum())
print(days["date"].dt.day_name().tolist())
month
2026-01 250
2026-02 200
2026-03 200
Freq: M, Name: amount, dtype: int64
['Monday', 'Tuesday', 'Tuesday', 'Wednesday', 'Monday']
A chart in two lines
import matplotlib.pyplot as plt # pip install matplotlib
sales.groupby("city")["total"].sum().plot(kind="bar", title="Revenue by city")
plt.tight_layout()
plt.savefig("revenue.png")
Using a loop over rows when a column calculation would do. for i in range(len(df)): df.loc[i, "total"] = ... is slow and long. Write df["total"] = df["qty"] * df["price"]; pandas works on whole columns at once.
Changing a filtered piece and expecting the original to change. Filtering returns a copy. Assign back to the original (df.loc[mask, "col"] = value) when you want to modify it.
Create a DataFrame of 5 students with name and mark. Print the average mark and the names of students above that average.
Show one possible answer
import pandas as pd
df = pd.DataFrame({"name": ["Sara", "Ali", "Mina", "Hira", "Omar"], "mark": [90, 72, 85, 60, 95]})
avg = df["mark"].mean()
print("Average:", avg)
print(df[df["mark"] > avg]["name"].tolist())
Average: 80.4
['Sara', 'Mina', 'Omar']
Given daily sales for two shops, find each shop’s total and the shop with the higher total.
Hint: groupby then idxmax().
Show one possible answer
import pandas as pd
df = pd.DataFrame({"shop": ["A", "B", "A", "B", "A"], "sales": [100, 80, 120, 150, 90]})
totals = df.groupby("shop")["sales"].sum()
print(totals)
print("Winner:", totals.idxmax())
shop
A 310
B 230
Name: sales, dtype: int64
Winner: A
Download a free CSV (for example a public dataset from your country’s open-data site) and ask it questions: what is the biggest? the average? which month? Every question you can phrase is usually a groupby away.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is a DataFrame?
- A picture
- A function
- A loop
- A table with named columns and rows
It is pandas’ main table type.
How do you add a column total from qty and price?
- A for loop only
df["total"] = df["qty"] * df["price"]df.add(total)df.new()
Column operations apply to every row.
What does groupby("city")["total"].sum() give?
- One grand total only
- The city list
- An error
- A total for each city
It splits by city and adds each group.
Which operator combines two row conditions?
&with bracketsand+,
Use & or | and bracket each condition.
What does pd.to_numeric(x, errors="coerce") do with bad values?
- Deletes the column
- Stops
- Turns them into NaN
- Turns them into 0
Missing values can then be filled or dropped.
Summary
- A DataFrame is a table; a column is a Series.
- Work on whole columns, not loops.
- groupby + a calculation summarises data.
- Clean first: types, blanks, duplicates.
Questions
pandas or Excel?
Excel is great for quick looks. pandas wins for big data, repeated reports and automation.
What is NaN?
Not a Number: pandas' marker for a missing value.
How do I read an Excel file?
pd.read_excel("file.xlsx"). It needs the openpyxl package installed.
