pandas: Tables of Data

Ava exports a year of sales as a spreadsheet, with columns for the date, the plant, the quantity, and the price. She wants to know, which plant earns most, and which month is busiest. In a spreadsheet, that is a lot of clicking. With pandas, it is a few readable lines, and it works on a million rows too.

• A spreadsheet of sales
• Which plant earns the most?
• pandas: a spreadsheet you can program

The DataFrame

The core object in pandas is the DataFrame, a table with labelled columns. Create one from a dictionary, or read it from a file with read csv. Head shows the first rows, shape tells the size, and info and describe summarise the data. Pick a column with square brackets, and you get a Series, one column of the table.

import pandas as pd

df = pd.DataFrame({
    "plant": ["fern", "ivy", "fern", "rose", "ivy"],
    "qty": [3, 5, 2, 4, 1],
    "price": [9.5, 7.0, 9.5, 11.0, 7.0],
})
print(df)
print(df.shape)
print(df["qty"].sum())

  plant  qty  price
0  fern    3    9.5
1   ivy    5    7.0
2  fern    2    9.5
3  rose    4   11.0
4   ivy    1    7.0
(5, 3)
15

New columns and filtering

Create a new column with a simple expression. The revenue is quantity times price, for every row at once. Filter rows with a condition, inside square brackets, just like with NumPy masks. Combine conditions with an ampersand, and round brackets. Sorting with sort values, answers questions like, what are the top sales.

import pandas as pd

df = pd.DataFrame({"plant": ["fern", "ivy", "fern", "rose", "ivy"], "qty": [3, 5, 2, 4, 1], "price": [9.5, 7.0, 9.5, 11.0, 7.0]})
df["revenue"] = df["qty"] * df["price"]
print(df[df["revenue"] > 20])
print(df.sort_values("revenue", ascending=False).head(2))

  plant  qty  price  revenue
0  fern    3    9.5     28.5
1   ivy    5    7.0     35.0
3  rose    4   11.0     44.0
  plant  qty  price  revenue
3  rose    4   11.0     44.0
1   ivy    5    7.0     35.0

Group and summarise

The most powerful tool is group by. Split the table by plant, then total the revenue for each. In one line, Ava gets the answer to her question. You can compute several summaries at once with agg. This split, apply and combine pattern is used on every kind of data, from sales to science.

import pandas as pd

df = pd.DataFrame({"plant": ["fern", "ivy", "fern", "rose", "ivy"], "qty": [3, 5, 2, 4, 1], "price": [9.5, 7.0, 9.5, 11.0, 7.0]})
df["revenue"] = df["qty"] * df["price"]
by_plant = df.groupby("plant")["revenue"].sum().sort_values(ascending=False)
print(by_plant)
print(df.groupby("plant").agg(units=("qty", "sum"), avg_price=("price", "mean")))

plant
fern    47.5
rose    44.0
ivy     42.0
Name: revenue, dtype: float64
       units  avg_price
plant
fern       5        9.5
ivy        6        7.0
rose       4       11.0

Reading and cleaning data

Real data is messy. Read csv loads a file. Missing values appear as NaN. You can drop those rows, or fill them with a default. Convert text dates with to datetime, and then pull out the month. After cleaning, the analysis is the same as before. Data scientists spend most of their time on this step.

import pandas as pd, io

csv = "date,plant,qty\n2025-01-03,fern,3\n2025-01-20,ivy,\n2025-02-02,fern,2\n"
df = pd.read_csv(io.StringIO(csv))
print(df["qty"].isna().sum())
df["qty"] = df["qty"].fillna(0)
df["date"] = pd.to_datetime(df["date"])
print(df.groupby(df["date"].dt.month)["qty"].sum())

1
date
1    3.0
2    2.0
Name: qty, dtype: float64

Best selling month

Your turn. Given this small table, with a month and the sales, create a DataFrame, and find the month with the highest sales, using idxmax. Ready? Here is one solution. A DataFrame, then the index of the maximum value of the sales column.

import pandas as pd

df = pd.DataFrame({"month": ["Jan", "Feb", "Mar"], "sales": [120, 340, 210]})
best = df.loc[df["sales"].idxmax(), "month"]
print(best)

Feb

Summary