cd "/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/" && python3 -c " import openpyxl wb = openpyxl.load_workbook('PP_Study_427_Completed (2).xlsx', read_only=True) print('Sheets:', wb.sheetnames) for sheet in wb.sheetnames: ws = wb[sheet] print(f'\nSheet: {sheet} | Rows: {ws.max_row} | Cols: {ws.max_column}') # Print first 5 rows for i, row in enumerate(ws.iter_rows(values_only=True)): if i >= 5: break print(row) "

cd "/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/" && python3 -c " import openpyxl wb = openpyxl.load_workbook('PP_Study_427_Completed (2).xlsx', read_only=True) # Read SPSS Syntax sheet fully ws = wb['SPSS Syntax'] print('=== SPSS SYNTAX SHEET ===') for i, row in enumerate(ws.iter_rows(values_only=True)): if row[0] is not None: print(f'Row {i+1}: {row[0]}') if i > 80: print('...truncated...') break "

cd "/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/" && python3 -c " import openpyxl wb = openpyxl.load_workbook('PP_Study_427_Completed (2).xlsx', read_only=True) ws = wb['Data Entry'] rows = list(ws.iter_rows(values_only=True)) print('Row 1:', rows[0]) print('Row 2:', rows[1]) print('Row 3 (headers):', rows[2]) print('Row 4 (data):', rows[3]) print('Row 5 (data):', rows[4]) print('Total rows:', len(rows)) print('Total cols:', len(rows[2])) "

