在python中用重复数据转换数据帧

Question

我想将下面的数据帧转换为将重复数据连接到一行中。例如：

data_dict={'FromTo_U': {0: 'L->R', 1: 'L->R', 2: 'S->I'},
     'GeneName': {0: 'EGFR', 1: 'EGFR', 2: 'EGFR'},
     'MutationAA_C': {0: 'p.L858R', 1: 'p.L858R', 2: 'p.S768I'},
     'MutationDescription': {0: 'Substitution - Missense',
      1: 'Substitution - Missense',
      2: 'Substitution - Missense'},
     'PubMed': {0: '22523351', 1: '23915069', 2: '26862733'},
     'VariantID': {0: 'COSM12979', 1: 'COSM12979', 2: 'COSM18486'},
     'VariantPos_U': {0: '858', 1: '858', 2: '768'},
     'VariantSource': {0: 'COSMIC', 1: 'COSMIC', 2: 'COSMIC'}}
df1=pd.DataFrame(data_dict)

转换后的数据帧应

data_dict_t={'FromTo_U': {0: 'L->R', 2: 'S->I'},
 'GeneName': {0: 'EGFR', 2: 'EGFR'},
 'MutationAA_C': {0: 'p.L858R', 2: 'p.S768I'},
 'MutationDescription': {0: 'Substitution - Missense',2: 'Substitution - Missense'},
 'PubMed': {0: '22523351,23915069', 2: '26862733'},
 'VariantID': {0: 'COSM12979', 2: 'COSM18486'},
 'VariantPos_U': {0: '858',  2: '768'},
 'VariantSource': {0: 'COSMIC', 2: 'COSMIC'}}

我只想在PubMed id不同且其余列具有相同数据的情况下合并df1的两行。提前谢谢

Answer 1

使用groupby+agg和str.join作为aggfunc

c = df1.columns.difference(['PubMed']).tolist()
df1.groupby(c, as_index=False).PubMed.agg(','.join)

  FromTo_U GeneName MutationAA_C      MutationDescription  VariantID  \
0     L->R     EGFR      p.L858R  Substitution - Missense  COSM12979   
1     S->I     EGFR      p.S768I  Substitution - Missense  COSM18486   

  VariantPos_U VariantSource             PubMed  
0          858        COSMIC  22523351,23915069  
1          768        COSMIC           26862733