CODEWITHSUNDEEP
pythonpandaslookup
Dhruv
Dhruv

Reputation: 43

Compare two columns of one dataframe to one column of another dataframe

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.

Upvotes: 0

Views: 547

Answers (1)

jezrael
jezrael

Reputation: 862591

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')

Upvotes: 1

Related Questions