修改代码分离EMR和APC统计

Posted on Edited on

你的代码中获取不到”放大”数据的原因主要有以下几点：

  1. 逻辑顺序问题：在检查类别的判断中，”放大”检查的判断被放在了”超声内镜”之后，而”其他”之前。如果数据中同时包含”超声”和”放大”信息，会被”超声内镜”的判断先捕获。

  2. 条件判断结构：你使用了elif链式结构，这意味着一旦某个条件满足，后续的条件就不会被检查。所以如果”超声内镜”的条件先满足，”放大”的条件就永远不会被检查。

  3. 项目映射缺失：在project_mapping字典中没有为”放大”定义映射关系。

解决方案：

  1. 调整判断顺序：将”放大”检查的判断移到更靠前的位置
  2. 修改判断逻辑：可以考虑使用独立的条件判断而非elif链
  3. 添加映射关系：在project_mapping中添加”放大”的映射

修改后的关键部分代码：

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
# 定义项目名称映射规则
project_mapping = {
    '无痛胃镜': '无胃',
    '无痛肠镜': '无肠',
    'EMR': 'EMR',
    'APC': 'APC',
    '止血术': '止血',
    '扩张术': '扩张',
    '超声内镜': '超声',
    '放大内镜': '放大',  # 添加放大映射
    '异物摄取': '异物',
    '病例数': '总数'
}

# 修改统计函数中的判断逻辑
def count_stats(df):
    stats = {
        # ...其他统计项...
        '放大': 0,
        # ...其他统计项...
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()
        exam = str(row['ExamItemsUI']).lower().strip()

        # 统计检查类别 - 修改判断逻辑
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '放大' in category or '放大' in exam:
            stats['放大'] += 1
        elif '超声内镜' in category or '超声' in exam:
            stats['超声内镜'] += 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
            
        # ...其他统计逻辑...

主要修改点：

  1. 在project_mapping中添加了”放大内镜”: “放大”的映射
  2. 将”放大”的判断逻辑移到”超声内镜”之前
  3. 同时检查category和exam字段中的”放大”关键词

这样修改后，系统会优先检查是否为放大内镜检查，然后再检查其他类型的检查，确保”放大”数据能够被正确捕获和统计。

同样的修改也需要应用到医生和护士的统计函数中，确保所有统计部分都能正确识别”放大”数据。

时间: 2025-05-31T00:56:21

👤 用户

帮我修改下代码。。目的是把原来的EMR/APC统计区分开EMR和APC单独统计。

定义项目名称映射规则：去除 ‘EMR/APC’: ‘EMR’,
定义项目顺序改成：’胃镜’, ‘无胃’, ‘肠镜’, ‘无肠’, ‘超声’, ‘EMR’, ‘APC’,’ESD’, ‘ERCP’,
‘止血’, ‘异物’, ‘扩张’, ‘其他’
统计镜下诊断里面需要修改：if ‘emr’ in diagnosis or ‘apc’ in diagnosis: 区分开，分别为EMR和APC，也就是if ‘emr’ in diagnosis统计为EMR，’apc’ in diagnosis统计为APC。。

其他的还又哪些需要微调的，帮修复下，譬如内镜中心工作统计表中，增加APC统计等。

import pandas as pd
import argparse

def process_files(file1_path, file2_path, output_filename):
# 读取数据
df_jan = pd.read_excel(file1_path, sheet_name=’导出数据’)
df_feb = pd.read_excel(file2_path, sheet_name=’导出数据’)

# 定义项目名称映射规则
project_mapping = {
    '无痛胃镜': '无胃',
    '无痛肠镜': '无肠',
    'EMR/APC': 'EMR',
    '止血术': '止血',
    '扩张术': '扩张',
    '超声内镜': '超声',
    '异物摄取': '异物',
    '病例数': '总数'
}

# 定义项目顺序（使用映射后的名称）
project_order = [
    '胃镜', '无胃', '肠镜', '无肠', '超声', 'EMR', 'ESD', 'ERCP',
    '止血', '异物', '扩张', '其他'
]

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        '超声内镜': 0,
        'EMR/APC': 0,
        'ESD': 0,
        'ERCP': 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
        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['超声内镜'] +
        stats['ERCP'] +
        stats['其他']
    )
    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)

