How to create a column based on the min and max of another dataframe

Question

I have two dataframes, one that contains the min and max of latitude and longitude of a grupo of places, and the second that contains the latitude and longitude of a specific place.

df1

group_places	min_lat	max_lat	min_long	max_long
A	39.1	40.1	9.2	10.5
B	38.10	39.05	8.1	9
C	33.1	35.1	7.1	8
D	31.1	35	6.1	7

df2

place	lat	long
f	34.1	7.9
g	39.2	9.8
h	31.2	6.8

I need to create a column in df2 that shows the group_places based on the latitude & longitude range in df1. (latitude and longitude are just an example)

df_result

place	lat	long	group
f	34.1	7.9	C
g	39.2	9.8	A
h	31.2	6.8	D

Thank you in advance.

Answer 1

With your data size, the cross-merge data is about 200M rows, so it should be doable:

(df2.merge(df1, on='key')
    .query('(min_lat<=lat<=max_lat) and (min_long<=long<=max_long)')
    [['place','lat','long', 'group_places']]
)

Output:

   place   lat  long group_places
2      f  34.1   7.9           C 
4      g  39.2   9.8           A 
11     h  31.2   6.8           D