Home » Python » Python Programs

Merge multi-indexed with single-indexed dataframes in pandas

Given a pandas dataframe, we have to merge multi-indexed with single-indexed.
Submitted by Pranit Sharma, on November 24, 2022

Pandas is a special tool that allows us to perform complex manipulations of data effectively and efficiently. Inside pandas, we mostly deal with a dataset in the form of DataFrame. DataFrames are 2-dimensional data structures in pandas. DataFrames consist of rows, columns, and data.

Multilevel Indexing

Multilevel indexing is a type of indexing that include different levels of indexes or simply multiple indexes. The DataFrame is classified under multiple indexes and the topmost index layer is presented as level 0 of the multilevel index followed by level 1, level 2, and so on.

Merging multi-indexed with single-indexed

The merging of a multi-index data frame with a single index data frame is almost similar to a join operation except for the fact that the first data frame is multi-indexed.

To merge multi-indexed with single-indexed, we will set the index of both data frames to some specific columns we will access the first-level values of the first data frame and we will add the new column to the first data frame by retrieving all the columns of the second data frame.

Let us understand with the help of an example,

Python program to merge multi-indexed with single-indexed

# Importing pandas package
import pandas as pd

# Importing numpy package
import numpy as np

# Creating dataframes
df1 = pd.DataFrame([['a', 'x', 0.123], ['a', 'x', 0.234],
                    ['a', 'y', 0.451], ['b', 'x', 0.453]],
                   columns=['first', 'second', 'value1']
                   ).set_index(['first', 'second'])

df2 = pd.DataFrame([['a', 10], ['b', 20]],
                   columns=['first', 'value']).set_index(['first'])

# Display original DataFrames
print("Original DataFrame 1:\n",df1,"\n")
print("Original DataFrame 2:\n",df2,"\n")

# Accessing values of first level
firsts = df1.index.get_level_values('first')

# Adding column to df1
df1['value2'] = df2.loc[firsts].values

# Display merged columns
print("Merged DataFrames:\n",df1)

Output

The output of the above program is:

Python Pandas Programs »