0

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:

enter image description here

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):

enter image description here

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() 

enter image description here

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.