I have a question about merging two dataframes.
df1:
CDR3 number
ASSADSPYEQY 0
ASSQDSPYEQY 0
ASSADSPYEQY 1
ASSYDSPYEQY 0
ASSADSPYEQY 3
df2:
seq origine pep protein clonecount clonefraction
ASSADSPYEQY database KAYNVTQAF nucleocapsid phosphoprotein [Severe acute respiratory syndrome coronavirus 2] 12 0.0001
ASSQDSPYEQY database VQELYSPIFLIV ORF7a protein [Severe acute respiratory syndrome coronavirus 2] 14 0.02
ASSADSPYEQY sample KPLEFGATSAAL orf1ab polyprotein [Severe acute respiratory syndrome coronavirus 2] 122 0.0002
ASSYDSPYEQY sample SEHDYQIGGYTEKW ORF3a protein [Severe acute respiratory syndrome coronavirus 2] 111 0.03
ASSADSPYEQY sample SEHDYQIGGYTEKW ORF3a protein [Severe acute respiratory syndrome coronavirus 2] 1021 0.004
Desired output:
seq origin pep clonecount clonefraction number
ASSADSPYEQY database,sample,sample KAYNVTQAF,KPLEFGATSAAL,SEHDYQIGGYTEKW 12,122,1021 0.0001,0.0002,0.004 0,1,3
ASSQDSPYEQY database VQELYSPIFLIV 14 0.0002 0
ASSADSPYEQY sample,database,sample KAYNVTQAF,KPLEFGATSAAL,SEHDYQIGGYTEKW 12,122,1021 0.0001,0.0002,0.004 0,1,3
ASSYDSPYEQY sample SEHDYQIGGYTEKW 111 0.003 0
ASSADSPYEQY sample,sample,database KAYNVTQAF,KPLEFGATSAAL,SEHDYQIGGYTEKW 12,122,1021 0.0001,0.0002,0.004 0,1,3
What I've tried:
df3 = df1.merge(df2.groupby('seq',as_index=False)[['origin']].agg(','.join),how='left')
Ouput I got:
seq origin
ASSADSPYEQY database,sample,sample
ASSQDSPYEQY database
ASSADSPYEQY sample,database,sample
ASSYDSPYEQY sample
ASSADSPYEQY sample,sample,database
But I don't know how to add the other columns in the same way... Is this possible at all? Thanks in advance.