Create a new column based on two columns from two different dataframes

Question

I have one column in the first dataframe called 'id' and another column in the second dataframe called 'first_id' which refers to the id from the first dataframe. I need to create a new column which has value 1 if the id and first_id match, otherwise it is 0. I tried this but I am getting an error ValueError: Length of values does not match length of index.

df2['new'] = np.where(df2[df2.first_id.isin(df1.id.values)], 1, 0)

I understand why is it happening, because df2 and df2[df2.first_id.isin(df1.id.values)] are of different lengths but I can't make them the same. Any ideas?

Answer 1

You were almost there!

Sample DFs:

In [387]: df1
Out[387]:
   id
0   1
1   2
2   3
3   4
4   5

In [388]: df2
Out[388]:
   first_id
0         7
1         6
2         5
3         1
4         3

Solution:

In [389]: df2['new'] = df2.first_id.isin(df1.id).astype(np.int8)

Result:

In [390]: df2
Out[390]:
   first_id  new
0         7    0
1         6    0
2         5    1
3         1    1
4         3    1

Answer 2

Something like this maybe?

df1 = pd.DataFrame(np.random.randint(0,5,size=(100, 1)), columns=list('A')) # random 1 column df
df2 = pd.DataFrame(np.random.randint(0,5,size=(100, 1)), columns=list('B')) # random 1 column df
df2["new"] = df2.apply(lambda row: 1 if row[0] == df1["A"][row.name] else 0, axis = 1) # lambda function to check if they match. row.name gets the index
df2