Data analysis with Python: investigate and explain a dataset

python -m venv .venv
.venv\Scripts\python -m pip install pandas==3.0.6 matplotlib==3.11.2 numpy==2.5.3

# make_data.py: invent messy support tickets (no real people)
import random
import sys
from datetime import date, timedelta
from pathlib import Path

import pandas as pd

rng = random.Random(2026)
HOURS = {"login": 2, "billing": 6, "delivery": 16}
CHAT = {"login": 0.6, "billing": 0.3, "delivery": 0.12}  # the topic sways the channel
OTHER = {"login": ["email", "phone"], "billing": ["email", "phone", "phone"],
         "delivery": ["email", "email", "email", "phone"]}
rows = []
for i in range(1, 301):
    day = rng.randrange(165)  # 1 January to 14 June 2026
    topic = rng.choice(["login", "login", "billing", "delivery"])
    chat = rng.random() < CHAT[topic] * (0.4 + day / 165)  # chat grows over time
    channel = "chat" if chat else rng.choice(OTHER[topic])
    hours = HOURS[topic] * rng.lognormvariate(0, 0.6) * (0.8 if chat else 1)
    score = round(min(5, max(1, 5.2 - hours / 6 + rng.gauss(0, 0.8))))
    rows.append({
        "ticket": f"T{i:04d}",
        "opened": str(date(2026, 1, 1) + timedelta(days=day)),
        "channel": channel.upper() if rng.random() < 0.1 else channel,
        "topic": topic,
        "time": round(hours * 60) if channel == "phone" else round(hours, 1),
        "unit": "min" if channel == "phone" else "h",
        "score": None if rng.random() < 0.35 else score,
    })
rows[57]["time"] = 999  # a placeholder someone typed
rows += rows[100:106]  # six rows exported twice

df = pd.DataFrame(rows)
if "--write" in sys.argv:
    df.to_csv(Path(__file__).parent / "tickets_raw.csv", index=False, mode="x")
    print(f"Wrote {len(df)} rows")
else:
    print(f"Dry run: {len(df)} rows. Add --write to save.")

python make_data.py
Dry run: 306 rows. Add --write to save.
python make_data.py --write
Wrote 306 rows

import pandas as pd

df = pd.read_csv("tickets_raw.csv")
print(df.shape)
print(df.dtypes)
print(df["channel"].value_counts())
print(df.describe().round(1))
print("duplicate rows:", df.duplicated().sum())

(306, 7)
ticket         str
opened         str
channel        str
topic          str
time       float64
unit           str
score      float64
dtype: object
channel
chat     93
phone    90
email    88
CHAT     14
EMAIL    11
PHONE    10
Name: count, dtype: int64
         time  score
count   306.0  219.0
mean    156.1    3.8
std     343.6    1.3
min       0.3    1.0
25%       2.2    3.0
50%       9.6    4.0
75%     116.8    5.0
max    2659.0    5.0
duplicate rows: 6

# clean.py: never edits the raw file; writes a cleaned copy and a log
import sys
from pathlib import Path

import pandas as pd

HERE = Path(__file__).parent
df = pd.read_csv(HERE / "tickets_raw.csv", parse_dates=["opened"])
log = [f"read {len(df)} rows"]

n = len(df)
df = df.drop_duplicates()
log.append(f"dropped {n - len(df)} duplicate rows")

df["channel"] = df["channel"].str.lower()
log.append("lower-cased channel")

bad = df["time"] == 999
df.loc[bad, "time"] = None
log.append(f"set {bad.sum()} placeholder 999 to missing")

mins = df["unit"] == "min"
df.loc[mins, "time"] = df.loc[mins, "time"] / 60
df = df.drop(columns="unit").rename(columns={"time": "hours"})
log.append(f"converted {mins.sum()} minute times to hours")

log.append(f"kept {len(df)} rows, {df['score'].isna().sum()} without a score")
print("\n".join(log))
if "--write" in sys.argv:
    df.to_csv(HERE / "tickets_clean.csv", index=False, mode="x")
    with open(HERE / "cleaning_log.txt", "x", encoding="utf-8") as f:
        f.write("\n".join(log) + "\n")
else:
    print("Dry run: add --write to save")

read 306 rows
dropped 6 duplicate rows
lower-cased channel
set 1 placeholder 999 to missing
converted 99 minute times to hours
kept 300 rows, 85 without a score
Dry run: add --write to save

import pandas as pd

