Search code examples
pythonpandasmulti-index

Pandas multindex on column after merge


I created this dataframe:

d1 = {'john': [10, 11], 'adam': [20,21]}
d2 = {'john': [1,12], 'adam': [3,34]}
test_df1= pd.DataFrame(data=d1)
test_df2= pd.DataFrame(data=d2)

current = pd.concat([test_df1, test_df2], keys=['test_df1','test_df2'], axis=1)
current

enter image description here

but I need to change order in column multiindex. I need to have something like this

data = {('John', 'test_df1'): [10, 11], ('John', 'test_df2'): [1, 12], ('Adam', 'test_df1'): [20, 21], ('Adam','test_df2'): [3,34]}
columns = pd.MultiIndex.from_tuples([('John', 'test_df1'),('John', 'test_df2'),('Adam', 'test_df1'),('Adam', 'test_df2')])
needed = pd.DataFrame(data, columns=columns)
needed

enter image description here

Could you help me :) ?


Solution

  • concat on the other axis, and unstack:

    pd.concat([test_df1, test_df2], keys=['test_df1','test_df2']).unstack(0)
    

    Output:

          john              adam         
      test_df1 test_df2 test_df1 test_df2
    0       10        1       20        3
    1       11       12       21       34