AdvancedLesson 28 of 306 min read

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.

Good to know

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
Good to know

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")
Common mistake

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.

Common mistake

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.

Practice

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']
Practice

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
Learn by exploring

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?

  1. A picture
  2. A function
  3. A loop
  4. A table with named columns and rows

It is pandas’ main table type.

How do you add a column total from qty and price?

  1. A for loop only
  2. df["total"] = df["qty"] * df["price"]
  3. df.add(total)
  4. df.new()

Column operations apply to every row.

What does groupby("city")["total"].sum() give?

  1. One grand total only
  2. The city list
  3. An error
  4. A total for each city

It splits by city and adds each group.

Which operator combines two row conditions?

  1. & with brackets
  2. and
  3. +
  4. ,

Use & or | and bracket each condition.

What does pd.to_numeric(x, errors="coerce") do with bad values?

  1. Deletes the column
  2. Stops
  3. Turns them into NaN
  4. 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.

Next lesson

Next: Flask, building your first web application.

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.

Progress is saved in this browser.