How to a conditional join(like SQL where join) of Dataframes in Python

user_8444708 0
Viewed 411

I have two dataframes like below

df1

visit_counts SG_lat   SG_long
0   3222.0  33.13623    -91.942026
1   6243.0  33.241981   -92.668384
2   5225.0  33.27683    -93.212498
3   6107.0  33.461784   -94.039191
4   3712.0  33.567683   -92.83685799999999

df2

num_transactions lat_dgr    long_dgr
0   45433   35.293364   -93.716224
1   41172   35.293364   -93.716224
2   41909   35.293364   -93.716224
3   37979   35.293364   -93.716224
4   43546   35.293364   -93.716224

I want to inner join these dataframes if the geo distance between two coordinates is less than 100m like below pseudo code i.e 

## pseudo code
coords_1 = (df1.SG_lat, df1.SG_long)
coords_2 = (df2.lat_dgr, df2.long_dgr)
geopy.distance.vincenty(coords_1, coords_2).m < 100

In SQL we can do this with where condition like below

ST_DISTANCE(ST_GEOGPOINT(long_dgr,lat_dgr), ST_GEOGPOINT( sg_long,sg_lat)) <= 100

pandas merge function doesn't allow for where condition. Is there any other way to join these two dataframes. I don't have any other key columns to join and then filter using loc.

pandas-groupby pandas dataframe python
1 Answers

If you wish to compute the distance all row combinations across tables, you can: 1) create a cartesian product of the tables, 2) compute the distance 3) filter on your threshold. This is memory inefficient as you are expanding out all row combinations, but at least straightforward to compute:

import pandas as pd
from geopy.distance import geodesic

# create a dummy key to join all rows from df1 to df2:
df1["dummy_key"] = 0
df2["dummy_key"] = 0

# create cartesian product table
df3 = pd.merge(left=df1, right=df2, on="dummy_key").drop(columns=["dummy_key"])

# apply geodesic (newer version of geopy.distance.vincenty) to get the distance in meters for each row
dist = df3.apply(lambda row: geodesic((row["SG_lat"], row["SG_long"]), (row["lat_dgr"], row["long_dgr"])).m, axis=1) 

# filter for rows that you desire:
df3 = df3[dist < 100]
user_50329410
Related