rpotter6298
63 lines
2.3 KiB
Python
63 lines
2.3 KiB
Python
import pandas as pd
|
|
import numpy as np
|
|
|
|
QC_FILE = "BEA25P077_RP.xlsx"
|
|
PATIENTS_FILE = "patients.xlsx"
|
|
THRESHOLDS = [0, 0.1, 0.25, 0.5, 0.75, 1.0, 1.5, 2.0, 5.0]
|
|
|
|
# Load patient data
|
|
patients = pd.read_excel(PATIENTS_FILE, usecols=["Number", "Exf"])
|
|
patients = patients.dropna(subset=["Number"])
|
|
patients["Number"] = patients["Number"].astype(int)
|
|
patients["Exf"] = patients["Exf"].astype(int)
|
|
|
|
def parse_conc(val):
|
|
"""Return float concentration, or NaN for 'Too low' / missing."""
|
|
if isinstance(val, str):
|
|
return np.nan
|
|
try:
|
|
return float(val)
|
|
except (TypeError, ValueError):
|
|
return np.nan
|
|
|
|
def analyse_sheet(sheet_name, conc_col):
|
|
df = pd.read_excel(QC_FILE, sheet_name=sheet_name, usecols=["ID", conc_col])
|
|
df["conc_val"] = df[conc_col].apply(parse_conc)
|
|
# Extract patient number from ID like "4A_G2" or "4B_G2"
|
|
extracted = df["ID"].str.extract(r"^(\d+)[AB]")[0]
|
|
df["Number"] = pd.to_numeric(extracted, errors="coerce")
|
|
df = df.dropna(subset=["Number"])
|
|
df["Number"] = df["Number"].astype(int)
|
|
# Merge with patient diagnosis
|
|
df = df.merge(patients, on="Number", how="left")
|
|
return df
|
|
|
|
sheets = {
|
|
"A (Schirmer strips)": ("A-samples", "conc (ng/ul)"),
|
|
"B (Lens tissues)": ("B-samples", "Conc [ng/ul]"),
|
|
}
|
|
|
|
print(f"{'Threshold':>10} {'Sheet':<22} {'Total':>6} {'Healthy (Exf=0)':>15} {'Diseased (Exf=1)':>16}")
|
|
print("-" * 80)
|
|
|
|
for label, (sheet_name, conc_col) in sheets.items():
|
|
df = analyse_sheet(sheet_name, conc_col)
|
|
total_samples = len(df)
|
|
n_too_low = df["conc_val"].isna().sum()
|
|
print(f"\n {label} ({total_samples} samples total, {n_too_low} 'Too low')")
|
|
print(f" {'Threshold':>10} {'Passing':>7} {'Healthy':>8} {'Diseased':>9} {'Unknown dx':>10}")
|
|
print(f" {'-'*55}")
|
|
for t in THRESHOLDS:
|
|
if t == 0:
|
|
# threshold=0: include all samples (Too low counts as passing)
|
|
passing = df.copy()
|
|
else:
|
|
# Only samples with a numeric conc >= threshold pass
|
|
passing = df[df["conc_val"] >= t]
|
|
healthy = (passing["Exf"] == 0).sum()
|
|
diseased = (passing["Exf"] == 1).sum()
|
|
unknown = passing["Exf"].isna().sum()
|
|
print(f" {t:>10.2f} {len(passing):>7} {healthy:>8} {diseased:>9} {unknown:>10}")
|
|
|
|
print()
|