~/

PDF text extraction patient ward list workflow

~/attachments

python - <<'PY' import pdfplumber p='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf' with pdfplumber.open(p) as pdf: print('PAGES',len(pdf.pages)) for i,page in enumerate(pdf.pages): print('\n===== PAGE',i+1,'=====') print(page.extract_text(x_tolerance=2,y_tolerance=3) or '[NO TEXT]') tables=page.extract_tables() print('TABLES',len(tables)) for j,t in enumerate(tables): print('T',j,t[:5]) PY

~/

mkdir -p "/tmp/workspace/ward-wise-unit-d/output" python - <<'PY' import re, pdfplumber, json from collections import defaultdict src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) # Strip headers and footers t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S) t=re.sub(r'Printed Date / Time.*','',t,flags=re.S) t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t) texts.append(t.strip()) text='\n'.join(texts) # Split blocks at each serial-start starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text)) rows=[] for i,m in enumerate(starts): block=text[m.start(): starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ') serial=int(m.group(1)); adm=m.group(2) # Date request, MRN, visit and remaining z=re.match(r'^(\d+)\s+(\S+\s+\S+)\s+(\S+\s+\S+)\s+(\d+)\s+(IP\d+)\s+(.*)$',block) if not z: print('FAIL basic',serial,block); continue _,ad,req,mrn,visit,rest=z.groups() # Get patient name before age marker. The report has either age 'N Y [x M x D]' or 'N Y' and gender after. agepat=re.search(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*?)\s+UNIT D\s*\((.*?)\)\s+(.*?)\s+(PMJAY|SELF PAY|PENSIONER \(AMC\)|CGHS-CREDIT)\s+(.*)$',rest) if not agepat: print('FAIL pattern',serial,rest); continue name,age,gender,bed,doc,dept,payor,ward=agepat.groups() rows.append(dict(S_No=serial,Admission=ad,Request=req,MRN=mrn,Visit_ID=visit,Patient_Name=name.title(),Age=int(age),Gender=gender,Bed=bed,Payor=payor,Ward=ward)) print('Parsed',len(rows),'of',len(starts)) for r in rows: print(r['S_No'],r['Ward'],r['Patient_Name'],r['Bed']) open('/tmp/workspace/ward-wise-unit-d/output/parsed.json','w').write(json.dumps(rows,indent=2)) PY

