cd /home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/ && python3 -c " import openpyxl wb = openpyxl.load_workbook('223 Govt Reserved Final.xlsx') print('Sheets:', wb.sheetnames) for sh in wb.sheetnames: ws = wb[sh] print(f'\n--- Sheet: {sh} (rows={ws.max_row}, cols={ws.max_column}) ---') for row in ws.iter_rows(min_row=1, max_row=30, values_only=True): print(row) "

python3 -c " import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx') # Check Sheet1 headers fully ws = wb['Sheet1'] print('Sheet1 headers (row 1):') for col in range(1, ws.max_column+1): print(f' Col {col}: {ws.cell(1,col).value}') print(f'\nTotal rows in Sheet1: {ws.max_row}') print(f'Total cols in Sheet1: {ws.max_column}') # Check SURGERY DONE sheet fully ws2 = wb['SURGERY DONE'] print('\nSURGERY DONE - all data rows:') for row in ws2.iter_rows(min_row=1, max_row=ws2.max_row, values_only=True): if any(v is not None for v in row): print(row) "

python3 -c " import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx') # Look at Sheet1 - find the procedure codes that match surgery done codes ws1 = wb['Sheet1'] # Get all procedure codes in Sheet1 (col 5 = 'Procedure Code') sheet1_codes = {} for row in ws1.iter_rows(min_row=2, max_row=ws1.max_row, values_only=True): code = row[4] # col E (index 4) if code: sheet1_codes[code] = row[0] # also store col A value print('Codes in Sheet1 (col E):') for code in list(sheet1_codes.keys())[:20]: print(f' {code}') print(f'Total: {len(sheet1_codes)}') # Check which surgery codes match surgery_codes = ['SL015A','SL002A','SL009A','SU077A','SU024A','SU040A', 'SG017A','SG017B','SG039A','SG039B','SE020B','SE020A', 'SE045AMH','SO010C','SO010B','SO010E','SO010A','SO053A/B/C','SO073A','SO018A'] print('\nMatches in Sheet1 col E:') for c in surgery_codes: match = c in sheet1_codes print(f' {c}: {\"FOUND\" if match else \"NOT FOUND\"}') "

python3 -c " import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side wb = openpyxl.load_workbook('/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx') ws1 = wb['Sheet1'] # Check existing header row formatting header_cell = ws1.cell(1, 1) print('Header font:', header_cell.font) print('Header fill:', header_cell.fill) print('Header alignment:', header_cell.alignment) # Check col widths print('\nColumn dimensions:') for col_letter, col_dim in list(ws1.column_dimensions.items())[:5]: print(f' {col_letter}: width={col_dim.width}') # Check a few data cells data_cell = ws1.cell(2, 1) print('\nData cell font:', data_cell.font) print('Data cell fill:', data_cell.fill) # Look at row 1 col 27 (last col) formatting last_header = ws1.cell(1, 27) print('\nLast header cell:', last_header.value) print('Last header font:', last_header.font) print('Last header fill:', last_header.fill.fgColor.rgb if last_header.fill else 'None') "

python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter import copy src = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx' dst = '/home/daytona/workspace/223_Govt_Reserved_Updated.xlsx' wb = openpyxl.load_workbook(src) # --- Build lookup dict from SURGERY DONE sheet --- ws_surgery = wb['SURGERY DONE'] surgery_data = {} # code -> (year1, year2, year3) for row in ws_surgery.iter_rows(min_row=2, max_row=ws_surgery.max_row, values_only=True): if row[1] is not None: # Code column code = str(row[1]).strip() surgery_data[code] = (row[3], row[4], row[5]) # Year1, Year2, Year3 print("Surgery data loaded:", len(surgery_data), "codes") print(surgery_data) # --- Add columns to Sheet1 --- ws1 = wb['Sheet1'] new_col_28 = 28 new_col_29 = 29 new_col_30 = 30 # Define header style (match existing bold headers) header_font = Font(name='Calibri', bold=True, size=11) header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) # Light blue fill for new header columns header_fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') # Write headers for col, label in [(new_col_28, 'Year 1 (2023)\nSurgery Count'), (new_col_29, 'Year 2 (2024)\nSurgery Count'), (new_col_30, 'Year 3 (2025)\nSurgery Count')]: cell = ws1.cell(row=1, column=col) cell.value = label cell.font = header_font cell.alignment = header_align cell.fill = header_fill # Set column widths for col in [new_col_28, new_col_29, new_col_30]: col_letter = get_column_letter(col) ws1.column_dimensions[col_letter].width = 16 # Write data rows - match on col E (index 4, column 5) which is Procedure Code data_font = Font(name='Calibri', size=11) data_align = Alignment(horizontal='center', vertical='center') matched = 0 not_matched = [] for row_num in range(2, ws1.max_row + 1): proc_code_cell = ws1.cell(row=row_num, column=5) proc_code = proc_code_cell.value if proc_code is None: continue code_str = str(proc_code).strip() counts = surgery_data.get(code_str) for i, col in enumerate([new_col_28, new_col_29, new_col_30]): cell = ws1.cell(row=row_num, column=col) if counts: cell.value = counts[i] else: cell.value = '-' cell.font = data_font cell.alignment = data_align if counts: matched += 1 else: not_matched.append(code_str) print(f"Matched rows: {matched}") print(f"Not matched (showing unique): {list(set(not_matched))[:10]}") wb.save(dst) print(f"\nSaved to: {dst}") EOF

mkdir -p /home/daytona/workspace/output && python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter import shutil, os src = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx' dst = '/home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx' # Copy first then open shutil.copy2(src, dst) # Open with keep_vba=False to avoid issues wb = openpyxl.load_workbook(dst) # --- Build lookup dict from SURGERY DONE sheet --- ws_surgery = wb['SURGERY DONE'] surgery_data = {} for row in ws_surgery.iter_rows(min_row=2, max_row=ws_surgery.max_row, values_only=True): if row[1] is not None: code = str(row[1]).strip() surgery_data[code] = (row[3], row[4], row[5]) print(f"Loaded {len(surgery_data)} surgery codes") # --- Modify Sheet1 --- ws1 = wb['Sheet1'] new_cols = {28: 'Year 1 (2023)\nSurgery Count', 29: 'Year 2 (2024)\nSurgery Count', 30: 'Year 3 (2025)\nSurgery Count'} header_font = Font(name='Calibri', bold=True, size=11) header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) header_fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') data_font = Font(name='Calibri', size=11) data_align = Alignment(horizontal='center', vertical='center') # Write headers for col, label in new_cols.items(): cell = ws1.cell(row=1, column=col) cell.value = label cell.font = header_font cell.alignment = header_align cell.fill = header_fill ws1.column_dimensions[get_column_letter(col)].width = 16 # Write data matched = 0 for row_num in range(2, ws1.max_row + 1): proc_code = ws1.cell(row=row_num, column=5).value if proc_code is None: continue code_str = str(proc_code).strip() counts = surgery_data.get(code_str) for i, col in enumerate([28, 29, 30]): cell = ws1.cell(row=row_num, column=col) cell.value = counts[i] if counts else '-' cell.font = data_font cell.alignment = data_align if counts: matched += 1 print(f"Matched {matched} rows with surgery counts") wb.save(dst) print(f"Saved: {dst}") EOF

rm -f /home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx && python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter import shutil src = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx' dst = '/home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx' # Open original (keep_links=False avoids broken external link errors on save) wb = openpyxl.load_workbook(src, keep_links=False) # --- Build lookup dict from SURGERY DONE sheet --- ws_surgery = wb['SURGERY DONE'] surgery_data = {} for row in ws_surgery.iter_rows(min_row=2, max_row=ws_surgery.max_row, values_only=True): if row[1] is not None: code = str(row[1]).strip() surgery_data[code] = (row[3], row[4], row[5]) print(f"Loaded {len(surgery_data)} surgery codes") # --- Modify Sheet1 --- ws1 = wb['Sheet1'] new_cols = {28: 'Year 1 (2023)\nSurgery Count', 29: 'Year 2 (2024)\nSurgery Count', 30: 'Year 3 (2025)\nSurgery Count'} header_font = Font(name='Calibri', bold=True, size=11) header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) header_fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') data_font = Font(name='Calibri', size=11) data_align = Alignment(horizontal='center', vertical='center') # Write headers for col, label in new_cols.items(): cell = ws1.cell(row=1, column=col) cell.value = label cell.font = header_font cell.alignment = header_align cell.fill = header_fill ws1.column_dimensions[get_column_letter(col)].width = 16 # Write data matched = 0 for row_num in range(2, ws1.max_row + 1): proc_code = ws1.cell(row=row_num, column=5).value if proc_code is None: continue code_str = str(proc_code).strip() counts = surgery_data.get(code_str) for i, col in enumerate([28, 29, 30]): cell = ws1.cell(row=row_num, column=col) cell.value = counts[i] if counts else '-' cell.font = data_font cell.alignment = data_align if counts: matched += 1 print(f"Matched {matched} rows with surgery counts") wb.save(dst) print(f"Saved: {dst}") EOF

mkdir -p /home/daytona/workspace/output && ls /home/daytona/workspace/output/

python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter src = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx' dst = '/home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx' # Open original wb = openpyxl.load_workbook(src, keep_links=False) # --- Build lookup dict from SURGERY DONE sheet --- ws_surgery = wb['SURGERY DONE'] surgery_data = {} for row in ws_surgery.iter_rows(min_row=2, max_row=ws_surgery.max_row, values_only=True): if row[1] is not None: code = str(row[1]).strip() surgery_data[code] = (row[3], row[4], row[5]) print(f"Loaded {len(surgery_data)} surgery codes") # --- Modify Sheet1 --- ws1 = wb['Sheet1'] header_font = Font(name='Calibri', bold=True, size=11) header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) header_fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') data_font = Font(name='Calibri', size=11) data_align = Alignment(horizontal='center', vertical='center') # Write headers at columns 28, 29, 30 for col, label in [(28, 'Year 1 (2023)\nSurgery Count'), (29, 'Year 2 (2024)\nSurgery Count'), (30, 'Year 3 (2025)\nSurgery Count')]: cell = ws1.cell(row=1, column=col) cell.value = label cell.font = header_font cell.alignment = header_align cell.fill = header_fill ws1.column_dimensions[get_column_letter(col)].width = 16 # Write data rows matched = 0 for row_num in range(2, ws1.max_row + 1): proc_code = ws1.cell(row=row_num, column=5).value if proc_code is None: continue code_str = str(proc_code).strip() counts = surgery_data.get(code_str) for i, col in enumerate([28, 29, 30]): cell = ws1.cell(row=row_num, column=col) cell.value = counts[i] if counts else '-' cell.font = data_font cell.alignment = data_align if counts: matched += 1 print(f"Matched {matched} rows with surgery counts") # Save to a fresh file path (ensure dir exists) import os os.makedirs(os.path.dirname(dst), exist_ok=True) wb.save(dst) print(f"Saved: {dst}") import os print(f"File size: {os.path.getsize(dst)} bytes") EOF

