基于条件替换单元格值：患者FinalStatus批量更新技术需求

核心思路 #

• 按患者姓名分组，检查该患者所有就诊记录中是否存在Died的出院状态
• 若存在，则该患者所有记录的FinalStatus设为Died
• 若全部为Survived，则FinalStatus设为Survived

Python Pandas 实现 #

适用于处理内存中的表格数据：

import pandas as pd

# 示例数据
df = pd.DataFrame({
    'PatientName': ['张三', '张三', '李四', '王五', '王五'],
    'PatientVisit': [1, 2, 1, 1, 2],
    'DischargeStatus': ['Survived', 'Died', 'Survived', 'Survived', 'Survived']
})

# 计算每个患者的最终状态
patient_final = df.groupby('PatientName')['DischargeStatus'].apply(
    lambda x: 'Died' if (x == 'Died').any() else 'Survived'
)

# 映射回原表格
df['FinalStatus'] = df['PatientName'].map(patient_final)

# 输出结果
print(df)

SQL 实现 #

适用于数据库中的表格（以MySQL/PostgreSQL为例）：

WITH patient_summary AS (
    SELECT 
        PatientName,
        -- 标记该患者是否有过死亡记录
        MAX(CASE WHEN DischargeStatus = 'Died' THEN 1 ELSE 0 END) AS has_died
    FROM 
        patient_visits
    GROUP BY 
        PatientName
)
UPDATE patient_visits pv
SET FinalStatus = CASE 
    WHEN ps.has_died = 1 THEN 'Died' 
    ELSE 'Survived' 
END
FROM patient_summary ps
WHERE pv.PatientName = ps.PatientName;

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