cd /tmp/workspace/attachments/32e1648d-3f7c-47ed-90cd-37b5f29b07a8/ && python3 -c " import pdfplumber with pdfplumber.open('Synopsis F.pdf') as pdf: print(f'Total pages: {len(pdf.pages)}') for i, page in enumerate(pdf.pages): text = page.extract_text() print(f'\n===== PAGE {i+1} =====') print(text) "

python3 -c " from pypdf import PdfReader import base64 reader = PdfReader('/tmp/workspace/attachments/32e1648d-3f7c-47ed-90cd-37b5f29b07a8/Synopsis F.pdf') print(f'Pages: {len(reader.pages)}') # Check if there are any embedded images or form fields for i, page in enumerate(reader.pages): resources = page.get('/Resources', {}) xobjects = resources.get('/XObject', {}) print(f'Page {i+1} XObjects: {list(xobjects.keys()) if xobjects else \"none\"}') annotations = page.get('/Annots', []) print(f'Page {i+1} Annotations: {len(annotations) if annotations else 0}') # Try raw text extraction raw = page.extract_text(extraction_mode='layout') if raw and raw.strip(): print(f'Page {i+1} text: {raw[:200]}') " 2>&1 | head -100

mkdir -p /tmp/workspace/synopsis_pages && python3 -c " from pypdf import PdfReader from PIL import Image import io reader = PdfReader('/tmp/workspace/attachments/32e1648d-3f7c-47ed-90cd-37b5f29b07a8/Synopsis F.pdf') for i, page in enumerate(reader.pages): resources = page['/Resources'] xobj = resources['/XObject'].get_object() for name, ref in xobj.items(): obj = ref.get_object() if obj.get('/Subtype') == '/Image': data = obj.get_data() w = obj['/Width'] h = obj['/Height'] cs = obj.get('/ColorSpace', '') bpc = obj.get('/BitsPerComponent', 8) try: # Try JPEG first filters = obj.get('/Filter', '') if '/DCTDecode' in str(filters) or 'DCTDecode' == str(filters): img = Image.open(io.BytesIO(data)) else: # Raw image data mode = 'RGB' if 'RGB' in str(cs) else 'L' img = Image.frombytes(mode, (w, h), data) out_path = f'/tmp/workspace/synopsis_pages/page_{i+1:02d}.png' img.save(out_path) print(f'Saved page {i+1}: {w}x{h} -> {out_path}') except Exception as e: print(f'Page {i+1} error: {e}') " 2>&1

python3 -c " # Use pytesseract or PIL-based OCR - check what's available import subprocess result = subprocess.run(['python3', '-c', 'import pytesseract; print(pytesseract.__version__)'], capture_output=True, text=True) print('pytesseract:', result.stdout, result.stderr) result2 = subprocess.run(['python3', '-c', 'import easyocr; print(\"easyocr ok\")'], capture_output=True, text=True) print('easyocr:', result2.stdout, result2.stderr[:100]) " 2>&1

python3 -c " # Try markitdown for PDF text extraction try: from markitdown import MarkItDown md = MarkItDown() result = md.convert('/tmp/workspace/attachments/32e1648d-3f7c-47ed-90cd-37b5f29b07a8/Synopsis F.pdf') print(result.text_content[:3000]) except Exception as e: print(f'markitdown error: {e}') " 2>&1

# Serve the images via a simple HTTP server temporarily and use analyse_media # First, let's try resizing images and saving as smaller JPEGs for analysis python3 -c " from PIL import Image import os pages_dir = '/tmp/workspace/synopsis_pages' out_dir = '/tmp/workspace/synopsis_small' os.makedirs(out_dir, exist_ok=True) for i in range(1, 16): src = f'{pages_dir}/page_{i:02d}.png' dst = f'{out_dir}/page_{i:02d}.jpg' img = Image.open(src) # Resize to 800px wide maintaining aspect ratio w, h = img.size new_w = 1200 new_h = int(h * new_w / w) img_resized = img.resize((new_w, new_h), Image.LANCZOS) img_resized.save(dst, 'JPEG', quality=85) print(f'Page {i}: {new_w}x{new_h} -> {dst} ({os.path.getsize(dst)//1024}KB)') " 2>&1

# Start a simple HTTP server in background to serve the files cd /tmp/workspace && python3 -m http.server 8765 & sleep 1 echo "Server started" curl -s -o /dev/null -w "%{http_code}" http://localhost:8765/synopsis_small/page_01.jpg