python3 << 'EOF' import openpyxl from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter from io import BytesIO import os src = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/223 Govt Reserved Final.xlsx' dst = '/home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx' wb = openpyxl.load_workbook(src, keep_links=False) # --- Build lookup dict from SURGERY DONE sheet --- ws_surgery = wb['SURGERY DONE'] surgery_data = {} for row in ws_surgery.iter_rows(min_row=2, max_row=ws_surgery.max_row, values_only=True): if row[1] is not None: code = str(row[1]).strip() surgery_data[code] = (row[3], row[4], row[5]) # --- Modify Sheet1 --- ws1 = wb['Sheet1'] header_font = Font(name='Calibri', bold=True, size=11) header_align = Alignment(horizontal='center', vertical='center', wrap_text=True) header_fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') data_font = Font(name='Calibri', size=11) data_align = Alignment(horizontal='center', vertical='center') for col, label in [(28, 'Year 1 (2023)\nSurgery Count'), (29, 'Year 2 (2024)\nSurgery Count'), (30, 'Year 3 (2025)\nSurgery Count')]: cell = ws1.cell(row=1, column=col) cell.value = label cell.font = header_font cell.alignment = header_align cell.fill = header_fill ws1.column_dimensions[get_column_letter(col)].width = 16 matched = 0 for row_num in range(2, ws1.max_row + 1): proc_code = ws1.cell(row=row_num, column=5).value if proc_code is None: continue code_str = str(proc_code).strip() counts = surgery_data.get(code_str) for i, col in enumerate([28, 29, 30]): cell = ws1.cell(row=row_num, column=col) cell.value = counts[i] if counts else '-' cell.font = data_font cell.alignment = data_align if counts: matched += 1 print(f"Matched {matched} rows") # Save to BytesIO then write to file manually buf = BytesIO() wb.save(buf) buf.seek(0) data = buf.read() os.makedirs('/home/daytona/workspace/output', exist_ok=True) with open(dst, 'wb') as f: f.write(data) print(f"Written {len(data)} bytes to {dst}") EOF

python3 -c " import openpyxl wb = openpyxl.load_workbook('/home/daytona/workspace/output/223_Govt_Reserved_Updated.xlsx') ws1 = wb['Sheet1'] print('Sheet1 last 3 headers:') for col in [28, 29, 30]: print(f' Col {col}: {ws1.cell(1, col).value}') print('\nSample rows with surgery counts (rows that have data):') count = 0 for row_num in range(2, ws1.max_row+1): v28 = ws1.cell(row_num, 28).value if v28 != '-' and v28 is not None: code = ws1.cell(row_num, 5).value name = ws1.cell(row_num, 8).value v29 = ws1.cell(row_num, 29).value v30 = ws1.cell(row_num, 30).value print(f' Row {row_num}: Code={code}, Name={str(name)[:30]}, Y1={v28}, Y2={v29}, Y3={v30}') count += 1 print(f'\nTotal rows with surgery counts: {count}') print(f'Total rows in Sheet1: {ws1.max_row - 1}') "

python3 << 'EOF' import openpyxl base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # Read the 223 Govt Reserved list wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') print("=== 223 Govt Reserved sheets:", wb223.sheetnames) ws = wb223['Sheet1'] print(f"Rows: {ws.max_row}, Cols: {ws.max_column}") print("Headers:") for c in range(1, ws.max_column+1): print(f" Col{c}: {ws.cell(1,c).value}") # Sample first 10 data rows - cols A,B,E,H (Code, Specialty, ProcCode, ProcName) print("\nSample rows (Col1=ProcCode, Col2=Specialty, Col5=ProcCode, Col8=ProcedureName):") for r in range(2, 12): print(f" {ws.cell(r,1).value} | {ws.cell(r,2).value} | {ws.cell(r,5).value} | {ws.cell(r,8).value}") EOF

python3 << 'EOF' import openpyxl base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' for fname in ['OT JAN 23- DEC 23.xlsx', 'OT JAN 24-DEC 24.xlsx', 'OT JAN 25-DEC 25.xlsx']: print(f"\n{'='*60}") print(f"FILE: {fname}") wb = openpyxl.load_workbook(base + fname) print(f"Sheets: {wb.sheetnames}") ws = wb.active print(f"Rows: {ws.max_row}, Cols: {ws.max_column}") print("Row 1 (headers):") for c in range(1, min(ws.max_column+1, 20)): print(f" Col{c}: {ws.cell(1,c).value}") print("Row 2-6 sample:") for r in range(2, 7): row_vals = [ws.cell(r,c).value for c in range(1, min(ws.max_column+1, 15))] print(f" {row_vals}") EOF

python3 << 'EOF' import openpyxl from collections import Counter base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # Get all unique surgery names per year from OT files for year, fname in [('2023', 'OT JAN 23- DEC 23.xlsx'), ('2024', 'OT JAN 24-DEC 24.xlsx'), ('2025', 'OT JAN 25-DEC 25.xlsx')]: wb = openpyxl.load_workbook(base + fname) ws = wb.active names = [] for r in range(2, ws.max_row+1): name = ws.cell(r, 10).value # SURGERY_NAME col 10 if name: names.append(str(name).strip()) counter = Counter(names) print(f"\n=== {year} - Total records: {len(names)}, Unique names: {len(counter)} ===") print("Top 50 by count:") for name, cnt in counter.most_common(50): print(f" {cnt:4d} {name}") EOF

python3 << 'EOF' import openpyxl from collections import defaultdict, Counter import re base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # ── 1. Load the 223 unique procedure list (from Unique Codes sheet + Sheet1 for names) ── wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') ws1 = wb223['Sheet1'] # Build: code -> (specialty, package_name, procedure_name) procedures = {} # code -> dict seen_codes = set() for r in range(2, ws1.max_row + 1): code = ws1.cell(r, 5).value specialty = ws1.cell(r, 2).value pkg_name = ws1.cell(r, 7).value proc_name = ws1.cell(r, 8).value if code and code not in seen_codes: seen_codes.add(code) procedures[code] = { 'specialty': str(specialty).strip() if specialty else '', 'package': str(pkg_name).strip() if pkg_name else '', 'proc_name': str(proc_name).strip() if proc_name else '', } print(f"Total unique procedure codes: {len(procedures)}") # ── 2. Build normalized name lookup for matching ── def normalize(s): s = str(s).lower().strip() # Remove codes like (S5D2.46) etc s = re.sub(r'\(s[\d\w\.]+\)', '', s) s = re.sub(r's[\d]+[a-z][\d]+\.[\d]+', '', s) s = re.sub(r'[^a-z0-9\s]', ' ', s) s = re.sub(r'\s+', ' ', s).strip() return s # ── 3. Build manual mapping rules (OT name keywords -> procedure code) ── # Based on visual inspection of OT names vs 223 list # Format: list of (keywords_that_must_all_appear, code) keyword_map = [ # Ophthalmology (['cataract', 'phaco'], 'SE020A'), (['cataract', 'sics'], 'SE020B'), (['cataract'], 'SE020B'), # generic cataract -> SICS (most common) (['dacryocyst', 'dct'], 'SE045AMH'), (['dacryocyst', 'dcr'], 'SE045AMH'), (['dacryocyst'], 'SE045AMH'), (['pterygium'], 'SE013A'), (['squint'], 'SE006A'), (['glaucoma'], 'SE003A'), (['trabeculect'], 'SE003A'), (['evisceration'], 'SE026A'), (['enucleation'], 'SE027A'), (['vitreo', 'retina'], 'SE030A'), (['amniotic', 'membrane'], 'SE013A'), # ENT (['tympanoplasty'], 'SL002A'), (['mastoidect', 'modified', 'radical'], 'SL004A'), (['mastoidect', 'radical'], 'SL004A'), (['mastoidect'], 'SL003A'), (['septoplasty'], 'SL009A'), (['fess'], 'SL011A'), (['endoscopic', 'sinus'], 'SL011A'), (['adenoidect'], 'SL015A'), (['tonsillect', 'adenoidect'], 'SL015A'), (['tonsillect'], 'SL016A'), (['myringotomy'], 'SL005A'), (['canuloplasty'], 'SL002A'), (['stapedect'], 'SL007A'), (['parotidect'], 'SL020A'), (['laryngoscopy'], 'SL022A'), (['microlaryngoscopy'], 'SL022A'), # General Surgery (['appendectomy', 'open'], 'SG017A'), (['appendectomy', 'lap'], 'SG017B'), (['lap', 'appendectomy'], 'SG017B'), (['lap', 'append'], 'SG017B'), (['open', 'append'], 'SG017A'), (['appendectomy'], 'SG017A'), (['cholecystectomy', 'lap'], 'SG039A'), (['lap', 'cholecystectomy'], 'SG039A'), (['cholecystectomy', 'open'], 'SG039B'), (['cholecystectomy'], 'SG039A'), (['hernioplasty', 'inguinal'], 'SG049A'), (['inguinal', 'hernia'], 'SG049A'), (['lap', 'hernia'], 'SG049B'), (['hernioplasty'], 'SG049A'), (['umbilical', 'hernia'], 'SG053A'), (['incision', 'drainage', 'abscess'], 'SG001A'), (['fistulectomy'], 'SG009A'), (['fistula', 'in', 'ano'], 'SG009A'), (['hemorrhoidect'], 'SG006A'), (['haemorrhoidect'], 'SG006A'), (['varicose', 'vein'], 'SG085A'), (['fibroadenoma'], 'SG074A'), (['breast', 'lump'], 'SG074A'), (['mastectomy', 'simple'], 'SG075A'), (['mastectomy', 'radical'], 'SG076A'), (['mrm'], 'SG076A'), (['mastectomy'], 'SG075A'), (['thyroidectomy', 'hemi'], 'SG070A'), (['hemi', 'thyroid'], 'SG070A'), (['thyroidectomy', 'total'], 'SG070B'), (['total', 'thyroid'], 'SG070B'), (['thyroidectomy'], 'SG070B'), (['parathyroid', 'adenoma'], 'SG072A'), (['exploratory', 'laparotomy'], 'SG022A'), (['exploratory', 'laprotomy'], 'SG022A'), (['laprotomy'], 'SG022A'), (['colostomy'], 'SG027A'), (['lipoma', 'excision'], 'SC066A'), (['soft', 'tissue', 'excision'], 'SC066A'), (['debridement'], 'SP002A'), (['wound', 'debridement'], 'SP002A'), (['excision', 'sinus', 'tract'], 'SG009A'), # OBG (['vaginal', 'hysterectomy'], 'SO010C'), (['abdominal', 'hysterectomy'], 'SO010B'), (['tah'], 'SO010B'), (['laparoscopic', 'hysterectomy'], 'SO010E'), (['tlh'], 'SO010E'), (['caesarean', 'hysterectomy'], 'SO010A'), (['lscs'], 'SO003A'), (['caesarean', 'section'], 'SO003A'), (['cs'], None), # skip (['d&c'], 'SO018A'), (['dilatation', 'curettage'], 'SO018A'), (['endometrial', 'biopsy'], 'SO073A'), (['cervical', 'biopsy'], 'SO073A'), (['eb', 'cb'], 'SO073A'), (['endometrial', 'cervical', 'biopsy'], 'SO073A'), (['diagnostic', 'hysteroscopy'], 'SO073A'), (['hysteroscopy', 'biopsy'], 'SO073A'), (['tubal', 'ligation'], 'SO014A'), (['lap', 'ovarian', 'drilling'], 'SO020A'), (['ovarian', 'cystectomy'], 'SO021A'), (['ovarian', 'drilling'], 'SO020A'), (['diagnostic', 'laparoscopy'], 'SO071A'), (['diagnostic', 'laproscopy'], 'SO071A'), (['dignostic', 'laproscopy'], 'SO071A'), (['mtp'], 'SO053A'), (['termination', 'pregnancy'], 'SO053A'), # Urology (['turp', 'single'], 'SU077A'), (['turp'], 'SU077A'), (['transurethral', 'resection', 'prostate'], 'SU077A'), (['pcnl'], 'SU094B'), (['pyelolithotomy'], 'SU024A'), (['cystolithotomy'], 'SU040A'), (['urs', 'dj'], 'SU036A'), (['ursl', 'dj'], 'SU036A'), (['dj', 'stent'], 'SU064B'), (['urethral', 'dilatation'], 'SU065A'), (['urethroplasty'], 'SU036A'), (['urethrotomy'], 'SU036AMH'), (['orchidectomy', 'high'], 'SU086A'), (['high', 'orchidectomy'], 'SU086A'), (['orchidectomy', 'bilateral'], 'SU087A'), (['bilateral', 'orchidectomy'], 'SU087A'), (['orchidectomy'], 'SU086A'), (['varicocele'], 'SU089A'), (['cystoscopy'], 'SU040A'), (['nephrolithotomy'], 'SU005A'), (['circumcision'], 'SU034AMH'), (['paraphimosis'], 'SU034AMH'), # Surgical Oncology (['soft', 'tissue', 'sarcoma'], 'SC066A'), (['benign', 'soft', 'tissue'], 'SC066A'), # Ortho (some in 223 list) (['interlocking', 'nail'], 'S14O1'), (['orif'], 'S14O1'), (['hip', 'replacement', 'indian'], 'S5D2A'), (['hip', 'replacement', 'hybrid'], 'S5D2A'), (['tkr', 'indian'], 'S5D3A'), (['tkr', 'imported'], 'S5D3B'), (['tkr'], 'S5D3A'), (['hemiarthroplasty', 'bipolar'], 'S5D2B'), (['bipolar', 'hemiarthroplasty'], 'S5D2B'), (['spinal', 'fusion'], 'S10I5'), (['laminectomy'], 'S10I4'), (['core', 'decompression'], 'S5D1'), # Plastic (['ear', 'pinna', 'reconstruct'], 'SP004A'), (['resuturing'], 'SP009A'), ] # ── 4. Load all OT data ── ot_data = [] # list of (year, surgery_name) for year, fname in [('2023', 'OT JAN 23- DEC 23.xlsx'), ('2024', 'OT JAN 24-DEC 24.xlsx'), ('2025', 'OT JAN 25-DEC 25.xlsx')]: wb = openpyxl.load_workbook(base + fname) ws = wb.active for r in range(2, ws.max_row + 1): name = ws.cell(r, 10).value if name: ot_data.append((year, str(name).strip())) print(f"Total OT records: {len(ot_data)}") # ── 5. Match each OT record to a procedure code ── def match_code(name): norm = normalize(name) words = set(norm.split()) for keywords, code in keyword_map: if all(kw in norm for kw in keywords): return code return None # Count per code per year counts = defaultdict(lambda: defaultdict(int)) # code -> year -> count unmatched = Counter() for year, name in ot_data: code = match_code(name) if code: counts[code][year] += 1 else: unmatched[name] += 1 print(f"\nMatched codes: {len(counts)}") print(f"\nTop 30 UNMATCHED names:") for name, cnt in unmatched.most_common(30): print(f" {cnt:4d} {name}") EOF

python3 << 'EOF' import openpyxl from collections import defaultdict, Counter import re base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # Load 223 list - get all unique codes with package name wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') ws1 = wb223['Sheet1'] # Use Unique Codes sheet to get the 223 codes ws_unique = wb223['Unique Codes'] unique_codes = [] for r in range(2, ws_unique.max_row + 1): c = ws_unique.cell(r, 2).value if c: unique_codes.append(str(c).strip()) print(f"Unique codes count: {len(unique_codes)}") # Map each unique code to specialty + package_name + proc_name from Sheet1 code_info = {} for r in range(2, ws1.max_row + 1): code = ws1.cell(r, 5).value if code and str(code).strip() in unique_codes and str(code).strip() not in code_info: code_info[str(code).strip()] = { 'specialty': str(ws1.cell(r, 2).value or '').strip(), 'package': str(ws1.cell(r, 7).value or '').strip(), 'proc_name': str(ws1.cell(r, 8).value or '').strip(), } print(f"Codes with info: {len(code_info)}") # Print all 223 - specialty, code, package_name print("\nAll 223 procedures:") for code in unique_codes: info = code_info.get(code, {}) print(f" {info.get('specialty','?'):35s} | {code:15s} | {info.get('package','?')}") EOF

