Data Analysis 📊

Why not just use lists?

# Pure Python: a loop, and a new list
prices = [10.0, 24.99, 3.25, 8.75]
with_vat = [p * 1.2 for p in prices]
print([round(p, 2) for p in with_vat])
print(round(sum(prices) / len(prices), 2))

[12.0, 29.99, 3.9, 10.5]
11.75

import numpy as np      # pip install numpy

prices = np.array([10.0, 24.99, 3.25, 8.75])

print(np.round(prices * 1.2, 2))      # no loop: applies to everything at once
print(prices.mean(), prices.std().round(2))
print(prices[prices > 9])

Three differences that matter. The syntax is shorter. The operation applies to the whole array at once, which is called vectorisation. And it runs perhaps fifty times faster, because the loop happens in compiled C over a contiguous block of memory rather than in Python over a list of separate objects.

pandas: a spreadsheet you can program

import pandas as pd      # pip install pandas

crew = pd.DataFrame([
    {"name": "Guybrush", "role": "captain", "pay": 100, "joined": "1990-10-15"},
    {"name": "Elaine", "role": "governor", "pay": 250, "joined": "1990-10-15"},
    {"name": "Otis", "role": "lookout", "pay": 40, "joined": "1991-01-03"},
    {"name": "Meathook", "role": "lookout", "pay": 45, "joined": "1991-06-20"},
])

print(crew.head())
print(crew.shape)
print(crew.dtypes)
print(crew["pay"].describe())

       name      role  pay      joined
0  Guybrush   captain  100  1990-10-15
1    Elaine  governor  250  1990-10-15
2      Otis   lookout   40  1991-01-03
3  Meathook   lookout   45  1991-06-20

(4, 4)

name      object
role      object
pay        int64
joined    object
dtype: object

count      4.000000
mean     108.750000
std      100.041658
min       40.000000
25%       43.750000
50%       72.500000
75%      137.500000
max      250.000000
Name: pay, dtype: float64

A DataFrame is a table with named columns, each column a typed array. .describe() on a numeric column is usually the first thing you run on unfamiliar data, and it will often tell you immediately that something is wrong.

Loading real data

import pandas as pd

df = pd.read_csv("sales.csv")
df = pd.read_json("data.json")
df = pd.read_excel("report.xlsx")           # needs openpyxl
df = pd.read_sql("SELECT * FROM crew", conn)
df = pd.read_html("https://example.com/table")[0]

df.to_csv("clean.csv", index=False)
df.to_json("clean.json", orient="records", indent=2)

That list is most of why pandas won: whatever the data is in, one line loads it, and one line writes it back out as something else.

Selecting, filtering, sorting

import pandas as pd

crew = pd.DataFrame({
    "name": ["Guybrush", "Elaine", "Otis", "Meathook"],
    "role": ["captain", "governor", "lookout", "lookout"],
    "pay": [100, 250, 40, 45],
})

print(crew["pay"].sum())
print(crew[crew["pay"] > 50])
print(crew[(crew["role"] == "lookout") & (crew["pay"] > 42)])
print(crew.sort_values("pay", ascending=False).head(2))
print(crew.loc[crew["name"] == "Otis", "pay"])

Grouping: the operation you will use most

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "South", "East"],
    "seller": ["Elaine", "Otis", "Guybrush", "Meathook", "Stan"],
    "amount": [1200, 340, 890, 1150, 4200],
})

print(sales.groupby("region")["amount"].sum().sort_values(ascending=False))
print(sales.groupby("region").agg(
    total=("amount", "sum"),
    average=("amount", "mean"),
    sellers=("seller", "count"),
))

region
East     4200
North    2090
South    1490
Name: amount, dtype: int64

        total  average  sellers
region
East     4200   4200.0        1
North    2090   1045.0        2
South    1490    745.0        2

Split by a key, apply a calculation, combine the results. It is the same idea as SQL's GROUP BY and as the dictionary-of-lists grouping you wrote by hand in Lesson 15, and it is the single most useful data operation there is.

Cleaning: where the real time goes

import pandas as pd
import numpy as np

messy = pd.DataFrame({
    "name": ["  Guybrush ", "ELAINE", None, "Otis"],
    "pay": ["100", "250", "40", "not recorded"],
    "joined": ["1990-10-15", "15/10/1990", None, "1991-01-03"],
})

clean = messy.copy()
clean["name"] = clean["name"].str.strip().str.title()
clean["pay"] = pd.to_numeric(clean["pay"], errors="coerce")
clean["joined"] = pd.to_datetime(clean["joined"], format="mixed", errors="coerce")

print(clean)
print(clean.isna().sum())
print(clean.dropna(subset=["name", "pay"]))

errors="coerce" turns anything unparseable into NaN rather than raising, which lets you see the whole extent of the mess before deciding what to do about it. .isna().sum() counts the gaps per column and is the second thing you should run on any new dataset.

An honest worked example

import pandas as pd

df = pd.DataFrame({
    "month": ["Jan", "Feb", "Mar", "Apr", "May", "Jun"],
    "sales": [100, 120, 118, 135, 150, 900],
})

print(f"mean:   {df['sales'].mean():.1f}")
print(f"median: {df['sales'].median():.1f}")

# a rough outlier check before believing either number
q1, q3 = df["sales"].quantile([0.25, 0.75])
iqr = q3 - q1
outliers = df[(df["sales"] < q1 - 1.5 * iqr) | (df["sales"] > q3 + 1.5 * iqr)]
print(outliers)

mean:   253.8
median: 127.5

  month  sales
5   Jun    900

The mean says business more than doubled. The median says it grew steadily. One exceptional June is dragging the mean, and reporting it without saying so would be technically true and actively misleading. Always look at the distribution before quoting an average, and say which one you used.

The wider ecosystem

import csv
from collections import defaultdict
from pathlib import Path
from statistics import mean

Path("sales.csv").write_text("""region,seller,amount
North,Elaine,1200
South,Otis,340
North,Guybrush,890
South,Meathook,1150
East,Stan,4200
""", encoding="utf-8")

amounts = defaultdict(list)
with open("sales.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        amounts[row["region"]].append(int(row["amount"]))

for region, values in sorted(amounts.items(), key=lambda kv: sum(kv[1]), reverse=True):
    print(f"{region:6} total {sum(values):5,}  mean {mean(values):7,.1f}  n={len(values)}")

East   total 4,200  mean 4,200.0  n=1
North  total 2,090  mean 1,045.0  n=2
South  total 1,490  mean   745.0  n=2

import pandas as pd

df = pd.read_csv("your-data.csv")

print(df.shape)            # how much is there
print(df.dtypes)           # did anything numeric load as text
print(df.head())           # what does a row look like
print(df.isna().sum())     # where are the gaps
print(df.describe())       # ranges, and any impossible values