df = pd.read_csv("tickets_clean.csv")
print(df.groupby("channel")["hours"].agg(["size", "count", "median", "mean"]).round(1))
print(pd.crosstab(df["topic"], df["channel"]))
print(df.pivot_table(index="topic", columns="channel", values="hours", aggfunc="median").round(1))

         size  count  median  mean
channel                           
chat      104    104     2.1   4.0
email      97     96     7.4  11.7
phone      99     99     4.0   7.5
channel   chat  email  phone
topic                       
billing     19     16     33
delivery    11     50     24
login       74     31     42
channel   chat  email  phone
topic                       
billing    4.8    6.0    5.9
delivery  12.9   14.4   16.2
login      1.6    1.9    1.8

topic,target_hours
login,4
billing,12
delivery,24

import pandas as pd

df = pd.read_csv("tickets_clean.csv")
targets = pd.read_csv("targets.csv")
df = df.merge(targets, on="topic", how="left", validate="many_to_one", indicator=True)
print(len(df), df["_merge"].value_counts().to_dict())

timed = df[df["hours"].notna()]
met = timed["hours"] <= timed["target_hours"]
print(met.groupby(timed["channel"]).mean().round(2))

300 {'both': 300, 'left_only': 0, 'right_only': 0}
channel
chat     0.93
email    0.80
phone    0.90
dtype: float64

# chart.py: tickets opened per month, one line per channel
import sys
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd

HERE = Path(__file__).parent
df = pd.read_csv(HERE / "tickets_clean.csv", parse_dates=["opened"])
monthly = pd.crosstab(df["opened"].dt.to_period("M"), df["channel"])
print(monthly)

fig, ax = plt.subplots(figsize=(7, 4))
monthly.plot(ax=ax, marker="o")
ax.set_ylim(bottom=0)
ax.set_title("Tickets per month by channel (invented data)")
ax.set_xlabel("Month opened (June holds 1 to 14 June only)")
ax.set_ylabel("Tickets opened")
fig.tight_layout()
if "--write" in sys.argv:
    with open(HERE / "tickets_by_month.png", "xb") as f:  # never overwrite
        fig.savefig(f, format="png", dpi=150)

# boot.py: how sure can we be about a gap between two medians?
import random
import statistics

import pandas as pd


def interval(a, b, seed=1):
    """95% interval for median(a) - median(b) from 1,000 resamples."""
    rng = random.Random(seed)
    gaps = sorted(
        statistics.median(rng.choices(a, k=len(a))) - statistics.median(rng.choices(b, k=len(b)))
        for _ in range(1000)
    )
    return gaps[24], gaps[974]  # the 25th and 975th


df = pd.read_csv("tickets_clean.csv").dropna(subset=["hours"])
print("hours v score:", round(df["hours"].corr(df["score"]), 2))
for topic in [None, "login", "billing", "delivery"]:
    part = df if topic is None else df[df["topic"] == topic]
    chat = part.loc[part["channel"] == "chat", "hours"].tolist()
    email = part.loc[part["channel"] == "email", "hours"].tolist()
    gap = statistics.median(chat) - statistics.median(email)
    low, high = interval(chat, email)
    print(f"{topic or 'all'}: n {len(chat)}/{len(email)}, gap {gap:.2f}, interval {low:.2f} to {high:.2f}")

hours v score: -0.8
all: n 104/96, gap -5.35, interval -8.95 to -3.90
login: n 74/30, gap -0.30, interval -1.30 to 0.10
billing: n 19/16, gap -1.15, interval -3.70 to 0.60
delivery: n 11/50, gap -1.45, interval -8.60 to 13.10

# check.py: recompute the chat median from the RAW file, without pandas
import csv
import statistics

seen, hours = set(), []
with open("tickets_raw.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        key = tuple(row.values())
        if key in seen:
            continue
        seen.add(key)
        t = float(row["time"])
        if row["channel"].lower() == "chat" and t != 999:
            hours.append(t / 60 if row["unit"] == "min" else t)
print(len(hours), round(statistics.median(hours), 1))

104 2.1

Columns: ticket (text), opened (date), channel (chat, email, phone),
topic (text), hours (number, some missing), score (1 to 5, many missing).
Invented row: T0001,2026-01-31,email,billing,17.6,1.0
Using pandas 3.0.6, print the median hours per channel and topic.
Do not drop or fill missing values. Print the row count after each step.

import pandas as pd

df = pd.read_csv("tickets_clean.csv").dropna()
targets = pd.read_csv("targets.csv")
df = df.merge(targets, on="channel")
print(df.groupby("channel")["resolved_on_time"].mean())