Add label/multi-index on top of columns

user_19053778 0
Viewed 50

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?

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

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!

pandas dataframe python
3 Answers

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
user_190537780

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
user_113050290

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
user_118153130
Related