convert_excel.py

import pandas as pd
import json
import numpy as np

# Load data (prioritize data.json if available, otherwise read Excel)
import os
if os.path.exists('data.json'):
    print("Loading data from data.json...")
    df = pd.read_json('data.json')
else:
    print("Loading data from IHD data entry.xlsx...")
    df = pd.read_excel('IHD data entry.xlsx')


patients = []

# Realistic names lists
male_names = ["Robert Miller", "William Davis", "James Smith", "John Johnson", "David Brown", 
              "Joseph Wilson", "Thomas Moore", "Charles Taylor", "Michael Anderson", "Richard Thomas",
              "Daniel Jackson", "Matthew White", "Anthony Harris", "Mark Martin", "Donald Thompson"]
female_names = ["Mary Smith", "Patricia Jones", "Jennifer Taylor", "Linda Brown", "Elizabeth Wilson",
                "Barbara Davis", "Susan Miller", "Jessica Moore", "Sarah Jackson", "Karen White",
                "Nancy Harris", "Lisa Martin", "Betty Thompson", "Margaret Garcia", "Sandra Martinez"]

def safe_int(val, default=0):
    if pd.isna(val):
        return default
    try:
        return int(float(val))
    except (ValueError, TypeError):
        val_str = str(val).lower().strip()
        if any(x in val_str for x in ["advised", "yes", "true", "pci", "stent", "cabg"]):
            return 1
        return default

