excel - compare one column value with all the values of other column using pandas

Question

I have the one excel file which contains the below values

I need to compare a_id value with all the value of b_id and if it matches i have to update the value of a_flag to 1 otherwise 0.

For example take the first value in a_tag ie; 123 then compare all the values of b_id(113,211,222,123) . When it reaches to 123 in b_id we can see it matches. So we will update the value of a_flag as 1.

Just like that take all the values of a_id and compare with all the values of b_id. So after everything done we will have value either 1 or 0 in a_flag column.

Once its done we will take the first value of b_id then compare with all the value in a_id column and update b_flag column accordingly.

Finally i will have the below data.

I need to this using pandas because i am dealing with large collection of data. Below is my findings but it compare only with the first value of b_id. For example it compares 123(a_id first value) with 113 only (b_id first value).

import pandas as pd 
df1 = pd.read_excel('system_data.xlsx')
df1['a_flag'] = (df3['a_id'] == df3['b_id']).astype(int)

Answer 1

Use Series.isin for test membership:

df1['a_flag'] = df3['a_id'].isin(df3['b_id']).astype(int)
df1['b_flag'] = df3['b_id'].isin(df3['a_id']).astype(int)