python - <<'PY' import re, pdfplumber, json from collections import Counter,defaultdict from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.table import Table, TableStyleInfo from openpyxl.utils import get_column_letter src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf' out='/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) or '' t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S) t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t) t=re.sub(r'Printed Date / Time.*','',t,flags=re.S) texts.append(t.strip()) text='\n'.join(texts) starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text)) rows=[] def ward_for_bed(b): b=b.strip() if b.startswith('GF-A2-RESUSC'): return 'GF-A2 Resuscitation / Trauma Ward' if b.startswith('GF-A2-ER OBSERV'): return 'GF-A2 ER Observation Ward' if b.startswith('GF-A1-ICU'): return 'GF-A1 Emergency ICU' if b.startswith('4F-B1-'): return '4F-B1 General Ward' if b.startswith('4F-B2-'): return '4F-B2 General Ward' if b.startswith('4F-C1-'): return '4F-C1 General Ward' if b.startswith('4F-C2-'): return '4F-C2 General Ward' if b.startswith('3F-C1-'): return '3F-C1 General Ward - Male' if b.startswith('3F-C2-'): return '3F-C2 General Ward - Female' if b.startswith('10F-B2-'): return '10F-B2 ICU - Female' if b.startswith('10F-B1-'): return '10F-B1 ICU - Male' if b.startswith('11F-A2-'): return '11F-A2 General Ward - Male HDU' if b.startswith('11F-C1-'): return '11F-C1 General Ward - Male HDU' if b.startswith('12F-A1-2S-'): return '12F-A1 Twin Sharing' if b.startswith('12F-A1-SP-'): return '12F-A1 Special Ward' return 'Unclassified' for i,m in enumerate(starts): block=text[m.start(): starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ').strip() z=re.match(r'^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d+)\s+(IP\d+)\s+(.*?)\s+UNIT D\b',block) if not z: raise ValueError(f'cannot parse row {m.group(1)} {block}') serial,adm,req,mrn,visit,pre=z.groups() # pre ends exactly after bed, and earlier form is name age Y (duration optional) gender bed a=re.match(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*)$',pre) if not a: raise ValueError(f'cannot parse pre row {serial}: {pre}') name,age,gender,bed=a.groups() rows.append({'S. No.':int(serial),'Admission Date & Time':adm,'MRN':mrn,'Visit ID':visit,'Patient Name':name.title(),'Age':int(age),'Gender':gender,'Bed at Registration':bed.strip(),'Ward':ward_for_bed(bed)}) assert len(rows)==181, len(rows) assert not any(r['Ward']=='Unclassified' for r in rows),[r for r in rows if r['Ward']=='Unclassified'] # workbook wb=Workbook(); ws=wb.active; ws.title='Ward Summary' headers=['Ward','No. of registrations'] ws.append(['UNIT D - WARD-WISE PATIENT REGISTRATION LIST']); ws.merge_cells('A1:B1') ws['A1'].font=Font(name='Arial',size=14,bold=True,color='FFFFFF'); ws['A1'].fill=PatternFill('solid',fgColor='1F4E78'); ws['A1'].alignment=Alignment(horizontal='center') ws.append(['Source: IP Registration Report | Admission period: 01-09-2026 to 30-09-2026']) ; ws.merge_cells('A2:B2') ws['A2'].font=Font(name='Arial',italic=True,color='555555') ws.append(headers) counts=Counter(r['Ward'] for r in rows) for ward,n in sorted(counts.items()): ws.append([ward,n]) ws.append(['TOTAL',len(rows)]) # formatting generic thin=Side(style='thin',color='D9E1F2') for c in ws[3]: c.font=Font(name='Arial',bold=True,color='FFFFFF'); c.fill=PatternFill('solid',fgColor='5B9BD5'); c.alignment=Alignment(horizontal='center') for row in ws.iter_rows(min_row=3): for c in row: c.font=Font(name='Arial',size=10,bold=(c.row==ws.max_row)); c.border=Border(bottom=thin); c.alignment=Alignment(vertical='top',wrap_text=True) ws.column_dimensions['A'].width=42; ws.column_dimensions['B'].width=21; ws.freeze_panes='A4'; ws.auto_filter.ref=f'A3:B{ws.max_row}' # sheets safe_map={} for idx,(ward,_) in enumerate(sorted(counts.items()),start=1): title=f'{idx:02d} '+ward.replace(' / ','-').replace(' - ','-') title=title[:31] safe_map[ward]=title; sh=wb.create_sheet(title) sh.append([f'UNIT D - {ward}']); sh.merge_cells('A1:I1') sh['A1'].font=Font(name='Arial',size=13,bold=True,color='FFFFFF');sh['A1'].fill=PatternFill('solid',fgColor='1F4E78');sh['A1'].alignment=Alignment(horizontal='center') sh.append(['Admission period: 01-09-2026 to 30-09-2026']);sh.merge_cells('A2:I2');sh['A2'].font=Font(name='Arial',italic=True,color='555555') cols=['S. No.','Admission Date & Time','MRN','Visit ID','Patient Name','Age','Gender','Bed at Registration','Ward'] sh.append(cols) data=sorted([r for r in rows if r['Ward']==ward],key=lambda x:x['S. No.']) for r in data: sh.append([r[c] for c in cols]) for c in sh[3]: c.font=Font(name='Arial',bold=True,color='FFFFFF');c.fill=PatternFill('solid',fgColor='5B9BD5');c.alignment=Alignment(horizontal='center',wrap_text=True) for rr in sh.iter_rows(min_row=4,max_row=sh.max_row): for c in rr: c.font=Font(name='Arial',size=10);c.border=Border(bottom=thin);c.alignment=Alignment(vertical='top',wrap_text=True) widths=[9,20,16,16,37,7,10,24,34] for j,w in enumerate(widths,1): sh.column_dimensions[get_column_letter(j)].width=w sh.freeze_panes='A4';sh.auto_filter.ref=f'A3:I{sh.max_row}' sh.sheet_view.showGridLines=False ws.sheet_view.showGridLines=False wb.save(out) print('saved',out,'count',len(rows),'wards',len(counts),sorted(counts.items())) PY ls -lh "/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx"