for idx, row in df.iterrows():
    s_no = int(row['S.NO']) if not pd.isna(row['S.NO']) else idx + 1
    h_no = str(row['H.NO']).split('.')[0] if not pd.isna(row['H.NO']) else f"H-{1000 + s_no}"
    
    age = safe_int(row['Age'], 65)
    gender_code = safe_int(row['Gender'], 1)
    gender = "Male" if gender_code == 1 else "Female"
    
    # Select realistic name
    name_list = male_names if gender == "Male" else female_names
    name = name_list[(s_no - 1) % len(name_list)] + f" ({s_no})"
    
    # Diagnosis mapping (Correction of Inversion)
    # Sheet1 mapping: Unstable Angina = 1, NSTEMI = 2, STEMI = 3, Stable = 4
    # Guideline mapping: STEMI = 1, NSTEMI = 2, Unstable Angina (UA) = 3
    diag_code = row['Diagnosis']
    if pd.isna(diag_code):
        diag = 3  # Unstable Angina by default
    else:
        try:
            diag_val = int(float(diag_code))
            if diag_val == 3:
                diag = 1  # STEMI
            elif diag_val == 2:
                diag = 2  # NSTEMI
            elif diag_val == 1:
                diag = 3  # UA
            else:
                diag = 3  # Stable maps to UA/Conservative
        except (ValueError, TypeError):
            diag = 3
        
    # Troponin Assay
    # Troponin I column in Excel contains "POSITIVE" or "negative" or NaN
    trop_val = str(row['Troponin I']).lower()
    if "positive" in trop_val or diag in [1, 2]:
        troponin = 1  # Elevated/Positive
    else:
        troponin = 0  # Normal/Negative
        
    # ECG Findings
    ecg_val = row['ECG']
    if pd.isna(ecg_val):
        if diag == 1:
            ecg = 1  # ST-Elevation
        elif diag == 2:
            ecg = 2  # Dynamic ST-Depression/T-Wave Inversion
        else:
            ecg = 0  # Normal
    else:
        ecg = safe_int(ecg_val, 0)
        if ecg not in [0, 1, 2]:
            ecg = 0
        
    # Complications text
    comp = str(row['Complications']).lower() if not pd.isna(row['Complications']) else ""
    
    # Ischemic Risk Indicators (Section B)
    ischemicInstability = 1 if "shock" in comp else 0
    ischemicAngina = 1 if safe_int(row['Occurrence of Ischemic events'], 0) == 1 or "angina" in comp else 0
    ischemicHeartFailure = 1 if any(x in comp for x in ["lv dysfunction", "heart failure", "ccf", "pulmonary edema"]) else 0
    ischemicArrhythmia = 1 if any(x in comp for x in ["arrhythmia", "vt", "vf", "fibrillation", "af"]) else 0
    
    # Bleeding Risk Indicators (Section C)
    bleedingRenal = 1 if safe_int(row['Creatinine'], 0) == 2 or "nephropathy" in comp or "renal" in comp else 0
    bleedingAnemia = 1 if safe_int(row['Hb'], 1) == 0 or any(x in comp for x in ["anemia", "ida"]) else 0
    bleedingAF = 1 if "atrial fibrillation" in comp or "af" in comp or safe_int(row['DAPT Regimen'], 0) == 2 else 0
    bleedingStroke = 1 if any(x in comp for x in ["cva", "stroke", "hemorrhage", "intracranial"]) else 0

    # Continuous variables (SBP, HR, SpO2)
    # Oxygen Saturation (SpO2) from Excel O2 SATURATION (normal=0, low=1, high=2 in Sheet1)
    o2_sat_val = safe_int(row.get('O2 SATURATION'), -1)
    if o2_sat_val == 1:
        spo2 = 88  # Hypoxemic (< 90)
    elif o2_sat_val == 0 or o2_sat_val == 2:
        spo2 = 98  # Normal
    else:
        # Check if they had O2 administered (column 107)
        o2_administered = safe_int(row.get('O2'), 0)
        spo2 = 89 if o2_administered == 1 else 96 # If administered, check for hypoxemia boundary

    # Systolic Blood Pressure (SBP) and Heart Rate (HR)
    # Coded using hemodynamic instability: cardiogenic shock implies low blood pressure and elevated heart rate
    if ischemicInstability == 1:
        sbp = 85   # hypotension (<90 mmHg)
        hr = 115   # tachycardia (>100 bpm)
    else:
        sbp = 120  # normotension
        hr = 75    # normal pulse rate

    # Chest Pain Type (Typical Angina = 1, Atypical = 2, Non-anginal = 3)
    # Default to 1 (Typical Angina) for acute diagnoses, or 2 for UA/Stable
    cpType = 1 if diag in [1, 2] else 2

    # Comorbidities & Secondary Prevention factors
    comorb_val = safe_int(row.get('Comorbidities'), 0)
    risk_val = safe_int(row.get('Risk factors'), 0)
    pmh_val = safe_int(row.get('Past medical history'), 0)
    
    # Diabetes
    diabetes = 1 if (comorb_val == 1 or risk_val == 8 or pmh_val == 2 or
                     "diabetes" in comp or "dm" in comp or
                     "diabetes" in str(row.get('Past medical history', '')).lower() or
                     "diabetes" in str(row.get('Comorbidities', '')).lower()) else 0
                     
    # CKD
    ckd = 1 if (pmh_val == 6 or comorb_val == 3 or bleedingRenal == 1 or
                "ckd" in comp or "renal" in comp or "nephropathy" in comp or
                "ckd" in str(row.get('Past medical history', '')).lower()) else 0

    # Prior Stroke / TIA
    priorStroke = 1 if (pmh_val == 5 or risk_val == 6 or bleedingStroke == 1 or
                        "stroke" in comp or "cva" in comp or "hemorrhage" in comp or "intracranial" in comp or
                        "cva" in str(row.get('Past medical history', '')).lower()) else 0

    # Peptic Ulcer Disease
    pepticUlcer = 1 if any(x in comp for x in ["peptic", "pud", "ulcer", "gastric", "gerd", "gastritis"]) else 0

    # Concurrent NSAID use
    nsaid = 1 if safe_int(row.get('Analgesics'), 0) == 1 else 0

    # Minor Bleeding / Hemoglobin Drop (Rule B trigger)
    # Sheet1 mapping row 23: Type of bleeding Nil=0 Minor=1 Major=2
    minorBleeding = 1 if safe_int(row.get('Type of bleeding'), 0) == 1 else 0

    # Concurrent OAC Use
    concurrentOac = 1 if (bleedingAF == 1 or "anticoagulant" in comp or "oac" in comp or "af" in comp or
                           "atrial fibrillation" in comp or risk_val == 10 or pmh_val == 9 or
                           "af" in str(row.get('Past medical history', '')).lower() or
                           "fibrillation" in str(row.get('Past medical history', '')).lower()) else 0

    # PPI Prescribed
    # Flag as 1 if protective PPI was given (or let the audit flag gastric gap)
    # We will search the medications or complications for PPI indicators.
    other_tx = str(row.get('Other treatment', '')).lower()
    ppiPrescribed = 1 if any(x in comp or x in other_tx for x in ["ppi", "pantoprazole", "omeprazole", "rabeprazole", "pantocid"]) else 0

    # Reperfusion Strategy
    # [1] Primary PCI, [2] Fibrinolytic Therapy, [3] CABG, [4] Medical Management
    fibrinolytic_used = safe_int(row['Fibrinolytic'], 0)
    treated_cabg = safe_int(row['Treated vessel CABG'], 0)
    pci_indicator = str(row['Unnamed: 23']).lower() if not pd.isna(row['Unnamed: 23']) else ""
    
    if fibrinolytic_used == 1:
        reperfusion = 2
    elif treated_cabg == 1 or "cabg" in pci_indicator or "cabg" in comp:
        reperfusion = 3
    elif "ptca" in pci_indicator or "stent" in pci_indicator:
        reperfusion = 1
    else:
        reperfusion = 4  # Medical Management
        
    # P2Y12 Inhibitor
    # [0] None, [1] Clopidogrel, [2] Ticagrelor, [3] Prasugrel
    pasugrel_used = safe_int(row['Pasugrel'], 0)
    ticagrelor_used = safe_int(row['Ticagrelor'], 0)
    clopidogrel_used = safe_int(row['Clopidogrel'], 0)
    
    if pasugrel_used == 1:
        p2y12 = 3
    elif ticagrelor_used == 1:
        p2y12 = 2
    elif clopidogrel_used == 1:
        p2y12 = 1
    else:
        p2y12 = 0
        
    # Statin Intensity
    # [0] None, [1] Low/Moderate, [2] High-Intensity Statin
    statin_used = safe_int(row['Statin'], 0)
    if statin_used == 1:
        statin = 2 if age < 75 else 1
    else:
        statin = 0
        
    # Follow-up LDL-C (mg/dL)
    ldl_code = safe_int(row['LDL'], -1)
    if ldl_code == -1:
        ldlc = 85
    elif ldl_code == 2:
        ldlc = 110  # elevated
    elif ldl_code == 1:
        ldlc = 65   # normal
    else:
        ldlc = 45   # low

    # Extract new treatment features
    betaBlocker = safe_int(row.get('B Blockers'), 0)
    
    # RAAS inhibitor is 1 if either ACEI == 1 or ARB == 1
    acei_val = safe_int(row.get('ACEI'), 0)
    arb_val = safe_int(row.get('ARB'), 0)
    raasInhibitor = 1 if (acei_val == 1 or arb_val == 1) else 0
    
    anticoagulation = safe_int(row.get('Parenteral anticoagulation'), 0)
    transfusion = safe_int(row.get('RBC Transfusion'), 0)
    
    # Extract relative contraindications for routine invasive and/or fibrinolytics
    # Severe thrombocytopenia (PLATELET == 1 in our raw data means low)
    thrombocytopenia = 1 if (safe_int(row.get('PLATELET'), 0) == 1 or "thrombocytopenia" in comp or "platelets" in comp) else 0
    
    dementia = 1 if ("dementia" in comp or "dementia" in str(row.get('Past medical history', '')).lower()) else 0
    
    expectancy = 1 if any(x in comp or x in str(row.get('Past medical history', '')).lower() for x in ["expectancy", "cancer", "malignancy", "metastatic", "life expectancy"]) else 0
    
    arf = 1 if ("arf" in comp or "acute renal failure" in comp or "acute kidney injury" in comp or "aki" in comp) else 0

    # Combine into patient structure
    patient_data = {
        "patientId": h_no,
        "name": name,
        "age": age,
        "gender": gender,
        "weight": 70, # default weight in kg
        "diagnosis": diag,
        "troponin": troponin,
        "ecg": ecg,
        "ischemicInstability": ischemicInstability,
        "ischemicAngina": ischemicAngina,
        "ischemicHeartFailure": ischemicHeartFailure,
        "ischemicArrhythmia": ischemicArrhythmia,
        "bleedingRenal": bleedingRenal,
        "bleedingAnemia": bleedingAnemia,
        "bleedingAF": bleedingAF,
        "bleedingStroke": bleedingStroke,
        "spo2": spo2,
        "sbp": sbp,
        "hr": hr,
        "cpType": cpType,
        "diabetes": diabetes,
        "ckd": ckd,
        "priorStroke": priorStroke,
        "pepticUlcer": pepticUlcer,
        "nsaid": nsaid,
        "minorBleeding": minorBleeding,
        "concurrentOac": concurrentOac,
        "ppiPrescribed": ppiPrescribed,
        "reperfusion": reperfusion,
        "p2y12": p2y12,
        "statin": statin,
        "ldlc": ldlc,
        "betaBlocker": betaBlocker,
        "raasInhibitor": raasInhibitor,
        "anticoagulation": anticoagulation,
        "transfusion": transfusion,
        "thrombocytopenia": thrombocytopenia,
        "dementia": dementia,
        "expectancy": expectancy,
        "arf": arf,
        "complications": str(row['Complications']) if not pd.isna(row['Complications']) else "None"
    }
    patients.append(patient_data)

