Add another level of headers to multiindex dataframe

Question

I have the following dataframe:

dic = {'US':{'Quality':{'points':"-2 n", 'difference':'equal', 'stat': 'same'}, 'Prices':{'points':"-7 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"3 n", 'difference':'positive', 'stat': 'below'}},
      'UK': {'Quality':{'points':"3 n", 'difference':'equal', 'stat': 'above'}, 'Prices':{'points':"-13 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"2 n", 'difference':'negative', 'stat': 'same'}}}
d1 = defaultdict(dict)
for k, v in dic.items():
    for k1, v1 in v.items():
        for k2, v2 in v1.items():
            d1[(k, k2)].update({k1: v2})

df = pd.DataFrame(d1)

df.columns = df.columns.rename("Skateboard", level=0)
df.columns = df.columns.rename("Q3", level=1)
df.insert(loc=0, column=('', 'Mode'), value="Website")

Currently, it looks like this:

How do I add another level of headers to the multiindex dataframe to make it look like the image below?

Update:

dic = {'US':{'Quality':{'points':"-2 n", 'difference':'equal', 'stat': 'same'}, 'Prices':{'points':"-7 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"3 n", 'difference':'positive', 'stat': 'below'}},
      'UK': {'Quality':{'points':"3 n", 'difference':'equal', 'stat': 'above'}, 'Prices':{'points':"-13 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"2 n", 'difference':'negative', 'stat': 'same'}}}
d1 = defaultdict(dict)
for k, v in dic.items():
    for k1, v1 in v.items():
        for k2, v2 in v1.items():
            d1[(k, k2)].update({k1: v2})

df = pd.DataFrame(d1)

df.columns = df.columns.rename("Skateboard", level=0)
df.columns = df.columns.rename("Metric", level=1)
df1 = df.xs('points', axis=1, level=1, drop_level=False)
df2 = df.drop('points', axis=1, level=1)
df3 = (pd.concat([df1, df2], keys=['GM', ''], axis=1)
 .swaplevel(0, 1, axis=1)
 .sort_index(axis=1))
df3.columns = df3.columns.rename("Q3", level=1)
df3.insert(loc=0, column=('','', 'Mode'), value="Website")

df3

Now the data frame looks like:

How do I move the header GM to be first for the column for both US and UK (see the second image for the final output)?

Panda Kim · Accepted Answer · 2022-11-28 06:07:45Z

1

Example

data = {('A', 'a'): {0: 8, 1: 3, 2: 4},
        ('A', 'b'): {0: 5, 1: 7, 2: 8},
        ('A', 'c'): {0: 1, 1: 7, 2: 6},
        ('B', 'a'): {0: 7, 1: 1, 2: 0},
        ('B', 'b'): {0: 1, 1: 1, 2: 7},
        ('B', 'c'): {0: 7, 1: 7, 2: 4}}
df = pd.DataFrame(data)

df

    A           B
    a   b   c   a   b   c
0   8   5   1   7   1   7
1   3   7   7   1   1   7
2   4   8   6   0   7   4

Code

make new level and add c to a column, add d except a

df with a (df1)

df1 = df.xs('a', axis=1, level=1, drop_level=False)

output(df1):

df except a (df2)

df2 = df.drop('a', axis=1, level=1)

output(df2):

    A       B
    b   c   b   c
0   5   1   1   7
1   7   7   1   7
2   8   6   7   4

concat df1 & df2 with key

pd.concat([df1, df2], keys=['c', 'd'], axis=1)

output:

    c       d
    A   B   A       B
    a   a   b   c   b   c
0   8   7   5   1   1   7
1   3   1   7   7   1   7
2   4   0   8   6   7   4

swaplevel and sort

(pd.concat([df1, df2], keys=['c', 'd'], axis=1)
 .swaplevel(0, 1, axis=1)
 .sort_index(axis=1))

result:

    A           B
    c   d       c   d
    a   b   c   a   b   c
0   8   5   1   7   1   7
1   3   7   7   1   1   7
2   4   8   6   0   7   4

we can add level to columns

or use simple way

df3 = pd.concat([df], keys=[''], names=['Q3'], axis=1).swaplevel(0, 1, axis=1)
df3.columns = df3.columns.map(lambda x: (x[0], 'c', x[2]) if x[2] == 'a' else x)

df3

    A           B
Q3  c           c   
    a   b   c   a   b   c
0   8   5   1   7   1   7
1   3   7   7   1   1   7
2   4   8   6   0   7   4

edited Nov 28, 2022 at 6:07

answered Nov 28, 2022 at 3:28

Panda Kim

13.7k2 gold badges8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

M J Over a year ago

I used your code as a template to work on my dataframe. Some changes are still needed to get to the required output. I made an update on my post. Please check.

Panda Kim Over a year ago

A simple solution is that make key by alphabetical order. and you can rename key to GM

M J Over a year ago

Okay, I was trying this df3.sortlevel(level = 1, ascending = False). But it throws an error: 'DataFrame' object has no attribute 'sortlevel'

Panda Kim Over a year ago

i update my answer with more simple code

M J Over a year ago

When I export df3 to excel, it merges level 1 column c spaning the level 2 columns: a,b,c. I only want it to be above level 2 column a

|

Collectives™ on Stack Overflow

Add another level of headers to multiindex dataframe

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related