cd /home/daytona/workspace/attachments/59111884-76f4-4a64-b0df-bad01e31806f && python3 -c " import pdfplumber for fname in ['page 1.pdf', 'student_questionnaire_single_page.pdf']: print(f'\n========== {fname} ==========') with pdfplumber.open(fname) as pdf: for i, page in enumerate(pdf.pages): text = page.extract_text() print(f'--- Page {i+1} ---') print(text) "

mkdir -p /home/daytona/workspace/precocious-puberty-study && python3 << 'PYEOF' import openpyxl from openpyxl.styles import ( Font, PatternFill, Alignment, Border, Side, Protection ) from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.styles.numbers import FORMAT_TEXT wb = openpyxl.Workbook() # ── Colour palette ────────────────────────────────────────────── C_TITLE_BG = "1F4E79" # dark-blue – sheet title bar C_TITLE_FG = "FFFFFF" C_SECTION_BG = "2E75B6" # mid-blue – section headers C_SECTION_FG = "FFFFFF" C_VAR_BG = "D6E4F0" # light-blue – variable label rows C_VAR_FG = "1F4E79" C_HDR_BG = "BDD7EE" # column header row C_HDR_FG = "1F4E79" C_ALT_BG = "EBF3FB" # alternating data row tint C_CODE_BG = "FFF2CC" # coding reference cells C_CODE_FG = "7F6000" C_NOTE_BG = "E2EFDA" # green note C_NOTE_FG = "375623" C_MANDATORY = "FCE4D6" # salmon – mandatory field header def fill(hex_colour): return PatternFill("solid", fgColor=hex_colour) def font(hex_colour, bold=False, sz=10, italic=False): return Font(name="Arial", size=sz, bold=bold, italic=italic, color=hex_colour) def border(style="thin"): s = Side(style=style) return Border(left=s, right=s, top=s, bottom=s) def center(wrap=False): return Alignment(horizontal="center", vertical="center", wrap_text=wrap) def left(wrap=True): return Alignment(horizontal="left", vertical="center", wrap_text=wrap) thin = border("thin") thick = border("medium") # ═══════════════════════════════════════════════════════════════════ # SHEET 1 – DATA ENTRY (Parent Questionnaire + Student Questionnaire) # ═══════════════════════════════════════════════════════════════════ ws = wb.active ws.title = "Data Entry" ws.sheet_view.showGridLines = False ws.freeze_panes = "C4" # freeze ID cols + header row # ── Row 1 : Main Title ───────────────────────────────────────── ws.merge_cells("A1:AJ1") t = ws["A1"] t.value = "PRECOCIOUS PUBERTY STUDY – DATA ENTRY DATABASE" t.font = font(C_TITLE_FG, bold=True, sz=14) t.fill = fill(C_TITLE_BG) t.alignment = center() ws.row_dimensions[1].height = 28 # ── Row 2 : Sub-title ───────────────────────────────────────── ws.merge_cells("A2:AJ2") s = ws["A2"] s.value = ("Study: Prevalence of Precocious Puberty in School Girls | " "Location: Manjeri & Anakkayam | Participants: Girls aged 10-15") s.font = font(C_TITLE_FG, sz=9, italic=True) s.fill = fill(C_TITLE_BG) s.alignment = center() ws.row_dimensions[2].height = 16 # ── Row 3 : Column Headers ───────────────────────────────────── # We define each column as: # (col_letter, width, header_text, spss_var_name, value_type, note) columns = [ # --- Admin --- ("A", 8, "ID\n(Auto)", "ID", "numeric", "Auto serial number"), ("B", 14, "Date of\nEntry", "Date_Entry", "date", "DD/MM/YYYY"), # --- SECTION 1: Demographics (Parent Q) --- ("C", 22, "P1. Child Name\n(Optional)", "Child_Name", "text", "Leave blank for anonymity"), ("D", 8, "P2. Age\n(years)", "Age", "numeric", "10–15 only"), ("E", 8, "P3. Grade/\nStandard", "Grade", "numeric", "e.g. 5,6,7,8,9,10"), ("F", 20, "P4. School Name", "School_Name", "text", ""), ("G", 12, "P5. Residence\n1=Urban\n2=Semiurban","Residence","numeric","1=Urban 2=Semiurban"), ("H", 12, "P6. Family Type\n1=Nuclear\n2=Joint","Family_Type","numeric","1=Nuclear 2=Joint"), ("I", 14, "P7a. Father\nEducation\n(1-5)","Father_Edu","numeric","1=Illiterate 2=Primary\n3=Secondary 4=Graduate\n5=Post-graduate"), ("J", 14, "P7b. Mother\nEducation\n(1-5)","Mother_Edu","numeric","1=Illiterate 2=Primary\n3=Secondary 4=Graduate\n5=Post-graduate"), ("K", 14, "P8. Monthly Income\n(1-4)","Income", "numeric", "1=<10000 2=10000-30000\n3=30000-50000 4=>50000"), # --- SECTION 2: Medical History --- ("L", 12, "P9. Chronic Illness\n1=Yes 0=No","Chronic_Illness","numeric","1=Yes 0=No"), ("M", 22, "P9. Illness\nDetails", "Illness_Details", "text", "Specify if Yes"), ("N", 12, "P10. Family Hx\nEarly Puberty\n1=Yes 0=No","Fam_Hx_Puberty","numeric","1=Yes 0=No"), ("O", 12, "P11. Medications/\nHormones\n1=Yes 0=No","Medications","numeric","1=Yes 0=No"), ("P", 22, "P11. Medication\nDetails","Medication_Details","text", "Specify if Yes"), ("Q", 12, "P12. Age at\nMenarche (yrs)","Age_Menarche", "numeric", "Enter 99 if not yet"), # --- SECTION 3: Lifestyle --- ("R", 14, "P13a. Diet:\nHome-cooked\n1=Yes 0=No","Diet_Homecooked","numeric","1=Yes 0=No"), ("S", 14, "P13b. Fast Food\n>3x/wk\n1=Yes 0=No","Diet_FastFood","numeric","1=Yes 0=No"), ("T", 14, "P13c. Processed\nFoods\n1=Yes 0=No","Diet_Processed","numeric","1=Yes 0=No"), ("U", 14, "P13d. High\nProtein\n1=Yes 0=No","Diet_HighProtein","numeric","1=Yes 0=No"), ("V", 14, "P13e. Trad Kerala\nDiet\n1=Yes 0=No","Diet_TraditionalKerala","numeric","1=Yes 0=No"), ("W", 14, "P14. Hours\nOutdoors/Day","Hrs_Outdoors", "numeric", "Hours per day"), ("X", 12, "P15. Screen Time\n>2 hrs/day\n1=Yes 0=No","Screen_Time_Parent","numeric","1=Yes 0=No"), ("Y", 12, "P16. Pesticide\nExposure\n1=Yes 0=No","Pesticide_Exp","numeric","1=Yes 0=No"), ("Z", 22, "P16. Pesticide\nDetails","Pesticide_Details","text", "Specify if Yes"), # --- SECTION 4: Physical Development --- ("AA",12, "P17. Age Puberty\nSigns Noticed\n(yrs)","Age_Puberty_Signs","numeric","Years"), ("AB",12, "P18. Doctor\nConfirmed Early\nPuberty\n1=Yes 0=No","Doctor_Confirmed","numeric","1=Yes 0=No"), ("AC",22, "P18. Doctor\nConfirmation Details","Doctor_Details","text","Specify if Yes"), # --- SECTION 5: Consent --- ("AD",12, "P19. Parent\nConsent\n1=Yes 0=No","Parent_Consent","numeric","1=Yes 0=No"), # ── STUDENT QUESTIONNAIRE ── ("AE",12, "S1. Plays\nOutdoors Daily\n1=Yes 0=No","S_Outdoor_Play","numeric","1=Yes 0=No"), ("AF",12, "S1. Outdoor\nHours/Day","S_Outdoor_Hrs", "numeric", "Hours per day"), ("AG",12, "S2. Screen Time\n>2 hrs/day\n1=Yes 0=No","S_Screen_Time","numeric","1=Yes 0=No"), ("AH",12, "S3. Noticed Body\nChanges\n1=Yes 0=No","S_Body_Changes","numeric","1=Yes 0=No"), ("AI",12, "S3. Age at Body\nChanges (yrs)","S_Age_Body_Changes","numeric","Enter 99=Don't know"), ("AJ",12, "S4. First Period\n1=Yes 0=No","S_Menarche", "numeric", "1=Yes 0=No"), ("AK",12, "S4. Age at First\nPeriod (yrs)","S_Age_Menarche", "numeric", "Enter 99 if NA"), ("AL",25, "S5. Health Notes\n(Open)","S_Health_Notes", "text", "Free text"), ("AM",12, "S6. Student\nAssent\n1=Yes 0=No","S_Assent", "numeric", "1=Yes 0=No"), ] # Write column headers (row 3) ws.row_dimensions[3].height = 60 for col_def in columns: col_letter, col_width, header_text, spss_var, val_type, note = col_def cell = ws[f"{col_letter}3"] cell.value = header_text cell.font = font(C_HDR_FG, bold=True, sz=9) cell.fill = fill(C_HDR_BG) cell.alignment = center(wrap=True) cell.border = thin ws.column_dimensions[col_letter].width = col_width # ── Data rows 4-203 (200 rows) ───────────────────────────────── for row in range(4, 204): # ID auto-fill id_cell = ws[f"A{row}"] id_cell.value = row - 3 id_cell.font = font("000000", bold=True, sz=9) id_cell.fill = fill("F2F2F2") id_cell.alignment = center() id_cell.border = thin row_fill = fill(C_ALT_BG) if row % 2 == 0 else fill("FFFFFF") for col_def in columns[1:]: # skip A (already done) col_letter, _, _, _, val_type, _ = col_def cell = ws[f"{col_letter}{row}"] cell.fill = row_fill cell.alignment = left() cell.border = thin cell.font = font("000000", sz=9) if val_type == "numeric": cell.number_format = "0" elif val_type == "date": cell.number_format = "DD/MM/YYYY" # ── Data Validations ────────────────────────────────────────── def dv_list(formula, sqref): dv = DataValidation(type="list", formula1=formula, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid entry", error="Use the dropdown list.") dv.sqref = sqref return dv def dv_whole(min_val, max_val, sqref, err_msg=""): dv = DataValidation(type="whole", operator="between", formula1=str(min_val), formula2=str(max_val), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=err_msg or f"Enter {min_val}–{max_val}") dv.sqref = sqref return dv def dv_decimal(min_val, max_val, sqref): dv = DataValidation(type="decimal", operator="between", formula1=str(min_val), formula2=str(max_val), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=f"Enter {min_val}–{max_val}") dv.sqref = sqref return dv def col_range(letter, start=4, end=203): return f"{letter}{start}:{letter}{end}" # Yes/No columns (1/0) yn_cols = ["L","N","O","R","S","T","U","V","X","Y","AB","AD","AE","AG","AH","AJ","AM"] for c in yn_cols: ws.add_data_validation(dv_list('"1,0"', col_range(c))) ws.add_data_validation(dv_whole(10, 15, col_range("D"), "Age must be 10-15")) ws.add_data_validation(dv_whole(1, 12, col_range("E"), "Grade 1-12")) ws.add_data_validation(dv_list('"1,2"', col_range("G"))) # Residence ws.add_data_validation(dv_list('"1,2"', col_range("H"))) # Family type ws.add_data_validation(dv_list('"1,2,3,4,5"', col_range("I"))) # Father edu ws.add_data_validation(dv_list('"1,2,3,4,5"', col_range("J"))) # Mother edu ws.add_data_validation(dv_list('"1,2,3,4"', col_range("K"))) # Income ws.add_data_validation(dv_decimal(0, 30, col_range("Q"))) # Menarche age ws.add_data_validation(dv_decimal(0, 24, col_range("W"))) # Outdoor hrs ws.add_data_validation(dv_decimal(0, 24, col_range("AF"))) # S outdoor hrs ws.add_data_validation(dv_decimal(4, 20, col_range("AA"))) # Puberty signs age ws.add_data_validation(dv_decimal(0, 20, col_range("AI"))) # S body changes age ws.add_data_validation(dv_decimal(8, 20, col_range("AK"))) # S menarche age # ═══════════════════════════════════════════════════════════════════ # SHEET 2 – SPSS VARIABLE CODEBOOK # ═══════════════════════════════════════════════════════════════════ wb2 = wb.create_sheet("SPSS Codebook") wb2.sheet_view.showGridLines = False wb2.column_dimensions["A"].width = 6 wb2.column_dimensions["B"].width = 25 wb2.column_dimensions["C"].width = 30 wb2.column_dimensions["D"].width = 14 wb2.column_dimensions["E"].width = 12 wb2.column_dimensions["F"].width = 45 wb2.column_dimensions["G"].width = 20 # Title wb2.merge_cells("A1:G1") wb2["A1"].value = "SPSS VARIABLE CODEBOOK – Precocious Puberty Study" wb2["A1"].font = font(C_TITLE_FG, bold=True, sz=13) wb2["A1"].fill = fill(C_TITLE_BG) wb2["A1"].alignment = center() wb2.row_dimensions[1].height = 26 # Header row hdrs = ["#", "SPSS Variable Name", "Question / Label", "Type", "Missing", "Value Labels", "Notes"] for i, h in enumerate(hdrs, 1): c = wb2.cell(row=2, column=i, value=h) c.font = font(C_HDR_FG, bold=True, sz=10) c.fill = fill(C_HDR_BG) c.alignment = center(wrap=True) c.border = thin wb2.row_dimensions[2].height = 20 codebook = [ # num, varname, label, type, missing, value_labels, notes (1, "ID", "Participant serial number", "Numeric","—", "Sequential 1,2,3…","Auto-filled"), (2, "Date_Entry", "Date of data entry", "Date", "—", "DD/MM/YYYY",""), # Demographics (3, "Child_Name", "P1. Child name (optional)", "String", "—", "Text (optional)","Leave blank for anonymity"), (4, "Age", "P2. Age of child (years)", "Numeric","99", "10–15 integer","Must be 10-15"), (5, "Grade", "P3. Grade / Standard", "Numeric","99", "Integer 1-12",""), (6, "School_Name", "P4. School name", "String", "—", "Text",""), (7, "Residence", "P5. Residence type", "Numeric","9", "1=Urban 2=Semiurban",""), (8, "Family_Type", "P6. Family type", "Numeric","9", "1=Nuclear 2=Joint",""), (9, "Father_Edu", "P7a. Father's education level", "Numeric","9", "1=Illiterate 2=Primary\n3=Secondary 4=Graduate\n5=Post-graduate",""), (10, "Mother_Edu", "P7b. Mother's education level", "Numeric","9", "1=Illiterate 2=Primary\n3=Secondary 4=Graduate\n5=Post-graduate",""), (11, "Income", "P8. Monthly family income (INR)", "Numeric","9", "1=<10,000 2=10,000-30,000\n3=30,000-50,000 4=>50,000",""), # Medical (12, "Chronic_Illness", "P9. Chronic illness present", "Numeric","9", "1=Yes 0=No",""), (13, "Illness_Details", "P9. Illness details (specify)", "String", "—", "Text","If Chronic_Illness=1"), (14, "Fam_Hx_Puberty", "P10. Family history of early puberty","Numeric","9", "1=Yes 0=No","Mother/sisters"), (15, "Medications", "P11. Medications/hormones used", "Numeric","9", "1=Yes 0=No",""), (16, "Medication_Details","P11. Medication details", "String", "—", "Text","If Medications=1"), (17, "Age_Menarche", "P12. Age at menarche (years)", "Numeric","99", "Continuous (years)","99=Not yet occurred"), # Lifestyle (18, "Diet_Homecooked", "P13a. Home-cooked meals", "Numeric","9", "1=Yes 0=No","Check all that apply"), (19, "Diet_FastFood", "P13b. Fast food >3x/week", "Numeric","9", "1=Yes 0=No",""), (20, "Diet_Processed", "P13c. Processed foods", "Numeric","9", "1=Yes 0=No",""), (21, "Diet_HighProtein", "P13d. High protein foods", "Numeric","9", "1=Yes 0=No",""), (22, "Diet_TraditionalKerala","P13e. Traditional Kerala diet", "Numeric","9", "1=Yes 0=No",""), (23, "Hrs_Outdoors", "P14. Hours outdoors per day", "Numeric","99", "Continuous (hours)",""), (24, "Screen_Time_Parent","P15. Screen time >2 hrs/day", "Numeric","9", "1=Yes 0=No",""), (25, "Pesticide_Exp", "P16. Pesticide/chemical exposure", "Numeric","9", "1=Yes 0=No",""), (26, "Pesticide_Details","P16. Pesticide details", "String", "—", "Text","If Pesticide_Exp=1"), # Physical (27, "Age_Puberty_Signs","P17. Age puberty signs first noticed","Numeric","99", "Continuous (years)",""), (28, "Doctor_Confirmed", "P18. Doctor confirmed early puberty","Numeric","9", "1=Yes 0=No",""), (29, "Doctor_Details", "P18. Doctor confirmation details", "String", "—", "Text","If Doctor_Confirmed=1"), (30, "Parent_Consent", "P19. Parent/guardian consent", "Numeric","9", "1=Yes 0=No","Exclude if 0"), # Student Q (31, "S_Outdoor_Play", "S1. Plays outdoors daily", "Numeric","9", "1=Yes 0=No",""), (32, "S_Outdoor_Hrs", "S1. Outdoor hours per day", "Numeric","99", "Continuous (hours)","If S_Outdoor_Play=1"), (33, "S_Screen_Time", "S2. Screen time >2 hrs/day", "Numeric","9", "1=Yes 0=No",""), (34, "S_Body_Changes", "S3. Noticed body changes", "Numeric","9", "1=Yes 0=No",""), (35, "S_Age_Body_Changes","S3. Age at body changes (years)", "Numeric","99", "Continuous; 99=Don't know","If S_Body_Changes=1"), (36, "S_Menarche", "S4. Had first menstrual period", "Numeric","9", "1=Yes 0=No",""), (37, "S_Age_Menarche", "S4. Age at first period (years)", "Numeric","99", "Continuous (years)","If S_Menarche=1"), (38, "S_Health_Notes", "S5. Additional health notes", "String", "—", "Free text",""), (39, "S_Assent", "S6. Student assent", "Numeric","9", "1=Yes 0=No","Exclude if 0"), ] for r, row_data in enumerate(codebook, 3): is_section_break = row_data[0] in [3,12,18,27,31] bg = C_ALT_BG if r % 2 == 0 else "FFFFFF" for ci, val in enumerate(row_data, 1): c = wb2.cell(row=r, column=ci, value=val) c.font = font("000000", sz=9) c.fill = fill(bg) c.border = thin c.alignment = left(wrap=True) if ci > 1 else center() wb2.row_dimensions[r].height = 32 # ── Section labels on codebook ───────────────────────────────── section_rows = { 3: "◀ PARENT QUESTIONNAIRE – SECTION 1: Demographics", 12: "◀ PARENT QUESTIONNAIRE – SECTION 2: Medical & Family History", 18: "◀ PARENT QUESTIONNAIRE – SECTION 3: Lifestyle & Environment", 27: "◀ PARENT QUESTIONNAIRE – SECTION 4: Physical Development", 30: "◀ PARENT QUESTIONNAIRE – SECTION 5: Consent", 32: "◀ STUDENT QUESTIONNAIRE", } for row_num, label in section_rows.items(): r = row_num + 2 # codebook data starts at row 3 wb2.insert_rows(r) wb2.merge_cells(f"A{r}:G{r}") sc = wb2[f"A{r}"] sc.value = label sc.font = font(C_SECTION_FG, bold=True, sz=10) sc.fill = fill(C_SECTION_BG) sc.alignment = left() sc.border = thin wb2.row_dimensions[r].height = 18 # ═══════════════════════════════════════════════════════════════════ # SHEET 3 – VALUE CODES REFERENCE (quick-look while entering data) # ═══════════════════════════════════════════════════════════════════ wb3 = wb.create_sheet("Value Codes (Reference)") wb3.sheet_view.showGridLines = False wb3.merge_cells("A1:D1") wb3["A1"].value = "QUICK REFERENCE – VALUE CODES FOR SPSS DATA ENTRY" wb3["A1"].font = font(C_TITLE_FG, bold=True, sz=13) wb3["A1"].fill = fill(C_TITLE_BG) wb3["A1"].alignment = center() wb3.row_dimensions[1].height = 26 sections = [ ("GENERAL", [("Yes / No fields", "1 = Yes 0 = No\n9 = Missing / Not applicable"), ("Unknown / Missing numeric", "99 = Not applicable / Unknown / Not yet occurred")]), ("P5 – RESIDENCE", [("Options", "1 = Urban\n2 = Semiurban")]), ("P6 – FAMILY TYPE", [("Options", "1 = Nuclear\n2 = Joint")]), ("P7 – EDUCATION (Father & Mother)", [("Levels", "1 = Illiterate\n2 = Primary\n3 = Secondary\n4 = Graduate\n5 = Post-graduate")]), ("P8 – MONTHLY INCOME (INR)", [("Ranges", "1 = Less than ₹10,000\n2 = ₹10,000 – ₹30,000\n3 = ₹30,000 – ₹50,000\n4 = More than ₹50,000")]), ("P12 – AGE AT MENARCHE", [("Rule", "Enter actual age in years (e.g. 12)\n99 = Menarche not yet occurred")]), ("P13 – DIET (Multiple choice)", [("Each item", "Enter separately:\n1 = Checked (selected by parent)\n0 = Not checked")]), ("P14 / S1 – OUTDOOR HOURS", [("Rule", "Enter numeric hours (e.g. 2.5)\n99 = Not reported")]), ("P17 – AGE PUBERTY SIGNS", [("Rule", "Enter actual age in years\n99 = Not noticed / Unknown")]), ("S3 – AGE AT BODY CHANGES", [("Rule", "Enter actual age\n99 = Don't know\nLeave blank if S_Body_Changes = 0")]), ("S4 – AGE AT FIRST PERIOD", [("Rule", "Enter actual age\n99 = Not applicable\nLeave blank if S_Menarche = 0")]), ("CONSENT / ASSENT", [("Rule", "1 = Consent given 0 = Refused\nExclude participant from analysis if 0")]), ] r = 3 wb3.column_dimensions["A"].width = 4 wb3.column_dimensions["B"].width = 35 wb3.column_dimensions["C"].width = 50 for section_title, items in sections: # Section header wb3.merge_cells(f"A{r}:C{r}") sc = wb3[f"A{r}"] sc.value = section_title sc.font = font(C_SECTION_FG, bold=True, sz=10) sc.fill = fill(C_SECTION_BG) sc.alignment = left() sc.border = thin wb3.row_dimensions[r].height = 18 r += 1 for label, codes in items: lb = wb3.cell(row=r, column=2, value=label) lb.font = font(C_CODE_FG, bold=True, sz=9) lb.fill = fill(C_CODE_BG) lb.border = thin lb.alignment = left() cv = wb3.cell(row=r, column=3, value=codes) cv.font = font("000000", sz=9) cv.fill = fill(C_CODE_BG) cv.border = thin cv.alignment = left(wrap=True) wb3.row_dimensions[r].height = max(18, codes.count("\n") * 14 + 10) r += 1 r += 1 # blank spacer # ═══════════════════════════════════════════════════════════════════ # SHEET 4 – SPSS IMPORT SYNTAX # ═══════════════════════════════════════════════════════════════════ wb4 = wb.create_sheet("SPSS Syntax") wb4.sheet_view.showGridLines = False wb4.column_dimensions["A"].width = 100 wb4.merge_cells("A1:A1") wb4["A1"].value = "SPSS IMPORT SYNTAX – Copy this into SPSS Syntax Editor and run" wb4["A1"].font = font(C_TITLE_FG, bold=True, sz=12) wb4["A1"].fill = fill(C_TITLE_BG) wb4["A1"].alignment = center() wb4.row_dimensions[1].height = 24 syntax_lines = [ "", "* ============================================================", "* STEP 1: Import Excel file into SPSS", "* ============================================================", "* In SPSS: File > Import Data > Excel", "* - Select this workbook", "* - Choose sheet: 'Data Entry'", "* - Check 'Read variable names from the first row of data'", "* - Set starting row to 3 (header row)", "", "* ============================================================", "* STEP 2: Assign Variable Labels", "* ============================================================", "VARIABLE LABELS", " ID 'Participant serial number'", " Date_Entry 'Date of data entry'", " Child_Name 'Child name (optional)'", " Age 'Age of child (years)'", " Grade 'Grade / Standard'", " School_Name 'School name'", " Residence 'Residence type'", " Family_Type 'Family type'", " Father_Edu 'Father education level'", " Mother_Edu 'Mother education level'", " Income 'Monthly family income (INR)'", " Chronic_Illness 'Chronic illness present'", " Illness_Details 'Illness details'", " Fam_Hx_Puberty 'Family history of early puberty'", " Medications 'Medications or hormones used'", " Medication_Details 'Medication details'", " Age_Menarche 'Age at menarche (years)'", " Diet_Homecooked 'Diet: Home-cooked meals'", " Diet_FastFood 'Diet: Fast food >3x/week'", " Diet_Processed 'Diet: Processed foods'", " Diet_HighProtein 'Diet: High protein foods'", " Diet_TraditionalKerala 'Diet: Traditional Kerala diet'", " Hrs_Outdoors 'Hours outdoors per day'", " Screen_Time_Parent 'Screen time >2 hrs/day (parent report)'", " Pesticide_Exp 'Pesticide/chemical exposure'", " Pesticide_Details 'Pesticide details'", " Age_Puberty_Signs 'Age puberty signs first noticed (years)'", " Doctor_Confirmed 'Doctor confirmed early puberty'", " Doctor_Details 'Doctor confirmation details'", " Parent_Consent 'Parent/guardian consent'", " S_Outdoor_Play 'Student: Plays outdoors daily'", " S_Outdoor_Hrs 'Student: Outdoor hours per day'", " S_Screen_Time 'Student: Screen time >2 hrs/day'", " S_Body_Changes 'Student: Noticed body changes'", " S_Age_Body_Changes 'Student: Age at body changes'", " S_Menarche 'Student: Had first menstrual period'", " S_Age_Menarche 'Student: Age at first period'", " S_Health_Notes 'Student: Additional health notes'", " S_Assent 'Student assent'", ".", "", "* ============================================================", "* STEP 3: Assign Value Labels", "* ============================================================", "VALUE LABELS", " Residence 1 'Urban' 2 'Semiurban' /", " Family_Type 1 'Nuclear' 2 'Joint' /", " Father_Edu Mother_Edu", " 1 'Illiterate' 2 'Primary' 3 'Secondary' 4 'Graduate' 5 'Post-graduate' /", " Income", " 1 '<10,000 INR' 2 '10,000-30,000 INR' 3 '30,000-50,000 INR' 4 '>50,000 INR' /", " Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_Parent", " Pesticide_Exp Doctor_Confirmed Parent_Consent", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent", " 1 'Yes' 0 'No' /", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " 1 'Yes' 0 'No' /", ".", "", "* ============================================================", "* STEP 4: Define Missing Values", "* ============================================================", "MISSING VALUES", " Age Grade (99)", " Residence Family_Type Father_Edu Mother_Edu Income (9)", " Chronic_Illness Fam_Hx_Puberty Medications (9)", " Age_Menarche (99)", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala (9)", " Hrs_Outdoors (99)", " Screen_Time_Parent Pesticide_Exp (9)", " Age_Puberty_Signs (99)", " Doctor_Confirmed Parent_Consent (9)", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent (9)", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche (99)", ".", "", "* ============================================================", "* STEP 5: Save as SPSS .sav file", "* ============================================================", "SAVE OUTFILE='/your/path/precocious_puberty_data.sav'", " /COMPRESSED.", "", "* End of syntax.", ] for i, line in enumerate(syntax_lines, 2): c = wb4.cell(row=i, column=1, value=line) c.font = Font(name="Courier New", size=9, color="000000") c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=False) if line.startswith("*"): c.font = Font(name="Courier New", size=9, color="375623") elif line.strip().endswith(".") and not line.startswith(" "): c.font = Font(name="Courier New", size=9, bold=True, color="1F4E79") wb4.row_dimensions[i].height = 13 # ═══════════════════════════════════════════════════════════════════ # SHEET 5 – INSTRUCTIONS # ═══════════════════════════════════════════════════════════════════ wb5 = wb.create_sheet("Instructions") wb5.sheet_view.showGridLines = False wb5.column_dimensions["A"].width = 5 wb5.column_dimensions["B"].width = 80 wb5.merge_cells("A1:B1") wb5["A1"].value = "DATA ENTRY INSTRUCTIONS" wb5["A1"].font = font(C_TITLE_FG, bold=True, sz=13) wb5["A1"].fill = fill(C_TITLE_BG) wb5["A1"].alignment = center() wb5.row_dimensions[1].height = 26 instructions = [ ("", "SHEET OVERVIEW", True), ("1", "Data Entry – Enter one row per participant. Each row = one girl in the study.", False), ("2", "SPSS Codebook – Full variable list with SPSS-ready names, types, and value labels.", False), ("3", "Value Codes (Reference) – Quick-look coding guide to keep open while entering data.", False), ("4", "SPSS Syntax – Paste this into SPSS Syntax Editor to import & label variables automatically.", False), ("", "", False), ("", "DATA ENTRY RULES", True), ("•", "Each row represents ONE participant (one school girl and her parent/guardian).", False), ("•", "The ID column is pre-filled (1–200). Do NOT change it.", False), ("•", "Enter the Date of Entry in DD/MM/YYYY format.", False), ("•", "Numeric fields: enter ONLY the code number shown in the column header (e.g. 1 for Yes, 0 for No).", False), ("•", "For Yes/No questions enter 1 (Yes) or 0 (No). Use 9 for missing/not answered.", False), ("•", "For age fields enter the age in years. Use 99 if not applicable or unknown.", False), ("•", "Diet (P13): each diet item is a SEPARATE column – enter 1 if the parent checked that item, 0 if not.", False), ("•", "Text fields (name, details, notes) accept free text. Keep entries concise.", False), ("•", "If a question was skipped, leave the cell blank OR enter the missing value code.", False), ("•", "Never delete column headers or the first three rows.", False), ("•", "Save a backup copy after each data entry session.", False), ("", "", False), ("", "EXPORTING TO SPSS", True), ("1", "Save this file as .xlsx.", False), ("2", "Open SPSS → File → Import Data → Excel.", False), ("3", "Select the 'Data Entry' sheet, check 'Read variable names from first row'.", False), ("4", "Go to the 'SPSS Syntax' sheet, copy all syntax, paste into SPSS Syntax Editor, and Run All.", False), ("5", "Save the SPSS dataset as a .sav file.", False), ("", "", False), ("", "MISSING VALUE CODES SUMMARY", True), ("9", "Used for Yes/No and categorical fields when answer is missing/not answered.", False), ("99", "Used for numeric/age/continuous fields when value is unknown or not applicable.", False), ("—", "Text fields: leave blank if no information.", False), ] r = 3 for num, text, is_header in instructions: if is_header: wb5.merge_cells(f"A{r}:B{r}") c = wb5[f"A{r}"] c.value = text c.font = font(C_SECTION_FG, bold=True, sz=11) c.fill = fill(C_SECTION_BG) c.alignment = left() c.border = thin wb5.row_dimensions[r].height = 20 else: wb5.cell(row=r, column=1, value=num).font = font("1F4E79", bold=True, sz=9) wb5.cell(row=r, column=1).fill = fill(C_ALT_BG if r%2==0 else "FFFFFF") wb5.cell(row=r, column=1).alignment = center() c = wb5.cell(row=r, column=2, value=text) c.font = font("000000", sz=9) c.fill = fill(C_ALT_BG if r%2==0 else "FFFFFF") c.alignment = left(wrap=True) wb5.row_dimensions[r].height = 18 if text else 8 r += 1 # ── Tab colours ───────────────────────────────────────────────── ws.sheet_properties.tabColor = "1F4E79" wb2.sheet_properties.tabColor = "2E75B6" wb3.sheet_properties.tabColor = "7F6000" wb4.sheet_properties.tabColor = "375623" wb5.sheet_properties.tabColor = "C55A11" # ── Save ──────────────────────────────────────────────────────── out_path = "/home/daytona/workspace/precocious-puberty-study/Precocious_Puberty_DataEntry_SPSS.xlsx" wb.save(out_path) print(f"Saved to: {out_path}") PYEOF

ls -la /home/daytona/workspace/ && ls -la /home/daytona/workspace/precocious-puberty-study/ 2>/dev/null || echo "dir does not exist"

mkdir -p /home/daytona/workspace/precocious-puberty-study && echo "OK"