python3 << 'EOF' import openpyxl from collections import defaultdict import re base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # ── Load 223 unique code list with info ── wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') ws_unique = wb223['Unique Codes'] ws1 = wb223['Sheet1'] unique_codes_ordered = [] for r in range(2, ws_unique.max_row+1): c = ws_unique.cell(r, 2).value if c: unique_codes_ordered.append(str(c).strip()) code_info = {} for r in range(2, ws1.max_row+1): code = ws1.cell(r,5).value if code and str(code).strip() in unique_codes_ordered and str(code).strip() not in code_info: code_info[str(code).strip()] = { 'specialty': str(ws1.cell(r,2).value or '').strip(), 'package': str(ws1.cell(r,7).value or '').strip(), 'proc_name': str(ws1.cell(r,8).value or '').strip(), } # ── Comprehensive keyword matching ── def normalize(s): s = str(s).lower().strip() s = re.sub(r'\(s[\d]+[a-z\d\.]+\)', '', s) s = re.sub(r's\d+[a-z]\d+\.\d+', '', s) s = re.sub(r'[^a-z0-9\s\/\+\-]', ' ', s) s = re.sub(r'\s+', ' ', s).strip() return s def has(norm, *kws): return all(kw in norm for kw in kws) def match_to_code(name): n = normalize(name) # OPHTHALMOLOGY if has(n,'cataract','phaco') or has(n,'phacoemulsif'): return 'SE020A' if has(n,'cataract','sics'): return 'SE020B' if has(n,'cataract'): return 'SE020B' if has(n,'dacryocyst') or has(n,'dct') or has(n,'dcr') and has(n,'surg'): return 'SE045AMH' if has(n,'dcr surg') or name.strip() in ['DCR Surgery','DCT Surgery']: return 'SE045AMH' if 'DCT Surgery' == name.strip() or 'DCR Surgery' == name.strip(): return 'SE045AMH' if has(n,'pterygium'): return 'SE014A' if has(n,'squint'): return 'SE006A' # not in 223 list if has(n,'amniotic','membrane'): return 'SE053AMH' if has(n,'intravitreal') or has(n,'anti-vegf') or has(n,'anti vegf'): return None if has(n,'corneal','graft'): return 'SE012A' # ENT if has(n,'tympanoplasty'): return 'SL002A' if has(n,'myringoplasty'): return 'SL056AMH' if has(n,'modified','radical','mastoidect'): return None # SL004 not in 223 if has(n,'radical','mastoidect'): return None if has(n,'mastoidect'): return None if has(n,'septoplasty'): return 'SL009A' if has(n,'functional','septo') or has(n,'septorhinoplasty') or has(n,'septorhino'): return 'SL008A' if has(n,'fess') or (has(n,'endoscopic','sinus') and not has(n,'endoscopic','sinus','surgery')): return 'SL013A' if has(n,'endoscopic sinus surgery') or has(n,'endoscopic','sinus','surg'): return 'SL013A' if 'ENDOSCOPIC SINUS SURGERY' in name.upper() or 'FESS' in name.upper(): return 'SL013A' if has(n,'adenoidect') and has(n,'tonsil'): return 'SL015A' if has(n,'adenoidect'): return 'SL015A' if has(n,'tonsillect'): return 'SL016A' if has(n,'myringotomy'): return 'SL005A' if has(n,'turbinect') or has(n,'turbinate','excis'): return 'SL040AMH' if has(n,'polypect') and (has(n,'nasal') or has(n,'nose') or has(n,'ent')): return 'SL048AMH' if has(n,'ethmoidect'): return 'SL049AMH' if has(n,'antrostomy'): return 'SL038AMH' if has(n,'rhinotomy'): return 'SL039AMH' if has(n,'stapedot') or has(n,'stapedect'): return 'SL007A' if has(n,'peritonsillar') or has(n,'quinsy'): return 'SL017A' if has(n,'incision','drainage') and has(n,'ent'): return 'SL017A' if has(n,'submandibular','salivary') or has(n,'parotidect'): return 'SL022A' if has(n,'thyroglossal') or has(n,'branchial','cyst'): return 'SL018E' if has(n,'angiofibroma','nose'): return 'SL051AMH' if has(n,'canuloplasty'): return 'SL002A' # ENT reconstruction -> tympanoplasty related # GENERAL SURGERY if has(n,'lap','appendect') or has(n,'lap','appendic') or has(n,'laparoscopic','append'): return 'SG017B' if has(n,'open','appendect') or has(n,'open','appendic'): return 'SG017A' if has(n,'appendect') or has(n,'appendic'): return 'SG017A' if has(n,'lap','cholecystect') or has(n,'laparoscopic','cholecyst'): return 'SG039A' if has(n,'open','cholecystect') or has(n,'cholecystect','open'): return 'SG039B' if has(n,'cholecystect'): return 'SG039A' if has(n,'lap','hernia') or has(n,'e-tep') or has(n,'etep') or has(n,'tapp'): return 'SG050B' if has(n,'inguinal','hernia') or has(n,'hernioplasty','inguinal'): return 'SG050A' if has(n,'groin','hernia'): return 'SG050A' if has(n,'herniotomy'): return 'SG050A' if has(n,'hernioplasty'): return 'SG050A' if has(n,'umbilical','hernia'): return 'SG051A' if has(n,'ventral','hernia'): return 'SG051A' if has(n,'incisional','hernia'): return 'SG052A' if has(n,'recurrent','hernia'): return 'SG052A' if has(n,'haemorrhoid') or has(n,'hemorrhoid'): return 'SG032A' if has(n,'fistula','ano') or has(n,'fistulect') or has(n,'fistula in ano'): return 'SG127AMH' if has(n,'high','fistula'): return 'SG127BMH' if has(n,'pilonidal'): return 'SG033A' if has(n,'fissure','ano'): return 'SG031A' if has(n,'hydrocele') or has(n,'hydrocelectomy'): return 'SG056A' if has(n,'incision','drainage','abscess') or has(n,'i and d') or has(n,'i&d'): return 'SG084A' if has(n,'debridement') and not has(n,'wound'): return 'SG086A' if has(n,'wound','debridement') or has(n,'major','debridement'): return 'SG086A' if has(n,'varicose','vein') or has(n,'ligation','stripping'): return 'SG085A' if has(n,'fibroadenoma'): return 'SG074A' if has(n,'breast','lump'): return 'SG074A' if has(n,'simple','mastect'): return 'SG075A' if has(n,'radical','mastect') or has(n,'mrm') or has(n,'breast','conserv'): return None if has(n,'mastect'): return 'SG075A' if has(n,'hemi','thyroid') or has(n,'hemithyroid') or has(n,'thyroidect','hemi'): return 'SG070A' if has(n,'total','thyroid') or has(n,'thyroidect','total') or has(n,'thyroidect'): return 'SG070B' if has(n,'parathyroid'): return 'SS022A' if has(n,'splenect') or has(n,'spleenect'): return 'SG042A' if has(n,'exploratory','laparot') or has(n,'exploratory','laprotomy'): return None if has(n,'colostomy'): return 'SG027A' if has(n,'rectal','prolapse'): return 'SG026A' if has(n,'lipoma','excis') or has(n,'excis','lipoma'): return 'SG085A' if has(n,'lipoma'): return 'SG085A' if has(n,'sebaceous','cyst'): return 'SG054A' if has(n,'epididymal','cyst') or has(n,'epididymal','nodule'): return 'SG057A' if has(n,'soft','tissue','sarcoma') or has(n,'sarcoma','excis'): return 'SC066A' if has(n,'benign','soft','tissue') or has(n,'soft','tissue','tumour'): return 'SC066A' if has(n,'scrotal','swelling') or has(n,'scrotal','hematoma'): return 'SG133AMH' if has(n,'bakers','cyst'): return 'SG138AMH' if has(n,'mole','excis') or has(n,'excis','mole'): return 'SG0113A' if has(n,'ingrowing','toe') or has(n,'ingrown','toe'): return 'SG0115A' if has(n,'circumcis'): return 'SG104A' if has(n,'biopsy','punch') or has(n,'punch','biopsy'): return 'SG096A' if has(n,'excision','cyst') or has(n,'cyst','excis'): return 'SG054B' if has(n,'cervical','swelling'): return 'SG143AMH' if has(n,'av','fistula'): return None # vascular - not in 223 if has(n,'excision','duct','wall') or has(n,'sinus','tract'): return 'SG034A' if has(n,'brachial','sinus'): return 'SG0110A' if has(n,'mesentric','cyst'): return 'SG0112A' # OBG if has(n,'lscs') or (has(n,'caesarean','section') and not has(n,'hysterect')): return 'SO057A' if has(n,'caesarean','hysterect'): return 'SO011A' if has(n,'vaginal','hysterect'): return 'SO010C' if has(n,'abdominal','hysterect') or has(n,'tah') or (has(n,'total','abdominal','hysterect')): return 'SO010B' if has(n,'laparoscopic','hysterect') or has(n,'tlh'): return 'SO010E' if has(n,'hysterect'): return 'SO010B' if has(n,'d&c') or has(n,'dilatation','curettage') or has(n,'dilatation','curretage'): return 'SO018A' if has(n,'dilatation','evacuation') or has(n,'d&e') or has(n,'dilation','evacuation'): return 'SO019A' if has(n,'endometrial','biopsy') or has(n,'cervical','biopsy') or has(n,'eb','cb') or has(n,'endometrial','cervical','biopsy'): return 'SO073A' if has(n,'hysteroscopy','biopsy') or has(n,'diagnostic','hysteroscopy') and has(n,'biopsy'): return 'SO073A' if has(n,'endometrial') and has(n,'biopsy'): return 'SO073A' if has(n,'tubal','ligation') or has(n,'tube','ligation'): return 'SO064A' if has(n,'lap','ovarian','drill') or has(n,'ovarian','drill'): return 'SO078AMH' if has(n,'laparoscopy','ovarian') or has(n,'laparoscopic','ovarian'): return 'SO078AMH' if has(n,'diagnostic','laparoscop') or has(n,'diagnost','laparoscop') or has(n,'laproscopy'): return 'SO039A' if has(n,'dignostic','laproscopy'): return 'SO039A' if has(n,'hysteroscopy') or has(n,'hysteroscop'): return 'SO016A' if has(n,'mtp') or has(n,'medical','termination'): return 'SO053A' if has(n,'anterior','posterior','colpo') or has(n,'a&p','repair') or has(n,'ap','repair'): return 'SO030A' if has(n,'bartholin') or has(n,'vulv','hematoma') or has(n,'vulval','hematoma'): return 'SO059A' if has(n,'vault','prolapse'): return 'SO081AMH' if has(n,'cone','biopsy'): return 'SO072A' if has(n,'lletz'): return 'SO026A' if has(n,'electro','cauterisat') or has(n,'cryo','surg') or has(n,'cauteris'): return 'SO044A' if has(n,'colpotomy'): return 'SO038A' if has(n,'cystocele') or has(n,'anterior','repair'): return 'SO042A' if has(n,'sling','surg') or has(n,'prolapse','repair'): return 'SO013A' if has(n,'secondary','sutur') or has(n,'episiotomy'): return 'SO056A' if has(n,'manual','removal','placenta'): return 'SO055A' if has(n,'high','risk','delivery') or has(n,'instrumental','delivery'): return 'SO054B' if has(n,'vacuum','delivery') or has(n,'forceps','delivery'): return 'SO075A' if has(n,'reversal','sterilisation') or has(n,'tuboplasty'): return 'SO065A' if has(n,'ovarian','cystect'): return 'SO023A' if has(n,'pyometra'): return 'SO020A' if has(n,'hymenotomy') or has(n,'hymenect'): return 'SO029A' # UROLOGY if has(n,'turp') or (has(n,'transurethral','resect','prostate')): return 'SU077A' if has(n,'prostatect'): return 'SU077A' if has(n,'pcnl') or has(n,'rirs') or has(n,'percutaneous','nephrolit'): return None # SU094B not in 223 if has(n,'pyelolithotomy'): return 'SU024A' if has(n,'cystolithotomy'): return 'SU040A' if has(n,'cystoscopy'): return 'SU040A' if has(n,'ursl') or (has(n,'urs') and has(n,'dj')): return None # not in 223 if has(n,'dj','stent') or has(n,'dj stent'): return None # SU064B not in 223 if has(n,'nephrolithotomy'): return 'SU005A' if has(n,'urethroplasty'): return None if has(n,'urethrotomy') or has(n,'optical','urethrotomy'): return 'SU036AMH' if has(n,'meatotomy') or has(n,'meatoplasty'): return 'SU065A' if has(n,'orchidect') or has(n,'orchiect'): if has(n,'bilateral'): return 'SU087A' return 'SU086A' if has(n,'varicocele'): return 'SU089A' if has(n,'circumcis'): return 'SG104A' if has(n,'paraphimosis') or has(n,'reduction','paraphimosis'): return 'SU034AMH' if has(n,'urethral','caruncle'): return 'SU074A' if has(n,'ureteric','stone') or has(n,'renal','colic'): return 'SU094A' if has(n,'acute','retention') or has(n,'retention','urine'): return 'SU064A' if has(n,'hematuria') or has(n,'haematuria'): return 'SU073A' # ORTHO (only those IN 223) if has(n,'total','hip','replacement') or has(n,'thr') or has(n,'hip','replacement'): if has(n,'imported'): return 'SB038C' if has(n,'hybrid'): return 'SB038B' return 'SB038A' if has(n,'tkr') or has(n,'total','knee','replacement') or has(n,'knee','replacement'): if has(n,'imported'): return 'SB039A' return 'SB039A' if has(n,'hemiarthroplasty') or has(n,'bipolar','hemiarthroplasty'): return 'SB038A' if has(n,'arthroscop','meniscect') or has(n,'menisect') or has(n,'meniscect'): return 'SB036A' if has(n,'acl','repair') or has(n,'acl','reconstr') or has(n,'cruciate','ligament'): return 'SB049A' if has(n,'arthroscop','acl'): return 'SB049A' if has(n,'arthroscop'): return 'SB036A' if has(n,'traction'): return 'SB002A' if has(n,'bursa','excis') or has(n,'excis','bursa'): return 'SB065A' if has(n,'ctev') or has(n,'club','foot'): return 'SB063A' if has(n,'correction','club') or has(n,'clubfoot'): return 'SB063A' if has(n,'clavicle','fracture') or has(n,'fracture','clavicle'): return 'SB076AMH' if has(n,'fracture','clavicle') or has(n,'undisplaced','fracture'): return 'SB076AMH' # PLASTIC SURGERY if has(n,'resuturing') or has(n,'wound','resutur'): return 'SP009A' if has(n,'npwt'): return 'SP008A' if has(n,'ear','pinna'): return 'SP004A' # ORAL MAXILLO if has(n,'orif','mandib') or has(n,'mandibul','fracture'): return 'SL033B' if has(n,'fracture','maxilla') or has(n,'orif','maxilla'): return 'SL033B' if has(n,'impacted','tooth') or has(n,'tooth','extraction'): return 'SM001A' if has(n,'apicoect'): return 'SM008A' # NEUROSURGERY if has(n,'craniotomy'): return None # not in 223 list if has(n,'laminect'): return None if has(n,'spinal','fusion') or has(n,'spinal','fixat'): return 'SN068BMH' if has(n,'corpectomy'): return 'SN032A' # CARDIOLOGY if has(n,'cabg') or has(n,'bypass','graft') or has(n,'valve','replac') or has(n,'valve','repair'): return None if has(n,'rotablation'): return 'MC036AMH' # PEDIATRIC SURGERY if has(n,'cleft','lip') or has(n,'cleft','palate'): return 'SS001A' if has(n,'ankyloglossia') or has(n,'tongue','tie'): return 'SS002A' if has(n,'hernia','hydrocele') or (has(n,'hernia') and has(n,'hydrocele')): return 'SS015A' if has(n,'cystic','hygroma'): return 'SS058AMH' # GENERAL MEDICINE if has(n,'malaria'): return 'MG003A' if has(n,'dengue'): return 'MG004A' if has(n,'typhoid') or has(n,'enteric','fever'): return 'MG006A' if has(n,'leptospiro'): return 'MG008A' if has(n,'renal','colic'): return 'MG044A' if has(n,'febrile') or has(n,'fever','illness'): return 'MG001A' if has(n,'anemia') or has(n,'anaemia') and has(n,'severe'): return 'MG064A' if has(n,'ercp'): return None return None # ── Load all OT data and count ── counts = defaultdict(lambda: defaultdict(int)) # code -> year -> count unmatched_names = defaultdict(int) for year, fname in [('2023', 'OT JAN 23- DEC 23.xlsx'), ('2024', 'OT JAN 24-DEC 24.xlsx'), ('2025', 'OT JAN 25-DEC 25.xlsx')]: wb = openpyxl.load_workbook(base + fname) ws = wb.active for r in range(2, ws.max_row+1): name = ws.cell(r,10).value if name: name = str(name).strip() code = match_to_code(name) if code: counts[code][year] += 1 else: unmatched_names[name] += 1 # ── Print result ordered by unique codes list ── print(f"\n{'SPECIALTY':<38} | {'CODE':<15} | {'PACKAGE NAME':<55} | {'2023':>6} | {'2024':>6} | {'2025':>6}") print('-'*145) for code in unique_codes_ordered: info = code_info.get(code, {}) y23 = counts[code].get('2023', 0) y24 = counts[code].get('2024', 0) y25 = counts[code].get('2025', 0) if y23 > 0 or y24 > 0 or y25 > 0: pkg = info.get('package','')[:53] print(f"{info.get('specialty',''):<38} | {code:<15} | {pkg:<55} | {y23:>6} | {y24:>6} | {y25:>6}") print(f"\n\nTop 20 unmatched OT names:") from collections import Counter un = Counter(unmatched_names) for n, c in un.most_common(20): print(f" {c:4d} {n}") EOF

