In this article we will discuss how to merge two dataframes in index of both the dataframes or index of one dataframe and some column of any other dataframe. Also, we will see how to keep the similar index in merged dataframe.

In previous two articles we have discussed about many features of Dataframe.merge(). Therefore here just a small intro of API i.e.

LINK

DataFrame.merge()

It’s used to merge dataframes.

Here we will focus on a few arguments only i.e.

  • on : Column name on which merge will be done. If not provided then merged on indexes.
  • left_on : Specific column names in left dataframe, on which merge will be done.
  • right_on : Specific column names in right dataframe, on which merge will be done.
  • left_index : bool (default False)
    • If True will choose index from left dataframe as join key.
  • right_index : bool (default False)
    • If True will choose index from right dataframe as join key.

Let’s see some examples to see how to merge dataframes on index.

First of all, let’s create two dataframes to be merged.

Dataframe 1:
This dataframe contains the details of the employees like, name, city, experience & Age. Index of the dataframe contains the IDs i.e.

Contents of the first dataframe created are,

Dataframe 2:
This dataframe contains the details of the employees like, salary, bonus and experience. . Index of the dataframe contains the IDs i.e.

Contents of the second dataframe created are,

Merging Dataframes by index of both the dataframes

As both the dataframe contains similar IDs on the index. So, to merge the dataframe on indices pass the left_index & right_index arguments as True i.e.

Contents of the merged dataframe are,

Both the dataframes are merged on index using default Inner Join. By this way we basically merged the dataframes by index and also kept the index as it is in merged dataframe.

Merge two Dataframes on index of one dataframe and some column of other dataframe

What if we want to merge two dataframe by index of first dataframe and on some column of second dataframe ?

For example let’s change the dataframe salaryDfObj by adding a new column ‘EmpID‘ and also reset it’s index i.e.

Contents of the dataframe 2 salaryDfObj are,

Now to merge dataframe 1 i.e. empDFObj by index with dataframe 2 i.e. salaryDfObj on some column pass following arguments,

  • left_index=True
  • right_on=’EmpID’

For example,

Contents of the merged dataframe are,

Here, in merged dataframe we also set the column ‘EmpID‘ as the index of dataframe.

In another scenario we can also do the vice versa i.e. merge two dataframe on some column of first dataframe and by index of second dataframe by passing following arguments right_index=True and left_on=<Column_Name>.

By this we also kept the index as it is in merged dataframe.

Complete example is as follows,

Output:

 

If you didn't find what you were looking, then do suggest us in the comments below. We will be more than happy to add that.

Do Subscribe with us for more Articles / Tutorials like this,