I'm trying to merge a grid cell data set with 7 travel time (to shopping centres) data sets.
Here's the head of the grid cell data:
Here's the head of one of the shopping centre data sets(The other shopping centres have the same amount of column and name but with different suffixes for travel time columns):
I tried to merge all of them with this code:
grid_with_all_shopping_centres = grid.merge(shopping_centres_jumbo,
left_on = "YKR_ID",
right_on = "from_id",
how = 'left'
).merge(shopping_centres_dixi,
left_on = "YKR_ID",
right_on = "from_id",
how = 'left'
).merge(shopping_centres_myyrmanni,
left_on = "YKR_ID",
right_on = 'from_id',
how = 'left'
).merge(shopping_centres_itis,
left_on = "YKR_ID",
right_on = "from_id",
how = "left"
).merge(shopping_centres_forum,
left_on = "YKR_ID",
right_on = 'from_id',
how = 'left'
).merge(shopping_centres_iso_omena,
left_on = "YKR_ID",
right_on = 'from_id',
how = 'left'
).merge(shopping_centres_ruoholahti,
left_on = "YKR_ID",
right_on = 'from_id',
how = 'left')
But the result was this: FutureWarning: Passing 'suffixes' which cause duplicate columns {'to_id_x', 'from_id_x'} in the result is deprecated and will raise a MergeError in a future version. Basically there were duplicates of from_id_x, to_id_x,from_id_y, and to_id_y because the suffixes weren't set.
I tried to set the suffixes with another code but somehow it looks wrong:
grid_merge_1 = gpd.pd.merge(left = grid, right = shopping_centres_jumbo, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Jumbo', '_Jumbo'))
grid_merge_2 = gpd.pd.merge(left = grid_merge_1, right = shopping_centres_dixi, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Dixi', '_Dixi'))
grid_merge_3 = gpd.pd.merge(left = grid_merge_2, right = shopping_centres_myyrmanni, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Myyrmanni', '_Myyrmanni'))
grid_merge_4 = gpd.pd.merge(left = grid_merge_3, right = shopping_centres_itis, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Itis', '_Itis'))
grid_merge_5 = gpd.pd.merge(left = grid_merge_4, right = shopping_centres_forum, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Forum', '_Forum'))
grid_merge_6 = gpd.pd.merge(left = grid_merge_5, right = shopping_centres_iso_omena, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Iso_Omena', '_Iso_Omena'))
grid_with_all_shopping_centres_2 = gpd.pd.merge(left = grid_merge_6, right = shopping_centres_ruoholahti, left_on = "YKR_ID", right_on = "from_id", how = "left", suffixes = ('_Ruoholahti','_Ruoholahti'))
grid_with_all_shopping_centres_2.head()
The first "from_id_Dixi"(3rd column) and "to_id_Dixi"(4th column) should be "from_id_Jumbo" and "to_id_Jumbo"
Therefore, how to set the suffixes properly when merging a lot of data frames?