python3 << 'PYEOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.Workbook() # ── Colours ────────────────────────────────────────────────────── C_TITLE_BG = "1F4E79" C_TITLE_FG = "FFFFFF" C_SEC_BG = "2E75B6" C_SEC_FG = "FFFFFF" C_HDR_BG = "BDD7EE" C_HDR_FG = "1F4E79" C_ALT = "EBF3FB" C_CODE_BG = "FFF2CC" C_CODE_FG = "7F6000" def fl(hex): return PatternFill("solid", fgColor=hex) def ft(hex, bold=False, sz=9, italic=False, name="Arial"): return Font(name=name, size=sz, bold=bold, italic=italic, color=hex) def bd(): s = Side(style="thin") return Border(left=s, right=s, top=s, bottom=s) def al(h="left", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) # ═══════════════════════════════════════════════ # SHEET 1 – DATA ENTRY # ═══════════════════════════════════════════════ ws = wb.active ws.title = "Data Entry" ws.sheet_view.showGridLines = False ws.freeze_panes = "C4" # Row 1 title ws.merge_cells("A1:AM1") c = ws["A1"] c.value = "PRECOCIOUS PUBERTY STUDY – DATA ENTRY DATABASE" c.font = ft(C_TITLE_FG, bold=True, sz=14) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ws.row_dimensions[1].height = 28 # Row 2 subtitle ws.merge_cells("A2:AM2") c = ws["A2"] c.value = "Study: Prevalence of Precocious Puberty in School Girls | Location: Manjeri & Anakkayam | Age: 10-15 years" c.font = ft(C_TITLE_FG, sz=9, italic=True) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ws.row_dimensions[2].height = 16 # ── Column definitions ────────────────────────────────────────── # (excel_col, width, header, spss_name, val_type) cols = [ # Admin ("A", 7, "ID", "ID", "num"), ("B", 13, "Date of Entry\n(DD/MM/YYYY)", "Date_Entry", "date"), # Section 1 – Demographics ("C", 22, "P1. Child Name\n(Optional)", "Child_Name", "txt"), ("D", 8, "P2. Age\n(10-15 yrs)", "Age", "num"), ("E", 8, "P3. Grade/\nStandard", "Grade", "num"), ("F", 18, "P4. School Name", "School_Name", "txt"), ("G", 12, "P5. Residence\n1=Urban\n2=Semiurban","Residence", "num"), ("H", 12, "P6. Family Type\n1=Nuclear\n2=Joint","Family_Type", "num"), ("I", 13, "P7a. Father\nEducation\n(1-5)", "Father_Edu", "num"), ("J", 13, "P7b. Mother\nEducation\n(1-5)", "Mother_Edu", "num"), ("K", 13, "P8. Monthly\nIncome (1-4)", "Income", "num"), # Section 2 – Medical ("L", 11, "P9. Chronic\nIllness\n1=Yes 0=No", "Chronic_Illness", "num"), ("M", 22, "P9. Illness\nDetails", "Illness_Details", "txt"), ("N", 11, "P10. Family Hx\nEarly Puberty\n1=Yes 0=No","Fam_Hx_Puberty","num"), ("O", 11, "P11. Medications\n/Hormones\n1=Yes 0=No","Medications", "num"), ("P", 22, "P11. Medication\nDetails", "Medication_Details", "txt"), ("Q", 11, "P12. Age at\nMenarche (yrs)\n99=Not yet","Age_Menarche", "num"), # Section 3 – Lifestyle ("R", 12, "P13a. Home-\ncooked Meals\n1=Yes 0=No","Diet_Homecooked", "num"), ("S", 12, "P13b. Fast Food\n>3x/wk\n1=Yes 0=No","Diet_FastFood", "num"), ("T", 12, "P13c. Processed\nFoods\n1=Yes 0=No", "Diet_Processed", "num"), ("U", 12, "P13d. High\nProtein\n1=Yes 0=No", "Diet_HighProtein", "num"), ("V", 12, "P13e. Trad Kerala\nDiet\n1=Yes 0=No","Diet_TraditionalKerala","num"), ("W", 11, "P14. Hours\nOutdoors/Day", "Hrs_Outdoors", "num"), ("X", 11, "P15. Screen Time\n>2 hrs/day\n1=Yes 0=No","Screen_Time_P", "num"), ("Y", 11, "P16. Pesticide\nExposure\n1=Yes 0=No","Pesticide_Exp", "num"), ("Z", 22, "P16. Pesticide\nDetails", "Pesticide_Details", "txt"), # Section 4 – Physical ("AA",11, "P17. Age Puberty\nSigns (yrs)\n99=Unknown","Age_Puberty_Signs","num"), ("AB",11, "P18. Doctor\nConfirmed\n1=Yes 0=No", "Doctor_Confirmed", "num"), ("AC",22, "P18. Doctor\nDetails", "Doctor_Details", "txt"), # Section 5 – Consent ("AD",11, "P19. Parent\nConsent\n1=Yes 0=No", "Parent_Consent", "num"), # Student Q ("AE",11, "S1. Outdoor\nPlay Daily\n1=Yes 0=No", "S_Outdoor_Play", "num"), ("AF",11, "S1. Outdoor\nHours/Day", "S_Outdoor_Hrs", "num"), ("AG",11, "S2. Screen Time\n>2 hrs/day\n1=Yes 0=No","S_Screen_Time", "num"), ("AH",11, "S3. Body\nChanges\n1=Yes 0=No", "S_Body_Changes", "num"), ("AI",11, "S3. Age Body\nChanges (yrs)\n99=DK", "S_Age_Body_Changes", "num"), ("AJ",11, "S4. First\nPeriod\n1=Yes 0=No", "S_Menarche", "num"), ("AK",11, "S4. Age First\nPeriod (yrs)\n99=NA", "S_Age_Menarche", "num"), ("AL",22, "S5. Health\nNotes (open)", "S_Health_Notes", "txt"), ("AM",11, "S6. Student\nAssent\n1=Yes 0=No", "S_Assent", "num"), ] # Section separator row coloring in header sec_cols = { "C": "PARENT Q – SEC 1: Demographics", "L": "SEC 2: Medical & Family History", "R": "SEC 3: Lifestyle & Environment", "AA":"SEC 4: Physical Development", "AD":"SEC 5: Consent", "AE":"STUDENT QUESTIONNAIRE", } # Write header row 3 ws.row_dimensions[3].height = 62 for col_letter, col_w, hdr, spss, vtype in cols: c = ws[f"{col_letter}3"] c.value = hdr c.font = ft(C_HDR_FG, bold=True, sz=8) c.fill = fl(C_HDR_BG) c.alignment = al("center", wrap=True) c.border = bd() ws.column_dimensions[col_letter].width = col_w # Colour section starts differently sec_colors = { "C": "1F4E79", "L": "1F5C8B", "R": "174478", "AA":"163d6e", "AD":"0F2D54", "AE":"7B3F00", } for scol, _ in sec_cols.items(): c = ws[f"{scol}3"] c.fill = fl(sec_colors.get(scol, C_HDR_BG)) c.font = ft("FFFFFF", bold=True, sz=8) # Data rows 4-203 for row in range(4, 204): rbg = C_ALT if row % 2 == 0 else "FFFFFF" # ID ic = ws[f"A{row}"] ic.value = row - 3 ic.font = ft("000000", bold=True, sz=9) ic.fill = fl("F2F2F2") ic.alignment = al("center") ic.border = bd() # other cols for col_letter, _, _, _, vtype in cols[1:]: c = ws[f"{col_letter}{row}"] c.fill = fl(rbg) c.alignment = al("left") c.border = bd() c.font = ft("000000", sz=9) if vtype == "num": c.number_format = "0" elif vtype == "date": c.number_format = "DD/MM/YYYY" # ── Data Validations ───────────────────────────────────────────── def dv_list(f1, sqref): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid", error="Use the dropdown.") dv.sqref = sqref; return dv def dv_whole(mn, mx, sqref, msg=""): dv = DataValidation(type="whole", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=msg or f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def dv_dec(mn, mx, sqref): dv = DataValidation(type="decimal", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def cr(col, s=4, e=203): return f"{col}{s}:{col}{e}" yn = ["L","N","O","R","S","T","U","V","X","Y","AB","AD","AE","AG","AH","AJ","AM"] for c in yn: ws.add_data_validation(dv_list('"1,0"', cr(c))) ws.add_data_validation(dv_whole(10, 15, cr("D"), "Age must be 10-15")) ws.add_data_validation(dv_whole(1, 12, cr("E"), "Grade 1-12")) ws.add_data_validation(dv_list('"1,2"', cr("G"))) ws.add_data_validation(dv_list('"1,2"', cr("H"))) ws.add_data_validation(dv_list('"1,2,3,4,5"', cr("I"))) ws.add_data_validation(dv_list('"1,2,3,4,5"', cr("J"))) ws.add_data_validation(dv_list('"1,2,3,4"', cr("K"))) ws.add_data_validation(dv_dec(0, 30, cr("Q"))) ws.add_data_validation(dv_dec(0, 24, cr("W"))) ws.add_data_validation(dv_dec(0, 24, cr("AF"))) ws.add_data_validation(dv_dec(4, 20, cr("AA"))) ws.add_data_validation(dv_dec(0, 20, cr("AI"))) ws.add_data_validation(dv_dec(8, 20, cr("AK"))) ws.sheet_properties.tabColor = "1F4E79" # ═══════════════════════════════════════════════ # SHEET 2 – SPSS CODEBOOK # ═══════════════════════════════════════════════ cb = wb.create_sheet("SPSS Codebook") cb.sheet_view.showGridLines = False cb.sheet_properties.tabColor = "2E75B6" for col, w in [("A",5),("B",26),("C",32),("D",12),("E",10),("F",45),("G",22)]: cb.column_dimensions[col].width = w cb.merge_cells("A1:G1") c = cb["A1"] c.value = "SPSS VARIABLE CODEBOOK – Precocious Puberty Study" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") cb.row_dimensions[1].height = 26 for i, h in enumerate(["#","SPSS Variable Name","Question Label","Type","Missing Code","Value Labels","Notes"],1): c = cb.cell(row=2, column=i, value=h) c.font = ft(C_HDR_FG, bold=True, sz=10) c.fill = fl(C_HDR_BG) c.alignment = al("center", wrap=True) c.border = bd() cb.row_dimensions[2].height = 20 codebook = [ # sec, num, varname, label, type, missing, value_labels, notes ("ADMIN", 1,"ID","Participant serial number","Numeric","—","1,2,3… (auto)",""), ("ADMIN", 2,"Date_Entry","Date of data entry","Date","—","DD/MM/YYYY",""), ("SEC1", 3,"Child_Name","P1. Child name (optional)","String","—","Text","Leave blank = anonymous"), ("SEC1", 4,"Age","P2. Age of child (years)","Numeric","99","Integer 10–15",""), ("SEC1", 5,"Grade","P3. Grade / Standard","Numeric","99","Integer 1-12",""), ("SEC1", 6,"School_Name","P4. School name","String","—","Text",""), ("SEC1", 7,"Residence","P5. Residence type","Numeric","9","1=Urban 2=Semiurban",""), ("SEC1", 8,"Family_Type","P6. Family type","Numeric","9","1=Nuclear 2=Joint",""), ("SEC1", 9,"Father_Edu","P7a. Father education","Numeric","9","1=Illiterate 2=Primary 3=Secondary\n4=Graduate 5=Post-graduate",""), ("SEC1", 10,"Mother_Edu","P7b. Mother education","Numeric","9","1=Illiterate 2=Primary 3=Secondary\n4=Graduate 5=Post-graduate",""), ("SEC1", 11,"Income","P8. Monthly family income (INR)","Numeric","9","1=<10,000 2=10,000-30,000\n3=30,000-50,000 4=>50,000",""), ("SEC2", 12,"Chronic_Illness","P9. Chronic illness present","Numeric","9","1=Yes 0=No",""), ("SEC2", 13,"Illness_Details","P9. Illness details","String","—","Text","If Chronic_Illness=1"), ("SEC2", 14,"Fam_Hx_Puberty","P10. Family history early puberty","Numeric","9","1=Yes 0=No","Mother/sisters"), ("SEC2", 15,"Medications","P11. Medications/hormones used","Numeric","9","1=Yes 0=No",""), ("SEC2", 16,"Medication_Details","P11. Medication details","String","—","Text","If Medications=1"), ("SEC2", 17,"Age_Menarche","P12. Age at menarche (years)","Numeric","99","Continuous (years)","99=Not yet occurred"), ("SEC3", 18,"Diet_Homecooked","P13a. Home-cooked meals","Numeric","9","1=Yes 0=No","Multi-select item"), ("SEC3", 19,"Diet_FastFood","P13b. Fast food >3x/week","Numeric","9","1=Yes 0=No",""), ("SEC3", 20,"Diet_Processed","P13c. Processed foods","Numeric","9","1=Yes 0=No",""), ("SEC3", 21,"Diet_HighProtein","P13d. High protein foods","Numeric","9","1=Yes 0=No",""), ("SEC3", 22,"Diet_TraditionalKerala","P13e. Traditional Kerala diet","Numeric","9","1=Yes 0=No",""), ("SEC3", 23,"Hrs_Outdoors","P14. Hours outdoors per day","Numeric","99","Continuous (hours)",""), ("SEC3", 24,"Screen_Time_P","P15. Screen time >2 hrs/day (parent)","Numeric","9","1=Yes 0=No",""), ("SEC3", 25,"Pesticide_Exp","P16. Pesticide/chemical exposure","Numeric","9","1=Yes 0=No",""), ("SEC3", 26,"Pesticide_Details","P16. Pesticide details","String","—","Text","If Pesticide_Exp=1"), ("SEC4", 27,"Age_Puberty_Signs","P17. Age puberty signs noticed (yrs)","Numeric","99","Continuous (years)",""), ("SEC4", 28,"Doctor_Confirmed","P18. Doctor confirmed early puberty","Numeric","9","1=Yes 0=No",""), ("SEC4", 29,"Doctor_Details","P18. Doctor details","String","—","Text","If Doctor_Confirmed=1"), ("SEC5", 30,"Parent_Consent","P19. Parent/guardian consent","Numeric","9","1=Yes 0=No","Exclude if 0"), ("STUQ", 31,"S_Outdoor_Play","S1. Plays outdoors daily","Numeric","9","1=Yes 0=No",""), ("STUQ", 32,"S_Outdoor_Hrs","S1. Outdoor hours per day","Numeric","99","Continuous (hours)","If S_Outdoor_Play=1"), ("STUQ", 33,"S_Screen_Time","S2. Screen time >2 hrs/day","Numeric","9","1=Yes 0=No",""), ("STUQ", 34,"S_Body_Changes","S3. Noticed body changes","Numeric","9","1=Yes 0=No",""), ("STUQ", 35,"S_Age_Body_Changes","S3. Age at body changes (yrs)","Numeric","99","Continuous; 99=Don't know","If S_Body_Changes=1"), ("STUQ", 36,"S_Menarche","S4. Had first menstrual period","Numeric","9","1=Yes 0=No",""), ("STUQ", 37,"S_Age_Menarche","S4. Age at first period (yrs)","Numeric","99","Continuous (years)","If S_Menarche=1"), ("STUQ", 38,"S_Health_Notes","S5. Additional health notes","String","—","Free text",""), ("STUQ", 39,"S_Assent","S6. Student assent","Numeric","9","1=Yes 0=No","Exclude if 0"), ] sec_labels = { "ADMIN": "ADMIN", "SEC1": "PARENT QUESTIONNAIRE – Section 1: Demographic Information", "SEC2": "PARENT QUESTIONNAIRE – Section 2: Medical & Family History", "SEC3": "PARENT QUESTIONNAIRE – Section 3: Lifestyle & Environmental Factors", "SEC4": "PARENT QUESTIONNAIRE – Section 4: Physical Development", "SEC5": "PARENT QUESTIONNAIRE – Section 5: Consent", "STUQ": "STUDENT QUESTIONNAIRE", } row = 3 prev_sec = None for entry in codebook: sec, num, varname, label, vtype, miss, vals, notes = entry if sec != prev_sec: cb.merge_cells(f"A{row}:G{row}") sc = cb[f"A{row}"] sc.value = sec_labels[sec] sc.font = ft(C_SEC_FG, bold=True, sz=10) sc.fill = fl(C_SEC_BG) sc.alignment = al("left") sc.border = bd() cb.row_dimensions[row].height = 18 row += 1 prev_sec = sec rbg = C_ALT if row % 2 == 0 else "FFFFFF" for ci, val in enumerate([num, varname, label, vtype, miss, vals, notes], 1): c = cb.cell(row=row, column=ci, value=val) c.font = ft("000000", sz=9) c.fill = fl(rbg) c.border = bd() c.alignment = al("center" if ci == 1 else "left", wrap=True) cb.row_dimensions[row].height = 30 row += 1 # ═══════════════════════════════════════════════ # SHEET 3 – VALUE CODES QUICK REFERENCE # ═══════════════════════════════════════════════ vr = wb.create_sheet("Value Codes") vr.sheet_view.showGridLines = False vr.sheet_properties.tabColor = "7F6000" vr.column_dimensions["A"].width = 35 vr.column_dimensions["B"].width = 55 vr.merge_cells("A1:B1") c = vr["A1"] c.value = "QUICK REFERENCE – VALUE CODES FOR DATA ENTRY" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") vr.row_dimensions[1].height = 26 ref_data = [ ("YES / NO FIELDS (all binary questions)", "1 = Yes\n0 = No\n9 = Missing / Not answered"), ("MISSING VALUES – numeric/age fields", "99 = Unknown / Not applicable / Not yet occurred"), ("P5 – RESIDENCE", "1 = Urban\n2 = Semiurban"), ("P6 – FAMILY TYPE", "1 = Nuclear\n2 = Joint"), ("P7 – EDUCATION (Father & Mother)", "1 = Illiterate\n2 = Primary\n3 = Secondary\n4 = Graduate\n5 = Post-graduate"), ("P8 – MONTHLY INCOME (INR)", "1 = Less than Rs.10,000\n2 = Rs.10,000 – Rs.30,000\n3 = Rs.30,000 – Rs.50,000\n4 = More than Rs.50,000"), ("P12 – AGE AT MENARCHE", "Enter actual age in years (e.g. 12)\n99 = Menarche not yet occurred"), ("P13 – DIET (each item = separate column)", "1 = Parent checked / selected this item\n0 = Not selected"), ("P14 & S1 – OUTDOOR HOURS", "Enter number of hours (e.g. 2 or 2.5)\n99 = Not reported"), ("P17 – AGE PUBERTY SIGNS NOTICED", "Enter age in years\n99 = Not noticed / Unknown"), ("S3 – AGE AT BODY CHANGES", "Enter age in years\n99 = Don't know\nLeave blank if S_Body_Changes = 0"), ("S4 – AGE AT FIRST PERIOD", "Enter age in years\n99 = Not applicable\nLeave blank if S_Menarche = 0"), ("CONSENT & ASSENT", "1 = Consent/Assent given\n0 = Refused\nIMPORTANT: Exclude participant from SPSS analysis if = 0"), ] r = 3 for label, codes in ref_data: lc = vr.cell(row=r, column=1, value=label) lc.font = ft(C_CODE_FG, bold=True, sz=9) lc.fill = fl(C_CODE_BG) lc.border = bd() lc.alignment = al("left") cc = vr.cell(row=r, column=2, value=codes) cc.font = ft("000000", sz=9) cc.fill = fl(C_CODE_BG) cc.border = bd() cc.alignment = al("left", wrap=True) lines = codes.count("\n") + 1 vr.row_dimensions[r].height = max(18, lines * 14 + 6) r += 1 # spacer vr.row_dimensions[r].height = 6 r += 1 # ═══════════════════════════════════════════════ # SHEET 4 – SPSS SYNTAX # ═══════════════════════════════════════════════ sx = wb.create_sheet("SPSS Syntax") sx.sheet_view.showGridLines = False sx.sheet_properties.tabColor = "375623" sx.column_dimensions["A"].width = 100 sx.merge_cells("A1:A1") c = sx["A1"] c.value = "SPSS IMPORT SYNTAX – Copy into SPSS Syntax Editor and click Run > All" c.font = ft(C_TITLE_FG, bold=True, sz=12) c.fill = fl(C_TITLE_BG) c.alignment = al("center") sx.row_dimensions[1].height = 24 syntax = [ "", "* ===========================================================", "* STEP 1 – Import Excel into SPSS", "* ===========================================================", "* SPSS Menu: File > Import Data > Excel", "* Sheet : Data Entry", "* Header row: 3 (check 'Read variable names from first row')", "* Data starts: row 4", "", "* ===========================================================", "* STEP 2 – Variable Labels", "* ===========================================================", "VARIABLE LABELS", " ID 'Participant serial number'", " Date_Entry 'Date of data entry'", " Child_Name 'P1. Child name (optional)'", " Age 'P2. Age of child (years)'", " Grade 'P3. Grade or Standard'", " School_Name 'P4. School name'", " Residence 'P5. Residence type'", " Family_Type 'P6. Family type'", " Father_Edu 'P7a. Father education level'", " Mother_Edu 'P7b. Mother education level'", " Income 'P8. Monthly family income INR'", " Chronic_Illness 'P9. Chronic illness present'", " Illness_Details 'P9. Illness details'", " Fam_Hx_Puberty 'P10. Family history of early puberty'", " Medications 'P11. Medications or hormones used'", " Medication_Details 'P11. Medication details'", " Age_Menarche 'P12. Age at menarche (years)'", " Diet_Homecooked 'P13a. Diet: Home-cooked meals'", " Diet_FastFood 'P13b. Diet: Fast food more than 3 times per week'", " Diet_Processed 'P13c. Diet: Processed foods'", " Diet_HighProtein 'P13d. Diet: High protein foods'", " Diet_TraditionalKerala 'P13e. Diet: Traditional Kerala diet'", " Hrs_Outdoors 'P14. Hours outdoors per day'", " Screen_Time_P 'P15. Screen time more than 2 hours per day (parent report)'", " Pesticide_Exp 'P16. Pesticide or chemical exposure'", " Pesticide_Details 'P16. Pesticide details'", " Age_Puberty_Signs 'P17. Age puberty signs first noticed (years)'", " Doctor_Confirmed 'P18. Doctor confirmed early puberty'", " Doctor_Details 'P18. Doctor confirmation details'", " Parent_Consent 'P19. Parent or guardian consent'", " S_Outdoor_Play 'S1. Student plays outdoors daily'", " S_Outdoor_Hrs 'S1. Student outdoor hours per day'", " S_Screen_Time 'S2. Student screen time more than 2 hours per day'", " S_Body_Changes 'S3. Student noticed body changes'", " S_Age_Body_Changes 'S3. Student age at body changes'", " S_Menarche 'S4. Student had first menstrual period'", " S_Age_Menarche 'S4. Student age at first period'", " S_Health_Notes 'S5. Student additional health notes'", " S_Assent 'S6. Student assent'", ".", "", "* ===========================================================", "* STEP 3 – Value Labels", "* ===========================================================", "VALUE LABELS", " Residence 1 'Urban' 2 'Semiurban' /", " Family_Type 1 'Nuclear' 2 'Joint' /", " Father_Edu Mother_Edu", " 1 'Illiterate' 2 'Primary' 3 'Secondary' 4 'Graduate' 5 'Post-graduate' /", " Income", " 1 'Less than 10000' 2 '10000 to 30000' 3 '30000 to 50000' 4 'More than 50000' /", " Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp", " Doctor_Confirmed Parent_Consent", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent", " 1 'Yes' 0 'No' /", ".", "", "* ===========================================================", "* STEP 4 – Missing Values", "* ===========================================================", "MISSING VALUES", " Age Grade Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche (99)", " Residence Family_Type Father_Edu Mother_Edu Income", " Chronic_Illness Fam_Hx_Puberty Medications", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " Screen_Time_P Pesticide_Exp Doctor_Confirmed Parent_Consent", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent (9)", ".", "", "* ===========================================================", "* STEP 5 – Save as SPSS .sav file", "* ===========================================================", "SAVE OUTFILE='/your/path/here/precocious_puberty_data.sav'", " /COMPRESSED.", "", "* End of syntax.", ] for i, line in enumerate(syntax, 2): c = sx.cell(row=i, column=1, value=line) if line.startswith("*"): c.font = Font(name="Courier New", size=9, color="375623") elif any(line.startswith(k) for k in ["VARIABLE","VALUE","MISSING","SAVE"]): c.font = Font(name="Courier New", size=9, bold=True, color="1F4E79") else: c.font = Font(name="Courier New", size=9, color="000000") c.alignment = Alignment(horizontal="left", vertical="top") sx.row_dimensions[i].height = 13 # ═══════════════════════════════════════════════ # SHEET 5 – INSTRUCTIONS # ═══════════════════════════════════════════════ ins = wb.create_sheet("Instructions") ins.sheet_view.showGridLines = False ins.sheet_properties.tabColor = "C55A11" ins.column_dimensions["A"].width = 5 ins.column_dimensions["B"].width = 85 ins.merge_cells("A1:B1") c = ins["A1"] c.value = "HOW TO USE THIS DATABASE" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ins.row_dimensions[1].height = 26 guide = [ ("SECTION", "WHAT EACH SHEET DOES"), ("Data Entry", "Main data entry sheet. One row = one participant (girl + parent). Pre-filled with 200 rows."), ("SPSS Codebook", "Full list of all 39 variables with SPSS names, types, value labels, and missing codes."), ("Value Codes", "Quick-look reference. Keep this open on a second screen while entering data."), ("SPSS Syntax", "Ready-to-use SPSS syntax. Paste into Syntax Editor after importing to label all variables."), ("Instructions", "This sheet – read before starting data entry."), ("SECTION", "DATA ENTRY RULES"), ("One row per girl", "Enter both the Parent questionnaire AND the Student questionnaire in the SAME row."), ("ID column", "Pre-filled 1-200. Do NOT change or delete."), ("Date column", "Enter date of data collection in DD/MM/YYYY format."), ("Yes/No fields", "Enter 1 for Yes, 0 for No. Enter 9 if the question was not answered (missing)."), ("Age fields", "Enter the actual age in years. Enter 99 if unknown or not applicable."), ("Diet (P13)", "Each diet type is a SEPARATE column. Enter 1 if the parent ticked it, 0 if not."), ("Text fields", "Name, details, notes – enter free text. Leave blank if not provided."), ("Dropdowns", "Most numeric fields have dropdown validation. Select from the list to avoid errors."), ("Skipped questions","Leave the cell blank OR enter the missing value code (9 or 99)."), ("SECTION", "EXPORTING TO SPSS"), ("Step 1", "Save this file as .xlsx"), ("Step 2", "Open SPSS > File > Import Data > Excel"), ("Step 3", "Select the 'Data Entry' sheet; set header row = 3; tick 'Read variable names from first row'"), ("Step 4", "Go to 'SPSS Syntax' sheet, copy ALL text, paste into SPSS Syntax Editor, click Run > All"), ("Step 5", "All variables will now have labels and missing value codes. Save as .sav file."), ("SECTION", "MISSING VALUE CODES"), ("9", "Use for YES/NO and all categorical variables when the answer is missing or not given."), ("99", "Use for age, hours, and all continuous variables when value is unknown or not applicable."), ("blank","Text/string fields: simply leave empty if no information was provided."), ("SECTION", "IMPORTANT REMINDERS"), ("Consent", "If Parent_Consent = 0, EXCLUDE that participant from SPSS analysis."), ("Assent", "If S_Assent = 0, EXCLUDE that participant from SPSS analysis."), ("Backup", "Save a dated backup copy after every data entry session."), ("200 rows", "The sheet has 200 rows. Add more rows if your sample exceeds 200."), ] r = 3 for item in guide: if item[0] == "SECTION": ins.merge_cells(f"A{r}:B{r}") sc = ins[f"A{r}"] sc.value = item[1] sc.font = ft(C_SEC_FG, bold=True, sz=10) sc.fill = fl(C_SEC_BG) sc.alignment = al("left") sc.border = bd() ins.row_dimensions[r].height = 20 else: label, text = item lc = ins.cell(row=r, column=1, value=label) lc.font = ft("1F4E79", bold=True, sz=9) lc.fill = fl(C_ALT if r%2==0 else "FFFFFF") lc.alignment = al("center") tc = ins.cell(row=r, column=2, value=text) tc.font = ft("000000", sz=9) tc.fill = fl(C_ALT if r%2==0 else "FFFFFF") tc.alignment = al("left", wrap=True) ins.row_dimensions[r].height = 18 r += 1 # ── Save ──────────────────────────────────────────────────────── out = "/home/daytona/workspace/precocious-puberty-study/Precocious_Puberty_DataEntry_SPSS.xlsx" wb.save(out) print("SAVED:", out) PYEOF

python3 -c " import openpyxl, shutil wb = openpyxl.Workbook() ws = wb.active ws['A1'] = 'test' wb.save('/tmp/test_write.xlsx') shutil.copy('/tmp/test_write.xlsx', '/home/daytona/workspace/precocious-puberty-study/test_write.xlsx') print('OK') "

ls -la /home/daytona/workspace/precocious-puberty-study/ && python3 -c " import openpyxl, shutil, os wb = openpyxl.Workbook() ws = wb.active ws['A1'] = 'test' wb.save('/tmp/test_write.xlsx') # check /tmp file print('tmp file size:', os.path.getsize('/tmp/test_write.xlsx')) # try writing directly to workspace shutil.copy('/tmp/test_write.xlsx', '/home/daytona/workspace/test_write.xlsx') print('workspace copy OK') "

python3 -c " import openpyxl, shutil, os wb = openpyxl.Workbook() ws = wb.active ws['A1'] = 'test' wb.save('/tmp/test_write.xlsx') print('tmp file size:', os.path.getsize('/tmp/test_write.xlsx')) shutil.copy('/tmp/test_write.xlsx', '/home/daytona/workspace/test_write.xlsx') print('workspace copy OK') "

