Adding a column based on another dataframe when merging is not possible


My first dataframe (same name and object might appear multiple times):

     name      object     number1      number2 
0      n1          o1         0.0          1.0
1      n1          o2         1.0          1.0
2      n2          o1         0.0          1.0
3      n3          o3         1.0          0.0

My second dataframe might not contain all the objects that appear in the first dataframe:

     value1     value2      number3      object   
0     90.10      40.15          Yes          o1        
1     80.30      92.16           No          o3        
2     20.57      93.69           No          o4        
3     40.60      94.32          Yes          o5  

I want to append the number3 column to the first dataframe, where Yes is converted to 1.0 and No is converted to 0.0, but ignore the rows where we don’t have any info about the object. So it would be:

     name      object     number1      number2     number3
0      n1          o1         0.0          1.0         1.0
1      n2          o1         0.0          1.0         1.0
2      n3          o3         1.0          0.0         0.0

For the convertion I’d just do:

df_2.number3.replace(to_replace=['No', 'Yes'], value=[0.0, 1.1])

But then I can’t just merge on object because they are not identical. Is it possible to search for the number3 of the objects which are in df_2, append it, and delete the rest of the rows?

>Solution :

It looks like you just want to do a join/merge on the object column:

df1.merge(df2,on=['object']).replace(to_replace={'number3':['No', 'Yes']}, value={'number3':[0.0, 1.0]})```

Leave a ReplyCancel reply