Exploring Hospital Patient Records

import pandas as pd
import numpy as np

# Build the MedCore admissions dataset
df = pd.DataFrame({
    "patient_id":       ["P001","P002","P003","P004","P005","P006",
                         "P007","P008","P009","P010","P011","P012"],
    "age":              [67, 34, 78, 52, 45, 71, 29, 83, 58, 41, 65, 37],
    "department":       ["Cardiology","Orthopaedics","General Medicine","Cardiology",
                         "Oncology","General Medicine","Orthopaedics","General Medicine",
                         "Cardiology","Oncology","Cardiology","Orthopaedics"],
    "admission_type":   ["Emergency","Elective","Urgent","Emergency","Elective",
                         "Urgent","Elective","Emergency","Emergency","Elective",
                         "Urgent","Elective"],
    "los_days":         [8, 3, 11, 6, 14, 9, 2, 13, 7, 10, 5, 4],
    "readmitted_30d":   [1, 0, 1, 0, 0, 1, 0, 1, 0, 0, 1, 0],
    "discharge_status": ["Home","Home","Rehab","Home","Home","Rehab",
                         "Home","Rehab","Home","Home","Home","Home"]
})

# Inspect shape and types
print("Shape:", df.shape)
print("\nData types:")
print(df.dtypes)

# Missing value audit
print("\nMissing values:")
print(df.isnull().sum())

# Summary stats for the two key numeric columns
print("\nLength of Stay — summary:")
print(df["los_days"].describe().round(1))

print(f"\nOverall readmission rate: {df['readmitted_30d'].mean():.1%}")
print(f"Total bed-days used:       {df['los_days'].sum()}")

# Aggregate by department: admissions count, total bed-days, mean and std of LOS
dept = df.groupby("department").agg(
    admissions    = ("patient_id", "count"),        # total patients admitted
    total_bed_days= ("los_days",   "sum"),           # total bed-days consumed
    mean_los      = ("los_days",   "mean"),          # average length of stay
    std_los       = ("los_days",   "std"),           # variability in stay length
    readmit_rate  = ("readmitted_30d", "mean")       # readmission rate per dept
).round(2).reset_index()

# Sort by total bed-days — the heaviest operational burden first
dept = dept.sort_values("total_bed_days", ascending=False)

print("Department performance summary:")
print(dept.to_string(index=False))

# Which department has the most variable LOS?
most_variable = dept.loc[dept["std_los"].idxmax(), "department"]
print(f"\nMost variable LOS: {most_variable} (std = {dept['std_los'].max():.2f} days)")

# Are the highest-admissions and longest-stay departments the same?
most_admissions = dept.loc[dept["admissions"].idxmax(), "department"]
longest_stay    = dept.loc[dept["mean_los"].idxmax(), "department"]
print(f"Most admissions:  {most_admissions}")
print(f"Longest avg stay: {longest_stay}")
print(f"Same department:  {most_admissions == longest_stay}")

# Bin age into clinical age groups using pd.cut()
# bins defines the edges of each bracket — right=True means right edge is included
# labels assigns a human-readable name to each bracket
df["age_group"] = pd.cut(
    df["age"],
    bins=[0, 40, 60, 75, 100],
    labels=["Under 40", "40–60", "61–75", "Over 75"],
    right=True
)

# Group by age group and compute key metrics
age_analysis = df.groupby("age_group", observed=True).agg(
    patient_count  = ("patient_id",    "count"),
    readmit_rate   = ("readmitted_30d","mean"),
    mean_los       = ("los_days",      "mean")
).round(2).reset_index()

print("Readmission rate by age group:")
print(age_analysis.to_string(index=False))

# Gap between highest and lowest readmission rate
max_rate = age_analysis["readmit_rate"].max()
min_rate = age_analysis["readmit_rate"].min()
max_grp  = age_analysis.loc[age_analysis["readmit_rate"].idxmax(), "age_group"]
min_grp  = age_analysis.loc[age_analysis["readmit_rate"].idxmin(), "age_group"]

print(f"\nHighest readmission: {max_grp} at {max_rate:.0%}")
print(f"Lowest readmission:  {min_grp} at {min_rate:.0%}")
print(f"Gap:                 {(max_rate - min_rate):.0%} percentage points")

# Overall admission type split — what proportion of all admissions is each type?
type_split = df["admission_type"].value_counts(normalize=True).round(3) * 100
print("Admission type distribution (% of total):")
print(type_split.to_string())

# Cross-tabulate department vs admission type
# pd.crosstab() creates a frequency table showing how many rows fall into each cell
cross = pd.crosstab(df["department"], df["admission_type"])
print("\nDepartment × Admission Type (count):")
print(cross.to_string())

# Average LOS by admission type — which type stays longest?
los_by_type = df.groupby("admission_type")["los_days"].agg(["mean","std"]).round(1)
print("\nAverage LOS by admission type:")
print(los_by_type.to_string())

# Which dept + admission_type combination has the longest average LOS?
combo = df.groupby(["department","admission_type"])["los_days"].mean().round(1)
print("\nLOS by department + admission type:")
print(combo.sort_values(ascending=False).head(5).to_string())