Convert column suffixes from pandas join into a MultiIndex

Question

I have two pandas DataFrames with (not necessarily) identical index and column names.

>>> df_L = pd.DataFrame({'X': [1, 3], 
                         'Y': [5, 7]})

>>> df_R = pd.DataFrame({'X': [2, 4], 
                         'Y': [6, 8]})

I can join them together and assign suffixes.

>>> df_L.join(df_R, lsuffix='_L', rsuffix='_R')

    X_L Y_L X_R Y_R
0   1   5   2   6
1   3   7   4   8

But what I want is to make 'L' and 'R' sub-columns under both 'X' and 'Y'.

The desired DataFrame looks like this:

>>> pd.DataFrame(columns=pd.MultiIndex.from_product([['X', 'Y'], ['L', 'R']]), 
         data=[[1, 5, 2, 6],
               [3, 7, 4, 8]])

    X       Y
    L   R   L   R
0   1   5   2   6
1   3   7   4   8

Is there a way I can combine the two original DataFrames to get this desired DataFrame?

Answer 1

You can use pd.concat with the keys argument, along the first axis:

df = pd.concat([df_L, df_R], keys=['L','R'],axis=1).swaplevel(0,1,axis=1).sort_index(level=0, axis=1)

>>> df
   X     Y   
   L  R  L  R
0  1  2  5  6
1  3  4  7  8