python3 << 'EOF' import openpyxl import csv import re wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/PP_Study_427_Completed (2).xlsx', read_only=True) ws = wb['Data Entry'] rows = list(ws.iter_rows(values_only=True)) # SPSS-safe short variable names (max 8 chars for SPSS 21, all unique, no spaces/special chars) # Mapping: col index -> SPSS variable name spss_vars = [ 'ID', # 0 'Child_Name', # 1 - P1 child name (optional, string) 'Age', # 2 - P2 age 'Grade', # 3 - P3 grade 'School', # 4 - P4 school name (string) 'Residence', # 5 - P5 residence (1=Urban 2=Semiurban) - formula, compute from school 'Fam_Type', # 6 - P6 family type 'Dad_Edu', # 7 - P7a father education 'Mom_Edu', # 8 - P7b mother education 'Income', # 9 - P8 income 'Chr_Ill', # 10 - P9 chronic illness 'Ill_Detail', # 11 - P9 illness details (string) 'Fam_Pub', # 12 - P10 family hx puberty 'Medic', # 13 - P11 medications 'Med_Detail', # 14 - P11 medication details (string) 'Age_Menar', # 15 - P12 age at menarche 'Diet_Home', # 16 - P13a homecooked 'Diet_Fast', # 17 - P13b fast food 'Diet_Proc', # 18 - P13c processed 'Diet_Prot', # 19 - P13d high protein 'Diet_Trad', # 20 - P13e traditional Kerala 'Hrs_Out', # 21 - P14 hours outdoors 'Scr_Time_P', # 22 - P15 screen time parent 'Pestic', # 23 - P16 pesticide exposure 'Pest_Det', # 24 - P16 pesticide details (string) 'Age_Pub', # 25 - P17 age puberty signs 'Doc_Conf', # 26 - P18 doctor confirmed 'Doc_Det', # 27 - P18 doctor details (string) 'P_Consent', # 28 - P19 parent consent 'S_Out_Play', # 29 - S1 outdoor play 'S_Out_Hrs', # 30 - S1 outdoor hours 'S_Scr_Time', # 31 - S2 screen time student 'S_Body_Chg', # 32 - S3 body changes 'S_Age_Body', # 33 - S3 age body changes 'S_Menarche', # 34 - S4 first period 'S_Age_Men', # 35 - S4 age first period 'S_Health', # 36 - S5 health notes (string) 'S_Assent', # 37 - S6 student assent 'Height', # 38 - height cm 'Weight', # 39 - weight kg 'BMI', # 40 - BMI (auto) ] # String columns (will be kept as text, others forced to numeric) string_cols = {1, 4, 11, 14, 24, 27, 36} # For Residence (col 5): formula - derive from School name (col 4) # Irumbuzhi = 2 (Semiurban), others = 1 (Urban) # For BMI (col 40): formula - compute from Height/Weight def compute_residence(school_val): if school_val is None or school_val == '': return '' school_str = str(school_val) if 'Irumbuzhi' in school_str or 'irumbuzhi' in school_str: return 2 return 1 def compute_bmi(height, weight): try: h = float(height) w = float(weight) if h > 0 and w > 0: return round(w / (h/100)**2, 1) except: pass return '' import os os.makedirs('/home/daytona/workspace/pp-study-spss/', exist_ok=True) output_path = '/home/daytona/workspace/pp-study-spss/PP_Study_SPSS_Ready.csv' data_rows = rows[3:] # skip rows 0,1,2 (titles + headers) written = 0 skipped = 0 with open(output_path, 'w', newline='', encoding='utf-8') as f: writer = csv.writer(f) # Write single clean header row writer.writerow(spss_vars) for row in data_rows: # Skip completely empty rows if all(v is None or v == '' for v in row): skipped += 1 continue # Skip rows where ID is missing or not a number if row[0] is None or row[0] == '': skipped += 1 continue clean_row = [] for i, val in enumerate(row): if i == 5: # Residence - compute from school name clean_row.append(compute_residence(row[4])) elif i == 40: # BMI - compute from height/weight h = row[38] if len(row) > 38 else None w = row[39] if len(row) > 39 else None clean_row.append(compute_bmi(h, w)) elif i in string_cols: # String columns - clean up newlines, keep as text if val is None: clean_row.append('') else: clean_row.append(str(val).replace('\n', ' ').strip()) else: # Numeric columns if val is None or val == '': clean_row.append('') elif isinstance(val, str): # Try to extract number from formula or text if val.startswith('='): clean_row.append('') # formula with no resolved value else: # Try to parse as number try: clean_row.append(float(val)) except: clean_row.append(val.replace('\n', ' ').strip()) else: clean_row.append(val) writer.writerow(clean_row) written += 1 print(f'Done! Written: {written} data rows, Skipped: {skipped} empty rows') print(f'Output: {output_path}') # Verify first few rows with open(output_path, 'r') as f: lines = f.readlines() print(f'Total lines in CSV: {len(lines)}') print('Header:', lines[0][:200]) print('Row 1:', lines[1][:200]) print('Row 2:', lines[2][:200]) EOF

python3 << 'EOF' import openpyxl import csv import os wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/PP_Study_427_Completed (2).xlsx', read_only=True) ws = wb['Data Entry'] rows = list(ws.iter_rows(values_only=True)) # First, let's see all unique school names to map them correctly schools = set() for row in rows[3:]: if row[0] is not None and row[4] is not None: schools.add(str(row[4])) print("Unique school values:") for s in sorted(schools): print(repr(s)) EOF

