How to merge two dataframes with multiple element from one column

Question

I try to merge a new dataframe based on a dataframe composed by element and a other dataframe with unique dataframe.

The dataframe1 :

        col1                 
0       A1               
1       A1,A5             
2       A1               
3       A2,A9,A3           
4       A3

The dataframe2 :

        column1        column2      
0       A1             DE   
1       A2             DZ    
2       A3             DA  
3       A4             AC     
4       A5             RC       
5       A6             UC     
6       A7             TC       
7       A8             VC 
8       A9             WC
9       A10            XC

The final dataframe :

        col1         column1        column2       
0       A1           A1             DE    
1       A1,A5        A1             DE
2       A1,A5        A5             RC
2       A1           A1             DE    
3       A2,A9,A3     A2             DZ       
4       A2,A9,A3     A9             WC         
5       A2,A9,A3     A3             DA
6       A3           A3             DA

score 0 · Accepted Answer · answered Jan 05 '21 at 13:25

0

Use DataFrame.explode by splitted values with DataFrame.merge and left join:

df = (df1.assign(column1 = df1['col1'].str.split(','))
         .explode('column1')
         .merge(df2, on='column1',how='left'))
print (df)
      col1 column1 column2
0        A1      A1      DE
1     A1,A5      A1      DE
2     A1,A5      A5      RC
3        A1      A1      DE
4  A2,A9,A3      A2      DZ
5  A2,A9,A3      A9      WC
6  A2,A9,A3      A3      DA
7        A3      A3      DA

answered Jan 05 '21 at 13:25

jezrael

822,522
95
1,334
1,252

Thanks for your answer @jezrael !! I have to use python 2.7 so do you know if there is a similar function explode on pandas 0.24.2 ? – Julie Hardy Jan 07 '21 at 10:32
@JulieHardy - Is possible upgrade pandas in `python 2.7` ? – jezrael Jan 07 '21 at 10:33
I check and it seems that the last version of pandas for python 2.7 is 0.24.2 @jezrael – Julie Hardy Jan 07 '21 at 10:39
@JulieHardy - Try check [this](https://stackoverflow.com/a/53218939/2901002) for some not explode solution. – jezrael Jan 07 '21 at 10:41
1

Thank you for your help I will try one of this method @jezrael ! – Julie Hardy Jan 07 '21 at 10:44

How to merge two dataframes with multiple element from one column

1 Answers1