Add label/multi-index on top of columns-CodePudding

Context: I'd like to add a new multi-index/row on top of the columns. For example if I have this dataframe:

tt = pd.DataFrame({'A':[1,2,3],'B':[4,5,6],'C':[7,8,9]})

Desired Output: How could I make it so that I can add "Table X" on top of the columns A,B, and C?

Possible solutions(?): I was thinking about transposing the dataframe, adding the multi-index, and transpose it back again, but not sure how to do that without having to write the dataframe columns manually (I've checked other SO posts about this as well)

Thank you!

CodePudding user response：

In the meantime I've also discovered this solution:

tt = pd.concat([tt],keys=['Table X'], axis=1)

Which also yields the desired output

  Table X
        A  B  C
0       1  4  7
1       2  5  8
2       3  6  9

CodePudding user response：

If you want a data frame like you wrote, you need a Multiindex data frame, try this:

import pandas as pd

# you need a nested dict first 
dict_nested = {'Table X': {'A':[1,2,3],'B':[4,5,6],'C':[7,8,9]}}

# then you have to reform it 
reformed_dict = {}
for outer_key, inner_dict in dict_nested.items():
    for inner_key, values in inner_dict.items():
        reformed_dict[(outer_key, inner_key)] = values
  
# last but not least convert it to a multiindex dataframe
multiindex_df = pd.DataFrame(reformed_dict)

print(multiIndex_df)
# >>   Table X      
# >>         A  B  C
# >> 0       1  4  7
# >> 1       2  5  8
# >> 2       3  6  9

CodePudding user response：

You can use pd.MultiIndex.from_tuples() to set / change the columns of the dataframe with a multi index:

tt.columns = pd.MultiIndex.from_tuples((
    ('Table X', 'A'), ('Table X', 'B'), ('Table X', 'C')))

Result (tt):

    Table X
    A   B   C
0   1   4   7
1   2   5   8
2   3   6   9

Add-on, as those are multi index levels you can later change them:

tt.columns.set_levels(['table_x'],level=0,inplace=True)
tt.columns.set_levels(['a','b','c'],level=1,inplace=True)

        table_x
        a   b   c
    0   1   4   7
    1   2   5   8
    2   3   6   9