How to use the pandas 'isin' function to give actual values of the df row instead of a boo-CodePudding

I have two dataframes and I'm comparing their columns labeled 'B'. If the value of column B in df2 matches the value of column B in df1, I want to extract the value of column C from df2 and add it to a new column in df1. Example:

df1

df2

Expected Result of df1:

I've tried the following. I know that this checks if there's a match of column B in both the dataframes - it returns a boolean value of True/False in the 'New' column. Is there a way to extract the value indicated under column 'C' when there's a match and add it to the 'New' column in df1 instead of the boolean values?

df1 = pd.read_csv('df1.csv')
df2 = pd.read_csv('df2.csv')

df1['New'] = df2['B'].isin(df1['B'])

CodePudding user response：

import pandas as pd

df1 = pd.DataFrame({'B': ['a', 'b', 'f', 'd', 'h'], 'C':[1, 5, 777, 10, 3]})
df2 = pd.DataFrame({'B': ['k', 'l', 'f', 'j', 'h'], 'C':[0, 9, 555, 15, 1]})
ind = df2[df2['B'].isin(df1['B'])].index
df1.loc[ind, 'new'] = df2.loc[ind, 'C']

df2

Output df1

   B    C    new
0  a    1    NaN
1  b    5    NaN
2  f  777  555.0
3  d   10    NaN
4  h    3    1.0

Here in ind are obtained indexes of rows df2 where there are matches. Further using loc, where on the left are the row indices, on the right are the column names.