# Write to patients.json
with open('patients.json', 'w', encoding='utf-8') as f:
    json.dump(patients, f, indent=2)

print(f"Successfully converted {len(patients)} patients to patients.json")

# Write to patients_data.js (bypasses CORS restrictions on file:// protocols)
with open('patients_data.js', 'w', encoding='utf-8') as f:
    f.write("const initialPatientsData = ")
    json.dump(patients, f, indent=2)
    f.write(";\n")

print("Successfully generated patients_data.js")

# Generate guideline CSV
csv_records = []
for p in patients:
    # Bleeding_Risk_HBR calculation based on guidelines: age >= 75 or renal or anemia or AF or stroke
    hbr = 1 if (p["age"] >= 75 or p["bleedingRenal"] == 1 or p["bleedingAnemia"] == 1 or p["bleedingAF"] == 1 or p["bleedingStroke"] == 1) else 0
    age_group = 1 if p["age"] >= 75 else 0
    csv_records.append({
        "Patient_ID": p["patientId"],
        "Diagnosis": p["diagnosis"],
        "Age": p["age"],
        "Age_Group": age_group,
        "Weight": p["weight"],
        "SpO2": p["spo2"],
        "SBP": p["sbp"],
        "HR": p["hr"],
        "Troponin": p["troponin"],
        "ECG": p["ecg"],
        "Reperfusion_Strategy": p["reperfusion"],
        "P2Y12_Inhibitor": p["p2y12"],
        "Bleeding_Risk_HBR": hbr,
        "Statin_Intensity": p["statin"],
        "Follow_Up_LDL": p["ldlc"],
        "PPI_Prescribed": p["ppiPrescribed"],
        "Concurrent_OAC": p["concurrentOac"],
        "ischemicInstability": p["ischemicInstability"],
        "ischemicAngina": p["ischemicAngina"],
        "ischemicHeartFailure": p["ischemicHeartFailure"],
        "ischemicArrhythmia": p["ischemicArrhythmia"],
        "bleedingRenal": p["bleedingRenal"],
        "bleedingAnemia": p["bleedingAnemia"],
        "bleedingAF": p["bleedingAF"],
        "bleedingStroke": p["bleedingStroke"],
        "pepticUlcer": p["pepticUlcer"],
        "nsaid": p["nsaid"],
        "minorBleeding": p["minorBleeding"],
        "betaBlocker": p["betaBlocker"],
        "raasInhibitor": p["raasInhibitor"],
        "anticoagulation": p["anticoagulation"],
        "transfusion": p["transfusion"],
        "thrombocytopenia": p["thrombocytopenia"],
        "dementia": p["dementia"],
        "expectancy": p["expectancy"],
        "arf": p["arf"],
        "complications": p["complications"]
    })

csv_df = pd.DataFrame(csv_records)
csv_df.to_csv("IHD data entry.xlsx - IHD.csv", index=False)
print("Successfully generated IHD data entry.xlsx - IHD.csv")

print("Sample patient:")
print(json.dumps(patients[0], indent=2))