hemangioblastoma_dataset_inspection

import pandas as pd

file_path = "/Users/philippgeiger/Library/Mobile Documents/com~apple~CloudDocs/Science/Vaskulaere Projekte/Hämangioblastom/Statistik/Hemangioblastoma/Hemangioblastoma_Merged_Dataset.xlsx"

excel_file = pd.ExcelFile(file_path)
sheet_name = excel_file.sheet_names[0]
df = pd.read_excel(file_path, sheet_name=sheet_name)

print(f"Loaded sheet: {sheet_name}")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])

df.head()

import pandas as pd

with pd.ExcelFile(file_path) as workbook:
    sheet_name = workbook.sheet_names[0]
    df = pd.read_excel(workbook, sheet_name=sheet_name)

print(f"Loaded sheet: {sheet_name}")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])

df.head()

audit = pd.DataFrame({
    "dtype": df.dtypes.astype(str),
    "missing_n": df.isna().sum(),
    "missing_pct": (df.isna().mean() * 100).round(1),
    "unique_n": df.nunique(dropna=True),
    "observed_values": [
        ", ".join(map(str, sorted(df[col].dropna().unique())))[:150]
        if df[col].nunique(dropna=True) <= 12 else ""
        for col in df.columns
    ],
})

audit

validation = []

for col in df.columns:
    numeric = pd.to_numeric(df[col], errors="coerce")
    validation.append({
        "column": col,
        "missing_n": int(df[col].isna().sum()),
        "unique_values": sorted(df[col].dropna().unique().tolist())
        if df[col].nunique(dropna=True) <= 12 else f"{df[col].nunique(dropna=True)} unique values",
        "min": numeric.min() if numeric.notna().any() else None,
        "max": numeric.max() if numeric.notna().any() else None,
    })

print(f"Exact duplicate rows: {df.duplicated().sum()}")
display(pd.DataFrame(validation))

expected_codes = {
    "GenderM0F1": {0, 1},
    "TypeCystic0Solid1CysticSolid2": {0, 1, 2},
    "LocationsInfra1Supra2Spinal3": {1, 2, 3},
    "LocationInfra1NonInfra0": {0, 1},
    "GeneticsPos1Neg0": {0, 1},
    "Preop embolization": {0, 1},
    "EORComp1Part0": {0, 1},
    "Preop hydrocephalus": {0, 1},
    "Postop hydrocephalus": {0, 1},
    "VP-shunt required": {0, 1},
    "Preop mRS (0-5)": set(range(6)),
    "Outcome GOS (1-5)": set(range(1, 6)),
    "Outcome mRS (0-5/6)": set(range(7)),
    "Bad outcome (mRS>=3)": {0, 1},
    "Mortality": {0, 1},
    "Recurrence": {0, 1},
    "Re-surgery": {0, 1},
    "DepthSup1Deep2": {1, 2},
    "XADone": {0, 1},
    "SurgComp": {0, 1},
}

checks = []
for col, allowed in expected_codes.items():
    observed = set(df[col].dropna().unique())
    checks.append({
        "column": col,
        "observed_codes": sorted(observed),
        "invalid_codes": sorted(observed - allowed),
        "valid": observed.issubset(allowed),
    })

pd.DataFrame(checks)

numeric_rules = {
    "Age at surgery": (0, 120),
    "Hematocrit (%)": (10, 70),
    "BloodLossinML": (0, None),
    "Vascularization": (0, None),
}

issues = []
for col, (lower, upper) in numeric_rules.items():
    values = pd.to_numeric(df[col], errors="coerce")
    mask = values.notna() & (
        (values < lower) if upper is None else ((values < lower) | (values > upper))
    )
    issues.append({
        "column": col,
        "outside_plausible_range_n": int(mask.sum()),
        "outside_values": sorted(values[mask].unique().tolist()),
    })

mrs = df["Outcome mRS (0-5/6)"]
bad_outcome = df["Bad outcome (mRS>=3)"]
complete_cases = mrs.notna() & bad_outcome.notna()

derived_mask = complete_cases & (
    (mrs >= 3).astype("Int64") != bad_outcome.astype("Int64")
)

print(f"Bad-outcome/mRS inconsistencies: {derived_mask.sum()}")
pd.DataFrame(issues)

import pandas as pd

n_total = len(df)
rows = []

def add_categorical(variable, label, value_labels=None):
    series = df[variable]
    valid_n = series.notna().sum()
    missing_n = series.isna().sum()
    
    rows.append({
        "Characteristic": label,
        "Overall": f"n = {valid_n}",
        "Missing": missing_n,
    })
    
    counts = series.value_counts(dropna=True).sort_index()
    for value, count in counts.items():
        category = value_labels.get(value, value) if value_labels else value
        rows.append({
            "Characteristic": f"   {category}",
            "Overall": f"{count} ({count / n_total * 100:.1f}%)",
            "Missing": "",
        })

def add_continuous(variable, label, decimals=1):
    series = pd.to_numeric(df[variable], errors="coerce")
    valid_n = series.notna().sum()
    missing_n = series.isna().sum()
    rows.append({
        "Characteristic": label,
        "Overall": (
            f"{series.mean():.{decimals}f} ± {series.std():.{decimals}f} "
            f"(median {series.median():.{decimals}f}; range "
            f"{series.min():.{decimals}f}–{series.max():.{decimals}f}; n = {valid_n})"
        ),
        "Missing": missing_n,
    })

rows.append({
    "Characteristic": "Total cohort",
    "Overall": f"n = {n_total}",
    "Missing": "",
})

add_continuous("Age at surgery", "Age at surgery, years")
add_categorical("GenderM0F1", "Sex", {0: "Male", 1: "Female"})
add_categorical(
    "LocationsInfra1Supra2Spinal3",
    "Tumor location",
    {1: "Infratentorial", 2: "Supratentorial", 3: "Spinal"},
)
add_categorical(
    "TypeCystic0Solid1CysticSolid2",
    "Tumor type",
    {0: "Cystic", 1: "Solid", 2: "Cystic-solid"},
)
add_categorical("GeneticsPos1Neg0", "Genetic predisposition", {0: "Negative", 1: "Positive"})
add_categorical("Preop hydrocephalus", "Preoperative hydrocephalus", {0: "No", 1: "Yes"})
add_categorical(
    "Preop mRS (0-5)",
    "Preoperative mRS score",
    {0: "0", 1: "1", 2: "2", 3: "3", 4: "4", 5: "5"},
)

demographic_table = pd.DataFrame(rows)
demographic_table

display(
    demographic_table.style
    .set_caption("Table 1. Demographic and Baseline Clinical Characteristics")
    .set_properties(**{"text-align": "left", "padding": "6px"})
    .set_table_styles([
        {"selector": "caption", "props": [("font-size", "16px"), ("font-weight", "bold"), ("text-align", "left")]},
        {"selector": "th", "props": [("background-color", "#1f4e78"), ("color", "white"), ("font-weight", "bold")]},
        {"selector": "tr:nth-child(even)", "props": [("background-color", "#f3f6f9")]},
        {"selector": "td:nth-child(2)", "props": [("font-family", "monospace")]},
    ])
    .hide(axis="index")
)

display(demographic_table)