# Try copyfile (without permissions copy) instead of copy python3 -c " import openpyxl, shutil, os wb = openpyxl.Workbook() ws = wb.active ws['A1'] = 'test' wb.save('/tmp/test_write.xlsx') shutil.copyfile('/tmp/test_write.xlsx', '/home/daytona/workspace/test_write.xlsx') print('OK - size:', os.path.getsize('/home/daytona/workspace/test_write.xlsx')) "

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.Workbook() # ── Colours ────────────────────────────────────────────────────── C_TITLE_BG = "1F4E79" C_TITLE_FG = "FFFFFF" C_SEC_BG = "2E75B6" C_SEC_FG = "FFFFFF" C_HDR_BG = "BDD7EE" C_HDR_FG = "1F4E79" C_ALT = "EBF3FB" C_CODE_BG = "FFF2CC" C_CODE_FG = "7F6000" def fl(hex_c): return PatternFill("solid", fgColor=hex_c) def ft(hex_c, bold=False, sz=9, italic=False, name="Arial"): return Font(name=name, size=sz, bold=bold, italic=italic, color=hex_c) def bd(): s = Side(style="thin") return Border(left=s, right=s, top=s, bottom=s) def al(h="left", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) # ═══════════════════════════════════════════════ # SHEET 1 – DATA ENTRY # ═══════════════════════════════════════════════ ws = wb.active ws.title = "Data Entry" ws.sheet_view.showGridLines = False ws.freeze_panes = "C4" # Row 1 title ws.merge_cells("A1:AM1") c = ws["A1"] c.value = "PRECOCIOUS PUBERTY STUDY - DATA ENTRY DATABASE" c.font = ft(C_TITLE_FG, bold=True, sz=14) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ws.row_dimensions[1].height = 28 # Row 2 subtitle ws.merge_cells("A2:AM2") c = ws["A2"] c.value = "Study: Prevalence of Precocious Puberty in School Girls | Location: Manjeri & Anakkayam | Age: 10-15 years" c.font = ft(C_TITLE_FG, sz=9, italic=True) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ws.row_dimensions[2].height = 16 # ── Column definitions ────────────────────────────────────────── cols = [ # (col_letter, width, header, spss_name, val_type) ("A", 7, "ID", "ID", "num"), ("B", 13, "Date of Entry\n(DD/MM/YYYY)", "Date_Entry", "date"), ("C", 22, "P1. Child Name\n(Optional)", "Child_Name", "txt"), ("D", 8, "P2. Age\n(10-15 yrs)", "Age", "num"), ("E", 8, "P3. Grade/\nStandard", "Grade", "num"), ("F", 18, "P4. School Name", "School_Name", "txt"), ("G", 12, "P5. Residence\n1=Urban\n2=Semiurban","Residence", "num"), ("H", 12, "P6. Family Type\n1=Nuclear\n2=Joint","Family_Type", "num"), ("I", 13, "P7a. Father\nEducation\n(1-5)", "Father_Edu", "num"), ("J", 13, "P7b. Mother\nEducation\n(1-5)", "Mother_Edu", "num"), ("K", 13, "P8. Monthly\nIncome (1-4)", "Income", "num"), ("L", 11, "P9. Chronic\nIllness\n1=Yes 0=No", "Chronic_Illness", "num"), ("M", 22, "P9. Illness\nDetails", "Illness_Details", "txt"), ("N", 11, "P10. Family Hx\nEarly Puberty\n1=Yes 0=No","Fam_Hx_Puberty", "num"), ("O", 11, "P11. Medications\n/Hormones\n1=Yes 0=No","Medications", "num"), ("P", 22, "P11. Medication\nDetails", "Medication_Details", "txt"), ("Q", 11, "P12. Age at\nMenarche (yrs)\n99=Not yet","Age_Menarche", "num"), ("R", 12, "P13a. Home-\ncooked Meals\n1=Yes 0=No","Diet_Homecooked", "num"), ("S", 12, "P13b. Fast Food\n>3x/wk\n1=Yes 0=No","Diet_FastFood", "num"), ("T", 12, "P13c. Processed\nFoods\n1=Yes 0=No", "Diet_Processed", "num"), ("U", 12, "P13d. High\nProtein\n1=Yes 0=No", "Diet_HighProtein", "num"), ("V", 12, "P13e. Trad Kerala\nDiet\n1=Yes 0=No","Diet_TraditionalKerala", "num"), ("W", 11, "P14. Hours\nOutdoors/Day", "Hrs_Outdoors", "num"), ("X", 11, "P15. Screen Time\n>2 hrs/day\n1=Yes 0=No","Screen_Time_P", "num"), ("Y", 11, "P16. Pesticide\nExposure\n1=Yes 0=No","Pesticide_Exp", "num"), ("Z", 22, "P16. Pesticide\nDetails", "Pesticide_Details", "txt"), ("AA",11, "P17. Age Puberty\nSigns (yrs)\n99=Unknown","Age_Puberty_Signs","num"), ("AB",11, "P18. Doctor\nConfirmed\n1=Yes 0=No", "Doctor_Confirmed", "num"), ("AC",22, "P18. Doctor\nDetails", "Doctor_Details", "txt"), ("AD",11, "P19. Parent\nConsent\n1=Yes 0=No", "Parent_Consent", "num"), ("AE",11, "S1. Outdoor\nPlay Daily\n1=Yes 0=No", "S_Outdoor_Play", "num"), ("AF",11, "S1. Outdoor\nHours/Day", "S_Outdoor_Hrs", "num"), ("AG",11, "S2. Screen Time\n>2 hrs/day\n1=Yes 0=No","S_Screen_Time", "num"), ("AH",11, "S3. Body\nChanges\n1=Yes 0=No", "S_Body_Changes", "num"), ("AI",11, "S3. Age Body\nChanges (yrs)\n99=DK", "S_Age_Body_Changes", "num"), ("AJ",11, "S4. First\nPeriod\n1=Yes 0=No", "S_Menarche", "num"), ("AK",11, "S4. Age First\nPeriod (yrs)\n99=NA", "S_Age_Menarche", "num"), ("AL",22, "S5. Health\nNotes (open)", "S_Health_Notes", "txt"), ("AM",11, "S6. Student\nAssent\n1=Yes 0=No", "S_Assent", "num"), ] # Section-start columns for special colouring sec_start = {"C":"1F4E79","L":"1A5276","R":"154360", "AA":"0E3251","AD":"0B2441","AE":"6E2F00"} # Write header row 3 ws.row_dimensions[3].height = 62 for col_letter, col_w, hdr, spss, vtype in cols: c = ws[f"{col_letter}3"] c.value = hdr bg = sec_start.get(col_letter, C_HDR_BG) fg = C_TITLE_FG if col_letter in sec_start else C_HDR_FG c.font = ft(fg, bold=True, sz=8) c.fill = fl(bg) c.alignment = al("center", wrap=True) c.border = bd() ws.column_dimensions[col_letter].width = col_w # Data rows 4-203 for row in range(4, 204): rbg = C_ALT if row % 2 == 0 else "FFFFFF" ic = ws[f"A{row}"] ic.value = row - 3 ic.font = ft("000000", bold=True, sz=9) ic.fill = fl("F2F2F2") ic.alignment = al("center") ic.border = bd() for col_letter, _, _, _, vtype in cols[1:]: c = ws[f"{col_letter}{row}"] c.fill = fl(rbg) c.alignment = al("left") c.border = bd() c.font = ft("000000", sz=9) if vtype == "num": c.number_format = "0" elif vtype == "date": c.number_format = "DD/MM/YYYY" # Data Validations def dv_list(f1, sqref): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid", error="Use the dropdown.") dv.sqref = sqref; return dv def dv_whole(mn, mx, sqref, msg=""): dv = DataValidation(type="whole", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=msg or f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def dv_dec(mn, mx, sqref): dv = DataValidation(type="decimal", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error=f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def cr(col): return f"{col}4:{col}203" for c in ["L","N","O","R","S","T","U","V","X","Y","AB","AD","AE","AG","AH","AJ","AM"]: ws.add_data_validation(dv_list('"1,0"', cr(c))) ws.add_data_validation(dv_whole(10, 15, cr("D"), "Age must be 10-15")) ws.add_data_validation(dv_whole(1, 12, cr("E"), "Grade 1-12")) ws.add_data_validation(dv_list('"1,2"', cr("G"))) ws.add_data_validation(dv_list('"1,2"', cr("H"))) ws.add_data_validation(dv_list('"1,2,3,4,5"', cr("I"))) ws.add_data_validation(dv_list('"1,2,3,4,5"', cr("J"))) ws.add_data_validation(dv_list('"1,2,3,4"', cr("K"))) ws.add_data_validation(dv_dec(0, 30, cr("Q"))) ws.add_data_validation(dv_dec(0, 24, cr("W"))) ws.add_data_validation(dv_dec(0, 24, cr("AF"))) ws.add_data_validation(dv_dec(4, 20, cr("AA"))) ws.add_data_validation(dv_dec(0, 20, cr("AI"))) ws.add_data_validation(dv_dec(8, 20, cr("AK"))) ws.sheet_properties.tabColor = "1F4E79" # ═══════════════════════════════════════════════ # SHEET 2 – SPSS CODEBOOK # ═══════════════════════════════════════════════ cb = wb.create_sheet("SPSS Codebook") cb.sheet_view.showGridLines = False cb.sheet_properties.tabColor = "2E75B6" for col, w in [("A",5),("B",26),("C",32),("D",12),("E",10),("F",45),("G",22)]: cb.column_dimensions[col].width = w cb.merge_cells("A1:G1") c = cb["A1"] c.value = "SPSS VARIABLE CODEBOOK - Precocious Puberty Study" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") cb.row_dimensions[1].height = 26 for i, h in enumerate(["#","SPSS Variable Name","Question Label","Type","Missing Code","Value Labels","Notes"],1): c = cb.cell(row=2, column=i, value=h) c.font = ft(C_HDR_FG, bold=True, sz=10) c.fill = fl(C_HDR_BG) c.alignment = al("center", wrap=True) c.border = bd() cb.row_dimensions[2].height = 20 codebook = [ ("ADMIN", 1,"ID","Participant serial number","Numeric","---","1,2,3 (auto-filled)",""), ("ADMIN", 2,"Date_Entry","Date of data entry","Date","---","DD/MM/YYYY",""), ("SEC1", 3,"Child_Name","P1. Child name (optional)","String","---","Text","Leave blank = anonymous"), ("SEC1", 4,"Age","P2. Age of child (years)","Numeric","99","Integer 10 to 15",""), ("SEC1", 5,"Grade","P3. Grade or Standard","Numeric","99","Integer 1-12",""), ("SEC1", 6,"School_Name","P4. School name","String","---","Text",""), ("SEC1", 7,"Residence","P5. Residence type","Numeric","9","1=Urban 2=Semiurban",""), ("SEC1", 8,"Family_Type","P6. Family type","Numeric","9","1=Nuclear 2=Joint",""), ("SEC1", 9,"Father_Edu","P7a. Father education level","Numeric","9","1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate",""), ("SEC1", 10,"Mother_Edu","P7b. Mother education level","Numeric","9","1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate",""), ("SEC1", 11,"Income","P8. Monthly family income (INR)","Numeric","9","1=<10,000 2=10,000-30,000 3=30,000-50,000 4=>50,000",""), ("SEC2", 12,"Chronic_Illness","P9. Chronic illness present","Numeric","9","1=Yes 0=No",""), ("SEC2", 13,"Illness_Details","P9. Illness details (specify)","String","---","Text","If Chronic_Illness=1"), ("SEC2", 14,"Fam_Hx_Puberty","P10. Family history of early puberty","Numeric","9","1=Yes 0=No","Mother or sisters"), ("SEC2", 15,"Medications","P11. Medications or hormones used","Numeric","9","1=Yes 0=No",""), ("SEC2", 16,"Medication_Details","P11. Medication details","String","---","Text","If Medications=1"), ("SEC2", 17,"Age_Menarche","P12. Age at menarche (years)","Numeric","99","Continuous (years)","99=Not yet occurred"), ("SEC3", 18,"Diet_Homecooked","P13a. Home-cooked meals","Numeric","9","1=Yes 0=No","Multi-select item"), ("SEC3", 19,"Diet_FastFood","P13b. Fast food more than 3x per week","Numeric","9","1=Yes 0=No",""), ("SEC3", 20,"Diet_Processed","P13c. Processed foods","Numeric","9","1=Yes 0=No",""), ("SEC3", 21,"Diet_HighProtein","P13d. High protein foods","Numeric","9","1=Yes 0=No",""), ("SEC3", 22,"Diet_TraditionalKerala","P13e. Traditional Kerala diet","Numeric","9","1=Yes 0=No",""), ("SEC3", 23,"Hrs_Outdoors","P14. Hours outdoors per day","Numeric","99","Continuous (hours)",""), ("SEC3", 24,"Screen_Time_P","P15. Screen time >2 hrs/day (parent)","Numeric","9","1=Yes 0=No",""), ("SEC3", 25,"Pesticide_Exp","P16. Pesticide or chemical exposure","Numeric","9","1=Yes 0=No",""), ("SEC3", 26,"Pesticide_Details","P16. Pesticide details","String","---","Text","If Pesticide_Exp=1"), ("SEC4", 27,"Age_Puberty_Signs","P17. Age puberty signs noticed (years)","Numeric","99","Continuous (years)",""), ("SEC4", 28,"Doctor_Confirmed","P18. Doctor confirmed early puberty","Numeric","9","1=Yes 0=No",""), ("SEC4", 29,"Doctor_Details","P18. Doctor confirmation details","String","---","Text","If Doctor_Confirmed=1"), ("SEC5", 30,"Parent_Consent","P19. Parent or guardian consent","Numeric","9","1=Yes 0=No","Exclude if 0"), ("STUQ", 31,"S_Outdoor_Play","S1. Student plays outdoors daily","Numeric","9","1=Yes 0=No",""), ("STUQ", 32,"S_Outdoor_Hrs","S1. Student outdoor hours per day","Numeric","99","Continuous (hours)","If S_Outdoor_Play=1"), ("STUQ", 33,"S_Screen_Time","S2. Student screen time >2 hrs/day","Numeric","9","1=Yes 0=No",""), ("STUQ", 34,"S_Body_Changes","S3. Student noticed body changes","Numeric","9","1=Yes 0=No",""), ("STUQ", 35,"S_Age_Body_Changes","S3. Age at body changes (years)","Numeric","99","Continuous; 99=Don't know","If S_Body_Changes=1"), ("STUQ", 36,"S_Menarche","S4. Student had first menstrual period","Numeric","9","1=Yes 0=No",""), ("STUQ", 37,"S_Age_Menarche","S4. Age at first period (years)","Numeric","99","Continuous (years)","If S_Menarche=1"), ("STUQ", 38,"S_Health_Notes","S5. Additional health notes","String","---","Free text",""), ("STUQ", 39,"S_Assent","S6. Student assent","Numeric","9","1=Yes 0=No","Exclude if 0"), ] sec_labels = { "ADMIN": "ADMIN", "SEC1": "PARENT QUESTIONNAIRE - Section 1: Demographic Information", "SEC2": "PARENT QUESTIONNAIRE - Section 2: Medical & Family History", "SEC3": "PARENT QUESTIONNAIRE - Section 3: Lifestyle & Environmental Factors", "SEC4": "PARENT QUESTIONNAIRE - Section 4: Physical Development", "SEC5": "PARENT QUESTIONNAIRE - Section 5: Consent", "STUQ": "STUDENT QUESTIONNAIRE", } row = 3 prev_sec = None for entry in codebook: sec, num, varname, label, vtype, miss, vals, notes = entry if sec != prev_sec: cb.merge_cells(f"A{row}:G{row}") sc = cb[f"A{row}"] sc.value = sec_labels[sec] sc.font = ft(C_SEC_FG, bold=True, sz=10) sc.fill = fl(C_SEC_BG) sc.alignment = al("left") sc.border = bd() cb.row_dimensions[row].height = 18 row += 1 prev_sec = sec rbg = C_ALT if row % 2 == 0 else "FFFFFF" for ci, val in enumerate([num, varname, label, vtype, miss, vals, notes], 1): c = cb.cell(row=row, column=ci, value=val) c.font = ft("000000", sz=9) c.fill = fl(rbg) c.border = bd() c.alignment = al("center" if ci == 1 else "left", wrap=True) cb.row_dimensions[row].height = 28 row += 1 # ═══════════════════════════════════════════════ # SHEET 3 – VALUE CODES REFERENCE # ═══════════════════════════════════════════════ vr = wb.create_sheet("Value Codes") vr.sheet_view.showGridLines = False vr.sheet_properties.tabColor = "7F6000" vr.column_dimensions["A"].width = 38 vr.column_dimensions["B"].width = 55 vr.merge_cells("A1:B1") c = vr["A1"] c.value = "QUICK REFERENCE - VALUE CODES FOR DATA ENTRY" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") vr.row_dimensions[1].height = 26 ref_data = [ ("YES / NO FIELDS (all binary questions)", "1 = Yes\n0 = No\n9 = Missing / Not answered"), ("MISSING VALUES - numeric/age fields", "99 = Unknown / Not applicable / Not yet occurred"), ("P5 - RESIDENCE", "1 = Urban\n2 = Semiurban"), ("P6 - FAMILY TYPE", "1 = Nuclear\n2 = Joint"), ("P7 - EDUCATION LEVEL (Father & Mother)", "1 = Illiterate\n2 = Primary\n3 = Secondary\n4 = Graduate\n5 = Post-graduate"), ("P8 - MONTHLY INCOME (INR)", "1 = Less than Rs.10,000\n2 = Rs.10,000 to Rs.30,000\n3 = Rs.30,000 to Rs.50,000\n4 = More than Rs.50,000"), ("P12 - AGE AT MENARCHE", "Enter actual age in years (e.g. 12)\n99 = Menarche not yet occurred"), ("P13 - DIET (each item is a separate column)", "1 = Parent ticked or selected this item\n0 = Not selected"), ("P14 & S1 - OUTDOOR HOURS PER DAY", "Enter number of hours (e.g. 2 or 2.5)\n99 = Not reported"), ("P17 - AGE PUBERTY SIGNS NOTICED", "Enter age in years\n99 = Not noticed / Unknown"), ("S3 - AGE AT BODY CHANGES", "Enter age in years\n99 = Does not know\nLeave blank if S_Body_Changes = 0"), ("S4 - AGE AT FIRST MENSTRUAL PERIOD", "Enter age in years\n99 = Not applicable\nLeave blank if S_Menarche = 0"), ("CONSENT & ASSENT", "1 = Consent/Assent given\n0 = Refused\nIMPORTANT: Exclude participant from SPSS analysis if Parent_Consent = 0 or S_Assent = 0"), ] r = 3 for label, codes in ref_data: lc = vr.cell(row=r, column=1, value=label) lc.font = ft(C_CODE_FG, bold=True, sz=9) lc.fill = fl(C_CODE_BG) lc.border = bd() lc.alignment = al("left") cc = vr.cell(row=r, column=2, value=codes) cc.font = ft("000000", sz=9) cc.fill = fl(C_CODE_BG) cc.border = bd() cc.alignment = al("left", wrap=True) lines = codes.count("\n") + 1 vr.row_dimensions[r].height = max(18, lines * 14 + 6) r += 1 vr.row_dimensions[r].height = 6 r += 1 # ═══════════════════════════════════════════════ # SHEET 4 – SPSS SYNTAX # ═══════════════════════════════════════════════ sx = wb.create_sheet("SPSS Syntax") sx.sheet_view.showGridLines = False sx.sheet_properties.tabColor = "375623" sx.column_dimensions["A"].width = 100 sx.merge_cells("A1:A1") c = sx["A1"] c.value = "SPSS IMPORT SYNTAX - Copy into SPSS Syntax Editor and click Run > All" c.font = ft(C_TITLE_FG, bold=True, sz=12) c.fill = fl(C_TITLE_BG) c.alignment = al("center") sx.row_dimensions[1].height = 24 syntax = [ "", "* =============================================================", "* STEP 1 - Import Excel into SPSS", "* =============================================================", "* SPSS Menu: File > Import Data > Excel", "* Sheet : Data Entry", "* Header row: 3 (tick 'Read variable names from first row')", "* Data starts: row 4", "", "* =============================================================", "* STEP 2 - Variable Labels", "* =============================================================", "VARIABLE LABELS", " ID 'Participant serial number'", " Date_Entry 'Date of data entry'", " Child_Name 'P1. Child name (optional)'", " Age 'P2. Age of child (years)'", " Grade 'P3. Grade or Standard'", " School_Name 'P4. School name'", " Residence 'P5. Residence type'", " Family_Type 'P6. Family type'", " Father_Edu 'P7a. Father education level'", " Mother_Edu 'P7b. Mother education level'", " Income 'P8. Monthly family income INR'", " Chronic_Illness 'P9. Chronic illness present'", " Illness_Details 'P9. Illness details'", " Fam_Hx_Puberty 'P10. Family history of early puberty'", " Medications 'P11. Medications or hormones used'", " Medication_Details 'P11. Medication details'", " Age_Menarche 'P12. Age at menarche (years)'", " Diet_Homecooked 'P13a. Diet: Home-cooked meals'", " Diet_FastFood 'P13b. Diet: Fast food more than 3 times per week'", " Diet_Processed 'P13c. Diet: Processed foods'", " Diet_HighProtein 'P13d. Diet: High protein foods'", " Diet_TraditionalKerala 'P13e. Diet: Traditional Kerala diet'", " Hrs_Outdoors 'P14. Hours outdoors per day'", " Screen_Time_P 'P15. Screen time more than 2 hours per day (parent report)'", " Pesticide_Exp 'P16. Pesticide or chemical exposure'", " Pesticide_Details 'P16. Pesticide details'", " Age_Puberty_Signs 'P17. Age puberty signs first noticed (years)'", " Doctor_Confirmed 'P18. Doctor confirmed early puberty'", " Doctor_Details 'P18. Doctor confirmation details'", " Parent_Consent 'P19. Parent or guardian consent'", " S_Outdoor_Play 'S1. Student plays outdoors daily'", " S_Outdoor_Hrs 'S1. Student outdoor hours per day'", " S_Screen_Time 'S2. Student screen time more than 2 hours per day'", " S_Body_Changes 'S3. Student noticed body changes'", " S_Age_Body_Changes 'S3. Student age at body changes'", " S_Menarche 'S4. Student had first menstrual period'", " S_Age_Menarche 'S4. Student age at first period'", " S_Health_Notes 'S5. Student additional health notes'", " S_Assent 'S6. Student assent'", ".", "", "* =============================================================", "* STEP 3 - Value Labels", "* =============================================================", "VALUE LABELS", " Residence 1 'Urban' 2 'Semiurban' /", " Family_Type 1 'Nuclear' 2 'Joint' /", " Father_Edu Mother_Edu", " 1 'Illiterate' 2 'Primary' 3 'Secondary' 4 'Graduate' 5 'Post-graduate' /", " Income", " 1 'Less than 10000' 2 '10000 to 30000' 3 '30000 to 50000' 4 'More than 50000' /", " Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp", " Doctor_Confirmed Parent_Consent", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent", " 1 'Yes' 0 'No' /", ".", "", "* =============================================================", "* STEP 4 - Missing Values", "* =============================================================", "MISSING VALUES", " Age Grade Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche (99)", " Residence Family_Type Father_Edu Mother_Edu Income", " Chronic_Illness Fam_Hx_Puberty Medications", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " Screen_Time_P Pesticide_Exp Doctor_Confirmed Parent_Consent", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent (9)", ".", "", "* =============================================================", "* STEP 5 - Save as SPSS .sav file", "* =============================================================", "SAVE OUTFILE='/your/path/here/precocious_puberty_data.sav'", " /COMPRESSED.", "", "* End of syntax.", ] for i, line in enumerate(syntax, 2): c = sx.cell(row=i, column=1, value=line) if line.startswith("*"): c.font = Font(name="Courier New", size=9, color="375623") elif any(line.startswith(k) for k in ["VARIABLE","VALUE","MISSING","SAVE"]): c.font = Font(name="Courier New", size=9, bold=True, color="1F4E79") else: c.font = Font(name="Courier New", size=9, color="000000") c.alignment = Alignment(horizontal="left", vertical="top") sx.row_dimensions[i].height = 13 # ═══════════════════════════════════════════════ # SHEET 5 – INSTRUCTIONS # ═══════════════════════════════════════════════ ins = wb.create_sheet("Instructions") ins.sheet_view.showGridLines = False ins.sheet_properties.tabColor = "C55A11" ins.column_dimensions["A"].width = 20 ins.column_dimensions["B"].width = 75 ins.merge_cells("A1:B1") c = ins["A1"] c.value = "HOW TO USE THIS DATABASE" c.font = ft(C_TITLE_FG, bold=True, sz=13) c.fill = fl(C_TITLE_BG) c.alignment = al("center") ins.row_dimensions[1].height = 26 guide = [ ("SECTION", "WHAT EACH SHEET DOES"), ("Data Entry", "Main entry sheet. ONE ROW = ONE PARTICIPANT (girl + her parent). Pre-filled 200 rows."), ("SPSS Codebook", "Full list of all 39 variables with SPSS names, types, value labels, and missing codes."), ("Value Codes", "Quick-look reference. Keep this open while entering data."), ("SPSS Syntax", "Ready-to-use SPSS syntax. Paste into Syntax Editor after importing to label all variables automatically."), ("Instructions", "This sheet."), ("SECTION", "DATA ENTRY RULES"), ("One row per girl", "Enter both the Parent questionnaire AND the Student questionnaire answers in the SAME row."), ("ID column", "Pre-filled 1-200. Do NOT change or delete."), ("Date column", "Enter date of data collection in DD/MM/YYYY format."), ("Yes / No fields", "Enter 1 = Yes, 0 = No. Enter 9 if the question was skipped or not answered."), ("Age fields", "Enter actual age in years. Enter 99 if unknown or not applicable."), ("Diet (P13)", "Each diet type has its own column. Enter 1 if the parent ticked it, 0 if not."), ("Text fields", "Name, details, and notes - enter free text. Leave blank if not provided."), ("Dropdowns", "Most numeric fields have dropdown validation. Use the dropdown to avoid data errors."), ("SECTION", "EXPORTING TO SPSS"), ("Step 1", "Save this file as .xlsx"), ("Step 2", "Open SPSS > File > Import Data > Excel"), ("Step 3", "Select the 'Data Entry' sheet; set starting row to 3; tick 'Read variable names from first row'"), ("Step 4", "Go to 'SPSS Syntax' sheet, copy ALL the text, paste into SPSS Syntax Editor, click Run > All"), ("Step 5", "All variables will have labels and missing value codes. Save as .sav file."), ("SECTION", "MISSING VALUE CODES"), ("9", "Use for YES/NO and all categorical variables when the answer is missing or not given."), ("99", "Use for age, hours, and all continuous variables when value is unknown or not applicable."), ("Blank", "Text/string fields: simply leave empty if no information was provided."), ("SECTION", "IMPORTANT NOTES"), ("Consent", "If Parent_Consent = 0, EXCLUDE that participant from SPSS analysis."), ("Assent", "If S_Assent = 0, EXCLUDE that participant from SPSS analysis."), ("Backup", "Save a dated backup copy after every data entry session."), ("200 rows", "Add more rows below row 203 if your sample size exceeds 200."), ] r = 3 for item in guide: if item[0] == "SECTION": ins.merge_cells(f"A{r}:B{r}") sc = ins[f"A{r}"] sc.value = item[1] sc.font = ft(C_SEC_FG, bold=True, sz=10) sc.fill = fl(C_SEC_BG) sc.alignment = al("left") sc.border = bd() ins.row_dimensions[r].height = 20 else: label, text = item lc = ins.cell(row=r, column=1, value=label) lc.font = ft("1F4E79", bold=True, sz=9) lc.fill = fl(C_ALT if r%2==0 else "FFFFFF") lc.alignment = al("center") lc.border = bd() tc = ins.cell(row=r, column=2, value=text) tc.font = ft("000000", sz=9) tc.fill = fl(C_ALT if r%2==0 else "FFFFFF") tc.alignment = al("left", wrap=True) tc.border = bd() ins.row_dimensions[r].height = 18 r += 1 # ── Save to /tmp then copy ──────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", os.path.getsize(dst), "bytes") PYEOF

