Python Pandas - How to merge two dataframes that have two different columns to two list columns

user_2675457 0
Viewed 45

I have two dataframes.

df1 :

key  column_a 
AA   AAB      
AA   AAC      
AB   ABA      
AB   ABK

df2:

key  column_b
AA   AAA
AB   ABA
AB   ABK
AC   ACC

I wanted to merge them but the column_a and column_b would become a list.

key  column_a       column_b
AA   ['AAB','AAC']  ['AAA']
AB   ['ABA','ABK']  ['ABA','ABK']
AC   NaN            ['ACC']
pandas dataframe merge python
1 Answers

If converting to lists, you lose the vectorised functionality which goes with using NumPy arrays held in contiguous memory blocks.

Solution with concat, groupby and aggregate lambda function - remove NaNs and create lists:

df = pd.concat([df1, df2]).groupby('key').agg(lambda x: x.dropna().tolist())
print (df)
       column_a    column_b
key                        
AA   [AAB, AAC]       [AAA]
AB   [ABA, ABK]  [ABA, ABK]
AC           []       [ACC]

Last if need replace empty lists to NaNs use DataFrame.where:

df = df.where(df.astype(bool))
print (df)
       column_a    column_b
key                        
AA   [AAB, AAC]       [AAA]
AB   [ABA, ABK]  [ABA, ABK]
AC          NaN       [ACC]
user_29010020
Related