如何基于type列条件为Pandas DataFrame生成unique_detail列？

原始DataFrame #

df type       detail1   detail2        name  
0  fruit                               apple
1  fruit -2   best      best           apple
2             yellow    yellowish      apple
3             green                    apple
4  fruit                               banana
5  sub
6  fruit -2   best      best           banana
7             yellow    orange         banana
8             green     brown          banana

需求规则 #

• 当type列中"fruit"后紧跟"fruit -2"时，将对应name分组下的detail1和detail2的非空值合并为列表（去重并保留出现顺序）
• 当"fruit"后是"sub"时，unique_detail列格式为sub: [合并的非空详情值]
• 注意：detail1或detail2可能为空值

期望输出 #

df type       detail1   detail2        name     unique_detail
0  fruit                               apple    [best, yellow, yellowish, green]
1  fruit -2   best      best           apple    [best, yellow, yellowish, green]
2             yellow    yellowish      apple    [best, yellow, yellowish, green]
3             green                    apple    [best, yellow, yellowish, green]
4  fruit                               banana   sub: [yellow, orange, green, brown]
5  sub
6  fruit -2                            banana   sub: [yellow, orange, green, brown]
7             yellow    orange         banana   sub: [yellow, orange, green, brown]
8             green     brown          banana   sub: [yellow, orange, green, brown]

用户尝试的代码 #

m = df.type.eq("fruit") & df.type.shift(-1).ne("fruit -2")
df["detail"] = df.detail1 + df.detail2
df["detail"] = df.groupby("type").transform("unique")
df["detail"] = df["detail"].mask(m, "sub:"+df.detail)

正确实现方法 #

步骤说明 #

1. 填充name列的缺失值：第5行name为空，属于banana分组，用向前填充补全
2. 为每个name分组处理详情值：收集组内所有非空的detail1和detail2，去重并保留顺序
3. 判断分组是否包含"sub"类型，决定unique_detail的格式
4. 单独处理type为"sub"的行，将其unique_detail设为空

完整代码 #

import pandas as pd

# 原始数据（假设已加载为df）
data = {
    'type': ['fruit', 'fruit -2', '', '', 'fruit', 'sub', 'fruit -2', '', ''],
    'detail1': ['', 'best', 'yellow', 'green', '', '', 'best', 'yellow', 'green'],
    'detail2': ['', 'best', 'yellowish', '', '', '', 'best', 'orange', 'brown'],
    'name': ['apple', 'apple', 'apple', 'apple', 'banana', '', 'banana', 'banana', 'banana']
}
df = pd.DataFrame(data)

# 1. 填充name列的缺失值
df['name'] = df['name'].ffill()

# 2. 定义分组处理函数
def process_group(group):
    # 收集所有非空的detail1和detail2（去除空白字符串）
    details = []
    for _, row in group.iterrows():
        # 处理detail1
        if pd.notna(row['detail1']) and row['detail1'].strip() != '':
            details.append(row['detail1'].strip())
        # 处理detail2
        if pd.notna(row['detail2']) and row['detail2'].strip() != '':
            details.append(row['detail2'].strip())
    # 去重并保留原始顺序
    unique_details = list(dict.fromkeys(details))
    # 判断分组是否包含sub类型
    has_sub = group['type'].eq('sub').any()
    # 返回对应的格式
    if has_sub:
        return f"sub: [{', '.join(unique_details)}]"
    else:
        return f"[{', '.join(unique_details)}]"

# 3. 分组生成unique_detail列
df['unique_detail'] = df.groupby('name').apply(process_group).loc[df['name']].values

# 4. 处理type为sub的行，设置unique_detail为空
df.loc[df['type'] == 'sub', 'unique_detail'] = ''

# 查看结果
print(df)

代码说明 #

• ffill()：向前填充name列的缺失值，确保分组正确
• dict.fromkeys(details)：在保留元素出现顺序的同时实现去重，避免重复值（比如apple组的"best"出现两次，最终只保留一次）
• 分组应用函数时，通过groupby('name').apply(process_group)得到每个name对应的结果，再映射回原DataFrame
• 最后单独处理type为"sub"的行，符合期望输出的格式

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