python3 -c " import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS.xlsx') print('Sheets:', wb.sheetnames) ws = wb['Data Entry'] print('Max row:', ws.max_row, '| Max col:', ws.max_column) # Print header row for cell in ws[3]: if cell.value: print(cell.column_letter, repr(cell.value)[:40]) "

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.chart import BarChart, PieChart, Reference from openpyxl.chart.series import DataPoint # ── Load existing workbook ──────────────────────────────────────── wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS.xlsx') # ── Colours ─────────────────────────────────────────────────────── C_TITLE_BG="1F4E79"; C_TITLE_FG="FFFFFF" C_SEC_BG="2E75B6"; C_SEC_FG="FFFFFF" C_HDR_BG="BDD7EE"; C_HDR_FG="1F4E79" C_ALT="EBF3FB"; C_OK="E2EFDA"; C_WARN="FCE4D6" C_CODE_BG="FFF2CC"; C_CODE_FG="7F6000" def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, italic=False, name="Arial"): return Font(name=name, size=sz, bold=bold, italic=italic, color=h) def bd(): s=Side(style="thin"); return Border(left=s,right=s,top=s,bottom=s) def al(h="left",wrap=True): return Alignment(horizontal=h,vertical="center",wrap_text=wrap) # ═══════════════════════════════════════════════════════════ # PART 1 – EXTEND DATA ENTRY TO 500 ROWS # ═══════════════════════════════════════════════════════════ ws = wb["Data Entry"] # Column metadata for formatting (same as original) col_types = { "A":"id","B":"date", "C":"txt","D":"num","E":"num","F":"txt","G":"num","H":"num","I":"num","J":"num","K":"num", "L":"num","M":"txt","N":"num","O":"num","P":"txt","Q":"num", "R":"num","S":"num","T":"num","U":"num","V":"num","W":"num","X":"num","Y":"num","Z":"txt", "AA":"num","AB":"num","AC":"txt","AD":"num", "AE":"num","AF":"num","AG":"num","AH":"num","AI":"num","AJ":"num","AK":"num","AL":"txt","AM":"num" } for row in range(204, 504): rbg = C_ALT if row % 2 == 0 else "FFFFFF" # ID ic = ws[f"A{row}"] ic.value = row - 3 ic.font = ft("000000", bold=True, sz=9) ic.fill = fl("F2F2F2") ic.alignment = al("center") ic.border = bd() # rest of cols for col_letter, vtype in col_types.items(): if col_letter == "A": continue c = ws[f"{col_letter}{row}"] c.fill = fl(rbg) c.alignment = al("left") c.border = bd() c.font = ft("000000", sz=9) if vtype == "num": c.number_format = "0" elif vtype == "date": c.number_format = "DD/MM/YYYY" # Extend data validations to row 503 def dv_list(f1, sqref): dv = DataValidation(type="list",formula1=f1,allow_blank=True, showDropDown=False,showErrorMessage=True, errorTitle="Invalid",error="Use the dropdown.") dv.sqref = sqref; return dv def dv_whole(mn,mx,sqref,msg=""): dv = DataValidation(type="whole",operator="between", formula1=str(mn),formula2=str(mx), allow_blank=True,showErrorMessage=True, errorTitle="Invalid",error=msg or f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def dv_dec(mn,mx,sqref): dv = DataValidation(type="decimal",operator="between", formula1=str(mn),formula2=str(mx), allow_blank=True,showErrorMessage=True, errorTitle="Invalid",error=f"Enter {mn}-{mx}") dv.sqref = sqref; return dv def cr(col): return f"{col}204:{col}503" for c in ["L","N","O","R","S","T","U","V","X","Y","AB","AD","AE","AG","AH","AJ","AM"]: ws.add_data_validation(dv_list('"1,0"', cr(c))) ws.add_data_validation(dv_whole(10,15,cr("D"),"Age must be 10-15")) ws.add_data_validation(dv_whole(1,12,cr("E"),"Grade 1-12")) ws.add_data_validation(dv_list('"1,2"',cr("G"))) ws.add_data_validation(dv_list('"1,2"',cr("H"))) ws.add_data_validation(dv_list('"1,2,3,4,5"',cr("I"))) ws.add_data_validation(dv_list('"1,2,3,4,5"',cr("J"))) ws.add_data_validation(dv_list('"1,2,3,4"',cr("K"))) ws.add_data_validation(dv_dec(0,30,cr("Q"))) ws.add_data_validation(dv_dec(0,24,cr("W"))) ws.add_data_validation(dv_dec(0,24,cr("AF"))) ws.add_data_validation(dv_dec(4,20,cr("AA"))) ws.add_data_validation(dv_dec(0,20,cr("AI"))) ws.add_data_validation(dv_dec(8,20,cr("AK"))) print("Data Entry extended to 500 rows") # ═══════════════════════════════════════════════════════════ # PART 2 – DATA CLEANING CHECKLIST SHEET # ═══════════════════════════════════════════════════════════ dc = wb.create_sheet("Data Cleaning Checklist") dc.sheet_view.showGridLines = False dc.sheet_properties.tabColor = "C55A11" dc.column_dimensions["A"].width = 5 dc.column_dimensions["B"].width = 35 dc.column_dimensions["C"].width = 55 dc.column_dimensions["D"].width = 20 dc.column_dimensions["E"].width = 15 dc.merge_cells("A1:E1") c=dc["A1"]; c.value="DATA CLEANING CHECKLIST - Before Importing to SPSS" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); dc.row_dimensions[1].height=26 for i,h in enumerate(["#","Check Item","How to Check / SPSS Command","Action if Problem Found","Done?"],1): c=dc.cell(row=2,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=10); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() dc.row_dimensions[2].height=20 checks = [ ("SECTION","BEFORE DATA ENTRY","","",""), (1,"Informed consent obtained","Check Parent_Consent column = 1 for all rows","Remove or exclude rows with Consent = 0",""), (2,"Student assent obtained","Check S_Assent column = 1 for all rows","Remove or exclude rows with Assent = 0",""), (3,"Age range correct","All Age values must be 10-15","Delete rows outside range",""), (4,"No duplicate IDs","Each ID appears only once","Remove duplicate rows",""), ("SECTION","COMPLETENESS CHECKS","","",""), (5,"Check for blank mandatory fields","In Excel: use COUNTBLANK() on key columns (D,G,H,I,J,K)","Flag and attempt to retrieve data",""), (6,"Check missing value codes used correctly","All skipped numeric = 9 or 99, not 0 or blank","Replace 0s with correct missing code",""), (7,"Consent column not blank","Column AD has no blank cells","Enter 9 if consent status unknown",""), ("SECTION","RANGE CHECKS","","",""), (8,"Age at menarche plausible","Age_Menarche: values should be 8-16 or 99","Verify with original form",""), (9,"Age puberty signs plausible","Age_Puberty_Signs: values 4-15 or 99","Verify with original form",""), (10,"Hours outdoors plausible","Hrs_Outdoors: 0-16 hours","Values >16 are likely errors",""), (11,"Student menarche age plausible","S_Age_Menarche: 8-16 or 99","Verify with original form",""), ("SECTION","LOGICAL CONSISTENCY CHECKS","","",""), (12,"If menarche = 99 then age blank","If Age_Menarche=99, expect no value for age","Flag inconsistency",""), (13,"If S_Menarche=0 then S_Age_Menarche blank","Student hasn't had period so age should be blank/99","Correct the entry",""), (14,"If S_Body_Changes=0 then S_Age_Body_Changes blank","No changes noticed so age field should be blank/99","Correct the entry",""), (15,"If S_Outdoor_Play=0 then S_Outdoor_Hrs blank","Not playing outside so hours should be blank","Correct the entry",""), (16,"If Chronic_Illness=0 then Illness_Details blank","No illness so details should be empty","Clear the details field",""), (17,"If Medications=0 then Medication_Details blank","No meds so details should be empty","Clear the details field",""), (18,"If Pesticide_Exp=0 then Pesticide_Details blank","No exposure so details should be empty","Clear the details field",""), ("SECTION","SPSS CHECKS AFTER IMPORT","","",""), (19,"Run FREQUENCIES on all categorical vars","FREQUENCIES VARIABLES=Residence Family_Type Father_Edu Mother_Edu Income.","Look for unexpected values",""), (20,"Run DESCRIPTIVES on all numeric vars","DESCRIPTIVES VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs.","Check min/max for outliers",""), (21,"Check missing value counts","MISSING VALUES command applied; check output","Re-examine high-missing variables",""), (22,"Check sample size","FREQUENCIES VARIABLES=ID. / N should match expected","Confirm all data entered",""), ("SECTION","FINAL SIGN-OFF","","",""), (23,"Data entry complete","All forms entered, all 500 rows checked","Sign and date below",""), (24,"Cleaning log saved","Document all changes made during cleaning","Save as separate file",""), (25,"Clean dataset saved as .sav","Final SPSS file saved with all labels","Ready for analysis",""), ] r=3 for item in checks: if item[0]=="SECTION": dc.merge_cells(f"A{r}:E{r}") sc=dc[f"A{r}"]; sc.value=item[1] sc.font=ft(C_SEC_FG,bold=True,sz=10); sc.fill=fl(C_SEC_BG) sc.alignment=al("left"); sc.border=bd() dc.row_dimensions[r].height=18 else: num,check,how,action,done=item rbg=C_ALT if r%2==0 else "FFFFFF" for ci,val in enumerate([num,check,how,action,done],1): c=dc.cell(row=r,column=ci,value=val) c.font=ft("000000",sz=9); c.fill=fl(rbg) c.border=bd(); c.alignment=al("center" if ci in [1,5] else "left",wrap=True) # Done column - dropdown dv=DataValidation(type="list",formula1='"Yes,No,Pending"',allow_blank=True) dv.sqref=f"E{r}"; dc.add_data_validation(dv) dc.row_dimensions[r].height=30 r+=1 print("Data Cleaning sheet done") # ═══════════════════════════════════════════════════════════ # PART 3 – SUMMARY STATISTICS SHEET # ═══════════════════════════════════════════════════════════ ss = wb.create_sheet("Summary Statistics") ss.sheet_view.showGridLines = False ss.sheet_properties.tabColor = "375623" for col,w in [("A",30),("B",14),("C",14),("D",14),("E",14),("F",18)]: ss.column_dimensions[col].width = w ss.merge_cells("A1:F1") c=ss["A1"]; c.value="SUMMARY STATISTICS - Auto-calculated from Data Entry sheet" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); ss.row_dimensions[1].height=26 ss.merge_cells("A2:F2") c=ss["A2"]; c.value="NOTE: These formulas reference the 'Data Entry' sheet. They update automatically as you enter data." c.font=ft("375623",sz=9,italic=True,bold=True); c.fill=fl(C_OK) c.alignment=al("center"); ss.row_dimensions[2].height=16 # Helper to write section header def sec_hdr(sheet, row, text, span="A:F"): cols = span.split(":") sheet.merge_cells(f"{cols[0]}{row}:{cols[1]}{row}") c=sheet[f"{cols[0]}{row}"]; c.value=text c.font=ft(C_SEC_FG,bold=True,sz=10); c.fill=fl(C_SEC_BG) c.alignment=al("left"); c.border=bd() sheet.row_dimensions[row].height=18 def stat_row(sheet, row, label, formula_b="", formula_c="", formula_d="", formula_e="", note=""): rbg = C_ALT if row%2==0 else "FFFFFF" items=[(1,label,False),(2,formula_b,True),(3,formula_c,True),(4,formula_d,True),(5,formula_e,True),(6,note,False)] for ci,val,is_formula in items: c=sheet.cell(row=row,column=ci,value=val) c.font=ft("000000",sz=9); c.fill=fl(rbg) c.border=bd(); c.alignment=al("center" if ci>1 else "left",wrap=True) sheet.row_dimensions[row].height=18 # Header row r=4 for i,h in enumerate(["Variable / Measure","N (valid)","Mean / %","Min","Max","Notes"],1): c=ss.cell(row=r,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=10); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() ss.row_dimensions[r].height=20; r+=1 sec_hdr(ss,r,"SAMPLE SIZE"); r+=1 stat_row(ss,r,"Total participants entered", '=COUNTA(\'Data Entry\'!A4:A503)', "","","","Count of all rows with an ID") r+=1 stat_row(ss,r,"Participants with parental consent (=1)", '=COUNTIF(\'Data Entry\'!AD4:AD503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AD4:AD503,1)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%"', "","","Consent=1") r+=1 stat_row(ss,r,"Participants with student assent (=1)", '=COUNTIF(\'Data Entry\'!AM4:AM503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AM4:AM503,1)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%"', "","","Assent=1") r+=1 sec_hdr(ss,r,"SECTION 1 - DEMOGRAPHICS"); r+=1 stat_row(ss,r,"Age of child (years)", '=COUNTIF(\'Data Entry\'!D4:D503,"<>99")', '=ROUND(AVERAGEIF(\'Data Entry\'!D4:D503,"<99"),1)', '=MINIFS(\'Data Entry\'!D4:D503,\'Data Entry\'!D4:D503,"<99")', '=MAXIFS(\'Data Entry\'!D4:D503,\'Data Entry\'!D4:D503,"<99")', "Excludes missing (99)") r+=1 stat_row(ss,r,"Urban residence (=1)", '=COUNTIF(\'Data Entry\'!G4:G503,1)', '=ROUND(COUNTIF(\'Data Entry\'!G4:G503,1)/COUNTIF(\'Data Entry\'!G4:G503,"<9")*100,1)&"%"', "","","% of valid responses") r+=1 stat_row(ss,r,"Semiurban residence (=2)", '=COUNTIF(\'Data Entry\'!G4:G503,2)', '=ROUND(COUNTIF(\'Data Entry\'!G4:G503,2)/COUNTIF(\'Data Entry\'!G4:G503,"<9")*100,1)&"%"', "","","% of valid responses") r+=1 stat_row(ss,r,"Nuclear family (=1)", '=COUNTIF(\'Data Entry\'!H4:H503,1)', '=ROUND(COUNTIF(\'Data Entry\'!H4:H503,1)/COUNTIF(\'Data Entry\'!H4:H503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Joint family (=2)", '=COUNTIF(\'Data Entry\'!H4:H503,2)', '=ROUND(COUNTIF(\'Data Entry\'!H4:H503,2)/COUNTIF(\'Data Entry\'!H4:H503,"<9")*100,1)&"%"', "","","") r+=1 sec_hdr(ss,r,"SECTION 2 - MEDICAL & FAMILY HISTORY"); r+=1 stat_row(ss,r,"Chronic illness present (=1)", '=COUNTIF(\'Data Entry\'!L4:L503,1)', '=ROUND(COUNTIF(\'Data Entry\'!L4:L503,1)/COUNTIF(\'Data Entry\'!L4:L503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Family history of early puberty (=1)", '=COUNTIF(\'Data Entry\'!N4:N503,1)', '=ROUND(COUNTIF(\'Data Entry\'!N4:N503,1)/COUNTIF(\'Data Entry\'!N4:N503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Medications/hormones used (=1)", '=COUNTIF(\'Data Entry\'!O4:O503,1)', '=ROUND(COUNTIF(\'Data Entry\'!O4:O503,1)/COUNTIF(\'Data Entry\'!O4:O503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Age at menarche (parent report, years)", '=COUNTIF(\'Data Entry\'!Q4:Q503,"<99")', '=ROUND(AVERAGEIF(\'Data Entry\'!Q4:Q503,"<99"),1)', '=MINIFS(\'Data Entry\'!Q4:Q503,\'Data Entry\'!Q4:Q503,"<99")', '=MAXIFS(\'Data Entry\'!Q4:Q503,\'Data Entry\'!Q4:Q503,"<99")', "Excludes 99 (not yet)") r+=1 sec_hdr(ss,r,"SECTION 3 - LIFESTYLE & DIET"); r+=1 for col_l, label in [("R","Diet: Home-cooked meals"),("S","Diet: Fast food >3x/wk"), ("T","Diet: Processed foods"),("U","Diet: High protein"), ("V","Diet: Traditional Kerala")]: stat_row(ss,r,label, f'=COUNTIF(\'Data Entry\'!{col_l}4:{col_l}503,1)', f'=ROUND(COUNTIF(\'Data Entry\'!{col_l}4:{col_l}503,1)/COUNTIF(\'Data Entry\'!{col_l}4:{col_l}503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Hours outdoors per day (parent)", '=COUNTIF(\'Data Entry\'!W4:W503,"<99")', '=ROUND(AVERAGEIF(\'Data Entry\'!W4:W503,"<99"),1)', '=MINIFS(\'Data Entry\'!W4:W503,\'Data Entry\'!W4:W503,"<99")', '=MAXIFS(\'Data Entry\'!W4:W503,\'Data Entry\'!W4:W503,"<99")', "") r+=1 stat_row(ss,r,"Screen time >2hrs/day - parent report (=1)", '=COUNTIF(\'Data Entry\'!X4:X503,1)', '=ROUND(COUNTIF(\'Data Entry\'!X4:X503,1)/COUNTIF(\'Data Entry\'!X4:X503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Pesticide/chemical exposure (=1)", '=COUNTIF(\'Data Entry\'!Y4:Y503,1)', '=ROUND(COUNTIF(\'Data Entry\'!Y4:Y503,1)/COUNTIF(\'Data Entry\'!Y4:Y503,"<9")*100,1)&"%"', "","","") r+=1 sec_hdr(ss,r,"SECTION 4 - PHYSICAL DEVELOPMENT"); r+=1 stat_row(ss,r,"Age puberty signs noticed (years)", '=COUNTIF(\'Data Entry\'!AA4:AA503,"<99")', '=ROUND(AVERAGEIF(\'Data Entry\'!AA4:AA503,"<99"),1)', '=MINIFS(\'Data Entry\'!AA4:AA503,\'Data Entry\'!AA4:AA503,"<99")', '=MAXIFS(\'Data Entry\'!AA4:AA503,\'Data Entry\'!AA4:AA503,"<99")', "") r+=1 stat_row(ss,r,"Doctor confirmed early puberty (=1)", '=COUNTIF(\'Data Entry\'!AB4:AB503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AB4:AB503,1)/COUNTIF(\'Data Entry\'!AB4:AB503,"<9")*100,1)&"%"', "","","") r+=1 sec_hdr(ss,r,"STUDENT QUESTIONNAIRE"); r+=1 stat_row(ss,r,"Student plays outdoors daily (=1)", '=COUNTIF(\'Data Entry\'!AE4:AE503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AE4:AE503,1)/COUNTIF(\'Data Entry\'!AE4:AE503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Student outdoor hours/day", '=COUNTIF(\'Data Entry\'!AF4:AF503,"<99")', '=ROUND(AVERAGEIF(\'Data Entry\'!AF4:AF503,"<99"),1)', '=MINIFS(\'Data Entry\'!AF4:AF503,\'Data Entry\'!AF4:AF503,"<99")', '=MAXIFS(\'Data Entry\'!AF4:AF503,\'Data Entry\'!AF4:AF503,"<99")', "") r+=1 stat_row(ss,r,"Student screen time >2hrs/day (=1)", '=COUNTIF(\'Data Entry\'!AG4:AG503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AG4:AG503,1)/COUNTIF(\'Data Entry\'!AG4:AG503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Student noticed body changes (=1)", '=COUNTIF(\'Data Entry\'!AH4:AH503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AH4:AH503,1)/COUNTIF(\'Data Entry\'!AH4:AH503,"<9")*100,1)&"%"', "","","") r+=1 stat_row(ss,r,"Student had first menstrual period (=1)", '=COUNTIF(\'Data Entry\'!AJ4:AJ503,1)', '=ROUND(COUNTIF(\'Data Entry\'!AJ4:AJ503,1)/COUNTIF(\'Data Entry\'!AJ4:AJ503,"<9")*100,1)&"%"', "","","KEY OUTCOME") r+=1 stat_row(ss,r,"Student age at first period (years)", '=COUNTIF(\'Data Entry\'!AK4:AK503,"<99")', '=ROUND(AVERAGEIF(\'Data Entry\'!AK4:AK503,"<99"),1)', '=MINIFS(\'Data Entry\'!AK4:AK503,\'Data Entry\'!AK4:AK503,"<99")', '=MAXIFS(\'Data Entry\'!AK4:AK503,\'Data Entry\'!AK4:AK503,"<99")', "") r+=1 # Prevalence estimate row r+=1 ss.merge_cells(f"A{r}:F{r}") c=ss[f"A{r}"]; c.value="PREVALENCE ESTIMATE" c.font=ft(C_SEC_FG,bold=True,sz=10); c.fill=fl(C_SEC_BG) c.alignment=al("left"); c.border=bd(); ss.row_dimensions[r].height=18; r+=1 rbg=C_OK for ci,val in enumerate(["Estimated Prevalence of Precocious Puberty", '=COUNTIF(\'Data Entry\'!AB4:AB503,1)', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!AB4:AB503,1)/COUNTIF(\'Data Entry\'!AD4:AD503,1)*100,1)&"%","Enter data first")', "","","Doctor-confirmed cases / total consented"],1): c=ss.cell(row=r,column=ci,value=val) c.font=ft("000000",bold=True if ci==1 else False,sz=9) c.fill=fl(rbg); c.border=bd() c.alignment=al("center" if ci>1 else "left",wrap=True) ss.row_dimensions[r].height=20 print("Summary Statistics sheet done") # ═══════════════════════════════════════════════════════════ # PART 4 – SPSS ANALYSIS GUIDE SHEET # ═══════════════════════════════════════════════════════════ ag = wb.create_sheet("SPSS Analysis Guide") ag.sheet_view.showGridLines = False ag.sheet_properties.tabColor = "7030A0" ag.column_dimensions["A"].width = 5 ag.column_dimensions["B"].width = 30 ag.column_dimensions["C"].width = 40 ag.column_dimensions["D"].width = 55 ag.merge_cells("A1:D1") c=ag["A1"]; c.value="SPSS ANALYSIS GUIDE - Recommended Statistical Tests for Your Study" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); ag.row_dimensions[1].height=26 for i,h in enumerate(["#","Analysis / Objective","SPSS Procedure","SPSS Syntax (copy & run)"],1): c=ag.cell(row=2,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=10); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() ag.row_dimensions[2].height=20 analyses = [ ("SECTION","STEP 1 - DESCRIPTIVE STATISTICS","",""), (1,"Sample characteristics\n(frequencies for categorical vars)", "Analyze > Descriptive Statistics > Frequencies", "FREQUENCIES VARIABLES=Residence Family_Type Father_Edu Mother_Edu Income\n Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp\n Doctor_Confirmed Diet_Homecooked Diet_FastFood Diet_Processed\n Diet_HighProtein Diet_TraditionalKerala\n S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent\n /ORDER=ANALYSIS."), (2,"Continuous variable summaries\n(age, hours, etc.)", "Analyze > Descriptive Statistics > Descriptives", "DESCRIPTIVES VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs\n S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche\n /STATISTICS=MEAN STDDEV MIN MAX."), (3,"Prevalence of precocious puberty\n(primary outcome)", "Analyze > Descriptive Statistics > Frequencies\non Doctor_Confirmed", "FREQUENCIES VARIABLES=Doctor_Confirmed\n /BARCHART FREQ\n /ORDER=ANALYSIS."), ("SECTION","STEP 2 - BIVARIATE ANALYSIS (Association between risk factors and precocious puberty)","",""), (4,"Residence vs Precocious Puberty\n(Chi-square test)", "Analyze > Descriptive Statistics > Crosstabs\n(request Chi-square)", "CROSSTABS\n /TABLES=Residence BY Doctor_Confirmed\n /FORMAT=AVALUE TABLES\n /STATISTICS=CHISQ PHI\n /CELLS=COUNT ROW COLUMN\n /COUNT ROUND CELL."), (5,"Family type vs Precocious Puberty", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Family_Type BY Doctor_Confirmed\n /STATISTICS=CHISQ PHI\n /CELLS=COUNT ROW COLUMN."), (6,"Family history of early puberty\nvs Precocious Puberty", "Crosstabs with Chi-square\n(key risk factor)", "CROSSTABS\n /TABLES=Fam_Hx_Puberty BY Doctor_Confirmed\n /STATISTICS=CHISQ OR\n /CELLS=COUNT ROW COLUMN."), (7,"Education level (mother) vs Precocious Puberty", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Mother_Edu BY Doctor_Confirmed\n /STATISTICS=CHISQ\n /CELLS=COUNT ROW COLUMN."), (8,"Income vs Precocious Puberty", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Income BY Doctor_Confirmed\n /STATISTICS=CHISQ\n /CELLS=COUNT ROW COLUMN."), (9,"Screen time vs Precocious Puberty\n(both parent and student reports)", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Screen_Time_P S_Screen_Time BY Doctor_Confirmed\n /STATISTICS=CHISQ OR\n /CELLS=COUNT ROW COLUMN."), (10,"Pesticide exposure vs Precocious Puberty", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Pesticide_Exp BY Doctor_Confirmed\n /STATISTICS=CHISQ OR\n /CELLS=COUNT ROW COLUMN."), (11,"Diet factors vs Precocious Puberty\n(all 5 diet variables)", "Crosstabs with Chi-square", "CROSSTABS\n /TABLES=Diet_Homecooked Diet_FastFood Diet_Processed\n Diet_HighProtein Diet_TraditionalKerala BY Doctor_Confirmed\n /STATISTICS=CHISQ OR\n /CELLS=COUNT ROW COLUMN."), (12,"Age at menarche: precocious vs normal\n(Independent samples t-test)", "Analyze > Compare Means > Independent\nSamples T-test", "T-TEST GROUPS=Doctor_Confirmed(0 1)\n /VARIABLES=Age_Menarche\n /CRITERIA=CI(.95)."), (13,"Outdoor hours: precocious vs normal\n(Independent samples t-test)", "Analyze > Compare Means > Independent\nSamples T-test", "T-TEST GROUPS=Doctor_Confirmed(0 1)\n /VARIABLES=Hrs_Outdoors S_Outdoor_Hrs\n /CRITERIA=CI(.95)."), ("SECTION","STEP 3 - MULTIVARIABLE ANALYSIS","",""), (14,"Binary logistic regression\n(identify independent risk factors\nfor precocious puberty)", "Analyze > Regression > Binary Logistic\nDependent: Doctor_Confirmed\nCovariates: all significant from Step 2", "LOGISTIC REGRESSION VARIABLES Doctor_Confirmed\n /METHOD=ENTER Residence Family_Type Mother_Edu Income\n Fam_Hx_Puberty Screen_Time_P Pesticide_Exp\n Diet_FastFood Diet_Processed Hrs_Outdoors\n /CLASSPLOT\n /PRINT=GOODFIT CI(95)\n /CRITERIA=PIN(.05) POUT(.10) ITERATE(20) CUT(.5)."), (15,"Calculate Odds Ratios with 95% CI\n(from logistic regression output)", "Automatically produced with syntax above", "* Odds Ratios are in the Exp(B) column of SPSS output.\n* 95% CI = Lower and Upper columns.\n* OR >1 = increased risk, OR <1 = protective factor."), ("SECTION","STEP 4 - CORRELATION ANALYSIS","",""), (16,"Correlation between age of puberty signs\nand menarche age", "Analyze > Correlate > Bivariate\n(Pearson or Spearman)", "CORRELATIONS\n /VARIABLES=Age_Puberty_Signs Age_Menarche S_Age_Menarche\n /PRINT=TWOTAIL NOSIG\n /MISSING=PAIRWISE."), ("SECTION","STEP 5 - RELIABILITY (if using scale items)","",""), (17,"Internal consistency of diet items\n(Cronbach alpha)", "Analyze > Scale > Reliability Analysis", "RELIABILITY\n /VARIABLES=Diet_Homecooked Diet_FastFood Diet_Processed\n Diet_HighProtein Diet_TraditionalKerala\n /MODEL=ALPHA\n /STATISTICS=DESCRIPTIVE SCALE\n /SUMMARY=TOTAL."), ("SECTION","REPORTING CHECKLIST","",""), (18,"Report prevalence as n (%) with 95% CI", "Use FREQUENCIES output", "* 95% CI for proportion = p +/- 1.96*sqrt(p*(1-p)/n)"), (19,"Report chi-square results as:\nX2(df) = value, p = value", "From Crosstabs output", "* Example: X2(1) = 4.52, p = 0.033"), (20,"Report OR as:\nOR = value (95% CI: lower - upper)", "From Logistic Regression output", "* Example: OR = 2.31 (95% CI: 1.12 - 4.76)"), ] r=3 for item in analyses: if item[0]=="SECTION": ag.merge_cells(f"A{r}:D{r}") sc=ag[f"A{r}"]; sc.value=item[1] sc.font=ft(C_SEC_FG,bold=True,sz=10); sc.fill=fl(C_SEC_BG) sc.alignment=al("left"); sc.border=bd() ag.row_dimensions[r].height=18 else: num,obj,proc,syntax_txt=item rbg=C_ALT if r%2==0 else "FFFFFF" for ci,val in enumerate([num,obj,proc,syntax_txt],1): c=ag.cell(row=r,column=ci,value=val) c.font=ft("000000" if ci!=4 else "154360", sz=9, name="Arial" if ci!=4 else "Courier New") c.fill=fl(rbg if ci!=4 else "F0F7FF") c.border=bd(); c.alignment=al("center" if ci==1 else "left",wrap=True) lines=max(str(item[1]).count("\n"),str(item[3]).count("\n"))+2 ag.row_dimensions[r].height=max(25, lines*13) r+=1 print("SPSS Analysis Guide sheet done") # ═══════════════════════════════════════════════════════════ # PART 5 – REPLACE SPSS SYNTAX SHEET WITH FULL VERSION # ═══════════════════════════════════════════════════════════ # Remove old and recreate if "SPSS Syntax" in wb.sheetnames: del wb["SPSS Syntax"] sx = wb.create_sheet("SPSS Syntax") sx.sheet_view.showGridLines = False sx.sheet_properties.tabColor = "375623" sx.column_dimensions["A"].width = 105 sx.merge_cells("A1:A1") c=sx["A1"]; c.value="SPSS COMPLETE SYNTAX - Copy into SPSS Syntax Editor and click Run > All" c.font=ft(C_TITLE_FG,bold=True,sz=12); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); sx.row_dimensions[1].height=24 full_syntax = """ * ================================================================ * PRECOCIOUS PUBERTY STUDY - COMPLETE SPSS SYNTAX * Study: Prevalence of Precocious Puberty in School Girls * Location: Manjeri & Anakkayam * ================================================================ * ---------------------------------------------------------------- * STEP 1: Import Excel (do this via menu first) * ---------------------------------------------------------------- * File > Import Data > Excel * Sheet : Data Entry * Header row: 3 * Data starts: row 4 * Tick: Read variable names from first row * ---------------------------------------------------------------- * STEP 2: Variable Labels * ---------------------------------------------------------------- VARIABLE LABELS ID 'Participant serial number' Date_Entry 'Date of data entry' Child_Name 'P1. Child name (optional)' Age 'P2. Age of child (years)' Grade 'P3. Grade or Standard' School_Name 'P4. School name' Residence 'P5. Residence type' Family_Type 'P6. Family type' Father_Edu 'P7a. Father education level' Mother_Edu 'P7b. Mother education level' Income 'P8. Monthly family income INR' Chronic_Illness 'P9. Chronic illness present' Illness_Details 'P9. Illness details' Fam_Hx_Puberty 'P10. Family history of early puberty' Medications 'P11. Medications or hormones used' Medication_Details 'P11. Medication details' Age_Menarche 'P12. Age at menarche years' Diet_Homecooked 'P13a. Diet home-cooked meals' Diet_FastFood 'P13b. Diet fast food more than 3 times per week' Diet_Processed 'P13c. Diet processed foods' Diet_HighProtein 'P13d. Diet high protein foods' Diet_TraditionalKerala 'P13e. Diet traditional Kerala' Hrs_Outdoors 'P14. Hours outdoors per day' Screen_Time_P 'P15. Screen time more than 2 hours per day parent report' Pesticide_Exp 'P16. Pesticide or chemical exposure' Pesticide_Details 'P16. Pesticide details' Age_Puberty_Signs 'P17. Age puberty signs first noticed years' Doctor_Confirmed 'P18. Doctor confirmed early puberty' Doctor_Details 'P18. Doctor confirmation details' Parent_Consent 'P19. Parent or guardian consent' S_Outdoor_Play 'S1. Student plays outdoors daily' S_Outdoor_Hrs 'S1. Student outdoor hours per day' S_Screen_Time 'S2. Student screen time more than 2 hours per day' S_Body_Changes 'S3. Student noticed body changes' S_Age_Body_Changes 'S3. Student age at body changes' S_Menarche 'S4. Student had first menstrual period' S_Age_Menarche 'S4. Student age at first period' S_Health_Notes 'S5. Student additional health notes' S_Assent 'S6. Student assent' . * ---------------------------------------------------------------- * STEP 3: Value Labels * ---------------------------------------------------------------- VALUE LABELS Residence 1 'Urban' 2 'Semiurban' / Family_Type 1 'Nuclear' 2 'Joint' / Father_Edu Mother_Edu 1 'Illiterate' 2 'Primary' 3 'Secondary' 4 'Graduate' 5 'Post-graduate' / Income 1 'Less than 10000' 2 '10000 to 30000' 3 '30000 to 50000' 4 'More than 50000' / Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp Doctor_Confirmed Parent_Consent Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent 1 'Yes' 0 'No' / . * ---------------------------------------------------------------- * STEP 4: Missing Values * ---------------------------------------------------------------- MISSING VALUES Age Grade Age_Menarche Hrs_Outdoors Age_Puberty_Signs S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche (99) Residence Family_Type Father_Edu Mother_Edu Income Chronic_Illness Fam_Hx_Puberty Medications Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala Screen_Time_P Pesticide_Exp Doctor_Confirmed Parent_Consent S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent (9) . * ---------------------------------------------------------------- * STEP 5: Exclude non-consented participants * ---------------------------------------------------------------- SELECT IF (Parent_Consent = 1 AND S_Assent = 1). EXECUTE. * ---------------------------------------------------------------- * STEP 6: DESCRIPTIVE STATISTICS * ---------------------------------------------------------------- FREQUENCIES VARIABLES=Residence Family_Type Father_Edu Mother_Edu Income Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp Doctor_Confirmed Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche /ORDER=ANALYSIS. DESCRIPTIVES VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche /STATISTICS=MEAN STDDEV MIN MAX. * ---------------------------------------------------------------- * STEP 7: CHI-SQUARE TESTS (Risk factors vs Precocious Puberty) * ---------------------------------------------------------------- CROSSTABS /TABLES=Residence Family_Type Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp S_Screen_Time Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala BY Doctor_Confirmed /FORMAT=AVALUE TABLES /STATISTICS=CHISQ OR /CELLS=COUNT ROW COLUMN EXPECTED /COUNT ROUND CELL. CROSSTABS /TABLES=Income Mother_Edu Father_Edu BY Doctor_Confirmed /STATISTICS=CHISQ /CELLS=COUNT ROW COLUMN /COUNT ROUND CELL. * ---------------------------------------------------------------- * STEP 8: INDEPENDENT SAMPLES T-TEST * ---------------------------------------------------------------- T-TEST GROUPS=Doctor_Confirmed(0 1) /MISSING=ANALYSIS /VARIABLES=Age_Menarche Hrs_Outdoors S_Outdoor_Hrs S_Age_Menarche /CRITERIA=CI(.95). * ---------------------------------------------------------------- * STEP 9: BINARY LOGISTIC REGRESSION * (Enter variables significant at p<0.25 from Step 7) * ---------------------------------------------------------------- LOGISTIC REGRESSION VARIABLES Doctor_Confirmed /METHOD=ENTER Residence Family_Type Mother_Edu Income Fam_Hx_Puberty Screen_Time_P Pesticide_Exp Diet_FastFood Diet_Processed Hrs_Outdoors /CLASSPLOT /PRINT=GOODFIT ITER(1) CI(95) /CRITERIA=PIN(.05) POUT(.10) ITERATE(20) CUT(.5). * ---------------------------------------------------------------- * STEP 10: CORRELATION * ---------------------------------------------------------------- CORRELATIONS /VARIABLES=Age_Puberty_Signs Age_Menarche S_Age_Menarche /PRINT=TWOTAIL NOSIG /MISSING=PAIRWISE. * ---------------------------------------------------------------- * STEP 11: SAVE CLEAN DATASET * ---------------------------------------------------------------- SAVE OUTFILE='/your/path/here/precocious_puberty_CLEAN.sav' /COMPRESSED. * End of syntax. """ for i, line in enumerate(full_syntax.split("\n"), 2): c = sx.cell(row=i, column=1, value=line) if line.strip().startswith("*"): c.font = Font(name="Courier New", size=9, color="375623") elif any(line.strip().startswith(k) for k in ["VARIABLE","VALUE","MISSING","SELECT","FREQ","DESC","CROSS", "T-TEST","LOGISTIC","CORR","SAVE"]): c.font = Font(name="Courier New", size=9, bold=True, color="1F4E79") else: c.font = Font(name="Courier New", size=9, color="000000") c.alignment = Alignment(horizontal="left", vertical="top") sx.row_dimensions[i].height = 13 print("SPSS Syntax sheet done") # ── Reorder sheets logically ──────────────────────────────────── desired_order = ["Instructions","Data Entry","Data Cleaning Checklist", "Summary Statistics","SPSS Codebook","Value Codes", "SPSS Syntax","SPSS Analysis Guide"] for i, name in enumerate(desired_order): if name in wb.sheetnames: wb.move_sheet(name, offset=wb.sheetnames.index(name)-i) # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v2.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v2.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst}") print(f"Size: {os.path.getsize(dst):,} bytes") print("Sheets:", wb.sheetnames) PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v2.xlsx') ws = wb["Data Entry"] # ── Styles helpers ─────────────────────────────────────────────── def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, italic=False): return Font(name="Arial", size=sz, bold=bold, italic=italic, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) def al(h="left", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) C_HDR_BG = "BDD7EE"; C_HDR_FG = "1F4E79" C_ALT = "EBF3FB" # ── School names ───────────────────────────────────────────────── SCHOOLS = [ "1 - Benchmark International School, Manjeri", "2 - GHSS Girls Manjeri", "3 - GHSS Irumbuzhi", ] DV_FORMULA = '"' + ",".join(SCHOOLS) + '"' # ── Update column F header ──────────────────────────────────────── hdr = ws["F3"] hdr.value = "P4. School Name\n(Select from list)" hdr.font = ft(C_HDR_FG, bold=True, sz=8) hdr.fill = fl(C_HDR_BG) hdr.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) hdr.border = bd() ws.column_dimensions["F"].width = 38 # wider to show full school name # ── Remove any existing DV on column F ─────────────────────────── to_remove = [dv for dv in ws.data_validations.dataValidation if "F4" in str(dv.sqref) or ":F" in str(dv.sqref)] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) # ── Add new dropdown DV for F4:F503 ────────────────────────────── dv = DataValidation( type="list", formula1=DV_FORMULA, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid School", error="Please select a school from the dropdown list." ) dv.sqref = "F4:F503" ws.add_data_validation(dv) # ── Reformat existing data cells in column F ───────────────────── for row in range(4, 504): c = ws[f"F{row}"] rbg = C_ALT if row % 2 == 0 else "FFFFFF" c.fill = fl(rbg) c.alignment = al("left", wrap=False) c.border = bd() c.font = ft("000000", sz=9) # ── Also update SPSS Codebook row for School_Name ──────────────── cb = wb["SPSS Codebook"] for row in cb.iter_rows(): for cell in row: if cell.value == "School_Name": # update value labels cell (column F = index 6) row_num = cell.row cb.cell(row=row_num, column=6).value = ( "1=Benchmark International School Manjeri\n" "2=GHSS Girls Manjeri\n" "3=GHSS Irumbuzhi" ) cb.cell(row=row_num, column=4).value = "String/Coded" cb.cell(row=row_num, column=7).value = "Select from dropdown; code = first digit" cb.row_dimensions[row_num].height = 42 # ── Update Value Codes sheet ────────────────────────────────────── vr = wb["Value Codes"] # find last used row and append school codes section max_r = vr.max_row + 2 vr.cell(row=max_r, column=1).value = "P4 - SCHOOL NAME (select from dropdown)" vr.cell(row=max_r, column=1).font = ft("7F6000", bold=True, sz=9) vr.cell(row=max_r, column=1).fill = fl("FFF2CC") vr.cell(row=max_r, column=1).border = bd() vr.cell(row=max_r, column=1).alignment = al("left") vr.cell(row=max_r, column=2).value = ( "1 = Benchmark International School, Manjeri\n" "2 = GHSS Girls Manjeri\n" "3 = GHSS Irumbuzhi" ) vr.cell(row=max_r, column=2).font = ft("000000", sz=9) vr.cell(row=max_r, column=2).fill = fl("FFF2CC") vr.cell(row=max_r, column=2).border = bd() vr.cell(row=max_r, column=2).alignment = al("left", wrap=True) vr.row_dimensions[max_r].height = 52 # ── Update SPSS Syntax to add value labels for school ──────────── # Find the VALUE LABELS section and note about School_Name sx = wb["SPSS Syntax"] for row in sx.iter_rows(): for cell in row: if cell.value and "School_Name" in str(cell.value): cell.value = " School_Name 1 'Benchmark International School Manjeri' 2 'GHSS Girls Manjeri' 3 'GHSS Irumbuzhi' /" # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v3.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v3.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v3.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, italic=False): return Font(name="Arial", size=sz, bold=bold, italic=italic, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) C_HDR_BG = "BDD7EE"; C_HDR_FG = "1F4E79" C_AUTO = "E2EFDA" # green tint = auto-filled cell # ── Update column G header to show it is auto-filled ───────────── hdr = ws["G3"] hdr.value = "P5. Residence\n(Auto from School)\n1=Urban 2=Semiurban" hdr.font = ft(C_HDR_FG, bold=True, sz=8) hdr.fill = fl("C6EFCE") # green header to indicate auto hdr.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) hdr.border = bd() # ── Remove any existing data validation on column G ─────────────── to_remove = [dv for dv in ws.data_validations.dataValidation if "G4" in str(dv.sqref) or ":G" in str(dv.sqref)] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) # ── Write formula for G4:G503 ───────────────────────────────────── # School names stored in F column: # "1 - Benchmark International School, Manjeri" -> 1 (Urban) # "2 - GHSS Girls Manjeri" -> 1 (Urban) # "3 - GHSS Irumbuzhi" -> 2 (Semiurban) # Formula: if F is blank -> blank; if contains "Irumbuzhi" -> 2; else -> 1 formula = '=IF(F{r}="","",IF(ISNUMBER(SEARCH("Irumbuzhi",F{r})),2,1))' for row in range(4, 504): c = ws[f"G{row}"] c.value = formula.replace("{r}", str(row)) c.font = ft("375623", bold=True, sz=9) # dark green = formula c.fill = fl(C_AUTO) c.alignment = Alignment(horizontal="center", vertical="center") c.border = bd() c.number_format = "0" c.protection = openpyxl.styles.Protection(locked=True) # prevent accidental editing # ── Add a note row below the title bar ─────────────────────────── # Update row 2 subtitle to mention auto-fill ws["A2"].value = ( "Study: Prevalence of Precocious Puberty in School Girls | " "Location: Manjeri & Anakkayam | Age: 10-15 yrs | " "GREEN columns = auto-filled (do not edit manually)" ) # ── Update Value Codes sheet ────────────────────────────────────── vr = wb["Value Codes"] # Find the school row and update it for row in vr.iter_rows(): for cell in row: if cell.value and "P4 - SCHOOL NAME" in str(cell.value): r = cell.row vr.cell(row=r, column=2).value = ( "1 = Benchmark International School, Manjeri --> Residence = 1 (Urban)\n" "2 = GHSS Girls Manjeri --> Residence = 1 (Urban)\n" "3 = GHSS Irumbuzhi --> Residence = 2 (Semiurban / Rural)" ) vr.row_dimensions[r].height = 56 # Add a new row explaining auto-fill max_r = vr.max_row + 2 vr.cell(row=max_r, column=1).value = "P5 - RESIDENCE (AUTO-FILLED)" vr.cell(row=max_r, column=1).font = Font(name="Arial", size=9, bold=True, color="375623") vr.cell(row=max_r, column=1).fill = fl("E2EFDA") vr.cell(row=max_r, column=1).border = bd() vr.cell(row=max_r, column=1).alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) vr.cell(row=max_r, column=2).value = ( "This column fills AUTOMATICALLY when you select a school in P4.\n" "DO NOT type in this column manually.\n" "1 = Urban (Benchmark + GHSS Girls Manjeri)\n" "2 = Semiurban/Rural (GHSS Irumbuzhi)" ) vr.cell(row=max_r, column=2).font = Font(name="Arial", size=9, color="000000") vr.cell(row=max_r, column=2).fill = fl("E2EFDA") vr.cell(row=max_r, column=2).border = bd() vr.cell(row=max_r, column=2).alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) vr.row_dimensions[max_r].height = 60 # ── Update SPSS Codebook note for Residence ────────────────────── cb = wb["SPSS Codebook"] for row in cb.iter_rows(): for cell in row: if cell.value == "Residence": rn = cell.row cb.cell(row=rn, column=7).value = ( "Auto-filled from School: " "Benchmark+GHSS Manjeri=Urban(1); " "GHSS Irumbuzhi=Semiurban(2)" ) # ── Update Data Cleaning Checklist note for residence ───────────── dc = wb["Data Cleaning Checklist"] for row in dc.iter_rows(): for cell in row: if cell.value and "Residence" in str(cell.value) and "Urban" in str(cell.value): cell.value = "Residence auto-fills from School column - verify formula is intact (green cells)" # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v4.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v4.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v4.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, italic=False): return Font(name="Arial", size=sz, bold=bold, italic=italic, color=h) def bd(): s = Side(style="thin") return Border(left=s, right=s, top=s, bottom=s) def al(h="center", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) # ═══════════════════════════════════════════════════════════ # COLOUR SCHEME BY DATA TYPE # ═══════════════════════════════════════════════════════════ # Each tuple: (header_bg, header_fg, even_row_bg, odd_row_bg) COLOURS = { "admin": ("2C3E50", "FFFFFF", "D5D8DC", "EBF0F1"), # dark slate / grey "auto": ("1E8449", "FFFFFF", "C6EFCE", "E9F7EF"), # green - auto-filled "text": ("5B2C6F", "FFFFFF", "E8DAEF", "F5EEF8"), # purple - free text "age": ("1A5276", "FFFFFF", "AED6F1", "D6EAF8"), # navy blue - age/continuous "yesno": ("B7950B", "FFFFFF", "FCF3CF", "FEFDF0"), # gold - yes/no binary "category": ("922B21", "FFFFFF", "FADBD8", "FDEDEC"), # red - categorical coded "diet": ("117A65", "FFFFFF", "A9DFBF", "E9F7EF"), # teal - diet multi-select "consent": ("6E2F00", "FFFFFF", "FAD7A0", "FEF9E7"), # amber - consent "student": ("154360", "FFFFFF", "D6EAF8", "EBF5FB"), # steel blue - student Q "stu_yesno": ("1B4F72", "FFFFFF", "D4E6F1", "EBF5FB"), # lighter blue - student yes/no "stu_age": ("1A5276", "FFFFFF", "A9CCE3", "D4E6F1"), # navy - student ages "stu_text": ("4A235A", "FFFFFF", "D7BDE2", "F4ECF7"), # deep purple - student text } # ── Column → colour type mapping ──────────────────────────────── col_type = { "A": "admin", # ID "B": "admin", # Date "C": "text", # Child name (text) "D": "age", # Age "E": "age", # Grade "F": "category", # School name dropdown "G": "auto", # Residence (auto) "H": "category", # Family type "I": "category", # Father education "J": "category", # Mother education "K": "category", # Income "L": "yesno", # Chronic illness "M": "text", # Illness details "N": "yesno", # Family hx puberty "O": "yesno", # Medications "P": "text", # Medication details "Q": "age", # Age at menarche "R": "diet", # Diet: home-cooked "S": "diet", # Diet: fast food "T": "diet", # Diet: processed "U": "diet", # Diet: high protein "V": "diet", # Diet: Kerala "W": "age", # Outdoor hours "X": "yesno", # Screen time parent "Y": "yesno", # Pesticide "Z": "text", # Pesticide details "AA":"age", # Age puberty signs "AB":"yesno", # Doctor confirmed "AC":"text", # Doctor details "AD":"consent", # Parent consent "AE":"stu_yesno", # S outdoor play "AF":"stu_age", # S outdoor hrs "AG":"stu_yesno", # S screen time "AH":"stu_yesno", # S body changes "AI":"stu_age", # S age body changes "AJ":"stu_yesno", # S menarche "AK":"stu_age", # S age menarche "AL":"stu_text", # S health notes "AM":"consent", # S assent } # ── Re-colour header row (row 3) ────────────────────────────────── ws.row_dimensions[3].height = 64 for col_letter, ctype in col_type.items(): hbg, hfg, _, _ = COLOURS[ctype] c = ws[f"{col_letter}3"] c.font = ft(hfg, bold=True, sz=8) c.fill = fl(hbg) c.alignment = al("center", wrap=True) c.border = bd() # ── Re-colour data rows (4–503) ─────────────────────────────────── for row in range(4, 504): for col_letter, ctype in col_type.items(): hbg, hfg, even_bg, odd_bg = COLOURS[ctype] c = ws[f"{col_letter}{row}"] # Keep green formula fill for auto column G if col_letter == "G": rbg = "C6EFCE" if row % 2 == 0 else "E9F7EF" c.font = ft("1E8449", bold=True, sz=9) else: rbg = even_bg if row % 2 == 0 else odd_bg # text cols: left align; numeric: center if ctype in ("text", "stu_text"): c.font = ft("4A235A" if "stu" in ctype else "4A235A", sz=9) elif ctype == "admin": c.font = ft("2C3E50", bold=(col_letter=="A"), sz=9) elif ctype == "consent": c.font = ft("6E2F00", bold=False, sz=9) else: c.font = ft("000000", sz=9) c.fill = fl(rbg) c.border = bd() if col_letter in ("C","F","M","P","Z","AC","AL"): c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False) elif col_letter == "G": c.alignment = Alignment(horizontal="center", vertical="center") else: c.alignment = Alignment(horizontal="center", vertical="center") # ── Re-colour ID column (A) separately ─────────────────────────── for row in range(4, 504): c = ws[f"A{row}"] c.font = ft("FFFFFF", bold=True, sz=9) c.fill = fl("2C3E50" if row % 2 == 0 else "34495E") c.alignment = Alignment(horizontal="center", vertical="center") c.border = bd() # ── Add a colour legend below the main title ───────────────────── # Row 2 already has subtitle; we'll add a legend row at row 2 as merged note ws["A2"].value = ( "COLOUR KEY: " "GREY = Admin | " "PURPLE = Free Text | " "NAVY = Age/Numbers | " "GOLD = Yes/No | " "RED = Categories | " "TEAL = Diet | " "AMBER = Consent | " "BLUE = Student Q | " "GREEN = Auto-filled (do not edit)" ) ws["A2"].font = ft("FFFFFF", sz=8, bold=True) ws["A2"].fill = fl("17202A") ws["A2"].alignment = Alignment(horizontal="left", vertical="center", wrap_text=False) ws.row_dimensions[2].height = 14 # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v5.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v5.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v5.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── Update column E header ──────────────────────────────────────── hdr = ws["E3"] hdr.value = "P3. Grade/\nStandard\n(6,7,8,9)" hdr.font = ft("FFFFFF", bold=True, sz=8) hdr.fill = fl("1A5276") # keep navy (age/category colour) hdr.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) hdr.border = bd() # ── Remove any existing DV on column E ─────────────────────────── to_remove = [dv for dv in ws.data_validations.dataValidation if "E4" in str(dv.sqref) or ":E" in str(dv.sqref)] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) # ── Add dropdown DV: only 6, 7, 8, 9 ───────────────────────────── dv = DataValidation( type="list", formula1='"6,7,8,9"', allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid Grade", error="Only Standards 6, 7, 8 or 9 are allowed. Please select from the dropdown." ) dv.sqref = "E4:E503" ws.add_data_validation(dv) # ── Update SPSS Codebook ────────────────────────────────────────── cb = wb["SPSS Codebook"] for row in cb.iter_rows(): for cell in row: if cell.value == "Grade": rn = cell.row cb.cell(row=rn, column=6).value = "6 = Standard 6\n7 = Standard 7\n8 = Standard 8\n9 = Standard 9" cb.cell(row=rn, column=5).value = "99" cb.cell(row=rn, column=7).value = "Only Std 6-9 collected" cb.row_dimensions[rn].height = 42 # ── Update Value Codes sheet ────────────────────────────────────── vr = wb["Value Codes"] for row in vr.iter_rows(): for cell in row: if cell.value and "P3" in str(cell.value) and "GRADE" in str(cell.value).upper(): rn = cell.row vr.cell(row=rn, column=2).value = ( "6 = Standard 6\n7 = Standard 7\n8 = Standard 8\n9 = Standard 9\n" "99 = Missing / Not recorded" ) vr.row_dimensions[rn].height = 62 break # If Grade not yet in Value Codes, add it found = any( cell.value and "P3" in str(cell.value) and "GRADE" in str(cell.value).upper() for row in vr.iter_rows() for cell in row ) if not found: max_r = vr.max_row + 2 vr.cell(row=max_r, column=1).value = "P3 - GRADE / STANDARD" vr.cell(row=max_r, column=1).font = Font(name="Arial", size=9, bold=True, color="7F6000") vr.cell(row=max_r, column=1).fill = fl("FFF2CC") vr.cell(row=max_r, column=1).border = bd() vr.cell(row=max_r, column=1).alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) vr.cell(row=max_r, column=2).value = ( "6 = Standard 6\n7 = Standard 7\n8 = Standard 8\n9 = Standard 9\n" "99 = Missing / Not recorded" ) vr.cell(row=max_r, column=2).font = Font(name="Arial", size=9, color="000000") vr.cell(row=max_r, column=2).fill = fl("FFF2CC") vr.cell(row=max_r, column=2).border = bd() vr.cell(row=max_r, column=2).alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) vr.row_dimensions[max_r].height = 62 # ── Update SPSS Syntax missing value for Grade ─────────────────── sx = wb["SPSS Syntax"] for row in sx.iter_rows(): for cell in row: if cell.value and "Age Grade" in str(cell.value): cell.value = cell.value.replace( "Age Grade Age_Menarche", "Age Grade Age_Menarche" ) # also update the FREQUENCIES line if present if cell.value and "FREQUENCIES VARIABLES=Residence" in str(cell.value): # ensure Grade is mentioned in descriptives, not frequencies pass # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v6.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v6.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v6.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── STEP 1: Delete column B (Date of Entry) ────────────────────── ws.delete_cols(2) # column B = index 2 # After deletion, what was C is now B, D→C, E→D, F→E, etc. # ── STEP 2: Fix merged cells in rows 1 & 2 after column deletion ─ # Re-apply title merges (now A:AL instead of A:AM) for mc in list(ws.merged_cells.ranges): ws.unmerge_cells(str(mc)) ws.merge_cells("A1:AL1") ws.merge_cells("A2:AL2") # Re-style row 1 c = ws["A1"] c.value = "PRECOCIOUS PUBERTY STUDY - DATA ENTRY DATABASE" c.font = Font(name="Arial", size=14, bold=True, color="FFFFFF") c.fill = fl("1F4E79") c.alignment = Alignment(horizontal="center", vertical="center") ws.row_dimensions[1].height = 28 # Re-style row 2 c = ws["A2"] c.value = ( "Study: Prevalence of Precocious Puberty in School Girls | " "Location: Manjeri & Anakkayam | Age: 10-15 yrs | " "GREEN columns = auto-filled (do not edit manually)" ) c.font = Font(name="Arial", size=8, bold=True, color="FFFFFF") c.fill = fl("17202A") c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False) ws.row_dimensions[2].height = 14 # ── STEP 3: Add Age dropdown (now column B after deletion) ──────── # Column B is now Age (was D before, then C after first deletion → B now) # Let's confirm by checking header age_col = None for cell in ws[3]: if cell.value and "Age" in str(cell.value) and "10-15" in str(cell.value): age_col = cell.column_letter break print(f"Age column found at: {age_col}") if age_col: # Remove existing whole-number DV on age column to_remove = [dv for dv in ws.data_validations.dataValidation if age_col+"4" in str(dv.sqref) or f":{age_col}" in str(dv.sqref)] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) # Add dropdown 10-15 dv = DataValidation( type="list", formula1='"10,11,12,13,14,15"', allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid Age", error="Age must be 10, 11, 12, 13, 14 or 15. Please select from the dropdown." ) dv.sqref = f"{age_col}4:{age_col}503" ws.add_data_validation(dv) print(f"Dropdown added to {age_col}4:{age_col}503") # ── STEP 4: Update SPSS Codebook – remove Date_Entry row ───────── cb = wb["SPSS Codebook"] rows_to_delete = [] for row in cb.iter_rows(): for cell in row: if cell.value == "Date_Entry": rows_to_delete.append(cell.row) for r in sorted(rows_to_delete, reverse=True): cb.delete_rows(r) # Update Age value labels in codebook for row in cb.iter_rows(): for cell in row: if cell.value == "Age": rn = cell.row cb.cell(row=rn, column=6).value = "10, 11, 12, 13, 14, 15 (years)" cb.cell(row=rn, column=7).value = "Select from dropdown; 10-15 only" # ── STEP 5: Update Value Codes sheet – remove date, update age ─── vr = wb["Value Codes"] for row in vr.iter_rows(): for cell in row: if cell.value and "P2" in str(cell.value) and "AGE" in str(cell.value).upper(): rn = cell.row vr.cell(row=rn, column=2).value = ( "10 = 10 years\n11 = 11 years\n12 = 12 years\n" "13 = 13 years\n14 = 14 years\n15 = 15 years\n" "99 = Missing" ) vr.row_dimensions[rn].height = 80 # ── STEP 6: Update SPSS Syntax – remove Date_Entry references ──── sx = wb["SPSS Syntax"] for row in sx.iter_rows(): for cell in row: if cell.value and "Date_Entry" in str(cell.value): cell.value = cell.value.replace( " Date_Entry 'Date of data entry'", "" ).replace(" Date_Entry 'Date of data entry'\n", "") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v7.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v7.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.utils import get_column_letter, column_index_from_string wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v6.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── Print current merged ranges to understand structure ────────── print("Merged ranges before:", [str(m) for m in ws.merged_cells.ranges]) print("Max col:", ws.max_column) # ── Check what's in each header cell (row 3) ───────────────────── for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:30])}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v6.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── STEP 1: Unmerge rows 1 & 2 safely ──────────────────────────── for mc in list(ws.merged_cells.ranges): ws.merged_cells.remove(mc) # ── STEP 2: Delete column B (Date of Entry) ────────────────────── ws.delete_cols(2) # Now: A=ID, B=Child Name, C=Age, D=Grade, E=School, F=Residence, ... # ── STEP 3: Re-merge and re-style title rows (now 38 cols: A:AL) ─ ws.merge_cells("A1:AL1") c = ws["A1"] c.value = "PRECOCIOUS PUBERTY STUDY - DATA ENTRY DATABASE" c.font = Font(name="Arial", size=14, bold=True, color="FFFFFF") c.fill = fl("1F4E79") c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.row_dimensions[1].height = 28 ws.merge_cells("A2:AL2") c = ws["A2"] c.value = ("Study: Prevalence of Precocious Puberty in School Girls | " "Location: Manjeri & Anakkayam | Age: 10-15 yrs | " "GREEN = auto-filled (do not edit manually)") c.font = Font(name="Arial", size=8, bold=True, color="FFFFFF") c.fill = fl("17202A") c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False) ws.row_dimensions[2].height = 14 # ── STEP 4: Find Age column (now C) and add dropdown 10-15 ─────── # Confirm age_col = None for cell in ws[3]: if cell.value and "Age" in str(cell.value) and "10-15" in str(cell.value): age_col = cell.column_letter break print(f"Age column: {age_col}") # Remove old whole-number DV on age col to_remove = [dv for dv in ws.data_validations.dataValidation if age_col and (f"{age_col}4" in str(dv.sqref) or f":{age_col}" in str(dv.sqref))] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) # Add list dropdown dv_age = DataValidation( type="list", formula1='"10,11,12,13,14,15"', allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle="Invalid Age", error="Age must be 10, 11, 12, 13, 14 or 15. Select from dropdown." ) dv_age.sqref = f"{age_col}4:{age_col}503" ws.add_data_validation(dv_age) print(f"Age dropdown added: {age_col}4:{age_col}503") # ── STEP 5: Update SPSS Codebook ───────────────────────────────── cb = wb["SPSS Codebook"] rows_to_del = [] for row in cb.iter_rows(): for cell in row: if cell.value == "Date_Entry": rows_to_del.append(cell.row) for r in sorted(rows_to_del, reverse=True): cb.delete_rows(r) for row in cb.iter_rows(): for cell in row: if cell.value == "Age": rn = cell.row cb.cell(row=rn, column=6).value = "10,11,12,13,14,15 (years) -- dropdown" cb.cell(row=rn, column=7).value = "Select from dropdown; 10-15 only" # ── STEP 6: Update SPSS Syntax – remove Date_Entry line ────────── sx = wb["SPSS Syntax"] for row in sx.iter_rows(): for cell in row: if cell.value and "Date_Entry" in str(cell.value) and "Date of data entry" in str(cell.value): cell.value = "" # ── STEP 7: Update Value Codes – update Age entry ──────────────── vr = wb["Value Codes"] for row in vr.iter_rows(): for cell in row: if cell.value and "P2" in str(cell.value) and "AGE" in str(cell.value).upper(): rn = cell.row vr.cell(row=rn, column=2).value = ( "10 = 10 years\n11 = 11 years\n12 = 12 years\n" "13 = 13 years\n14 = 14 years\n15 = 15 years\n" "99 = Missing / Not recorded" ) vr.row_dimensions[rn].height = 80 break # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v7.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v7.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print("SAVED:", dst) print("Size:", f"{os.path.getsize(dst):,}", "bytes") # confirm col B header wb2 = openpyxl.load_workbook(dst) ws2 = wb2["Data Entry"] print("Col A:", ws2["A3"].value) print("Col B:", ws2["B3"].value) print("Col C:", ws2["C3"].value) PYEOF