python3 << 'EOF' import openpyxl base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' for fname in ['ICD- 2023 ONLY DIAGNOSIS.xlsx', 'ICD- 2024 ONLY DIAGNOSIS.xlsx', 'ICD 2025 ONLY DIAGNOSIS.xlsx']: print(f"\n{'='*70}") print(f"FILE: {fname}") wb = openpyxl.load_workbook(base + fname) print(f"Sheets: {wb.sheetnames}") ws = wb.active print(f"Rows: {ws.max_row}, Cols: {ws.max_column}") print("Headers (Row 1):") for c in range(1, ws.max_column+1): print(f" Col{c}: {ws.cell(1,c).value}") print("Sample rows 2-8:") for r in range(2, 9): row = [ws.cell(r,c).value for c in range(1, ws.max_column+1)] print(f" {row}") EOF

python3 -c " import openpyxl base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' wb = openpyxl.load_workbook(base + 'ICD- 2023 ONLY DIAGNOSIS.xlsx') ws = wb.active print('Rows:', ws.max_row, 'Cols:', ws.max_column) print('Headers:') for c in range(1, ws.max_column+1): print(' Col'+str(c)+':', ws.cell(1,c).value) print('Row2-6:') for r in range(2,7): print([ws.cell(r,c).value for c in range(1,ws.max_column+1)]) "

python3 -c " import openpyxl from collections import Counter base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # Check 2024 and 2025 structure + sample for fname in ['ICD- 2024 ONLY DIAGNOSIS.xlsx', 'ICD 2025 ONLY DIAGNOSIS.xlsx']: wb = openpyxl.load_workbook(base + fname) ws = wb.active print(fname, '-> rows:', ws.max_row) print('Sample ICD codes & diagnosis (first 10 unique):') seen = set() for r in range(2, ws.max_row+1): icd = ws.cell(r,18).value diag = ws.cell(r,19).value cpt = ws.cell(r,20).value proc = ws.cell(r,21).value key = str(icd) if key not in seen: seen.add(key) print(f' ICD={icd} | Diag={diag} | CPT={cpt} | Proc={proc}') if len(seen) >= 15: break print() "