mkdir -p /tmp/workspace/eras-cs && python3 << 'EOF' import openpyxl from openpyxl.styles import ( Font, PatternFill, Alignment, Border, Side, GradientFill ) from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.styles.numbers import FORMAT_NUMBER_00 wb = openpyxl.Workbook() # ── Colour palette ────────────────────────────────────────────────────────── DARK_BLUE = "1F3864" # title bg MID_BLUE = "2E75B6" # section headers LIGHT_BLUE = "BDD7EE" # sub-headers CASES_GREEN = "E2EFDA" # case row fill CTRL_AMBER = "FFF2CC" # control row fill WHITE = "FFFFFF" GREY = "F2F2F2" ORANGE = "FF6600" RED_FILL = "FF0000" def hdr_font(bold=True, size=10, color="FFFFFF"): return Font(name="Arial", bold=bold, size=size, color=color) def cell_font(bold=False, size=10, color="000000"): return Font(name="Arial", bold=bold, size=size, color=color) def fill(hex_color): return PatternFill("solid", fgColor=hex_color) def thin_border(): s = Side(style="thin", color="AAAAAA") return Border(left=s, right=s, top=s, bottom=s) def thick_border(): s = Side(style="medium", color="2E75B6") return Border(left=s, right=s, top=s, bottom=s) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) # ════════════════════════════════════════════════════════════════════════════ # SHEET 1 – DATA COLLECTION MASTER # ════════════════════════════════════════════════════════════════════════════ ws = wb.active ws.title = "Data Collection" ws.freeze_panes = "C4" ws.sheet_view.showGridLines = True # ── Title row ──────────────────────────────────────────────────────────────── ws.merge_cells("A1:AW1") title_cell = ws["A1"] title_cell.value = "ENHANCED RECOVERY AFTER CAESAREAN SECTION (ERAS-CS) — CASE vs. CONTROL DATA COLLECTION FORM" title_cell.font = Font(name="Arial", bold=True, size=14, color="FFFFFF") title_cell.fill = fill(DARK_BLUE) title_cell.alignment = center() ws.row_dimensions[1].height = 36 ws.merge_cells("A2:AW2") sub = ws["A2"] sub.value = "CASES = ERAS Protocol Group | CONTROLS = Standard Care Group (one row per patient)" sub.font = Font(name="Arial", italic=True, size=10, color="FFFFFF") sub.fill = fill(MID_BLUE) sub.alignment = center() ws.row_dimensions[2].height = 20 # ── Section & column definitions ───────────────────────────────────────────── # Each entry: (section_label, column_header, col_width, note) COLUMNS = [ # A IDENTIFIERS ("IDENTIFIERS", "S.No", 6, ""), ("IDENTIFIERS", "Patient ID / MRN", 16, ""), ("IDENTIFIERS", "Group\n(Case/Control)", 14, "C = Case (ERAS)\nT = Control (Std)"), ("IDENTIFIERS", "Date of Surgery\n(DD/MM/YYYY)", 16, ""), ("IDENTIFIERS", "Surgeon", 16, ""), ("IDENTIFIERS", "Anaesthetist", 16, ""), # B DEMOGRAPHICS ("DEMOGRAPHICS", "Age (yrs)", 10, ""), ("DEMOGRAPHICS", "Gravida", 10, ""), ("DEMOGRAPHICS", "Para", 10, ""), ("DEMOGRAPHICS", "BMI (kg/m²)", 12, "Pre-op weight/height²"), ("DEMOGRAPHICS", "Gestational Age\n(weeks)", 14, ""), ("DEMOGRAPHICS", "Type of CS\n(Elective/Emergency)", 16, ""), ("DEMOGRAPHICS", "Previous CS\n(Yes/No)", 14, ""), ("DEMOGRAPHICS", "ASA Grade\n(I/II/III)", 12, ""), ("DEMOGRAPHICS", "Comorbidities\n(DM/HTN/Others)", 18, "List all"), # C PRE-OPERATIVE ELEMENTS ("PRE-OP ERAS ELEMENTS","Pre-op Counselling\n(Yes/No)", 16, "Patient education given"), ("PRE-OP ERAS ELEMENTS","Pre-op Carb Loading\n(Yes/No)", 16, "Carbohydrate drink ≤2h before"), ("PRE-OP ERAS ELEMENTS","Fasting Duration\n(hrs – solids)", 16, ""), ("PRE-OP ERAS ELEMENTS","Fasting Duration\n(hrs – liquids)", 16, ""), ("PRE-OP ERAS ELEMENTS","Anxiolytic\nPre-med (Yes/No)", 16, ""), ("PRE-OP ERAS ELEMENTS","Antibiotic\nProphylaxis (Yes/No)", 18, "Given before skin incision"), ("PRE-OP ERAS ELEMENTS","Antacid\nProphylaxis (Yes/No)", 16, ""), ("PRE-OP ERAS ELEMENTS","Compression\nStockings (Yes/No)", 16, "DVT prophylaxis"), ("PRE-OP ERAS ELEMENTS","LMWH Pre-op\n(Yes/No/Dose)", 16, ""), # D INTRA-OPERATIVE ("INTRA-OP", "Anaesthesia Type\n(SAB/CSE/GA)", 16, ""), ("INTRA-OP", "Spinal Opioid\nDose (µg)", 14, "Intrathecal morphine/fentanyl"), ("INTRA-OP", "IV Fluid Volume\n(mL)", 14, "Total intra-op"), ("INTRA-OP", "Vasopressor Used\n(Yes/No/Agent)", 18, "e.g. Phenylephrine/Ephedrine"), ("INTRA-OP", "Skin-to-Delivery\nTime (min)", 16, ""), ("INTRA-OP", "Total Op Time\n(min)", 14, ""), ("INTRA-OP", "EBL (mL)", 12, "Estimated Blood Loss"), ("INTRA-OP", "Uterotonic Used\n(Oxytocin dose IU)", 18, ""), ("INTRA-OP", "Warming\n(Yes/No)", 12, "Active patient warming"), ("INTRA-OP", "Nausea/Vomiting\nIntra-op (Yes/No)", 18, ""), # E POST-OPERATIVE ("POST-OP", "Time to First\nOral Fluids (hrs)", 18, "From end of surgery"), ("POST-OP", "Time to First\nSolid Food (hrs)", 18, ""), ("POST-OP", "Time to First\nAmbulation (hrs)", 18, ""), ("POST-OP", "Urinary Catheter\nRemoval (hrs)", 18, "Hours post-op"), ("POST-OP", "IV Fluid Duration\n(hrs)", 16, "Total hours of IV fluids"), ("POST-OP", "Post-op Nausea/\nVomiting (Yes/No)", 18, ""), ("POST-OP", "Post-op Shivering\n(Yes/No)", 16, ""), # F PAIN ("PAIN & ANALGESIA", "NRS Pain Score\n@ 2h (0–10)", 16, "Numeric Rating Scale"), ("PAIN & ANALGESIA", "NRS Pain Score\n@ 6h (0–10)", 16, ""), ("PAIN & ANALGESIA", "NRS Pain Score\n@ 12h (0–10)", 16, ""), ("PAIN & ANALGESIA", "NRS Pain Score\n@ 24h (0–10)", 16, ""), ("PAIN & ANALGESIA", "First Rescue\nAnalgesia (hrs)", 16, "Time to first opioid/NSAID rescue"), ("PAIN & ANALGESIA", "Total Opioid\nConsumption (mg morphine eq)",22, "24-hr total"), ("PAIN & ANALGESIA", "Multimodal Analgesia\nUsed (Yes/No)", 18, "Paracetamol+NSAID+opioid"), # G OUTCOMES ("PRIMARY OUTCOMES", "Length of Hospital\nStay (hrs)", 18, "Admission to discharge"), ("PRIMARY OUTCOMES", "Time to Discharge\nReadiness (hrs)", 20, "Medically fit to go"), ("PRIMARY OUTCOMES", "Breastfeeding\nInitiated (Yes/No)", 18, "Within 1h of delivery"), ("PRIMARY OUTCOMES", "Mother–Baby\nBonding Score", 18, "Kangaroo care / skin-to-skin (0–10)"), # H COMPLICATIONS ("COMPLICATIONS", "Wound Infection\n(Yes/No/Grade)", 18, "CDC SSI criteria"), ("COMPLICATIONS", "Haemorrhage\n(>1000 mL – Yes/No)", 18, ""), ("COMPLICATIONS", "Urinary\nRetention (Yes/No)", 16, ""), ("COMPLICATIONS", "DVT / PE\n(Yes/No)", 14, ""), ("COMPLICATIONS", "Readmission\nwithin 30 days (Yes/No)", 20, ""), ("COMPLICATIONS", "ICU Admission\n(Yes/No)", 16, ""), ("COMPLICATIONS", "Other Complications\n(specify)", 22, ""), # I NEONATAL ("NEONATAL", "Birth Weight (g)", 14, ""), ("NEONATAL", "APGAR @ 1 min", 14, ""), ("NEONATAL", "APGAR @ 5 min", 14, ""), ("NEONATAL", "NICU Admission\n(Yes/No)", 16, ""), ("NEONATAL", "Neonatal Outcome\n(Good/Fair/Poor)", 18, ""), # J SATISFACTION ("SATISFACTION", "Patient Satisfaction\nScore (1–10)", 20, "At discharge"), ("SATISFACTION", "Remarks / Notes", 30, ""), ] # ── Write section header row (row 3) ───────────────────────────────────────── ws.row_dimensions[3].height = 22 ws.row_dimensions[4].height = 50 # column headers SECTION_COLORS = { "IDENTIFIERS": "1F3864", "DEMOGRAPHICS": "2E75B6", "PRE-OP ERAS ELEMENTS": "375623", "INTRA-OP": "7030A0", "POST-OP": "833C00", "PAIN & ANALGESIA": "C00000", "PRIMARY OUTCOMES": "0070C0", "COMPLICATIONS": "FF0000", "NEONATAL": "7030A0", "SATISFACTION": "375623", } # Group columns by section from itertools import groupby col_sections = {} for idx, (sec, hdr, wid, note) in enumerate(COLUMNS, start=1): if sec not in col_sections: col_sections[sec] = [] col_sections[sec].append(idx) # Write section merges in row 3 current_col = 1 section_starts = {} for sec, hdr, wid, note in COLUMNS: if sec not in section_starts: section_starts[sec] = current_col current_col += 1 # Rebuild section ranges sec_ranges = {} current_col = 1 prev_sec = None sec_start = 1 for i, (sec, hdr, wid, note) in enumerate(COLUMNS): col = i + 1 if sec != prev_sec: if prev_sec is not None: sec_ranges[prev_sec] = (sec_start, col - 1) prev_sec = sec sec_start = col sec_ranges[prev_sec] = (sec_start, len(COLUMNS)) for sec, (c1, c2) in sec_ranges.items(): l1, l2 = get_column_letter(c1), get_column_letter(c2) if c1 != c2: ws.merge_cells(f"{l1}3:{l2}3") cell = ws[f"{l1}3"] cell.value = sec cell.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") cell.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) cell.alignment = center() bd = Side(style="medium", color="FFFFFF") cell.border = Border(left=bd, right=bd, top=bd, bottom=bd) # ── Write column headers (row 4) ───────────────────────────────────────────── for i, (sec, hdr, wid, note) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}4"] c.value = hdr c.font = Font(name="Arial", bold=True, size=9, color="FFFFFF") c.fill = fill(LIGHT_BLUE if sec in ("IDENTIFIERS","DEMOGRAPHICS") else SECTION_COLORS.get(sec,"2E75B6")) c.font = Font(name="Arial", bold=True, size=9, color="FFFFFF") c.alignment = center() s = Side(style="thin", color="FFFFFF") c.border = Border(left=s, right=s, top=s, bottom=s) ws.column_dimensions[cl].width = wid ws.row_dimensions[4].height = 52 # ── Pre-populate 40 Case rows + 40 Control rows (rows 5–84, 85–124) ───────── def make_row(row_num, group_label, fill_color): for i, (sec, hdr, wid, note) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}{row_num}"] c.fill = fill(fill_color) c.font = cell_font() c.alignment = left() tb = Side(style="thin", color="CCCCCC") c.border = Border(left=tb, right=tb, top=tb, bottom=tb) # Serial number ws[f"A{row_num}"].value = row_num - 4 ws[f"A{row_num}"].alignment = center() # Group label pre-filled ws[f"C{row_num}"].value = group_label ws[f"C{row_num}"].alignment = center() ws[f"C{row_num}"].font = Font(name="Arial", bold=True, size=10, color="1F5C1A" if group_label=="Case (ERAS)" else "7B3F00") # Cases: rows 5–44 for r in range(5, 45): make_row(r, "Case (ERAS)", CASES_GREEN) # Controls: rows 45–84 for r in range(45, 85): make_row(r, "Control (Std)", CTRL_AMBER) # Serial numbers correction (cases 1–40, controls 1–40 with prefix) for r in range(5, 45): ws[f"A{r}"].value = r - 4 for r in range(45, 85): ws[f"A{r}"].value = r - 44 # ── Data validations ───────────────────────────────────────────────────────── def add_dv(ws, formula, sqref): dv = DataValidation(type="list", formula1=formula, allow_blank=True) dv.sqref = sqref ws.add_data_validation(dv) rows_range = "5:84" add_dv(ws, '"Case (ERAS),Control (Std)"', f"C{rows_range}") add_dv(ws, '"Elective,Emergency"', f"M{rows_range}") add_dv(ws, '"Yes,No"', f"N{rows_range}") add_dv(ws, '"I,II,III"', f"O{rows_range}") add_dv(ws, '"Yes,No"', f"P{rows_range}") add_dv(ws, '"Yes,No"', f"Q{rows_range}") add_dv(ws, '"Yes,No"', f"T{rows_range}") add_dv(ws, '"Yes,No"', f"U{rows_range}") add_dv(ws, '"Yes,No"', f"V{rows_range}") add_dv(ws, '"Yes,No"', f"W{rows_range}") add_dv(ws, '"Yes,No,Dose"', f"X{rows_range}") add_dv(ws, '"SAB,CSE,GA"', f"Y{rows_range}") add_dv(ws, '"Yes,No"', f"AD{rows_range}") add_dv(ws, '"Yes,No"', f"AE{rows_range}") add_dv(ws, '"Yes,No"', f"AH{rows_range}") add_dv(ws, '"Yes,No"', f"AI{rows_range}") add_dv(ws, '"Yes,No"', f"AJ{rows_range}") add_dv(ws, '"Yes,No"', f"AK{rows_range}") add_dv(ws, '"Yes,No"', f"AL{rows_range}") add_dv(ws, '"Yes,No"', f"AM{rows_range}") add_dv(ws, '"Yes,No"', f"AQ{rows_range}") add_dv(ws, '"Yes,No"', f"AR{rows_range}") add_dv(ws, '"Yes,No"', f"AS{rows_range}") add_dv(ws, '"Yes,No"', f"AT{rows_range}") add_dv(ws, '"Yes,No"', f"AU{rows_range}") add_dv(ws, '"Yes,No"', f"AV{rows_range}") add_dv(ws, '"Yes,No"', f"AX{rows_range}") add_dv(ws, '"Yes,No"', f"AY{rows_range}") add_dv(ws, '"Yes,No"', f"AZ{rows_range}") add_dv(ws, '"Yes,No"', f"BA{rows_range}") add_dv(ws, '"Yes,No"', f"BB{rows_range}") add_dv(ws, '"Good,Fair,Poor"', f"BC{rows_range}") add_dv(ws, '"Yes,No"', f"AT{rows_range}") # ════════════════════════════════════════════════════════════════════════════ # SHEET 2 – SUMMARY STATISTICS # ════════════════════════════════════════════════════════════════════════════ ws2 = wb.create_sheet("Summary Statistics") ws2.sheet_view.showGridLines = True ws2.freeze_panes = "B4" ws2.merge_cells("A1:F1") t = ws2["A1"] t.value = "ERAS-CS STUDY — SUMMARY STATISTICS" t.font = Font(name="Arial", bold=True, size=14, color="FFFFFF") t.fill = fill(DARK_BLUE) t.alignment = center() ws2.row_dimensions[1].height = 32 ws2.merge_cells("A2:F2") s = ws2["A2"] s.value = "Auto-calculated from Data Collection sheet (Rows 5–44 = Cases, Rows 45–84 = Controls)" s.font = Font(name="Arial", italic=True, size=10, color="FFFFFF") s.fill = fill(MID_BLUE) s.alignment = center() ws2.row_dimensions[2].height = 18 headers_s = ["Parameter", "Cases (ERAS)\nn=40", "Controls (Std Care)\nn=40", "Difference\n(Case – Control)", "Unit / Scale", "Notes"] col_widths_s = [35, 22, 22, 22, 18, 30] for i, (h, w) in enumerate(zip(headers_s, col_widths_s), start=1): cl = get_column_letter(i) c = ws2[f"{cl}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() s_bd = Side(style="thin", color="FFFFFF") c.border = Border(left=s_bd, right=s_bd, top=s_bd, bottom=s_bd) ws2.column_dimensions[cl].width = w ws2.row_dimensions[3].height = 40 summary_params = [ # (label, case_formula, ctrl_formula, unit) ("Age (mean ± SD)", "=AVERAGE('Data Collection'!G5:G44)", "=AVERAGE('Data Collection'!G45:G84)", "Years"), ("BMI (mean)", "=AVERAGE('Data Collection'!J5:J44)", "=AVERAGE('Data Collection'!J45:J84)", "kg/m²"), ("Gestational Age (mean)", "=AVERAGE('Data Collection'!K5:K44)", "=AVERAGE('Data Collection'!K45:K84)", "Weeks"), ("Elective CS (%)", '=COUNTIF(\'Data Collection\'!M5:M44,"Elective")/40*100', '=COUNTIF(\'Data Collection\'!M45:M84,"Elective")/40*100', "%"), ("Pre-op Counselling (%)", '=COUNTIF(\'Data Collection\'!P5:P44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!P45:P84,"Yes")/40*100', "%"), ("Carbohydrate Loading (%)", '=COUNTIF(\'Data Collection\'!Q5:Q44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!Q45:Q84,"Yes")/40*100', "%"), ("Fasting Duration – Solids (mean hrs)", "=AVERAGE('Data Collection'!R5:R44)", "=AVERAGE('Data Collection'!R45:R84)", "Hours"), ("Fasting Duration – Liquids (mean hrs)", "=AVERAGE('Data Collection'!S5:S44)", "=AVERAGE('Data Collection'!S45:S84)", "Hours"), ("IV Fluid Volume Intra-op (mean mL)", "=AVERAGE('Data Collection'!AB5:AB44)", "=AVERAGE('Data Collection'!AB45:AB84)", "mL"), ("EBL (mean mL)", "=AVERAGE('Data Collection'!AE5:AE44)", "=AVERAGE('Data Collection'!AE45:AE84)", "mL"), ("Total Op Time (mean min)", "=AVERAGE('Data Collection'!AD5:AD44)", "=AVERAGE('Data Collection'!AD45:AD84)", "Minutes"), ("Time to First Oral Fluids (mean hrs)", "=AVERAGE('Data Collection'!AH5:AH44)", "=AVERAGE('Data Collection'!AH45:AH84)", "Hours"), ("Time to First Solid Food (mean hrs)", "=AVERAGE('Data Collection'!AI5:AI44)", "=AVERAGE('Data Collection'!AI45:AI84)", "Hours"), ("Time to First Ambulation (mean hrs)", "=AVERAGE('Data Collection'!AJ5:AJ44)", "=AVERAGE('Data Collection'!AJ45:AJ84)", "Hours"), ("Catheter Removal Time (mean hrs)", "=AVERAGE('Data Collection'!AK5:AK44)", "=AVERAGE('Data Collection'!AK45:AK84)", "Hours"), ("NRS Pain @ 2h (mean)", "=AVERAGE('Data Collection'!AN5:AN44)", "=AVERAGE('Data Collection'!AN45:AN84)", "Score 0–10"), ("NRS Pain @ 6h (mean)", "=AVERAGE('Data Collection'!AO5:AO44)", "=AVERAGE('Data Collection'!AO45:AO84)", "Score 0–10"), ("NRS Pain @ 12h (mean)", "=AVERAGE('Data Collection'!AP5:AP44)", "=AVERAGE('Data Collection'!AP45:AP84)", "Score 0–10"), ("NRS Pain @ 24h (mean)", "=AVERAGE('Data Collection'!AQ5:AQ44)", "=AVERAGE('Data Collection'!AQ45:AQ84)", "Score 0–10"), ("First Rescue Analgesia Time (mean hrs)", "=AVERAGE('Data Collection'!AR5:AR44)", "=AVERAGE('Data Collection'!AR45:AR84)", "Hours"), ("Total Opioid Consumption (mean mg ME)", "=AVERAGE('Data Collection'!AS5:AS44)", "=AVERAGE('Data Collection'!AS45:AS84)", "mg morphine equiv."), ("Length of Hospital Stay (mean hrs)", "=AVERAGE('Data Collection'!AU5:AU44)", "=AVERAGE('Data Collection'!AU45:AU84)", "Hours"), ("Breastfeeding Initiated (%)", '=COUNTIF(\'Data Collection\'!AW5:AW44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!AW45:AW84,"Yes")/40*100', "%"), ("Wound Infection (%)", '=COUNTIF(\'Data Collection\'!AY5:AY44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!AY45:AY84,"Yes")/40*100', "%"), ("PPH > 1000 mL (%)", '=COUNTIF(\'Data Collection\'!AZ5:AZ44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!AZ45:AZ84,"Yes")/40*100', "%"), ("Readmission within 30 days (%)", '=COUNTIF(\'Data Collection\'!BB5:BB44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!BB45:BB84,"Yes")/40*100', "%"), ("NICU Admission (%)", '=COUNTIF(\'Data Collection\'!BE5:BE44,"Yes")/40*100', '=COUNTIF(\'Data Collection\'!BE45:BE84,"Yes")/40*100', "%"), ("APGAR @ 1 min (mean)", "=AVERAGE('Data Collection'!BC5:BC44)", "=AVERAGE('Data Collection'!BC45:BC84)", "Score 0–10"), ("APGAR @ 5 min (mean)", "=AVERAGE('Data Collection'!BD5:BD44)", "=AVERAGE('Data Collection'!BD45:BD84)", "Score 0–10"), ("Patient Satisfaction Score (mean)", "=AVERAGE('Data Collection'!BG5:BG44)", "=AVERAGE('Data Collection'!BG45:BG84)", "Score 1–10"), ] row_fill_s = [CASES_GREEN, CTRL_AMBER] for row_i, (label, case_f, ctrl_f, unit) in enumerate(summary_params, start=4): alt_fill = GREY if row_i % 2 == 0 else WHITE ws2[f"A{row_i}"].value = label ws2[f"B{row_i}"].value = case_f ws2[f"C{row_i}"].value = ctrl_f ws2[f"D{row_i}"].value = f"=B{row_i}-C{row_i}" ws2[f"E{row_i}"].value = unit ws2[f"F{row_i}"].value = "" for col in ["A","B","C","D","E","F"]: c = ws2[f"{col}{row_i}"] c.fill = fill(CASES_GREEN if col in ("A","E","F") else (CASES_GREEN if col=="B" else (CTRL_AMBER if col=="C" else WHITE))) c.font = cell_font() c.alignment = left() if col in ("A","E","F") else center() tb = Side(style="thin", color="CCCCCC") c.border = Border(left=tb, right=tb, top=tb, bottom=tb) # Bold difference if large ws2[f"D{row_i}"].font = Font(name="Arial", bold=True, size=10, color="C00000") ws2.row_dimensions[row_i].height = 18 # ════════════════════════════════════════════════════════════════════════════ # SHEET 3 – ERAS PROTOCOL CHECKLIST # ════════════════════════════════════════════════════════════════════════════ ws3 = wb.create_sheet("ERAS Protocol Checklist") ws3.merge_cells("A1:D1") tc = ws3["A1"] tc.value = "ERAS-CS PROTOCOL COMPLIANCE CHECKLIST" tc.font = Font(name="Arial", bold=True, size=13, color="FFFFFF") tc.fill = fill(DARK_BLUE) tc.alignment = center() ws3.row_dimensions[1].height = 30 ws3.merge_cells("A2:D2") sc = ws3["A2"] sc.value = "Tick each element applied per ERAS Society / RCOG Guidelines" sc.font = Font(name="Arial", italic=True, size=10, color="FFFFFF") sc.fill = fill(MID_BLUE) sc.alignment = center() ws3.row_dimensions[2].height = 18 chk_headers = ["Phase", "ERAS Element", "Applied\n(Yes/No/NA)", "Remarks"] chk_widths = [20, 52, 18, 40] for i, (h, w) in enumerate(zip(chk_headers, chk_widths), start=1): cl = get_column_letter(i) c = ws3[f"{cl}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() ws3.column_dimensions[cl].width = w s_bd = Side(style="thin", color="FFFFFF") c.border = Border(left=s_bd, right=s_bd, top=s_bd, bottom=s_bd) ws3.row_dimensions[3].height = 36 checklist = [ ("Pre-operative", "Patient education and counselling (written + verbal)"), ("Pre-operative", "Carbohydrate loading (oral CHO drink 2 hrs pre-op)"), ("Pre-operative", "Minimum fasting: 6h solids / 2h clear fluids"), ("Pre-operative", "Antibiotic prophylaxis given BEFORE skin incision"), ("Pre-operative", "Antacid prophylaxis (ranitidine/omeprazole)"), ("Pre-operative", "Anxiolytic pre-medication avoided/minimised"), ("Pre-operative", "DVT prophylaxis: compression stockings ± LMWH"), ("Pre-operative", "Anti-emetic prophylaxis given"), ("Intra-operative","Spinal anaesthesia preferred (SAB or CSE)"), ("Intra-operative","Intrathecal opioid (morphine/fentanyl) used"), ("Intra-operative","Active patient warming (warmed IV fluids, blanket)"), ("Intra-operative","Vasopressor infusion for spinal hypotension prevention"), ("Intra-operative","Goal-directed IV fluid therapy (avoid over-hydration)"), ("Intra-operative","Oxytocin infusion (slow IV, not bolus) to reduce PONV"), ("Intra-operative","Uterine exteriorisation avoided"), ("Intra-operative","Intra-op PONV prophylaxis (dexamethasone ± ondansetron)"), ("Intra-operative","Skin-to-skin contact / early bonding facilitated"), ("Post-operative", "Early oral hydration (<30 min post-op if stable)"), ("Post-operative", "Early feeding – solid food within 2h post-op"), ("Post-operative", "Multimodal analgesia (paracetamol + NSAID + low opioid)"), ("Post-operative", "Urinary catheter removed within 12–24h post-op"), ("Post-operative", "Early mobilisation (<8h post-op)"), ("Post-operative", "Incentive spirometry / deep breathing exercises"), ("Post-operative", "Thromboprophylaxis continued post-op"), ("Post-operative", "Breastfeeding support and lactation counselling"), ("Post-operative", "Wound care and inspection before discharge"), ("Post-operative", "Structured discharge criteria checklist used"), ("Post-operative", "Post-discharge follow-up planned (24–72h call)"), ] phase_colors = {"Pre-operative": "E2EFDA", "Intra-operative": "DEEAF1", "Post-operative": "FFF2CC"} for row_i, (phase, element) in enumerate(checklist, start=4): fc = phase_colors.get(phase, WHITE) ws3[f"A{row_i}"].value = phase ws3[f"B{row_i}"].value = element ws3[f"C{row_i}"].value = "" ws3[f"D{row_i}"].value = "" for col in ["A","B","C","D"]: c = ws3[f"{col}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col=="A")) c.alignment = left() tb = Side(style="thin", color="CCCCCC") c.border = Border(left=tb, right=tb, top=tb, bottom=tb) ws3.row_dimensions[row_i].height = 18 # Data validation for Applied column dv3 = DataValidation(type="list", formula1='"Yes,No,NA"', allow_blank=True) ws3.add_data_validation(dv3) dv3.sqref = f"C{row_i}" # ════════════════════════════════════════════════════════════════════════════ # SHEET 4 – LEGEND / CODEBOOK # ════════════════════════════════════════════════════════════════════════════ ws4 = wb.create_sheet("Legend & Codebook") ws4.merge_cells("A1:C1") lc = ws4["A1"] lc.value = "LEGEND & VARIABLE CODEBOOK — ERAS-CS STUDY" lc.font = Font(name="Arial", bold=True, size=13, color="FFFFFF") lc.fill = fill(DARK_BLUE) lc.alignment = center() ws4.row_dimensions[1].height = 30 ws4.column_dimensions["A"].width = 35 ws4.column_dimensions["B"].width = 22 ws4.column_dimensions["C"].width = 55 for i, h in enumerate(["Variable / Column", "Allowed Values", "Definition / Notes"], start=1): c = ws4[f"{get_column_letter(i)}2"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() ws4.row_dimensions[2].height = 22 legend_rows = [ ("Group", "Case (ERAS) / Control (Std)", "Cases received full ERAS protocol; Controls received standard perioperative care"), ("ASA Grade", "I / II / III", "American Society of Anaesthesiologists physical status classification"), ("Type of CS", "Elective / Emergency", "Scheduled vs. unscheduled caesarean section"), ("BMI", "Numeric kg/m²", "Pre-operative body mass index"), ("NRS Pain Score", "0–10", "Numeric Rating Scale: 0=no pain, 10=worst imaginable pain"), ("EBL", "Numeric mL", "Estimated blood loss measured by swab weighing + suction"), ("Anaesthesia Type", "SAB / CSE / GA", "Spinal Anaesthesia Block / Combined Spinal-Epidural / General Anaesthesia"), ("APGAR Score", "0–10", "Assessed at 1 min and 5 min by attending paediatrician"), ("Length of Stay", "Numeric hours", "Time from end of surgery to actual discharge"), ("Discharge Readiness", "Numeric hours", "Time from end of surgery to meeting all discharge criteria"), ("Breastfeeding", "Yes / No", "Skin-to-skin contact and first latch within 1 hour of delivery"), ("SSI / Wound Inf.", "Yes / No / Grade", "Surgical Site Infection per CDC criteria (superficial/deep/organ-space)"), ("Readmission", "Yes / No", "Any unplanned hospital readmission within 30 days of discharge"), ("DVT / PE", "Yes / No", "Deep Vein Thrombosis or Pulmonary Embolism confirmed by imaging"), ("Opioid Consumption", "Numeric mg ME", "Total 24-h opioid in morphine equivalent (ME) milligrams"), ("Satisfaction Score", "1–10", "Validated patient satisfaction questionnaire score at discharge"), ("Neonatal Outcome", "Good / Fair / Poor", "Good=no NICU, normal APGAR; Fair=brief NICU obs; Poor=prolonged NICU/adverse event"), ("Multimodal Analgesia","Yes / No", "Combination of ≥2 analgesic classes (paracetamol+NSAID+low-dose opioid)"), ("PONV", "Yes / No", "Post-Operative Nausea and Vomiting (any episode requiring treatment)"), ("LMWH", "Yes / No / Dose", "Low Molecular Weight Heparin thromboprophylaxis"), ] for row_i, (var, vals, defn) in enumerate(legend_rows, start=3): fc = GREY if row_i % 2 == 0 else WHITE ws4[f"A{row_i}"].value = var ws4[f"B{row_i}"].value = vals ws4[f"C{row_i}"].value = defn for col in ["A","B","C"]: c = ws4[f"{col}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col=="A")) c.alignment = left() tb = Side(style="thin", color="CCCCCC") c.border = Border(left=tb, right=tb, top=tb, bottom=tb) ws4.row_dimensions[row_i].height = 20 # ── Save ────────────────────────────────────────────────────────────────────── out = "/tmp/workspace/eras-cs/ERAS_CS_DataCollection.xlsx" wb.save(out) print(f"Saved: {out}") import os print(f"File size: {os.path.getsize(out)//1024} KB") EOF

