Setting a new column based in a dataframe based on condition of other df-CodePudding

I have two pandas df data1, data2

data1 is

place   lat        long
pl-1   56.130432  106.346887
pl-2   56.182371  106.331682
pl-3   56.182370  106.331685

and data2 is

gridid    minlat   minlong     maxlat     maxlong
31      56.130430  106.346885  56.130434  106.346889
32      56.182369  106.331680  56.182379  106.331685

I want to add a gridId in data1, according to the condition

df1['lat]>=data2['minLat'] and df1['long']>=data2['minLong'] and df1['lat']<=data2['maxLat'] and df1['long'] <=df2['maxLong']

so that

data1 is

place   lat        long        grid
pl-1   56.130432  106.346887    31
pl-2   56.182371  106.331682    32
pl-3   56.182370  106.331685    32

I tried but was not able to get behind the logic in pandas, all the qsn in sf are only over 1 condition.

CodePudding user response：

First need merge both DataFrames, there is no common column, so used cross join, then is possible filtering:

df = df1.merge(data2, how='cross')
m = (df['lat']>=df['minLat']) & (df['long']>=df['minLong']) & 
    (df['lat']<=df['maxLat']) & (df['long'] <=df['maxLong'])

out = df.loc[m, df1.columns.tolist()   ['gridid']]