python3 << 'PYEOF' import openpyxl from collections import defaultdict, Counter import re base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # ── 1. ICD CODE → 223 Govt Reserved Procedure mapping ── # Based on standard ICD-10 to procedure relationship # Each entry: (icd_prefix_or_exact, procedure_code, package_name) icd_map = [ # OPHTHALMOLOGY # Cataract ('H25', 'SE020B', 'Cataract surgery (SICS)'), ('H26', 'SE020B', 'Cataract surgery (SICS)'), ('H28', 'SE020B', 'Cataract surgery (SICS)'), # Pterygium ('H11.0', 'SE014A', 'Pterygium + Conjunctival Autograft'), ('H11.01', 'SE014A', 'Pterygium + Conjunctival Autograft'), ('H11.02', 'SE014A', 'Pterygium + Conjunctival Autograft'), # DCT/DCR ('H04.0', 'SE045AMH', 'Dacryocystectomy'), ('H04.1', 'SE045AMH', 'Dacryocystectomy'), ('H04.3', 'SE045AMH', 'Dacryocystectomy'), ('H04.4', 'SE045AMH', 'Dacryocystectomy'), ('H04.5', 'SE045AMH', 'Dacryocystectomy'), # Corneal graft ('H17', 'SE012A', 'Corneal Grafting'), ('H18', 'SE012A', 'Corneal Grafting'), # Squint/Strabismus (not in 223 - skip) # Glaucoma ('H40', 'SE003A', 'Glaucoma surgery'), # Retinal ('H33', 'SE030A', 'Vitreo-retinal surgery'), ('H35.3', 'SE030A', 'Vitreo-retinal surgery'), # Amniotic membrane ('H16.0', 'SE053AMH', 'Amniotic Membrane Graft'), ('H16.1', 'SE053AMH', 'Amniotic Membrane Graft'), # ENT # Tympanoplasty / Chronic otitis ('H65', 'SL002A', 'Tympanoplasty'), ('H66', 'SL002A', 'Tympanoplasty'), ('H72', 'SL002A', 'Tympanoplasty'), ('H74', 'SL002A', 'Tympanoplasty'), # Myringoplasty ('H73', 'SL056AMH', 'Myringoplasty'), # Adenoids/tonsils ('J35.0', 'SL016A', 'Tonsillectomy'), ('J35.1', 'SL015A', 'Adenoidectomy'), ('J35.2', 'SL015A', 'Adenoidectomy'), ('J35.3', 'SL015A', 'Adenoidectomy'), ('J35.8', 'SL015A', 'Adenoidectomy'), ('J35.9', 'SL015A', 'Adenoidectomy'), # Nasal polyp / FESS ('J33', 'SL013A', 'FESS'), ('J32', 'SL013A', 'FESS'), ('J34.3', 'SL013A', 'FESS'), # Septum / Septoplasty ('J34.2', 'SL009A', 'Septoplasty'), ('J34.89', 'SL009A', 'Septoplasty'), # Rhinoplasty/Septorhinoplasty ('M95.0', 'SL008A', 'Functional septo rhinoplasty'), # Myringotomy ('H69', 'SL005A', 'Myringotomy'), ('H70', 'SL005A', 'Myringotomy'), # Peritonsillar abscess ('J36', 'SL017A', 'Peritonsillar abscess drainage'), # Epistaxis ('R04.0', 'SL007A', 'Epistaxis treatment'), # Branchial/Thyroglossal cyst ('Q18.0', 'SL018E', 'Branchial cyst excision'), ('Q89.2', 'SL018E', 'Thyroglossal cyst excision'), # Turbinectomy ('J30', 'SL040AMH', 'Turbinectomy'), ('J31', 'SL040AMH', 'Turbinectomy'), # Salivary gland ('K11.2', 'SL022A', 'Submandibular gland removal'), ('K11.3', 'SL022A', 'Submandibular gland removal'), ('K11.6', 'SL022A', 'Submandibular gland removal'), # Mandible/Maxilla fracture ('S02.4', 'SL033B', 'Closed reduction IMF mandible/maxilla'), ('S02.6', 'SL033B', 'Closed reduction IMF mandible/maxilla'), ('S02.8', 'SL033B', 'Closed reduction IMF mandible/maxilla'), ('S03.0', 'SL033B', 'Closed reduction IMF mandible/maxilla'), # GENERAL SURGERY # Appendicitis ('K35', 'SG017A', 'Appendicectomy'), ('K36', 'SG017A', 'Appendicectomy'), ('K37', 'SG017A', 'Appendicectomy'), # Cholecystitis / Gallstones ('K80', 'SG039A', 'Cholecystectomy (Lap)'), ('K81', 'SG039A', 'Cholecystectomy (Lap)'), ('K82', 'SG039A', 'Cholecystectomy (Lap)'), # Inguinal hernia ('K40', 'SG050A', 'Groin Hernia Repair'), ('K41', 'SG050A', 'Groin Hernia Repair (Femoral)'), # Ventral / Umbilical hernia ('K42', 'SG051A', 'Hernia - Ventral (Umbilical)'), ('K43', 'SG052A', 'Repair of Incisional Hernia'), ('K44', 'SG052A', 'Repair of Incisional Hernia'), # Haemorrhoids ('K64', 'SG032A', 'Haemorrhoidectomy'), ('K64.0', 'SG032A', 'Haemorrhoidectomy'), ('K64.1', 'SG032A', 'Haemorrhoidectomy'), ('K64.2', 'SG032A', 'Haemorrhoidectomy'), # Fistula in ano ('K60.3', 'SG127AMH', 'Fistula in Ano'), ('K60.4', 'SG127AMH', 'Fistula in Ano'), # Pilonidal sinus ('L05', 'SG033A', 'Pilonidal Sinus'), # Fissure in ano ('K60.0', 'SG031A', 'Fissure in Ano'), ('K60.1', 'SG031A', 'Fissure in Ano'), ('K60.2', 'SG031A', 'Fissure in Ano'), # Rectal prolapse ('K62.3', 'SG026A', 'Rectal Prolapse'), # Hydrocele ('N43', 'SG056A', 'Operation for Hydrocele'), ('N43.0', 'SG056A', 'Operation for Hydrocele'), ('N43.1', 'SG056A', 'Operation for Hydrocele'), ('N43.2', 'SG056A', 'Operation for Hydrocele'), # Epididymal cyst ('N50.8', 'SG057A', 'Epididymal Cyst Excision'), ('N50', 'SG057A', 'Epididymal Cyst Excision'), # Thyroid ('E04', 'SG070B', 'Thyroidectomy'), ('E05', 'SG070A', 'Hemi Thyroidectomy'), ('C73', 'SG070B', 'Thyroidectomy'), ('D34', 'SG070A', 'Hemi Thyroidectomy'), # Parathyroid ('E21', 'SS022A', 'Parathyroidectomy'), ('D35.1', 'SS022A', 'Parathyroidectomy'), # Breast lump / Fibroadenoma ('N60', 'SG074A', 'Breast Lump Excision (Benign)'), ('D24', 'SG074A', 'Breast Lump Excision (Benign)'), ('D48.6', 'SG074A', 'Breast Lump Excision (Benign)'), # Mastectomy ('C50', 'SG075A', 'Mastectomy'), # Varicose veins ('I83', 'SG085A', 'Varicose vein ligation/stripping'), # Lipoma / Cyst / Skin swelling ('D17', 'SG085A', 'Lipoma Excision'), ('D21', 'SC066A', 'Benign Soft Tissue Tumour Excision'), ('L72', 'SG054A', 'Sebaceous Cyst Excision'), ('L72.1', 'SG054A', 'Sebaceous Cyst Excision'), # Abscess / I&D ('L02', 'SG084A', 'I&D Abscess'), ('L03', 'SG084A', 'I&D Abscess'), # Debridement / Ulcer ('L97', 'SG086A', 'Debridement of Ulcer'), ('L98.4', 'SG086A', 'Debridement of Ulcer'), ('T79.3', 'SG086A', 'Debridement'), # Biopsy ('D37', 'SG096A', 'Biopsy'), ('D48', 'SG096A', 'Biopsy'), # Splenectomy ('D73', 'SG042A', 'Splenectomy'), ('Q89.0', 'SG042A', 'Splenectomy'), # Circumcision ('N47', 'SG104A', 'Circumcision'), ('N47.0', 'SG104A', 'Circumcision'), # Sinus tract ('L08.9', 'SG034A', 'Sinus/Curettage'), # OBG # LSCS / Caesarean ('O34.2', 'SO057A', 'Caesarean Delivery'), ('O82', 'SO057A', 'Caesarean Delivery'), ('O63', 'SO057A', 'Caesarean Delivery'), ('O64', 'SO057A', 'Caesarean Delivery'), ('O65', 'SO057A', 'Caesarean Delivery'), # Abdominal / TAH Hysterectomy ('D25', 'SO010B', 'Abdominal Hysterectomy'), ('N80', 'SO010B', 'Abdominal Hysterectomy (Endometriosis)'), ('N81', 'SO010C', 'Vaginal Hysterectomy (Prolapse)'), ('C53', 'SO010B', 'Hysterectomy (Cervical Ca)'), ('C54', 'SO010B', 'Hysterectomy (Uterine Ca)'), ('D26', 'SO010B', 'Hysterectomy (Uterine lesion)'), # Laparoscopic Hysterectomy # Diagnostic Laparoscopy ('N83', 'SO039A', 'Diagnostic Laparoscopy (Ovarian cyst)'), ('N97', 'SO039A', 'Diagnostic Laparoscopy (Infertility)'), ('N94', 'SO039A', 'Diagnostic Laparoscopy (Pelvic pain)'), # D&C ('N85', 'SO018A', 'D&C'), ('O08', 'SO018A', 'D&C (post-abortion)'), ('O03', 'SO019A', 'D&E (Abortion)'), ('O04', 'SO019A', 'D&E'), # MTP ('Z30.2', 'SO053A', 'MTP'), ('O06', 'SO053A', 'MTP'), # Tubal ligation / Sterilisation ('Z30.2', 'SO064A', 'Sterilisation'), ('Z30.5', 'SO064A', 'Sterilisation'), # Endometrial / Cervical Biopsy ('N86', 'SO073A', 'Endometrial/Cervical Biopsy'), ('N87', 'SO073A', 'Endometrial Biopsy'), ('N88', 'SO073A', 'Cervical Biopsy'), ('N84.0', 'SO073A', 'Endometrial polypectomy'), # Ovarian drilling ('E28.2', 'SO078AMH', 'Ovarian Drilling (PCOD)'), ('N93', 'SO018A', 'D&C (abnormal bleeding)'), # Bartholin cyst ('N75', 'SO059A', 'Bartholin cyst drainage'), # Anterior-posterior repair ('N81.1', 'SO042A', 'Cystocele/Anterior repair'), ('N81.2', 'SO030A', 'A&P Repair'), ('N81.3', 'SO030A', 'A&P Repair'), ('N81.4', 'SO030A', 'A&P Repair'), # Hysteroscopy ('N85.0', 'SO016A', 'Diagnostic Hysteroscopy'), # Sling surgery / TVT ('N39.3', 'SO013A', 'Sling Surgery'), # Episiotomy repair ('O90.1', 'SO056A', 'Episiotomy Repair'), # High risk delivery ('O60', 'SO054B', 'High risk delivery'), ('O67', 'SO054B', 'High risk delivery'), ('O72', 'SO055A', 'Manual removal of placenta'), # Vacuum/forceps ('O81', 'SO075A', 'Vacuum/Forceps delivery'), # Ectopic ('O00', 'SO052A', 'Ectopic pregnancy'), # Hymenectomy ('Q52.3', 'SO029A', 'Hymenectomy'), # Colpotomy ('N73.0', 'SO038A', 'Colpotomy'), # Vault prolapse ('N99.3', 'SO081AMH', 'Vault Prolapse'), # UROLOGY # TURP / BPH / Prostate ('N40', 'SU077A', 'TURP (BPH)'), ('N41', 'SU077A', 'TURP (Prostatitis)'), # Calculus / Nephrolithiasis ('N20.0', 'SU024A', 'Pyelolithotomy'), ('N20.1', 'SU094A', 'Ureteric stone management'), ('N20.2', 'SU040A', 'Cystolithotomy'), ('N21.0', 'SU040A', 'Cystolithotomy'), ('N21.1', 'SU040A', 'Cystolithotomy'), # Orchidectomy ('C62', 'SU086A', 'Orchidectomy'), ('C61', 'SU087A', 'Bilateral Orchidectomy (Prostate Ca)'), # Varicocele ('I86.1', 'SU089A', 'Varicocele'), # Paraphimosis / Phimosis / Circumcision ('N47', 'SG104A', 'Circumcision'), ('N47.1', 'SU034AMH', 'Reduction of Paraphimosis'), # Haematuria ('R31', 'SU073A', 'Emergency Hematuria management'), # Urethral stricture ('N35', 'SU036AMH', 'Urethrotomy'), # Acute retention ('R33', 'SU064A', 'Acute retention of urine'), # ORTHOPEDICS # THR ('M16', 'SB038B', 'Total Hip Replacement'), ('M16.0', 'SB038B', 'Total Hip Replacement'), ('M16.1', 'SB038B', 'Total Hip Replacement'), ('M16.5', 'SB038B', 'Total Hip Replacement'), ('M16.9', 'SB038B', 'Total Hip Replacement'), # TKR ('M17', 'SB039A', 'Total Knee Replacement'), ('M17.0', 'SB039A', 'Total Knee Replacement'), ('M17.1', 'SB039A', 'Total Knee Replacement'), ('M17.9', 'SB039A', 'Total Knee Replacement'), # ACL ('M23.6', 'SB049A', 'ACL Reconstruction'), ('M23.5', 'SB049A', 'ACL Reconstruction'), # Meniscus ('M23.2', 'SB036A', 'Arthroscopic Meniscectomy'), ('M23.0', 'SB036A', 'Arthroscopic Meniscectomy'), # Club foot ('Q66', 'SB063A', 'CTEV / Club foot'), ('Q66.0', 'SB063A', 'CTEV / Club foot'), # Clavicle fracture ('S42.0', 'SB076AMH', 'Clavicle Fracture'), # Traction ('S72', 'SB002A', 'Application of Traction'), ('S82', 'SB002A', 'Application of Traction'), # Bursa ('M71', 'SB065A', 'Excision of Bursa'), # Spine fusion ('M47', 'SN068BMH', 'Spine Fusion'), ('M48', 'SN068BMH', 'Spine Fusion'), ('M51', 'SN068BMH', 'Spine Fusion'), ('M50', 'SN068BMH', 'Spine Fusion'), ('S12', 'SN032A', 'Corpectomy'), ('S22', 'SN032A', 'Corpectomy'), # GENERAL MEDICINE ('A00', 'MG009A', 'Acute gastroenteritis'), ('A01', 'MG006A', 'Enteric fever'), ('A01.0', 'MG006A', 'Enteric fever (Typhoid)'), ('A09', 'MG009A', 'Acute gastroenteritis with dehydration'), ('A90', 'MG004A', 'Dengue fever'), ('A91', 'MG004A', 'Dengue haemorrhagic fever'), ('A92', 'MG005A', 'Chikungunya fever'), ('A96', 'MG001A', 'Acute febrile illness'), ('A27', 'MG008A', 'Leptospirosis'), ('B50', 'MG003A', 'Malaria (P. falciparum)'), ('B51', 'MG003A', 'Malaria (P. vivax)'), ('B54', 'MG003A', 'Malaria (unspecified)'), ('R50', 'MG001A', 'Acute febrile illness'), ('R50.9', 'MG001A', 'Fever unspecified'), ('J06', 'MG028A', 'Acute bronchitis'), ('J20', 'MG028A', 'Acute bronchitis'), ('J21', 'MG028A', 'Acute bronchitis'), ('J45', 'MG039A', 'Asthma'), ('J18', 'MG001A', 'Pneumonia/febrile illness'), ('N10', 'MG021A', 'Urinary Tract Infection'), ('N30', 'MG021A', 'Urinary Tract Infection (cystitis)'), ('N39.0', 'MG021A', 'Urinary Tract Infection'), ('K52', 'MG009A', 'Acute gastroenteritis'), ('K29.0', 'MG009A', 'Acute gastritis/gastroenteritis'), ('N23', 'MG044A', 'Renal colic'), ('R10.2', 'MG044A', 'Renal colic'), ('E16.0', 'MG057A', 'Hypoglycemia'), ('E16.2', 'MG057A', 'Hypoglycemia'), ('I10', 'MG062A', 'Accelerated hypertension'), ('D50', 'MG064A', 'Severe anaemia'), ('D51', 'MG064A', 'Severe anaemia'), ('D52', 'MG064A', 'Severe anaemia'), ('D53', 'MG064A', 'Severe anaemia'), ('D64', 'MG064A', 'Severe anaemia'), ('T78.2', 'MG066A', 'Anaphylaxis'), ('T67', 'MG067A', 'Heat stroke'), ('R55', 'MG001A', 'Acute febrile illness'), # MENTAL DISORDERS ('F70', 'MM001A', 'Mental Retardation'), ('F71', 'MM001A', 'Mental Retardation'), ('F00', 'MM002A', 'Mental disorders - Organic'), ('F06', 'MM002A', 'Mental disorders - Organic'), ('F20', 'MM003A', 'Schizophrenia'), ('F25', 'MM003A', 'Schizoaffective'), ('F40', 'MM004A', 'Neurotic/stress disorders'), ('F41', 'MM004A', 'Neurotic/stress disorders'), ('F32', 'MM005A', 'Mood disorders'), ('F33', 'MM005A', 'Mood disorders'), ('F31', 'MM005A', 'Bipolar disorder'), ('F50', 'MM006A', 'Behavioural syndromes'), ('F10', 'MM007A', 'Substance use disorders'), ('F11', 'MM007A', 'Substance use disorders'), # EMERGENCY ('T14.0', 'ER001A', 'Laceration/Wound suturing'), ('T14.1', 'ER001A', 'Wound suturing'), ('S01', 'ER001A', 'Laceration/wound'), ('W54', 'ER003A', 'Animal bite'), ('W53', 'ER003A', 'Animal bite'), ('T14.0', 'ER001A', 'Laceration'), # PLASTIC ('T79.3', 'SP009A', 'Resuturing wound'), ('T87', 'SP009A', 'Resuturing wound gap'), # PEDIATRIC ('Q37', 'SS001A', 'Cleft palate'), ('Q36', 'SS001A', 'Cleft lip'), ('Q38.1', 'SS002A', 'Ankyloglossia'), ('Q53', 'SS015A', 'Undescended testis - Hernia/Hydrocele'), ('D18.1', 'SS058AMH', 'Cystic hygroma'), # DENTAL / ORAL ('K08.1', 'SM001A', 'Tooth extraction (impacted)'), ('S03.2', 'SM011A', 'Dental trauma wiring'), # CARDIOLOGY ('I25', 'MC036AMH', 'Rotablation / Cardiac'), ] # Build prefix lookup (longest match wins) # Sort by ICD length descending so longer matches take priority icd_map_sorted = sorted(icd_map, key=lambda x: len(x[0]), reverse=True) def get_procedure(icd_code): if not icd_code: return None, None # ICD codes may be multiple separated by --- codes = str(icd_code).split('---') for code in codes: code = code.strip() for prefix, proc_code, pkg_name in icd_map_sorted: if code == prefix or code.startswith(prefix): return proc_code, pkg_name return None, None # ── 2. Load 223 Govt Reserved procedure info ── wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') ws_unique = wb223['Unique Codes'] ws1 = wb223['Sheet1'] unique_codes_ordered = [] for r in range(2, ws_unique.max_row+1): c = ws_unique.cell(r,2).value if c: unique_codes_ordered.append(str(c).strip()) code_info = {} for r in range(2, ws1.max_row+1): code = ws1.cell(r,5).value if code and str(code).strip() in unique_codes_ordered and str(code).strip() not in code_info: code_info[str(code).strip()] = { 'specialty': str(ws1.cell(r,2).value or '').strip(), 'package': str(ws1.cell(r,7).value or '').strip(), } # ── 3. Process each year ── year_counts = {} # year -> {code -> count} for year, fname in [('2023','ICD- 2023 ONLY DIAGNOSIS.xlsx'), ('2024','ICD- 2024 ONLY DIAGNOSIS.xlsx'), ('2025','ICD 2025 ONLY DIAGNOSIS.xlsx')]: wb = openpyxl.load_workbook(base + fname) ws = wb.active counts = defaultdict(int) unmatched_icds = Counter() total = 0 matched = 0 for r in range(2, ws.max_row+1): icd = ws.cell(r,18).value if icd: total += 1 proc_code, pkg_name = get_procedure(icd) if proc_code: counts[proc_code] += 1 matched += 1 else: unmatched_icds[str(icd).split('---')[0].strip()] += 1 year_counts[year] = dict(counts) print(f"{year}: total={total}, matched={matched}, unmatched={total-matched}") print(f" Top unmatched ICD codes: {unmatched_icds.most_common(10)}") # ── 4. Print results ── print(f"\n\n{'SPECIALTY':<38} | {'CODE':<12} | {'PACKAGE NAME':<55} | {'2023':>7} | {'2024':>7} | {'2025':>7} | {'TOTAL':>7}") print('='*155) grand = [0,0,0,0] prev_spec = '' for code in unique_codes_ordered: info = code_info.get(code, {}) y23 = year_counts['2023'].get(code,0) y24 = year_counts['2024'].get(code,0) y25 = year_counts['2025'].get(code,0) tot = y23+y24+y25 if tot > 0: spec = info.get('specialty','') if spec != prev_spec: print(f"{'--- '+spec+' ---'}") prev_spec = spec pkg = info.get('package','')[:53] print(f" {code:<12} | {pkg:<55} | {y23:>7} | {y24:>7} | {y25:>7} | {tot:>7}") grand[0]+=y23; grand[1]+=y24; grand[2]+=y25; grand[3]+=tot print(f"\n{'GRAND TOTAL':<55} | {grand[0]:>7} | {grand[1]:>7} | {grand[2]:>7} | {grand[3]:>7}") PYEOF

