今天在沈阳出差。入职第三个月，出的第三趟差。这次是质控中心的主题。
在本草的引导下，我发现我所在的中街原来是个繁华之地，周边竟有很多好吃的。今天就吃了熏肉卷饼。感觉不错！
这几天上课的时候，终于，一鼓作气，把1.2-1.3的大表，也就是临床科室主要医疗指标的表实现了自动化。实际上，入职三院前，我也是不会python的。而现在，可以把胶水语言一直写下去，基本熟练地适用openpyxl这个包，也是不容易了。未来还要加油。
把代码下载出来看了，竟然有1000行。下面的这一大坨，就是代码段了。
#!/usr/bin/env python
# coding: utf-8
# In[1]:
”’
1.2-1.3 临床科室主要医疗指标月报表
文件输入核查
1 上一年度同期月报表 /UDB/(yyyy-1)-m.xlsx
2 【可选】上一年度CMI新算法 /UDB/CMI统计(yyyy-1)年新分组器.xlsx’
3 当月住院报表 /NODE/住院工作报表(yyyy) 年.xlsx
4 当月手术工作量（例次） /NODE/(yyyy)年经营办手术例数.xlsx
5 当月门急诊报表 /NODE/门诊工作报表（分部门）(yyyy)年.xlsx
6 本年度上月报表 /UDB/(yyyy)-(m-1).xlsx
7 当月急诊留观室出院人次 var1
8 [可选]当月眼科手术总例次 var2
# 上一年采用直接写入的方式
# 当年采用字典结构写入
s1 构建产出表
s2 填充历史值
s3.1 填充当月出院值
s3.2 填充累计出院值
s4.1 填充当月手术例次
s4.2 填充累计手术例次
s5.1 填充当月门急诊
s5.2 填充累计门急诊
”’
ed = ‘2018-10-31’ # 统计截止日期
var1 = 2910 # 当月急诊留观室出院人次，病案科吴虎兵提供
var2 = 1149 #眼科总手术例次，眼科戴红老师提供
newcmi = 1 # cmi列不需要更新时，改为0
# In[2]:
dr = ‘/Users/y829/Desktop/NODE/’ # 输入文件位置
drb = ‘/Users/y829/Desktop/UDB/’ # 历史月报表位置
_dic3a = {} # 当月门急诊量字典
_dic3b = {} # 累计月门急诊量字典
_dic_pvt = {} # 病案科门急诊报表 科室名称转换
_dic_pvt2 = {} # 经营办手术例次表 科室名称转换
_dic4 = {} # 手术例次库（当月；累计）
_dic1 = {} #储存特殊科室的平均住院日
# wb1 去年表 表名称 UBD/(tyear-1)-(tmonth).xlsx
# wb2 住院工作报表yyyy年.xlsx
# wb3 yyyy年经营办手术例数
# wb4 门诊工作报表yyyy年
# wb5 本年度 上月 表名称 UBD/(tyear)-(tmonth-1).xlsx
## 定义科室字典
_dic_dp = {1:’全院’,2:’心血管内科’,3:’心血管综合病房’,4:’呼吸内科’,5:’内科/呼吸重症监护病房’,6:’内分泌内科’,7:’肾脏内科’,
8:’血液内科’,9:’风湿免疫科’,10:’感染疾病科’,11:’神经内科’,12:’消化科’,13:’普通外科’,14:’介入血管外科’,15:’骨科’,
16:’骨科北方病房’,17:’泌尿外科’,18:’胸外科’,19:’心脏外科’,20:’心脏外科重症监护病房’,21:’神经外科’,22:’综合外科和肛肠诊疗中心’,23:’妇科’,
24:’生殖医学中心’,25:’产科’,26:’儿科’,27:’新生儿重症监护病房’,28:’眼科’,29:’耳鼻喉科’,30:’口腔科’,31:’皮肤科’,32:’肿瘤化疗与放射病科’,
33:’肿瘤放疗科’,34:’职业病科’,35:’成形科’,36:’运动医学’,37:’运动医学四病房’,38:’康复医学科’,39:’老年病内科’,40:’老年病内科三病房’,
41:’综合外科’,42:’危重医学科’,43:’医疗美容科’,44:’疼痛科’,45:’急诊科’,46:’中医科’,47:’第二门诊部’,48:’党校院区’,49:’伤口治疗中心’}
dataset = {} # 历史值，数据结构
import pandas as pd
import numpy as np
import os, re
import openpyxl as pxl
endday = pd.Timestamp(ed)
tyear = endday.year
tmonth = endday.month
# In[3]:
# 写入表准备
from openpyxl import Workbook
wbt = Workbook()
sht1 = wbt.active
sht1.title = str(tmonth)
if tmonth > 1:
sht2 = wbt.create_sheet(‘1-‘ + str(tmonth))
wbt.save(dr +str(tyear) +’-‘ + str(tmonth) + ‘.xlsx’)
# In[4]:
# 历史值数据结构
len(_dic_dp)
for dep in _dic_dp:
dataset[dep] = {‘v1’:{}, ‘v2’:{}, ‘v3’:{}, ‘v4’:{}, ‘v5’:{}, ‘v6’:{}, ‘v7’:{}, ‘v8’:{}}
# unmark to check
# dataset
# In[5]:
# 产出表建构格式，写入科室列表
print(‘======构建产出表=======’)
sht1[‘A1’] = str(tyear) + ‘年’ + str(tmonth) + ‘月临床科室主要医疗指标与去年同期比较’
if tmonth > 1:
sht2[‘A1’] = str(tyear) + ‘年1-‘ + str(tmonth) + ‘月临床科室主要医疗指标与去年同期比较’
sht1[‘B2’] = ‘门诊量（人次）’
sht1[‘E2’] = ‘急诊量（人次）’
sht1[‘H2’] = ‘出院患者数（人次）’
sht1[‘K2’] = ‘手术量（例次）’
sht1[‘N2’] = ‘床位使用率（％）’
sht1[‘Q2’] = ‘平均住院日（天）’
sht1[‘T2’] = ‘术前平均住院日（天）’
sht1[‘W2’] = ‘CMI’
sht1[‘B3’] = str(tyear) + ‘年’
sht1[‘C3’] = str(tyear-1) + ‘年’
sht1[‘E3’] = str(tyear) + ‘年’
sht1[‘F3’] = str(tyear-1) + ‘年’
sht1[‘H3’] = str(tyear) + ‘年’
sht1[‘I3’] = str(tyear-1) + ‘年’
sht1[‘K3’] = str(tyear) + ‘年’
sht1[‘L3’] = str(tyear-1) + ‘年’
sht1[‘N3’] = str(tyear) + ‘年’
sht1[‘O3’] = str(tyear-1) + ‘年’
sht1[‘Q3’] = str(tyear) + ‘年’
sht1[‘R3’] = str(tyear-1) + ‘年’
sht1[‘T3’] = str(tyear) + ‘年’
sht1[‘U3’] = str(tyear-1) + ‘年’
sht1[‘W3’] = str(tyear) + ‘年’
sht1[‘X3’] = str(tyear-1) + ‘年’
sht1[‘D3’] = ‘增长（％）’
sht1[‘G3’] = ‘增长（％）’
sht1[‘J3’] = ‘增长（％）’
sht1[‘M3’] = ‘增长（％）’
sht1[‘P3’] = ‘增长（％）’
sht1[‘S3’] = ‘增长（％）’
sht1[‘V3’] = ‘增长（％）’
sht1[‘Y3’] = ‘增长（％）’
if tmonth > 1:
sht2[‘B2’] = ‘门诊量（人次）’
sht2[‘E2’] = ‘急诊量（人次）’
sht2[‘H2’] = ‘出院患者数（人次）’
sht2[‘K2’] = ‘手术量（例次）’
sht2[‘N2’] = ‘床位使用率（％）’
sht2[‘Q2’] = ‘平均住院日（天）’
sht2[‘T2’] = ‘术前平均住院日（天）’
sht2[‘W2’] = ‘CMI’
sht2[‘B3’] = str(tyear) + ‘年’
sht2[‘C3’] = str(tyear-1) + ‘年’
sht2[‘E3’] = str(tyear) + ‘年’
sht2[‘F3’] = str(tyear-1) + ‘年’
sht2[‘H3’] = str(tyear) + ‘年’
sht2[‘I3’] = str(tyear-1) + ‘年’
sht2[‘K3’] = str(tyear) + ‘年’
sht2[‘L3’] = str(tyear-1) + ‘年’
sht2[‘N3’] = str(tyear) + ‘年’
sht2[‘O3’] = str(tyear-1) + ‘年’
sht2[‘Q3’] = str(tyear) + ‘年’
sht2[‘R3’] = str(tyear-1) + ‘年’
sht2[‘T3’] = str(tyear) + ‘年’
sht2[‘U3’] = str(tyear-1) + ‘年’
sht2[‘W3’] = str(tyear) + ‘年’
sht2[‘X3’] = str(tyear-1) + ‘年’
sht2[‘D3’] = ‘增长（％）’
sht2[‘G3’] = ‘增长（％）’
sht2[‘J3’] = ‘增长（％）’
sht2[‘M3’] = ‘增长（％）’
sht2[‘P3’] = ‘增长（％）’
sht2[‘S3’] = ‘增长（％）’
sht2[‘V3’] = ‘增长（％）’
sht2[‘Y3’] = ‘增长（％）’
# 写入科室名称
i = 4
print(‘科室:’, end = ”)
for ii in range(1, len(_dic_dp)+1):
sht1[‘A’+str(i)] = _dic_dp.get(ii)
if tmonth > 1:
sht2[‘A’+str(i)] = _dic_dp.get(ii)
i = i + 1
ii = ii + 1
print(i, end = ‘,’)
# In[6]:
###############
# 填充当月表历史值
###############
# 先搞出2017年文件
# wb1 去年表 表名称 UBD/(tyear-1)-(tmonth).xlsx
wb1 = pxl.load_workbook(drb + str(tyear-1) + ‘-‘+ str(tmonth) + ‘.xlsx’, read_only=True, data_only = True)
sheet1a = wb1[str(tmonth) ]
if tmonth > 2:
sheet1b = wb1[‘1-‘+ str(tmonth) ]
# In[7]:
print(‘======填充历史值======’)
print(‘填充当月表历史值’)
for i in range(4,sht1.max_row+2):
ii = 1
for ii in range(4, sheet1a.max_row+3):
if sheet1a[‘A’ + str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘C’+str(i)] = sheet1a[‘B’ + str(ii)].value
sht1[‘F’+str(i)] = sheet1a[‘E’ + str(ii)].value
sht1[‘I’+str(i)] = sheet1a[‘H’ + str(ii)].value
sht1[‘L’+str(i)] = sheet1a[‘K’ + str(ii)].value
sht1[‘O’+str(i)] = sheet1a[‘N’ + str(ii)].value
sht1[‘R’+str(i)] = sheet1a[‘Q’ + str(ii)].value
sht1[‘U’+str(i)] = sheet1a[‘T’ + str(ii)].value
sht1[‘X’+str(i)] = sheet1a[‘W’ + str(ii)].value
print(‘>’, end=”)
print(‘ OK’)
if tmonth > 1:
print(‘填充累积表历史值’)
i = 1
for i in range(4,sht2.max_row+2):
ii = 1
for ii in range(4, sheet1b.max_row+3):
if sheet1b[‘A’ + str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘C’+str(i)] = sheet1b[‘B’ + str(ii)].value
sht2[‘F’+str(i)] = sheet1b[‘E’ + str(ii)].value
sht2[‘I’+str(i)] = sheet1b[‘H’ + str(ii)].value
sht2[‘L’+str(i)] = sheet1b[‘K’ + str(ii)].value
sht2[‘O’+str(i)] = sheet1b[‘N’ + str(ii)].value
sht2[‘R’+str(i)] = sheet1b[‘Q’ + str(ii)].value
sht2[‘U’+str(i)] = sheet1b[‘T’ + str(ii)].value
sht2[‘X’+str(i)] = sheet1b[‘W’ + str(ii)].value
print(‘>’, end=”)
print(‘ OK’)
wb1.close()
# In[8]:
print(‘======规范病案科的数据======’)
###
#读表前规范科室名称
###
_dic_standard_title = {
‘妇科内分泌病房’:’生殖医学中心’,
‘普外科’:’普通外科’,
‘胸外’:’胸外科’,
‘泌尿科’:’泌尿外科’,
‘呼吸科’:’呼吸内科’,
‘心血管’:’心血管内科’,
‘内分泌’:’内分泌内科’,
‘肾病科’:’肾脏内科’,
‘血液科’:’血液内科’,
‘重症科’:’危重医学科’,
‘老年病科’:’老年病内科’,
‘神经科’:’神经内科’,
‘康复中心’:’康复医学科’,
‘总 计：’:’全院’,
‘急诊科留观室’:’急诊科’,
‘二门诊’:’第二门诊部’,
‘肛肠病房’:’综合外科和肛肠诊疗中心’,
‘骨北’:’骨科北方病房’,
‘医疗美容科病房’:’医疗美容科’,
‘呼吸重症’:’内科/呼吸重症监护病房’,
‘老年病科三病房’:’老年病内科三病房’}
import sys, re, os
for dirPath, folderName, filenames in os.walk(dr):
for filename in filenames:
try:
wb_temp = pxl.load_workbook(os.path.join(dr, filename))
print(‘Checking’, filename,’:’)
for sheet in wb_temp:
print(sheet)
for col in sheet.iter_cols():
for cell in col:
if cell.value in _dic_standard_title:
cell.value = _dic_standard_title.get(cell.value)
except:
print(‘Workbook error’, filename, ‘\n’)
else:
wb_temp.save(os.path.join(dr, filename))
print(filename, ‘ checked, pass \n’)
wb_temp.close()
# In[9]:
wb2 = pxl.load_workbook(dr + ‘住院工作报表’ + str(tyear) + ‘年.xlsx’, read_only=True, data_only = True)
sheet2a = wb2[str(tmonth) + ‘月’]
if tmonth > 2:
sheet2b = wb2[‘1-‘ + str(tmonth) + ‘月’]
# In[10]:
###############
# 填充当月表当月值
###############
print(‘======填充当月值出院系列数据（出院人数、床位使用率、平均住院日、术前平均住院日）=======’)
print(‘急诊科>’)
i = 3
for i in range(3, sht1.max_row + 1):
if sht1[‘A’+str(i)].value == ‘急诊科’:
sht1[‘H’+str(i)] = var1
break
##########
### 填充当月出院
##########
_dic1 = {} #储存特殊科室的平均住院日
i = 4
for i in range(4, sht1.max_row): ##嵌套循环来源表抓数
if sht1[‘A’+str(i)].value == ‘内科/呼吸重症监护病房’:
ii = 4
for ii in range(4, sheet2a.max_row+1):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘H’+str(i)] = str(sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value) + ‘(‘ + str(sheet2a[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
_dic1[‘内科/呼吸重症监护病房’] = sheet2a[‘Q’+str(ii)].value / (sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value)
break
if sheet2a[‘H’+str(ii)].value is None:
sheet2a[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:内科/呼吸重症监护病房 出院人次可能出错’)
elif sht1[‘A’+str(i)].value == ‘心脏外科重症监护病房’:
ii = 4
for ii in range(4, sheet2a.max_row+1):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘H’+str(i)] = str(sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value) + ‘(‘ + str(sheet2a[‘F’+str(ii)].value) + ‘)’ # 出院人数(括号内为实际出院人数)
sht1[‘T’+str(i)] = sheet2a[‘AI’+str(ii)].value # 术前平均住院日
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
_dic1[‘心脏外科重症监护病房’] = sheet2a[‘Q’+str(ii)].value / (sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value)
break
if sheet2a[‘H’+str(ii)].value is None:
sheet2a[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:心脏外科重症监护病房出院人次可能出错’)
elif sht1[‘A’+str(i)].value == ‘新生儿重症监护病房’:
ii = 4
for ii in range(4, sheet2a.max_row+1):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘H’+str(i)] = str(sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value) + ‘(‘ + str(sheet2a[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
_dic1[‘新生儿重症监护病房’] = sheet2a[‘Q’+str(ii)].value / (sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value)
break
if sheet2a[‘H’+str(ii)].value is None:
sheet2a[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:新生儿重症监护病房出院人次可能出错’)
elif sht1[‘A’+str(i)].value == ‘危重医学科’:
ii = 4
for ii in range(4, sheet2a.max_row+1):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘H’+str(i)] = str(sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value) + ‘(‘ + str(sheet2a[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
_dic1[‘危重医学科’] = sheet2a[‘Q’+str(ii)].value / (sheet2a[‘F’+str(ii)].value + sheet2a[‘M’+str(ii)].value)
break
if sheet2a[‘H’+str(ii)].value is None:
sheet2a[‘H’+str(ii)] = ‘不应空值’
print(‘ATT:危重医学科出院人次可能出错’)
elif sht1[‘A’+str(i)].value == ‘神经内科’:
ii = 4
_a = 0
_b = 0
_c = 0
_d = 0
for ii in range(4, sheet2a.max_row+1):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
_a = sheet2a[‘F’+str(ii)].value
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
v8 = sheet2a[‘R’+str(ii)].value # 神经内科 出院者 占总床日数
if sheet2a[‘A’+str(ii)].value == ‘高干神经’:
_b = sheet2a[‘F’+str(ii)].value
v9 = sheet2a[‘R’+str(ii)].value # 高干神经 出院者 占总床日数
if _a * _b == 0:
print(‘ATT：神经内科出院人数可能出现错误’, _a, _b)
sht1[‘H’+str(i)] = ‘不应空值’
else:
sht1[‘H’+str(i)] = _a + _b # 出院人数
sht1[‘Q’+str(i)] = (v8 + v9) / (_a + _b) # 平均住院日 = （神内+高干神经 出院者 占总床日数）/ （出院人数）
else:
ii = 4
for ii in range(4, sheet2a.max_row+1):
if sht1[‘A’+str(i)].value == sheet2a[‘A’+str(ii)].value:
sht1[‘H’+str(i)] = sheet2a[‘F’+str(ii)].value # 出院人数
sht1[‘N’+str(i)] = sheet2a[‘T’+str(ii)].value # 床位使用率
sht1[‘Q’+str(i)] = sheet2a[‘W’+str(ii)].value # 平均住院日 （特殊科室平均住院日在_dic1中）
try:
if sht1[‘U’+str(i)].value > -1:
sht1[‘T’+str(i)] = sheet2a[‘AI’+str(ii)].value # 术前平均住院日
except:
pass
print(sht1[‘A’+str(i)].value, end = ‘>’)
break
# 特殊科室质控
i = 4
for i in range(4, sht1.max_row): ##嵌套循环来源表抓数
if sht1[‘A’+str(i)].value in _dic1:
sht1[‘Q’+str(i)] = _dic1[sht1[‘A’+str(i)].value]
#_dic1
# In[11]:
if tmonth > 1:
###############
# 填充累计表出院系列数（出院人数、床位使用率、平均住院日、术前平均住院日）
###############
print(‘======填充累计值出院系列数据=======’)
print(‘急诊科’, end = ‘>’)
i = 3
for i in range(3, sht2.max_row + 1):
if sht2[‘A’+str(i)].value == ‘急诊科’:
# sht1[‘H’+str(i)] = var1
sht2[‘H’+str(i)] = ‘ATT 待开发累计’
break
##########
### 填充累计出院
##########
_dic2 = {} #储存特殊科室的平均住院日
i = 4
for i in range(4, sht2.max_row): ##嵌套循环来源表抓数
if sht2[‘A’+str(i)].value == ‘内科/呼吸重症监护病房’:
ii = 4
for ii in range(4, sheet2b.max_row+1):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘H’+str(i)] = str(sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value) + ‘(‘ + str(sheet2b[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
_dic2[‘内科/呼吸重症监护病房’] = sheet2b[‘Q’+str(ii)].value / (sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value)
break
if sheet2b[‘H’+str(ii)].value is None:
sheet2b[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:内科/呼吸重症监护病房 出院人次可能出错’)
elif sht2[‘A’+str(i)].value == ‘心脏外科重症监护病房’:
ii = 4
for ii in range(4, sheet2b.max_row+1):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘H’+str(i)] = str(sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value) + ‘(‘ + str(sheet2b[‘F’+str(ii)].value) + ‘)’ # 出院人数(括号内为实际出院人数)
sht2[‘T’+str(i)] = sheet2b[‘AI’+str(ii)].value # 术前平均住院日
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
_dic2[‘心脏外科重症监护病房’] = sheet2b[‘Q’+str(ii)].value / (sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value)
break
if sheet2b[‘H’+str(ii)].value is None:
sheet2b[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:心脏外科重症监护病房出院人次可能出错’)
elif sht2[‘A’+str(i)].value == ‘新生儿重症监护病房’:
ii = 4
for ii in range(4, sheet2b.max_row+1):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘H’+str(i)] = str(sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value) + ‘(‘ + str(sheet2b[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
_dic2[‘新生儿重症监护病房’] = sheet2b[‘Q’+str(ii)].value / (sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value)
break
if sheet2b[‘H’+str(ii)].value is None:
sheet2b[‘H’+str(ii)] = ‘不应空值’ # 出院人数
print(‘ATT:新生儿重症监护病房出院人次可能出错’)
elif sht2[‘A’+str(i)].value == ‘危重医学科’:
ii = 4
for ii in range(4, sheet2b.max_row+1):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘H’+str(i)] = str(sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value) + ‘(‘ + str(sheet2b[‘F’+str(ii)].value) + ‘)’ # 出院人数
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
_dic2[‘危重医学科’] = sheet2b[‘Q’+str(ii)].value / (sheet2b[‘F’+str(ii)].value + sheet2b[‘M’+str(ii)].value)
break
if sheet2b[‘H’+str(ii)].value is None:
sheet2b[‘H’+str(ii)] = ‘不应空值’
print(‘ATT:危重医学科出院人次可能出错’)
elif sht2[‘A’+str(i)].value == ‘神经内科’:
ii = 4
_a = 0
_b = 0
_c = 0
_d = 0
for ii in range(4, sheet2b.max_row+1):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
_a = sheet2b[‘F’+str(ii)].value
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
_c = sheet2b[‘R’+str(ii)].value # 神经内科 出院者 占总床日数
if sheet2b[‘A’+str(ii)].value == ‘高干神经’:
_b = sheet2b[‘F’+str(ii)].value
_d = sheet2b[‘R’+str(ii)].value # 高干神经 出院者 占总床日数
if _a * _b == 0:
print(‘ATT：神经内科出院人数可能出现错误’, _a, _b)
sht2[‘H’+str(i)] = ‘不应空值’
else:
sht2[‘H’+str(i)] = _a + _b # 出院人数
sht2[‘Q’+str(i)] = (_c + _d) / (_a + _b) # 平均住院日 = （神内+高干神经 出院者 占总床日数）/ （出院人数）
else: # 其他非特殊科室
ii = 4
for ii in range(4, sheet2b.max_row+1):
if sht2[‘A’+str(i)].value == sheet2b[‘A’+str(ii)].value:
sht2[‘H’+str(i)] = sheet2b[‘F’+str(ii)].value # 出院人数
sht2[‘N’+str(i)] = sheet2b[‘T’+str(ii)].value # 床位使用率
sht2[‘Q’+str(i)] = sheet2b[‘W’+str(ii)].value # 平均住院日 （特殊科室平均住院日在_dic1中）
try:
if sht2[‘U’+str(i)].value > -1:
sht2[‘T’+str(i)] = sheet2b[‘AI’+str(ii)].value # 术前平均住院日
except:
pass
print(sht2[‘A’+str(i)].value, end = ‘>’)
break
# 特殊科室质控
i = 4
for i in range(4, sht2.max_row): ##嵌套循环来源表抓数
if sht2[‘A’+str(i)].value in _dic2:
sht2[‘Q’+str(i)] = _dic2[sht2[‘A’+str(i)].value]
_dic2
# In[ ]:
# In[12]:
_dic_cmi = {‘3′:’老年病内科三病房’,’7′:’儿科’,’10’:’眼科’,’11’:’耳鼻喉科’,’12’:’口腔科’,’13’:’皮肤科’,’14’:’医疗美容科’,
’19’:’肿瘤化疗与放射病科’,’21’:’康复医学科’,’22’:’运动医学’,’23’:’职业病科’,’27’:’疼痛科’,’301′:’呼吸内科’,
‘302’:’消化科’,’303′:’神经内科’,’304′:’心血管内科’,’305′:’血液内科’,’306′:’肾脏内科’,’307′:’内分泌内科’,
‘308’:’风湿免疫科’,’310′:’老年病内科’,’401′:’普通外科’,’402′:’神经外科’,’403′:’骨科’,’404′:’泌尿外科’,
‘405’:’胸外科’,’406′:’心脏外科’,’408′:’成形科’,’409′:’综合外科’,’501′:’妇科’,’502′:’产科’,’505′:’生殖医学中心’,
‘3209’:’介入血管外科’,’3210′:’肿瘤放疗科’,’5011′:’综合外科和肛肠诊疗中心’,’6102′:’内科/呼吸重症监护病房’,
‘6103’:’危重医学科’,’总计’:’全院’}
print(‘====写入本年CMI值====’)
wb9 = pxl.load_workbook(drb + ‘CMI统计’ + str(tyear) + ‘年.xlsx’, read_only=True, data_only = True)
sheet9a = wb9[str(tyear)+ ‘-‘ + str(tmonth) + ‘月’]
if tmonth > 1:
sheet9b = wb9[‘1-‘ + str(tmonth) +’月’]
i = 3
for i in range(3, sheet9a.max_row+1):
ii = 2
for ii in range(2, sht1.max_row):
if _dic_cmi[str(sheet9a[‘B’+str(i)].value)] == sht1[‘A’+str(ii)].value:
sht1[‘W’+str(ii)] = sheet9a[‘E’+str(i)].value
print(‘>’, end=”)
ii = ii + 1
print(‘ OK’)
if tmonth > 1:
i = 3
for i in range(3, sheet9b.max_row+1):
ii = 2
for ii in range(2, sht2.max_row):
if _dic_cmi[str(sheet9b[‘B’+str(i)].value)] == sht2[‘A’+str(ii)].value:
sht2[‘W’+str(ii)] = sheet9b[‘E’+str(i)].value
print(‘>’, end=”)
ii = ii + 1
print(‘ OK’)
# In[13]:
if newcmi == 1:
print(‘=====CMI历史值需要更新====’)
wb9 = pxl.load_workbook(drb + ‘CMI统计’ + str(tyear-1) + ‘年新分组器.xlsx’, read_only=True, data_only = True)
sheet9a = wb9[str(tyear-1)+ ‘-‘ + str(tmonth) + ‘月’]
if tmonth > 1:
sheet9b = wb9[‘1-‘ + str(tmonth) +’月’]
i = 3
for i in range(3, sheet9a.max_row+1):
ii = 2
for ii in range(2, sht1.max_row):
if _dic_cmi[str(sheet9a[‘B’+str(i)].value)] == sht1[‘A’+str(ii)].value:
sht1[‘X’+str(ii)] = sheet9a[‘E’+str(i)].value
print(‘>’, end=”)
ii = ii + 1
print(‘ OK’)
if tmonth > 1:
i = 3
for i in range(3, sheet9b.max_row+1):
ii = 2
for ii in range(2, sht2.max_row):
if _dic_cmi[str(sheet9b[‘B’+str(i)].value)] == sht2[‘A’+str(ii)].value:
sht2[‘X’+str(ii)] = sheet9b[‘E’+str(i)].value
print(‘>’, end=”)
ii = ii + 1
print(‘ OK’)
# In[14]:
print(‘=====更新当月手术例次====’)
wb3 = pxl.load_workbook(dr + str(tyear) + ‘年经营办手术例数.xlsx’, read_only=True, data_only = True)
sheet3 = wb3[str(tyear) + ‘-‘ + str(tmonth)]
if sheet3[‘H3’].value == var2:
print(‘checking: 眼科手术总例次一致 OK’)
else:
print(‘ATT: 眼科手术在经营办中未更新，或 程序中var2 未更新’)
# In[15]:
_dic4={}
_dic_pvt2 = {
‘普外’:’普通外科’,
‘泌尿’:’泌尿外科’,
‘骨科’:’骨科’,
‘胸科’:’胸外科’,
‘神外’:’神经外科’,
‘心外’:’心脏外科’,
‘介入血外’:’介入血管外科’,
‘妇科’:’妇科’,
‘产科’:’产科’,
‘耳科’:’耳鼻喉科’,
‘口腔’:’口腔科’,
‘成型’:’成形科’,
‘运动医学’:’运动医学’,
‘肾内’:’内科’,
‘儿科’:’内科’,
‘消化’:’内科’,
‘肿瘤’:’内科’,
‘呼吸’:’内科’,
‘危重’:’内科’,
‘疼痛’:’疼痛科’,
‘眼科’:’眼科’,
‘皮科’:’内科’,
‘伤口治疗中心’:’伤口治疗中心’,
‘北方：骨科’:’骨科北方病房’,
‘党校：肛肠’:’综合外科和肛肠诊疗中心’,
‘生殖中心：’:’生殖医学中心’,
‘美容成型’:’医疗美容科’,
‘北方：泌尿’:’泌尿外科’}
for i in range(1,len(_dic_dp)+1):
_dic4[_dic_dp[i]] = {‘当月’:0,’累计’:0}
# In[16]:
i = 4
print(‘抓取当月手术例次，构建字典’, end=”)
for i in range(4, sheet3.max_row):
if sheet3[‘A’+str(i)].value in _dic_pvt2:
if _dic_pvt2.get(sheet3[‘A’+str(i)].value) in _dic4:
_x = _dic_pvt2.get(sheet3[‘A’+str(i)].value)
_dic4[_x][‘当月’] = sheet3[‘F’+str(i)].value + _dic4[_x][‘当月’]
print(‘>’, end=”)
elif sheet3[‘A’+str(i)].value is None:
pass
elif sheet3[‘A’+str(i)].value == ‘总计’:
break
elif sheet3[‘A’+str(i)].value == ‘北方、生殖中心、党校、美容中心’:
pass
elif sheet3[‘A’+str(i)].value == ‘科室’:
pass
elif sheet3[‘A’+str(i)].value == ‘合计’:
pass
else:
print(‘ATT: ‘ , sheet3[‘A’+str(i)].value , ‘不存在转换关系，请检查’)
_dic4[‘全院’][‘当月’] = sheet3[‘B’+ str(sheet3.max_row)].value
i = 4
for i in range(4, sheet3.max_row):
if _dic_pvt2.get(sheet3[‘A’+str(i)].value) == ‘医疗美容科’:
_dic4[‘医疗美容科’][‘当月’] = sheet3[‘B’+str(i)].value
print(‘ OK’)
#_dic4
# In[17]:
print(‘=====写入手术例次当月数据====’)
i = 4
for i in range(4, sht1.max_row):
if sht1[‘A’+str(i)].value in _dic4:
if _dic4[sht1[‘A’+str(i)].value][‘当月’] > 0:
sht1[‘K’+str(i)] = _dic4[sht1[‘A’+str(i)].value][‘当月’]
for i in range(4, sht1.max_row):
if sht1[‘A’+str(i)].value == ‘眼科’:
sht1[‘K’+str(i)] = var2
# In[18]:
print(‘=====更新门急诊数据====’)
wb4 = pxl.load_workbook(dr + ‘门诊工作报表’ + str(tyear) + ‘年.xlsx’, read_only=True, data_only = True)
sheet4a = wb4[‘分部报表’ + str(tmonth) + ‘月’]
if tmonth > 2:
sheet4b = wb4[‘分部报表1-‘ + str(tmonth) + ‘月’]
# In[19]:
_dic_pvt = {
‘本部合计’:’全院’,
‘变态反应’:’职业病科’,
‘产科’:’产科’,
‘肠道门诊’:’感染疾病科’,
‘儿科’:’儿科’,
‘儿童健康发展门诊’:’儿科’,
‘耳鼻喉科’:’耳鼻喉科’,
‘风湿免疫科’:’风湿免疫科’,
‘妇科’:’妇科’,
‘感染疾病科’:’感染疾病科’,
‘肛肠科’:’综合外科和肛肠诊疗中心’,
‘高干门诊’:’老年病内科’,
‘高干泌尿外门诊’:’泌尿外科’,
‘高干神经门诊’:’神经内科’,
‘骨科’:’骨科’,
‘骨质疏松门诊’:’康复医学科’,
‘康复医学科’:’康复医学科’,
‘核素治疗门诊’:’独立’,
‘呼吸’:’呼吸内科’,
‘急诊内科’:’急诊科’,
‘计划生育门诊’:’妇科’,
‘减肥门诊’:’运动医学’,
‘介入血管外科’:’介入血管外科’,
‘戒烟咨询门诊’:’呼吸内科’,
‘康复中心’:’康复医学科’,
‘口腔科’:’口腔科’,
‘老年病内科门诊’:’老年病内科’,
‘美容_成形’:’医疗美容科’,
‘美容_皮肤科’:’皮肤科’,
‘美容_中医’:’中医科’,
‘美容成形’:’成形科’,
‘泌尿’:’泌尿外科’,
‘男科’:’泌尿外科’,
‘内分泌’:’内分泌内科’,
‘内分泌内科’:’内分泌内科’,
‘皮肤科’:’皮肤科’,
‘伤口治疗中心’:’伤口治疗中心’,
‘神经科’:’神经内科’,
‘神经内科’:’神经内科’,
‘神经外科’:’神经外科’,
‘肾病’:’肾脏内科’,
‘生殖中心’:’生殖医学中心’,
‘疼痛及介入超声门诊’:’疼痛科’,
‘疼痛及肿瘤CT介入门诊’:’独立’,
‘疼痛门诊’:’疼痛科’,
‘临床营养治疗门诊’:’独立’,
‘外科’:’普通外科’,
‘西医肝炎’:’感染疾病科’,
‘消化科’:’消化科’,
‘心理咨询’:’独立’,
‘心血管’:’心血管内科’,
‘心血管内科’:’心血管内科’,
‘心脏外科’:’心脏外科’,
‘胸外科’:’胸外科’,
‘胸外’:’胸外科’,
‘血液’:’血液内科’,
‘眼科’:’眼科’,
‘营养咨询门诊’:’独立’,
‘院内职工保健’:’内科’,
‘运动医学’:’运动医学’,
‘针灸科’:’中医科’,
‘职业病科’:’职业病科’,
‘中医科’:’中医科’,
‘肿瘤放疗门诊’:’肿瘤放疗科’,
‘肿瘤化疗与放射病门诊’:’肿瘤化疗与放射病科’,
‘党校合计’:’全院’,
‘南区耳鼻喉科’:’耳鼻喉科’,
‘南区妇科’:’妇科’,
‘南区肛肠科’:’综合外科和肛肠诊疗中心’,
‘南区骨科’:’骨科’,
‘南区呼吸内科’:’呼吸内科’,
‘南区急诊科’:’内科’,
‘南区康复科’:’康复医学科’,
‘南区口腔科’:’口腔科’,
‘南区老年内科’:’老年病内科’,
‘南区泌尿科’:’泌尿外科’,
‘南区内分泌科’:’内分泌内科’,
‘南区内科’:’内科’,
‘南区皮肤科’:’皮肤科’,
‘南区神经科’:’神经内科’,
‘南区肾内科’:’肾脏内科’,
‘南区外科’:’普通外科’,
‘南区消化内科’:’消化科’,
‘南区心血管内科’:’心血管内科’,
‘南区眼科’:’眼科’,
‘南区肿瘤化疗与放疗病科’:’肿瘤化疗与放射病科’,
‘南区中医科’:’中医科’,
‘二门诊合计’:’全院’,
‘二门儿科’:’儿科’,
‘二门耳鼻喉’:’耳鼻喉科’,
‘二门妇科’:’妇科’,
‘二门骨科’:’骨科’,
‘二门口腔科’:’口腔科’,
‘二门内分泌’:’内分泌内科’,
‘二门内科’:’内科’,
‘二门皮肤科’:’皮肤科’,
‘二门神经科’:’神经内科’,
‘二门外科’:’普通外科’,
‘二门心血管’:’心血管内科’,
‘二门眼科’:’眼科’,
‘二门预防保健科’:’独立’,
‘二门运医按摩’:’独立’,
‘二门中医科’:’中医科’}
# In[20]:
i = 1
for i in range(1,len(_dic_dp)+1):
_dic3a[_dic_dp[i]] = {‘门诊量’:0,’急诊量’:0}
_dic3b[_dic_dp[i]] = {‘门诊量’:0,’急诊量’:0}
print(‘=======写入当月及门急诊量=======’)
# In[21]:
i = 4
print(‘抓取当月门急诊数据，构建字典’, end=”)
for i in range(4, sheet4a.max_row):
if sheet4a[‘A’+str(i)].value in _dic_pvt:
if _dic_pvt.get(sheet4a[‘A’+str(i)].value) in _dic3a:
_x = _dic_pvt.get(sheet4a[‘A’+str(i)].value)
_dic3a[_x][‘门诊量’] = _dic3a[_x][‘门诊量’] + sheet4a[‘E’+str(i)].value
_dic3a[_x][‘急诊量’] = _dic3a[_x][‘急诊量’] + sheet4a[‘C’+str(i)].value
print(‘>’, end=”)
elif sheet4a[‘A’+str(i)].value is None:
pass
elif len(sheet4a[‘A’+str(i)].value) > 15:
print(‘ OK’)
else:
print(‘ATT: ‘ , sheet4a[‘A’+str(i)].value , ‘可能为新，不存在转换关系，请检查’)
# 特殊部门处理
for i in range(4, sheet4a.max_row):
if sheet4a[‘A’+str(i)].value == ‘二门诊合计’:
_dic3a[‘第二门诊部’][‘门诊量’] = sheet4a[‘E’+str(i)].value
if sheet4a[‘A’+str(i)].value == ‘党校合计’:
_dic3a[‘党校院区’][‘门诊量’] = sheet4a[‘E’+str(i)].value
_dic3a[‘党校院区’][‘急诊量’] = sheet4a[‘C’+str(i)].value
print(‘抓取累计门急诊数据，构建字典’, end=”)
for i in range(4, sheet4b.max_row):
if sheet4b[‘A’+str(i)].value in _dic_pvt:
if _dic_pvt.get(sheet4b[‘A’+str(i)].value) in _dic3b:
_x = _dic_pvt.get(sheet4b[‘A’+str(i)].value)
_dic3b[_x][‘门诊量’] = _dic3b[_x][‘门诊量’] + sheet4b[‘E’+str(i)].value
_dic3b[_x][‘急诊量’] = _dic3b[_x][‘急诊量’] + sheet4b[‘C’+str(i)].value
print(‘>’, end=”)
elif sheet4b[‘A’+str(i)].value is None:
pass
elif len(sheet4b[‘A’+str(i)].value) > 15:
print(‘ OK’)
else:
print(‘ATT: ‘ , sheet4b[‘A’+str(i)].value , ‘可能为新，不存在转换关系，请检查’)
# 特殊部门处理
for i in range(4, sheet4b.max_row):
if sheet4b[‘A’+str(i)].value == ‘二门诊合计’:
_dic3b[‘第二门诊部’][‘门诊量’] = sheet4b[‘E’+str(i)].value
if sheet4b[‘A’+str(i)].value == ‘党校合计’:
_dic3b[‘党校院区’][‘门诊量’] = sheet4b[‘E’+str(i)].value
_dic3b[‘党校院区’][‘急诊量’] = sheet4b[‘C’+str(i)].value
# _dic3a #需要核对时unmark
#_dic3b #需要核对时unmark
# In[22]:
i = 4
for i in range(4, sht1.max_row):
if sht1[‘A’+str(i)].value in _dic3a:
if _dic3a[sht1[‘A’+str(i)].value][‘门诊量’] >0:
sht1[‘B’+str(i)] = _dic3a[sht1[‘A’+str(i)].value][‘门诊量’]
if _dic3a[sht1[‘A’+str(i)].value][‘急诊量’] > 0:
sht1[‘E’+str(i)] = _dic3a[sht1[‘A’+str(i)].value][‘急诊量’]
if sht2[‘A’+str(i)].value in _dic3b:
if _dic3b[sht2[‘A’+str(i)].value][‘门诊量’] > 0:
sht2[‘B’+str(i)] = _dic3b[sht2[‘A’+str(i)].value][‘门诊量’]
if _dic3b[sht2[‘A’+str(i)].value][‘急诊量’] > 0:
sht2[‘E’+str(i)] = _dic3b[sht2[‘A’+str(i)].value][‘急诊量’]
# In[23]:
# wb5 本年度 上月 表名称 UBD/(tyear)-(tmonth-1).xlsx
wb5 = pxl.load_workbook(drb + str(tyear) + ‘-‘+ str(tmonth-1) + ‘.xlsx’, read_only=True, data_only = True)
if tmonth > 1:
sheet5 = wb5[‘1-‘+ str(tmonth-1) ]
else:
sheet5 = wb5[str(tmonth-1) ]
# In[24]:
print(‘=======计算和写入手术例次累计值=======’)
i = 4
for i in range(4, sheet5.max_row):
if sheet5[‘A’+str(i)].value in _dic4:
try:
if sheet5[‘A’+str(i)].value == ‘眼科’:
_dic4[sheet5[‘A’+str(i)].value][‘累计’] = sheet5[‘K’+str(i)].value + var2
else:
if sheet5[‘A’+str(i)].value == ‘急诊科’:
_f = sheet5[‘H’+str(i)].value
_dic4[sheet5[‘A’+str(i)].value][‘累计’] = sheet5[‘K’+str(i)].value + _dic4[sheet5[‘A’+str(i)].value][‘当月’]
print(‘>’, end=”)
except:
pass
i = 4
for i in range(4, sht2.max_row):
if _dic4[sht2[‘A’ + str(i)].value][‘累计’] > 0:
sht2[‘K’ + str(i)] = _dic4[sht2[‘A’ + str(i)].value][‘累计’]
print(‘>’, end=”)
print(‘ OK’)
# _dic4
# In[25]:
print(‘=======打补丁======’)
i = 4
for i in range (4, sht2.max_row):
if sht2[‘A’+str(i)].value == ‘急诊科’:
sht2[‘H’+str(i)] = _f + var1
i = 4
for i in range (4, sht1.max_row):
if sht1[‘A’+str(i)].value == ‘运动医学四病房’:
for ii in range(4, sheet2a.max_row):
if sheet2a[‘A’+str(ii)].value == sht1[‘A’+str(i)].value:
sht1[‘T’+str(i)] = sheet2a[‘AI’+str(ii)].value #术前平均住院日
i = 4
for i in range (4, sht2.max_row):
if sht2[‘A’+str(i)].value == ‘运动医学四病房’:
for ii in range(4, sheet2b.max_row):
if sheet2b[‘A’+str(ii)].value == sht2[‘A’+str(i)].value:
sht2[‘T’+str(i)] = sheet2b[‘AI’+str(ii)].value #术前平均住院日
# In[26]:
wbt.save(dr +str(tyear) +’-‘ + str(tmonth) + ‘.xlsx’)
# In[27]:
print(‘ATT: Do not forget to change the subtitles under the printing version\n\n’)
print(‘GREAT congratulations from y829 on 18-11-15’)
# In[ ]: