0

I have one dataframe as below:

Id1 Id2
1 4
2 5
3

The 2nd dataframe is:

ID Comment
1 Pears
2 Grapes
3 Orange
4 Banana
5 Apple

How can I get the output like:

Id1 Id2 Review
1 4 Banana
2 5 Apple
3 Orange

So, basically I am trying to do a look up for Id2 (from dataframe 1) and get the comment from 2nd dataframe but if the Id2 (in first dataframe) is null then get the Id1 comment from 2nd dataframe.

Dhruv
  • 43
  • 4

1 Answers1

1

Use Series.fillna for replace missing values in Id2 by Id1 and then mapping column by Series.map by Series created by another DataFrame:

s = df2.set_index('ID')['Comment']

df1['Comment'] = df1['Id2'].fillna(df1['Id1']).map(s)

If there is multiple ID columns is possible forward filling missing values and selected last column, then mapping:

df1['Comment'] = df1.ffill(axis=1).iloc[:, -1].map(s)

Solution with merge is possible with helper column:

df1['ID'] = df1['Id2'].fillna(df1['Id1'])
#another idea
#df1['ID'] = df1.ffill(axis=1).iloc[:, -1]
df = df1.merge(df2, on='ID', how='left')

Or:

df = df1.assign(ID = df1['Id2'].fillna(df1['Id1'])).merge(df2, on='ID', how='left')
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • @jeyrael Thanks for your comment. Even I thought of the same but is there any other way? I was going through solutions on the internet and was trying to do it with merge (read somewhere lookup could be done using merge). – Dhruv Jun 02 '21 at 05:28
  • 2
    @KamalenduNayek - Sure, it is also possible, added to answer. Only need some preprocessing for replace missing values. – jezrael Jun 02 '21 at 05:31