python - <<'PY' import re, pdfplumber from collections import Counter from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf';out='/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) or '' t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S);t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t);t=re.sub(r'Printed Date / Time.*','',t,flags=re.S);texts.append(t.strip()) text='\n'.join(texts);starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text));rows=[] def ward_for_bed(b): for prefix,label in [('GF-A2-RESUSC','GF-A2 Resuscitation / Trauma Ward'),('GF-A2-ER OBSERV','GF-A2 ER Observation Ward'),('GF-A1-ICU','GF-A1 Emergency ICU'),('4F-B1-','4F-B1 General Ward'),('4F-B2-','4F-B2 General Ward'),('4F-C1-','4F-C1 General Ward'),('4F-C2-','4F-C2 General Ward'),('3F-C1-','3F-C1 General Ward - Male'),('3F-C2-','3F-C2 General Ward - Female'),('10F-B2-','10F-B2 ICU - Female'),('10F-B1-','10F-B1 ICU - Male'),('11F-A2-','11F-A2 General Ward - Male HDU'),('11F-C1-','11F-C1 General Ward - Male HDU'),('12F-A1-2S-','12F-A1 Twin Sharing'),('12F-A1-SP-','12F-A1 Special Ward')]: if b.startswith(prefix): return label return 'Unclassified' for i,m in enumerate(starts): block=text[m.start():starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ').strip() z=re.match(r'^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d+)\s+(IP\d+)\s+(.*?)\s+UNIT(?: D)?\b',block) if not z:raise ValueError(block) serial,adm,req,mrn,visit,pre=z.groups();a=re.match(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*)$',pre) if not a:raise ValueError(pre) name,age,gender,bed=a.groups();rows.append({'S. No.':int(serial),'Admission Date & Time':adm,'MRN':mrn,'Visit ID':visit,'Patient Name':name.title(),'Age':int(age),'Gender':gender,'Bed at Registration':bed.strip(),'Ward':ward_for_bed(bed)}) assert len(rows)==181 and all(r['Ward']!='Unclassified' for r in rows) wb=Workbook();ws=wb.active;ws.title='Ward Summary';blue='1F4E78';light='5B9BD5';thin=Side(style='thin',color='D9E1F2') def title(sh,text,cols): sh.append([text]);sh.merge_cells(start_row=1,start_column=1,end_row=1,end_column=cols);sh['A1'].font=Font(name='Arial',size=13,bold=True,color='FFFFFF');sh['A1'].fill=PatternFill('solid',fgColor=blue);sh['A1'].alignment=Alignment(horizontal='center') def style(sh,header_row,max_col): for c in sh[header_row]: c.font=Font(name='Arial',bold=True,color='FFFFFF');c.fill=PatternFill('solid',fgColor=light);c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True) for rr in sh.iter_rows(min_row=header_row+1,max_row=sh.max_row,max_col=max_col): for c in rr:c.font=Font(name='Arial',size=10);c.border=Border(bottom=thin);c.alignment=Alignment(vertical='top',wrap_text=True) sh.freeze_panes=f'A{header_row+1}';sh.auto_filter.ref=f'A{header_row}:{get_column_letter(max_col)}{sh.max_row}';sh.sheet_view.showGridLines=False counts=Counter(r['Ward'] for r in rows);title(ws,'UNIT D - WARD-WISE PATIENT REGISTRATION LIST',2);ws.append(['Source: IP Registration Report | Admission period: 01-09-2026 to 30-09-2026']);ws.merge_cells('A2:B2');ws['A2'].font=Font(name='Arial',italic=True,color='555555');ws.append(['Ward','No. of registrations']) for ward,n in sorted(counts.items()):ws.append([ward,n]) ws.append(['TOTAL',len(rows)]);style(ws,3,2);ws['A'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws['B'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws.column_dimensions['A'].width=42;ws.column_dimensions['B'].width=21 cols=['S. No.','Admission Date & Time','MRN','Visit ID','Patient Name','Age','Gender','Bed at Registration','Ward'] for idx,ward in enumerate(sorted(counts),1): sh=wb.create_sheet((f'{idx:02d} {ward.replace(" / ","-").replace(" - ","-")}')[:31]);title(sh,f'UNIT D - {ward}',9);sh.append(['Admission period: 01-09-2026 to 30-09-2026']);sh.merge_cells('A2:I2');sh['A2'].font=Font(name='Arial',italic=True,color='555555');sh.append(cols) for r in sorted((x for x in rows if x['Ward']==ward),key=lambda q:q['S. No.']):sh.append([r[c] for c in cols]) style(sh,3,9) for j,w in enumerate([9,20,16,16,37,7,10,24,34],1):sh.column_dimensions[get_column_letter(j)].width=w wb.save(out) print('OK',len(rows),len(counts),sorted(counts.items())) PY python - <<'PY' from openpyxl import load_workbook p='/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx';w=load_workbook(p,read_only=True) print('sheets',len(w.sheetnames),w.sheetnames);print('summary total',w['Ward Summary'].cell(w['Ward Summary'].max_row,2).value) PY ls -lh "/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx"

python - <<'PY' import re, pdfplumber from collections import Counter src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) or '' t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S);t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t);t=re.sub(r'Printed Date / Time.*','',t,flags=re.S);texts.append(t.strip()) text='\n'.join(texts); starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text)); for i,m in enumerate(starts): block=text[m.start():starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ').strip(); z=re.match(r'^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d+)\s+(IP\d+)\s+(.*?)\s+UNIT(?: D)?\b',block);pre=z.group(6);a=re.match(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*)$',pre);bed=a.group(4).strip() if bed=='GF-A2-':print('bare',m.group(1),block) PY

