如何按Element分组并合并DataFrame数据生成指定宽表

无法合并DataFrame中的行，输入数据如下：

element tool time    disease
    AAK1   SE   12       SARS
    AAK1   RI   12       SARS
    AAK1 A5SS   12       SARS
    AAK1   SE   12       MERS
    AAK1   RI   12       MERS
    AAK1  MXE   12       MERS
   ABCC1   SE   12 SARS-CoV-2
   ABCC1  MXE   12 SARS-CoV-2
  ABCC10   SE   12       MERS
  ABCC10   A3SS 12       MERS

期望输出：

element  time    disease                  SE   RI    MXE   A3SS  A5SS
AAK1       12    SARS and MERS             1     1     0     0     0
AAK1       12    SARS                      0     0     0     0     1
AAK1       12    MERS                      0     0     1     0     0
ABCC1      12    SARS-CoV-2                1     0     1     0     0
ABCC10     12    MERS                      1     0     0     1     0

需求逻辑：

• 按element分组
• 将tool列拆分为SE、RI、MXE、A3SS、A5SS这5列，用0/1标记是否存在
• 若同一tool在不同disease中都存在，则合并这些disease为类似"SARS and MERS"的形式
• 无法合并的记录（即某tool只属于单个disease）单独列出
• 最终生成指定格式的DataFrame

用Pandas的分组、集合运算等操作可实现需求，步骤如下：

1. 准备数据与定义目标tool列

import pandas as pd

# 构造输入DataFrame
df = pd.DataFrame({
    'element': ['AAK1', 'AAK1', 'AAK1', 'AAK1', 'AAK1', 'AAK1', 'ABCC1', 'ABCC1', 'ABCC10', 'ABCC10'],
    'tool': ['SE', 'RI', 'A5SS', 'SE', 'RI', 'MXE', 'SE', 'MXE', 'SE', 'A3SS'],
    'time': [12]*10,
    'disease': ['SARS', 'SARS', 'SARS', 'MERS', 'MERS', 'MERS', 'SARS-CoV-2', 'SARS-CoV-2', 'MERS', 'MERS']
})

# 定义需要拆分的tool列
target_tools = ['SE', 'RI', 'MXE', 'A3SS', 'A5SS']

2. 分组获取每个(element, disease)对应的tool集合

grouped = df.groupby(['element', 'disease'])['tool'].apply(set).reset_index()

3. 找出每个element下跨disease共享的tool

shared_tools = grouped.groupby('element')['tool'].apply(lambda x: set.intersection(*x)).reset_index(name='shared')

4. 生成合并行与单独行并组装结果

result = []

for elem in df['element'].unique():
    elem_group = grouped[grouped['element'] == elem]
    elem_shared = shared_tools[shared_tools['element'] == elem]['shared'].iloc[0]
    
    # 处理共享tool的合并行
    if elem_shared:
        merged_disease = ' and '.join(elem_group['disease'].unique())
        merged_row = {
            'element': elem,
            'time': df[df['element'] == elem]['time'].iloc[0],
            'disease': merged_disease
        }
        for tool in target_tools:
            merged_row[tool] = 1 if tool in elem_shared else 0
        result.append(merged_row)
    
    # 处理非共享tool的单独行
    for _, row in elem_group.iterrows():
        unique_tools = row['tool'] - elem_shared
        if unique_tools:
            single_row = {
                'element': elem,
                'time': df[df['element'] == elem]['time'].iloc[0],
                'disease': row['disease']
            }
            for tool in target_tools:
                single_row[tool] = 1 if tool in unique_tools else 0
            result.append(single_row)

# 转换为DataFrame并调整列顺序
final_df = pd.DataFrame(result)[['element', 'time', 'disease'] + target_tools]
print(final_df.to_string(index=False))

运行代码后输出与期望一致：

element  time    disease  SE  RI  MXE  A3SS  A5SS
   AAK1    12 SARS and MERS   1   1    0     0     0
   AAK1    12          SARS   0   0    0     0     1
   AAK1    12          MERS   0   0    1     0     0
  ABCC1    12 SARS-CoV-2   1   0    1     0     0
 ABCC10    12          MERS   1   0    0     1     0

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