Excel数据提取与统计分析

Posted on

感谢你的反馈！这个错误是因为在较新的 pandas 版本中，DataFrame.append 方法已经被弃用，并建议使用 pd.concat 来代替。我们可以通过使用 pd.concat 来解决这个问题。

以下是修正后的代码，使用 pd.concat 来替代 append 方法：

修正后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 统计检查类别
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 增加汇总行
summary_row = pd.DataFrame({
    '项目': ['汇总'],
    '本月数量': [center_df['本月数量'].sum()],
    '上月数量': [center_df['上月数量'].sum()],
    '同比变化（%）': [calculate_change(center_df['本月数量'].sum(), center_df['上月数量'].sum())],
    '备注': ['']
})
center_df = pd.concat([center_df, summary_row], ignore_index=True)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        else:
            doctor_stats[doctor]['其他'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        # 按照模板的列顺序更新数据
        doctor_df.loc[index, '胃镜'] = doctor_stats_feb[doctor]['胃镜']
        doctor_df.loc[index, '无痛胃镜'] = doctor_stats_feb[doctor]['无痛胃镜']
        doctor_df.loc[index, '肠镜'] = doctor_stats_feb[doctor]['肠镜']
        doctor_df.loc[index, '无痛肠镜'] = doctor_stats_feb[doctor]['无痛肠镜']
        doctor_df.loc[index, 'ERCP'] = doctor_stats_feb[doctor]['ERCP']
        doctor_df.loc[index, 'EMR/APC'] = doctor_stats_feb[doctor]['EMR/APC']
        doctor_df.loc[index, 'ESD'] = doctor_stats_feb[doctor]['ESD']
        doctor_df.loc[index, '止血术'] = doctor_stats_feb[doctor]['止血术']
        doctor_df.loc[index, '扩张术'] = doctor_stats_feb[doctor]['扩张术']
        doctor_df.loc[index, '异物摄取'] = doctor_stats_feb[doctor]['异物摄取']
        doctor_df.loc[index, '其他'] = doctor_stats_feb[doctor]['其他']
        doctor_df.loc[index, '病例数'] = doctor_stats_feb[doctor]['病例数']
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = pd.DataFrame({
    '检查医师': ['汇总'],
    '胃镜': [doctor_df['胃镜'].sum()],
    '无痛胃镜': [doctor_df['无痛胃镜'].sum()],
    '肠镜': [doctor_df['肠镜'].sum()],
    '无痛肠镜': [doctor_df['无痛肠镜'].sum()],
    'ERCP': [doctor_df['ERCP'].sum()],
    'EMR/APC': [doctor_df['EMR/APC'].sum()],
    'ESD': [doctor_df['ESD'].sum()],
    '止血术': [doctor_df['止血术'].sum()],
    '扩张术': [doctor_df['扩张术'].sum()],
    '异物摄取': [doctor_df['异物摄取'].sum()],
    '其他': [doctor_df['其他'].sum()],
    '病例数': [doctor_df['病例数'].sum()],
    '百分比': [round((doctor_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)]
})
doctor_df = pd.concat([doctor_df, summary_row], ignore_index=True)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        else:
            nurse_stats[nurse]['其他'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        # 按照模板的列顺序更新数据
        nurse_df.loc[index, '胃镜'] = nurse_stats_feb[nurse]['胃镜']
        nurse_df.loc[index, '无痛胃镜'] = nurse_stats_feb[nurse]['无痛胃镜']
        nurse_df.loc[index, '肠镜'] = nurse_stats_feb[nurse]['肠镜']
        nurse_df.loc[index, '无痛肠镜'] = nurse_stats_feb[nurse]['无痛肠镜']
        nurse_df.loc[index, 'ERCP'] = nurse_stats_feb[nurse]['ERCP']
        nurse_df.loc[index, 'EMR/APC'] = nurse_stats_feb[nurse]['EMR/APC']
        nurse_df.loc[index, 'ESD'] = nurse_stats_feb[nurse]['ESD']
        nurse_df.loc[index, '止血术'] = nurse_stats_feb[nurse]['止血术']
        nurse_df.loc[index, '扩张术'] = nurse_stats_feb[nurse]['扩张术']
        nurse_df.loc[index, '异物摄取'] = nurse_stats_feb[nurse]['异物摄取']
        nurse_df.loc[index, '其他'] = nurse_stats_feb[nurse]['其他']
        nurse_df.loc[index, '病例数'] = nurse_stats_feb[nurse]['病例数']
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = pd.DataFrame({
    '护士': ['汇总'],
    '胃镜': [nurse_df['胃镜'].sum()],
    '无痛胃镜': [nurse_df['无痛胃镜'].sum()],
    '肠镜': [nurse_df['肠镜'].sum()],
    '无痛肠镜': [nurse_df['无痛肠镜'].sum()],
    'ERCP': [nurse_df['ERCP'].sum()],
    'EMR/APC': [nurse_df['EMR/APC'].sum()],
    'ESD': [nurse_df['ESD'].sum()],
    '止血术': [nurse_df['止血术'].sum()],
    '扩张术': [nurse_df['扩张术'].sum()],
    '异物摄取': [nurse_df['异物摄取'].sum()],
    '其他': [nurse_df['其他'].sum()],
    '病例数': [nurse_df['病例数'].sum()],
    '百分比': [round((nurse_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)]
})
nurse_df = pd.concat([nurse_df, summary_row], ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 替换 append 为 pd.concat：

    • 使用 pd.concat 来替代 append 方法，确保代码兼容较新的 pandas 版本。

  2. 保持列顺序：

    • 在更新数据时，严格按照模板的列顺序更新，确保“其他”列位于“病例数”之前。

运行说明：

  1. 运行代码后，生成的 updated_output.xlsx 文件将包含更新后的统计数据。
  2. 检查“其他”列是否位于“病例数”之前，并确保“汇总”行正确统计了各项目的总数。

如果还有其他问题，请随时告诉我！

时间: 2025-03-14T22:23:52

👤 用户

我有内镜中心1月份及2月份的工作量统计表格，分别为1.xls,2.xls{项目项都一样),请根据我提供的output.xlsx模板（包含：内镜中心工作量统计，医生工作量统计，护士工作量统计），把二月份数据2.xls提取到的数据填入到各个表中，并生成表格。在内镜中心工作量统计中病例数为“胃镜”“无痛胃镜”“肠镜”“无痛肠镜”“ERCP”之和，并加入本月数量和上月数量对比，并计算同比百分比变化，取小数点后两位填入相应的表格中。医生工作量统计中额外规则：病例数为“胃镜”“无痛胃镜”“肠镜”“无痛肠镜”“ERCP”之和。百分比为病例数占所有病例数的百分比，取小数点后两位。护士工作量统计额外规则：病例数为“胃镜”“无痛胃镜”“肠镜”“无痛肠镜”“ERCP”之和。百分比为病例数占所有病例数的百分比，取小数点后两位。帮我用python写一下语句。

数据提取规则如下：
根据“检查类别”列，进行如下提取(英文字母一律不计大小写)：
是“胃镜”提取到胃镜
是“无痛胃镜”提取到无痛胃镜
是“肠镜”提取到肠镜
是“无痛肠镜”提取到无痛肠镜
是“十二指肠镜”提取到ERCP
其余提取为其他。

根据“镜下诊断”列，进行如下提取(英文字母一律不计大小写)：
包含“扩张”提取到扩张术
包含“esd”但不包含“esd术后”提取到ESD
包含“emr”提取到EMR/APC
包含“apc”提取到EMR/APC
包含“止血”提取到止血术
包含“异物”提取到异物摄取

“报告医师”列中为检查医师
“报告医师”列中为护士

时间: 2025-03-14T20:36:22

🤖 DeepSeek (deepseek-chat)

为了完成这个任务，我们可以使用Python的pandas库来处理Excel文件。以下是一个示例代码，它将根据你提供的规则从2.xls文件中提取数据，并将其填充到output.xlsx模板中。

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
import pandas as pd

# 读取2.xls文件
df = pd.read_excel('2.xls', sheet_name='导出数据')

# 初始化统计字典
center_stats = {
    '胃镜': 0,
    '无痛胃镜': 0,
    '肠镜': 0,
    '无痛肠镜': 0,
    'ERCP': 0,
    'EMR/APC': 0,
    'ESD': 0,
    '止血术': 0,
    '异物摄取': 0,
    '扩张术': 0,
    '其他': 0
}

doctor_stats = {}
nurse_stats = {}

# 遍历每一行数据
for index, row in df.iterrows():
    category = row['检查类别'].lower()
    diagnosis = str(row['镜下诊断']).lower()
    doctor = row['报告医师']
    nurse = row['助手']

    # 统计内镜中心工作量
    if '胃镜' in category:
        center_stats['胃镜'] += 1
    elif '无痛胃镜' in category:
        center_stats['无痛胃镜'] += 1
    elif '肠镜' in category:
        center_stats['肠镜'] += 1
    elif '无痛肠镜' in category:
        center_stats['无痛肠镜'] += 1
    elif '十二指肠镜' in category:
        center_stats['ERCP'] += 1
    else:
        center_stats['其他'] += 1

    # 统计医生工作量
    if doctor not in doctor_stats:
        doctor_stats[doctor] = {
            '胃镜': 0,
            '无痛胃镜': 0,
            '肠镜': 0,
            '无痛肠镜': 0,
            'ERCP': 0,
            'EMR/APC': 0,
            'ESD': 0,
            '止血术': 0,
            '扩张术': 0,
            '异物摄取': 0,
            '病例数': 0
        }
    if '胃镜' in category:
        doctor_stats[doctor]['胃镜'] += 1
    elif '无痛胃镜' in category:
        doctor_stats[doctor]['无痛胃镜'] += 1
    elif '肠镜' in category:
        doctor_stats[doctor]['肠镜'] += 1
    elif '无痛肠镜' in category:
        doctor_stats[doctor]['无痛肠镜'] += 1
    elif '十二指肠镜' in category:
        doctor_stats[doctor]['ERCP'] += 1

    # 统计护士工作量
    if nurse not in nurse_stats:
        nurse_stats[nurse] = {
            '胃镜': 0,
            '无痛胃镜': 0,
            '肠镜': 0,
            '无痛肠镜': 0,
            'ERCP': 0,
            'EMR/APC': 0,
            'ESD': 0,
            '止血术': 0,
            '扩张术': 0,
            '异物摄取': 0,
            '病例数': 0
        }
    if '胃镜' in category:
        nurse_stats[nurse]['胃镜'] += 1
    elif '无痛胃镜' in category:
        nurse_stats[nurse]['无痛胃镜'] += 1
    elif '肠镜' in category:
        nurse_stats[nurse]['肠镜'] += 1
    elif '无痛肠镜' in category:
        nurse_stats[nurse]['无痛肠镜'] += 1
    elif '十二指肠镜' in category:
        nurse_stats[nurse]['ERCP'] += 1

    # 根据镜下诊断统计
    if '扩张' in diagnosis:
        center_stats['扩张术'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['扩张术'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['扩张术'] += 1
    if 'esd' in diagnosis and 'esd术后' not in diagnosis:
        center_stats['ESD'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['ESD'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['ESD'] += 1
    if 'emr' in diagnosis:
        center_stats['EMR/APC'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['EMR/APC'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['EMR/APC'] += 1
    if 'apc' in diagnosis:
        center_stats['EMR/APC'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['EMR/APC'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['EMR/APC'] += 1
    if '止血' in diagnosis:
        center_stats['止血术'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['止血术'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['止血术'] += 1
    if '异物' in diagnosis:
        center_stats['异物摄取'] += 1
        if doctor in doctor_stats:
            doctor_stats[doctor]['异物摄取'] += 1
        if nurse in nurse_stats:
            nurse_stats[nurse]['异物摄取'] += 1

# 计算病例数
center_stats['病例数'] = center_stats['胃镜'] + center_stats['无痛胃镜'] + center_stats['肠镜'] + center_stats['无痛肠镜'] + center_stats['ERCP']

for doctor in doctor_stats:
    doctor_stats[doctor]['病例数'] = doctor_stats[doctor]['胃镜'] + doctor_stats[doctor]['无痛胃镜'] + doctor_stats[doctor]['肠镜'] + doctor_stats[doctor]['无痛肠镜'] + doctor_stats[doctor]['ERCP']

for nurse in nurse_stats:
    nurse_stats[nurse]['病例数'] = nurse_stats[nurse]['胃镜'] + nurse_stats[nurse]['无痛胃镜'] + nurse_stats[nurse]['肠镜'] + nurse_stats[nurse]['无痛肠镜'] + nurse_stats[nurse]['ERCP']

# 计算百分比
total_cases = center_stats['病例数']

for doctor in doctor_stats:
    doctor_stats[doctor]['百分比'] = round((doctor_stats[doctor]['病例数'] / total_cases) * 100, 2)

for nurse in nurse_stats:
    nurse_stats[nurse]['百分比'] = round((nurse_stats[nurse]['病例数'] / total_cases) * 100, 2)

# 读取output.xlsx模板
template = pd.ExcelFile('output.xlsx')

# 更新内镜中心工作量统计
center_df = pd.read_excel(template, sheet_name='内镜中心工作量统计')
center_df.loc[0, '本月数量'] = center_stats['胃镜']
center_df.loc[1, '本月数量'] = center_stats['无痛胃镜']
center_df.loc[2, '本月数量'] = center_stats['胃镜'] + center_stats['无痛胃镜']
center_df.loc[3, '本月数量'] = center_stats['无痛胃镜']
center_df.loc[4, '本月数量'] = center_stats['肠镜']
center_df.loc[5, '本月数量'] = center_stats['无痛肠镜']
center_df.loc[6, '本月数量'] = center_stats['EMR/APC']
center_df.loc[7, '本月数量'] = center_stats['ESD']
center_df.loc[8, '本月数量'] = center_stats['ERCP']
center_df.loc[9, '本月数量'] = center_stats['止血术']
center_df.loc[10, '本月数量'] = center_stats['异物摄取']
center_df.loc[11, '本月数量'] = center_stats['扩张术']
center_df.loc[12, '本月数量'] = center_stats['其他']
center_df.loc[13, '本月数量'] = center_stats['病例数']

# 计算同比变化
for i in range(len(center_df)):
    if center_df.loc[i, '上月数量'] != 0:
        center_df.loc[i, '同比变化（%）'] = round((center_df.loc[i, '本月数量'] - center_df.loc[i, '上月数量']) / center_df.loc[i, '上月数量'] * 100, 2)

# 更新医生工作量统计
doctor_df = pd.read_excel(template, sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats:
        doctor_df.loc[index, '胃镜'] = doctor_stats[doctor]['胃镜']
        doctor_df.loc[index, '无痛胃镜'] = doctor_stats[doctor]['无痛胃镜']
        doctor_df.loc[index, '肠镜'] = doctor_stats[doctor]['肠镜']
        doctor_df.loc[index, '无痛肠镜'] = doctor_stats[doctor]['无痛肠镜']
        doctor_df.loc[index, 'EMR/APC'] = doctor_stats[doctor]['EMR/APC']
        doctor_df.loc[index, 'ESD'] = doctor_stats[doctor]['ESD']
        doctor_df.loc[index, 'ERCP'] = doctor_stats[doctor]['ERCP']
        doctor_df.loc[index, '止血术'] = doctor_stats[doctor]['止血术']
        doctor_df.loc[index, '扩张术'] = doctor_stats[doctor]['扩张术']
        doctor_df.loc[index, '异物摄取'] = doctor_stats[doctor]['异物摄取']
        doctor_df.loc[index, '病例数'] = doctor_stats[doctor]['病例数']
        doctor_df.loc[index, '百分比'] = doctor_stats[doctor]['百分比']

# 更新护士工作量统计
nurse_df = pd.read_excel(template, sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats:
        nurse_df.loc[index, '胃镜'] = nurse_stats[nurse]['胃镜']
        nurse_df.loc[index, '无痛胃镜'] = nurse_stats[nurse]['无痛胃镜']
        nurse_df.loc[index, '肠镜'] = nurse_stats[nurse]['肠镜']
        nurse_df.loc[index, '无痛肠镜'] = nurse_stats[nurse]['无痛肠镜']
        nurse_df.loc[index, 'EMR/APC'] = nurse_stats[nurse]['EMR/APC']
        nurse_df.loc[index, 'ESD'] = nurse_stats[nurse]['ESD']
        nurse_df.loc[index, 'ERCP'] = nurse_stats[nurse]['ERCP']
        nurse_df.loc[index, '止血术'] = nurse_stats[nurse]['止血术']
        nurse_df.loc[index, '扩张术'] = nurse_stats[nurse]['扩张术']
        nurse_df.loc[index, '异物摄取'] = nurse_stats[nurse]['异物摄取']
        nurse_df.loc[index, '病例数'] = nurse_stats[nurse]['病例数']
        nurse_df.loc[index, '百分比'] = nurse_stats[nurse]['百分比']

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

这个代码会读取2.xls文件中的数据，并根据规则提取和统计相关信息，然后更新output.xlsx模板中的各个工作表，并生成一个新的Excel文件updated_output.xlsx。请确保你已经安装了pandas库，并且2.xls和output.xlsx文件在正确的路径下。

时间: 2025-03-14T20:36:22

👤 用户

似乎缺少了和一月份数据1.xls对比啊，而且你这个代码看起来非常的长，在内镜中心工作量统计中病例数为“胃镜”“无痛胃镜”“肠镜”“无痛肠镜”“ERCP”之和，并加入本月数量和上月数量对比，并计算同比百分比变化，取小数点后两位填入相应的表格中。

时间: 2025-03-14T20:40:00

🤖 DeepSeek (deepseek-chat)

感谢你的反馈！你提到的两点非常重要：一是需要与一月份数据进行对比，二是代码可以进一步优化。以下是改进后的代码，它会从1.xls和2.xls中提取数据，并计算同比变化，同时代码更加简洁和模块化。

改进后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = row['检查类别'].lower()
        diagnosis = str(row['镜下诊断']).lower()

        # 统计检查类别
        if '胃镜' in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        elif '十二指肠镜' in category:
            stats['ERCP'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    # 医生和护士工作量统计可以根据需要继续补充

代码说明：

  1. 统计函数 (count_stats)：

    • 该函数从数据框中提取并统计每种检查类别和镜下诊断的数量。
    • 计算病例数为“胃镜”“无痛胃镜”“肠镜”“无痛肠镜”“ERCP”之和。

  2. 同比变化计算 (calculate_change)：

    • 计算本月数量与上月数量的百分比变化，保留两位小数。

  3. 更新内镜中心工作量统计：

    • 从1.xls和2.xls中提取数据，分别统计1月和2月的工作量。
    • 将统计结果填充到output.xlsx的“内镜中心工作量统计”表中，并计算同比变化。

  4. 保存结果：

    • 将更新后的数据保存到updated_output.xlsx中。

下一步：

  • 如果需要更新“医生工作量统计”和“护士工作量统计”，可以按照类似的逻辑扩展代码。
  • 确保1.xls和2.xls的文件路径正确，并且output.xlsx的模板格式与代码中的列名一致。

运行此代码后，updated_output.xlsx中的“内镜中心工作量统计”表将包含本月数量、上月数量和同比变化。

时间: 2025-03-14T20:40:00

👤 用户

完整代码来一下吗

时间: 2025-03-14T20:41:05

🤖 DeepSeek (deepseek-chat)

以下是完整的代码，包含内镜中心工作量统计、医生工作量统计和护士工作量统计的更新逻辑。代码会从1.xls和2.xls中提取数据，计算同比变化，并更新到output.xlsx模板中。

完整代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = row['检查类别'].lower()
        diagnosis = str(row['镜下诊断']).lower()

        # 统计检查类别
        if '胃镜' in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        elif '十二指肠镜' in category:
            stats['ERCP'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = row['检查类别'].lower()
        diagnosis = str(row['镜下诊断']).lower()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        elif '十二指肠镜' in category:
            doctor_stats[doctor]['ERCP'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            doctor_df.loc[index, col] = doctor_stats_feb[doctor][col]
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = row['检查类别'].lower()
        diagnosis = str(row['镜下诊断']).lower()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        elif '十二指肠镜' in category:
            nurse_stats[nurse]['ERCP'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            nurse_df.loc[index, col] = nurse_stats_feb[nurse][col]
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

代码功能：

  1. 内镜中心工作量统计：

    • 从1.xls和2.xls中提取数据，计算本月数量、上月数量和同比变化。
    • 更新到output.xlsx的“内镜中心工作量统计”表中。

  2. 医生工作量统计：

    • 统计每位医生的检查数量，并计算百分比。
    • 更新到output.xlsx的“医生工作量统计”表中。

  3. 护士工作量统计：

    • 统计每位护士的检查数量，并计算百分比。
    • 更新到output.xlsx的“护士工作量统计”表中。

  4. 保存结果：

    • 将更新后的数据保存到updated_output.xlsx中。

运行说明：

  • 确保1.xls、2.xls和output.xlsx文件在相同目录下。
  • 运行代码后，生成的updated_output.xlsx将包含更新后的统计数据。

如果有任何问题，欢迎随时反馈！

时间: 2025-03-14T20:41:05

👤 用户

有问题，通过上述代码，统计出来的无痛胃镜 无痛肠镜 和 ERCP都是零

时间: 2025-03-14T20:53:17

🤖 DeepSeek (deepseek-chat)

感谢你的反馈！问题可能出在数据提取逻辑上，特别是检查类别的匹配规则。我们需要确保代码能够正确识别“无痛胃镜”“无痛肠镜”和“ERCP”等检查类别。以下是修正后的代码，并增加了调试信息来帮助排查问题。

修正后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 调试信息：打印检查类别
        print(f"检查类别: {category}")

        # 统计检查类别
        if '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 打印1月和2月的统计数据
print("1月统计数据:", stats_jan)
print("2月统计数据:", stats_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            doctor_df.loc[index, col] = doctor_stats_feb[doctor][col]
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            nurse_df.loc[index, col] = nurse_stats_feb[nurse][col]
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 检查类别匹配规则：

    • 确保“无痛胃镜”和“无痛肠镜”被正确识别。
    • 增加对“十二指肠镜”和“ERCP”的识别逻辑。

  2. 调试信息：

    • 打印每条数据的检查类别，帮助排查问题。

  3. 去除空格和大小写问题：

    • 使用.strip()去除前后空格，并使用.lower()统一转为小写，避免匹配失败。

运行说明：

  • 运行代码后，检查控制台输出的调试信息，确保检查类别被正确识别。
  • 如果仍有问题，请提供部分数据样本，我可以进一步调整代码。

希望这次能解决问题！如果还有其他疑问，请随时告诉我。

时间: 2025-03-14T20:53:17

👤 用户

还是有个问题，为什么ERCP统计不到呢？在原始数据’检查类别’中十二指肠镜被提取到了ERCP中了啊，为什么在工作量统计表格中ERCP都显示为0呢？

时间: 2025-03-14T20:59:36

🤖 DeepSeek (deepseek-chat)

感谢你的耐心反馈！根据你的描述，问题可能出在以下几个地方：

  1. 检查类别的匹配逻辑：

    • 虽然代码中已经尝试匹配“十二指肠镜”和“ERCP”，但可能由于数据格式或大小写问题，匹配失败。

  2. 数据格式问题：

    • 原始数据中的“检查类别”可能包含额外的空格、特殊字符或大小写不一致，导致匹配失败。

  3. 统计逻辑问题：

    • 在统计逻辑中，可能没有正确地将“十二指肠镜”或“ERCP”归类到ERCP中。

修正后的代码：

以下是修正后的代码，重点解决ERCP统计不到的问题：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 调试信息：打印检查类别
        print(f"检查类别: {category}")

        # 统计检查类别
        if '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
            print(f"找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 打印1月和2月的统计数据
print("1月统计数据:", stats_jan)
print("2月统计数据:", stats_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
            print(f"医生 {doctor} 找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            doctor_df.loc[index, col] = doctor_stats_feb[doctor][col]
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        elif '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
            print(f"护士 {nurse} 找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            nurse_df.loc[index, col] = nurse_stats_feb[nurse][col]
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 增加调试信息：

    • 在匹配到“十二指肠镜”或“ERCP”时，打印调试信息，帮助确认是否匹配成功。

  2. 检查类别匹配逻辑：

    • 确保“十二指肠镜”和“ERCP”被正确匹配，并归类到ERCP中。

  3. 去除空格和大小写问题：

    • 使用.strip()去除前后空格，并使用.lower()统一转为小写，避免匹配失败。

运行说明：

  • 运行代码后，检查控制台输出的调试信息，确认“十二指肠镜”和“ERCP”是否被正确匹配。
  • 如果仍有问题，请提供部分数据样本，我可以进一步调整代码。

希望这次能解决问题！如果还有其他疑问，请随时告诉我。

时间: 2025-03-14T20:59:36

👤 用户

我知道问题所在了，十二指肠镜被统计到了肠镜里面了

时间: 2025-03-14T21:04:44

🤖 DeepSeek (deepseek-chat)

感谢你的反馈！问题确实出在检查类别的匹配逻辑上。由于“十二指肠镜”和“肠镜”都包含“肠镜”关键字，导致“十二指肠镜”被错误地归类到了“肠镜”中。我们需要调整匹配逻辑，确保“十二指肠镜”被正确归类到ERCP中。

以下是修正后的代码，重点解决“十二指肠镜”被错误归类的问题：

修正后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 调试信息：打印检查类别
        print(f"检查类别: {category}")

        # 统计检查类别
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
            print(f"找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP
        elif '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 打印1月和2月的统计数据
print("1月统计数据:", stats_jan)
print("2月统计数据:", stats_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
            print(f"医生 {doctor} 找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP
        elif '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            doctor_df.loc[index, col] = doctor_stats_feb[doctor][col]
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
            print(f"护士 {nurse} 找到 ERCP: {category}")  # 调试信息：打印匹配到的 ERCP
        elif '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '病例数']:
            nurse_df.loc[index, col] = nurse_stats_feb[nurse][col]
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 优先匹配“十二指肠镜”和“ERCP”：

    • 在检查类别的匹配逻辑中，优先判断是否为“十二指肠镜”或“ERCP”，确保它们不会被错误归类到“肠镜”中。

  2. 调试信息：

    • 在匹配到“十二指肠镜”或“ERCP”时，打印调试信息，帮助确认是否匹配成功。

  3. 逻辑顺序调整：

    • 将“十二指肠镜”和“ERCP”的匹配逻辑放在最前面，避免被其他条件覆盖。

运行说明：

  1. 运行代码后，检查控制台输出的调试信息，确认“十二指肠镜”和“ERCP”是否被正确匹配。
  2. 如果仍有问题，请提供部分数据样本，我可以进一步调整代码。

希望这次能彻底解决问题！如果还有其他疑问，请随时告诉我。

时间: 2025-03-14T21:04:44

👤 用户

我注意到有“其他”这部分工作量没有统计医生工作表和护士表格中，帮我修改下代码。此外，能不能在三个表格中，最下面一行增加一个“汇总”，统计不同项目的数据个数。更新后的表格如下

时间: 2025-03-14T21:26:55

🤖 DeepSeek (deepseek-chat)

好的！根据你的需求，我将对代码进行以下修改：

  1. 在医生工作表和护士工作表中增加“其他”统计：

    • 在医生和护士的统计逻辑中，增加对“其他”类别的统计。

  2. 在三个表格的最下面一行增加“汇总”：

    • 在“内镜中心工作量统计”“医生工作量统计”和“护士工作量统计”中，增加一行“汇总”，统计各项目的总数。

以下是更新后的代码：

更新后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 统计检查类别
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 增加汇总行
summary_row = {
    '项目': '汇总',
    '本月数量': center_df['本月数量'].sum(),
    '上月数量': center_df['上月数量'].sum(),
    '同比变化（%）': calculate_change(center_df['本月数量'].sum(), center_df['上月数量'].sum()),
    '备注': ''
}
center_df = center_df.append(summary_row, ignore_index=True)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        else:
            doctor_stats[doctor]['其他'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '其他', '病例数']:
            doctor_df.loc[index, col] = doctor_stats_feb[doctor][col]
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = {
    '检查医师': '汇总',
    '胃镜': doctor_df['胃镜'].sum(),
    '无痛胃镜': doctor_df['无痛胃镜'].sum(),
    '肠镜': doctor_df['肠镜'].sum(),
    '无痛肠镜': doctor_df['无痛肠镜'].sum(),
    'ERCP': doctor_df['ERCP'].sum(),
    'EMR/APC': doctor_df['EMR/APC'].sum(),
    'ESD': doctor_df['ESD'].sum(),
    '止血术': doctor_df['止血术'].sum(),
    '扩张术': doctor_df['扩张术'].sum(),
    '异物摄取': doctor_df['异物摄取'].sum(),
    '其他': doctor_df['其他'].sum(),
    '病例数': doctor_df['病例数'].sum(),
    '百分比': round((doctor_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)
}
doctor_df = doctor_df.append(summary_row, ignore_index=True)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        else:
            nurse_stats[nurse]['其他'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        for col in ['胃镜', '无痛胃镜', '肠镜', '无痛肠镜', 'ERCP', 'EMR/APC', 'ESD', '止血术', '扩张术', '异物摄取', '其他', '病例数']:
            nurse_df.loc[index, col] = nurse_stats_feb[nurse][col]
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = {
    '护士': '汇总',
    '胃镜': nurse_df['胃镜'].sum(),
    '无痛胃镜': nurse_df['无痛胃镜'].sum(),
    '肠镜': nurse_df['肠镜'].sum(),
    '无痛肠镜': nurse_df['无痛肠镜'].sum(),
    'ERCP': nurse_df['ERCP'].sum(),
    'EMR/APC': nurse_df['EMR/APC'].sum(),
    'ESD': nurse_df['ESD'].sum(),
    '止血术': nurse_df['止血术'].sum(),
    '扩张术': nurse_df['扩张术'].sum(),
    '异物摄取': nurse_df['异物摄取'].sum(),
    '其他': nurse_df['其他'].sum(),
    '病例数': nurse_df['病例数'].sum(),
    '百分比': round((nurse_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)
}
nurse_df = nurse_df.append(summary_row, ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

更新内容：

  1. 增加“其他”统计：

    • 在医生和护士的统计逻辑中，增加了对“其他”类别的统计。

  2. 增加“汇总”行：

    • 在“内镜中心工作量统计”“医生工作量统计”和“护士工作量统计”中，增加了一行“汇总”，统计各项目的总数。

  3. 优化代码结构：

    • 将统计逻辑和汇总逻辑分开，使代码更清晰易读。

运行说明：

  1. 运行代码后，生成的updated_output.xlsx文件将包含更新后的统计数据。
  2. 检查“汇总”行是否正确统计了各项目的总数。

如果还有其他需求或问题，请随时告诉我！

时间: 2025-03-14T21:26:55

👤 用户

C:\Users\Notebook\Desktop\test2>script.py
Traceback (most recent call last):
File “C:\Users\Notebook\Desktop\test2\script.py”, line 80, in
center_df = center_df.append(summary_row, ignore_index=True)
^^^^^^^^^^^^^^^^
File “C:\Users\Notebook\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\core\generic.py”, line 6299, in getattr
return object.getattribute(self, name)

时间: 2025-03-14T21:30:06

🤖 DeepSeek (deepseek-chat)

The server is busy. Please try again later.

时间: 2025-03-14T21:30:06

👤 用户

C:\Users\Notebook\Desktop\test2>script.py
Traceback (most recent call last):
File “C:\Users\Notebook\Desktop\test2\script.py”, line 80, in
center_df = center_df.append(summary_row, ignore_index=True)
^^^^^^^^^^^^^^^^
File “C:\Users\Notebook\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\core\generic.py”, line 6299, in getattr
return object.getattribute(self, name)

时间: 2025-03-14T21:31:15

🤖 DeepSeek (deepseek-chat)

The server is busy. Please try again later.

时间: 2025-03-14T21:31:15

👤 用户

为什么我通过如下代码，生产的其他项在最后一列，而不是应该在我给你的表格模板中在病例数之前？

时间: 2025-03-14T21:47:59

🤖 DeepSeek (deepseek-chat)

感谢你的反馈！问题出在代码中列的顺序没有完全按照模板的列顺序来排列。我们需要确保在更新数据时，列的顺序与模板中的顺序一致。

以下是修正后的代码，确保“其他”列位于“病例数”之前，同时保持模板的列顺序：

修正后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 统计检查类别
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 增加汇总行
summary_row = {
    '项目': '汇总',
    '本月数量': center_df['本月数量'].sum(),
    '上月数量': center_df['上月数量'].sum(),
    '同比变化（%）': calculate_change(center_df['本月数量'].sum(), center_df['上月数量'].sum()),
    '备注': ''
}
center_df = center_df.append(summary_row, ignore_index=True)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        else:
            doctor_stats[doctor]['其他'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        # 按照模板的列顺序更新数据
        doctor_df.loc[index, '胃镜'] = doctor_stats_feb[doctor]['胃镜']
        doctor_df.loc[index, '无痛胃镜'] = doctor_stats_feb[doctor]['无痛胃镜']
        doctor_df.loc[index, '肠镜'] = doctor_stats_feb[doctor]['肠镜']
        doctor_df.loc[index, '无痛肠镜'] = doctor_stats_feb[doctor]['无痛肠镜']
        doctor_df.loc[index, 'ERCP'] = doctor_stats_feb[doctor]['ERCP']
        doctor_df.loc[index, 'EMR/APC'] = doctor_stats_feb[doctor]['EMR/APC']
        doctor_df.loc[index, 'ESD'] = doctor_stats_feb[doctor]['ESD']
        doctor_df.loc[index, '止血术'] = doctor_stats_feb[doctor]['止血术']
        doctor_df.loc[index, '扩张术'] = doctor_stats_feb[doctor]['扩张术']
        doctor_df.loc[index, '异物摄取'] = doctor_stats_feb[doctor]['异物摄取']
        doctor_df.loc[index, '其他'] = doctor_stats_feb[doctor]['其他']
        doctor_df.loc[index, '病例数'] = doctor_stats_feb[doctor]['病例数']
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = {
    '检查医师': '汇总',
    '胃镜': doctor_df['胃镜'].sum(),
    '无痛胃镜': doctor_df['无痛胃镜'].sum(),
    '肠镜': doctor_df['肠镜'].sum(),
    '无痛肠镜': doctor_df['无痛肠镜'].sum(),
    'ERCP': doctor_df['ERCP'].sum(),
    'EMR/APC': doctor_df['EMR/APC'].sum(),
    'ESD': doctor_df['ESD'].sum(),
    '止血术': doctor_df['止血术'].sum(),
    '扩张术': doctor_df['扩张术'].sum(),
    '异物摄取': doctor_df['异物摄取'].sum(),
    '其他': doctor_df['其他'].sum(),
    '病例数': doctor_df['病例数'].sum(),
    '百分比': round((doctor_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)
}
doctor_df = doctor_df.append(summary_row, ignore_index=True)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        else:
            nurse_stats[nurse]['其他'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        # 按照模板的列顺序更新数据
        nurse_df.loc[index, '胃镜'] = nurse_stats_feb[nurse]['胃镜']
        nurse_df.loc[index, '无痛胃镜'] = nurse_stats_feb[nurse]['无痛胃镜']
        nurse_df.loc[index, '肠镜'] = nurse_stats_feb[nurse]['肠镜']
        nurse_df.loc[index, '无痛肠镜'] = nurse_stats_feb[nurse]['无痛肠镜']
        nurse_df.loc[index, 'ERCP'] = nurse_stats_feb[nurse]['ERCP']
        nurse_df.loc[index, 'EMR/APC'] = nurse_stats_feb[nurse]['EMR/APC']
        nurse_df.loc[index, 'ESD'] = nurse_stats_feb[nurse]['ESD']
        nurse_df.loc[index, '止血术'] = nurse_stats_feb[nurse]['止血术']
        nurse_df.loc[index, '扩张术'] = nurse_stats_feb[nurse]['扩张术']
        nurse_df.loc[index, '异物摄取'] = nurse_stats_feb[nurse]['异物摄取']
        nurse_df.loc[index, '其他'] = nurse_stats_feb[nurse]['其他']
        nurse_df.loc[index, '病例数'] = nurse_stats_feb[nurse]['病例数']
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = {
    '护士': '汇总',
    '胃镜': nurse_df['胃镜'].sum(),
    '无痛胃镜': nurse_df['无痛胃镜'].sum(),
    '肠镜': nurse_df['肠镜'].sum(),
    '无痛肠镜': nurse_df['无痛肠镜'].sum(),
    'ERCP': nurse_df['ERCP'].sum(),
    'EMR/APC': nurse_df['EMR/APC'].sum(),
    'ESD': nurse_df['ESD'].sum(),
    '止血术': nurse_df['止血术'].sum(),
    '扩张术': nurse_df['扩张术'].sum(),
    '异物摄取': nurse_df['异物摄取'].sum(),
    '其他': nurse_df['其他'].sum(),
    '病例数': nurse_df['病例数'].sum(),
    '百分比': round((nurse_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)
}
nurse_df = nurse_df.append(summary_row, ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 列顺序调整：

    • 在更新医生和护士工作量统计时，严格按照模板的列顺序更新数据，确保“其他”列位于“病例数”之前。

  2. 代码优化：

    • 将列顺序的逻辑明确化，避免列顺序混乱。

运行说明：

  1. 运行代码后，生成的updated_output.xlsx文件将包含更新后的统计数据。
  2. 检查“其他”列是否位于“病例数”之前，并确保“汇总”行正确统计了各项目的总数。

如果还有其他问题，请随时告诉我！

时间: 2025-03-14T21:47:59

👤 用户

Traceback (most recent call last):
File “C:\Users\Notebook\Desktop\test2\script.py”, line 80, in
center_df = center_df.append(summary_row, ignore_index=True)
^^^^^^^^^^^^^^^^
File “C:\Users\Notebook\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\core\generic.py”, line 6299, in getattr
return object.getattribute(self, name)
~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^

时间: 2025-03-14T22:23:52

🤖 DeepSeek (deepseek-chat)

感谢你的反馈！这个错误是因为在较新的 pandas 版本中，DataFrame.append 方法已经被弃用，并建议使用 pd.concat 来代替。我们可以通过使用 pd.concat 来解决这个问题。

以下是修正后的代码，使用 pd.concat 来替代 append 方法：

修正后的代码：

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
import pandas as pd

# 读取1月份和2月份的数据
df_jan = pd.read_excel('1.xls', sheet_name='导出数据')
df_feb = pd.read_excel('2.xls', sheet_name='导出数据')

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        'ERCP': 0,
        'EMR/APC': 0,
        'ESD': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()  # 确保去除前后空格并转为小写
        diagnosis = str(row['镜下诊断']).lower().strip()

        # 统计检查类别
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            stats['胃镜'] += 1
        elif '无痛胃镜' in category:
            stats['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            stats['肠镜'] += 1
        elif '无痛肠镜' in category:
            stats['无痛肠镜'] += 1
        else:
            stats['其他'] += 1

        # 统计镜下诊断
        if '扩张' in diagnosis:
            stats['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            stats['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            stats['EMR/APC'] += 1
        if '止血' in diagnosis:
            stats['止血术'] += 1
        if '异物' in diagnosis:
            stats['异物摄取'] += 1

    # 计算病例数
    stats['病例数'] = stats['胃镜'] + stats['无痛胃镜'] + stats['肠镜'] + stats['无痛肠镜'] + stats['ERCP']
    return stats

# 获取1月和2月的统计数据
stats_jan = count_stats(df_jan)
stats_feb = count_stats(df_feb)

# 计算同比变化
def calculate_change(current, previous):
    if previous == 0:
        return 0
    return round((current - previous) / previous * 100, 2)

# 更新内镜中心工作量统计
center_df = pd.read_excel('output.xlsx', sheet_name='内镜中心工作量统计')
center_df['上月数量'] = center_df['项目'].map(stats_jan)
center_df['本月数量'] = center_df['项目'].map(stats_feb)
center_df['同比变化（%）'] = center_df.apply(lambda row: calculate_change(row['本月数量'], row['上月数量']), axis=1)

# 增加汇总行
summary_row = pd.DataFrame({
    '项目': ['汇总'],
    '本月数量': [center_df['本月数量'].sum()],
    '上月数量': [center_df['上月数量'].sum()],
    '同比变化（%）': [calculate_change(center_df['本月数量'].sum(), center_df['上月数量'].sum())],
    '备注': ['']
})
center_df = pd.concat([center_df, summary_row], ignore_index=True)

# 统计医生工作量
def count_doctor_stats(df):
    doctor_stats = {}
    for _, row in df.iterrows():
        doctor = row['报告医师']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            doctor_stats[doctor]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            doctor_stats[doctor]['胃镜'] += 1
        elif '无痛胃镜' in category:
            doctor_stats[doctor]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            doctor_stats[doctor]['肠镜'] += 1
        elif '无痛肠镜' in category:
            doctor_stats[doctor]['无痛肠镜'] += 1
        else:
            doctor_stats[doctor]['其他'] += 1

        if '扩张' in diagnosis:
            doctor_stats[doctor]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            doctor_stats[doctor]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            doctor_stats[doctor]['EMR/APC'] += 1
        if '止血' in diagnosis:
            doctor_stats[doctor]['止血术'] += 1
        if '异物' in diagnosis:
            doctor_stats[doctor]['异物摄取'] += 1

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            doctor_stats[doctor]['肠镜'] +
            doctor_stats[doctor]['无痛肠镜'] +
            doctor_stats[doctor]['ERCP']
        )
    return doctor_stats

# 获取1月和2月的医生统计数据
doctor_stats_jan = count_doctor_stats(df_jan)
doctor_stats_feb = count_doctor_stats(df_feb)

# 更新医生工作量统计
doctor_df = pd.read_excel('output.xlsx', sheet_name='医生工作量统计')
for index, row in doctor_df.iterrows():
    doctor = row['检查医师']
    if doctor in doctor_stats_feb:
        # 按照模板的列顺序更新数据
        doctor_df.loc[index, '胃镜'] = doctor_stats_feb[doctor]['胃镜']
        doctor_df.loc[index, '无痛胃镜'] = doctor_stats_feb[doctor]['无痛胃镜']
        doctor_df.loc[index, '肠镜'] = doctor_stats_feb[doctor]['肠镜']
        doctor_df.loc[index, '无痛肠镜'] = doctor_stats_feb[doctor]['无痛肠镜']
        doctor_df.loc[index, 'ERCP'] = doctor_stats_feb[doctor]['ERCP']
        doctor_df.loc[index, 'EMR/APC'] = doctor_stats_feb[doctor]['EMR/APC']
        doctor_df.loc[index, 'ESD'] = doctor_stats_feb[doctor]['ESD']
        doctor_df.loc[index, '止血术'] = doctor_stats_feb[doctor]['止血术']
        doctor_df.loc[index, '扩张术'] = doctor_stats_feb[doctor]['扩张术']
        doctor_df.loc[index, '异物摄取'] = doctor_stats_feb[doctor]['异物摄取']
        doctor_df.loc[index, '其他'] = doctor_stats_feb[doctor]['其他']
        doctor_df.loc[index, '病例数'] = doctor_stats_feb[doctor]['病例数']
        doctor_df.loc[index, '百分比'] = round((doctor_stats_feb[doctor]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = pd.DataFrame({
    '检查医师': ['汇总'],
    '胃镜': [doctor_df['胃镜'].sum()],
    '无痛胃镜': [doctor_df['无痛胃镜'].sum()],
    '肠镜': [doctor_df['肠镜'].sum()],
    '无痛肠镜': [doctor_df['无痛肠镜'].sum()],
    'ERCP': [doctor_df['ERCP'].sum()],
    'EMR/APC': [doctor_df['EMR/APC'].sum()],
    'ESD': [doctor_df['ESD'].sum()],
    '止血术': [doctor_df['止血术'].sum()],
    '扩张术': [doctor_df['扩张术'].sum()],
    '异物摄取': [doctor_df['异物摄取'].sum()],
    '其他': [doctor_df['其他'].sum()],
    '病例数': [doctor_df['病例数'].sum()],
    '百分比': [round((doctor_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)]
})
doctor_df = pd.concat([doctor_df, summary_row], ignore_index=True)

# 统计护士工作量
def count_nurse_stats(df):
    nurse_stats = {}
    for _, row in df.iterrows():
        nurse = row['助手']
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                'ERCP': 0,
                'EMR/APC': 0,
                'ESD': 0,
                '止血术': 0,
                '扩张术': 0,
                '异物摄取': 0,
                '其他': 0,
                '病例数': 0
            }

        if '十二指肠镜' in category or 'ercp' in category:
            nurse_stats[nurse]['ERCP'] += 1
        elif '胃镜' in category and '无痛' not in category:
            nurse_stats[nurse]['胃镜'] += 1
        elif '无痛胃镜' in category:
            nurse_stats[nurse]['无痛胃镜'] += 1
        elif '肠镜' in category and '无痛' not in category:
            nurse_stats[nurse]['肠镜'] += 1
        elif '无痛肠镜' in category:
            nurse_stats[nurse]['无痛肠镜'] += 1
        else:
            nurse_stats[nurse]['其他'] += 1

        if '扩张' in diagnosis:
            nurse_stats[nurse]['扩张术'] += 1
        if 'esd' in diagnosis and 'esd术后' not in diagnosis:
            nurse_stats[nurse]['ESD'] += 1
        if 'emr' in diagnosis or 'apc' in diagnosis:
            nurse_stats[nurse]['EMR/APC'] += 1
        if '止血' in diagnosis:
            nurse_stats[nurse]['止血术'] += 1
        if '异物' in diagnosis:
            nurse_stats[nurse]['异物摄取'] += 1

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            nurse_stats[nurse]['肠镜'] +
            nurse_stats[nurse]['无痛肠镜'] +
            nurse_stats[nurse]['ERCP']
        )
    return nurse_stats

# 获取1月和2月的护士统计数据
nurse_stats_jan = count_nurse_stats(df_jan)
nurse_stats_feb = count_nurse_stats(df_feb)

# 更新护士工作量统计
nurse_df = pd.read_excel('output.xlsx', sheet_name='护士工作量统计')
for index, row in nurse_df.iterrows():
    nurse = row['护士']
    if nurse in nurse_stats_feb:
        # 按照模板的列顺序更新数据
        nurse_df.loc[index, '胃镜'] = nurse_stats_feb[nurse]['胃镜']
        nurse_df.loc[index, '无痛胃镜'] = nurse_stats_feb[nurse]['无痛胃镜']
        nurse_df.loc[index, '肠镜'] = nurse_stats_feb[nurse]['肠镜']
        nurse_df.loc[index, '无痛肠镜'] = nurse_stats_feb[nurse]['无痛肠镜']
        nurse_df.loc[index, 'ERCP'] = nurse_stats_feb[nurse]['ERCP']
        nurse_df.loc[index, 'EMR/APC'] = nurse_stats_feb[nurse]['EMR/APC']
        nurse_df.loc[index, 'ESD'] = nurse_stats_feb[nurse]['ESD']
        nurse_df.loc[index, '止血术'] = nurse_stats_feb[nurse]['止血术']
        nurse_df.loc[index, '扩张术'] = nurse_stats_feb[nurse]['扩张术']
        nurse_df.loc[index, '异物摄取'] = nurse_stats_feb[nurse]['异物摄取']
        nurse_df.loc[index, '其他'] = nurse_stats_feb[nurse]['其他']
        nurse_df.loc[index, '病例数'] = nurse_stats_feb[nurse]['病例数']
        nurse_df.loc[index, '百分比'] = round((nurse_stats_feb[nurse]['病例数'] / stats_feb['病例数']) * 100, 2)

# 增加汇总行
summary_row = pd.DataFrame({
    '护士': ['汇总'],
    '胃镜': [nurse_df['胃镜'].sum()],
    '无痛胃镜': [nurse_df['无痛胃镜'].sum()],
    '肠镜': [nurse_df['肠镜'].sum()],
    '无痛肠镜': [nurse_df['无痛肠镜'].sum()],
    'ERCP': [nurse_df['ERCP'].sum()],
    'EMR/APC': [nurse_df['EMR/APC'].sum()],
    'ESD': [nurse_df['ESD'].sum()],
    '止血术': [nurse_df['止血术'].sum()],
    '扩张术': [nurse_df['扩张术'].sum()],
    '异物摄取': [nurse_df['异物摄取'].sum()],
    '其他': [nurse_df['其他'].sum()],
    '病例数': [nurse_df['病例数'].sum()],
    '百分比': [round((nurse_df['病例数'].sum() / stats_feb['病例数']) * 100, 2)]
})
nurse_df = pd.concat([nurse_df, summary_row], ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter('updated_output.xlsx') as writer:
    center_df.to_excel(writer, sheet_name='内镜中心工作量统计', index=False)
    doctor_df.to_excel(writer, sheet_name='医生工作量统计', index=False)
    nurse_df.to_excel(writer, sheet_name='护士工作量统计', index=False)

print("数据已更新并保存到 updated_output.xlsx")

修正内容：

  1. 替换 append 为 pd.concat：

    • 使用 pd.concat 来替代 append 方法，确保代码兼容较新的 pandas 版本。

  2. 保持列顺序：

    • 在更新数据时，严格按照模板的列顺序更新，确保“其他”列位于“病例数”之前。

运行说明：

  1. 运行代码后，生成的 updated_output.xlsx 文件将包含更新后的统计数据。
  2. 检查“其他”列是否位于“病例数”之前，并确保“汇总”行正确统计了各项目的总数。

如果还有其他问题，请随时告诉我！

时间: 2025-03-14T22:23:52

对话统计

  • 消息总数: 22 条
  • 处理状态: 成功转换