如何在Pandas中将同Sample ID、不同Compound的多行合并为单行？

我有一份超过5万行的调研数据，数据结构如下：

import pandas as pd
import numpy as np

df1 = pd.DataFrame(list(zip(['0001', '0001', '0002', '0003', '0004', '0004'],
                            ['a', 'b', 'a', 'b', 'a', 'b'],
                           ['USA', 'USA', 'USA', 'USA', 'USA', 'USA'],
                           ['Jan', 'Jan', 'Jan', 'Jan', 'Jan', 'Jan'],
                           [1,2,3,4,5,6])),
                    columns=['sample ID', 'compound', 'country', 'month', 'value'])

部分sample ID对应两种Compound（化合物），我希望将同一sample ID下对应两种化合物的多行合并为单行，得到如下结构的数据：

df2 = pd.DataFrame(list(zip(['0001', '0002', '0003', '0004'],
                            ['a', 'a', '', 'a'],
                            [1, 3, np.nan, 5],
                            ['b', '', 'b', 'b'],
                            [2, np.nan, 4, 6],
                            ['USA', 'USA', 'USA', 'USA'],
                           ['Jan', 'Jan', 'Jan', 'Jan'])),
                    columns=['sample ID', 'compound1', 'value1', 'compound2', 'value2','country', 'month'])

目前我用以下方法实现了需求：

pd.merge((df1.loc[df1.compound == 'a']),
         (df1.loc[df1.compound == 'b']),
         how="outer",
         on=['sample ID', 'country', 'month'],
        suffixes=("_no3", "_no2"))

请问有没有更优的实现方案？

推荐使用pivot或pivot_table方法，相比merge更简洁灵活，尤其适合这种长表转宽表的场景，而且当化合物种类增多时，不需要逐个拆分合并，扩展性更强。

方法1：使用pivot #

# 对指定列进行透视转换
pivoted = df1.pivot(index=['sample ID', 'country', 'month'], 
                    columns='compound', 
                    values=['compound', 'value'])

# 重新命名列名，匹配目标格式
pivoted.columns = [f'{col[0]}{i+1}' for i, col in enumerate(pivoted.columns)]
# 重置索引并填充空值
df_result = pivoted.reset_index().fillna({'compound1': '', 'compound2': ''})

方法2：使用pivot_table（适配重复数据场景） #

如果数据中存在同一sample ID+compound组合的重复行，可通过pivot_table指定聚合函数（如first取首个值、mean取平均值）处理：

pivoted_table = df1.pivot_table(index=['sample ID', 'country', 'month'], 
                                columns='compound', 
                                values=['compound', 'value'],
                                aggfunc='first')

# 重命名列名
pivoted_table.columns = [f'{col[0]}{i+1}' for i, col in enumerate(pivoted_table.columns)]
# 重置索引并填充空值
df_result_table = pivoted_table.reset_index().fillna({'compound1': '', 'compound2': ''})

方案优势 #

• 代码更简洁：无需手动拆分不同化合物的子集再合并，一步完成转换
• 扩展性强：后续新增化合物时，代码无需修改，会自动生成对应列（如新增'c'则生成compound3、value3）
• 性能更优：针对5万行规模的数据，pivot内部实现更高效，运行速度优于多次merge操作

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