check if the value in the column of one dataframe exists in the semicolon separated values of another dataframe in pandas

user_10481744 0
Viewed 89

I have 2 dataframes df1

----------------
Column_df1
------------------
abc
pqr
xyz

and another dataframe is df2

-----------------------------------
Column_df2_value      Column_df2
------------------------------------
aaa                   abc;mkp;txy
jjj                   tkp;xyz;lmn
ppp                   vnm;pqr;tmc
dbm                   krt;qwe;cfe
wer                   weq;trt;cfd

my resultant dataframe will be following

-------------------------------------
column_df1      column_df2_value
-------------------------------------
abc              aaa
pqr              ppp
xyz              jjj

I am trying to do this

mask=np.where(df1['Column_df1'].tolist().isin(df2['column_df2_value'].str.split(';',expand=True)).any(1))

df["value_exists"]=df1[mask]

but it's not working. What do I need to do to get the following dataframe?

-------------------------------------
column_df1      column_df2_value
-------------------------------------
abc              aaa
pqr              ppp
xyz              jjj
pandas python
2 Answers

Let's try str.split + DataFrame.explode on df2 then merge back to df1:

df2['Column_df2'] = df2['Column_df2'].str.split(';')
df2 = df2.explode('Column_df2')

df3 = df1.merge(df2.rename(columns={'Column_df2': 'Column_df1'}),
                on='Column_df1',
                how='left')

df3:

  Column_df1 Column_df2_value
0        abc              aaa
1        pqr              ppp
2        xyz              jjj
user_154978880

Create list vide str.split, explode and do a left merge

df2=df2.assign(Column_df2=df2['Column_df2'].str.split(';')).explode('Column_df2')
pd.merge(df1,df2, how='left', left_on='Column_df1', right_on='Column_df2')

    Column_df1 Column_df2_value Column_df2
0        abc              aaa        abc
1        pqr              ppp        pqr
2        xyz              jjj        xyz
user_89869750
Related