python3 -c " import openpyxl from collections import defaultdict, Counter base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # Use read_only mode for large files for year, fname in [('2023','ICD- 2023 ONLY DIAGNOSIS.xlsx'), ('2024','ICD- 2024 ONLY DIAGNOSIS.xlsx'), ('2025','ICD 2025 ONLY DIAGNOSIS.xlsx')]: wb = openpyxl.load_workbook(base + fname, read_only=True, data_only=True) ws = wb.active icd_counter = Counter() total = 0 for row in ws.iter_rows(min_row=2, values_only=True): icd = row[17] # col 18 = index 17 if icd: total += 1 # Take first code if multiple first = str(icd).split('---')[0].strip() # Take only first 3-4 chars as prefix icd_counter[first[:5]] += 1 wb.close() print(f'{year}: total={total}') print('Top 30 ICD prefixes:', icd_counter.most_common(30)) print() "

python3 -c " import openpyxl from collections import defaultdict, Counter base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' # ICD prefix -> (proc_code, package_name) -- longer prefix wins ICD_TO_PROC = { # OPHTHALMOLOGY 'H25':'SE020B','H26':'SE020B','H28':'SE020B', # Cataract 'H11.0':'SE014A','H11.01':'SE014A','H11.02':'SE014A', # Pterygium 'H04.0':'SE045AMH','H04.1':'SE045AMH','H04.3':'SE045AMH','H04.4':'SE045AMH','H04.5':'SE045AMH', # DCT/DCR 'H17':'SE012A','H18':'SE012A', # Corneal graft 'H40':'SE003A', # Glaucoma 'H33':'SE030A','H35.3':'SE030A', # Retinal 'H16.0':'SE053AMH','H16.1':'SE053AMH','H16':'SE053AMH', # Amniotic membrane # ENT 'H65':'SL002A','H66':'SL002A','H72':'SL002A','H74':'SL002A', # Tympanoplasty 'H73':'SL056AMH', # Myringoplasty 'J35.0':'SL016A', # Tonsillectomy 'J35.1':'SL015A','J35.2':'SL015A','J35.3':'SL015A','J35':'SL015A', # Adenoidectomy 'J33':'SL013A','J32':'SL013A','J34.3':'SL013A', # FESS / Sinus 'J34.2':'SL009A', # Septoplasty 'M95.0':'SL008A', # Septo rhinoplasty 'H69':'SL005A','H70':'SL005A', # Myringotomy 'J36':'SL017A', # Peritonsillar abscess 'R04.0':'SL007A', # Epistaxis 'Q18.0':'SL018E','Q89.2':'SL018E', # Thyroglossal/Branchial 'J30':'SL040AMH','J31':'SL040AMH', # Turbinectomy (allergic/vasomotor rhinitis) 'K11.2':'SL022A','K11.3':'SL022A','K11.6':'SL022A', # Salivary gland 'S02.4':'SL033B','S02.6':'SL033B','S02.8':'SL033B','S03.0':'SL033B', # Mandible fracture # GENERAL SURGERY 'K35':'SG017A','K36':'SG017A','K37':'SG017A', # Appendicitis 'K80':'SG039A','K81':'SG039A','K82':'SG039A', # Cholecystitis 'K40':'SG050A','K41':'SG050A', # Inguinal hernia 'K42':'SG051A', # Umbilical hernia 'K43':'SG052A','K44':'SG052A', # Incisional hernia 'K64':'SG032A', # Haemorrhoids 'K60.3':'SG127AMH','K60.4':'SG127AMH', # Fistula in ano 'K60.0':'SG031A','K60.1':'SG031A','K60.2':'SG031A', # Fissure 'L05':'SG033A', # Pilonidal 'K62.3':'SG026A', # Rectal prolapse 'N43':'SG056A', # Hydrocele 'N50':'SG057A', # Epididymal cyst 'E04':'SG070B','C73':'SG070B','D34':'SG070A','E05':'SG070A', # Thyroid 'E21':'SS022A','D35.1':'SS022A', # Parathyroid 'N60':'SG074A','D24':'SG074A','D48.6':'SG074A', # Breast lump 'C50':'SG075A', # Mastectomy 'I83':'SG085A', # Varicose veins 'D17':'SG085A', # Lipoma 'D21':'SC066A', # Benign soft tissue 'L72':'SG054A', # Sebaceous cyst 'L02':'SG084A','L03':'SG084A', # Abscess I&D 'L97':'SG086A','L98.4':'SG086A','T79.3':'SG086A', # Debridement 'D37':'SG096A','D48':'SG096A', # Biopsy 'D73':'SG042A','Q89.0':'SG042A', # Splenectomy 'N47':'SG104A', # Circumcision 'L08':'SG034A', # Sinus/curettage 'I86.1':'SU089A', # Varicocele 'L08.9':'SG034A', # OBG 'O34.2':'SO057A','O82':'SO057A','O63':'SO057A','O64':'SO057A','O65':'SO057A', # LSCS 'D25':'SO010B', # Fibroids -> TAH 'N80':'SO010B', # Endometriosis 'N81.0':'SO010C','N81.1':'SO042A','N81.2':'SO030A','N81.3':'SO030A','N81.4':'SO010C', # Prolapse 'N81':'SO010C', # Prolapse -> Vaginal Hyst 'C53':'SO010B','C54':'SO010B', # Ca cervix/uterus 'D26':'SO010B', # Uterine lesion 'N83':'SO039A','N97':'SO039A','N94':'SO039A', # Diagnostic lap 'N85':'SO018A', # D&C 'O08':'SO018A', # Post abortion D&C 'O03':'SO019A','O04':'SO019A', # D&E 'Z30.2':'SO053A','O06':'SO053A', # MTP 'Z30.5':'SO064A', # Sterilisation 'N86':'SO073A','N87':'SO073A','N88':'SO073A','N84.0':'SO073A', # Biopsy 'N93':'SO018A', # Abnormal uterine bleeding -> D&C 'E28.2':'SO078AMH', # PCOD -> Ovarian drilling 'N75':'SO059A', # Bartholin 'N85.0':'SO016A', # Hysteroscopy 'N39.3':'SO013A', # Sling 'O90.1':'SO056A', # Episiotomy repair 'O60':'SO054B','O67':'SO054B', # High risk delivery 'O72':'SO055A', # Manual placenta removal 'O81':'SO075A', # Vacuum/forceps 'O00':'SO052A', # Ectopic 'Q52.3':'SO029A', # Hymenectomy 'N73.0':'SO038A', # Colpotomy 'N99.3':'SO081AMH', # Vault prolapse # UROLOGY 'N40':'SU077A','N41':'SU077A', # TURP/BPH 'N20.0':'SU024A', # Pyelolithotomy (renal calculus) 'N20.1':'SU094A', # Ureteric stone 'N20.2':'SU040A','N21.0':'SU040A','N21.1':'SU040A', # Cystolithotomy 'C62':'SU086A', # Orchidectomy 'C61':'SU087A', # Bilateral orchidectomy (prostate Ca) 'N47.1':'SU034AMH', # Paraphimosis 'R31':'SU073A', # Hematuria 'N35':'SU036AMH', # Urethrotomy 'R33':'SU064A', # Acute retention # ORTHOPEDICS 'M16':'SB038B','M16.0':'SB038B','M16.1':'SB038B','M16.5':'SB038B','M16.9':'SB038B', # THR 'M17':'SB039A','M17.0':'SB039A','M17.1':'SB039A','M17.9':'SB039A', # TKR 'M23.6':'SB049A','M23.5':'SB049A', # ACL 'M23.2':'SB036A','M23.0':'SB036A', # Meniscus 'Q66':'SB063A', # Club foot 'S42.0':'SB076AMH', # Clavicle fracture 'S72':'SB002A','S82':'SB002A', # Traction 'M71':'SB065A', # Bursa 'M47':'SN068BMH','M48':'SN068BMH','M51':'SN068BMH','M50':'SN068BMH', # Spine fusion 'S12':'SN032A','S22':'SN032A', # Corpectomy # GENERAL MEDICINE 'A01.0':'MG006A','A01':'MG006A', # Typhoid 'A09':'MG009A', # Gastroenteritis 'A90':'MG004A','A91':'MG004A', # Dengue 'A92':'MG005A', # Chikungunya 'A27':'MG008A', # Leptospirosis 'B50':'MG003A','B51':'MG003A','B54':'MG003A', # Malaria 'R50.9':'MG001A','R50':'MG001A', # Fever 'J20':'MG028A','J21':'MG028A', # Acute bronchitis 'J45':'MG039A', # Asthma 'N10':'MG021A','N30':'MG021A','N39.0':'MG021A', # UTI 'K52':'MG009A','K29.0':'MG009A', # Gastroenteritis 'N23':'MG044A','R10.2':'MG044A', # Renal colic 'E16.0':'MG057A','E16.2':'MG057A', # Hypoglycemia 'I10':'MG062A', # Hypertension 'D50':'MG064A','D51':'MG064A','D52':'MG064A','D53':'MG064A','D64':'MG064A', # Anaemia 'T78.2':'MG066A', # Anaphylaxis 'T67':'MG067A', # Heat stroke # MENTAL DISORDERS 'F70':'MM001A','F71':'MM001A', 'F00':'MM002A','F06':'MM002A', 'F20':'MM003A','F25':'MM003A', 'F40':'MM004A','F41':'MM004A', 'F32':'MM005A','F33':'MM005A','F31':'MM005A', 'F50':'MM006A', 'F10':'MM007A','F11':'MM007A', # EMERGENCY / PLASTIC / PEDIATRIC 'T14.0':'ER001A','T14.1':'ER001A','S01':'ER001A', 'W54':'ER003A','W53':'ER003A', 'Q37':'SS001A','Q36':'SS001A', 'Q38.1':'SS002A', 'Q53':'SS015A', 'D18.1':'SS058AMH', 'K08.1':'SM001A', } # Sort by prefix length descending for longest-match sorted_prefixes = sorted(ICD_TO_PROC.keys(), key=len, reverse=True) def lookup(icd_str): if not icd_str: return None codes = str(icd_str).split('---') for code in codes: code = code.strip() for prefix in sorted_prefixes: if code == prefix or code.startswith(prefix): return ICD_TO_PROC[prefix] return None # Process each year results = {} for year, fname in [('2023','ICD- 2023 ONLY DIAGNOSIS.xlsx'), ('2024','ICD- 2024 ONLY DIAGNOSIS.xlsx'), ('2025','ICD 2025 ONLY DIAGNOSIS.xlsx')]: wb = openpyxl.load_workbook(base + fname, read_only=True, data_only=True) ws = wb.active counts = defaultdict(int) total = 0; matched = 0 unmatched = Counter() for row in ws.iter_rows(min_row=2, values_only=True): icd = row[17] if icd: total += 1 code = lookup(icd) if code: counts[code] += 1 matched += 1 else: first = str(icd).split('---')[0].strip()[:5] unmatched[first] += 1 wb.close() results[year] = dict(counts) print(f'{year}: total={total}, matched={matched} ({100*matched//total}%)') print(' Top unmatched:', unmatched.most_common(15)) import json with open('/tmp/icd_results.json','w') as f: json.dump(results, f) print('Done') "