python3 << 'PYEOF' import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v7.xlsx') ws = wb["Data Entry"] print("Current columns (row 3):") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:50])}") print("\nExisting data validations:") for dv in ws.data_validations.dataValidation: print(f" sqref={dv.sqref} type={dv.type} formula1={dv.formula1}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v7.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── Helper: remove ALL existing DVs for a given column letter ──── def remove_dv_for_col(col): to_remove = [dv for dv in ws.data_validations.dataValidation if col in str(dv.sqref)] for dv in to_remove: ws.data_validations.dataValidation.remove(dv) print(f" Removed {len(to_remove)} old DV(s) for col {col}") # ── Helper: add list dropdown for full range ────────────────────── def add_list_dv(col, formula, error_msg, title="Invalid"): dv = DataValidation( type="list", formula1=formula, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=error_msg ) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) print(f" Added list dropdown: {col}4:{col}503 formula={formula}") print("=== Fixing dropdowns ===") # ── P3 Grade = col D → replace whole-number with list 6,7,8,9 ──── print("\nP3 Grade (col D):") remove_dv_for_col("D") add_list_dv("D", '"6,7,8,9"', "Select Standard 6, 7, 8 or 9 from the dropdown.", "Invalid Grade") # ── P6 Family Type = col G → add 1=Nuclear, 2=Joint ───────────── print("\nP6 Family Type (col G):") remove_dv_for_col("G") # G is auto-fill formula col (Residence). Wait - check actual header print(" Col G header:", repr(str(ws["G3"].value)[:50])) print(" Col H header:", repr(str(ws["H3"].value)[:50])) # From our map: G=Family Type (P6), H=Father Education (P7a) # G has NO dropdown, H has "1,2" list (which should be Family Type!) # Family Type is col G — let's add it there add_list_dv("G", '"1,2"', "Enter 1=Nuclear or 2=Joint.", "Invalid Family Type") # ── P10 Family Hx Early Puberty = col M → add 1,0 dropdown ────── print("\nP10 Family Hx (col M):") remove_dv_for_col("M") add_list_dv("M", '"1,0"', "Enter 1=Yes or 0=No.", "Invalid Entry") # ── P13a Home-cooked meals = col Q → replace decimal with list 1,0 print("\nP13a Diet Homecooked (col Q):") remove_dv_for_col("Q") add_list_dv("Q", '"1,0"', "Enter 1=Yes (selected) or 0=No (not selected).", "Invalid Entry") # ── P15 Screen Time = col W → replace decimal with list 1,0 ────── print("\nP15 Screen Time (col W):") remove_dv_for_col("W") add_list_dv("W", '"1,0"', "Enter 1=Yes (screen time >2hrs/day) or 0=No.", "Invalid Entry") # ── P18 Doctor Confirmed = col AA → replace decimal with list 1,0 - print("\nP18 Doctor Confirmed (col AA):") remove_dv_for_col("AA") add_list_dv("AA", '"1,0"', "Enter 1=Yes (doctor confirmed early puberty) or 0=No.", "Invalid Entry") # ── Also fix all Yes/No cols that only have partial coverage ───── # These should all cover rows 4-503 with "1,0" yn_cols = { "K": ("P9. Chronic Illness", "1=Yes or 0=No"), "N": ("P11. Medications/Hormones", "1=Yes or 0=No"), "X": ("P16. Pesticide Exposure", "1=Yes or 0=No"), "AB": ("P18. Doctor Details - N/A it's text", None), # skip - text col "AC": ("P19. Parent Consent", "1=Yes or 0=No"), "AD": ("S1. Outdoor Play", "1=Yes or 0=No"), "AF": ("S2. Screen Time Student", "1=Yes or 0=No"), "AG": ("S3. Body Changes", "1=Yes or 0=No"), "AI": ("S4. First Period", "1=Yes or 0=No"), "AL": ("S6. Student Assent", "1=Yes or 0=No"), } print("\nFixing/consolidating all Yes/No columns:") for col, (label, msg) in yn_cols.items(): if msg is None: print(f" Skipping {col} ({label}) - text field") continue remove_dv_for_col(col) add_list_dv(col, '"1,0"', f"Enter {msg}", "Invalid Entry") # Also ensure these already-correct ones cover full 4-503 for col in ["H","I","J"]: remove_dv_for_col(col) for col, f1, err in [ ("H",'"1,2"',"1=Nuclear or 2=Joint"), ("I",'"1,2,3,4,5"',"1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate"), ("J",'"1,2,3,4,5"',"1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate"), ]: add_list_dv(col, f1, err, "Invalid Entry") # Income col K remove_dv_for_col("K") add_list_dv("K", '"1,2,3,4"', "1=<10000 2=10000-30000 3=30000-50000 4=>50000", "Invalid Income") # ── Verify final state ─────────────────────────────────────────── print("\n=== Final DV summary ===") dvs = sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)) for dv in dvs: print(f" {str(dv.sqref):<20} {dv.type:<8} {dv.formula1}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst}") print(f"Size: {os.path.getsize(dst):,} bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx') ws = wb["Data Entry"] # ── Remove ALL existing DVs and rebuild cleanly from scratch ────── ws.data_validations.dataValidation.clear() print("Cleared all DVs. Rebuilding...") def add_dv(col, dv_type, formula1, formula2=None, error="Invalid entry", title="Invalid", is_list=True, allow_blank=True): if is_list: dv = DataValidation(type="list", formula1=formula1, allow_blank=allow_blank, showDropDown=False, showErrorMessage=True, errorTitle=title, error=error) elif dv_type == "whole": dv = DataValidation(type="whole", operator="between", formula1=formula1, formula2=formula2, allow_blank=allow_blank, showErrorMessage=True, errorTitle=title, error=error) elif dv_type == "decimal": dv = DataValidation(type="decimal", operator="between", formula1=formula1, formula2=formula2, allow_blank=allow_blank, showErrorMessage=True, errorTitle=title, error=error) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) print(f" {col}4:{col}503 {dv_type} {formula1}") # ── All dropdowns clean, full range 4:503 ──────────────────────── # ADMIN add_dv("C","list",'"10,11,12,13,14,15"', error="Age must be 10-15. Select from dropdown.", title="Invalid Age") # P3 Grade add_dv("D","list",'"6,7,8,9"', error="Select Standard 6, 7, 8 or 9.", title="Invalid Grade") # P4 School Name (col E) schools = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' add_dv("E","list", schools, error="Select school from dropdown.", title="Invalid School") # P6 Family Type (col G) add_dv("G","list",'"1,2"', error="1=Nuclear or 2=Joint.", title="Invalid Family Type") # P7a Father Education (col H) add_dv("H","list",'"1,2,3,4,5"', error="1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate", title="Invalid Education") # P7b Mother Education (col I) add_dv("I","list",'"1,2,3,4,5"', error="1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate", title="Invalid Education") # P8 Income (col J) add_dv("J","list",'"1,2,3,4"', error="1=<10000 2=10000-30000 3=30000-50000 4=>50000", title="Invalid Income") # P9 Chronic Illness (col K) - Yes/No add_dv("K","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P10 Family Hx Early Puberty (col M) - Yes/No add_dv("M","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P11 Medications (col N) - Yes/No add_dv("N","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P12 Age at Menarche (col P) - decimal 0-30 or 99 add_dv("P","decimal","0","30", is_list=False, dv_type="decimal", error="Enter age in years (0-30) or 99 if not yet occurred.", title="Invalid Age") # P13a-e Diet items (cols Q-U) - Yes/No for col in ["Q","R","S","T","U"]: add_dv(col,"list",'"1,0"', error="1=Yes (selected) or 0=No.", title="Invalid") # P14 Hours outdoors (col V) - decimal add_dv("V","decimal","0","24", is_list=False, dv_type="decimal", error="Enter hours (0-24).", title="Invalid Hours") # P15 Screen Time parent (col W) - Yes/No add_dv("W","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P16 Pesticide (col X) - Yes/No add_dv("X","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P17 Age Puberty Signs (col Z) - decimal add_dv("Z","decimal","4","20", is_list=False, dv_type="decimal", error="Enter age in years (4-20) or 99 if unknown.", title="Invalid Age") # P18 Doctor Confirmed (col AA) - Yes/No add_dv("AA","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # P19 Parent Consent (col AC) - Yes/No add_dv("AC","list",'"1,0"', error="1=Yes or 0=No.", title="Invalid") # Student Q add_dv("AD","list",'"1,0"', # S1 outdoor play error="1=Yes or 0=No.", title="Invalid") add_dv("AE","decimal","0","24", # S1 outdoor hours is_list=False, dv_type="decimal", error="Enter hours (0-24).", title="Invalid Hours") add_dv("AF","list",'"1,0"', # S2 screen time error="1=Yes or 0=No.", title="Invalid") add_dv("AG","list",'"1,0"', # S3 body changes error="1=Yes or 0=No.", title="Invalid") add_dv("AH","decimal","0","20", # S3 age body changes is_list=False, dv_type="decimal", error="Enter age in years or 99.", title="Invalid Age") add_dv("AI","list",'"1,0"', # S4 first period error="1=Yes or 0=No.", title="Invalid") add_dv("AJ","decimal","8","20", # S4 age first period is_list=False, dv_type="decimal", error="Enter age in years (8-20) or 99.", title="Invalid Age") add_dv("AL","list",'"1,0"', # S6 assent error="1=Yes or 0=No.", title="Invalid") print(f"\nTotal DVs added: {len(ws.data_validations.dataValidation)}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst}") print(f"Size: {os.path.getsize(dst):,} bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v7.xlsx') ws = wb["Data Entry"] ws.data_validations.dataValidation.clear() def add_list(col, f1, err, title="Invalid"): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) def add_dec(col, mn, mx, err, title="Invalid"): dv = DataValidation(type="decimal", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) # ── All validations - clean single range 4:503 ─────────────────── SCHOOLS = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' add_list("C", '"10,11,12,13,14,15"', "Select age 10-15.", "Invalid Age") # P2 Age add_list("D", '"6,7,8,9"', "Select Standard 6, 7, 8 or 9.", "Invalid Grade") # P3 Grade add_list("E", SCHOOLS, "Select school from dropdown.", "Invalid School") # P4 School add_list("G", '"1,2"', "1=Nuclear 2=Joint.", "Invalid") # P6 Family type add_list("H", '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") # P7a Father edu add_list("I", '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") # P7b Mother edu add_list("J", '"1,2,3,4"', "1=<10k 2=10-30k 3=30-50k 4=>50k.","Invalid") # P8 Income add_list("K", '"1,0"', "1=Yes 0=No.", "Invalid") # P9 Chronic illness add_list("M", '"1,0"', "1=Yes 0=No.", "Invalid") # P10 Family hx puberty add_list("N", '"1,0"', "1=Yes 0=No.", "Invalid") # P11 Medications add_dec( "P", 0, 30, "Enter age in years (0-30) or 99 if not yet.", "Invalid Age") # P12 Menarche age add_list("Q", '"1,0"', "1=Yes (selected) 0=No.", "Invalid") # P13a Home-cooked add_list("R", '"1,0"', "1=Yes (selected) 0=No.", "Invalid") # P13b Fast food add_list("S", '"1,0"', "1=Yes (selected) 0=No.", "Invalid") # P13c Processed add_list("T", '"1,0"', "1=Yes (selected) 0=No.", "Invalid") # P13d High protein add_list("U", '"1,0"', "1=Yes (selected) 0=No.", "Invalid") # P13e Trad Kerala add_dec( "V", 0, 24, "Enter hours per day (0-24).", "Invalid Hours") # P14 Outdoor hrs add_list("W", '"1,0"', "1=Yes (>2 hrs/day) 0=No.", "Invalid") # P15 Screen time add_list("X", '"1,0"', "1=Yes 0=No.", "Invalid") # P16 Pesticide add_dec( "Z", 4, 20, "Enter age in years (4-20) or 99.", "Invalid Age") # P17 Puberty signs age add_list("AA",'"1,0"', "1=Yes 0=No.", "Invalid") # P18 Doctor confirmed add_list("AC",'"1,0"', "1=Yes (consent given) 0=No.", "Invalid") # P19 Parent consent add_list("AD",'"1,0"', "1=Yes 0=No.", "Invalid") # S1 Outdoor play add_dec( "AE", 0, 24, "Enter hours per day (0-24).", "Invalid Hours") # S1 Outdoor hrs add_list("AF",'"1,0"', "1=Yes (>2 hrs/day) 0=No.", "Invalid") # S2 Screen time add_list("AG",'"1,0"', "1=Yes 0=No.", "Invalid") # S3 Body changes add_dec( "AH", 0, 20, "Enter age in years or 99.", "Invalid Age") # S3 Age body changes add_list("AI",'"1,0"', "1=Yes 0=No.", "Invalid") # S4 First period add_dec( "AJ", 8, 20, "Enter age in years (8-20) or 99.", "Invalid Age") # S4 Age first period add_list("AL",'"1,0"', "1=Yes (assent given) 0=No.", "Invalid") # S6 Student assent total = len(ws.data_validations.dataValidation) print(f"Total DVs applied: {total}") for dv in sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)): print(f" {str(dv.sqref):<18} {dv.type:<8} {str(dv.formula1)[:55]}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") PYEOF

python3 << 'PYEOF' import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx') ws = wb["Data Entry"] # Check column layout print("Column headers:") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:40])}") print("\nResidence column check:") # Find residence col res_col = None school_col = None for cell in ws[3]: if cell.value and "Residence" in str(cell.value): res_col = cell.column_letter if cell.value and "School" in str(cell.value): school_col = cell.column_letter print(f" School col: {school_col}") print(f" Residence col: {res_col}") if res_col: print(f"\nResidence formula in row 4 ({res_col}4):") print(f" value={repr(ws[f'{res_col}4'].value)}") print(f" data_type={ws[f'{res_col}4'].data_type}") print(f"\nResidence formula in row 5 ({res_col}5):") print(f" value={repr(ws[f'{res_col}5'].value)}") # Check a few rows print(f"\nSample rows (school col {school_col}, residence col {res_col}):") for r in range(4, 10): s = ws[f"{school_col}{r}"].value res = ws[f"{res_col}{r}"].value print(f" Row {r}: School={repr(s)}, Residence={repr(res)}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9): return Font(name="Arial", size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── The problem: after column deletion, School and Residence are # both crammed into col F (F has the auto-formula referencing F itself). # We need to INSERT a new column G for Residence, then: # E = School (dropdown) # F = Residence (auto-formula referencing E) # ── But first, let's check what headers are in E and F right now ── print("Before fix:") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:45])}") # ── INSERT a blank column after F (so current G+ shifts right) ─── # This inserts at column 6 (F), pushing F→G, G→H, etc. ws.insert_cols(6) # insert BEFORE col F, so old F becomes G print("\nAfter insert_cols(6):") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:45])}") # Now layout is: # E = School (dropdown) - still correct # F = NEW blank column - this will be Residence # G = old F contents (what was Family Type or old auto-formula) # Check what moved to G print("\nCol G3 after insert:", repr(str(ws["G3"].value)[:50])) print("Col F3 after insert:", repr(str(ws["F3"].value))) PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v8.xlsx') ws = wb["Data Entry"] def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, name="Arial"): return Font(name=name, size=sz, bold=bold, color=h) def bd(): s = Side(style="thin"); return Border(left=s, right=s, top=s, bottom=s) # ── INSERT col at position 6 (new col F = Residence) ──────────── ws.insert_cols(6) # Now: E=School, F=blank(new Residence), G=old broken auto-col, H=Family Type... # ── Delete old broken Residence at G ───────────────────────────── ws.delete_cols(7) # col G (index 7) = old broken residence # Now: E=School, F=new Residence, G=Family Type, H=Father Edu ... # Verify print("Column headers after fix:") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:45])}") # ── Style and fill col F header ────────────────────────────────── hdr = ws["F3"] hdr.value = "P5. Residence\n(Auto from School)\n1=Urban 2=Semiurban" hdr.font = ft("FFFFFF", bold=True, sz=8) hdr.fill = fl("1E8449") # green = auto-filled hdr.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) hdr.border = bd() ws.column_dimensions["F"].width = 14 # ── Write correct auto-formula in F4:F503 ──────────────────────── # Formula: if E is blank -> blank; if E contains "Irumbuzhi" -> 2 (Semiurban); else -> 1 (Urban) for row in range(4, 504): c = ws[f"F{row}"] c.value = f'=IF(E{row}="","",IF(ISNUMBER(SEARCH("Irumbuzhi",E{row})),2,1))' c.font = ft("1E8449", bold=True, sz=9) c.fill = fl("C6EFCE" if row % 2 == 0 else "E9F7EF") c.alignment = Alignment(horizontal="center", vertical="center") c.border = bd() c.number_format = "0" print("\nF4 formula:", ws["F4"].value) print("F5 formula:", ws["F5"].value) # ── Fix merged title rows (now AM = last col = 39 cols) ────────── for mc in list(ws.merged_cells.ranges): ws.merged_cells.remove(mc) ws.merge_cells("A1:AM1") c = ws["A1"] c.value = "PRECOCIOUS PUBERTY STUDY - DATA ENTRY DATABASE" c.font = Font(name="Arial", size=14, bold=True, color="FFFFFF") c.fill = fl("1F4E79") c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.row_dimensions[1].height = 28 ws.merge_cells("A2:AM2") c = ws["A2"] c.value = ("Study: Prevalence of Precocious Puberty in School Girls | " "Location: Manjeri & Anakkayam | Age: 10-15 yrs | " "GREEN = auto-filled from School (do not edit manually)") c.font = Font(name="Arial", size=8, bold=True, color="FFFFFF") c.fill = fl("17202A") c.alignment = Alignment(horizontal="left", vertical="center") ws.row_dimensions[2].height = 14 # ── Rebuild all DVs cleanly (all column refs shifted by +1 after E) - ws.data_validations.dataValidation.clear() def add_list(col, f1, err, title="Invalid"): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503"; ws.add_data_validation(dv) def add_dec(col, mn, mx, err, title="Invalid"): dv = DataValidation(type="decimal", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503"; ws.add_data_validation(dv) SCHOOLS = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' add_list("C", '"10,11,12,13,14,15"', "Select age 10-15.", "Invalid Age") add_list("D", '"6,7,8,9"', "Select Standard 6,7,8 or 9.", "Invalid Grade") add_list("E", SCHOOLS, "Select school from dropdown.", "Invalid School") # F = auto-formula, no DV needed add_list("G", '"1,2"', "1=Nuclear 2=Joint.", "Invalid") add_list("H", '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") add_list("I", '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") add_list("J", '"1,2,3,4"', "1=<10k 2=10-30k 3=30-50k 4=>50k.","Invalid") add_list("K", '"1,0"', "1=Yes 0=No.", "Invalid") # L = text (illness details) add_list("M", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("N", '"1,0"', "1=Yes 0=No.", "Invalid") # O = text (medication details) add_dec( "P", 0, 30, "Enter age in years (0-30) or 99 if not yet.", "Invalid Age") add_list("Q", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("R", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("S", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("T", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("U", '"1,0"', "1=Yes 0=No.", "Invalid") add_dec( "V", 0, 24, "Enter hours per day (0-24).", "Invalid Hours") add_list("W", '"1,0"', "1=Yes 0=No.", "Invalid") add_list("X", '"1,0"', "1=Yes 0=No.", "Invalid") # Y = text (pesticide details) add_dec( "Z", 4, 20, "Enter age in years (4-20) or 99.", "Invalid Age") add_list("AA",'"1,0"', "1=Yes 0=No.", "Invalid") # AB = text (doctor details) add_list("AC",'"1,0"', "1=Yes 0=No.", "Invalid") add_list("AD",'"1,0"', "1=Yes 0=No.", "Invalid") add_dec( "AE", 0, 24, "Enter hours per day (0-24).", "Invalid Hours") add_list("AF",'"1,0"', "1=Yes 0=No.", "Invalid") add_list("AG",'"1,0"', "1=Yes 0=No.", "Invalid") add_dec( "AH", 0, 20, "Enter age in years or 99.", "Invalid Age") add_list("AI",'"1,0"', "1=Yes 0=No.", "Invalid") add_dec( "AJ", 8, 20, "Enter age in years (8-20) or 99.", "Invalid Age") # AK = text (health notes) add_list("AM",'"1,0"', "1=Yes 0=No.", "Invalid") print(f"\nTotal DVs: {len(ws.data_validations.dataValidation)}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v9.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v9.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") # ── Final verification ──────────────────────────────────────────── wb2 = openpyxl.load_workbook(dst) ws2 = wb2["Data Entry"] print("\nFinal column layout:") for cell in ws2[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:45])}") print("\nResidence formula check:") print(f" F4 = {ws2['F4'].value}") print(f" F5 = {ws2['F5'].value}") PYEOF

python3 << 'PYEOF' import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v9.xlsx') ws = wb["Data Entry"] # Find student assent column print("Columns near end:") for cell in ws[3]: if cell.value: print(f" {cell.column_letter}: {repr(str(cell.value)[:45])}") print("\nAll existing DVs:") for dv in sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)): print(f" {str(dv.sqref):<20} {dv.type:<8} {dv.formula1}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v9.xlsx') ws = wb["Data Entry"] # ── WIPE all DVs and rebuild 100% correctly ────────────────────── ws.data_validations.dataValidation.clear() print("All DVs cleared. Rebuilding from scratch against actual column layout...") # Build a map: column_letter -> question_code from actual headers col_map = {} for cell in ws[3]: if cell.value: col_map[cell.column_letter] = str(cell.value) # Print for verification print("\nActual column -> question mapping:") for col, hdr in col_map.items(): print(f" {col}: {repr(hdr[:50])}") # ── Now add every DV matched to ACTUAL column letters ──────────── def add_list(col, f1, err, title="Invalid"): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) print(f" LIST {col}4:{col}503 {f1[:50]}") def add_dec(col, mn, mx, err, title="Invalid"): dv = DataValidation(type="decimal", operator="between", formula1=str(mn), formula2=str(mx), allow_blank=True, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) print(f" DEC {col}4:{col}503 {mn}-{mx}") SCHOOLS = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' # Match each DV to the ACTUAL column letter from col_map def find_col(keyword1, keyword2=""): for col, hdr in col_map.items(): if keyword1 in hdr and (keyword2 == "" or keyword2 in hdr): return col return None print("\nAdding DVs:") # Age c = find_col("P2. Age"); add_list(c, '"10,11,12,13,14,15"', "Select age 10-15.", "Invalid Age") if c else print(" MISSING: Age") # Grade c = find_col("P3."); add_list(c, '"6,7,8,9"', "Select Standard 6,7,8 or 9.", "Invalid Grade") if c else print(" MISSING: Grade") # School c = find_col("P4. School"); add_list(c, SCHOOLS, "Select school from dropdown.", "Invalid School") if c else print(" MISSING: School") # Residence = F = auto formula, NO DV needed # Family Type c = find_col("P6. Family"); add_list(c, '"1,2"', "1=Nuclear 2=Joint.", "Invalid") if c else print(" MISSING: Family Type") # Father Education c = find_col("P7a."); add_list(c, '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") if c else print(" MISSING: Father Edu") # Mother Education c = find_col("P7b."); add_list(c, '"1,2,3,4,5"', "1=Illiterate 2=Primary 3=Secondary 4=Graduate 5=Post-graduate.", "Invalid") if c else print(" MISSING: Mother Edu") # Income c = find_col("P8."); add_list(c, '"1,2,3,4"', "1=<10k 2=10-30k 3=30-50k 4=>50k.", "Invalid") if c else print(" MISSING: Income") # Chronic Illness c = find_col("P9. Chronic"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Chronic Illness") # Family Hx Puberty c = find_col("P10."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Family Hx") # Medications c = find_col("P11. Medications"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Medications") # Age at Menarche c = find_col("P12."); add_dec(c, 0, 30, "Enter age (0-30) or 99.", "Invalid Age") if c else print(" MISSING: Menarche age") # Diet P13a-e c = find_col("P13a."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Diet 13a") c = find_col("P13b."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Diet 13b") c = find_col("P13c."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Diet 13c") c = find_col("P13d."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Diet 13d") c = find_col("P13e."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Diet 13e") # Outdoor hrs c = find_col("P14."); add_dec(c, 0, 24, "Enter hours (0-24).", "Invalid Hours") if c else print(" MISSING: Outdoor hrs") # Screen time parent c = find_col("P15."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Screen time P") # Pesticide c = find_col("P16. Pesticide\nExposure"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Pesticide") # Age puberty signs c = find_col("P17."); add_dec(c, 4, 20, "Enter age (4-20) or 99.", "Invalid Age") if c else print(" MISSING: Puberty signs age") # Doctor confirmed c = find_col("P18. Doctor\nConfirmed"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: Doctor confirmed") # Parent consent c = find_col("P19."); add_list(c, '"1,0"', "1=Yes (consent given) 0=No.", "Invalid") if c else print(" MISSING: Parent consent") # Student Q c = find_col("S1. Outdoor\nPlay"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: S1 outdoor play") c = find_col("S1. Outdoor\nHours"); add_dec(c, 0, 24, "Enter hours (0-24).", "Invalid Hours") if c else print(" MISSING: S1 outdoor hrs") c = find_col("S2."); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: S2 screen time") c = find_col("S3. Body"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: S3 body changes") c = find_col("S3. Age"); add_dec(c, 0, 20, "Enter age or 99.", "Invalid Age") if c else print(" MISSING: S3 age") c = find_col("S4. First"); add_list(c, '"1,0"', "1=Yes 0=No.", "Invalid") if c else print(" MISSING: S4 first period") c = find_col("S4. Age"); add_dec(c, 8, 20, "Enter age (8-20) or 99.", "Invalid Age") if c else print(" MISSING: S4 age") # S6 Student Assent - find explicitly c = find_col("S6."); if c: add_list(c, '"1,0"', "1=Yes (assent given) 0=No.", "Invalid") print(f" *** Student Assent DV added at {c} ***") else: print(" MISSING: Student Assent!") print(f"\nTotal DVs applied: {len(ws.data_validations.dataValidation)}") # Final check - list all DVs print("\nFinal DV list:") for dv in sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)): print(f" {str(dv.sqref):<20} {dv.type:<8} {str(dv.formula1)[:55]}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v10.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v10.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v10.xlsx') def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, name="Arial", italic=False): return Font(name=name, size=sz, bold=bold, italic=italic, color=h) def bd(style="thin"): s = Side(style=style); return Border(left=s, right=s, top=s, bottom=s) def al(h="left", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) C_TITLE_BG="1F4E79"; C_TITLE_FG="FFFFFF" C_SEC_BG="2E75B6"; C_SEC_FG="FFFFFF" C_HDR_BG="BDD7EE"; C_HDR_FG="1F4E79" C_ALT="EBF3FB"; C_OK="E2EFDA"; C_OK_FG="375623" C_WARN="FFF2CC"; C_WARN_FG="7F6000" C_ERR="FCE4D6"; C_ERR_FG="9C3B1B" C_GREEN="375623"; C_AMBER="7F6000"; C_RED="9C3B1B" # ═══════════════════════════════════════════════════════════════ # SHEET: Validation Report # All formulas reference 'Data Entry'!colRow so they live-update # ═══════════════════════════════════════════════════════════════ vr = wb.create_sheet("Validation Report") vr.sheet_view.showGridLines = False vr.sheet_properties.tabColor = "FF0000" # column widths for col,w in [("A",5),("B",32),("C",16),("D",16),("E",16),("F",40)]: vr.column_dimensions[col].width = w # ── Title ──────────────────────────────────────────────────── vr.merge_cells("A1:F1") c=vr["A1"]; c.value="DATA QUALITY VALIDATION REPORT - Auto-updates as data is entered" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); vr.row_dimensions[1].height=26 vr.merge_cells("A2:F2") c=vr["A2"]; c.value="All counts reference the Data Entry sheet (rows 4-503). GREEN = OK | AMBER = Warning | RED = Action needed" c.font=ft(C_TITLE_FG,sz=9,italic=True); c.fill=fl("17202A") c.alignment=al("left"); vr.row_dimensions[2].height=14 # ── Column headers ─────────────────────────────────────────── r=3 for i,h in enumerate(["#","Check","Total Entered","Issues Found","% Complete","Status / Action"],1): c=vr.cell(row=r,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=10); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() vr.row_dimensions[r].height=20; r+=1 # Helper: write one check row def chk(row, num, label, formula_c, formula_d, formula_e, formula_f, bg_row=None): rbg = C_ALT if row%2==0 else "FFFFFF" vals = [num, label, formula_c, formula_d, formula_e, formula_f] for ci,val in enumerate(vals,1): c=vr.cell(row=row,column=ci,value=val) c.fill=fl(rbg); c.border=bd() c.alignment=al("center" if ci in [1,3,4,5] else "left",wrap=True) c.font=ft("000000",sz=9) vr.row_dimensions[row].height=20 def sec(row, text): vr.merge_cells(f"A{row}:F{row}") c=vr[f"A{row}"]; c.value=text c.font=ft(C_SEC_FG,bold=True,sz=10); c.fill=fl(C_SEC_BG) c.alignment=al("left"); c.border=bd() vr.row_dimensions[row].height=18 # ── SECTION 1: SAMPLE SIZE ─────────────────────────────────── sec(r,"1. SAMPLE SIZE"); r+=1 chk(r,1,"Total rows with data entered (ID filled)", '=COUNTA(\'Data Entry\'!A4:A503)', '=""', '=IFERROR(TEXT(COUNTA(\'Data Entry\'!A4:A503)/500,"0%"),"—")', '=IF(COUNTA(\'Data Entry\'!A4:A503)=0,"No data entered yet","Data entry in progress - "&COUNTA(\'Data Entry\'!A4:A503)&" of 500 rows used")') r+=1 chk(r,2,"Rows with Parent Consent = 1 (included)", '=COUNTIF(\'Data Entry\'!AC4:AC503,1)', '=COUNTIF(\'Data Entry\'!AC4:AC503,0)', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!AC4:AC503,1)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%","—")', '=IF(COUNTIF(\'Data Entry\'!AC4:AC503,0)>0,"WARNING: "&COUNTIF(\'Data Entry\'!AC4:AC503,0)&" participant(s) refused consent - exclude from analysis","All consented participants - OK")') r+=1 chk(r,3,"Rows with Student Assent = 1 (included)", '=COUNTIF(\'Data Entry\'!AL4:AL503,1)', '=COUNTIF(\'Data Entry\'!AL4:AL503,0)', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!AL4:AL503,1)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%","—")', '=IF(COUNTIF(\'Data Entry\'!AL4:AL503,0)>0,"WARNING: "&COUNTIF(\'Data Entry\'!AL4:AL503,0)&" student(s) refused assent - exclude from analysis","All assents given - OK")') r+=1 # ── SECTION 2: MISSING DATA ────────────────────────────────── sec(r,"2. MISSING DATA BY QUESTION"); r+=1 missing_checks = [ (4, "P2. Age", "C"), (5, "P3. Grade", "D"), (6, "P4. School Name", "E"), (7, "P5. Residence (auto)", "F"), (8, "P6. Family Type", "G"), (9, "P7a. Father Education", "H"), (10, "P7b. Mother Education", "I"), (11, "P8. Monthly Income", "J"), (12, "P9. Chronic Illness", "K"), (13, "P10. Family Hx Puberty", "M"), (14, "P11. Medications", "N"), (15, "P13a. Diet: Home-cooked", "Q"), (16, "P13b. Diet: Fast Food", "R"), (17, "P13c. Diet: Processed", "S"), (18, "P13d. Diet: High Protein", "T"), (19, "P13e. Diet: Kerala", "U"), (20, "P15. Screen Time (parent)", "W"), (21, "P16. Pesticide Exposure", "X"), (22, "P18. Doctor Confirmed", "AA"), (23, "P19. Parent Consent", "AC"), (24, "S1. Outdoor Play", "AD"), (25, "S2. Screen Time (student)", "AF"), (26, "S3. Body Changes", "AG"), (27, "S4. First Period", "AI"), (28, "S6. Student Assent", "AL"), ] for num, label, col in missing_checks: n_entered = f"COUNTA('Data Entry'!A4:A503)" n_filled = f"COUNTA('Data Entry'!{col}4:{col}503)" n_blank = f"COUNTA('Data Entry'!A4:A503)-COUNTA('Data Entry'!{col}4:{col}503)" pct = f"IFERROR(ROUND(COUNTA('Data Entry'!{col}4:{col}503)/COUNTA('Data Entry'!A4:A503)*100,1)&\"%\",\"—\")" status = (f"IF(COUNTA('Data Entry'!A4:A503)=0,\"No data yet\"," f"IF(COUNTA('Data Entry'!{col}4:{col}503)=COUNTA('Data Entry'!A4:A503)," f"\"Complete - no missing\"," f"\"MISSING: \"&({n_blank})&\" blank cell(s)\"))") chk(r, num, f"Missing: {label}", f"={n_filled}", f"={n_blank}", f"={pct}", f"={status}") r+=1 # ── SECTION 3: RANGE / VALUE ERRORS ───────────────────────── sec(r,"3. VALUE & RANGE ERROR CHECKS"); r+=1 range_checks = [ (29, "Age out of range (not 10-15)", '=SUMPRODUCT((\'Data Entry\'!C4:C503<>"")*((\'Data Entry\'!C4:C503<10)+(\'Data Entry\'!C4:C503>15)))', '=IF(SUMPRODUCT((\'Data Entry\'!C4:C503<>"")*((\'Data Entry\'!C4:C503<10)+(\'Data Entry\'!C4:C503>15)))=0,"All ages valid - OK","ERROR: "&SUMPRODUCT((\'Data Entry\'!C4:C503<>"")*((\'Data Entry\'!C4:C503<10)+(\'Data Entry\'!C4:C503>15)))&" age value(s) outside 10-15")'), (30, "Grade not in 6,7,8,9", '=SUMPRODUCT((\'Data Entry\'!D4:D503<>"")*((\'Data Entry\'!D4:D503<6)+(\'Data Entry\'!D4:D503>9)))', '=IF(SUMPRODUCT((\'Data Entry\'!D4:D503<>"")*((\'Data Entry\'!D4:D503<6)+(\'Data Entry\'!D4:D503>9)))=0,"All grades valid - OK","ERROR: "&SUMPRODUCT((\'Data Entry\'!D4:D503<>"")*((\'Data Entry\'!D4:D503<6)+(\'Data Entry\'!D4:D503>9)))&" grade value(s) outside 6-9")'), (31, "Menarche age implausible (<8 or >18, excluding 99)", '=SUMPRODUCT((\'Data Entry\'!P4:P503<>"")*((\'Data Entry\'!P4:P503<8)+(\'Data Entry\'!P4:P503>18))*(\'Data Entry\'!P4:P503<>99))', '=IF(SUMPRODUCT((\'Data Entry\'!P4:P503<>"")*((\'Data Entry\'!P4:P503<8)+(\'Data Entry\'!P4:P503>18))*(\'Data Entry\'!P4:P503<>99))=0,"All menarche ages plausible - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!P4:P503<>"")*((\'Data Entry\'!P4:P503<8)+(\'Data Entry\'!P4:P503>18))*(\'Data Entry\'!P4:P503<>99))&" implausible age(s)")'), (32, "Outdoor hours >16 per day (implausible)", '=SUMPRODUCT((\'Data Entry\'!V4:V503<>"")*((\'Data Entry\'!V4:V503>16)))', '=IF(SUMPRODUCT((\'Data Entry\'!V4:V503<>"")*((\'Data Entry\'!V4:V503>16)))=0,"All outdoor hours plausible - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!V4:V503<>"")*((\'Data Entry\'!V4:V503>16)))&" value(s) >16 hrs - possible error")'), (33, "Puberty signs age <5 or >16 (implausible)", '=SUMPRODUCT((\'Data Entry\'!Z4:Z503<>"")*((\'Data Entry\'!Z4:Z503<5)+(\'Data Entry\'!Z4:Z503>16))*(\'Data Entry\'!Z4:Z503<>99))', '=IF(SUMPRODUCT((\'Data Entry\'!Z4:Z503<>"")*((\'Data Entry\'!Z4:Z503<5)+(\'Data Entry\'!Z4:Z503>16))*(\'Data Entry\'!Z4:Z503<>99))=0,"All puberty sign ages plausible - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!Z4:Z503<>"")*((\'Data Entry\'!Z4:Z503<5)+(\'Data Entry\'!Z4:Z503>16))*(\'Data Entry\'!Z4:Z503<>99))&" implausible value(s)")'), (34, "Student menarche age <8 or >16 (implausible)", '=SUMPRODUCT((\'Data Entry\'!AJ4:AJ503<>"")*((\'Data Entry\'!AJ4:AJ503<8)+(\'Data Entry\'!AJ4:AJ503>16))*(\'Data Entry\'!AJ4:AJ503<>99))', '=IF(SUMPRODUCT((\'Data Entry\'!AJ4:AJ503<>"")*((\'Data Entry\'!AJ4:AJ503<8)+(\'Data Entry\'!AJ4:AJ503>16))*(\'Data Entry\'!AJ4:AJ503<>99))=0,"All student menarche ages plausible - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!AJ4:AJ503<>"")*((\'Data Entry\'!AJ4:AJ503<8)+(\'Data Entry\'!AJ4:AJ503>16))*(\'Data Entry\'!AJ4:AJ503<>99))&" implausible value(s)")'), ] for num, label, f_issues, f_status in range_checks: chk(r, num, label, '=COUNTA(\'Data Entry\'!A4:A503)', f_issues, "—", f_status) r+=1 # ── SECTION 4: LOGICAL CONSISTENCY ────────────────────────── sec(r,"4. LOGICAL CONSISTENCY CHECKS"); r+=1 logic_checks = [ (35, "S_Menarche=0 but S_Age_Menarche filled (inconsistent)", '=SUMPRODUCT((\'Data Entry\'!AI4:AI503=0)*(\'Data Entry\'!AJ4:AJ503<>"")*(\'Data Entry\'!AJ4:AJ503<>99))', '=IF(SUMPRODUCT((\'Data Entry\'!AI4:AI503=0)*(\'Data Entry\'!AJ4:AJ503<>"")*(\'Data Entry\'!AJ4:AJ503<>99))=0,"No inconsistencies - OK","INCONSISTENCY: "&SUMPRODUCT((\'Data Entry\'!AI4:AI503=0)*(\'Data Entry\'!AJ4:AJ503<>"")*(\'Data Entry\'!AJ4:AJ503<>99))&" row(s): S4=No but age filled")'), (36, "S_Body_Changes=0 but S_Age_Body_Changes filled (inconsistent)", '=SUMPRODUCT((\'Data Entry\'!AG4:AG503=0)*(\'Data Entry\'!AH4:AH503<>"")*(\'Data Entry\'!AH4:AH503<>99))', '=IF(SUMPRODUCT((\'Data Entry\'!AG4:AG503=0)*(\'Data Entry\'!AH4:AH503<>"")*(\'Data Entry\'!AH4:AH503<>99))=0,"No inconsistencies - OK","INCONSISTENCY: "&SUMPRODUCT((\'Data Entry\'!AG4:AG503=0)*(\'Data Entry\'!AH4:AH503<>"")*(\'Data Entry\'!AH4:AH503<>99))&" row(s): S3=No but age filled")'), (37, "S_Outdoor_Play=0 but Outdoor_Hrs filled (inconsistent)", '=SUMPRODUCT((\'Data Entry\'!AD4:AD503=0)*(\'Data Entry\'!AE4:AE503<>"")*(\'Data Entry\'!AE4:AE503<>0))', '=IF(SUMPRODUCT((\'Data Entry\'!AD4:AD503=0)*(\'Data Entry\'!AE4:AE503<>"")*(\'Data Entry\'!AE4:AE503<>0))=0,"No inconsistencies - OK","INCONSISTENCY: "&SUMPRODUCT((\'Data Entry\'!AD4:AD503=0)*(\'Data Entry\'!AE4:AE503<>"")*(\'Data Entry\'!AE4:AE503<>0))&" row(s): S1=No but hours filled")'), (38, "Chronic_Illness=0 but Illness_Details filled (inconsistent)", '=SUMPRODUCT((\'Data Entry\'!K4:K503=0)*(\'Data Entry\'!L4:L503<>""))', '=IF(SUMPRODUCT((\'Data Entry\'!K4:K503=0)*(\'Data Entry\'!L4:L503<>""))=0,"No inconsistencies - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!K4:K503=0)*(\'Data Entry\'!L4:L503<>""))&" row(s): No illness but details filled")'), (39, "Medications=0 but Medication_Details filled (inconsistent)", '=SUMPRODUCT((\'Data Entry\'!N4:N503=0)*(\'Data Entry\'!O4:O503<>""))', '=IF(SUMPRODUCT((\'Data Entry\'!N4:N503=0)*(\'Data Entry\'!O4:O503<>""))=0,"No inconsistencies - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!N4:N503=0)*(\'Data Entry\'!O4:O503<>""))&" row(s): No meds but details filled")'), (40, "Doctor_Confirmed=1 but Age_Puberty_Signs is 99/blank (incomplete)", '=SUMPRODUCT((\'Data Entry\'!AA4:AA503=1)*((\'Data Entry\'!Z4:Z503=99)+(\'Data Entry\'!Z4:Z503="")))', '=IF(SUMPRODUCT((\'Data Entry\'!AA4:AA503=1)*((\'Data Entry\'!Z4:Z503=99)+(\'Data Entry\'!Z4:Z503="")))=0,"No inconsistencies - OK","CHECK: "&SUMPRODUCT((\'Data Entry\'!AA4:AA503=1)*((\'Data Entry\'!Z4:Z503=99)+(\'Data Entry\'!Z4:Z503="")))&" row(s): Doctor confirmed but no puberty age recorded")'), ] for num, label, f_issues, f_status in logic_checks: chk(r, num, label, '=COUNTA(\'Data Entry\'!A4:A503)', f_issues, "—", f_status) r+=1 # ── SECTION 5: SCHOOL BREAKDOWN ───────────────────────────── sec(r,"5. SCHOOL-WISE DATA COUNT"); r+=1 for num, school in [(41,"1 - Benchmark International School, Manjeri"), (42,"2 - GHSS Girls Manjeri"), (43,"3 - GHSS Irumbuzhi")]: esc = school.replace("'","''") chk(r, num, f"Participants from: {school}", f'=COUNTIF(\'Data Entry\'!E4:E503,"{esc}")', '=""', f'=IFERROR(ROUND(COUNTIF(\'Data Entry\'!E4:E503,"{esc}")/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%","—")', f'=COUNTIF(\'Data Entry\'!E4:E503,"{esc}")&" participants from this school"') r+=1 # Urban vs Rural breakdown chk(r,44,"Urban participants (Residence=1)", '=COUNTIF(\'Data Entry\'!F4:F503,1)', '=""', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!F4:F503,1)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%","—")', '=COUNTIF(\'Data Entry\'!F4:F503,1)&" urban participants"') r+=1 chk(r,45,"Semiurban participants (Residence=2)", '=COUNTIF(\'Data Entry\'!F4:F503,2)', '=""', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!F4:F503,2)/COUNTA(\'Data Entry\'!A4:A503)*100,1)&"%","—")', '=COUNTIF(\'Data Entry\'!F4:F503,2)&" semiurban participants"') r+=1 # ── SECTION 6: OUTCOME SUMMARY ─────────────────────────────── sec(r,"6. KEY OUTCOME SUMMARY"); r+=1 chk(r,46,"Precocious puberty confirmed (Doctor_Confirmed=1)", '=COUNTIF(\'Data Entry\'!AA4:AA503,1)', '=COUNTIF(\'Data Entry\'!AA4:AA503,0)', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!AA4:AA503,1)/COUNTIF(\'Data Entry\'!AC4:AC503,1)*100,1)&"%","—")', '="Prevalence = "&IFERROR(ROUND(COUNTIF(\'Data Entry\'!AA4:AA503,1)/COUNTIF(\'Data Entry\'!AC4:AC503,1)*100,1),"—")&"% of consented participants"') r+=1 chk(r,47,"Student reported menarche (S_Menarche=1)", '=COUNTIF(\'Data Entry\'!AI4:AI503,1)', '=COUNTIF(\'Data Entry\'!AI4:AI503,0)', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!AI4:AI503,1)/COUNTIF(\'Data Entry\'!AL4:AL503,1)*100,1)&"%","—")', '=COUNTIF(\'Data Entry\'!AI4:AI503,1)&" students reported first period"') r+=1 chk(r,48,"Screen time >2hrs (parent) = Yes", '=COUNTIF(\'Data Entry\'!W4:W503,1)', '=""', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!W4:W503,1)/COUNTIF(\'Data Entry\'!AC4:AC503,1)*100,1)&"%","—")', '=COUNTIF(\'Data Entry\'!W4:W503,1)&" participants with screen time >2 hrs/day"') r+=1 chk(r,49,"Family history of early puberty = Yes", '=COUNTIF(\'Data Entry\'!M4:M503,1)', '=""', '=IFERROR(ROUND(COUNTIF(\'Data Entry\'!M4:M503,1)/COUNTIF(\'Data Entry\'!AC4:AC503,1)*100,1)&"%","—")', '=COUNTIF(\'Data Entry\'!M4:M503,1)&" participants with family history"') r+=1 # ── Add conditional formatting using manual cell coloring in Status col ── # Apply colour to the status column (F) based on content keywords # We do this via openpyxl ConditionalFormatting from openpyxl.formatting.rule import CellIsRule, ContainsText, FormulaRule # Green = OK, Amber = WARNING/CHECK, Red = ERROR/INCONSISTENCY ws_data_range = f"F4:F{r}" from openpyxl.styles import PatternFill as PF green_fill = PF("solid", fgColor="C6EFCE") amber_fill = PF("solid", fgColor="FFEB9C") red_fill = PF("solid", fgColor="FFC7CE") from openpyxl.formatting.rule import Rule from openpyxl.styles.differential import DifferentialStyle def cf_contains(sheet, col_range, text, fill): ds = DifferentialStyle(fill=fill) rule = Rule(type='containsText', operator='containsText', text=text, dxf=ds) rule.formula = [f'NOT(ISERROR(SEARCH("{text}",{col_range.split(":")[0]})))'] sheet.conditional_formatting.add(col_range, rule) cf_contains(vr, f"F4:F{r-1}", "OK", green_fill) cf_contains(vr, f"F4:F{r-1}", "WARNING", amber_fill) cf_contains(vr, f"F4:F{r-1}", "CHECK", amber_fill) cf_contains(vr, f"F4:F{r-1}", "ERROR", red_fill) cf_contains(vr, f"F4:F{r-1}", "INCONSISTENCY", red_fill) cf_contains(vr, f"F4:F{r-1}", "MISSING", red_fill) # Also colour column D (Issues Found) - red if >0 ds_red = DifferentialStyle(fill=red_fill, font=Font(color="9C3B1B", bold=True)) ds_grn = DifferentialStyle(fill=green_fill, font=Font(color="375623", bold=True)) rule_nonzero = Rule(type='cellIs', operator='greaterThan', formula=['0'], dxf=ds_red) rule_zero = Rule(type='cellIs', operator='equal', formula=['0'], dxf=ds_grn) vr.conditional_formatting.add(f"D4:D{r-1}", rule_nonzero) vr.conditional_formatting.add(f"D4:D{r-1}", rule_zero) # ── Freeze header ──────────────────────────────────────────── vr.freeze_panes = "A4" print(f"Validation Report: {r-4} check rows created") # ═══════════════════════════════════════════════════════════════ # SHEET: Row-by-Row Error Flag sheet # ═══════════════════════════════════════════════════════════════ ef = wb.create_sheet("Row Error Flags") ef.sheet_view.showGridLines = False ef.sheet_properties.tabColor = "FF7800" for col,w in [("A",5),("B",14),("C",22),("D",15),("E",15),("F",15),("G",15),("H",15),("I",15),("J",40)]: ef.column_dimensions[col].width = w ef.merge_cells("A1:J1") c=ef["A1"]; c.value="ROW-BY-ROW ERROR FLAG TABLE - Highlights problem rows automatically" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); ef.row_dimensions[1].height=26 ef.merge_cells("A2:J2") c=ef["A2"]; c.value="Each row = one participant. RED cell = issue detected. 0 = OK. Review flagged rows in Data Entry sheet." c.font=ft(C_TITLE_FG,sz=9,italic=True); c.fill=fl("17202A") c.alignment=al("left"); ef.row_dimensions[2].height=14 for i,h in enumerate(["Row","ID","Age OK?","Grade OK?","Consent?","Assent?","Menarche Logic","Body Chg Logic","Outdoor Logic","Issues Summary"],1): c=ef.cell(row=3,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=9); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() ef.row_dimensions[3].height=24 ef.freeze_panes = "A4" for row in range(4, 504): dr = row # Data Entry row number same as here rbg = C_ALT if row%2==0 else "FFFFFF" # Row number c=ef.cell(row=row,column=1,value=row) c.font=ft("000000",bold=True,sz=9); c.fill=fl("F2F2F2") c.alignment=al("center"); c.border=bd() # ID c=ef.cell(row=row,column=2,value=f"='Data Entry'!A{dr}") c.font=ft("000000",sz=9); c.fill=fl(rbg) c.alignment=al("center"); c.border=bd() # Age OK (1=ok, 0=error) c=ef.cell(row=row,column=3, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(AND(\'Data Entry\'!C{dr}>=10,\'Data Entry\'!C{dr}<=15),1,0))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Grade OK c=ef.cell(row=row,column=4, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(OR(\'Data Entry\'!D{dr}=6,\'Data Entry\'!D{dr}=7,\'Data Entry\'!D{dr}=8,\'Data Entry\'!D{dr}=9),1,0))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Consent c=ef.cell(row=row,column=5, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(\'Data Entry\'!AC{dr}=1,1,IF(\'Data Entry\'!AC{dr}=0,0,"Missing")))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Assent c=ef.cell(row=row,column=6, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(\'Data Entry\'!AL{dr}=1,1,IF(\'Data Entry\'!AL{dr}=0,0,"Missing")))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Menarche logic: S4=0 but age filled c=ef.cell(row=row,column=7, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(AND(\'Data Entry\'!AI{dr}=0,\'Data Entry\'!AJ{dr}<>"",\'Data Entry\'!AJ{dr}<>99),"FLAG","OK"))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Body changes logic: S3=0 but age filled c=ef.cell(row=row,column=8, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(AND(\'Data Entry\'!AG{dr}=0,\'Data Entry\'!AH{dr}<>"",\'Data Entry\'!AH{dr}<>99),"FLAG","OK"))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Outdoor logic: S1=0 but hours filled c=ef.cell(row=row,column=9, value=f'=IF(\'Data Entry\'!A{dr}="","",IF(AND(\'Data Entry\'!AD{dr}=0,\'Data Entry\'!AE{dr}<>"",\'Data Entry\'!AE{dr}<>0),"FLAG","OK"))') c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("center"); c.border=bd() # Issues summary c=ef.cell(row=row,column=10, value=(f'=IF(\'Data Entry\'!A{dr}="","",TRIM(' f'IF(AND(\'Data Entry\'!C{dr}>=10,\'Data Entry\'!C{dr}<=15),"","Age error; ")&' f'IF(OR(\'Data Entry\'!D{dr}=6,\'Data Entry\'!D{dr}=7,\'Data Entry\'!D{dr}=8,\'Data Entry\'!D{dr}=9),"","Grade error; ")&' f'IF(\'Data Entry\'!AC{dr}=1,"","Consent issue; ")&' f'IF(\'Data Entry\'!AL{dr}=1,"","Assent issue; ")&' f'IF(AND(\'Data Entry\'!AI{dr}=0,\'Data Entry\'!AJ{dr}<>"",\'Data Entry\'!AJ{dr}<>99),"Menarche logic error; ","")&' f'IF(AND(\'Data Entry\'!AG{dr}=0,\'Data Entry\'!AH{dr}<>"",\'Data Entry\'!AH{dr}<>99),"Body chg logic error; ","")&' f'IF(AND(\'Data Entry\'!AD{dr}=0,\'Data Entry\'!AE{dr}<>"",\'Data Entry\'!AE{dr}<>0),"Outdoor logic error; ",""))' f')')) c.font=ft("000000",sz=9); c.fill=fl(rbg); c.alignment=al("left"); c.border=bd() # Conditional formatting on Row Error Flags # Highlight cells with "FLAG" or "0" in cols C-I, and non-blank issues in J for col_letter in ["C","D","E","F","G","H","I"]: cf_range = f"{col_letter}4:{col_letter}503" # FLAG = amber ds_flag = DifferentialStyle(fill=PF("solid", fgColor="FFEB9C"), font=Font(color="7F6000", bold=True)) r_flag = Rule(type='containsText', operator='containsText', text='FLAG', dxf=ds_flag) r_flag.formula = [f'NOT(ISERROR(SEARCH("FLAG",{col_letter}4)))'] ef.conditional_formatting.add(cf_range, r_flag) # 0 = red ds_zero = DifferentialStyle(fill=PF("solid", fgColor="FFC7CE"), font=Font(color="9C3B1B", bold=True)) r_zero = Rule(type='cellIs', operator='equal', formula=['0'], dxf=ds_zero) ef.conditional_formatting.add(cf_range, r_zero) # 1 = green ds_one = DifferentialStyle(fill=PF("solid", fgColor="C6EFCE"), font=Font(color="375623", bold=True)) r_one = Rule(type='cellIs', operator='equal', formula=['1'], dxf=ds_one) ef.conditional_formatting.add(cf_range, r_one) # J column: highlight non-blank non-empty issues ds_issues = DifferentialStyle(fill=PF("solid", fgColor="FFC7CE"), font=Font(color="9C3B1B", bold=True)) r_issues = Rule(type='notContainsText', operator='notContainsBlanks', dxf=ds_issues) r_issues.formula = ['LEN(TRIM(J4))>0'] ef.conditional_formatting.add("J4:J503", r_issues) print("Row Error Flags sheet: 500 rows created") # ── Save ───────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v11.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v11.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") print("Sheets:", wb.sheetnames) PYEOF

