How do I join grid cell data set with 2 travel time data sets?

Question

I was doing an exercise from Automating GIS Processes courses and one of the tasks required me to join a YKR grid cell data set with two data sets of travel times to two shopping centres from every other place in the Helsinki metropolitan area.

Here's the head of the YKR grid cell data set:

# YKR grid cell data set
import geopandas as gpd
grid = gpd.read_file(DATA_DIRECTORY / "YKR_grid_EPSG3067.gpkg")
grid.head()

Here's the travel time data set for shopping centre 1(Itis) (I removed the unnecessary column & replace -1 values with NaN):

shopping_centres_itis = shopping_centres_itis[["from_id","to_id","pt_r_t","car_r_t"]]
shopping_centres_itis = shopping_centres_itis.rename(columns = {'pt_r_t' : 'pt_r_t_Itis', 'car_r_t' : 'car_r_t_Itis'})
shopping_centres_itis['pt_r_t_Itis'] = shopping_centres_itis['pt_r_t_Itis'].replace({-1: numpy.nan})
shopping_centres_itis['car_r_t_Itis'] = shopping_centres_itis['car_r_t_Itis'].replace({-1: numpy.nan})

shopping_centres_itis.head()

Here's the data set for shopping complex 2 (Myyrmanni)

shopping_centres_myyrmanni = shopping_centres_myyrmanni[["from_id","to_id","pt_r_t","car_r_t"]]
shopping_centres_myyrmanni = shopping_centres_myyrmanni.rename(columns = {'pt_r_t' : 'pt_r_t_Myyrmanni', 'car_r_t' : 'car_r_t_Myyrmanni'})
shopping_centres_myyrmanni['pt_r_t_Myyrmanni'] = shopping_centres_myyrmanni['pt_r_t_Myyrmanni'].replace({-1: numpy.nan})
shopping_centres_myyrmanni['car_r_t_Myyrmanni'] = shopping_centres_myyrmanni['car_r_t_Myyrmanni'].replace({-1: numpy.nan})
shopping_centres_myyrmanni.head()

The question is what is the configuration to join the 2 travel time data frames to the YKR grid GeoDataFrame? Should I rename "from_id" column from the travel time data frames to YKR_ID? I tried .sjoin but failed because the travel time data frames weren't GeoDataFrame. Meanwhile, I got KeyError when doing .join

Answer 1

You should use .merge, not .join:

Merge DataFrame or named Series objects with a database-style join.

import geopandas as gpd
from shapely.geometry import Point

grid = gpd.GeoDataFrame(data={"YKR_ID":[5785640, 5785641]}, geometry=[Point(0,0).buffer(10), Point(1,1).buffer(10)])
#print(grid)
#     YKR_ID                                           geometry
# 0  5785640  POLYGON ((10.00000 0.00000, 9.95185 -0.98017, ...
# 1  5785641  POLYGON ((11.00000 1.00000, 10.95185 0.01983, ...

shopping_centres_1 = gpd.pd.DataFrame(data={"name":["itis", "itis"], "from_id":[5785640, 5785641]})
shopping_centres_2 = gpd.pd.DataFrame(data={"name":["myyrmanni", "myyrmanni"], "from_id":[5785640, 5785641]})

merge1 = gpd.pd.merge(left=grid, right=shopping_centres_1, left_on="YKR_ID", right_on="from_id", how="left")
merge2 = gpd.pd.merge(left=merge1, right=shopping_centres_2, left_on="YKR_ID", right_on="from_id", how="left")

# print(merge2[[c for c in merge2.columns if "geom" not in c]]) #Print all columns except geometry
#     YKR_ID name_x  from_id_x     name_y  from_id_y
# 0  5785640   itis    5785640  myyrmanni    5785640
# 1  5785641   itis    5785641  myyrmanni    5785641