python - <<'PY' import re, pdfplumber from collections import Counter from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit d.pdf';out='/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) or '' t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S);t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t);t=re.sub(r'Printed Date / Time.*','',t,flags=re.S);texts.append(t.strip()) text='\n'.join(texts); starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text)); rows=[] def classify(block,pre): if 'GF-A2-RESUCITATIONS' in block: b='GF-A2-RESUSC-'+(re.search(r'RESUSC-(\d+)',block).group(1) if re.search(r'RESUSC-(\d+)',block) else '') return 'GF-A2 Resuscitation / Trauma Ward',b if 'GF-A2-ER OBSERV' in block: b='GF-A2-ER OBSERV-'+(re.search(r'OBSERV-(\d+)',block).group(1) if re.search(r'OBSERV-(\d+)',block) else '') return 'GF-A2 ER Observation Ward',b b=pre.strip() mapping=[('GF-A1-ICU','GF-A1 Emergency ICU'),('4F-B1-','4F-B1 General Ward'),('4F-B2-','4F-B2 General Ward'),('4F-C1-','4F-C1 General Ward'),('4F-C2-','4F-C2 General Ward'),('3F-C1-','3F-C1 General Ward - Male'),('3F-C2-','3F-C2 General Ward - Female'),('10F-B2-','10F-B2 ICU - Female'),('10F-B1-','10F-B1 ICU - Male'),('11F-A2-','11F-A2 General Ward - Male HDU'),('11F-C1-','11F-C1 General Ward - Male HDU'),('12F-A1-2S-','12F-A1 Twin Sharing'),('12F-A1-SP-','12F-A1 Special Ward')] for p,w in mapping: if b.startswith(p): return w,b return 'Unclassified',b for i,m in enumerate(starts): block=text[m.start():starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ').strip() z=re.match(r'^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d+)\s+(IP\d+)\s+(.*?)\s+UNIT(?: D)?\b',block) serial,adm,req,mrn,visit,pre=z.groups(); a=re.match(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*)$',pre) name,age,gender,bedpre=a.groups(); ward,bed=classify(block,bedpre) rows.append({'S. No.':int(serial),'Admission Date & Time':adm,'MRN':mrn,'Visit ID':visit,'Patient Name':name.title(),'Age':int(age),'Gender':gender,'Bed at Registration':bed,'Ward':ward}) assert len(rows)==181;assert all(r['Ward']!='Unclassified' for r in rows),[r for r in rows if r['Ward']=='Unclassified'] wb=Workbook();ws=wb.active;ws.title='Ward Summary';blue='1F4E78';light='5B9BD5';thin=Side(style='thin',color='D9E1F2') def title(sh,text,cols): sh.append([text]);sh.merge_cells(start_row=1,start_column=1,end_row=1,end_column=cols);sh['A1'].font=Font(name='Arial',size=13,bold=True,color='FFFFFF');sh['A1'].fill=PatternFill('solid',fgColor=blue);sh['A1'].alignment=Alignment(horizontal='center') def style(sh,hr,n): for c in sh[hr]:c.font=Font(name='Arial',bold=True,color='FFFFFF');c.fill=PatternFill('solid',fgColor=light);c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True) for rr in sh.iter_rows(min_row=hr+1,max_row=sh.max_row,max_col=n): for c in rr:c.font=Font(name='Arial',size=10);c.border=Border(bottom=thin);c.alignment=Alignment(vertical='top',wrap_text=True) sh.freeze_panes=f'A{hr+1}';sh.auto_filter.ref=f'A{hr}:{get_column_letter(n)}{sh.max_row}';sh.sheet_view.showGridLines=False counts=Counter(r['Ward'] for r in rows);title(ws,'UNIT D - WARD-WISE PATIENT REGISTRATION LIST',2);ws.append(['Source: IP Registration Report | Admission period: 01-09-2026 to 30-09-2026']);ws.merge_cells('A2:B2');ws['A2'].font=Font(name='Arial',italic=True,color='555555');ws.append(['Ward','No. of registrations']) for ward,n in sorted(counts.items()):ws.append([ward,n]) ws.append(['TOTAL',len(rows)]);style(ws,3,2);ws['A'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws['B'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws.column_dimensions['A'].width=42;ws.column_dimensions['B'].width=21 cols=['S. No.','Admission Date & Time','MRN','Visit ID','Patient Name','Age','Gender','Bed at Registration','Ward'] for ix,ward in enumerate(sorted(counts),1): sh=wb.create_sheet((f'{ix:02d} {ward.replace(" / ","-").replace(" - ","-")}')[:31]);title(sh,f'UNIT D - {ward}',9);sh.append(['Admission period: 01-09-2026 to 30-09-2026']);sh.merge_cells('A2:I2');sh['A2'].font=Font(name='Arial',italic=True,color='555555');sh.append(cols) for r in sorted((r for r in rows if r['Ward']==ward),key=lambda x:x['S. No.']):sh.append([r[c] for c in cols]) style(sh,3,9) for j,w in enumerate([9,20,16,16,37,7,10,24,34],1):sh.column_dimensions[get_column_letter(j)].width=w wb.save(out) print('Created:',out);print('Registrations:',len(rows));print('Wards:',sorted(counts.items())) # verify v=load_workbook(out,read_only=True);print('verify sheets',len(v.sheetnames),'summary total',v['Ward Summary'].cell(v['Ward Summary'].max_row,2).value) PY ls -lh "/tmp/workspace/ward-wise-unit-d/output/Unit_D_Ward_Wise_Patient_List.xlsx"

~/attachments

PDF table extraction spreadsheet ward-wise patient registration list

python - <<'PY' import pdfplumber p='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit e.pdf' with pdfplumber.open(p) as pdf: print('PAGES',len(pdf.pages)) for i,page in enumerate(pdf.pages): text=page.extract_text(x_tolerance=2,y_tolerance=3) or '' print('\n---PAGE',i+1,'---\n',text[:4000]) PY

mkdir -p "/tmp/workspace/ward-wise-unit-e/output" && python - <<'PY' import re, pdfplumber from collections import Counter from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter src='/tmp/workspace/attachments/b0ab73f4-6c19-45bc-9086-a0c126d89a6a/unit e.pdf'; out='/tmp/workspace/ward-wise-unit-e/output/Unit_E_Ward_Wise_Patient_List.xlsx' texts=[] with pdfplumber.open(src) as pdf: for pg in pdf.pages: t=pg.extract_text(x_tolerance=2,y_tolerance=3) or '' t=re.sub(r'Sardar Vallabhbhai Patel.*?AdmissionReqDate&Time\n','',t,flags=re.S) t=re.sub(r'S\.No AdmissionDate&Time MRN Visit id Patient Name Age Gender Bed Doctors Department Payor Ward\n','',t) t=re.sub(r'Printed Date / Time.*','',t,flags=re.S) texts.append(t.strip()) text='\n'.join(texts) starts=list(re.finditer(r'(?m)^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+',text)) def classify(block, bed): if 'GF-A2-RESUCITATIONS' in block or 'GF-A2-RESUSCITATIONS' in block or 'GF-A2-RESUSC' in block or 'GF-A2-RESUCITATIONS' in block or 'GF-A2-RESUCITATIONS' in block or 'GF-A2-RESUSCITATIONS' in block: match=re.search(r'(RESUSC|TRAUMA)-(\d+)',block) return 'GF-A2 Resuscitation / Trauma Ward', ('GF-A2-'+match.group(1)+'-'+match.group(2) if match else 'GF-A2 Resuscitation') if 'GF-A2-ER OBSERV' in block: match=re.search(r'OBSERV-(\d+)',block) return 'GF-A2 ER Observation Ward', ('GF-A2-ER OBSERV-'+match.group(1) if match else 'GF-A2 ER Observation') for prefix,ward in [('GF-A1-ICU','GF-A1 Emergency ICU'),('4F-B1-','4F-B1 General Ward'),('4F-B2-','4F-B2 General Ward'),('4F-C1-','4F-C1 General Ward'),('4F-C2-','4F-C2 General Ward'),('3F-C1-','3F-C1 General Ward - Male'),('3F-C2-','3F-C2 General Ward - Female'),('10F-B2-','10F-B2 ICU - Female'),('10F-B1-','10F-B1 ICU - Male'),('11F-A1-','11F-A1 General Ward - Male'),('11F-C1-','11F-C1 General Ward - Male'),('12F-A1-2S-','12F-A1 Twin Sharing'),('12F-A1-SP-','12F-A1 Special Ward')]: if bed.startswith(prefix): return ward,bed return 'Unclassified',bed rows=[] for i,m in enumerate(starts): block=text[m.start():starts[i+1].start() if i+1<len(starts) else len(text)].replace('\n',' ').strip() z=re.match(r'^(\d+)\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d{2}-\d{2}-\d{4}\s+\d{2}:\d{2})\s+(\d+)\s+(IP\d+)\s+(.*?)\s+UNIT(?: [A-Z])?\b',block) if not z: raise ValueError('base fail '+block) serial,admit,request,mrn,visit,pre=z.groups() a=re.match(r'^(.*?)\s+(\d+)\s+Y(?:\s+\d+\s+M\s+\d+\s+D)?\s+(Male|Female)\s+(.*)$',pre) if not a: raise ValueError('person fail '+pre) name,age,gender,bedpre=a.groups();ward,bed=classify(block,bedpre.strip()) rows.append({'S. No.':int(serial),'Admission Date & Time':admit,'MRN':mrn,'Visit ID':visit,'Patient Name':name.title(),'Age':int(age),'Gender':gender,'Bed at Registration':bed,'Ward':ward}) print('parsed',len(rows),'range',rows[0]['S. No.'],rows[-1]['S. No.']) print('unclassified', [r for r in rows if r['Ward']=='Unclassified']) assert len(rows)==159 and all(r['Ward']!='Unclassified' for r in rows) blue='1F4E78'; light='5B9BD5'; thin=Side(style='thin',color='D9E1F2') def add_title(ws,text,ncols): ws.append([text]); ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=ncols) ws['A1'].font=Font(name='Arial',size=13,bold=True,color='FFFFFF');ws['A1'].fill=PatternFill('solid',fgColor=blue);ws['A1'].alignment=Alignment(horizontal='center') def format_sheet(ws,headerrow,ncols): for c in ws[headerrow]: c.font=Font(name='Arial',bold=True,color='FFFFFF');c.fill=PatternFill('solid',fgColor=light);c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True) for rr in ws.iter_rows(min_row=headerrow+1,max_row=ws.max_row,max_col=ncols): for c in rr: c.font=Font(name='Arial',size=10);c.border=Border(bottom=thin);c.alignment=Alignment(vertical='top',wrap_text=True) ws.freeze_panes=f'A{headerrow+1}';ws.auto_filter.ref=f'A{headerrow}:{get_column_letter(ncols)}{ws.max_row}';ws.sheet_view.showGridLines=False wb=Workbook();ws=wb.active;ws.title='Ward Summary';counts=Counter(r['Ward'] for r in rows) add_title(ws,'UNIT E - WARD-WISE PATIENT REGISTRATION LIST',2);ws.append(['Source: IP Registration Report | Admission period: 01-09-2026 to 30-09-2026']);ws.merge_cells('A2:B2');ws['A2'].font=Font(name='Arial',italic=True,color='555555');ws.append(['Ward','No. of registrations']) for ward,n in sorted(counts.items()):ws.append([ward,n]) ws.append(['TOTAL',len(rows)]);format_sheet(ws,3,2);ws['A'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws['B'+str(ws.max_row)].font=Font(name='Arial',bold=True);ws.column_dimensions['A'].width=42;ws.column_dimensions['B'].width=21 cols=['S. No.','Admission Date & Time','MRN','Visit ID','Patient Name','Age','Gender','Bed at Registration','Ward'] for ix,ward in enumerate(sorted(counts),1): title=(f'{ix:02d} {ward.replace(" / ","-").replace(" - ","-")}')[:31]; sh=wb.create_sheet(title) add_title(sh,f'UNIT E - {ward}',9);sh.append(['Admission period: 01-09-2026 to 30-09-2026']);sh.merge_cells('A2:I2');sh['A2'].font=Font(name='Arial',italic=True,color='555555');sh.append(cols) for r in sorted((x for x in rows if x['Ward']==ward),key=lambda q:q['S. No.']):sh.append([r[c] for c in cols]) format_sheet(sh,3,9) for j,w in enumerate([9,20,16,16,37,7,10,24,34],1):sh.column_dimensions[get_column_letter(j)].width=w wb.save(out) check=load_workbook(out,read_only=True) print('saved',out,'sheets',len(check.sheetnames),'total',check['Ward Summary'].cell(check['Ward Summary'].max_row,2).value,'counts',sorted(counts.items())) PY ls -lh "/tmp/workspace/ward-wise-unit-e/output/Unit_E_Ward_Wise_Patient_List.xlsx"