如何计算2020年各周截止时接受特定手术的患者累计总数？

数据集说明 #

我有一份记录2020年就医患者就诊当日是否接受某特定手术的数据集，示例如下：

注：Procedure Ind为1表示患者当日接受了目标手术，0表示未接受

需求描述 #

希望生成输出结果，展示每连续一周截止时的累计接受手术患者数量：

• 第1周（1/1/2020 - 1/7/2020）：该周内接受手术的患者数（即累计总数）
• 第2周（截止到1/14/2020）：累计总数为第1周数量加第2周新增的手术患者数
• 以此类推，直到2020年最后一周

预期输出格式示例：

解决方案 #

方法1：SQL实现（以MySQL为例） #

假设数据表名为patient_procedures，通过以下步骤实现：

1. 转换就诊日期格式并筛选接受手术的患者
2. 按周分组计算新增手术患者数，再用窗口函数计算累计总数
3. 动态生成列名并转置结果

-- 第一步：计算每周新增及累计手术患者数
WITH weekly_data AS (
    SELECT
        WEEK(STR_TO_DATE(`Visit Date`, '%m/%d/%Y'), 1) AS week_num,
        MIN(STR_TO_DATE(`Visit Date`, '%m/%d/%Y')) AS week_start,
        MAX(STR_TO_DATE(`Visit Date`, '%m/%d/%Y')) AS week_end,
        COUNT(DISTINCT PatientID) AS weekly_new,
        SUM(COUNT(DISTINCT PatientID)) OVER (ORDER BY week_num) AS cumulative_total
    FROM patient_procedures
    WHERE `Procedure Ind` = 1
        AND YEAR(STR_TO_DATE(`Visit Date`, '%m/%d/%Y')) = 2020
    GROUP BY week_num
),
-- 第二步：生成动态列名并转置
week_columns AS (
    SELECT GROUP_CONCAT(
        CONCAT('MAX(CASE WHEN week_num = ', week_num, ' THEN cumulative_total END) AS `第', week_num, '周累计总数(', DATE_FORMAT(week_start, '%m/%d/%Y'), ' - ', DATE_FORMAT(week_end, '%m/%d/%Y'), ')`')
    ) AS column_sql
    FROM weekly_data
)
SELECT CONCAT('SELECT ', column_sql, ' FROM weekly_data') INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方法2：Python实现（使用Pandas） #

import pandas as pd

# 读取数据集（假设数据存储在csv文件中）
df = pd.read_csv('patient_data.csv')

# 转换日期格式并筛选2020年接受手术的患者
df['Visit Date'] = pd.to_datetime(df['Visit Date'], format='%m/%d/%Y')
df_filtered = df[(df['Procedure Ind'] == 1) & (df['Visit Date'].dt.year == 2020)]

# 按周分组，计算每周新增患者数、周起始和结束日期
df_filtered['week_num'] = df_filtered['Visit Date'].dt.isocalendar().week
weekly_data = df_filtered.groupby('week_num').agg(
    weekly_new=('PatientID', 'nunique'),
    week_start=('Visit Date', 'min'),
    week_end=('Visit Date', 'max')
).reset_index()

# 计算累计总数
weekly_data['cumulative_total'] = weekly_data['weekly_new'].cumsum()

# 生成预期的列名
weekly_data['column_name'] = weekly_data.apply(
    lambda row: f"第{row['week_num']}周累计总数({row['week_start'].strftime('%m/%d/%Y')} - {row['week_end'].strftime('%m/%d/%Y')})",
    axis=1
)

# 转置为预期格式
result = weekly_data.set_index('column_name')['cumulative_total'].to_frame().T

# 输出结果
print(result)

内容的提问来源于stack exchange，提问作者armsagainstatrophy