# 创建内镜中心工作量统计 DataFrame
center_data = []
for project in project_order:
    original_project = next(
        (key for key, value in project_mapping.items() if value == project),
        project
    )
    center_data.append({
        '项目': project,
        '本月数量': stats_feb.get(original_project, 0),
        '上月数量': stats_jan.get(original_project, 0),
        '同比变化（%）': calculate_change(stats_feb.get(original_project, 0), stats_jan.get(original_project, 0))
    })

center_df = pd.DataFrame(center_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '项目': ['汇总'],
    '本月数量': [
        stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
        stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他']
    ],
    '上月数量': [
        stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
        stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
    ],
    '同比变化（%）': [calculate_change(
        stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
        stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他'],
        stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
        stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
    )],
    '备注': ['']
})
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,
                '超声内镜': 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
        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]['超声内镜'] +
            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)

# 创建医生工作量统计 DataFrame
doctor_data = []
for doctor, stats in doctor_stats_feb.items():
    doctor_data.append({
        '医师': doctor,
        **{project_mapping.get(k, k): v for k, v in stats.items()}
    })

doctor_df = pd.DataFrame(doctor_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '医师': ['汇总'],
    **{project_mapping.get(k, k): [doctor_df[project_mapping.get(k, k)].sum()] for k in project_order},
    '总数': [doctor_df['总数'].sum()]
})
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,
                '超声内镜': 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
        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]['超声内镜'] +
            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)

# 创建护士工作量统计 DataFrame
nurse_data = []
for nurse, stats in nurse_stats_feb.items():
    nurse_data.append({
        '护士': nurse,
        **{project_mapping.get(k, k): v for k, v in stats.items()}
    })

nurse_df = pd.DataFrame(nurse_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '护士': ['汇总'],
    **{project_mapping.get(k, k): [nurse_df[project_mapping.get(k, k)].sum()] for k in project_order},
    '总数': [nurse_df['总数'].sum()]
})
nurse_df = pd.concat([nurse_df, summary_row], ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter(output_filename) 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)

if name == ‘main‘:
parser = argparse.ArgumentParser(description=’Process Excel files.’)
parser.add_argument(‘file1’, help=’Path to the first Excel file’)
parser.add_argument(‘file2’, help=’Path to the second Excel file’)
parser.add_argument(‘output’, help=’Path to save the output Excel file’)

args = parser.parse_args()

process_files(args.file1, args.file2, args.output)

时间: 2025-05-08T23:37:25

🤖 DeepSeek (deepseek-chat)

我已帮你修改了代码，主要做了以下调整：

  1. 将EMR和APC分开统计
  2. 更新了项目名称映射规则和顺序
  3. 修改了镜下诊断的统计逻辑
  4. 在所有相关统计部分(中心、医生、护士)都增加了APC的单独统计

以下是修改后的代码：

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
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
import pandas as pd
import argparse

