如何将单列离职原因拆分为仅显示死亡、退出两类值的两个独立列

问题翻译 #

我有如下结构的表格数据：

注：流失原因列仅包含「死亡（Death）」和「退出（WithDrawal）」两类取值。
我需要将该列拆分为两个列：其中一列仅展示死亡类记录，未死亡的客户对应位置留空；另一列仅展示退出类记录，未退出的客户对应位置留空，拆分后预期效果如下：

解决方案 #

Excel 操作

新增两个列，分别填入对应公式后下拉填充所有行即可：

• 死亡列公式：=IF(B2="Death","Death","")（B列为原「流失原因」所在列，按需调整列号即可）
• 退出列公式：=IF(B2="WithDrawal","WithDrawal","")

Python Pandas 操作

基于numpy.where做条件赋值即可，示例代码：

import pandas as pd
import numpy as np

# df为你读取的原始数据表
df["死亡原因"] = np.where(df["Reason for Leaving"] == "Death", "Death", "")
df["退出原因"] = np.where(df["Reason for Leaving"] == "WithDrawal", "WithDrawal", "")

# 若需保持列名与预期一致，可重命名列
df.columns = ["Client number", "Reason for Leaving", "Reason for Leaving"]

SQL 操作

查询时通过CASE WHEN生成目标列即可：

SELECT 
    `Client number`,
    CASE WHEN `Reason for Leaving` = 'Death' THEN 'Death' ELSE '' END AS `Reason for Leaving`,
    CASE WHEN `Reason for Leaving` = 'WithDrawal' THEN 'WithDrawal' ELSE '' END AS `Reason for Leaving`
FROM 你的表名

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