ls /home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v10.xlsx && echo "FILE OK"

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.formatting.rule import Rule from openpyxl.styles.differential import DifferentialStyle wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v10.xlsx') def fl(h): return PatternFill("solid", fgColor=h) def ft(h, bold=False, sz=9, italic=False): return Font(name="Arial", size=sz, bold=bold, italic=italic, color=h) def bd(): s = Side(style="thin"); return Border(left=s,right=s,top=s,bottom=s) def al(h="left", wrap=True): return Alignment(horizontal=h, vertical="center", wrap_text=wrap) C_TITLE_BG="1F4E79"; C_TITLE_FG="FFFFFF" C_SEC_BG="2E75B6"; C_SEC_FG="FFFFFF" C_HDR_BG="BDD7EE"; C_HDR_FG="1F4E79" C_ALT="EBF3FB" # ── Shared conditional format fills ──────────────────────────── ok_fill = PatternFill("solid", fgColor="C6EFCE") warn_fill = PatternFill("solid", fgColor="FFEB9C") err_fill = PatternFill("solid", fgColor="FFC7CE") ok_font = Font(name="Arial", color="375623", bold=True, size=9) warn_font = Font(name="Arial", color="7F6000", bold=True, size=9) err_font = Font(name="Arial", color="9C3B1B", bold=True, size=9) def add_cf_contains(sheet, rng, text, fill, font): ds = DifferentialStyle(fill=fill, font=font) rule = Rule(type='containsText', operator='containsText', text=text, dxf=ds) rule.formula = [f'NOT(ISERROR(SEARCH("{text}",{rng.split(":")[0]})))'] sheet.conditional_formatting.add(rng, rule) def add_cf_equals(sheet, rng, val, fill, font): ds = DifferentialStyle(fill=fill, font=font) rule = Rule(type='cellIs', operator='equal', formula=[str(val)], dxf=ds) sheet.conditional_formatting.add(rng, rule) def add_cf_greater(sheet, rng, val, fill, font): ds = DifferentialStyle(fill=fill, font=font) rule = Rule(type='cellIs', operator='greaterThan', formula=[str(val)], dxf=ds) sheet.conditional_formatting.add(rng, rule) # ════════════════════════════════════════════════════════════════ # SHEET 1 – VALIDATION REPORT (summary with 49 live checks) # ════════════════════════════════════════════════════════════════ vr = wb.create_sheet("Validation Report") vr.sheet_view.showGridLines = False vr.sheet_properties.tabColor = "C00000" vr.freeze_panes = "A4" for col,w in [("A",5),("B",34),("C",14),("D",14),("E",12),("F",42)]: vr.column_dimensions[col].width = w # Title vr.merge_cells("A1:F1") c=vr["A1"]; c.value="DATA QUALITY VALIDATION REPORT - Live auto-updates as data is entered" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); vr.row_dimensions[1].height=26 vr.merge_cells("A2:F2") c=vr["A2"]; c.value="GREEN = No issues | AMBER = Warning / Check required | RED = Error / Action needed" c.font=ft(C_TITLE_FG,sz=9,italic=True); c.fill=fl("17202A") c.alignment=al("left"); vr.row_dimensions[2].height=14 # Header row for i,h in enumerate(["#","Quality Check","Valid Count","Issues","% Complete","Status"],1): c=vr.cell(row=3,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=10); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() vr.row_dimensions[3].height=20 r = 4 def sec_hdr(text): global r vr.merge_cells(f"A{r}:F{r}") c=vr[f"A{r}"]; c.value=text c.font=ft(C_SEC_FG,bold=True,sz=10); c.fill=fl(C_SEC_BG) c.alignment=al("left"); c.border=bd() vr.row_dimensions[r].height=18; r+=1 def chk_row(num, label, f_valid, f_issues, f_pct, f_status): global r rbg = C_ALT if r%2==0 else "FFFFFF" for ci,val in enumerate([num,label,f_valid,f_issues,f_pct,f_status],1): c=vr.cell(row=r,column=ci,value=val) c.fill=fl(rbg); c.border=bd() c.font=ft("000000",sz=9) c.alignment=al("center" if ci in [1,3,4,5] else "left",wrap=True) vr.row_dimensions[r].height=20; r+=1 DE = "'Data Entry'" # shorthand # ── SECTION 1: SAMPLE ────────────────────────────────────────── sec_hdr("SECTION 1 – SAMPLE SIZE & CONSENT") chk_row(1,"Total participants entered", f"=COUNTA({DE}!A4:A503)", '=""', f'=IFERROR(TEXT(COUNTA({DE}!A4:A503)/500,"0%"),"—")', f'=COUNTA({DE}!A4:A503)&" of 500 rows used"') chk_row(2,"Parent consent given (=1)", f"=COUNTIF({DE}!AC4:AC503,1)", f"=COUNTIF({DE}!AC4:AC503,0)", f'=IFERROR(ROUND(COUNTIF({DE}!AC4:AC503,1)/COUNTA({DE}!A4:A503)*100,1)&"%","—")', f'=IF(COUNTIF({DE}!AC4:AC503,0)>0,"WARNING: "&COUNTIF({DE}!AC4:AC503,0)&" refused - must exclude","OK: All consented")') chk_row(3,"Student assent given (=1)", f"=COUNTIF({DE}!AL4:AL503,1)", f"=COUNTIF({DE}!AL4:AL503,0)", f'=IFERROR(ROUND(COUNTIF({DE}!AL4:AL503,1)/COUNTA({DE}!A4:A503)*100,1)&"%","—")', f'=IF(COUNTIF({DE}!AL4:AL503,0)>0,"WARNING: "&COUNTIF({DE}!AL4:AL503,0)&" refused - must exclude","OK: All assented")') # ── SECTION 2: MISSING DATA ──────────────────────────────────── sec_hdr("SECTION 2 – MISSING DATA PER QUESTION") missing = [ (4, "P2 Age", "C"), (5, "P3 Grade", "D"), (6, "P4 School Name", "E"), (7, "P6 Family Type", "G"), (8, "P7a Father Education", "H"), (9, "P7b Mother Education", "I"), (10,"P8 Monthly Income", "J"), (11,"P9 Chronic Illness", "K"), (12,"P10 Family Hx Puberty", "M"), (13,"P11 Medications", "N"), (14,"P13a Diet: Home-cooked","Q"), (15,"P13b Diet: Fast Food", "R"), (16,"P13c Diet: Processed", "S"), (17,"P13d Diet: High Protein","T"), (18,"P13e Diet: Kerala", "U"), (19,"P15 Screen Time (Parent)","W"), (20,"P16 Pesticide Exposure","X"), (21,"P18 Doctor Confirmed", "AA"), (22,"P19 Parent Consent", "AC"), (23,"S1 Outdoor Play", "AD"), (24,"S2 Screen Time (Student)","AF"), (25,"S3 Body Changes", "AG"), (26,"S4 First Period", "AI"), (27,"S6 Student Assent", "AL"), ] for num,label,col in missing: chk_row(num, f"Missing: {label}", f"=COUNTA({DE}!{col}4:{col}503)", f"=COUNTA({DE}!A4:A503)-COUNTA({DE}!{col}4:{col}503)", f'=IFERROR(ROUND(COUNTA({DE}!{col}4:{col}503)/COUNTA({DE}!A4:A503)*100,1)&"%","—")', f'=IF(COUNTA({DE}!A4:A503)=0,"No data yet",IF(COUNTA({DE}!{col}4:{col}503)=COUNTA({DE}!A4:A503),"OK: No missing",IF(COUNTA({DE}!A4:A503)-COUNTA({DE}!{col}4:{col}503)>5,"ERROR: "&(COUNTA({DE}!A4:A503)-COUNTA({DE}!{col}4:{col}503))&" missing","WARNING: "&(COUNTA({DE}!A4:A503)-COUNTA({DE}!{col}4:{col}503))&" missing")))') # ── SECTION 3: RANGE ERRORS ──────────────────────────────────── sec_hdr("SECTION 3 – RANGE & VALUE ERROR CHECKS") chk_row(28,"Age outside 10-15", f"=COUNTA({DE}!C4:C503)", f'=SUMPRODUCT(({DE}!C4:C503<>"")*({DE}!C4:C503<>"")*(({DE}!C4:C503<10)+({DE}!C4:C503>15)))', "—", f'=IF(SUMPRODUCT(({DE}!C4:C503<>"")*({DE}!C4:C503<>"")*(({DE}!C4:C503<10)+({DE}!C4:C503>15)))=0,"OK: All ages valid","ERROR: "&SUMPRODUCT(({DE}!C4:C503<>"")*({DE}!C4:C503<>"")*(({DE}!C4:C503<10)+({DE}!C4:C503>15)))&" age(s) out of range")') chk_row(29,"Grade outside 6-9", f"=COUNTA({DE}!D4:D503)", f'=SUMPRODUCT(({DE}!D4:D503<>"")*({DE}!D4:D503<>"")*(({DE}!D4:D503<6)+({DE}!D4:D503>9)))', "—", f'=IF(SUMPRODUCT(({DE}!D4:D503<>"")*({DE}!D4:D503<>"")*(({DE}!D4:D503<6)+({DE}!D4:D503>9)))=0,"OK: All grades valid","ERROR: "&SUMPRODUCT(({DE}!D4:D503<>"")*({DE}!D4:D503<>"")*(({DE}!D4:D503<6)+({DE}!D4:D503>9)))&" invalid grade(s)")') chk_row(30,"Menarche age implausible (<8 or >18, excl 99)", f"=COUNTIF({DE}!P4:P503,\"<99\")", f'=SUMPRODUCT(({DE}!P4:P503<>"")*({DE}!P4:P503<>99)*(({DE}!P4:P503<8)+({DE}!P4:P503>18)))', "—", f'=IF(SUMPRODUCT(({DE}!P4:P503<>"")*({DE}!P4:P503<>99)*(({DE}!P4:P503<8)+({DE}!P4:P503>18)))=0,"OK: All menarche ages plausible","CHECK: "&SUMPRODUCT(({DE}!P4:P503<>"")*({DE}!P4:P503<>99)*(({DE}!P4:P503<8)+({DE}!P4:P503>18)))&" implausible value(s)")') chk_row(31,"Outdoor hours >16/day (implausible)", f"=COUNTA({DE}!V4:V503)", f'=SUMPRODUCT(({DE}!V4:V503<>"")*({DE}!V4:V503>16))', "—", f'=IF(SUMPRODUCT(({DE}!V4:V503<>"")*({DE}!V4:V503>16))=0,"OK: All outdoor hours plausible","CHECK: "&SUMPRODUCT(({DE}!V4:V503<>"")*({DE}!V4:V503>16))&" values >16 hrs")') chk_row(32,"Puberty signs age <5 or >16 (excl 99)", f"=COUNTIF({DE}!Z4:Z503,\"<99\")", f'=SUMPRODUCT(({DE}!Z4:Z503<>"")*({DE}!Z4:Z503<>99)*(({DE}!Z4:Z503<5)+({DE}!Z4:Z503>16)))', "—", f'=IF(SUMPRODUCT(({DE}!Z4:Z503<>"")*({DE}!Z4:Z503<>99)*(({DE}!Z4:Z503<5)+({DE}!Z4:Z503>16)))=0,"OK: All puberty ages plausible","CHECK: "&SUMPRODUCT(({DE}!Z4:Z503<>"")*({DE}!Z4:Z503<>99)*(({DE}!Z4:Z503<5)+({DE}!Z4:Z503>16)))&" implausible value(s)")') chk_row(33,"Student menarche age <8 or >16 (excl 99)", f"=COUNTIF({DE}!AJ4:AJ503,\"<99\")", f'=SUMPRODUCT(({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99)*(({DE}!AJ4:AJ503<8)+({DE}!AJ4:AJ503>16)))', "—", f'=IF(SUMPRODUCT(({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99)*(({DE}!AJ4:AJ503<8)+({DE}!AJ4:AJ503>16)))=0,"OK: All student menarche ages plausible","CHECK: "&SUMPRODUCT(({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99)*(({DE}!AJ4:AJ503<8)+({DE}!AJ4:AJ503>16)))&" implausible value(s)")') # ── SECTION 4: LOGICAL CONSISTENCY ──────────────────────────── sec_hdr("SECTION 4 – LOGICAL CONSISTENCY CHECKS") chk_row(34,"S4 Period=No but age at period filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!AI4:AI503=0)*({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99))', "—", f'=IF(SUMPRODUCT(({DE}!AI4:AI503=0)*({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99))=0,"OK: No inconsistencies","ERROR: "&SUMPRODUCT(({DE}!AI4:AI503=0)*({DE}!AJ4:AJ503<>"")*({DE}!AJ4:AJ503<>99))&" row(s): S4=No but age filled")') chk_row(35,"S3 Body changes=No but age at changes filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!AG4:AG503=0)*({DE}!AH4:AH503<>"")*({DE}!AH4:AH503<>99))', "—", f'=IF(SUMPRODUCT(({DE}!AG4:AG503=0)*({DE}!AH4:AH503<>"")*({DE}!AH4:AH503<>99))=0,"OK: No inconsistencies","ERROR: "&SUMPRODUCT(({DE}!AG4:AG503=0)*({DE}!AH4:AH503<>"")*({DE}!AH4:AH503<>99))&" row(s): S3=No but age filled")') chk_row(36,"S1 Outdoor play=No but hours filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!AD4:AD503=0)*({DE}!AE4:AE503<>"")*({DE}!AE4:AE503<>0))', "—", f'=IF(SUMPRODUCT(({DE}!AD4:AD503=0)*({DE}!AE4:AE503<>"")*({DE}!AE4:AE503<>0))=0,"OK: No inconsistencies","ERROR: "&SUMPRODUCT(({DE}!AD4:AD503=0)*({DE}!AE4:AE503<>"")*({DE}!AE4:AE503<>0))&" row(s): S1=No but hours filled")') chk_row(37,"P9 No illness but illness details filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!K4:K503=0)*({DE}!L4:L503<>""))', "—", f'=IF(SUMPRODUCT(({DE}!K4:K503=0)*({DE}!L4:L503<>""))=0,"OK: No inconsistencies","WARNING: "&SUMPRODUCT(({DE}!K4:K503=0)*({DE}!L4:L503<>""))&" row(s): No illness but details filled")') chk_row(38,"P11 No medications but details filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!N4:N503=0)*({DE}!O4:O503<>""))', "—", f'=IF(SUMPRODUCT(({DE}!N4:N503=0)*({DE}!O4:O503<>""))=0,"OK: No inconsistencies","WARNING: "&SUMPRODUCT(({DE}!N4:N503=0)*({DE}!O4:O503<>""))&" row(s): No meds but details filled")') chk_row(39,"P16 No pesticide but details filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!X4:X503=0)*({DE}!Y4:Y503<>""))', "—", f'=IF(SUMPRODUCT(({DE}!X4:X503=0)*({DE}!Y4:Y503<>""))=0,"OK: No inconsistencies","WARNING: "&SUMPRODUCT(({DE}!X4:X503=0)*({DE}!Y4:Y503<>""))&" row(s): No pesticide but details filled")') chk_row(40,"P18 No doctor confirm but details filled", f"=COUNTA({DE}!A4:A503)", f'=SUMPRODUCT(({DE}!AA4:AA503=0)*({DE}!AB4:AB503<>""))', "—", f'=IF(SUMPRODUCT(({DE}!AA4:AA503=0)*({DE}!AB4:AB503<>""))=0,"OK: No inconsistencies","WARNING: "&SUMPRODUCT(({DE}!AA4:AA503=0)*({DE}!AB4:AB503<>""))&" row(s): Not confirmed but details filled")') # ── SECTION 5: SCHOOL BREAKDOWN ──────────────────────────────── sec_hdr("SECTION 5 – SCHOOL & RESIDENCE BREAKDOWN") for num,school in [ (41,"1 - Benchmark International School, Manjeri"), (42,"2 - GHSS Girls Manjeri"), (43,"3 - GHSS Irumbuzhi")]: chk_row(num, f"Participants: {school}", f'=COUNTIF({DE}!E4:E503,"{school}")', '=""', f'=IFERROR(ROUND(COUNTIF({DE}!E4:E503,"{school}")/COUNTA({DE}!A4:A503)*100,1)&"%","—")', f'=COUNTIF({DE}!E4:E503,"{school}")&" participants from this school"') chk_row(44,"Urban participants (Residence=1)", f"=COUNTIF({DE}!F4:F503,1)","","", f'=COUNTIF({DE}!F4:F503,1)&" urban | "&COUNTIF({DE}!F4:F503,2)&" semiurban"') chk_row(45,"Residence auto-fill errors (blank when school filled)", f"=COUNTA({DE}!E4:E503)", f'=SUMPRODUCT(({DE}!E4:E503<>"")*({DE}!F4:F503=""))', "—", f'=IF(SUMPRODUCT(({DE}!E4:E503<>"")*({DE}!F4:F503=""))=0,"OK: All residence values auto-filled","ERROR: "&SUMPRODUCT(({DE}!E4:E503<>"")*({DE}!F4:F503=""))&" row(s): School filled but residence blank")') # ── SECTION 6: KEY OUTCOMES ──────────────────────────────────── sec_hdr("SECTION 6 – KEY OUTCOME SUMMARY") chk_row(46,"Precocious puberty confirmed (Doctor=1)", f"=COUNTIF({DE}!AA4:AA503,1)", f"=COUNTIF({DE}!AA4:AA503,0)", f'=IFERROR(ROUND(COUNTIF({DE}!AA4:AA503,1)/COUNTIF({DE}!AC4:AC503,1)*100,1)&"%","—")', f'="Estimated prevalence: "&IFERROR(ROUND(COUNTIF({DE}!AA4:AA503,1)/COUNTIF({DE}!AC4:AC503,1)*100,1),"—")&"% of consented participants"') chk_row(47,"Student reported menarche (S4=1)", f"=COUNTIF({DE}!AI4:AI503,1)","","", f'=COUNTIF({DE}!AI4:AI503,1)&" students reported first period"') chk_row(48,"Family history of early puberty (P10=1)", f"=COUNTIF({DE}!M4:M503,1)","","", f'=COUNTIF({DE}!M4:M503,1)&" with positive family history"') chk_row(49,"Screen time >2hrs both sources agree (=1)", f'=SUMPRODUCT(({DE}!W4:W503=1)*({DE}!AF4:AF503=1))', "","", f'=SUMPRODUCT(({DE}!W4:W503=1)*({DE}!AF4:AF503=1))&" participants: both parent & student report screen time >2hrs"') # ── Conditional formatting on Status column (F) ──────────────── last_data_row = r - 1 cf_range = f"F4:F{last_data_row}" add_cf_contains(vr, cf_range, "OK", ok_fill, ok_font) add_cf_contains(vr, cf_range, "WARNING", warn_fill, warn_font) add_cf_contains(vr, cf_range, "CHECK", warn_fill, warn_font) add_cf_contains(vr, cf_range, "ERROR", err_fill, err_font) add_cf_contains(vr, cf_range, "INCONSISTENCY", err_fill, err_font) add_cf_contains(vr, cf_range, "MISSING", err_fill, err_font) # Issues column D: red if >0, green if 0 add_cf_greater(vr, f"D4:D{last_data_row}", 0, err_fill, err_font) add_cf_equals (vr, f"D4:D{last_data_row}", 0, ok_fill, ok_font) print(f"Validation Report: {last_data_row-3} rows created") # ════════════════════════════════════════════════════════════════ # SHEET 2 – ROW ERROR FLAGS (one row per participant) # ════════════════════════════════════════════════════════════════ ef = wb.create_sheet("Row Error Flags") ef.sheet_view.showGridLines = False ef.sheet_properties.tabColor = "FF7800" ef.freeze_panes = "A4" for col,w in [("A",5),("B",12),("C",10),("D",10),("E",10),("F",10), ("G",12),("H",12),("I",12),("J",42)]: ef.column_dimensions[col].width = w ef.merge_cells("A1:J1") c=ef["A1"]; c.value="ROW-BY-ROW ERROR FLAG TABLE - One row per participant, flags errors automatically" c.font=ft(C_TITLE_FG,bold=True,sz=13); c.fill=fl(C_TITLE_BG) c.alignment=al("center"); ef.row_dimensions[1].height=26 ef.merge_cells("A2:J2") c=ef["A2"]; c.value="GREEN=OK | AMBER=Warning | RED=Error | Check flagged rows in Data Entry sheet" c.font=ft(C_TITLE_FG,sz=9,italic=True); c.fill=fl("17202A") c.alignment=al("left"); ef.row_dimensions[2].height=14 for i,h in enumerate(["Row","ID","Age\nOK?","Grade\nOK?","Consent\n=1?","Assent\n=1?", "Menarche\nLogic","Body Chg\nLogic","Outdoor\nLogic","Issues Summary"],1): c=ef.cell(row=3,column=i,value=h) c.font=ft(C_HDR_FG,bold=True,sz=9); c.fill=fl(C_HDR_BG) c.alignment=al("center",wrap=True); c.border=bd() ef.row_dimensions[3].height=30 for row in range(4, 504): dr = row rbg = C_ALT if row%2==0 else "FFFFFF" def cell(col, val, ctr=True): c = ef.cell(row=row, column=col, value=val) c.fill=fl(rbg); c.border=bd(); c.font=ft("000000",sz=9) c.alignment=al("center" if ctr else "left",wrap=True) cell(1, row) cell(2, f"='Data Entry'!A{dr}") cell(3, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF(AND('Data Entry'!C{dr}>=10,'Data Entry'!C{dr}<=15),1,0))") cell(4, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF(OR('Data Entry'!D{dr}=6,'Data Entry'!D{dr}=7,'Data Entry'!D{dr}=8,'Data Entry'!D{dr}=9),1,0))") cell(5, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF('Data Entry'!AC{dr}=1,1,0))") cell(6, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF('Data Entry'!AL{dr}=1,1,0))") cell(7, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF(AND('Data Entry'!AI{dr}=0,'Data Entry'!AJ{dr}<>\"\",'Data Entry'!AJ{dr}<>99),\"FLAG\",\"OK\"))") cell(8, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF(AND('Data Entry'!AG{dr}=0,'Data Entry'!AH{dr}<>\"\",'Data Entry'!AH{dr}<>99),\"FLAG\",\"OK\"))") cell(9, f"=IF('Data Entry'!A{dr}=\"\",\"\",IF(AND('Data Entry'!AD{dr}=0,'Data Entry'!AE{dr}<>\"\",'Data Entry'!AE{dr}<>0),\"FLAG\",\"OK\"))") cell(10, f"=IF('Data Entry'!A{dr}=\"\",\"\"," f"TRIM(" f"IF(AND('Data Entry'!C{dr}>=10,'Data Entry'!C{dr}<=15),\"\",\"Age error; \")&" f"IF(OR('Data Entry'!D{dr}=6,'Data Entry'!D{dr}=7,'Data Entry'!D{dr}=8,'Data Entry'!D{dr}=9),\"\",\"Grade error; \")&" f"IF('Data Entry'!AC{dr}=1,\"\",\"Consent missing/refused; \")&" f"IF('Data Entry'!AL{dr}=1,\"\",\"Assent missing/refused; \")&" f"IF(AND('Data Entry'!AI{dr}=0,'Data Entry'!AJ{dr}<>\"\",'Data Entry'!AJ{dr}<>99),\"Menarche logic error; \",\"\")&" f"IF(AND('Data Entry'!AG{dr}=0,'Data Entry'!AH{dr}<>\"\",'Data Entry'!AH{dr}<>99),\"Body change logic error; \",\"\")&" f"IF(AND('Data Entry'!AD{dr}=0,'Data Entry'!AE{dr}<>\"\",'Data Entry'!AE{dr}<>0),\"Outdoor logic error; \",\"\")" f"))", ctr=False) # Conditional formatting Row Error Flags for col_letter in ["C","D","E","F"]: rng = f"{col_letter}4:{col_letter}503" add_cf_equals(ef, rng, 0, err_fill, err_font) add_cf_equals(ef, rng, 1, ok_fill, ok_font) for col_letter in ["G","H","I"]: rng = f"{col_letter}4:{col_letter}503" add_cf_contains(ef, rng, "FLAG", warn_fill, warn_font) add_cf_contains(ef, rng, "OK", ok_fill, ok_font) # J column: highlight if not empty (has issues text) ds_issues = DifferentialStyle(fill=err_fill, font=err_font) r_issues = Rule(type='expression', formula=['LEN(TRIM(J4))>0'], dxf=ds_issues) ef.conditional_formatting.add("J4:J503", r_issues) # ID column: dark if filled ef["A3"].fill = fl("2C3E50"); ef["A3"].font = ft("FFFFFF",bold=True,sz=9) for row in range(4,504): ef.cell(row=row,column=1).fill = fl("2C3E50" if row%2==0 else "34495E") ef.cell(row=row,column=1).font = ft("FFFFFF",bold=True,sz=9) ef.cell(row=row,column=1).alignment = al("center") print("Row Error Flags: 500 rows created") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v11.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v11.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") print("Sheets:", wb.sheetnames) PYEOF

ls /home/daytona/skills/docx/