def process_files(file1_path, file2_path, output_filename):
    # 读取数据
    df_jan = pd.read_excel(file1_path, sheet_name='导出数据')
    df_feb = pd.read_excel(file2_path, sheet_name='导出数据')

    # 定义项目名称映射规则
    project_mapping = {
        '无痛胃镜': '无胃',
        '无痛肠镜': '无肠',
        'EMR': 'EMR',
        'APC': 'APC',
        '止血术': '止血',
        '扩张术': '扩张',
        '超声内镜': '超声',
        '异物摄取': '异物',
        '病例数': '总数'
    }

    # 定义项目顺序（使用映射后的名称）
    project_order = [
        '胃镜', '无胃', '肠镜', '无肠', '超声', 'EMR', 'APC', 'ESD', 'ERCP',
        '止血', '异物', '扩张', '其他'
    ]

    # 定义统计函数
    def count_stats(df):
        stats = {
            '胃镜': 0,
            '无痛胃镜': 0,
            '肠镜': 0,
            '无痛肠镜': 0,
            '超声内镜': 0,
            'EMR': 0,
            'APC': 0,
            'ESD': 0,
            'ERCP': 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
            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:
                stats['EMR'] += 1
            if 'apc' in diagnosis:
                stats['APC'] += 1
            if '止血' in diagnosis:
                stats['止血术'] += 1
            if '异物' in diagnosis:
                stats['异物摄取'] += 1

        # 计算病例数
        stats['病例数'] = (
            stats['胃镜'] +
            stats['无痛胃镜'] +
            stats['肠镜'] +
            stats['无痛肠镜'] +
            stats['超声内镜'] +
            stats['ERCP'] +
            stats['其他']
        )
        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)

    # 创建内镜中心工作量统计 DataFrame
    center_data = []
    for project in project_order:
        original_project = next(
            (key for key, value in project_mapping.items() if value == project),
            project
        )
        center_data.append({
            '项目': project,
            '本月数量': stats_feb.get(original_project, 0),
            '上月数量': stats_jan.get(original_project, 0),
            '同比变化（%）': calculate_change(stats_feb.get(original_project, 0), stats_jan.get(original_project, 0))
        })

    center_df = pd.DataFrame(center_data)

    # 增加汇总行
    summary_row = pd.DataFrame({
        '项目': ['汇总'],
        '本月数量': [
            stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
            stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他']
        ],
        '上月数量': [
            stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
            stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
        ],
        '同比变化（%）': [calculate_change(
            stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
            stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他'],
            stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
            stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
        )],
        '备注': ['']
    })
    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,
                    '超声内镜': 0,
                    'ERCP': 0,
                    'EMR': 0,
                    '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
            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:
                doctor_stats[doctor]['EMR'] += 1
            if 'apc' in diagnosis:
                doctor_stats[doctor]['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]['超声内镜'] +
                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)

    # 创建医生工作量统计 DataFrame
    doctor_data = []
    for doctor, stats in doctor_stats_feb.items():
        doctor_data.append({
            '医师': doctor,
            **{project_mapping.get(k, k): v for k, v in stats.items()}
        })

    doctor_df = pd.DataFrame(doctor_data)

    # 增加汇总行
    summary_row = pd.DataFrame({
        '医师': ['汇总'],
        **{project_mapping.get(k, k): [doctor_df[project_mapping.get(k, k)].sum()] for k in project_order},
        '总数': [doctor_df['总数'].sum()]
    })
    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,
                    '超声内镜': 0,
                    'ERCP': 0,
                    'EMR': 0,
                    '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
            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:
                nurse_stats[nurse]['EMR'] += 1
            if 'apc' in diagnosis:
                nurse_stats[nurse]['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]['超声内镜'] +
                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)

    # 创建护士工作量统计 DataFrame
    nurse_data = []
    for nurse, stats in nurse_stats_feb.items():
        nurse_data.append({
            '护士': nurse,
            **{project_mapping.get(k, k): v for k, v in stats.items()}
        })

    nurse_df = pd.DataFrame(nurse_data)

    # 增加汇总行
    summary_row = pd.DataFrame({
        '护士': ['汇总'],
        **{project_mapping.get(k, k): [nurse_df[project_mapping.get(k, k)].sum()] for k in project_order},
        '总数': [nurse_df['总数'].sum()]
    })
    nurse_df = pd.concat([nurse_df, summary_row], ignore_index=True)

    # 保存更新后的Excel文件
    with pd.ExcelWriter(output_filename) 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)

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='Process Excel files.')
    parser.add_argument('file1', help='Path to the first Excel file')
    parser.add_argument('file2', help='Path to the second Excel file')
    parser.add_argument('output', help='Path to save the output Excel file')

    args = parser.parse_args()

    process_files(args.file1, args.file2, args.output)

主要修改点：

  1. 在project_mapping中将EMR和APC分开
  2. 在project_order中增加了APC
  3. 修改了所有统计函数(中心、医生、护士)中的EMR/APC统计逻辑，现在会分别统计
  4. 更新了所有相关的统计字典结构，确保包含EMR和APC两个独立字段

这些修改确保了EMR和APC会被分开统计并显示在所有报表中。

时间: 2025-05-08T23:37:25

👤 用户

帮我看一下，下面的代码为什么获取不到“放大”数据

import pandas as pd
import argparse

def process_files(file1_path, file2_path, output_filename):
# 读取数据
df_jan = pd.read_excel(file1_path, sheet_name=’导出数据’)
df_feb = pd.read_excel(file2_path, sheet_name=’导出数据’)

# 定义项目名称映射规则
project_mapping = {
    '无痛胃镜': '无胃',
    '无痛肠镜': '无肠',
    'EMR': 'EMR',
    'APC': 'APC',
    'APC': 'APC',
    '止血术': '止血',
    '扩张术': '扩张',
    '超声内镜': '超声',
    '异物摄取': '异物',
    '病例数': '总数'
}

# 定义项目顺序（使用映射后的名称）
project_order = [
    '胃镜', '无胃', '肠镜', '无肠', '超声', '放大','EMR','CSP', 'APC', 'ESD', 'ERCP',
    '止血', '异物', '扩张', '其他'
]

# 定义统计函数
def count_stats(df):
    stats = {
        '胃镜': 0,
        '无痛胃镜': 0,
        '肠镜': 0,
        '无痛肠镜': 0,
        '超声内镜': 0,
        'EMR': 0,
        'APC': 0,
        '放大': 0,
        'CSP': 0,
        'ESD': 0,
        'ERCP': 0,
        '止血术': 0,
        '异物摄取': 0,
        '扩张术': 0,
        '其他': 0
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()
        exam = str(row['ExamItemsUI']).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
        elif '超声内镜' in category or '超声' in exam:
            stats['超声内镜'] += 1
        elif '放大' in category or '放大' in exam:
            stats['放大'] += 1
        else:
            stats['其他'] += 1

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

    # 计算病例数
    stats['病例数'] = (
        stats['胃镜'] +
        stats['无痛胃镜'] +
        stats['肠镜'] +
        stats['无痛肠镜'] +
        stats['超声内镜'] +
        stats['ERCP'] +
        stats['放大'] +
        stats['其他']
    )
    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)

# 创建内镜中心工作量统计 DataFrame
center_data = []
for project in project_order:
    original_project = next(
        (key for key, value in project_mapping.items() if value == project),
        project
    )
    center_data.append({
        '项目': project,
        '本月数量': stats_feb.get(original_project, 0),
        '上月数量': stats_jan.get(original_project, 0),
        '同比变化（%）': calculate_change(stats_feb.get(original_project, 0), stats_jan.get(original_project, 0))
    })

center_df = pd.DataFrame(center_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '项目': ['汇总'],
    '本月数量': [
        stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
        stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他']
    ],
    '上月数量': [
        stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
        stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
    ],
    '同比变化（%）': [calculate_change(
        stats_feb['胃镜'] + stats_feb['无痛胃镜'] + stats_feb['肠镜'] +
        stats_feb['无痛肠镜'] + stats_feb['超声内镜'] + stats_feb['ERCP'] + stats_feb['其他'],
        stats_jan['胃镜'] + stats_jan['无痛胃镜'] + stats_jan['肠镜'] +
        stats_jan['无痛肠镜'] + stats_jan['超声内镜'] + stats_jan['ERCP'] + stats_jan['其他']
    )],
    '备注': ['']
})
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()
        exam = str(row['ExamItemsUI']).lower().strip()

        if doctor not in doctor_stats:
            doctor_stats[doctor] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                '超声内镜': 0,
                'EMR': 0,
                'APC': 0,
                '放大': 0,
                'CSP': 0,
                'ESD': 0,
                'ERCP': 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
        elif '超声内镜' in category or '超声' in exam:
            doctor_stats[doctor]['超声内镜'] += 1
        elif '放大' in category or '放大' in exam:
            doctor_stats[doctor]['放大'] += 1
        else:
            doctor_stats[doctor]['其他'] += 1

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

        # 计算病例数
        doctor_stats[doctor]['病例数'] = (
            doctor_stats[doctor]['胃镜'] +
            doctor_stats[doctor]['无痛胃镜'] +
            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)

# 创建医生工作量统计 DataFrame
doctor_data = []
for doctor, stats in doctor_stats_feb.items():
    doctor_data.append({
        '医师': doctor,
        **{project_mapping.get(k, k): v for k, v in stats.items()}
    })

doctor_df = pd.DataFrame(doctor_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '医师': ['汇总'],
    **{project_mapping.get(k, k): [doctor_df[project_mapping.get(k, k)].sum()] for k in project_order},
    '总数': [doctor_df['总数'].sum()]
})
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()
        exam = str(row['ExamItemsUI']).lower().strip()

        if nurse not in nurse_stats:
            nurse_stats[nurse] = {
                '胃镜': 0,
                '无痛胃镜': 0,
                '肠镜': 0,
                '无痛肠镜': 0,
                '超声内镜': 0,
                'EMR': 0,
                'APC': 0,
                '放大': 0,
                'CSP': 0,
                'ESD': 0,
                'ERCP': 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
        elif '超声内镜' in category or '超声' in exam:
            nurse_stats[nurse]['超声内镜'] += 1
        elif '放大' in category or '放大' in exam:
            nurse_stats[nurse]['放大'] += 1
        else:
            nurse_stats[nurse]['其他'] += 1

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

        # 计算病例数
        nurse_stats[nurse]['病例数'] = (
            nurse_stats[nurse]['胃镜'] +
            nurse_stats[nurse]['无痛胃镜'] +
            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)

# 创建护士工作量统计 DataFrame
nurse_data = []
for nurse, stats in nurse_stats_feb.items():
    nurse_data.append({
        '护士': nurse,
        **{project_mapping.get(k, k): v for k, v in stats.items()}
    })

nurse_df = pd.DataFrame(nurse_data)

# 增加汇总行
summary_row = pd.DataFrame({
    '护士': ['汇总'],
    **{project_mapping.get(k, k): [nurse_df[project_mapping.get(k, k)].sum()] for k in project_order},
    '总数': [nurse_df['总数'].sum()]
})
nurse_df = pd.concat([nurse_df, summary_row], ignore_index=True)

# 保存更新后的Excel文件
with pd.ExcelWriter(output_filename) 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)

if name == ‘main‘:
parser = argparse.ArgumentParser(description=’Process Excel files.’)
parser.add_argument(‘file1’, help=’Path to the first Excel file’)
parser.add_argument(‘file2’, help=’Path to the second Excel file’)
parser.add_argument(‘output’, help=’Path to save the output Excel file’)

args = parser.parse_args()

process_files(args.file1, args.file2, args.output)

时间: 2025-05-31T00:56:21

🤖 DeepSeek (deepseek-chat)

你的代码中获取不到”放大”数据的原因主要有以下几点：

  1. 逻辑顺序问题：在检查类别的判断中，”放大”检查的判断被放在了”超声内镜”之后，而”其他”之前。如果数据中同时包含”超声”和”放大”信息，会被”超声内镜”的判断先捕获。

  2. 条件判断结构：你使用了elif链式结构，这意味着一旦某个条件满足，后续的条件就不会被检查。所以如果”超声内镜”的条件先满足，”放大”的条件就永远不会被检查。

  3. 项目映射缺失：在project_mapping字典中没有为”放大”定义映射关系。

解决方案：

  1. 调整判断顺序：将”放大”检查的判断移到更靠前的位置
  2. 修改判断逻辑：可以考虑使用独立的条件判断而非elif链
  3. 添加映射关系：在project_mapping中添加”放大”的映射

修改后的关键部分代码：

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
# 定义项目名称映射规则
project_mapping = {
    '无痛胃镜': '无胃',
    '无痛肠镜': '无肠',
    'EMR': 'EMR',
    'APC': 'APC',
    '止血术': '止血',
    '扩张术': '扩张',
    '超声内镜': '超声',
    '放大内镜': '放大',  # 添加放大映射
    '异物摄取': '异物',
    '病例数': '总数'
}

# 修改统计函数中的判断逻辑
def count_stats(df):
    stats = {
        # ...其他统计项...
        '放大': 0,
        # ...其他统计项...
    }
    for _, row in df.iterrows():
        category = str(row['检查类别']).lower().strip()
        diagnosis = str(row['镜下诊断']).lower().strip()
        exam = str(row['ExamItemsUI']).lower().strip()

        # 统计检查类别 - 修改判断逻辑
        if '十二指肠镜' in category or 'ercp' in category:
            stats['ERCP'] += 1
        elif '放大' in category or '放大' in exam:
            stats['放大'] += 1
        elif '超声内镜' in category or '超声' in exam:
            stats['超声内镜'] += 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
            
        # ...其他统计逻辑...

主要修改点：

  1. 在project_mapping中添加了”放大内镜”: “放大”的映射
  2. 将”放大”的判断逻辑移到”超声内镜”之前
  3. 同时检查category和exam字段中的”放大”关键词

这样修改后，系统会优先检查是否为放大内镜检查，然后再检查其他类型的检查，确保”放大”数据能够被正确捕获和统计。

同样的修改也需要应用到医生和护士的统计函数中，确保所有统计部分都能正确识别”放大”数据。

时间: 2025-05-31T00:56:21

对话统计

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