Unit IV — Data Handling with Pandas

Why Pandas? Meet the Series

Lists vs Series - the ladder

Python list: values only, position access doses[2]. NumPy: fast math, still no labels. Series: values + index labels - doses["P03"] reads as patient P03. That label is what makes PK time-courses and ADR severity counts self-documenting.

# Series: one labelled column of doses (mg)
import pandas as pd
doses = pd.Series([100, 250, 500, 500, 250],
    index=["P01", "P02", "P03", "P04", "P05"], name="dose_mg")
print(doses.mean(), doses.max())

DataFrame - columns, index, values

Synthetic teaching data - generated with seeded numpy for classroom practice.

# DataFrame from a dict: each key becomes a column
adr_mini = pd.DataFrame({
    "patient": ["P01", "P02", "P03"],
    "drug": ["PCM", "IBU", "AMX"],
    "severity": ["mild", "severe", "moderate"]})
print(adr_mini.shape, list(adr_mini.columns))

Dataset 1 - plasma concentration vs time

Design: 5 patients x 4 timepoints

Columns: patient time_hr conc_ng_ml. Seeded numpy (seed 42) so every run reproduces the same table. Exponential decay + small noise mimics a real single-dose PK curve.

# Seeded synthetic PK study (20 rows)
import numpy as np, pandas as pd
np.random.seed(42)
rows = []
for p in ["P01", "P02", "P03", "P04", "P05"]:
    cmax = np.random.uniform(700, 950)
    for t in [0.5, 1.0, 2.0, 4.0]:
        conc = round(cmax*np.exp(-0.35*t)+np.random.normal(0,18), 1)
        rows.append((p, t, conc))
pk = pd.DataFrame(rows, columns=["patient", "time_hr", "conc_ng_ml"])

Why Seeded? Without seed(42) every student gets different numbers and answers stop matching. Seeded data = one answer key for the whole class.

Dataset 2 - ADR reports with missing outcomes

# 12 ADR reports; 2 outcomes left blank (NaN) on purpose
adr = pd.DataFrame({
    "patient": ["P01".."P12"], # 12 IDs
    "drug": ["Paracetamol", "Ibuprofen", ...],
    "severity": ["mild", "moderate", "severe", ...],
    "outcome": ["recovered", ...,None, ...,None]}) # 2 NaN
pk.to_csv("u4_pk.csv", index=False)
adr.to_csv("u4_adr.csv", index=False)
with pd.ExcelWriter("u4_data.xlsx") as w:
    pk.to_excel(w, sheet_name="PK", index=False)
    adr.to_excel(w, sheet_name="ADR", index=False)

File layout

u4_pk.csv holds the concentration table. u4_adr.csv holds ADR reports. u4_data.xlsx bundles both as sheets PK and ADR via openpyxl.

Reading CSV and Excel

# Read the files we just saved
pk2 = pd.read_csv("u4_pk.csv")
adr2 = pd.read_excel("u4_data.xlsx", sheet_name="ADR")
print(pk2.shape, adr2.shape)

The two readers side by side

read_csv(path) parses comma text - fastest for PK exports from instruments. read_excel(path, sheet_name=...) opens workbook sheets - standard for ADR registers. Both return a DataFrame; check .shape immediately.

Preview rows, check structure

# Peek at the top, bottom and structure
print(pk.head(3)) # first 3 rows
print(pk.tail(3)) # last 3 rows
pk.info() # dtype + non-null counts

Output info PK: 20 entries, 3 cols, float64 x2 + object x1, 612 bytes | ADR: 12 entries, outcome 10 non-null

Reading info

Non-Null Count below the total = missing values. outcome shows 10 of 12 - the 2 NaN we planted. dtypes tell you which columns can do math.

describe - one-line summary statistics

print(pk.describe().round(2))
print(adr["severity"].value_counts())

Output severity value_counts: mild 4, moderate 4, severe 4 (balanced teaching set)

How to read describe

mean centres the curve, std spreads it, min / max bound it, 25% / 50% / 75% split it in quartiles. count below 20 would itself flag missing concentrations.

Finding Missing Values

# BEFORE: count the gaps
print(adr.isna().sum())

Two fixes - drop rows or fill values

# AFTER option A: drop gappy rows (12 -> 10 rows)
clean_drop = adr.dropna()
# AFTER option B: keep rows, label the gap
clean_fill = adr.copy()
clean_fill["outcome"] = clean_fill["outcome"].fillna("under review")

Which fix when? dropna when gaps are few and rows expendable - simplest for analysis. fillna when every report counts - ADR registers must not lose cases. Never fill concentrations with 0; it fakes a measurement.

Filtering with Conditions

# Boolean mask keeps only matching rows
severe = adr[adr.severity == "severe"] # 4 rows
high = pk[pk.conc_ng_ml > 500] # 9 rows above threshold

Output conc > 500: 9 rows, all at 0.5h-1.0h (early timepoints only)

Mask anatomy

adr.severity == 'severe' builds a True/False mask; adr[mask] keeps True rows. Combine with & (and) / | (or), each test in brackets: (a) & (b).

Groupby: Split-Apply-Combine

