Checking attribute condition using two unequal dataframes and then applying spatial join

Question

I am having a problem with checking attribute condition whether the attributes match in two separate dataframes and then applying spatial join.

Little background: I have two dataframes. DF1 = digitised is with 138 rows and a common field called road_id. DF2 = buffer_dissolved which has only 4 rows and a common field called road_id.

i think it could have been done with following code if they both had same records but its not the case here.

np.where(digitised['road_id'] == buffer_dissolved['road_id'], 'True', 'False')

Now the workaround is I apply spatial join first and then check conditional comparison whether both have same road_id or not but this is not exactly what i want.

Partial working

sj = gpd.sjoin(digitised, buffer_dissolved, how='left', predicate='within', lsuffix='D', rsuffix='B')

failed attempts include: trying isin, ==. also tried converting buffer_dissolved dataframe to gpd.Series but no luck

broken code:

#sj=gpd.GeoDataFrame(crs=digitised.crs)

for row in digitised.itertuples():
    if row['road_id'].isin(buffer_dissolved['road_id']):
        sj = gpd.sjoin(digitised, buffer_dissolved, how='left', predicate='within', lsuffix='D', rsuffix='B')

#sj.to_file('something.shp')

p.s: dont flag it for duplicate question as its nature is different. both dataframes are having diff field names and different number of records so merge, concat etc cant be applied

Answer 1

Maybe I misunderstand you, but you can use .isin like this to create a third dataframe of the records in df1 that exists in df2:

import pandas as pd

data1 = {'col_1': [3, 2, 1, 0], 'col_2': ['a', 'b', 'c', 'd']}
data2 = {'col_1': [9, 2, 1, 8], 'col_2': ['e', 'f', 'g', 'h']}

df1 = pd.DataFrame.from_dict(data1)
#   col_1 col_2
#0      3     a
#1      2     b
#2      1     c
#3      0     d
df2 = pd.DataFrame.from_dict(data2)
#   col_1 col_2
#0      9     e
#1      2     f
#2      1     g
#3      8     h

df3 = df1.loc[df1['col_1'].isin(df2['col_1'])]
#   col_1 col_2
#1      2     b
#2      1     c

Or if you want a column in df1:

import numpy as np
df1['in2'] = np.where(df1['col_1'].isin(df2['col_1']), 'yes', 'no')

   col_1 col_2  in2
0      3     a   no
1      2     b  yes
2      1     c  yes
3      0     d   no