python3 << 'EOF' import openpyxl import csv wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/PP_Study_427_Completed (2).xlsx', read_only=True) ws = wb['Data Entry'] rows = list(ws.iter_rows(values_only=True)) # Age columns that should use 99 for missing (col index) # Age_Menar=15, Age_Pub=25, S_Age_Body=33, S_Age_Men=35, S_Out_Hrs=30 age_cols = {15, 25, 33, 35} # Numeric cols that should use 99 for missing (not binary 0/1) # Also S_Out_Hrs (col 30) and Hrs_Out (col 21) - use 99 numeric_99_cols = {15, 21, 25, 30, 33, 35, 38, 39, 40} # age + hours + height/weight/BMI # Binary cols (0/1) - leave blank as blank (or 9 for missing, but user said 99 for age only) # We'll fill age-type columns with 99, leave others blank # School name -> number mapping (extract leading digit) def school_to_num(val): if val is None or val == '': return 9 # unknown school s = str(val).strip() # Extract leading number if s.startswith('1'): return 1 elif s.startswith('2'): return 2 elif s.startswith('3'): return 3 return 9 def compute_residence(school_val): if school_val is None or school_val == '': return 9 s = str(school_val) if 'Irumbuzhi' in s or 'irumbuzhi' in s: return 2 return 1 def compute_bmi(height, weight): try: h = float(height) w = float(weight) if h > 0 and w > 0: return round(w / (h/100)**2, 1) except: pass return 99 spss_vars = [ 'ID','Child_Name','Age','Grade','School','Residence','Fam_Type', 'Dad_Edu','Mom_Edu','Income','Chr_Ill','Ill_Detail','Fam_Pub', 'Medic','Med_Detail','Age_Menar','Diet_Home','Diet_Fast','Diet_Proc', 'Diet_Prot','Diet_Trad','Hrs_Out','Scr_Time_P','Pestic','Pest_Det', 'Age_Pub','Doc_Conf','Doc_Det','P_Consent','S_Out_Play','S_Out_Hrs', 'S_Scr_Time','S_Body_Chg','S_Age_Body','S_Menarche','S_Age_Men', 'S_Health','S_Assent','Height','Weight','BMI' ] string_cols = {1, 4, 11, 14, 24, 27, 36} # col 4 (School) now numeric, remove it string_cols = {1, 11, 14, 24, 27, 36} data_rows = rows[3:] output_path = '/home/daytona/workspace/pp-study-spss/PP_Study_SPSS_Ready.csv' written = 0 with open(output_path, 'w', newline='', encoding='utf-8') as f: writer = csv.writer(f) writer.writerow(spss_vars) for row in data_rows: if all(v is None or v == '' for v in row): continue if row[0] is None or row[0] == '': continue clean_row = [] for i, val in enumerate(row): # --- Special computed columns --- if i == 4: # School: number only clean_row.append(school_to_num(val)) continue if i == 5: # Residence: compute from school name clean_row.append(compute_residence(row[4])) continue if i == 40: # BMI: compute from height/weight h = row[38] if len(row) > 38 else None w = row[39] if len(row) > 39 else None clean_row.append(compute_bmi(h, w)) continue # --- String columns --- if i in string_cols: if val is None: clean_row.append('') else: clean_row.append(str(val).replace('\n', ' ').strip()) continue # --- Numeric columns --- if val is None or val == '': # Fill age/numeric cols with 99, binary cols leave blank if i in numeric_99_cols: clean_row.append(99) else: clean_row.append('') continue if isinstance(val, str): if val.startswith('='): # Unresolved formula if i in numeric_99_cols: clean_row.append(99) else: clean_row.append('') else: try: clean_row.append(float(val)) except: clean_row.append(val.replace('\n', ' ').strip()) continue clean_row.append(val) writer.writerow(clean_row) written += 1 print(f"Written: {written} rows") # Quick verification with open(output_path) as f: lines = f.readlines() print(f"Total lines: {len(lines)}") print("Header:", lines[0]) print("Row1:", lines[1]) print("Row2:", lines[2]) # Check for any remaining long school names school_vals = set() for line in lines[1:]: parts = line.split(',') if len(parts) > 4: school_vals.add(parts[4]) print("\nUnique School values in CSV:", school_vals) # Check age column sample print("\nAge_Menar (col 15) sample:", [l.split(',')[15] for l in lines[1:6]]) print("Age_Pub (col 25) sample:", [l.split(',')[25] for l in lines[1:6]]) EOF

python3 << 'EOF' import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/0a20a44c-dc26-48cb-97fd-2ac26319ee6a/PP_Study_427_Completed (2).xlsx', read_only=True) ws = wb['SPSS Syntax'] print("=== FULL SPSS SYNTAX SHEET ===") for i, row in enumerate(ws.iter_rows(values_only=True)): if row[0] is not None: print(f"{i+1}: {row[0]}") EOF

~/pp-study-spss/PP_Study_427_Syntax.sps