Unable to join to dataframe in pandas

Question

I have two df. The first df is a multiindex and the other one is typical single index.

Figure 1: Multiindex df

and

Figure 2: Single indexing

Upon join these two df, I got the following error

cannot join with no overlapping index names

I suspect, this error due to the index column name in the first df (Figure 1).

Even, swaping the index name and typical numeric value also does not help

Figure 2: Multiindex df

May I know how to address this error?

Thanks in advance for the time taken

Answer 1

You can convert first level in MultiIndex to column before merge:

df = (df1.reset_index(level=0)
         .merge(df2, left_index=True, right_index=True)
         .set_index('name', append=True)
         .swaplevel(1, 0))

Or if use join:

df = df1.reset_index(level=0).join(df2).set_index('name', append=True).swaplevel(1, 0)