Pandas - Merging two dataframes by index ID

Question

I have 2 Dataframes as below:

Dataframe1:

   6     count
store_1   10
store_2   23
store_3   53

Dataframe2:

store_name  location
store_1     location_a
store_2     location_b

I am trying to join the above two Dataframes such that I get the below output:

   6     count  location
store_1   10    location_a
store_2   23    location_b
store_3   53

I am trying to merge Dataframe1 with Dataframe2 using index id of the column and not by the column name.

@anky_91, the first row are the column names. I am trying to build this such that it tries to join the two dataframes using the first column in each of the dataframes. Hence I am trying to join them by first column of the Dataframe — Kevin Nash
– Kevin Nash, Commented Apr 13, 2019 at 12:53
how does this work? df1.set_index(6).combine_first(df.set_index('store_name')) — anky
– anky, Commented Apr 13, 2019 at 13:02

Yash Nag · Accepted Answer · 2019-04-13 13:27:48Z

2

Left merge on indexes would work for you.

data = {'6': ['store1', 'store2', 'store3'], 'count': [10, 23, 53]}
df1 = pd.DataFrame(data).set_index('6')
df1

data = {'store_name': ['store1', 'store2'], 'location': ['location_a', 'location_b']}
df2 = pd.DataFrame(data).set_index('store_name')
df2

df1.merge(df2, left_index=True, right_index=True, how='left')

Hope this helps.

answered Apr 13, 2019 at 13:27

Yash Nag

1,26513 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Kovtun · Accepted Answer · 2019-04-13 13:19:40Z

1

pd.concat([df1, df2], axis=1)

simply adds columns (axis=1) from df2 to 1st data frame

answered Apr 13, 2019 at 13:19

Pavel Kovtun

3672 silver badges8 bronze badges