python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.Workbook() DARK_BLUE = "1F3864" MID_BLUE = "2E75B6" LIGHT_BLUE = "BDD7EE" CASES_GREEN = "E2EFDA" CTRL_AMBER = "FFF2CC" WHITE = "FFFFFF" GREY = "F2F2F2" def hdr_font(bold=True, size=10, color="FFFFFF"): return Font(name="Arial", bold=bold, size=size, color=color) def cell_font(bold=False, size=10, color="000000"): return Font(name="Arial", bold=bold, size=size, color=color) def fill(hex_color): return PatternFill("solid", fgColor=hex_color) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) def thin_border(): s = Side(style="thin", color="CCCCCC") return Border(left=s, right=s, top=s, bottom=s) def thick_side(): s = Side(style="medium", color="FFFFFF") return Border(left=s, right=s, top=s, bottom=s) # ════════════════════════════════════════════════════════════════════════════ # SHEET 1 – DATA COLLECTION # ════════════════════════════════════════════════════════════════════════════ ws = wb.active ws.title = "Data Collection" ws.freeze_panes = "C4" ws.merge_cells("A1:BH1") ws["A1"].value = "ENHANCED RECOVERY AFTER CAESAREAN SECTION (ERAS-CS) — CASE vs. CONTROL DATA COLLECTION FORM" ws["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws["A1"].fill = fill(DARK_BLUE) ws["A1"].alignment = center() ws.row_dimensions[1].height = 36 ws.merge_cells("A2:BH2") ws["A2"].value = "CASES = ERAS Protocol Group | CONTROLS = Standard Care Group (one row per patient)" ws["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws["A2"].fill = fill(MID_BLUE) ws["A2"].alignment = center() ws.row_dimensions[2].height = 20 SECTION_COLORS = { "IDENTIFIERS": "1F3864", "DEMOGRAPHICS": "2E75B6", "PRE-OP ERAS": "375623", "INTRA-OP": "7030A0", "POST-OP": "843C0C", "PAIN & ANALGESIA": "C00000", "PRIMARY OUTCOMES": "0070C0", "COMPLICATIONS": "833C00", "NEONATAL": "403152", "SATISFACTION": "375623", } COLUMNS = [ # sec, header, width ("IDENTIFIERS", "S.No", 6), ("IDENTIFIERS", "Patient\nID / MRN", 16), ("IDENTIFIERS", "Group\n(Case/Control)", 16), ("IDENTIFIERS", "Date of\nSurgery", 14), ("IDENTIFIERS", "Surgeon", 16), ("IDENTIFIERS", "Anaesthetist", 16), ("DEMOGRAPHICS", "Age\n(yrs)", 10), ("DEMOGRAPHICS", "Gravida", 10), ("DEMOGRAPHICS", "Para", 10), ("DEMOGRAPHICS", "BMI\n(kg/m²)", 12), ("DEMOGRAPHICS", "Gestational\nAge (wks)", 14), ("DEMOGRAPHICS", "Type of CS\n(Elective/\nEmergency)", 16), ("DEMOGRAPHICS", "Previous CS\n(Yes/No)", 14), ("DEMOGRAPHICS", "ASA\nGrade", 12), ("DEMOGRAPHICS", "Comorbidities", 20), ("PRE-OP ERAS", "Pre-op\nCounselling\n(Yes/No)", 16), ("PRE-OP ERAS", "Carb\nLoading\n(Yes/No)", 14), ("PRE-OP ERAS", "Fasting-\nSolids\n(hrs)", 12), ("PRE-OP ERAS", "Fasting-\nLiquids\n(hrs)", 12), ("PRE-OP ERAS", "Anxiolytic\nPre-med\n(Yes/No)", 14), ("PRE-OP ERAS", "Antibiotic\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Antacid\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Compression\nStockings\n(Yes/No)", 14), ("PRE-OP ERAS", "LMWH\n(Yes/No/\nDose)", 14), ("PRE-OP ERAS", "Anti-emetic\nProphylaxis\n(Yes/No)", 14), ("INTRA-OP", "Anaesthesia\n(SAB/CSE/GA)", 16), ("INTRA-OP", "Intrathecal\nOpioid\nDose (µg)", 14), ("INTRA-OP", "IV Fluids\nIntra-op\n(mL)", 14), ("INTRA-OP", "Vasopressor\n(Yes/No/\nAgent)", 16), ("INTRA-OP", "Skin-to-\nDelivery\nTime (min)", 14), ("INTRA-OP", "Total Op\nTime (min)", 14), ("INTRA-OP", "EBL\n(mL)", 12), ("INTRA-OP", "Oxytocin\nDose (IU)", 12), ("INTRA-OP", "Active\nWarming\n(Yes/No)", 14), ("INTRA-OP", "Intra-op\nNausea/\nVomiting", 14), ("POST-OP", "Time to\nOral Fluids\n(hrs)", 14), ("POST-OP", "Time to\nSolid Food\n(hrs)", 14), ("POST-OP", "Time to\nAmbulation\n(hrs)", 14), ("POST-OP", "Catheter\nRemoval\n(hrs)", 14), ("POST-OP", "IV Fluid\nDuration\n(hrs)", 14), ("POST-OP", "Post-op\nNausea/\nVomiting", 14), ("POST-OP", "Post-op\nShivering\n(Yes/No)", 14), ("PAIN & ANALGESIA", "NRS Pain\n@ 2h\n(0–10)", 14), ("PAIN & ANALGESIA", "NRS Pain\n@ 6h\n(0–10)", 14), ("PAIN & ANALGESIA", "NRS Pain\n@ 12h\n(0–10)", 14), ("PAIN & ANALGESIA", "NRS Pain\n@ 24h\n(0–10)", 14), ("PAIN & ANALGESIA", "1st Rescue\nAnalgesia\n(hrs)", 14), ("PAIN & ANALGESIA", "Total Opioid\nConsumption\n(mg ME/24h)", 18), ("PAIN & ANALGESIA", "Multimodal\nAnalgesia\n(Yes/No)", 14), ("PRIMARY OUTCOMES", "Length of\nHospital\nStay (hrs)", 16), ("PRIMARY OUTCOMES", "Discharge\nReadiness\n(hrs)", 16), ("PRIMARY OUTCOMES", "Breastfeeding\nInitiated\n(Yes/No)", 16), ("PRIMARY OUTCOMES", "Mother-Baby\nBonding\nScore (0–10)", 16), ("COMPLICATIONS","Wound\nInfection\n(Yes/No)", 16), ("COMPLICATIONS","PPH >1000mL\n(Yes/No)", 14), ("COMPLICATIONS","Urinary\nRetention\n(Yes/No)", 14), ("COMPLICATIONS","DVT/PE\n(Yes/No)", 14), ("COMPLICATIONS","Readmission\n≤30 days\n(Yes/No)", 16), ("COMPLICATIONS","ICU\nAdmission\n(Yes/No)", 14), ("COMPLICATIONS","Other\nComplications", 22), ("NEONATAL", "Birth\nWeight (g)", 14), ("NEONATAL", "APGAR\n@ 1 min", 12), ("NEONATAL", "APGAR\n@ 5 min", 12), ("NEONATAL", "NICU\nAdmission\n(Yes/No)", 14), ("NEONATAL", "Neonatal\nOutcome", 16), ("SATISFACTION", "Patient\nSatisfaction\n(1–10)", 16), ("SATISFACTION", "Remarks /\nNotes", 30), ] # Section boundary calculation sec_ranges = {} prev = None st = 1 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): if sec != prev: if prev: sec_ranges[prev] = (st, i-1) prev = sec st = i sec_ranges[prev] = (st, len(COLUMNS)) # Row 3: section headers ws.row_dimensions[3].height = 22 for sec, (c1, c2) in sec_ranges.items(): l1, l2 = get_column_letter(c1), get_column_letter(c2) if c1 != c2: ws.merge_cells(f"{l1}3:{l2}3") c = ws[f"{l1}3"] c.value = sec c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="medium", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) # Row 4: column headers ws.row_dimensions[4].height = 52 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}4"] c.value = hdr c.font = Font(name="Arial", bold=True, size=9, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws.column_dimensions[cl].width = wid # Data rows: 5–44 Cases, 45–84 Controls def make_row(r, group, fc): for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}{r}"] c.fill = fill(fc) c.font = cell_font() c.alignment = center() if i == 1 else left() c.border = thin_border() ws[f"A{r}"].value = r - 4 if r <= 44 else r - 44 ws[f"C{r}"].value = group ws[f"C{r}"].font = Font(name="Arial", bold=True, size=10, color="1F5C1A" if group=="Case (ERAS)" else "7B3F00") ws[f"C{r}"].alignment = center() for r in range(5, 45): make_row(r, "Case (ERAS)", CASES_GREEN) for r in range(45, 85): make_row(r, "Control (Std)", CTRL_AMBER) # Data validations — use explicit full range like "C5:C84" def add_dv(ws, formula, col_letter, row_start, row_end): dv = DataValidation(type="list", formula1=formula, allow_blank=True, showErrorMessage=False) ws.add_data_validation(dv) dv.add(f"{col_letter}{row_start}:{col_letter}{row_end}") # Map column index to letter col_map = {i: get_column_letter(i) for i in range(1, len(COLUMNS)+1)} # Find column indices by header keyword def col_of(keyword): for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): if keyword.lower() in hdr.lower(): return get_column_letter(i) return None RS, RE = 5, 84 add_dv(ws, '"Case (ERAS),Control (Std)"', "C", RS, RE) # Type CS col index for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): hdr_lower = hdr.lower() if "type of cs" in hdr_lower or "elective" in hdr_lower: add_dv(ws, '"Elective,Emergency"', get_column_letter(i), RS, RE) elif "previous cs" in hdr_lower: add_dv(ws, '"Yes,No"', get_column_letter(i), RS, RE) elif "asa" in hdr_lower: add_dv(ws, '"I,II,III"', get_column_letter(i), RS, RE) elif any(k in hdr_lower for k in ["counselling","loading","anxiolytic","antibiotic", "antacid","compression","anti-emetic","warming","nausea","shivering", "breastfeed","multimodal","wound","pph","urinary","dvt","readmission", "icu","nicu"]): add_dv(ws, '"Yes,No"', get_column_letter(i), RS, RE) elif "lmwh" in hdr_lower: add_dv(ws, '"Yes,No,NA"', get_column_letter(i), RS, RE) elif "anaesthesia" in hdr_lower and "sab" in hdr_lower: add_dv(ws, '"SAB,CSE,GA"', get_column_letter(i), RS, RE) elif "neonatal\noutcome" in hdr_lower: add_dv(ws, '"Good,Fair,Poor"', get_column_letter(i), RS, RE) # ════════════════════════════════════════════════════════════════════════════ # SHEET 2 – SUMMARY # ════════════════════════════════════════════════════════════════════════════ ws2 = wb.create_sheet("Summary Statistics") ws2.freeze_panes = "B4" ws2.merge_cells("A1:F1") ws2["A1"].value = "ERAS-CS STUDY — SUMMARY STATISTICS" ws2["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws2["A1"].fill = fill(DARK_BLUE) ws2["A1"].alignment = center() ws2.row_dimensions[1].height = 32 ws2.merge_cells("A2:F2") ws2["A2"].value = "Auto-calculated from Data Collection sheet (Rows 5–44 = Cases, Rows 45–84 = Controls)" ws2["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws2["A2"].fill = fill(MID_BLUE) ws2["A2"].alignment = center() ws2.row_dimensions[2].height = 18 sum_headers = ["Parameter", "Cases (ERAS)\nn=40", "Controls\n(Std Care) n=40", "Difference\n(Case–Control)", "Unit", "Notes"] sum_widths = [40, 22, 22, 22, 20, 35] for i, (h, w) in enumerate(zip(sum_headers, sum_widths), start=1): cl = get_column_letter(i) c = ws2[f"{cl}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws2.column_dimensions[cl].width = w ws2.row_dimensions[3].height = 40 # Build column letter lookup from Data Collection sheet dc_col = {} for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): dc_col[hdr] = get_column_letter(i) # Helper to build AVERAGE and COUNTIF formulas def avg(col_l, r1, r2): return f"=IFERROR(AVERAGE('Data Collection'!{col_l}{r1}:{col_l}{r2}),\"\")" def pct_yes(col_l, r1, r2, n=40): return f'=IFERROR(COUNTIF(\'Data Collection\'!{col_l}{r1}:{col_l}{r2},"Yes")/{n}*100,"")' # We'll hardcode the important column letters based on COLUMNS order above # Col index reference (1-based): # G=Age, J=BMI, K=GestAge, L=TypeCS, P=Counselling, Q=CarbLoad, # R=FastSolids, S=FastLiqs, AB=IVFluids, AE=EBL, AD=TotalOpTime... # Let's just build it by counting col_letters = [get_column_letter(i) for i in range(1, len(COLUMNS)+1)] # COLUMNS order: # 0=SNo,1=MRN,2=Group,3=Date,4=Surgeon,5=Anaesthetist -> A..F # 6=Age,7=Gravida,8=Para,9=BMI,10=GestAge,11=TypeCS,12=PrevCS,13=ASA,14=Comorbid -> G..O # 15=Counselling,16=CarbLoad,17=FastSolids,18=FastLiqs,19=Anxiolytic, # 20=Antibio,21=Antacid,22=CompStocking,23=LMWH,24=AntiEmetic -> P..Y (16..24 -> 1-based 17..25) # Actually let me just index properly: def cl(zero_idx): return get_column_letter(zero_idx + 1) # 0-based indices for key columns: I_AGE=6; I_BMI=9; I_GA=10; I_TYPECS=11 I_COUNS=15; I_CARB=16; I_FSOL=17; I_FLIQ=18 I_IVFL=27; I_EBL=31; I_OPTIME=30 I_FLORAL=35; I_SOLID=36; I_AMBUL=37; I_CATH=38; I_IVDUR=39 I_NRS2=42; I_NRS6=43; I_NRS12=44; I_NRS24=45; I_RESCUE=46; I_OPI=47 I_LOS=50; I_DISCH=51; I_BF=52 I_WI=55; I_PPH=56; I_READT=58; I_ICU=59 I_BW=62; I_APG1=63; I_APG5=64; I_NICU=65 I_SAT=67 sum_rows = [ ("DEMOGRAPHICS", None, None, None, None), ("Age (mean)", avg(cl(I_AGE),5,44), avg(cl(I_AGE),45,84), "Years", ""), ("BMI (mean)", avg(cl(I_BMI),5,44), avg(cl(I_BMI),45,84), "kg/m²", ""), ("Gestational Age (mean)", avg(cl(I_GA),5,44), avg(cl(I_GA),45,84), "Weeks", ""), ("Elective CS (%)", f'=IFERROR(COUNTIF(\'Data Collection\'!{cl(I_TYPECS)}5:{cl(I_TYPECS)}44,"Elective")/40*100,"")', f'=IFERROR(COUNTIF(\'Data Collection\'!{cl(I_TYPECS)}45:{cl(I_TYPECS)}84,"Elective")/40*100,"")', "%", ""), ("PRE-OPERATIVE", None, None, None, None), ("Pre-op Counselling (%)", pct_yes(cl(I_COUNS),5,44), pct_yes(cl(I_COUNS),45,84), "%", ""), ("Carbohydrate Loading (%)",pct_yes(cl(I_CARB),5,44), pct_yes(cl(I_CARB),45,84), "%", ""), ("Fasting – Solids (mean)",avg(cl(I_FSOL),5,44), avg(cl(I_FSOL),45,84), "hrs", ""), ("Fasting – Liquids (mean)",avg(cl(I_FLIQ),5,44), avg(cl(I_FLIQ),45,84), "hrs", ""), ("INTRA-OPERATIVE", None, None, None, None), ("IV Fluid Volume (mean)", avg(cl(I_IVFL),5,44), avg(cl(I_IVFL),45,84), "mL", ""), ("Total Op Time (mean)", avg(cl(I_OPTIME),5,44),avg(cl(I_OPTIME),45,84),"min", ""), ("EBL (mean)", avg(cl(I_EBL),5,44), avg(cl(I_EBL),45,84), "mL", ""), ("POST-OPERATIVE", None, None, None, None), ("Time to Oral Fluids (mean)", avg(cl(I_FLORAL),5,44), avg(cl(I_FLORAL),45,84), "hrs", ""), ("Time to Solid Food (mean)", avg(cl(I_SOLID),5,44), avg(cl(I_SOLID),45,84), "hrs", ""), ("Time to Ambulation (mean)", avg(cl(I_AMBUL),5,44), avg(cl(I_AMBUL),45,84), "hrs", ""), ("Catheter Removal (mean)", avg(cl(I_CATH),5,44), avg(cl(I_CATH),45,84), "hrs", ""), ("PAIN & ANALGESIA", None, None, None, None), ("NRS Pain @ 2h (mean)", avg(cl(I_NRS2),5,44), avg(cl(I_NRS2),45,84), "0–10", ""), ("NRS Pain @ 6h (mean)", avg(cl(I_NRS6),5,44), avg(cl(I_NRS6),45,84), "0–10", ""), ("NRS Pain @ 12h (mean)", avg(cl(I_NRS12),5,44), avg(cl(I_NRS12),45,84), "0–10", ""), ("NRS Pain @ 24h (mean)", avg(cl(I_NRS24),5,44), avg(cl(I_NRS24),45,84), "0–10", ""), ("1st Rescue Analgesia (mean)", avg(cl(I_RESCUE),5,44), avg(cl(I_RESCUE),45,84), "hrs", ""), ("Total Opioid (mean)", avg(cl(I_OPI),5,44), avg(cl(I_OPI),45,84), "mg ME", ""), ("PRIMARY OUTCOMES", None, None, None, None), ("Length of Stay (mean)", avg(cl(I_LOS),5,44), avg(cl(I_LOS),45,84), "hrs", "Key outcome"), ("Discharge Readiness (mean)", avg(cl(I_DISCH),5,44), avg(cl(I_DISCH),45,84), "hrs", ""), ("Breastfeeding Initiated (%)", pct_yes(cl(I_BF),5,44), pct_yes(cl(I_BF),45,84), "%", ""), ("COMPLICATIONS", None, None, None, None), ("Wound Infection (%)", pct_yes(cl(I_WI),5,44), pct_yes(cl(I_WI),45,84), "%", ""), ("PPH > 1000 mL (%)", pct_yes(cl(I_PPH),5,44), pct_yes(cl(I_PPH),45,84), "%", ""), ("Readmission ≤30 days (%)",pct_yes(cl(I_READT),5,44), pct_yes(cl(I_READT),45,84), "%", ""), ("ICU Admission (%)", pct_yes(cl(I_ICU),5,44), pct_yes(cl(I_ICU),45,84), "%", ""), ("NEONATAL", None, None, None, None), ("Birth Weight (mean)", avg(cl(I_BW),5,44), avg(cl(I_BW),45,84), "g", ""), ("APGAR @ 1 min (mean)", avg(cl(I_APG1),5,44), avg(cl(I_APG1),45,84), "0–10",""), ("APGAR @ 5 min (mean)", avg(cl(I_APG5),5,44), avg(cl(I_APG5),45,84), "0–10",""), ("NICU Admission (%)", pct_yes(cl(I_NICU),5,44), pct_yes(cl(I_NICU),45,84), "%",""), ("SATISFACTION", None, None, None, None), ("Patient Satisfaction (mean)", avg(cl(I_SAT),5,44), avg(cl(I_SAT),45,84), "1–10",""), ] data_row = 4 for row_data in sum_rows: label = row_data[0] if len(row_data) == 5 and row_data[1] is None: # Section divider ws2.merge_cells(f"A{data_row}:F{data_row}") c = ws2[f"A{data_row}"] c.value = f" {label}" c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get( label.upper().replace("-","").strip().split()[0], "2E75B6")) c.alignment = left() ws2.row_dimensions[data_row].height = 20 data_row += 1 continue label, case_f, ctrl_f, unit, note = row_data alt = GREY if data_row % 2 == 0 else WHITE ws2[f"A{data_row}"].value = label ws2[f"B{data_row}"].value = case_f ws2[f"C{data_row}"].value = ctrl_f ws2[f"D{data_row}"].value = f"=IFERROR(B{data_row}-C{data_row},\"\")" ws2[f"E{data_row}"].value = unit ws2[f"F{data_row}"].value = note for col in ["A","B","C","D","E","F"]: c = ws2[f"{col}{data_row}"] if col == "B": c.fill = fill(CASES_GREEN) elif col == "C": c.fill = fill(CTRL_AMBER) else: c.fill = fill(alt) c.font = cell_font(bold=(col == "A")) c.alignment = left() if col in ("A","E","F") else center() c.border = thin_border() if col == "D": c.font = Font(name="Arial", bold=True, size=10, color="C00000") # Number format for percentage columns if unit in ("%",) and col in ("B","C","D"): c.number_format = '0.0"%"' ws2[f"B{data_row}"].number_format = "0.00" ws2[f"C{data_row}"].number_format = "0.00" ws2.row_dimensions[data_row].height = 18 data_row += 1 # ════════════════════════════════════════════════════════════════════════════ # SHEET 3 – ERAS PROTOCOL CHECKLIST # ════════════════════════════════════════════════════════════════════════════ ws3 = wb.create_sheet("ERAS Protocol Checklist") ws3.merge_cells("A1:D1") ws3["A1"].value = "ERAS-CS PROTOCOL COMPLIANCE CHECKLIST" ws3["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws3["A1"].fill = fill(DARK_BLUE) ws3["A1"].alignment = center() ws3.row_dimensions[1].height = 30 ws3.merge_cells("A2:D2") ws3["A2"].value = "Tick each element applied — per ERAS Society / RCOG / NICE Guidelines" ws3["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws3["A2"].fill = fill(MID_BLUE) ws3["A2"].alignment = center() ws3.row_dimensions[2].height = 18 chk_headers = ["Phase", "ERAS Element", "Applied (Yes/No/NA)", "Remarks"] chk_widths = [20, 58, 20, 45] for i, (h, w) in enumerate(zip(chk_headers, chk_widths), start=1): cl_c = get_column_letter(i) c = ws3[f"{cl_c}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws3.column_dimensions[cl_c].width = w ws3.row_dimensions[3].height = 36 phase_colors = { "Pre-operative": "E2EFDA", "Intra-operative": "DEEAF1", "Post-operative": "FFF2CC", } checklist = [ ("Pre-operative", "Patient education & counselling (written + verbal information provided)"), ("Pre-operative", "Carbohydrate loading — oral CHO drink given ≤2 hrs before surgery"), ("Pre-operative", "Minimum fasting: 6h solids / 2h clear fluids (no prolonged fasting)"), ("Pre-operative", "Antibiotic prophylaxis administered BEFORE skin incision"), ("Pre-operative", "Antacid prophylaxis (ranitidine / omeprazole) given"), ("Pre-operative", "Anxiolytic pre-medication avoided or minimised"), ("Pre-operative", "DVT prophylaxis: compression stockings applied"), ("Pre-operative", "LMWH thromboprophylaxis given (if indicated)"), ("Pre-operative", "Anti-emetic prophylaxis administered"), ("Intra-operative","Spinal anaesthesia preferred over GA (SAB or CSE)"), ("Intra-operative","Intrathecal opioid (morphine/fentanyl) administered for post-op analgesia"), ("Intra-operative","Active patient warming (warmed IV fluids, forced-air warming blanket)"), ("Intra-operative","Vasopressor infusion used to prevent spinal-induced hypotension"), ("Intra-operative","Goal-directed IV fluid therapy — avoid excessive hydration"), ("Intra-operative","Oxytocin given as slow infusion (NOT rapid IV bolus)"), ("Intra-operative","Uterine exteriorisation avoided where possible"), ("Intra-operative","Intra-op PONV prophylaxis (dexamethasone ± ondansetron)"), ("Intra-operative","Skin-to-skin contact / early mother-baby bonding facilitated in OT"), ("Post-operative", "Early oral hydration — clear fluids within 30 min of end of surgery"), ("Post-operative", "Early feeding — solid food offered within 2 hrs post-op"), ("Post-operative", "Multimodal analgesia: paracetamol + NSAID + low-dose opioid"), ("Post-operative", "Urinary catheter removed within 12–24 hrs post-op"), ("Post-operative", "Early mobilisation — patient ambulating within 8 hrs post-op"), ("Post-operative", "Incentive spirometry / deep breathing exercises taught and performed"), ("Post-operative", "DVT/thromboprophylaxis continued post-op per protocol"), ("Post-operative", "Breastfeeding support and lactation counselling provided"), ("Post-operative", "Wound inspection and dressing performed before discharge"), ("Post-operative", "Structured discharge criteria checklist completed"), ("Post-operative", "Post-discharge follow-up planned (phone call within 24–72 hrs)"), ] for row_i, (phase, element) in enumerate(checklist, start=4): fc = phase_colors.get(phase, WHITE) ws3[f"A{row_i}"].value = phase ws3[f"B{row_i}"].value = element ws3[f"C{row_i}"].value = "" ws3[f"D{row_i}"].value = "" for col_c in ["A","B","C","D"]: c = ws3[f"{col_c}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col_c == "A")) c.alignment = left() c.border = thin_border() ws3.row_dimensions[row_i].height = 20 dv3 = DataValidation(type="list", formula1='"Yes,No,NA"', allow_blank=True, showErrorMessage=False) ws3.add_data_validation(dv3) dv3.add(f"C{row_i}") # ════════════════════════════════════════════════════════════════════════════ # SHEET 4 – LEGEND / CODEBOOK # ════════════════════════════════════════════════════════════════════════════ ws4 = wb.create_sheet("Legend & Codebook") ws4.merge_cells("A1:C1") ws4["A1"].value = "LEGEND & VARIABLE CODEBOOK — ERAS-CS STUDY" ws4["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws4["A1"].fill = fill(DARK_BLUE) ws4["A1"].alignment = center() ws4.row_dimensions[1].height = 30 ws4.column_dimensions["A"].width = 38 ws4.column_dimensions["B"].width = 26 ws4.column_dimensions["C"].width = 58 for i, h in enumerate(["Variable", "Allowed Values", "Definition / Notes"], start=1): c = ws4[f"{get_column_letter(i)}2"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws4.row_dimensions[2].height = 22 legend_rows = [ ("Group", "Case (ERAS) / Control (Std)", "Cases received full ERAS protocol; Controls received standard perioperative care"), ("ASA Grade", "I / II / III", "American Society of Anaesthesiologists physical status"), ("Type of CS", "Elective / Emergency", "Scheduled vs. unplanned caesarean section"), ("BMI", "Numeric (kg/m²)", "Pre-operative body mass index = weight(kg) / height²(m)"), ("NRS Pain Score", "0–10", "Numeric Rating Scale: 0=no pain, 10=worst imaginable"), ("EBL", "Numeric (mL)", "Estimated blood loss (swab weighing + suction canister)"), ("Anaesthesia Type", "SAB / CSE / GA", "Spinal Anaesthesia Block / Combined Spinal-Epidural / General"), ("APGAR Score", "0–10", "Assessed at 1 min and 5 min by paediatrician"), ("Length of Stay", "Numeric (hours)", "Time from end of surgery to actual hospital discharge"), ("Discharge Readiness", "Numeric (hours)", "Time from end of surgery to meeting all predefined discharge criteria"), ("Breastfeeding", "Yes / No", "Skin-to-skin contact and first latch achieved within 1h of delivery"), ("Wound Infection", "Yes / No", "Per CDC Surgical Site Infection criteria (superficial/deep/organ-space)"), ("PPH > 1000 mL", "Yes / No", "Postpartum haemorrhage: estimated blood loss > 1000 mL"), ("Readmission", "Yes / No", "Any unplanned readmission within 30 days of initial discharge"), ("DVT / PE", "Yes / No", "Deep Vein Thrombosis or Pulmonary Embolism confirmed by imaging"), ("Opioid Consumption", "Numeric (mg ME)", "Total 24-h opioid consumption expressed in morphine equivalents (mg)"), ("Satisfaction Score", "1–10", "Patient satisfaction questionnaire at discharge (10=completely satisfied)"), ("Neonatal Outcome", "Good / Fair / Poor", "Good=no NICU/normal APGAR; Fair=brief obs; Poor=prolonged NICU or adverse"), ("Multimodal Analgesia", "Yes / No", "Combination of ≥2 analgesic classes (paracetamol + NSAID + low-dose opioid)"), ("PONV", "Yes / No", "Post-operative nausea/vomiting requiring treatment"), ("LMWH", "Yes / No / NA", "Low Molecular Weight Heparin thromboprophylaxis (NA if not indicated)"), ("Vasopressor", "Yes / No / Agent", "e.g. Phenylephrine infusion, Ephedrine bolus"), ("Intrathecal Opioid", "Numeric (µg)", "Dose of intrathecal fentanyl or morphine in micrograms"), ("Morphine Equivalent", "mg ME", "Conversion: 10mg oxycodone=15mg ME; 10mg tramadol=1mg ME"), ("Carb Loading", "Yes / No", "Oral carbohydrate drink (e.g. Nutricia PreOp) given ≤2h before surgery"), ] for row_i, (var, vals, defn) in enumerate(legend_rows, start=3): fc = GREY if row_i % 2 == 0 else WHITE ws4[f"A{row_i}"].value = var ws4[f"B{row_i}"].value = vals ws4[f"C{row_i}"].value = defn for col_c in ["A","B","C"]: c = ws4[f"{col_c}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col_c == "A")) c.alignment = left() c.border = thin_border() ws4.row_dimensions[row_i].height = 20 # ── Save ───────────────────────────────────────────────────────────────────── import os os.makedirs("/tmp/workspace/eras-cs", exist_ok=True) out = "/tmp/workspace/eras-cs/ERAS_CS_DataCollection.xlsx" wb.save(out) print(f"Saved: {out}") print(f"File size: {os.path.getsize(out)//1024} KB") print("Sheets:", [s.title for s in wb.worksheets]) EOF