# Mean conc per timepoint + ADR counts per drug
print(pk.groupby("time_hr")["conc_ng_ml"].mean().round(1))
print(adr.groupby("drug").size())

Output ADR counts: Amoxicillin 3, Ibuprofen 4, Paracetamol 5

Split - Apply - Combine

Split rows by key (time, drug). Apply one function (mean, size, count). Combine results into a small summary frame. Two keys give two-level summaries: groupby(["drug","severity"]).

ADR summary pipeline - one function, full workflow

def adr_summary(df):
    # clean -> group -> sort, all inside one function
    df = df.copy()
    df["outcome"] = df["outcome"].fillna("under review")
    out = df.groupby(["drug", "severity"]).size().reset_index(name="n")
    return out.sort_values(["drug", "n"], ascending=[True, False])
print(adr_summary(adr).to_string(index=False))

Trace the pipeline

1 copy protects the original. 2 fillna labels 2 gaps. 3 groupby + size counts each drug-severity cell. 4 reset_index turns the result back into a table. 5 sort lists the most frequent severity first within each drug.

Exam Gold

This function chains every Unit-IV skill: clean, filter-ready, group, sort. Reproduce it from memory and the 5-mark pipeline question is yours.

Two Errors, Zero Panic

# KeyError: wrong column name
try:
     adr["dosage"]
except KeyError as e:
     print("KeyError:", e) # KeyError: 'dosage'

KeyError - the typo tax

Column names are case- and spelling-sensitive: "Severity" is not "severity". Diagnosis: print(adr.columns.tolist()) and copy-paste. Prevention: never retype a column name from memory.

Every Unit-IV method on one card

How to Memorise

Read each row as verb-object-target: Series labels, DataFrame tables, readers load, head previews, info diagnoses, cleaners fix, masks filter, groupby summarises.

* Three Demos, Real Output

D1 · describe row guide

# D1: what each describe() row tells - real PK frame
print(pk["conc_ng_ml"].describe().round(2))

D2 · median vs mean fill

# D2: knock out P03 2.0h (was 400.1), then choose the fill
pk2 = pk.copy(); pk2.loc[10, "conc_ng_ml"] = float("nan")
med = pk2.conc_ng_ml.median() # 492.4
avg = pk2.conc_ng_ml.mean() # 451.06
clean = pk2.conc_ng_ml.fillna(med) # 0 gaps left

D3 · multi-aggregation

# D3: centre AND peak per timepoint in one pass
print(pk.groupby("time_hr")["conc_ng_ml"].agg(["mean", "max"]).round(1))

Dive Rules

count = coverage, mean = centre, 50% = median, min/max = bounds. Low 4.0h troughs drag the mean (451.06) below the median (492.4) - skewed tails mean fill with median, never 0. agg(['mean','max']) gives centre + peak together.

Exam Points

Q1 - Series vs DataFrame (2m)

Series is 1-D labelled pd.Series(doses); DataFrame is 2-D pd.DataFrame(d). Quote dose list vs ADR table.

Q2 - read_csv vs read_excel (2m)

read_csv('u4_pk.csv') for text CSV; read_excel(x, sheet_name='ADR') for workbooks. Always verify with .shape.

Q3 - head/info/describe (3m)

head() previews, info() shows dtypes + non-null counts (outcome 10/12), describe() gives mean 448.51, max 727.80.

Q4 - isna/dropna/fillna (3m)

isna().sum() found outcome 2; dropna() 12->10 rows; fillna('under review') keeps all 12.

Q5 - filter + groupby pipeline (5m)

Filter adr[sev=='severe'] (4 rows); group groupby('time_hr').mean() decay 650.9->187.7; reproduce adr_summary().

Five questions - honest answers only

Try each question first, then check the answer after the dash.

• Series([100,250]) with index [P01,P02]: what does s['P02'] return? - 250 - labels select values.
• adr has 12 rows, outcome 2 NaN: what does dropna leave? What does fillna keep? - dropna: 10 rows; fillna: all 12 with labels.
• Write the one-liner that keeps only severe ADR rows. - adr[adr.severity == 'severe'] - mask then select.
• groupby('time_hr').mean() on PK gives 650.9, 557.0, 398.5, 187.7: rising or falling? - Falling: a classic decay curve.
• adr['dosage'] raises KeyError: what is the first debugging command? - print(adr.columns.tolist()) - copy the exact name.

Unit-IV in One Line

Pandas turns PK tables and ADR reports into labelled DataFrames you read, explore, clean, filter and group - one reproducible pipeline from raw file to safety summary.

! Pinpoints - Must Remember

3 exam predictions

P1 - 5-mark pipeline: reproduce adr_summary() from memory - clean, group, sort. P2 - 3-mark describe: quote mean 448.51, median 459.80, max 727.80; say what 50% means. P3 - 2-mark debug: KeyError on 'dosage' - first command print(adr.columns.tolist()).

Mnemonic - the 5 L's

Load, Look, Lose-gaps, Limit, Lump: Load files, Look with head/info/describe, Lose gaps via dropna/fillna, Limit rows with a mask, Lump rows with groupby. Five L's, five calls, full unit.