mkdir -p /home/daytona/workspace/spss-guide && cat << 'JSEOF' > /home/daytona/workspace/spss-guide/create_guide.js const { Document, Packer, Paragraph, TextRun, Table, TableRow, TableCell, HeadingLevel, AlignmentType, BorderStyle, WidthType, ShadingType, VerticalAlign, PageNumber, Header, Footer, TabStopType, TabStopPosition, PageBreak } = require('docx'); const fs = require('fs'); // ── Colour palette ──────────────────────────────────────────── const DARK_BLUE = "1F4E79"; const MID_BLUE = "2E75B6"; const LIGHT_BLUE = "BDD7EE"; const TEAL = "117A65"; const AMBER = "7F6000"; const RED = "922B21"; const GREEN = "375623"; const PURPLE = "6E2F00"; const LIGHT_GREY = "F2F2F2"; const WHITE = "FFFFFF"; // ── Helpers ────────────────────────────────────────────────── const txt = (text, opts={}) => new TextRun({ text, font: "Arial", size: (opts.sz || 10) * 2, bold: opts.bold || false, italics: opts.italic || false, color: opts.color || "000000", ...opts }); const h1 = (text) => new Paragraph({ heading: HeadingLevel.HEADING_1, spacing: { before: 300, after: 120 }, children: [new TextRun({ text, font: "Arial", size: 28, bold: true, color: WHITE, highlight: "none", })], shading: { type: ShadingType.SOLID, color: DARK_BLUE, fill: DARK_BLUE }, indent: { left: 200, right: 200 }, }); const h2 = (text) => new Paragraph({ heading: HeadingLevel.HEADING_2, spacing: { before: 240, after: 80 }, children: [new TextRun({ text, font: "Arial", size: 24, bold: true, color: WHITE })], shading: { type: ShadingType.SOLID, color: MID_BLUE, fill: MID_BLUE }, indent: { left: 100 }, }); const h3 = (text) => new Paragraph({ heading: HeadingLevel.HEADING_3, spacing: { before: 160, after: 60 }, children: [new TextRun({ text, font: "Arial", size: 22, bold: true, color: DARK_BLUE })], border: { bottom: { style: BorderStyle.SINGLE, size: 6, color: MID_BLUE } }, }); const para = (text, opts={}) => new Paragraph({ spacing: { before: 60, after: 60 }, indent: opts.indent ? { left: 360 } : {}, children: [txt(text, opts)], }); const bullet = (text, opts={}) => new Paragraph({ bullet: { level: 0 }, spacing: { before: 40, after: 40 }, indent: { left: 360, hanging: 180 }, children: [txt(text, { sz: 10, ...opts })], }); const sub_bullet = (text) => new Paragraph({ bullet: { level: 1 }, spacing: { before: 20, after: 20 }, indent: { left: 720, hanging: 180 }, children: [txt(text, { sz: 9, color: "444444" })], }); const code_para = (text) => new Paragraph({ spacing: { before: 40, after: 40 }, indent: { left: 360 }, shading: { type: ShadingType.SOLID, fill: "F0F7FF", color: "F0F7FF" }, border: { left: { style: BorderStyle.SINGLE, size: 12, color: MID_BLUE }, }, children: [new TextRun({ text, font: "Courier New", size: 18, color: "154360" })], }); const note_box = (text) => new Paragraph({ spacing: { before: 80, after: 80 }, indent: { left: 200, right: 200 }, shading: { type: ShadingType.SOLID, fill: "FFF9E6", color: "FFF9E6" }, border: { left: { style: BorderStyle.THICK, size: 12, color: "F4B942" } }, children: [txt(" NOTE: " + text, { sz: 9, italic: true, color: AMBER })], }); const pageBreak = () => new Paragraph({ children: [new PageBreak()] }); // ── Table builder ───────────────────────────────────────────── function makeTable(headers, rows, colWidths) { const headerCells = headers.map((h, i) => new TableCell({ width: { size: colWidths[i], type: WidthType.DXA }, shading: { type: ShadingType.SOLID, fill: DARK_BLUE, color: DARK_BLUE }, verticalAlign: VerticalAlign.CENTER, children: [new Paragraph({ alignment: AlignmentType.CENTER, children: [new TextRun({ text: h, font: "Arial", size: 18, bold: true, color: WHITE })], })], })); const dataRows = rows.map((row, ri) => new TableRow({ children: row.map((cell, ci) => new TableCell({ width: { size: colWidths[ci], type: WidthType.DXA }, shading: { type: ShadingType.SOLID, fill: ri % 2 === 0 ? "EBF3FB" : WHITE, color: ri % 2 === 0 ? "EBF3FB" : WHITE }, verticalAlign: VerticalAlign.TOP, children: [new Paragraph({ spacing: { before: 40, after: 40 }, children: [new TextRun({ text: cell, font: "Arial", size: 18, color: "000000" })], })], })) })); return new Table({ width: { size: 9200, type: WidthType.DXA }, rows: [new TableRow({ children: headerCells }), ...dataRows], }); } // ── Syntax block with label ──────────────────────────────────── function syntaxBlock(label, lines) { const items = []; items.push(new Paragraph({ spacing: { before: 60, after: 20 }, children: [new TextRun({ text: `SPSS Syntax – ${label}`, font: "Arial", size: 18, bold: true, color: TEAL })], })); for (const line of lines) { items.push(new Paragraph({ spacing: { before: 0, after: 0 }, shading: { type: ShadingType.SOLID, fill: "F0F7FF", color: "F0F7FF" }, border: { left: { style: BorderStyle.THICK, size: 10, color: MID_BLUE } }, indent: { left: 200 }, children: [new TextRun({ text: line || " ", font: "Courier New", size: 18, color: "154360" })], })); } return items; } // ═══════════════════════════════════════════════════════════════ // DOCUMENT CONTENT // ═══════════════════════════════════════════════════════════════ const children = [ // ── COVER PAGE ─────────────────────────────────────────────── new Paragraph({ spacing: { before: 1200, after: 200 }, alignment: AlignmentType.CENTER, shading: { type: ShadingType.SOLID, fill: DARK_BLUE, color: DARK_BLUE }, children: [new TextRun({ text: "SPSS ANALYSIS GUIDE", font: "Arial", size: 52, bold: true, color: WHITE })], }), new Paragraph({ alignment: AlignmentType.CENTER, spacing: { before: 100, after: 100 }, shading: { type: ShadingType.SOLID, fill: DARK_BLUE, color: DARK_BLUE }, children: [new TextRun({ text: "Prevalence of Precocious Puberty in School Girls", font: "Arial", size: 28, bold: true, color: "BDD7EE" })], }), new Paragraph({ alignment: AlignmentType.CENTER, spacing: { before: 60, after: 60 }, shading: { type: ShadingType.SOLID, fill: DARK_BLUE, color: DARK_BLUE }, children: [new TextRun({ text: "Manjeri & Anakkayam, Kerala | Grades 6-9 | Age 10-15 years", font: "Arial", size: 22, color: "BDD7EE" })], }), new Paragraph({ alignment: AlignmentType.CENTER, spacing: { before: 600, after: 200 }, shading: { type: ShadingType.SOLID, fill: DARK_BLUE, color: DARK_BLUE }, children: [new TextRun({ text: "Statistical Tests | SPSS Syntax | Reporting Format", font: "Arial", size: 20, italic: true, color: "D6E4F0" })], }), pageBreak(), // ── TABLE OF CONTENTS NOTE ──────────────────────────────────── h1("CONTENTS"), para("1. Overview of Study Design & Analysis Approach", { bold: true }), para("2. Step-by-Step SPSS Workflow", { bold: true }), para("3. Step 1 – Data Import & Labelling", { bold: true }), para("4. Step 2 – Descriptive Statistics", { bold: true }), para("5. Step 3 – Bivariate Analysis (Chi-square & t-tests)", { bold: true }), para("6. Step 4 – Multivariable Analysis (Logistic Regression)", { bold: true }), para("7. Step 5 – Correlation Analysis", { bold: true }), para("8. Step 6 – School-wise & Subgroup Comparison", { bold: true }), para("9. Reporting Format & Interpretation", { bold: true }), para("10. Variable Reference Table", { bold: true }), pageBreak(), // ── SECTION 1: OVERVIEW ───────────────────────────────────── h1("1. OVERVIEW OF STUDY DESIGN & ANALYSIS APPROACH"), para("This guide provides a complete statistical analysis plan for the study:"), para("\"Prevalence of Precocious Puberty in School Girls\" conducted in Manjeri and Anakkayam, Kerala.", { bold: true }), para(""), h3("Study Objectives"), bullet("Estimate the prevalence of precocious puberty among school girls aged 10-15."), bullet("Identify risk factors associated with precocious puberty."), bullet("Compare risk factor distribution across urban and semiurban schools."), bullet("Determine independent predictors of precocious puberty using logistic regression."), para(""), h3("Study Design"), makeTable( ["Parameter", "Details"], [ ["Study Type", "Cross-sectional descriptive study"], ["Population", "School girls aged 10-15, Grades 6-9"], ["Schools", "Benchmark International School Manjeri (Urban), GHSS Girls Manjeri (Urban), GHSS Irumbuzhi (Semiurban)"], ["Sample Size", "Up to 500 participants"], ["Data Sources", "Parent/Guardian questionnaire (P1-P19) + Student questionnaire (S1-S6)"], ["Primary Outcome", "Precocious puberty (Doctor_Confirmed = 1)"], ["Analysis Software","IBM SPSS Statistics v25 or later"], ], [2400, 6800] ), para(""), h3("Types of Variables"), makeTable( ["Variable Type", "Examples", "SPSS Measure Level"], [ ["Categorical nominal", "Residence, Family Type, School", "Nominal"], ["Categorical ordinal", "Education level, Income group, Grade", "Ordinal"], ["Binary (Yes/No)", "All Yes/No questions (0/1 coded)", "Nominal"], ["Continuous", "Age, Menarche age, Outdoor hours", "Scale"], ["Outcome (binary)", "Doctor_Confirmed (precocious puberty)", "Nominal"], ], [2600, 4200, 2400] ), pageBreak(), // ── SECTION 2: WORKFLOW ───────────────────────────────────── h1("2. STEP-BY-STEP SPSS ANALYSIS WORKFLOW"), para("Follow these steps in order. Do NOT skip steps."), para(""), makeTable( ["Step", "Task", "SPSS Procedure"], [ ["1", "Import data & assign labels", "File > Import Data > Excel, then run syntax"], ["2", "Descriptive statistics", "Analyze > Descriptive Statistics"], ["3", "Bivariate analysis", "Crosstabs (chi-square) + Independent t-test"], ["4", "Multivariable logistic regression", "Analyze > Regression > Binary Logistic"], ["5", "Correlation analysis", "Analyze > Correlate > Bivariate"], ["6", "School-wise comparison", "Analyze > Compare Means + Crosstabs"], ["7", "Save clean dataset", "File > Save As (.sav)"], ], [600, 4000, 4600] ), pageBreak(), // ── SECTION 3: IMPORT ──────────────────────────────────────── h1("3. STEP 1 – DATA IMPORT & LABELLING"), h3("3.1 Import from Excel"), bullet("Open SPSS. Go to: File > Import Data > Excel"), bullet("Select the file: Precocious_Puberty_DataEntry_SPSS_v11.xlsx"), bullet("Select sheet: Data Entry"), bullet("Set starting row to 3 (the header row). Tick: Read variable names from first row."), bullet("Click OK."), para(""), h3("3.2 Run the Complete Labelling Syntax"), para("Copy ALL text from the SPSS Syntax sheet in the Excel file and paste into SPSS Syntax Editor. Then click Run > All."), para("This will automatically assign:"), bullet("Variable labels for all 39 variables"), bullet("Value labels (e.g. 1=Urban, 2=Semiurban)"), bullet("Missing value codes (9 for categorical, 99 for continuous)"), para(""), h3("3.3 Exclude Non-Consented Participants"), ...syntaxBlock("Exclude non-consented", [ "* Exclude participants without consent or assent", "SELECT IF (Parent_Consent = 1 AND S_Assent = 1).", "EXECUTE.", ]), note_box("Always run this exclusion step BEFORE any analysis. Save the filtered dataset as a separate .sav file."), pageBreak(), // ── SECTION 4: DESCRIPTIVES ────────────────────────────────── h1("4. STEP 2 – DESCRIPTIVE STATISTICS"), h3("4.1 Frequency Tables (Categorical Variables)"), para("Use FREQUENCIES for all categorical and binary variables to get n (%) for each category."), ...syntaxBlock("Frequencies – Categorical variables", [ "FREQUENCIES VARIABLES=", " Residence Family_Type Father_Edu Mother_Edu Income", " Chronic_Illness Fam_Hx_Puberty Medications", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein Diet_TraditionalKerala", " Screen_Time_P Pesticide_Exp Doctor_Confirmed Parent_Consent", " S_Outdoor_Play S_Screen_Time S_Body_Changes S_Menarche S_Assent", " /ORDER=ANALYSIS.", ]), para(""), h3("4.2 Descriptive Statistics (Continuous Variables)"), para("Use DESCRIPTIVES for all continuous/numeric variables to get mean, SD, min, max."), ...syntaxBlock("Descriptives – Continuous variables", [ "DESCRIPTIVES VARIABLES=", " Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche", " /STATISTICS=MEAN STDDEV MIN MAX RANGE.", ]), para(""), h3("4.3 Grade and School Distribution"), ...syntaxBlock("Grade and School frequencies", [ "FREQUENCIES VARIABLES=Grade School_Name", " /BARCHART FREQ", " /ORDER=ANALYSIS.", ]), para(""), h3("4.4 Prevalence of Precocious Puberty (Primary Outcome)"), ...syntaxBlock("Prevalence estimate", [ "FREQUENCIES VARIABLES=Doctor_Confirmed", " /BARCHART PERCENT", " /ORDER=ANALYSIS.", "", "* Manual 95% CI formula:", "* p = proportion confirmed / total consented", "* 95% CI = p +/- 1.96 * SQRT(p*(1-p)/n)", ]), note_box("Report prevalence as: n (%) [95% CI: lower% – upper%]. Example: 23 (12.4%) [95% CI: 7.9% – 17.0%]"), pageBreak(), // ── SECTION 5: BIVARIATE ───────────────────────────────────── h1("5. STEP 3 – BIVARIATE ANALYSIS"), para("Test each potential risk factor against the outcome variable (Doctor_Confirmed)."), para("Use a significance threshold of p < 0.05 for bivariate screening. Include all variables with p < 0.25 in the logistic regression model."), para(""), h3("5.1 Chi-Square Tests (Categorical vs Categorical Outcome)"), para("Use for all binary and categorical risk factors vs Doctor_Confirmed."), ...syntaxBlock("Chi-square – All risk factors vs outcome", [ "CROSSTABS", " /TABLES=Residence Family_Type Fam_Hx_Puberty Medications", " Screen_Time_P S_Screen_Time Pesticide_Exp", " Diet_Homecooked Diet_FastFood Diet_Processed", " Diet_HighProtein Diet_TraditionalKerala", " S_Outdoor_Play S_Body_Changes S_Menarche", " BY Doctor_Confirmed", " /FORMAT=AVALUE TABLES", " /STATISTICS=CHISQ OR", " /CELLS=COUNT ROW COLUMN EXPECTED", " /COUNT ROUND CELL.", ]), para(""), h3("5.2 Chi-Square for Ordinal Categorical Variables"), ...syntaxBlock("Chi-square – Ordinal variables", [ "CROSSTABS", " /TABLES=Mother_Edu Father_Edu Income Grade", " BY Doctor_Confirmed", " /FORMAT=AVALUE TABLES", " /STATISTICS=CHISQ GAMMA", " /CELLS=COUNT ROW COLUMN", " /COUNT ROUND CELL.", ]), para(""), h3("5.3 School-wise vs Outcome (Residence comparison)"), ...syntaxBlock("School & Residence vs Outcome", [ "CROSSTABS", " /TABLES=Residence BY Doctor_Confirmed", " /STATISTICS=CHISQ OR", " /CELLS=COUNT ROW COLUMN.", ]), para(""), h3("5.4 Independent Samples T-Test (Continuous Variables)"), para("Compare mean values of continuous variables between Doctor_Confirmed=1 vs 0."), ...syntaxBlock("Independent t-test", [ "T-TEST GROUPS=Doctor_Confirmed(0 1)", " /MISSING=ANALYSIS", " /VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche", " /CRITERIA=CI(.95).", ]), note_box("Check Levene's Test for Equality of Variances. If p < 0.05, use the 'Equal variances not assumed' row."), para(""), h3("5.5 Interpreting Chi-Square Results"), makeTable( ["Statistic", "What It Tells You", "How to Report"], [ ["Chi-square (X2)", "Whether the distribution differs between groups", "X2(df) = value, p = value"], ["Odds Ratio (OR)", "How much more likely outcome is in exposed group", "OR = value (95% CI: lower – upper)"], ["p-value", "Statistical significance", "p < 0.05 = significant"], ["Fisher's Exact Test", "Use when any expected cell count < 5", "p = value (Fisher's Exact)"], ["Cramer's V / Phi", "Strength of association", "Small=0.1, Medium=0.3, Large=0.5"], ], [2200, 3800, 3200] ), pageBreak(), // ── SECTION 6: LOGISTIC REGRESSION ────────────────────────── h1("6. STEP 4 – BINARY LOGISTIC REGRESSION"), para("Purpose: Identify which risk factors independently predict precocious puberty after controlling for confounders."), para(""), h3("6.1 Variable Selection for the Model"), bullet("Include ALL variables with p < 0.25 from bivariate analysis (Step 3)."), bullet("Always include Age, Residence, and Family_Type regardless of p-value (biological/demographic confounders)."), bullet("Enter categorical variables with >2 categories as dummy variables."), para(""), h3("6.2 Running the Logistic Regression"), ...syntaxBlock("Binary Logistic Regression", [ "LOGISTIC REGRESSION VARIABLES Doctor_Confirmed", " /METHOD=ENTER Residence Family_Type Mother_Edu Income", " Fam_Hx_Puberty Screen_Time_P S_Screen_Time Pesticide_Exp", " Diet_FastFood Diet_Processed Diet_HighProtein", " Hrs_Outdoors S_Outdoor_Play Chronic_Illness Medications", " /CLASSPLOT", " /PRINT=GOODFIT ITER(1) CI(95)", " /CRITERIA=PIN(.05) POUT(.10) ITERATE(20) CUT(.5).", "", "* Note: Replace the variable list above with only those", "* variables significant at p<0.25 in your bivariate analysis.", ]), para(""), h3("6.3 Interpreting the Output"), makeTable( ["Output Table", "What to Look For"], [ ["Omnibus Tests", "Model Chi-square p < 0.05 = model is significant"], ["Hosmer & Lemeshow Test", "p > 0.05 = good model fit (you WANT non-significance here)"], ["Classification Table", "Overall correct classification % – aim for >70%"], ["Variables in the Equation","B = coefficient; Exp(B) = Odds Ratio; 95% CI for OR"], ["Nagelkerke R Square", "Pseudo-R2: proportion of variance explained (0-1)"], ], [3200, 6000] ), para(""), h3("6.4 Reading the OR Table"), bullet("Exp(B) = Odds Ratio (OR). This is the key number to report."), bullet("OR > 1 = increased risk of precocious puberty in that group."), bullet("OR < 1 = protective factor (lower risk)."), bullet("95% CI that does NOT cross 1.0 = statistically significant."), note_box("Example: Fam_Hx_Puberty OR = 3.21 (95% CI: 1.54–6.70), p = 0.002 means girls with family history are 3.2 times more likely to have precocious puberty."), pageBreak(), // ── SECTION 7: CORRELATION ─────────────────────────────────── h1("7. STEP 5 – CORRELATION ANALYSIS"), para("Purpose: Examine relationships between continuous variables."), para(""), h3("7.1 Pearson / Spearman Correlation"), para("Use Pearson for normally distributed continuous variables, Spearman for skewed or ordinal data."), ...syntaxBlock("Pearson Correlation", [ "CORRELATIONS", " /VARIABLES=Age Age_Menarche Age_Puberty_Signs", " S_Age_Body_Changes S_Age_Menarche Hrs_Outdoors", " /PRINT=TWOTAIL NOSIG FULL", " /MISSING=PAIRWISE.", "", "* For non-normal data use Spearman:", "NONPAR CORR", " /VARIABLES=Age Age_Menarche Age_Puberty_Signs", " /PRINT=SPEARMAN TWOTAIL NOSIG", " /MISSING=PAIRWISE.", ]), para(""), h3("7.2 Interpreting Correlation Coefficients"), makeTable( ["r value (absolute)", "Strength of Correlation"], [ ["0.00 – 0.19", "Very weak / negligible"], ["0.20 – 0.39", "Weak"], ["0.40 – 0.59", "Moderate"], ["0.60 – 0.79", "Strong"], ["0.80 – 1.00", "Very strong"], ], [3000, 6200] ), pageBreak(), // ── SECTION 8: SCHOOL COMPARISON ──────────────────────────── h1("8. STEP 6 – SCHOOL-WISE & SUBGROUP COMPARISONS"), h3("8.1 Prevalence by School"), ...syntaxBlock("Prevalence by school", [ "CROSSTABS", " /TABLES=School_Name BY Doctor_Confirmed", " /STATISTICS=CHISQ OR", " /CELLS=COUNT ROW COLUMN.", ]), para(""), h3("8.2 Urban vs Semiurban Comparison"), ...syntaxBlock("Urban vs Semiurban", [ "CROSSTABS", " /TABLES=Residence BY Doctor_Confirmed", " /STATISTICS=CHISQ OR", " /CELLS=COUNT ROW COLUMN.", "", "* Compare continuous vars across residence:", "T-TEST GROUPS=Residence(1 2)", " /VARIABLES=Age Age_Menarche Hrs_Outdoors", " /CRITERIA=CI(.95).", ]), para(""), h3("8.3 Grade-wise Analysis"), ...syntaxBlock("Grade-wise comparison", [ "ONEWAY Doctor_Confirmed BY Grade", " /STATISTICS DESCRIPTIVES", " /MISSING ANALYSIS.", "", "CROSSTABS", " /TABLES=Grade BY Doctor_Confirmed", " /STATISTICS=CHISQ", " /CELLS=COUNT ROW.", ]), pageBreak(), // ── SECTION 9: REPORTING ───────────────────────────────────── h1("9. REPORTING FORMAT & INTERPRETATION"), h3("9.1 How to Report Each Statistical Test"), makeTable( ["Test", "Reporting Format", "Example"], [ ["Prevalence", "n (%) [95% CI]", "23 (12.4%) [95% CI: 7.9%–17.0%]"], ["Chi-square", "X2(df) = value, p = value", "X2(1) = 6.34, p = 0.012"], ["Odds Ratio", "OR = value (95% CI: lower–upper)", "OR = 2.31 (95% CI: 1.12–4.76)"], ["t-test", "t(df) = value, p = value, mean diff (95% CI)", "t(198)=2.14, p=0.033, mean diff=0.8 (0.06–1.54)"], ["Logistic Regression","aOR = value (95% CI), p = value", "aOR = 3.21 (95% CI: 1.54–6.70), p = 0.002"], ["Correlation", "r = value, p = value", "r = 0.43, p < 0.001"], ["Mean (SD)", "Mean ± SD (range)", "Age: 12.3 ± 1.4 years (10–15)"], ], [2000, 3400, 3800] ), para(""), h3("9.2 Table 1 Template – Participant Characteristics"), makeTable( ["Variable", "Category", "n (%)", "p-value vs Outcome"], [ ["Age (years)", "Mean ± SD", "12.3 ± 1.4", "—"], ["Residence", "Urban", "n (%)", "Chi-sq p"], ["", "Semiurban", "n (%)", ""], ["Family Type", "Nuclear", "n (%)", "Chi-sq p"], ["", "Joint", "n (%)", ""], ["Mother Education","Illiterate–Post-graduate","n (%)", "Chi-sq p"], ["Monthly Income", "<10k – >50k", "n (%)", "Chi-sq p"], ["Family History", "Yes", "n (%)", "Chi-sq p"], ["Screen Time >2h", "Yes", "n (%)", "Chi-sq p"], ], [2400, 2400, 2200, 2200] ), para(""), h3("9.3 Significance Levels"), bullet("p < 0.05 = Statistically significant (use *)"), bullet("p < 0.01 = Highly significant (use **)"), bullet("p < 0.001 = Very highly significant (use ***)"), bullet("p >= 0.05 = Not statistically significant (NS)"), note_box("Always report exact p-values (e.g. p = 0.032) rather than just p < 0.05, except when p < 0.001 where you may write p < 0.001."), pageBreak(), // ── SECTION 10: VARIABLE REFERENCE ─────────────────────────── h1("10. VARIABLE REFERENCE TABLE"), para("Complete list of all SPSS variables, types, and recommended tests."), para(""), makeTable( ["SPSS Variable", "Question", "Type", "Recommended Test"], [ ["Age", "P2. Age of child", "Continuous", "Descriptives, t-test"], ["Grade", "P3. Grade/Standard", "Ordinal", "Crosstabs Chi-sq"], ["Residence", "P5. Urban/Semiurban", "Nominal", "Crosstabs Chi-sq, OR"], ["Family_Type", "P6. Nuclear/Joint", "Nominal", "Crosstabs Chi-sq, OR"], ["Father_Edu", "P7a. Father education", "Ordinal", "Crosstabs Chi-sq"], ["Mother_Edu", "P7b. Mother education", "Ordinal", "Crosstabs Chi-sq"], ["Income", "P8. Monthly income", "Ordinal", "Crosstabs Chi-sq"], ["Chronic_Illness", "P9. Chronic illness", "Nominal", "Crosstabs Chi-sq, OR"], ["Fam_Hx_Puberty", "P10. Family history", "Nominal", "Crosstabs Chi-sq, OR"], ["Medications", "P11. Medications/hormones", "Nominal", "Crosstabs Chi-sq, OR"], ["Age_Menarche", "P12. Age at menarche", "Continuous", "Descriptives, t-test, Correlation"], ["Diet_Homecooked", "P13a. Home-cooked diet", "Nominal", "Crosstabs Chi-sq, OR"], ["Diet_FastFood", "P13b. Fast food", "Nominal", "Crosstabs Chi-sq, OR"], ["Diet_Processed", "P13c. Processed food", "Nominal", "Crosstabs Chi-sq, OR"], ["Diet_HighProtein", "P13d. High protein", "Nominal", "Crosstabs Chi-sq, OR"], ["Diet_TraditionalKerala", "P13e. Kerala diet", "Nominal", "Crosstabs Chi-sq, OR"], ["Hrs_Outdoors", "P14. Outdoor hours/day", "Continuous", "Descriptives, t-test"], ["Screen_Time_P", "P15. Screen time (parent)", "Nominal", "Crosstabs Chi-sq, OR"], ["Pesticide_Exp", "P16. Pesticide exposure", "Nominal", "Crosstabs Chi-sq, OR"], ["Age_Puberty_Signs", "P17. Age puberty signs", "Continuous", "Descriptives, t-test, Correlation"], ["Doctor_Confirmed", "P18. OUTCOME: Early puberty", "Nominal", "PRIMARY OUTCOME"], ["S_Outdoor_Play", "S1. Outdoor play", "Nominal", "Crosstabs Chi-sq, OR"], ["S_Outdoor_Hrs", "S1. Outdoor hours/day", "Continuous", "Descriptives, t-test"], ["S_Screen_Time", "S2. Screen time (student)", "Nominal", "Crosstabs Chi-sq, OR"], ["S_Body_Changes", "S3. Body changes noticed", "Nominal", "Crosstabs Chi-sq, OR"], ["S_Age_Body_Changes", "S3. Age at body changes", "Continuous", "Descriptives, Correlation"], ["S_Menarche", "S4. First menstrual period", "Nominal", "Crosstabs Chi-sq, OR"], ["S_Age_Menarche", "S4. Age at first period", "Continuous", "Descriptives, t-test, Correlation"], ], [2200, 2600, 1600, 2800] ), para(""), pageBreak(), // ── FINAL PAGE: COMPLETE SYNTAX ────────────────────────────── h1("COMPLETE SPSS SYNTAX REFERENCE"), para("Copy the entire block below into SPSS Syntax Editor and click Run > All after importing your data."), ...syntaxBlock("COMPLETE ANALYSIS SYNTAX", [ "* === STEP 1: EXCLUDE NON-CONSENTED ===", "SELECT IF (Parent_Consent = 1 AND S_Assent = 1).", "EXECUTE.", "", "* === STEP 2: FREQUENCIES ===", "FREQUENCIES VARIABLES=Residence Family_Type Father_Edu Mother_Edu Income", " Chronic_Illness Fam_Hx_Puberty Medications Screen_Time_P Pesticide_Exp", " Doctor_Confirmed Diet_Homecooked Diet_FastFood Diet_Processed", " Diet_HighProtein Diet_TraditionalKerala S_Outdoor_Play", " S_Screen_Time S_Body_Changes S_Menarche /ORDER=ANALYSIS.", "", "* === STEP 3: DESCRIPTIVES ===", "DESCRIPTIVES VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " S_Outdoor_Hrs S_Age_Body_Changes S_Age_Menarche", " /STATISTICS=MEAN STDDEV MIN MAX.", "", "* === STEP 4: CHI-SQUARE ===", "CROSSTABS /TABLES=Residence Family_Type Fam_Hx_Puberty Medications", " Screen_Time_P S_Screen_Time Pesticide_Exp", " Diet_Homecooked Diet_FastFood Diet_Processed Diet_HighProtein", " Diet_TraditionalKerala S_Outdoor_Play S_Body_Changes S_Menarche", " BY Doctor_Confirmed", " /STATISTICS=CHISQ OR /CELLS=COUNT ROW COLUMN /COUNT ROUND CELL.", "", "* === STEP 5: T-TEST ===", "T-TEST GROUPS=Doctor_Confirmed(0 1)", " /VARIABLES=Age Age_Menarche Hrs_Outdoors Age_Puberty_Signs", " /CRITERIA=CI(.95).", "", "* === STEP 6: LOGISTIC REGRESSION ===", "LOGISTIC REGRESSION VARIABLES Doctor_Confirmed", " /METHOD=ENTER Residence Family_Type Mother_Edu Income", " Fam_Hx_Puberty Screen_Time_P Pesticide_Exp", " Diet_FastFood Diet_Processed Hrs_Outdoors", " /CLASSPLOT /PRINT=GOODFIT CI(95)", " /CRITERIA=PIN(.05) POUT(.10) ITERATE(20) CUT(.5).", "", "* === STEP 7: CORRELATION ===", "CORRELATIONS /VARIABLES=Age Age_Menarche Age_Puberty_Signs", " S_Age_Body_Changes S_Age_Menarche Hrs_Outdoors", " /PRINT=TWOTAIL NOSIG /MISSING=PAIRWISE.", "", "* === STEP 8: SAVE ===", "SAVE OUTFILE='/your/path/precocious_puberty_CLEAN.sav'", " /COMPRESSED.", ]), ]; // ── Build document ──────────────────────────────────────────── const doc = new Document({ creator: "Orris Research Assistant", title: "SPSS Analysis Guide – Precocious Puberty Study", description: "Complete statistical analysis guide for the precocious puberty prevalence study", styles: { paragraphStyles: [ { id: "Normal", name: "Normal", run: { font: "Arial", size: 20 }, paragraph: { spacing: { after: 80 } }, }, ], }, sections: [{ properties: { page: { margin: { top: 720, bottom: 720, left: 900, right: 900 }, }, }, headers: { default: new Header({ children: [new Paragraph({ border: { bottom: { style: BorderStyle.SINGLE, size: 4, color: MID_BLUE } }, children: [ new TextRun({ text: "SPSS Analysis Guide | Precocious Puberty Study | Manjeri & Anakkayam", font: "Arial", size: 16, color: "666666" }), ], })], }), }, footers: { default: new Footer({ children: [new Paragraph({ alignment: AlignmentType.CENTER, border: { top: { style: BorderStyle.SINGLE, size: 4, color: MID_BLUE } }, children: [ new TextRun({ text: "Page ", font: "Arial", size: 16, color: "666666" }), new PageNumber({ font: "Arial", size: 16, color: "666666" }), ], })], }), }, children, }], }); Packer.toBuffer(doc).then(buffer => { fs.writeFileSync('/tmp/SPSS_Analysis_Guide_Precocious_Puberty.docx', buffer); console.log('SAVED to /tmp'); }); JSEOF echo "Script written"

cd /home/daytona/workspace/spss-guide && npm install docx 2>&1 | tail -3 && node create_guide.js && echo "Node done"

which node && node --version && ls /home/daytona/workspace/spss-guide/node_modules/docx 2>/dev/null || echo "docx not installed"

ls /home/daytona/workspace/spss-guide/node_modules/docx/build/index.js && echo "docx ready"

ls /usr/local/lib/node_modules/ 2>/dev/null; ls /home/daytona/node_modules/docx 2>/dev/null || echo "not in home"; find /usr/local/share/nvm -name "docx" -type d 2>/dev/null | head -5

cd /home/daytona/workspace/spss-guide && node create_guide.js && echo "DONE"

~/spss-guide/create_guide.js

cd /home/daytona/workspace/spss-guide && node create_guide.js && echo "DONE"

# Validate and copy to workspace python3 /home/daytona/skills/docx/scripts/office/validate.py /tmp/SPSS_Analysis_Guide_Precocious_Puberty.docx && \ python3 -c "import shutil; shutil.copyfile('/tmp/SPSS_Analysis_Guide_Precocious_Puberty.docx', '/home/daytona/workspace/SPSS_Analysis_Guide_Precocious_Puberty.docx')" && \ python3 -c "import os; print('Size:', os.path.getsize('/home/daytona/workspace/SPSS_Analysis_Guide_Precocious_Puberty.docx'), 'bytes')"

python3 -c " import shutil, os, zipfile src = '/tmp/SPSS_Analysis_Guide_Precocious_Puberty.docx' dst = '/home/daytona/workspace/SPSS_Analysis_Guide_Precocious_Puberty.docx' # Quick validity check - a docx is a zip with zipfile.ZipFile(src, 'r') as z: names = z.namelist() print('ZIP contents:', names[:6]) assert 'word/document.xml' in names, 'Missing document.xml!' shutil.copyfile(src, dst) print('Copied. Size:', os.path.getsize(dst), 'bytes') "

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v11.xlsx') ws = wb["Data Entry"] # ── Print current DVs to see what needs fixing ─────────────────── print("Current DVs:") for dv in sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)): print(f" {str(dv.sqref):<22} {dv.type:<8} formula1={dv.formula1} formula2={getattr(dv,'formula2',None)}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.worksheet.datavalidation import DataValidation wb = openpyxl.load_workbook('/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v11.xlsx') ws = wb["Data Entry"] # ── Clear ALL DVs and rebuild with 99 allowed everywhere ───────── ws.data_validations.dataValidation.clear() def add_list(col, f1, err, title="Invalid"): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) def add_custom(col, formula, err, title="Invalid"): """Custom formula validation - allows both normal range AND 99""" dv = DataValidation(type="custom", formula1=formula, allow_blank=True, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) SCHOOLS = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' # ── LIST dropdowns (unchanged - these don't block 99) ──────────── add_list("C", '"10,11,12,13,14,15,99"', "Select age 10-15 or 99 if unknown.", "Invalid Age") # P2 Age - add 99 add_list("D", '"6,7,8,9"', "Select Standard 6, 7, 8 or 9.", "Invalid Grade") # P3 Grade add_list("E", SCHOOLS, "Select school from dropdown.", "Invalid School") # P4 School # F = auto-formula, no DV add_list("G", '"1,2,9"', "1=Nuclear 2=Joint 9=Not answered.", "Invalid") # P6 Family type add_list("H", '"1,2,3,4,5,9"', "1-5 for education level, 9=Not answered.", "Invalid") # P7a Father edu add_list("I", '"1,2,3,4,5,9"', "1-5 for education level, 9=Not answered.", "Invalid") # P7b Mother edu add_list("J", '"1,2,3,4,9"', "1-4 for income, 9=Not answered.", "Invalid") # P8 Income add_list("K", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P9 Chronic illness add_list("M", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P10 Family hx add_list("N", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P11 Medications # P12 Menarche age - custom: allow 0-30 OR 99 add_custom("P", "=OR(AND(P4>=0,P4<=30),P4=99)", "Enter age in years (0-30) or 99 if not yet occurred.", "Invalid Age") add_list("Q", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P13a add_list("R", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P13b add_list("S", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P13c add_list("T", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P13d add_list("U", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P13e # P14 Outdoor hours - custom: allow 0-24 OR 99 add_custom("V", "=OR(AND(V4>=0,V4<=24),V4=99)", "Enter hours (0-24) or 99 if unknown.", "Invalid Hours") add_list("W", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P15 Screen time add_list("X", '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P16 Pesticide # P17 Puberty signs age - custom: allow 4-20 OR 99 add_custom("Z", "=OR(AND(Z4>=4,Z4<=20),Z4=99)", "Enter age in years (4-20) or 99 if unknown.", "Invalid Age") add_list("AA",'"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P18 Doctor confirmed add_list("AC",'"1,0,9"', "1=Yes (consent given) 0=No 9=Not recorded.", "Invalid") # P19 Consent # Student Q add_list("AD",'"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # S1 Outdoor play # S1 Outdoor hours - custom: allow 0-24 OR 99 add_custom("AE", "=OR(AND(AE4>=0,AE4<=24),AE4=99)", "Enter hours (0-24) or 99 if unknown.", "Invalid Hours") add_list("AF",'"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # S2 Screen time add_list("AG",'"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # S3 Body changes # S3 Age body changes - custom: allow 0-20 OR 99 add_custom("AH", "=OR(AND(AH4>=0,AH4<=20),AH4=99)", "Enter age in years or 99 if unknown.", "Invalid Age") add_list("AI",'"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # S4 First period # S4 Age first period - custom: allow 8-20 OR 99 add_custom("AJ", "=OR(AND(AJ4>=8,AJ4<=20),AJ4=99)", "Enter age in years (8-20) or 99 if not applicable.", "Invalid Age") add_list("AL",'"1,0,9"', "1=Yes (assent given) 0=No 9=Not recorded.", "Invalid") # S6 Assent print(f"Total DVs applied: {len(ws.data_validations.dataValidation)}") print("\nFinal DV list:") for dv in sorted(ws.data_validations.dataValidation, key=lambda d: str(d.sqref)): print(f" {str(dv.sqref):<22} {dv.type:<8} {str(dv.formula1)[:55]}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_DataEntry_SPSS_v12.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_DataEntry_SPSS_v12.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") PYEOF

python3 << 'PYEOF' import openpyxl # Read the uploaded file and check what data exists src = '/home/daytona/workspace/attachments/59111884-76f4-4a64-b0df-bad01e31806f/Precocious_Puberty_DataEntry_SPSS_v11 (2) (2).xlsx' wb = openpyxl.load_workbook(src) ws = wb["Data Entry"] print("Sheets:", wb.sheetnames) print("Data rows with content:") count = 0 for row in range(4, 504): if ws[f"A{row}"].value and ws[f"C{row}"].value: # ID and some data count += 1 if count <= 10: # show first 10 vals = [] for col in ["A","C","D","E","F","G","H","I","J","K"]: vals.append(f"{col}={ws[f'{col}{row}'].value}") print(f" Row {row}: {' | '.join(vals)}") print(f"Total rows with data: {count}") PYEOF

python3 << 'PYEOF' import openpyxl, shutil from openpyxl.worksheet.datavalidation import DataValidation src = '/home/daytona/workspace/attachments/59111884-76f4-4a64-b0df-bad01e31806f/Precocious_Puberty_DataEntry_SPSS_v11 (2) (2).xlsx' wb = openpyxl.load_workbook(src) ws = wb["Data Entry"] # ── ONLY clear DVs - touch nothing else ────────────────────────── ws.data_validations.dataValidation.clear() def add_list(col, f1, err, title="Invalid"): dv = DataValidation(type="list", formula1=f1, allow_blank=True, showDropDown=False, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) def add_custom(col, formula, err, title="Invalid"): dv = DataValidation(type="custom", formula1=formula, allow_blank=True, showErrorMessage=True, errorTitle=title, error=err) dv.sqref = f"{col}4:{col}503" ws.add_data_validation(dv) SCHOOLS = '"1 - Benchmark International School, Manjeri,2 - GHSS Girls Manjeri,3 - GHSS Irumbuzhi"' # C – Age: dropdown includes 99 add_list("C", '"10,11,12,13,14,15,99"', "Select age 10-15, or 99 if unknown.", "Invalid Age") # D – Grade add_list("D", '"6,7,8,9"', "Select Standard 6, 7, 8 or 9.", "Invalid Grade") # E – School add_list("E", SCHOOLS, "Select school from dropdown.", "Invalid School") # F – Residence: auto-formula, no DV needed # G – Family Type + 9 add_list("G", '"1,2,9"', "1=Nuclear 2=Joint 9=Not answered.", "Invalid") # H,I – Education + 9 add_list("H", '"1,2,3,4,5,9"', "1-5 for education level, 9=Not answered.", "Invalid") add_list("I", '"1,2,3,4,5,9"', "1-5 for education level, 9=Not answered.", "Invalid") # J – Income + 9 add_list("J", '"1,2,3,4,9"', "1-4 for income, 9=Not answered.", "Invalid") # All Yes/No cols + 9 for col in ["K","M","N","Q","R","S","T","U","W","X","AA","AC","AD","AF","AG","AI","AL"]: add_list(col, '"1,0,9"', "1=Yes 0=No 9=Not answered.", "Invalid") # P – Menarche age: 0-30 OR 99 add_custom("P", "=OR(AND(P4>=0,P4<=30),P4=99)", "Enter age in years (0-30) or 99 if not yet occurred.", "Invalid Age") # V – Outdoor hours: 0-24 OR 99 add_custom("V", "=OR(AND(V4>=0,V4<=24),V4=99)", "Enter hours (0-24) or 99 if unknown.", "Invalid Hours") # Z – Puberty signs age: 4-20 OR 99 add_custom("Z", "=OR(AND(Z4>=4,Z4<=20),Z4=99)", "Enter age (4-20) or 99 if unknown.", "Invalid Age") # AE – Student outdoor hours: 0-24 OR 99 add_custom("AE", "=OR(AND(AE4>=0,AE4<=24),AE4=99)", "Enter hours (0-24) or 99 if unknown.", "Invalid Hours") # AH – Student body changes age: 0-20 OR 99 add_custom("AH", "=OR(AND(AH4>=0,AH4<=20),AH4=99)", "Enter age or 99 if unknown.", "Invalid Age") # AJ – Student first period age: 8-20 OR 99 add_custom("AJ", "=OR(AND(AJ4>=8,AJ4<=20),AJ4=99)", "Enter age (8-20) or 99 if not applicable.", "Invalid Age") print(f"DVs applied: {len(ws.data_validations.dataValidation)}") # ── Verify data is still intact ─────────────────────────────────── count = sum(1 for r in range(4,504) if ws[f"A{r}"].value and ws[f"C{r}"].value) print(f"Data rows still present: {count}") # ── Save ───────────────────────────────────────────────────────── tmp = "/tmp/Precocious_Puberty_WITH_DATA_fixed.xlsx" dst = "/home/daytona/workspace/Precocious_Puberty_WITH_DATA_fixed.xlsx" wb.save(tmp) shutil.copyfile(tmp, dst) import os print(f"\nSAVED: {dst} | {os.path.getsize(dst):,} bytes") # ── Final data check ────────────────────────────────────────────── wb2 = openpyxl.load_workbook(dst) ws2 = wb2["Data Entry"] print("\nFirst 5 data rows (confirming data intact):") for row in range(4, 9): vals = {col: ws2[f"{col}{row}"].value for col in ["A","C","D","E","G","H","I","J"]} print(f" Row {row}: {vals}") PYEOF