python3 << 'PYEOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation import os # ── Palette ────────────────────────────────────────────────────────────────── DARK_BLUE = "1F3864" MID_BLUE = "2E75B6" CASES_GREEN = "E2EFDA" CTRL_AMBER = "FFF2CC" WHITE = "FFFFFF" GREY = "F2F2F2" EXCL_RED = "FCE4D6" INCL_LIME = "E2EFDA" WARN_ORANGE = "F4B942" N = 75 # per group CASE_START = 5 CASE_END = CASE_START + N - 1 # 5..79 CTRL_START = CASE_END + 1 # 80 CTRL_END = CTRL_START + N - 1 # 80..154 SECTION_COLORS = { "IDENTIFIERS": "1F3864", "DEMOGRAPHICS": "2E75B6", "PRE-OP ERAS": "375623", "INTRA-OP": "7030A0", "POST-OP": "843C0C", "PAIN & ANALGESIA": "C00000", "PRIMARY OUTCOMES": "0070C0", "COMPLICATIONS": "833C00", "NEONATAL": "403152", "SATISFACTION": "375623", } def fill(h): return PatternFill("solid", fgColor=h) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) def cell_font(bold=False, size=10, color="000000"): return Font(name="Arial", bold=bold, size=size, color=color) def thin_border(): s = Side(style="thin", color="CCCCCC") return Border(left=s, right=s, top=s, bottom=s) COLUMNS = [ ("IDENTIFIERS", "S.No", 6), ("IDENTIFIERS", "Patient\nID / MRN", 16), ("IDENTIFIERS", "Group\n(Case/Control)", 16), ("IDENTIFIERS", "Date of\nSurgery\n(DD/MM/YY)", 14), ("IDENTIFIERS", "Surgeon", 16), ("IDENTIFIERS", "Anaesthetist", 16), ("DEMOGRAPHICS", "Age (yrs)", 10), ("DEMOGRAPHICS", "Gravida", 10), ("DEMOGRAPHICS", "Para", 10), ("DEMOGRAPHICS", "BMI\n(kg/m²)", 12), ("DEMOGRAPHICS", "Gestational\nAge (wks)", 14), ("DEMOGRAPHICS", "Type of CS\n(Elective/\nEmergency)", 16), ("DEMOGRAPHICS", "Singleton\nPregnancy\n(Yes/No)", 14), ("DEMOGRAPHICS", "Previous CS\n(Yes/No)", 14), ("DEMOGRAPHICS", "ASA\nGrade", 12), ("DEMOGRAPHICS", "Comorbidities\n(specify)", 22), ("PRE-OP ERAS", "Pre-op\nCounselling\n(Yes/No)", 14), ("PRE-OP ERAS", "Carb\nLoading\n(Yes/No)", 14), ("PRE-OP ERAS", "Fasting –\nSolids (hrs)", 12), ("PRE-OP ERAS", "Fasting –\nLiquids (hrs)", 12), ("PRE-OP ERAS", "Anxiolytic\nPre-med\n(Yes/No)", 14), ("PRE-OP ERAS", "Antibiotic\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Antacid\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Compression\nStockings\n(Yes/No)", 14), ("PRE-OP ERAS", "LMWH\n(Yes/No/NA)", 14), ("PRE-OP ERAS", "Anti-emetic\nProphylaxis\n(Yes/No)", 14), ("INTRA-OP", "Anaesthesia\n(SAB/CSE/GA)", 16), ("INTRA-OP", "Intrathecal\nOpioid\nDose (µg)", 14), ("INTRA-OP", "IV Fluids\nIntra-op\n(mL)", 14), ("INTRA-OP", "Vasopressor\n(Yes/No/\nAgent)", 16), ("INTRA-OP", "Skin-to-\nDelivery\nTime (min)", 14), ("INTRA-OP", "Total Op\nTime (min)", 14), ("INTRA-OP", "EBL (mL)", 12), ("INTRA-OP", "Oxytocin\nDose (IU)", 12), ("INTRA-OP", "Active\nWarming\n(Yes/No)", 14), ("INTRA-OP", "Intra-op\nNausea/\nVomiting", 14), ("POST-OP", "Time to\nOral Fluids\n(hrs)", 14), ("POST-OP", "Time to\nSolid Food\n(hrs)", 14), ("POST-OP", "Time to\nAmbulation\n(hrs)", 14), ("POST-OP", "Catheter\nRemoval\n(hrs)", 14), ("POST-OP", "IV Fluid\nDuration\n(hrs)", 14), ("POST-OP", "Post-op\nNausea/\nVomiting", 14), ("POST-OP", "Post-op\nShivering\n(Yes/No)", 14), ("PAIN & ANALGESIA", "NRS Pain\n@ 2h\n(0–10)", 13), ("PAIN & ANALGESIA", "NRS Pain\n@ 6h\n(0–10)", 13), ("PAIN & ANALGESIA", "NRS Pain\n@ 12h\n(0–10)", 13), ("PAIN & ANALGESIA", "NRS Pain\n@ 24h\n(0–10)", 13), ("PAIN & ANALGESIA", "1st Rescue\nAnalgesia\n(hrs)", 14), ("PAIN & ANALGESIA", "Total Opioid\n24h\n(mg ME)", 16), ("PAIN & ANALGESIA", "Multimodal\nAnalgesia\n(Yes/No)", 14), ("PRIMARY OUTCOMES", "Length of\nHospital\nStay (hrs)", 16), ("PRIMARY OUTCOMES", "Discharge\nReadiness\n(hrs)", 16), ("PRIMARY OUTCOMES", "Breastfeeding\nInitiated\n(Yes/No)", 16), ("PRIMARY OUTCOMES", "Mother-Baby\nBonding\nScore (0–10)", 16), ("COMPLICATIONS", "Wound\nInfection\n(Yes/No)", 14), ("COMPLICATIONS", "PPH\n>1000mL\n(Yes/No)", 13), ("COMPLICATIONS", "Urinary\nRetention\n(Yes/No)", 14), ("COMPLICATIONS", "DVT / PE\n(Yes/No)", 13), ("COMPLICATIONS", "Readmission\n≤30 days\n(Yes/No)", 15), ("COMPLICATIONS", "ICU\nAdmission\n(Yes/No)", 13), ("COMPLICATIONS", "Other\nComplications\n(specify)", 22), ("NEONATAL", "Birth\nWeight (g)", 14), ("NEONATAL", "APGAR\n@ 1 min", 12), ("NEONATAL", "APGAR\n@ 5 min", 12), ("NEONATAL", "NICU\nAdmission\n(Yes/No)", 14), ("NEONATAL", "Neonatal\nOutcome", 16), ("SATISFACTION", "Patient\nSatisfaction\n(1–10)", 16), ("SATISFACTION", "Remarks /\nNotes", 32), ] # ═══════════════════════════════════════════════════════════════════════════ wb = openpyxl.Workbook() # ──────────────────────────────────────────────────────────────────────────── # SHEET 1 – DATA COLLECTION # ──────────────────────────────────────────────────────────────────────────── ws = wb.active ws.title = "Data Collection" ws.freeze_panes = "C5" TOTAL_COLS = len(COLUMNS) last_col = get_column_letter(TOTAL_COLS) # Row 1 – Main title ws.merge_cells(f"A1:{last_col}1") ws["A1"].value = "ENHANCED RECOVERY AFTER CAESAREAN SECTION (ERAS-CS) — CASE vs. CONTROL DATA COLLECTION FORM" ws["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws["A1"].fill = fill(DARK_BLUE) ws["A1"].alignment = center() ws.row_dimensions[1].height = 36 # Row 2 – Subtitle with n=75 ws.merge_cells(f"A2:{last_col}2") ws["A2"].value = f"CASES = ERAS Protocol Group (n={N}) | CONTROLS = Standard Care Group (n={N}) · Inclusion: Elective & Emergency CS, Singleton Pregnancies · Exclusion: Multiple Pregnancies, Fetal Anomalies" ws["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws["A2"].fill = fill(MID_BLUE) ws["A2"].alignment = center() ws.row_dimensions[2].height = 20 # Row 3 – Group label banners ws.merge_cells(f"A3:{last_col}3") ws["A3"].value = f"▶ ROWS 5–{CASE_END} = CASES (ERAS GROUP) | ROWS {CTRL_START}–{CTRL_END} = CONTROLS (STANDARD CARE)" ws["A3"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws["A3"].fill = fill("1A5276") ws["A3"].alignment = center() ws.row_dimensions[3].height = 22 # Row 4 – Section headers # Build section ranges sec_ranges = {} prev = None; st = 1 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): if sec != prev: if prev: sec_ranges[prev] = (st, i-1) prev = sec; st = i sec_ranges[prev] = (st, TOTAL_COLS) ws.row_dimensions[4].height = 22 for sec, (c1, c2) in sec_ranges.items(): l1, l2 = get_column_letter(c1), get_column_letter(c2) if c1 != c2: ws.merge_cells(f"{l1}4:{l2}4") c = ws[f"{l1}4"] c.value = sec c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="medium", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) # Row 5 is FIRST data row — column headers go in row 5? No — keep header at row 5 # Actually: title=1, subtitle=2, banner=3, section=4 → col headers=5, data starts 6 # WAIT — let me keep it: section row=4, col headers=5, data rows=6.. # Shift: col headers → row 5, data rows → 6..80 (cases) + 81..155 (controls) CASE_START = 6 CASE_END = CASE_START + N - 1 # 6..80 CTRL_START = CASE_END + 1 # 81 CTRL_END = CTRL_START + N - 1 # 81..155 # Update row 3 banner ws["A3"].value = f"▶ ROWS {CASE_START}–{CASE_END} = CASES (ERAS GROUP) | ROWS {CTRL_START}–{CTRL_END} = CONTROLS (STANDARD CARE)" # Column headers – row 5 ws.row_dimensions[5].height = 52 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}5"] c.value = hdr c.font = Font(name="Arial", bold=True, size=9, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws.column_dimensions[cl].width = wid # Data rows def make_row(r, group, fc, serial): for i in range(1, TOTAL_COLS+1): cl = get_column_letter(i) c = ws[f"{cl}{r}"] c.fill = fill(fc) c.font = cell_font() c.alignment = center() if i == 1 else left() c.border = thin_border() ws[f"A{r}"].value = serial ws[f"C{r}"].value = group ws[f"C{r}"].font = Font(name="Arial", bold=True, size=10, color="1F5C1A" if "Case" in group else "7B3F00") ws[f"C{r}"].alignment = center() # Singleton pre-filled Yes (inclusion criterion) ws[f"M{r}"].value = "Yes" ws[f"M{r}"].alignment = center() ws[f"M{r}"].font = Font(name="Arial", size=10, color="1F5C1A") for r in range(CASE_START, CASE_END+1): make_row(r, "Case (ERAS)", CASES_GREEN, r - CASE_START + 1) for r in range(CTRL_START, CTRL_END+1): make_row(r, "Control (Std)", CTRL_AMBER, r - CTRL_START + 1) # Data validations def add_dv(ws, formula, col_letter, r1, r2): dv = DataValidation(type="list", formula1=formula, allow_blank=True, showErrorMessage=False) ws.add_data_validation(dv) dv.add(f"{col_letter}{r1}:{col_letter}{r2}") RS, RE = CASE_START, CTRL_END # Map column by keyword dv_rules = [] for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): h = hdr.lower().replace("\n"," ") cl = get_column_letter(i) if "group" in h and "case" not in h: dv_rules.append((cl, '"Case (ERAS),Control (Std)"')) elif "type of cs" in h: dv_rules.append((cl, '"Elective,Emergency"')) elif "singleton" in h: dv_rules.append((cl, '"Yes,No"')) elif "previous cs" in h: dv_rules.append((cl, '"Yes,No"')) elif "asa" in h: dv_rules.append((cl, '"I,II,III"')) elif "lmwh" in h: dv_rules.append((cl, '"Yes,No,NA"')) elif "anaesthesia" in h and "sab" in h: dv_rules.append((cl, '"SAB,CSE,GA"')) elif "neonatal\noutcome" in hdr.lower(): dv_rules.append((cl, '"Good,Fair,Poor"')) elif any(k in h for k in ["counselling","loading","anxiolytic","antibiotic", "antacid","compression","anti-emetic","warming","nausea","shivering", "breastfeed","multimodal","wound","pph","urinary","dvt","readmission", "icu admission","nicu"]): dv_rules.append((cl, '"Yes,No"')) for cl, formula in dv_rules: add_dv(ws, formula, cl, RS, RE) # Add conditional format note to col C header ws["C5"].comment = None # clear any stale # ──────────────────────────────────────────────────────────────────────────── # SHEET 2 – INCLUSION / EXCLUSION CRITERIA # ──────────────────────────────────────────────────────────────────────────── ws_ie = wb.create_sheet("Inclusion & Exclusion", 1) ws_ie.column_dimensions["A"].width = 8 ws_ie.column_dimensions["B"].width = 45 ws_ie.column_dimensions["C"].width = 55 ws_ie.merge_cells("A1:C1") ws_ie["A1"].value = "STUDY ELIGIBILITY CRITERIA — ERAS-CS (n=75 per group)" ws_ie["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws_ie["A1"].fill = fill(DARK_BLUE) ws_ie["A1"].alignment = center() ws_ie.row_dimensions[1].height = 34 # ── INCLUSION ────────────────────────────────────────────────────────────── ws_ie.merge_cells("A2:C2") ws_ie["A2"].value = " INCLUSION CRITERIA" ws_ie["A2"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws_ie["A2"].fill = fill("375623") ws_ie["A2"].alignment = left() ws_ie.row_dimensions[2].height = 24 inc_headers = ["#", "Criterion", "Details / Rationale"] for i, h in enumerate(inc_headers, start=1): c = ws_ie[f"{get_column_letter(i)}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill("538135") c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws_ie.row_dimensions[3].height = 22 inclusion = [ ("1", "Both elective and emergency caesarean section", "Elective = scheduled CS; Emergency = Category I–III CS not in extremis"), ("2", "Singleton pregnancy", "One fetus only; multiple pregnancies are excluded (see below)"), ("3", "Gestational age ≥ 28 weeks", "Viable gestation; modify this threshold per your unit's policy"), ("4", "Age ≥ 18 years", "Adult patients able to provide informed written consent"), ("5", "Written informed consent obtained", "Patient/guardian consented before enrolment into either group"), ("6", "Planned spinal or combined spinal-epidural anaesthesia (Cases)", "ERAS protocol requires regional anaesthesia as default"), ("7", "All ASA grades I–III", "Include all physical status classes unless other exclusion criteria apply"), ] for row_i, (num, crit, rationale) in enumerate(inclusion, start=4): ws_ie[f"A{row_i}"].value = num ws_ie[f"B{row_i}"].value = crit ws_ie[f"C{row_i}"].value = rationale for col_c in ["A","B","C"]: c = ws_ie[f"{col_c}{row_i}"] c.fill = fill(INCL_LIME) c.font = cell_font(bold=(col_c=="B")) c.alignment = left() c.border = thin_border() ws_ie.row_dimensions[row_i].height = 20 # ── EXCLUSION ────────────────────────────────────────────────────────────── excl_header_row = 4 + len(inclusion) + 1 # blank gap row ws_ie.row_dimensions[excl_header_row - 1].height = 10 # spacer ws_ie.merge_cells(f"A{excl_header_row}:C{excl_header_row}") ws_ie[f"A{excl_header_row}"].value = " EXCLUSION CRITERIA" ws_ie[f"A{excl_header_row}"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws_ie[f"A{excl_header_row}"].fill = fill("843C0C") ws_ie[f"A{excl_header_row}"].alignment = left() ws_ie.row_dimensions[excl_header_row].height = 24 exc_hdr_row = excl_header_row + 1 for i, h in enumerate(inc_headers, start=1): c = ws_ie[f"{get_column_letter(i)}{exc_hdr_row}"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill("C55A11") c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws_ie.row_dimensions[exc_hdr_row].height = 22 exclusion = [ ("1", "Multiple pregnancy (twins, triplets, higher order)", "Key exclusion — singleton criterion is inclusion requirement"), ("2", "Fetal anomaly (structural or chromosomal)", "Fetal anomalies alter management; neonatal outcomes non-comparable"), ("3", "General anaesthesia (GA) required", "ERAS protocol is designed around regional anaesthesia; GA cases managed differently"), ("4", "Placenta praevia / placenta accreta spectrum", "High-risk cases with atypical surgical and anaesthetic management"), ("5", "Severe preeclampsia / eclampsia", "Haemodynamic instability affects fluid management, outcome comparability"), ("6", "Active maternal coagulopathy or bleeding disorder", "Affects perioperative fluid, analgesic and anticoagulant decisions"), ("7", "Maternal cardiac or pulmonary disease (ASA IV/V)", "High-risk anaesthetic cases require deviation from standard ERAS elements"), ("8", "Patient refusal of consent", "Ethical requirement — any withdrawal of consent at any point"), ("9", "Incomplete data / lost to follow-up", "Cases with >20% missing primary outcome data will be excluded from analysis"), ] for row_i, (num, crit, rationale) in enumerate(exclusion, start=exc_hdr_row+1): ws_ie[f"A{row_i}"].value = num ws_ie[f"B{row_i}"].value = crit ws_ie[f"C{row_i}"].value = rationale for col_c in ["A","B","C"]: c = ws_ie[f"{col_c}{row_i}"] c.fill = fill(EXCL_RED) c.font = cell_font(bold=(col_c=="B")) c.alignment = left() c.border = thin_border() ws_ie.row_dimensions[row_i].height = 20 # ── SAMPLE SIZE NOTE ────────────────────────────────────────────────────── note_row = exc_hdr_row + len(exclusion) + 2 ws_ie.merge_cells(f"A{note_row}:C{note_row}") ws_ie[f"A{note_row}"].value = ( f" SAMPLE SIZE NOTE: n = {N} per group (Total N = {N*2}). " "Power calculation should be based on primary outcome (Length of Hospital Stay). " "Assumed α = 0.05, Power = 80%, effect size per published ERAS-CS trials." ) ws_ie[f"A{note_row}"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws_ie[f"A{note_row}"].fill = fill(MID_BLUE) ws_ie[f"A{note_row}"].alignment = left() ws_ie.row_dimensions[note_row].height = 36 # ──────────────────────────────────────────────────────────────────────────── # SHEET 3 – SUMMARY STATISTICS (updated for n=75) # ──────────────────────────────────────────────────────────────────────────── ws2 = wb.create_sheet("Summary Statistics") ws2.freeze_panes = "B4" ws2.merge_cells("A1:F1") ws2["A1"].value = "ERAS-CS STUDY — SUMMARY STATISTICS" ws2["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws2["A1"].fill = fill(DARK_BLUE) ws2["A1"].alignment = center() ws2.row_dimensions[1].height = 32 ws2.merge_cells("A2:F2") ws2["A2"].value = (f"Auto-calculated from Data Collection sheet " f"(Rows {CASE_START}–{CASE_END} = Cases n={N}, " f"Rows {CTRL_START}–{CTRL_END} = Controls n={N})") ws2["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws2["A2"].fill = fill(MID_BLUE) ws2["A2"].alignment = center() ws2.row_dimensions[2].height = 18 sum_hdrs = [f"Parameter", f"Cases (ERAS)\nn={N}", f"Controls (Std)\nn={N}", "Difference\n(Case – Control)", "Unit", "Notes"] sum_widths = [42, 22, 22, 22, 18, 35] for i, (h, w) in enumerate(zip(sum_hdrs, sum_widths), start=1): cl = get_column_letter(i) c = ws2[f"{cl}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws2.column_dimensions[cl].width = w ws2.row_dimensions[3].height = 40 def cl_of(zero_idx): return get_column_letter(zero_idx + 1) def avg(col_l, r1, r2): return f"=IFERROR(AVERAGE('Data Collection'!{col_l}{r1}:{col_l}{r2}),\"\")" def pct(col_l, r1, r2, n, val="Yes"): return f'=IFERROR(COUNTIF(\'Data Collection\'!{col_l}{r1}:{col_l}{r2},"{val}")/{n}*100,"")' # 0-based column index from COLUMNS list ci = {hdr.replace("\n"," "): i for i, (sec, hdr, wid) in enumerate(COLUMNS)} def C(key): # fuzzy match for k, v in ci.items(): if key.lower() in k.lower(): return cl_of(v) return "A" I_AGE=6; I_BMI=9; I_GA=10; I_TYPECS=11 I_COUNS=16; I_CARB=17; I_FSOL=18; I_FLIQ=19 I_IVFL=28; I_EBL=32; I_OPTIME=31 I_FLORAL=36; I_SOLID=37; I_AMBUL=38; I_CATH=39 I_NRS2=43; I_NRS6=44; I_NRS12=45; I_NRS24=46; I_RESCUE=47; I_OPI=48 I_LOS=51; I_DISCH=52; I_BF=53 I_WI=56; I_PPH=57; I_READT=59; I_ICU=60 I_BW=63; I_APG1=64; I_APG5=65; I_NICU=66 I_SAT=68 sum_rows = [ # (label, case_formula, ctrl_formula, unit, note) or (section_label,) ("DEMOGRAPHICS",), ("Age — mean", avg(cl_of(I_AGE),CASE_START,CASE_END), avg(cl_of(I_AGE),CTRL_START,CTRL_END), "Years", ""), ("BMI — mean", avg(cl_of(I_BMI),CASE_START,CASE_END), avg(cl_of(I_BMI),CTRL_START,CTRL_END), "kg/m²", ""), ("Gestational Age — mean", avg(cl_of(I_GA),CASE_START,CASE_END), avg(cl_of(I_GA),CTRL_START,CTRL_END), "Weeks", ""), ("Elective CS — %", pct(cl_of(I_TYPECS),CASE_START,CASE_END,N,"Elective"), pct(cl_of(I_TYPECS),CTRL_START,CTRL_END,N,"Elective"), "%", ""), ("PRE-OPERATIVE",), ("Pre-op Counselling — %", pct(cl_of(I_COUNS),CASE_START,CASE_END,N), pct(cl_of(I_COUNS),CTRL_START,CTRL_END,N), "%", ""), ("Carbohydrate Loading — %", pct(cl_of(I_CARB),CASE_START,CASE_END,N), pct(cl_of(I_CARB),CTRL_START,CTRL_END,N), "%", ""), ("Fasting Solids — mean hrs", avg(cl_of(I_FSOL),CASE_START,CASE_END), avg(cl_of(I_FSOL),CTRL_START,CTRL_END), "hrs", ""), ("Fasting Liquids — mean hrs", avg(cl_of(I_FLIQ),CASE_START,CASE_END), avg(cl_of(I_FLIQ),CTRL_START,CTRL_END), "hrs", ""), ("INTRA-OPERATIVE",), ("IV Fluid Volume — mean mL", avg(cl_of(I_IVFL),CASE_START,CASE_END), avg(cl_of(I_IVFL),CTRL_START,CTRL_END), "mL", ""), ("Total Op Time — mean min", avg(cl_of(I_OPTIME),CASE_START,CASE_END), avg(cl_of(I_OPTIME),CTRL_START,CTRL_END), "min", ""), ("EBL — mean mL", avg(cl_of(I_EBL),CASE_START,CASE_END), avg(cl_of(I_EBL),CTRL_START,CTRL_END), "mL", ""), ("POST-OPERATIVE",), ("Time to Oral Fluids — mean hrs", avg(cl_of(I_FLORAL),CASE_START,CASE_END), avg(cl_of(I_FLORAL),CTRL_START,CTRL_END), "hrs", ""), ("Time to Solid Food — mean hrs", avg(cl_of(I_SOLID),CASE_START,CASE_END), avg(cl_of(I_SOLID),CTRL_START,CTRL_END), "hrs", ""), ("Time to Ambulation — mean hrs", avg(cl_of(I_AMBUL),CASE_START,CASE_END), avg(cl_of(I_AMBUL),CTRL_START,CTRL_END), "hrs", ""), ("Catheter Removal — mean hrs", avg(cl_of(I_CATH),CASE_START,CASE_END), avg(cl_of(I_CATH),CTRL_START,CTRL_END), "hrs", ""), ("PAIN & ANALGESIA",), ("NRS Pain @ 2h — mean", avg(cl_of(I_NRS2),CASE_START,CASE_END), avg(cl_of(I_NRS2),CTRL_START,CTRL_END), "0–10", ""), ("NRS Pain @ 6h — mean", avg(cl_of(I_NRS6),CASE_START,CASE_END), avg(cl_of(I_NRS6),CTRL_START,CTRL_END), "0–10", ""), ("NRS Pain @ 12h — mean", avg(cl_of(I_NRS12),CASE_START,CASE_END), avg(cl_of(I_NRS12),CTRL_START,CTRL_END), "0–10", ""), ("NRS Pain @ 24h — mean", avg(cl_of(I_NRS24),CASE_START,CASE_END), avg(cl_of(I_NRS24),CTRL_START,CTRL_END), "0–10", ""), ("1st Rescue Analgesia — mean hrs", avg(cl_of(I_RESCUE),CASE_START,CASE_END), avg(cl_of(I_RESCUE),CTRL_START,CTRL_END), "hrs", ""), ("Total Opioid Consumption 24h — mean mg ME", avg(cl_of(I_OPI),CASE_START,CASE_END), avg(cl_of(I_OPI),CTRL_START,CTRL_END), "mg ME", ""), ("PRIMARY OUTCOMES",), ("Length of Hospital Stay — mean hrs", avg(cl_of(I_LOS),CASE_START,CASE_END), avg(cl_of(I_LOS),CTRL_START,CTRL_END), "hrs", "PRIMARY endpoint"), ("Discharge Readiness — mean hrs", avg(cl_of(I_DISCH),CASE_START,CASE_END), avg(cl_of(I_DISCH),CTRL_START,CTRL_END), "hrs", ""), ("Breastfeeding Initiated — %", pct(cl_of(I_BF),CASE_START,CASE_END,N), pct(cl_of(I_BF),CTRL_START,CTRL_END,N), "%", ""), ("COMPLICATIONS",), ("Wound Infection — %", pct(cl_of(I_WI),CASE_START,CASE_END,N), pct(cl_of(I_WI),CTRL_START,CTRL_END,N), "%", ""), ("PPH > 1000 mL — %", pct(cl_of(I_PPH),CASE_START,CASE_END,N), pct(cl_of(I_PPH),CTRL_START,CTRL_END,N), "%", ""), ("Readmission ≤30 days — %", pct(cl_of(I_READT),CASE_START,CASE_END,N), pct(cl_of(I_READT),CTRL_START,CTRL_END,N), "%", ""), ("ICU Admission — %", pct(cl_of(I_ICU),CASE_START,CASE_END,N), pct(cl_of(I_ICU),CTRL_START,CTRL_END,N), "%", ""), ("NEONATAL",), ("Birth Weight — mean g", avg(cl_of(I_BW),CASE_START,CASE_END), avg(cl_of(I_BW),CTRL_START,CTRL_END), "g", ""), ("APGAR @ 1 min — mean", avg(cl_of(I_APG1),CASE_START,CASE_END), avg(cl_of(I_APG1),CTRL_START,CTRL_END), "0–10", ""), ("APGAR @ 5 min — mean", avg(cl_of(I_APG5),CASE_START,CASE_END), avg(cl_of(I_APG5),CTRL_START,CTRL_END), "0–10", ""), ("NICU Admission — %", pct(cl_of(I_NICU),CASE_START,CASE_END,N), pct(cl_of(I_NICU),CTRL_START,CTRL_END,N), "%", ""), ("SATISFACTION",), ("Patient Satisfaction — mean", avg(cl_of(I_SAT),CASE_START,CASE_END), avg(cl_of(I_SAT),CTRL_START,CTRL_END), "1–10", ""), ] data_row = 4 for row_data in sum_rows: if len(row_data) == 1: ws2.merge_cells(f"A{data_row}:F{data_row}") c = ws2[f"A{data_row}"] c.value = f" {row_data[0]}" c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(row_data[0].upper().split()[0], "2E75B6")) # find section color by key for sec_key, sec_col in SECTION_COLORS.items(): if sec_key.split()[0] in row_data[0].upper(): c.fill = fill(sec_col); break c.alignment = left() ws2.row_dimensions[data_row].height = 20 data_row += 1 continue label, case_f, ctrl_f, unit, note = row_data alt = GREY if data_row % 2 == 0 else WHITE ws2[f"A{data_row}"].value = label ws2[f"B{data_row}"].value = case_f ws2[f"C{data_row}"].value = ctrl_f ws2[f"D{data_row}"].value = f"=IFERROR(B{data_row}-C{data_row},\"\")" ws2[f"E{data_row}"].value = unit ws2[f"F{data_row}"].value = note for col_c in ["A","B","C","D","E","F"]: c = ws2[f"{col_c}{data_row}"] c.fill = fill(CASES_GREEN if col_c=="B" else (CTRL_AMBER if col_c=="C" else alt)) c.font = cell_font(bold=(col_c in ("A","D"))) c.alignment = left() if col_c in ("A","E","F") else center() c.border = thin_border() if col_c == "D": c.font = Font(name="Arial", bold=True, size=10, color="C00000") ws2[f"B{data_row}"].number_format = "0.00" ws2[f"C{data_row}"].number_format = "0.00" ws2[f"D{data_row}"].number_format = "0.00" if note == "PRIMARY endpoint": for col_c in ["A","B","C","D","E","F"]: ws2[f"{col_c}{data_row}"].font = Font( name="Arial", bold=True, size=10, color="C00000" if col_c=="D" else "000000") ws2.row_dimensions[data_row].height = 18 data_row += 1 # ──────────────────────────────────────────────────────────────────────────── # SHEET 4 – ERAS PROTOCOL CHECKLIST # ──────────────────────────────────────────────────────────────────────────── ws3 = wb.create_sheet("ERAS Protocol Checklist") ws3.merge_cells("A1:D1") ws3["A1"].value = "ERAS-CS PROTOCOL COMPLIANCE CHECKLIST" ws3["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws3["A1"].fill = fill(DARK_BLUE) ws3["A1"].alignment = center() ws3.row_dimensions[1].height = 30 ws3.merge_cells("A2:D2") ws3["A2"].value = "Per ERAS Society / RCOG / NICE Guidelines — tick each element applied" ws3["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws3["A2"].fill = fill(MID_BLUE) ws3["A2"].alignment = center() ws3.row_dimensions[2].height = 18 chk_headers = ["Phase", "ERAS Element", "Applied (Yes/No/NA)", "Remarks"] chk_widths = [20, 60, 20, 48] for i, (h, w) in enumerate(zip(chk_headers, chk_widths), start=1): cl_c = get_column_letter(i) c = ws3[f"{cl_c}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws3.column_dimensions[cl_c].width = w ws3.row_dimensions[3].height = 36 phase_colors = {"Pre-operative":"E2EFDA","Intra-operative":"DEEAF1","Post-operative":"FFF2CC"} checklist = [ ("Pre-operative","Patient education & counselling (written + verbal)"), ("Pre-operative","Carbohydrate loading — oral CHO drink ≤2 hrs before surgery"), ("Pre-operative","Minimum fasting: 6h solids / 2h clear fluids (no prolonged starving)"), ("Pre-operative","Antibiotic prophylaxis given BEFORE skin incision"), ("Pre-operative","Antacid prophylaxis (ranitidine / omeprazole) given"), ("Pre-operative","Anxiolytic pre-medication avoided / minimised"), ("Pre-operative","DVT prophylaxis: compression stockings applied"), ("Pre-operative","LMWH thromboprophylaxis (if indicated per risk score)"), ("Pre-operative","Anti-emetic prophylaxis administered"), ("Intra-operative","Spinal anaesthesia preferred (SAB or CSE); GA only if unavoidable"), ("Intra-operative","Intrathecal opioid (morphine/fentanyl) for post-op analgesia"), ("Intra-operative","Active patient warming — warmed IV fluids + forced-air blanket"), ("Intra-operative","Vasopressor infusion to prevent spinal-induced hypotension"), ("Intra-operative","Goal-directed IV fluid therapy — avoid excessive hydration"), ("Intra-operative","Oxytocin as slow IV infusion (not rapid bolus) to reduce PONV"), ("Intra-operative","Uterine exteriorisation avoided where possible"), ("Intra-operative","Intra-op PONV prophylaxis (dexamethasone ± ondansetron)"), ("Intra-operative","Skin-to-skin contact / early mother-baby bonding in OT"), ("Post-operative","Early oral hydration — clear fluids within 30 min post-op"), ("Post-operative","Early solid feeding — offered within 2 hrs post-op"), ("Post-operative","Multimodal analgesia: paracetamol + NSAID + low-dose opioid"), ("Post-operative","Urinary catheter removed within 12–24 hrs post-op"), ("Post-operative","Early mobilisation — ambulation within 8 hrs post-op"), ("Post-operative","Incentive spirometry / deep breathing exercises"), ("Post-operative","DVT/thromboprophylaxis continued post-op per protocol"), ("Post-operative","Breastfeeding support + lactation counselling"), ("Post-operative","Wound inspection before discharge"), ("Post-operative","Structured discharge criteria checklist completed"), ("Post-operative","Post-discharge follow-up planned (phone call 24–72 hrs)"), ] for row_i, (phase, element) in enumerate(checklist, start=4): ws3[f"A{row_i}"].value = phase ws3[f"B{row_i}"].value = element for col_c in ["A","B","C","D"]: c = ws3[f"{col_c}{row_i}"] c.fill = fill(phase_colors.get(phase, WHITE)) c.font = cell_font(bold=(col_c=="A")) c.alignment = left() c.border = thin_border() ws3.row_dimensions[row_i].height = 20 dv3 = DataValidation(type="list", formula1='"Yes,No,NA"', allow_blank=True, showErrorMessage=False) ws3.add_data_validation(dv3) dv3.add(f"C{row_i}") # ──────────────────────────────────────────────────────────────────────────── # SHEET 5 – LEGEND / CODEBOOK # ──────────────────────────────────────────────────────────────────────────── ws4 = wb.create_sheet("Legend & Codebook") ws4.column_dimensions["A"].width = 38 ws4.column_dimensions["B"].width = 26 ws4.column_dimensions["C"].width = 60 ws4.merge_cells("A1:C1") ws4["A1"].value = "LEGEND & VARIABLE CODEBOOK — ERAS-CS STUDY" ws4["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws4["A1"].fill = fill(DARK_BLUE) ws4["A1"].alignment = center() ws4.row_dimensions[1].height = 30 for i, h in enumerate(["Variable", "Allowed Values", "Definition / Notes"], start=1): c = ws4[f"{get_column_letter(i)}2"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws4.row_dimensions[2].height = 22 legend_rows = [ ("Group", "Case (ERAS) / Control (Std)","Cases = full ERAS protocol; Controls = standard perioperative care"), ("Singleton Pregnancy", "Yes / No", "Yes = inclusion criterion; No = exclude from study"), ("Multiple Pregnancy", "Excluded", "Twins / triplets / higher-order multiples — EXCLUSION criterion"), ("Fetal Anomaly", "Excluded", "Any structural or chromosomal anomaly — EXCLUSION criterion"), ("ASA Grade", "I / II / III", "American Society of Anaesthesiologists physical status classification"), ("Type of CS", "Elective / Emergency", "Elective = scheduled; Emergency = Category I–III unscheduled"), ("BMI", "Numeric (kg/m²)", "Pre-op body mass index = weight(kg) / height²(m)"), ("NRS Pain Score", "0–10", "Numeric Rating Scale: 0=no pain, 10=worst imaginable pain"), ("EBL", "Numeric (mL)", "Estimated blood loss (swab weighing + suction canister)"), ("Anaesthesia Type", "SAB / CSE / GA", "Spinal Block / Combined Spinal-Epidural / General Anaesthesia"), ("APGAR Score", "0–10", "Assessed at 1 min and 5 min by attending paediatrician"), ("Length of Stay", "Numeric (hours)", "From end of surgery to actual hospital discharge — PRIMARY outcome"), ("Discharge Readiness", "Numeric (hours)", "Time from surgery end to meeting all predefined discharge criteria"), ("Breastfeeding", "Yes / No", "Skin-to-skin contact and first latch within 1h of delivery"), ("Wound Infection", "Yes / No", "CDC SSI criteria (superficial / deep / organ-space)"), ("PPH > 1000 mL", "Yes / No", "Postpartum haemorrhage: estimated blood loss > 1000 mL"), ("Readmission", "Yes / No", "Unplanned readmission within 30 days of initial discharge"), ("DVT / PE", "Yes / No", "Confirmed by Doppler/CTPA"), ("Opioid Consumption", "Numeric (mg ME)", "Total 24h opioid in morphine equivalents"), ("Satisfaction Score", "1–10", "Patient satisfaction questionnaire at discharge (10=fully satisfied)"), ("Neonatal Outcome", "Good / Fair / Poor", "Good=no NICU/normal APGAR; Fair=brief obs; Poor=prolonged NICU"), ("Multimodal Analgesia", "Yes / No", "≥2 analgesic classes: paracetamol + NSAID + low-dose opioid"), ("LMWH", "Yes / No / NA", "Low Molecular Weight Heparin; NA if not clinically indicated"), ("Vasopressor", "Yes / No / Agent", "e.g. Phenylephrine infusion or Ephedrine bolus"), ("Intrathecal Opioid", "Numeric (µg)", "Dose of intrathecal fentanyl or morphine in micrograms"), ("mg ME", "Morphine Equivalent (mg)", "Oxycodone 10mg=15mg ME; Tramadol 10mg=1mg ME; Codeine 10mg=1mg ME"), ("Carb Loading", "Yes / No", "Oral CHO drink (e.g. Nutricia PreOp) given ≤2h before surgery"), ("PONV", "Yes / No", "Post-operative nausea/vomiting requiring pharmacological treatment"), ] for row_i, (var, vals, defn) in enumerate(legend_rows, start=3): fc = GREY if row_i % 2 == 0 else WHITE ws4[f"A{row_i}"].value = var ws4[f"B{row_i}"].value = vals ws4[f"C{row_i}"].value = defn for col_c in ["A","B","C"]: c = ws4[f"{col_c}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col_c=="A")) c.alignment = left() c.border = thin_border() ws4.row_dimensions[row_i].height = 20 # ── Save ───────────────────────────────────────────────────────────────────── os.makedirs("/tmp/workspace/eras-cs", exist_ok=True) out = "/tmp/workspace/eras-cs/ERAS_CS_DataCollection_v2.xlsx" wb.save(out) print(f"Saved: {out}") print(f"File size: {os.path.getsize(out)//1024} KB") print(f"Sheets: {[s.title for s in wb.worksheets]}") print(f"Cases rows: {CASE_START}–{CASE_END} (n={N})") print(f"Controls rows: {CTRL_START}–{CTRL_END} (n={N})") PYEOF

python3 << 'PYEOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation import os, random random.seed(42) # ── Palette ────────────────────────────────────────────────────────────────── DARK_BLUE = "1F3864" MID_BLUE = "2E75B6" CASES_GREEN = "E2EFDA" CTRL_AMBER = "FFF2CC" PILOT_PURPLE = "E8D5F5" WHITE = "FFFFFF" GREY = "F2F2F2" SAMPLE_BLUE = "DEEAF1" WARN_YELLOW = "FFFF99" RED_TEXT = "C00000" SECTION_COLORS = { "IDENTIFIERS": "1F3864", "DEMOGRAPHICS": "2E75B6", "PRE-OP ERAS": "375623", "INTRA-OP": "7030A0", "POST-OP": "843C0C", "PAIN & ANALGESIA": "C00000", "PRIMARY OUTCOMES": "0070C0", "COMPLICATIONS": "833C00", "NEONATAL": "403152", "SATISFACTION": "375623", } def fill(h): return PatternFill("solid", fgColor=h) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) def cell_font(bold=False, size=10, color="000000"): return Font(name="Arial", bold=bold, size=size, color=color) def thin_border(): s = Side(style="thin", color="CCCCCC") return Border(left=s, right=s, top=s, bottom=s) def thick_border(): s = Side(style="medium", color="2E75B6") return Border(left=s, right=s, top=s, bottom=s) COLUMNS = [ ("IDENTIFIERS", "S.No", 6), ("IDENTIFIERS", "Patient\nID / MRN", 16), ("IDENTIFIERS", "Group\n(Case/Control)", 16), ("IDENTIFIERS", "Date of\nSurgery\n(DD/MM/YY)", 14), ("IDENTIFIERS", "Surgeon", 16), ("IDENTIFIERS", "Anaesthetist", 16), ("DEMOGRAPHICS", "Age\n(yrs)", 10), ("DEMOGRAPHICS", "Gravida", 10), ("DEMOGRAPHICS", "Para", 10), ("DEMOGRAPHICS", "BMI\n(kg/m²)", 12), ("DEMOGRAPHICS", "Gestational\nAge (wks)", 14), ("DEMOGRAPHICS", "Type of CS\n(Elective/\nEmergency)", 16), ("DEMOGRAPHICS", "Singleton\n(Yes/No)", 14), ("DEMOGRAPHICS", "Previous CS\n(Yes/No)", 14), ("DEMOGRAPHICS", "ASA\nGrade", 12), ("DEMOGRAPHICS", "Comorbidities", 22), ("PRE-OP ERAS", "Counselling\n(Yes/No)", 14), ("PRE-OP ERAS", "Carb Loading\n(Yes/No)", 14), ("PRE-OP ERAS", "Fasting\nSolids (hrs)", 12), ("PRE-OP ERAS", "Fasting\nLiquids (hrs)", 12), ("PRE-OP ERAS", "Anxiolytic\nPre-med\n(Yes/No)", 14), ("PRE-OP ERAS", "Antibiotic\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Antacid\nProphylaxis\n(Yes/No)", 14), ("PRE-OP ERAS", "Compression\nStockings\n(Yes/No)", 14), ("PRE-OP ERAS", "LMWH\n(Yes/No/NA)", 14), ("PRE-OP ERAS", "Anti-emetic\n(Yes/No)", 14), ("INTRA-OP", "Anaesthesia\n(SAB/CSE/GA)", 16), ("INTRA-OP", "IT Opioid\nDose (µg)", 14), ("INTRA-OP", "IV Fluids\n(mL)", 14), ("INTRA-OP", "Vasopressor\n(Yes/No)", 14), ("INTRA-OP", "Skin–Delivery\nTime (min)", 14), ("INTRA-OP", "Total Op\nTime (min)", 14), ("INTRA-OP", "EBL\n(mL)", 12), ("INTRA-OP", "Oxytocin\nIU", 12), ("INTRA-OP", "Active\nWarming\n(Yes/No)", 14), ("INTRA-OP", "Intra-op\nPONV\n(Yes/No)", 14), ("POST-OP", "Oral Fluids\n(hrs)", 13), ("POST-OP", "Solid Food\n(hrs)", 13), ("POST-OP", "Ambulation\n(hrs)", 13), ("POST-OP", "Catheter\nRemoval (hrs)", 13), ("POST-OP", "IV Fluid\nDuration (hrs)", 13), ("POST-OP", "Post-op\nPONV\n(Yes/No)", 13), ("POST-OP", "Shivering\n(Yes/No)", 13), ("PAIN & ANALGESIA", "NRS\n@ 2h", 11), ("PAIN & ANALGESIA", "NRS\n@ 6h", 11), ("PAIN & ANALGESIA", "NRS\n@ 12h", 11), ("PAIN & ANALGESIA", "NRS\n@ 24h", 11), ("PAIN & ANALGESIA", "Rescue\nAnalgesia\n(hrs)", 13), ("PAIN & ANALGESIA", "Total Opioid\n(mg ME)", 14), ("PAIN & ANALGESIA", "Multimodal\n(Yes/No)", 13), ("PRIMARY OUTCOMES", "LOS\n(hrs)", 12), ("PRIMARY OUTCOMES", "Discharge\nReadiness\n(hrs)", 15), ("PRIMARY OUTCOMES", "Breastfeeding\n(Yes/No)", 14), ("PRIMARY OUTCOMES", "Bonding\nScore", 12), ("COMPLICATIONS", "Wound\nInfection", 12), ("COMPLICATIONS", "PPH\n>1000mL", 12), ("COMPLICATIONS", "Urinary\nRetention", 12), ("COMPLICATIONS", "DVT/PE", 11), ("COMPLICATIONS", "Readmission\n≤30d", 13), ("COMPLICATIONS", "ICU", 10), ("COMPLICATIONS", "Other\nComplications", 22), ("NEONATAL", "Birth\nWt (g)", 12), ("NEONATAL", "APGAR\n@1 min", 12), ("NEONATAL", "APGAR\n@5 min", 12), ("NEONATAL", "NICU", 10), ("NEONATAL", "Neonatal\nOutcome", 15), ("SATISFACTION", "Satisfaction\n(1–10)", 14), ("SATISFACTION", "Remarks", 30), ] TOTAL_COLS = len(COLUMNS) last_col = get_column_letter(TOTAL_COLS) # ── Realistic sample data ──────────────────────────────────────────────────── # Format: list of dicts keyed by col index (0-based) # CASES (ERAS group) — better outcomes expected cases_data = [ # P1 {"mrn":"ERAS-001","date":"03/01/2025","surg":"Dr. Sharma","anaes":"Dr. Patel", "age":26,"grav":2,"para":1,"bmi":23.4,"ga":38,"cs_type":"Elective","prev_cs":"Yes","asa":"II","comorbid":"Nil", "couns":"Yes","carb":"Yes","fast_s":6,"fast_l":2,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"Yes","lmwh":"NA","antiemetic":"Yes", "anaes_t":"SAB","it_opi":25,"ivfl":800,"vaso":"Yes","skin_del":8,"op_time":42,"ebl":350,"oxytocin":5,"warming":"Yes","intra_ponv":"No", "oral_fl":0.5,"solid":2,"ambul":6,"cath":12,"iv_dur":4,"post_ponv":"No","shiver":"No", "nrs2":3,"nrs6":2,"nrs12":2,"nrs24":1,"rescue":8,"opioid":4,"multi":"Yes", "los":36,"disch_ready":30,"bf":"Yes","bond":9, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":2960,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":9,"remarks":"Smooth recovery; EBF initiated in OT"}, # P2 {"mrn":"ERAS-002","date":"05/01/2025","surg":"Dr. Sharma","anaes":"Dr. Patel", "age":31,"grav":1,"para":0,"bmi":27.1,"ga":39,"cs_type":"Emergency","prev_cs":"No","asa":"II","comorbid":"GDM", "couns":"Yes","carb":"No","fast_s":6,"fast_l":2,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"Yes","lmwh":"NA","antiemetic":"Yes", "anaes_t":"CSE","it_opi":25,"ivfl":900,"vaso":"Yes","skin_del":10,"op_time":50,"ebl":400,"oxytocin":5,"warming":"Yes","intra_ponv":"No", "oral_fl":0.5,"solid":2,"ambul":7,"cath":14,"iv_dur":5,"post_ponv":"No","shiver":"No", "nrs2":4,"nrs6":3,"nrs12":2,"nrs24":2,"rescue":6,"opioid":6,"multi":"Yes", "los":40,"disch_ready":36,"bf":"Yes","bond":8, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3200,"apg1":7,"apg5":9,"nicu":"No","neo_out":"Good", "sat":8,"remarks":"Emergency CS, ERAS partially applied; carb loading not possible"}, # P3 {"mrn":"ERAS-003","date":"08/01/2025","surg":"Dr. Kumar","anaes":"Dr. Patel", "age":28,"grav":3,"para":2,"bmi":24.8,"ga":37,"cs_type":"Elective","prev_cs":"Yes","asa":"I","comorbid":"Nil", "couns":"Yes","carb":"Yes","fast_s":6,"fast_l":2,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"Yes","lmwh":"NA","antiemetic":"Yes", "anaes_t":"SAB","it_opi":25,"ivfl":700,"vaso":"Yes","skin_del":7,"op_time":38,"ebl":300,"oxytocin":5,"warming":"Yes","intra_ponv":"No", "oral_fl":0.5,"solid":1.5,"ambul":5,"cath":10,"iv_dur":3,"post_ponv":"No","shiver":"No", "nrs2":2,"nrs6":2,"nrs12":1,"nrs24":1,"rescue":10,"opioid":2,"multi":"Yes", "los":30,"disch_ready":24,"bf":"Yes","bond":10, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3050,"apg1":9,"apg5":9,"nicu":"No","neo_out":"Good", "sat":10,"remarks":"Excellent recovery; discharged at 30h"}, # P4 {"mrn":"ERAS-004","date":"10/01/2025","surg":"Dr. Kumar","anaes":"Dr. Singh", "age":34,"grav":2,"para":1,"bmi":30.2,"ga":38,"cs_type":"Elective","prev_cs":"Yes","asa":"II","comorbid":"Hypothyroidism", "couns":"Yes","carb":"Yes","fast_s":6,"fast_l":2,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"Yes","lmwh":"Yes","antiemetic":"Yes", "anaes_t":"SAB","it_opi":25,"ivfl":850,"vaso":"Yes","skin_del":9,"op_time":45,"ebl":380,"oxytocin":5,"warming":"Yes","intra_ponv":"No", "oral_fl":0.5,"solid":2,"ambul":7,"cath":12,"iv_dur":4,"post_ponv":"Yes","shiver":"No", "nrs2":3,"nrs6":3,"nrs12":2,"nrs24":2,"rescue":7,"opioid":5,"multi":"Yes", "los":42,"disch_ready":38,"bf":"Yes","bond":8, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3100,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":8,"remarks":"PONV episode at 3h; managed with ondansetron"}, # P5 {"mrn":"ERAS-005","date":"12/01/2025","surg":"Dr. Sharma","anaes":"Dr. Singh", "age":22,"grav":1,"para":0,"bmi":21.5,"ga":40,"cs_type":"Emergency","prev_cs":"No","asa":"I","comorbid":"Nil", "couns":"Yes","carb":"No","fast_s":5,"fast_l":2,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"Yes","lmwh":"NA","antiemetic":"Yes", "anaes_t":"SAB","it_opi":25,"ivfl":1000,"vaso":"Yes","skin_del":11,"op_time":52,"ebl":450,"oxytocin":5,"warming":"Yes","intra_ponv":"No", "oral_fl":0.5,"solid":2,"ambul":6,"cath":12,"iv_dur":4,"post_ponv":"No","shiver":"Yes", "nrs2":4,"nrs6":3,"nrs12":2,"nrs24":2,"rescue":5,"opioid":6,"multi":"Yes", "los":38,"disch_ready":34,"bf":"Yes","bond":9, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3400,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":9,"remarks":"Emergency CS; shivering managed with pethidine 25mg"}, ] # CONTROLS (Standard care) — longer stays, more pain, delayed feeding controls_data = [ # P1 {"mrn":"STD-001","date":"04/01/2025","surg":"Dr. Sharma","anaes":"Dr. Patel", "age":27,"grav":2,"para":1,"bmi":24.0,"ga":38,"cs_type":"Elective","prev_cs":"Yes","asa":"II","comorbid":"Nil", "couns":"No","carb":"No","fast_s":8,"fast_l":6,"anxio":"Yes","ab":"Yes","antacid":"Yes","stocks":"No","lmwh":"NA","antiemetic":"No", "anaes_t":"SAB","it_opi":0,"ivfl":1500,"vaso":"No","skin_del":10,"op_time":50,"ebl":450,"oxytocin":10,"warming":"No","intra_ponv":"Yes", "oral_fl":6,"solid":12,"ambul":18,"cath":24,"iv_dur":24,"post_ponv":"Yes","shiver":"Yes", "nrs2":6,"nrs6":5,"nrs12":5,"nrs24":4,"rescue":2,"opioid":18,"multi":"No", "los":72,"disch_ready":60,"bf":"No","bond":5, "wi":"No","pph":"No","ur":"Yes","dvt":"No","readm":"No","icu":"No","other":"Urinary retention managed with recatheterisation", "bwt":2980,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":5,"remarks":"Prolonged stay; pain poorly controlled D1"}, # P2 {"mrn":"STD-002","date":"06/01/2025","surg":"Dr. Sharma","anaes":"Dr. Patel", "age":32,"grav":1,"para":0,"bmi":28.0,"ga":39,"cs_type":"Emergency","prev_cs":"No","asa":"II","comorbid":"GDM", "couns":"No","carb":"No","fast_s":10,"fast_l":6,"anxio":"Yes","ab":"Yes","antacid":"Yes","stocks":"No","lmwh":"NA","antiemetic":"No", "anaes_t":"SAB","it_opi":0,"ivfl":1800,"vaso":"No","skin_del":12,"op_time":58,"ebl":500,"oxytocin":10,"warming":"No","intra_ponv":"Yes", "oral_fl":8,"solid":14,"ambul":20,"cath":24,"iv_dur":24,"post_ponv":"Yes","shiver":"Yes", "nrs2":7,"nrs6":6,"nrs12":5,"nrs24":4,"rescue":1,"opioid":24,"multi":"No", "los":80,"disch_ready":68,"bf":"No","bond":4, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3150,"apg1":7,"apg5":9,"nicu":"No","neo_out":"Good", "sat":5,"remarks":"Significant nausea D0; late ambulation D2"}, # P3 {"mrn":"STD-003","date":"09/01/2025","surg":"Dr. Kumar","anaes":"Dr. Patel", "age":29,"grav":3,"para":2,"bmi":25.5,"ga":37,"cs_type":"Elective","prev_cs":"Yes","asa":"I","comorbid":"Nil", "couns":"No","carb":"No","fast_s":8,"fast_l":4,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"No","lmwh":"NA","antiemetic":"No", "anaes_t":"SAB","it_opi":0,"ivfl":1400,"vaso":"No","skin_del":9,"op_time":46,"ebl":380,"oxytocin":10,"warming":"No","intra_ponv":"No", "oral_fl":6,"solid":12,"ambul":20,"cath":24,"iv_dur":20,"post_ponv":"No","shiver":"No", "nrs2":5,"nrs6":5,"nrs12":4,"nrs24":3,"rescue":3,"opioid":14,"multi":"No", "los":64,"disch_ready":56,"bf":"Yes","bond":6, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3020,"apg1":9,"apg5":9,"nicu":"No","neo_out":"Good", "sat":6,"remarks":"Routine standard care; discharged D3"}, # P4 {"mrn":"STD-004","date":"11/01/2025","surg":"Dr. Kumar","anaes":"Dr. Singh", "age":35,"grav":2,"para":1,"bmi":31.0,"ga":38,"cs_type":"Elective","prev_cs":"Yes","asa":"II","comorbid":"Hypothyroidism", "couns":"No","carb":"No","fast_s":8,"fast_l":6,"anxio":"Yes","ab":"Yes","antacid":"Yes","stocks":"No","lmwh":"Yes","antiemetic":"No", "anaes_t":"SAB","it_opi":0,"ivfl":1600,"vaso":"No","skin_del":11,"op_time":52,"ebl":420,"oxytocin":10,"warming":"No","intra_ponv":"Yes", "oral_fl":8,"solid":14,"ambul":22,"cath":24,"iv_dur":24,"post_ponv":"Yes","shiver":"No", "nrs2":6,"nrs6":6,"nrs12":5,"nrs24":4,"rescue":2,"opioid":20,"multi":"No", "los":78,"disch_ready":66,"bf":"No","bond":5, "wi":"Yes","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Superficial SSI D4; dressed with betadine", "bwt":3080,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":5,"remarks":"Wound infection D4; antibiotics extended 5 days"}, # P5 {"mrn":"STD-005","date":"13/01/2025","surg":"Dr. Sharma","anaes":"Dr. Singh", "age":23,"grav":1,"para":0,"bmi":22.0,"ga":40,"cs_type":"Emergency","prev_cs":"No","asa":"I","comorbid":"Nil", "couns":"No","carb":"No","fast_s":6,"fast_l":4,"anxio":"No","ab":"Yes","antacid":"Yes","stocks":"No","lmwh":"NA","antiemetic":"No", "anaes_t":"SAB","it_opi":0,"ivfl":1500,"vaso":"No","skin_del":13,"op_time":55,"ebl":480,"oxytocin":10,"warming":"No","intra_ponv":"No", "oral_fl":6,"solid":12,"ambul":18,"cath":24,"iv_dur":20,"post_ponv":"No","shiver":"Yes", "nrs2":5,"nrs6":4,"nrs12":4,"nrs24":3,"rescue":3,"opioid":16,"multi":"No", "los":68,"disch_ready":60,"bf":"Yes","bond":6, "wi":"No","pph":"No","ur":"No","dvt":"No","readm":"No","icu":"No","other":"Nil", "bwt":3380,"apg1":8,"apg5":9,"nicu":"No","neo_out":"Good", "sat":6,"remarks":"Discharged D3; pain better D2"}, ] # ── Build workbook ──────────────────────────────────────────────────────────── wb = openpyxl.Workbook() # ════════════════════════════════════════════════════════════════════════════ # SHEET 1 – PILOT DATA ENTRY (with sample records) # ════════════════════════════════════════════════════════════════════════════ ws = wb.active ws.title = "Pilot Data Entry" ws.freeze_panes = "C7" # Row 1 – Main title ws.merge_cells(f"A1:{last_col}1") ws["A1"].value = "ERAS-CS STUDY — PILOT DATA ENTRY TEMPLATE (Sample Patient Records)" ws["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws["A1"].fill = fill(DARK_BLUE) ws["A1"].alignment = center() ws.row_dimensions[1].height = 36 # Row 2 – Pilot notice ws.merge_cells(f"A2:{last_col}2") ws["A2"].value = "⚠ PILOT TEMPLATE — Contains 5 sample CASES + 5 sample CONTROLS for training and validation purposes only. Replace with real patient data before formal data collection." ws["A2"].font = Font(name="Arial", bold=True, size=10, color="7B3F00") ws["A2"].fill = fill(WARN_YELLOW) ws["A2"].alignment = center() ws.row_dimensions[2].height = 22 # Row 3 – Study info ws.merge_cells(f"A3:{last_col}3") ws["A3"].value = "Inclusion: Elective & Emergency CS · Singleton Pregnancies · ASA I–III · Age ≥18 yrs · Written consent | Exclusion: Multiple Pregnancy · Fetal Anomaly · GA cases · ASA IV/V · Coagulopathy" ws["A3"].font = Font(name="Arial", italic=True, size=9, color="FFFFFF") ws["A3"].fill = fill(MID_BLUE) ws["A3"].alignment = center() ws.row_dimensions[3].height = 18 # Row 4 – Group banner ws.merge_cells(f"A4:{last_col}4") ws["A4"].value = "▶ ROWS 7–11 = CASES (ERAS GROUP, n=5 sample) | ROWS 13–17 = CONTROLS (STANDARD CARE, n=5 sample)" ws["A4"].font = Font(name="Arial", bold=True, size=10, color="FFFFFF") ws["A4"].fill = fill("1A5276") ws["A4"].alignment = center() ws.row_dimensions[4].height = 20 # Row 5 – Section headers sec_ranges = {} prev = None; st = 1 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): if sec != prev: if prev: sec_ranges[prev] = (st, i-1) prev = sec; st = i sec_ranges[prev] = (st, TOTAL_COLS) ws.row_dimensions[5].height = 22 for sec, (c1, c2) in sec_ranges.items(): l1, l2 = get_column_letter(c1), get_column_letter(c2) if c1 != c2: ws.merge_cells(f"{l1}5:{l2}5") c = ws[f"{l1}5"] c.value = sec c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="medium", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) # Row 6 – Column headers ws.row_dimensions[6].height = 50 for i, (sec, hdr, wid) in enumerate(COLUMNS, start=1): cl = get_column_letter(i) c = ws[f"{cl}6"] c.value = hdr c.font = Font(name="Arial", bold=True, size=9, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(sec, "2E75B6")) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws.column_dimensions[cl].width = wid # ── Helper to write one patient row ────────────────────────────────────────── def write_patient(row, serial, group, d, fc): vals = [ serial, d["mrn"], group, d["date"], d["surg"], d["anaes"], d["age"], d["grav"], d["para"], d["bmi"], d["ga"], d["cs_type"], "Yes", d["prev_cs"], d["asa"], d["comorbid"], d["couns"], d["carb"], d["fast_s"], d["fast_l"], d["anxio"], d["ab"], d["antacid"], d["stocks"], d["lmwh"], d["antiemetic"], d["anaes_t"], d["it_opi"], d["ivfl"], d["vaso"], d["skin_del"], d["op_time"], d["ebl"], d["oxytocin"], d["warming"], d["intra_ponv"], d["oral_fl"], d["solid"], d["ambul"], d["cath"], d["iv_dur"], d["post_ponv"], d["shiver"], d["nrs2"], d["nrs6"], d["nrs12"], d["nrs24"], d["rescue"], d["opioid"], d["multi"], d["los"], d["disch_ready"], d["bf"], d["bond"], d["wi"], d["pph"], d["ur"], d["dvt"], d["readm"], d["icu"], d["other"], d["bwt"], d["apg1"], d["apg5"], d["nicu"], d["neo_out"], d["sat"], d["remarks"], ] for col_i, val in enumerate(vals, start=1): cl = get_column_letter(col_i) c = ws[f"{cl}{row}"] c.value = val c.fill = fill(fc) c.font = cell_font(bold=(col_i == 3)) c.alignment = center() if col_i in (1, 3, 4, 7, 8, 9, 10, 11, 15, 19, 20, 28, 30, 31, 32, 33, 34, 37, 38, 39, 40, 44, 45, 46, 47, 48, 49, 51, 52, 54) else left() c.border = thin_border() ws.row_dimensions[row].height = 18 # Cases header banner ws.merge_cells(f"A7:{last_col}7") # Actually rows shift — let me use row separators differently # Actually write rows 7-11 for cases, then a separator at 12, then 13-17 for controls # ── CASES sub-header row ───────────────────────────────────────────────── # Row 7: cases label CASES_HDR_ROW = 7 ws.merge_cells(f"A{CASES_HDR_ROW}:{last_col}{CASES_HDR_ROW}") ws[f"A{CASES_HDR_ROW}"].value = " CASES — ERAS PROTOCOL GROUP (n=5 sample patients)" ws[f"A{CASES_HDR_ROW}"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws[f"A{CASES_HDR_ROW}"].fill = fill("375623") ws[f"A{CASES_HDR_ROW}"].alignment = left() ws.row_dimensions[CASES_HDR_ROW].height = 22 CASE_DATA_START = 8 for i, d in enumerate(cases_data): write_patient(CASE_DATA_START + i, i+1, "Case (ERAS)", d, CASES_GREEN) # Separator row SEP1 = CASE_DATA_START + len(cases_data) # 13 ws.merge_cells(f"A{SEP1}:{last_col}{SEP1}") ws[f"A{SEP1}"].fill = fill("D9D9D9") ws.row_dimensions[SEP1].height = 8 # Controls sub-header CTRL_HDR_ROW = SEP1 + 1 # 14 ws.merge_cells(f"A{CTRL_HDR_ROW}:{last_col}{CTRL_HDR_ROW}") ws[f"A{CTRL_HDR_ROW}"].value = " CONTROLS — STANDARD CARE GROUP (n=5 sample patients)" ws[f"A{CTRL_HDR_ROW}"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws[f"A{CTRL_HDR_ROW}"].fill = fill("843C0C") ws[f"A{CTRL_HDR_ROW}"].alignment = left() ws.row_dimensions[CTRL_HDR_ROW].height = 22 CTRL_DATA_START = CTRL_HDR_ROW + 1 # 15 for i, d in enumerate(controls_data): write_patient(CTRL_DATA_START + i, i+1, "Control (Std)", d, CTRL_AMBER) # Blank rows for additional data entry SEP2 = CTRL_DATA_START + len(controls_data) # 20 ws.merge_cells(f"A{SEP2}:{last_col}{SEP2}") ws[f"A{SEP2}"].fill = fill("D9D9D9") ws.row_dimensions[SEP2].height = 8 BLANK_START = SEP2 + 1 ws.merge_cells(f"A{BLANK_START}:{last_col}{BLANK_START}") ws[f"A{BLANK_START}"].value = " ↓ Add new patient entries below this row (continue cases first, then controls)" ws[f"A{BLANK_START}"].font = Font(name="Arial", italic=True, size=10, color="555555") ws[f"A{BLANK_START}"].fill = fill("F2F2F2") ws[f"A{BLANK_START}"].alignment = left() ws.row_dimensions[BLANK_START].height = 20 # Add 10 blank entry rows for r in range(BLANK_START+1, BLANK_START+11): for i in range(1, TOTAL_COLS+1): cl = get_column_letter(i) c = ws[f"{cl}{r}"] c.fill = fill(WHITE) c.border = thin_border() c.font = cell_font() ws.row_dimensions[r].height = 18 # ════════════════════════════════════════════════════════════════════════════ # SHEET 2 – PILOT COMPARISON (mini summary of 5 vs 5) # ════════════════════════════════════════════════════════════════════════════ ws2 = wb.create_sheet("Pilot Comparison") ws2.freeze_panes = "B4" ws2.merge_cells("A1:F1") ws2["A1"].value = "PILOT STUDY — CASES vs. CONTROLS COMPARISON (n=5 per group)" ws2["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws2["A1"].fill = fill(DARK_BLUE) ws2["A1"].alignment = center() ws2.row_dimensions[1].height = 32 ws2.merge_cells("A2:F2") ws2["A2"].value = "⚠ Pilot data only — not for formal statistical inference. Use to validate data entry and identify field issues." ws2["A2"].font = Font(name="Arial", italic=True, size=10, color="7B3F00") ws2["A2"].fill = fill(WARN_YELLOW) ws2["A2"].alignment = center() ws2.row_dimensions[2].height = 18 s_hdrs = ["Outcome Parameter", "CASES\n(ERAS, n=5)", "CONTROLS\n(Std Care, n=5)", "Difference\n(C–T)", "Expected\nDirection", "Notes"] s_widths = [42, 18, 18, 18, 18, 40] for i, (h, w) in enumerate(zip(s_hdrs, s_widths), start=1): cl = get_column_letter(i) c = ws2[f"{cl}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws2.column_dimensions[cl].width = w ws2.row_dimensions[3].height = 40 # Reference row ranges for pilot data # Cases = rows 8–12, Controls = rows 15–19 on Pilot Data Entry sheet C1, C2 = 8, 12 T1, T2 = 15, 19 SHT = "'Pilot Data Entry'" def avg2(col_l, r1, r2): return f"=IFERROR(AVERAGE({SHT}!{col_l}{r1}:{col_l}{r2}),\"\")" def pct2(col_l, r1, r2, n=5, val="Yes"): return f'=IFERROR(COUNTIF({SHT}!{col_l}{r1}:{col_l}{r2},"{val}")/{n}*100,"")' # 0-based col indices (same COLUMNS list): # age=6, bmi=9, ga=10, fast_s=18, fast_l=19, ivfl=28 # oral_fl=36, solid=37, ambul=38, cath=39 # nrs2=43,nrs6=44,nrs12=45,nrs24=46, opioid=48 # los=50, disch=51, bf=52 # wi=54, opioid_col=48 def cl0(zero_idx): return get_column_letter(zero_idx+1) pilot_rows = [ ("DEMOGRAPHICS",), ("Age — mean (yrs)", avg2(cl0(6),C1,C2), avg2(cl0(6),T1,T2), "Years", "–", "Baseline comparability"), ("BMI — mean (kg/m²)", avg2(cl0(9),C1,C2), avg2(cl0(9),T1,T2), "kg/m²", "–", "Baseline comparability"), ("PRE-OPERATIVE",), ("Fasting – Solids (mean hrs)", avg2(cl0(18),C1,C2), avg2(cl0(18),T1,T2), "hrs", "Cases < Controls", "ERAS restricts fasting"), ("Fasting – Liquids (mean hrs)", avg2(cl0(19),C1,C2), avg2(cl0(19),T1,T2), "hrs", "Cases < Controls", ""), ("INTRA-OPERATIVE",), ("IV Fluids (mean mL)", avg2(cl0(28),C1,C2), avg2(cl0(28),T1,T2), "mL", "Cases < Controls", "Goal-directed fluids"), ("Op Time (mean min)", avg2(cl0(31),C1,C2), avg2(cl0(31),T1,T2), "min", "–", "Should be similar"), ("EBL (mean mL)", avg2(cl0(32),C1,C2), avg2(cl0(32),T1,T2), "mL", "Cases ≤ Controls", ""), ("POST-OPERATIVE",), ("Time to Oral Fluids (mean hrs)", avg2(cl0(36),C1,C2), avg2(cl0(36),T1,T2), "hrs", "Cases << Controls", "Key ERAS element"), ("Time to Solid Food (mean hrs)", avg2(cl0(37),C1,C2), avg2(cl0(37),T1,T2), "hrs", "Cases << Controls", ""), ("Time to Ambulation (mean hrs)", avg2(cl0(38),C1,C2), avg2(cl0(38),T1,T2), "hrs", "Cases << Controls", ""), ("Catheter Removal (mean hrs)", avg2(cl0(39),C1,C2), avg2(cl0(39),T1,T2), "hrs", "Cases < Controls", ""), ("PAIN",), ("NRS Pain @ 2h (mean)", avg2(cl0(43),C1,C2), avg2(cl0(43),T1,T2), "0–10", "Cases < Controls", "IT opioid benefit"), ("NRS Pain @ 24h (mean)", avg2(cl0(46),C1,C2), avg2(cl0(46),T1,T2), "0–10", "Cases < Controls", ""), ("Total Opioid Consumption (mean mg ME)", avg2(cl0(48),C1,C2), avg2(cl0(48),T1,T2), "mg ME", "Cases < Controls", "Opioid-sparing"), ("PRIMARY OUTCOME",), ("Length of Hospital Stay (mean hrs) ★", avg2(cl0(50),C1,C2), avg2(cl0(50),T1,T2), "hrs", "Cases << Controls", "PRIMARY endpoint"), ("Discharge Readiness (mean hrs)", avg2(cl0(51),C1,C2), avg2(cl0(51),T1,T2), "hrs", "Cases < Controls", ""), ("Breastfeeding Initiated (%)", pct2(cl0(52),C1,C2), pct2(cl0(52),T1,T2), "%", "Cases > Controls", ""), ("Patient Satisfaction (mean)", avg2(cl0(67),C1,C2), avg2(cl0(67),T1,T2), "1–10", "Cases > Controls", ""), ] d_row = 4 for row_data in pilot_rows: if len(row_data) == 1: ws2.merge_cells(f"A{d_row}:F{d_row}") c = ws2[f"A{d_row}"] c.value = f" {row_data[0]}" c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(SECTION_COLORS.get(row_data[0].upper().split()[0][:10], "2E75B6")) for k, v in SECTION_COLORS.items(): if k.split()[0] in row_data[0].upper(): c.fill = fill(v); break c.alignment = left() ws2.row_dimensions[d_row].height = 20 d_row += 1 continue label, case_f, ctrl_f, unit, direction, note = row_data alt = GREY if d_row % 2 == 0 else WHITE ws2[f"A{d_row}"].value = label ws2[f"B{d_row}"].value = case_f ws2[f"C{d_row}"].value = ctrl_f ws2[f"D{d_row}"].value = f"=IFERROR(B{d_row}-C{d_row},\"\")" ws2[f"E{d_row}"].value = direction ws2[f"F{d_row}"].value = note for col_c in ["A","B","C","D","E","F"]: c = ws2[f"{col_c}{d_row}"] c.fill = fill(CASES_GREEN if col_c=="B" else CTRL_AMBER if col_c=="C" else alt) c.font = cell_font(bold=(col_c in ("A",))) c.alignment = left() if col_c in ("A","E","F") else center() c.border = thin_border() if col_c == "D": c.font = Font(name="Arial", bold=True, size=10, color=RED_TEXT) ws2[f"B{d_row}"].number_format = "0.0" ws2[f"C{d_row}"].number_format = "0.0" ws2[f"D{d_row}"].number_format = "0.0" if "PRIMARY" in label or "★" in label: for col_c in ["A","B","C","D","E","F"]: c = ws2[f"{col_c}{d_row}"] c.fill = fill("D6E4F7") c.font = Font(name="Arial", bold=True, size=10, color=RED_TEXT if col_c=="D" else "000000") ws2.row_dimensions[d_row].height = 18 d_row += 1 # ════════════════════════════════════════════════════════════════════════════ # SHEET 3 – DATA ENTRY GUIDE # ════════════════════════════════════════════════════════════════════════════ ws_guide = wb.create_sheet("Data Entry Guide") ws_guide.column_dimensions["A"].width = 28 ws_guide.column_dimensions["B"].width = 22 ws_guide.column_dimensions["C"].width = 60 ws_guide.merge_cells("A1:C1") ws_guide["A1"].value = "DATA ENTRY GUIDE & FIELD VALIDATION RULES" ws_guide["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws_guide["A1"].fill = fill(DARK_BLUE) ws_guide["A1"].alignment = center() ws_guide.row_dimensions[1].height = 30 ws_guide.merge_cells("A2:C2") ws_guide["A2"].value = "Tip: All Yes/No fields have drop-down menus. Numeric fields accept numbers only. Remarks are free text." ws_guide["A2"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws_guide["A2"].fill = fill(MID_BLUE) ws_guide["A2"].alignment = center() ws_guide.row_dimensions[2].height = 18 for i, h in enumerate(["Field", "Accepted Values", "Instructions / Validation Rule"], start=1): c = ws_guide[f"{get_column_letter(i)}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws_guide.row_dimensions[3].height = 22 guide_rows = [ ("Patient ID / MRN", "Text (alphanumeric)", "Unique identifier. Cases: ERAS-001 … ERAS-075. Controls: STD-001 … STD-075"), ("Group", "Drop-down list", "Select 'Case (ERAS)' or 'Control (Std)' only. Do NOT type free text."), ("Date of Surgery", "DD/MM/YYYY", "Enter as text in DD/MM/YYYY format. Consistent formatting essential for sorting."), ("Age", "Integer (yrs)", "Age in completed years at time of surgery. Must be ≥18 (inclusion criterion)."), ("Gravida / Para", "Integer", "Gravida = total pregnancies including current. Para = completed deliveries."), ("BMI", "Decimal (kg/m²)", "Enter to 1 decimal place. Use pre-operative weight and height."), ("Gestational Age", "Decimal (weeks)", "E.g. 38.5. Use ultrasound-dating or LMP-based GA as per records."), ("Type of CS", "Drop-down list", "Elective = scheduled; Emergency = unscheduled Category I–III."), ("Singleton", "Yes / No (drop-down)", "Should always be 'Yes' — No = exclusion criterion, do not enrol."), ("ASA Grade", "I / II / III (drop-down)","IV or V = exclusion criterion."), ("IT Opioid Dose", "Numeric (µg)", "ERAS cases: intrathecal fentanyl (25µg) or morphine (100–200µg). Controls: enter 0 if none given."), ("IV Fluids Intra-op", "Numeric (mL)", "Total IV fluid volume administered during surgery until transfer to recovery."), ("Vasopressor", "Yes / No (drop-down)", "Yes if any vasopressor (phenylephrine/ephedrine) given intra-op."), ("EBL", "Numeric (mL)", "Estimated blood loss = swab weight difference + suction canister volume."), ("Oral Fluids (hrs)", "Decimal (hours)", "Time from end of surgery to first sip of water. ERAS target ≤0.5h."), ("Ambulation (hrs)", "Decimal (hours)", "Time from end of surgery to standing/walking. ERAS target ≤8h."), ("Catheter Removal", "Numeric (hours)", "Time post-op when urinary catheter removed. ERAS target ≤12h."), ("NRS Pain Scores", "Integer 0–10", "Numeric Rating Scale at 2h, 6h, 12h and 24h post-op. Ask patient verbally."), ("Total Opioid (mg ME)", "Decimal", "Sum all opioids over 24h converted to morphine equivalents. See Legend sheet."), ("LOS (hrs)", "Numeric (hours)", "PRIMARY OUTCOME. Time from surgery end to actual hospital discharge."), ("Discharge Readiness", "Numeric (hours)", "Time to meeting all discharge criteria (tolerating diet, pain ≤4, mobile)."), ("Breastfeeding", "Yes / No (drop-down)", "Yes = skin-to-skin contact AND first latch achieved within 1h of delivery."), ("Wound Infection", "Yes / No (drop-down)", "Per CDC SSI definition. Enter grade in Remarks if positive."), ("PPH >1000 mL", "Yes / No (drop-down)", "Based on EBL. Confirm with clinical note."), ("APGAR Score", "Integer 0–10", "Recorded by paediatrician at 1 min and 5 min. Obtain from neonatal records."), ("NICU", "Yes / No (drop-down)", "Any neonatal ICU admission, regardless of duration."), ("Satisfaction Score", "Integer 1–10", "Administer validated satisfaction questionnaire at time of discharge."), ("Remarks", "Free text", "Record any deviations from protocol, adverse events, or notable clinical observations."), ] for row_i, (fld, val, instr) in enumerate(guide_rows, start=4): fc = GREY if row_i % 2 == 0 else WHITE ws_guide[f"A{row_i}"].value = fld ws_guide[f"B{row_i}"].value = val ws_guide[f"C{row_i}"].value = instr for col_c in ["A","B","C"]: c = ws_guide[f"{col_c}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col_c=="A")) c.alignment = left() c.border = thin_border() ws_guide.row_dimensions[row_i].height = 20 # ════════════════════════════════════════════════════════════════════════════ # SHEET 4 – INCLUSION / EXCLUSION # ════════════════════════════════════════════════════════════════════════════ ws_ie = wb.create_sheet("Inclusion & Exclusion") ws_ie.column_dimensions["A"].width = 8 ws_ie.column_dimensions["B"].width = 46 ws_ie.column_dimensions["C"].width = 55 ws_ie.merge_cells("A1:C1") ws_ie["A1"].value = "STUDY ELIGIBILITY CRITERIA — ERAS-CS (n=75 per group)" ws_ie["A1"].font = Font(name="Arial", bold=True, size=14, color="FFFFFF") ws_ie["A1"].fill = fill(DARK_BLUE) ws_ie["A1"].alignment = center() ws_ie.row_dimensions[1].height = 34 ws_ie.merge_cells("A2:C2") ws_ie["A2"].value = " INCLUSION CRITERIA" ws_ie["A2"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws_ie["A2"].fill = fill("375623") ws_ie["A2"].alignment = left() ws_ie.row_dimensions[2].height = 24 for i, h in enumerate(["#", "Criterion", "Details / Rationale"], start=1): c = ws_ie[f"{get_column_letter(i)}3"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill("538135") c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws_ie.row_dimensions[3].height = 22 inclusion = [ ("1","Both elective and emergency caesarean section","Elective = scheduled CS; Emergency = Category I–III CS"), ("2","Singleton pregnancy","Single fetus only — multiple pregnancies excluded"), ("3","Gestational age ≥ 28 weeks","Viable gestation; adjust per unit policy if needed"), ("4","Age ≥ 18 years","Adult patients capable of independent informed consent"), ("5","Written informed consent obtained","Pre-enrolment consent from patient / guardian"), ("6","Regional anaesthesia feasible (Cases)","SAB or CSE planned — core ERAS anaesthetic element"), ("7","ASA physical status I–III","All fitness grades unless other exclusions apply"), ] for row_i, (n, crit, rat) in enumerate(inclusion, start=4): ws_ie[f"A{row_i}"].value = n ws_ie[f"B{row_i}"].value = crit ws_ie[f"C{row_i}"].value = rat for col_c in ["A","B","C"]: c = ws_ie[f"{col_c}{row_i}"] c.fill = fill("E2EFDA") c.font = cell_font(bold=(col_c=="B")) c.alignment = left() c.border = thin_border() ws_ie.row_dimensions[row_i].height = 20 # spacer ws_ie.row_dimensions[11].height = 10 ws_ie.merge_cells("A12:C12") ws_ie["A12"].value = " EXCLUSION CRITERIA" ws_ie["A12"].font = Font(name="Arial", bold=True, size=11, color="FFFFFF") ws_ie["A12"].fill = fill("843C0C") ws_ie["A12"].alignment = left() ws_ie.row_dimensions[12].height = 24 for i, h in enumerate(["#","Criterion","Rationale"], start=1): c = ws_ie[f"{get_column_letter(i)}13"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill("C55A11") c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws_ie.row_dimensions[13].height = 22 exclusion = [ ("1","Multiple pregnancy (twins, triplets, higher order)","Core exclusion — singleton is inclusion requirement"), ("2","Fetal anomaly (structural or chromosomal)","Alters neonatal management; outcomes non-comparable"), ("3","General anaesthesia required","ERAS designed around regional; GA cases differ fundamentally"), ("4","Placenta praevia / placenta accreta spectrum","Atypical surgical and anaesthetic management"), ("5","Severe preeclampsia / eclampsia","Haemodynamic instability affects fluid & anaesthetic decisions"), ("6","Active maternal coagulopathy or bleeding disorder","Impacts anticoagulant and analgesic choices"), ("7","Maternal cardiac or pulmonary disease (ASA IV/V)","Require deviation from standard ERAS elements"), ("8","Patient refusal of consent","Ethical requirement; any withdrawal must be honoured"), ("9","Incomplete data (>20% primary outcome missing)","Exclude from final analysis — document reason"), ] for row_i, (n, crit, rat) in enumerate(exclusion, start=14): ws_ie[f"A{row_i}"].value = n ws_ie[f"B{row_i}"].value = crit ws_ie[f"C{row_i}"].value = rat for col_c in ["A","B","C"]: c = ws_ie[f"{col_c}{row_i}"] c.fill = fill("FCE4D6") c.font = cell_font(bold=(col_c=="B")) c.alignment = left() c.border = thin_border() ws_ie.row_dimensions[row_i].height = 20 note_row = 24 ws_ie.merge_cells(f"A{note_row}:C{note_row}") ws_ie[f"A{note_row}"].value = " SAMPLE SIZE: n=75 per group (Total N=150) | Power 80%, α=0.05 | Primary outcome: Length of Hospital Stay" ws_ie[f"A{note_row}"].font = Font(name="Arial", italic=True, size=10, color="FFFFFF") ws_ie[f"A{note_row}"].fill = fill(MID_BLUE) ws_ie[f"A{note_row}"].alignment = left() ws_ie.row_dimensions[note_row].height = 30 # ════════════════════════════════════════════════════════════════════════════ # SHEET 5 – LEGEND # ════════════════════════════════════════════════════════════════════════════ ws4 = wb.create_sheet("Legend & Codebook") ws4.column_dimensions["A"].width = 38 ws4.column_dimensions["B"].width = 26 ws4.column_dimensions["C"].width = 60 ws4.merge_cells("A1:C1") ws4["A1"].value = "LEGEND & VARIABLE CODEBOOK — ERAS-CS STUDY" ws4["A1"].font = Font(name="Arial", bold=True, size=13, color="FFFFFF") ws4["A1"].fill = fill(DARK_BLUE) ws4["A1"].alignment = center() ws4.row_dimensions[1].height = 30 for i, h in enumerate(["Variable","Allowed Values","Definition / Notes"], start=1): c = ws4[f"{get_column_letter(i)}2"] c.value = h c.font = Font(name="Arial", bold=True, size=10, color="FFFFFF") c.fill = fill(MID_BLUE) c.alignment = center() bd = Side(style="thin", color="FFFFFF") c.border = Border(left=bd, right=bd, top=bd, bottom=bd) ws4.row_dimensions[2].height = 22 legend_rows = [ ("Group", "Case (ERAS) / Control (Std)", "Cases = full ERAS protocol; Controls = standard perioperative care"), ("Singleton Pregnancy", "Yes / No", "Yes = inclusion criterion; No = exclude"), ("Multiple Pregnancy", "Excluded", "Twins / triplets — EXCLUSION criterion"), ("Fetal Anomaly", "Excluded", "Structural or chromosomal anomaly — EXCLUSION"), ("ASA Grade", "I / II / III", "American Society of Anaesthesiologists physical status"), ("Type of CS", "Elective / Emergency", "Elective = scheduled; Emergency = unscheduled Category I–III"), ("BMI", "Decimal (kg/m²)", "Pre-operative BMI = weight(kg) / height²(m)"), ("NRS Pain Score", "Integer 0–10", "0 = no pain, 10 = worst imaginable pain"), ("EBL", "Numeric (mL)", "Estimated blood loss: swab weighing + suction canister"), ("Anaesthesia", "SAB / CSE / GA", "Spinal Block / Combined Spinal-Epidural / General Anaesthesia"), ("IT Opioid", "Numeric (µg)", "Intrathecal fentanyl (25µg) or morphine (100–200µg)"), ("APGAR Score", "Integer 0–10", "Assessed at 1 min and 5 min by paediatrician"), ("LOS", "Numeric (hours)", "PRIMARY OUTCOME: surgery end → hospital discharge"), ("Discharge Readiness", "Numeric (hours)", "Surgery end → meeting all discharge criteria"), ("Breastfeeding", "Yes / No", "Skin-to-skin + first latch within 1h of delivery"), ("Wound Infection", "Yes / No", "Per CDC SSI criteria (superficial/deep/organ-space)"), ("PPH > 1000 mL", "Yes / No", "Confirmed by EBL measurement"), ("Readmission", "Yes / No", "Unplanned readmission within 30 days of discharge"), ("DVT / PE", "Yes / No", "Confirmed by Doppler ultrasound / CTPA"), ("mg ME", "Morphine Equivalent (mg)", "Oxycodone 10mg=15ME; Tramadol 10mg=1ME; Codeine 10mg=1ME"), ("Neonatal Outcome", "Good / Fair / Poor", "Good=normal APGAR no NICU; Fair=brief obs; Poor=prolonged NICU"), ("Satisfaction Score", "Integer 1–10", "Validated questionnaire at discharge; 10 = fully satisfied"), ("PONV", "Yes / No", "Post-operative nausea/vomiting requiring pharmacological treatment"), ("LMWH", "Yes / No / NA", "Low Molecular Weight Heparin; NA = not clinically indicated"), ("Multimodal Analgesia","Yes / No", "≥2 classes: paracetamol + NSAID + low-dose opioid combined"), ] for row_i, (var, vals, defn) in enumerate(legend_rows, start=3): fc = GREY if row_i % 2 == 0 else WHITE ws4[f"A{row_i}"].value = var ws4[f"B{row_i}"].value = vals ws4[f"C{row_i}"].value = defn for col_c in ["A","B","C"]: c = ws4[f"{col_c}{row_i}"] c.fill = fill(fc) c.font = cell_font(bold=(col_c=="A")) c.alignment = left() c.border = thin_border() ws4.row_dimensions[row_i].height = 20 # ── Save ───────────────────────────────────────────────────────────────────── os.makedirs("/tmp/workspace/eras-cs", exist_ok=True) out = "/tmp/workspace/eras-cs/ERAS_CS_Pilot_Template.xlsx" wb.save(out) print(f"Saved: {out}") print(f"File size: {os.path.getsize(out)//1024} KB") print(f"Sheets: {[s.title for s in wb.worksheets]}") PYEOF