python3 -c " import openpyxl, json base = '/home/daytona/workspace/attachments/da6ed562-2d2f-4a3d-852f-7a482193ed30/' with open('/tmp/icd_results.json') as f: results = json.load(f) wb223 = openpyxl.load_workbook(base + '223 Govt Reserved Final (1).xlsx') ws_u = wb223['Unique Codes'] ws1 = wb223['Sheet1'] unique_codes = [] for r in range(2, ws_u.max_row+1): c = ws_u.cell(r,2).value if c: unique_codes.append(str(c).strip()) code_info = {} for r in range(2, ws1.max_row+1): code = ws1.cell(r,5).value if code and str(code).strip() in unique_codes and str(code).strip() not in code_info: code_info[str(code).strip()] = { 'specialty': str(ws1.cell(r,2).value or '').strip(), 'package': str(ws1.cell(r,7).value or '').strip(), } print('SPECIALTY | CODE | PACKAGE NAME | 2023 | 2024 | 2025 | TOTAL') print('='*110) grand = [0,0,0,0] prev_spec = '' for code in unique_codes: info = code_info.get(code, {}) y23 = results['2023'].get(code,0) y24 = results['2024'].get(code,0) y25 = results['2025'].get(code,0) tot = y23+y24+y25 if tot > 0: spec = info.get('specialty','') if spec != prev_spec: print(f'\\n=== {spec} ===') prev_spec = spec pkg = info.get('package','')[:50] print(f' {code:<12} | {pkg:<52} | {y23:>6} | {y24:>6} | {y25:>6} | {tot:>7}') grand[0]+=y23; grand[1]+=y24; grand[2]+=y25; grand[3]+=tot print(f'\\n\\nGRAND TOTAL: 2023={grand[0]}, 2024={grand[1]}, 2025={grand[2]}, TOTAL={grand[3]}') "