Switch rows and columns of a multindex dataframe created from nested dictionary-CodePudding

I converted the following nested dictionary into a data frame:

dic = {'US':{'Traffic':{'new':1415, 'repeat':670}, 'Sales':{'new':67068, 'repeat':105677}},
      'UK': {'Traffic':{'new':230, 'repeat':156}, 'Sales':{'new':4568, 'repeat':10738}}}

df = pd.DataFrame.from_dict({(i,j): dic[i][j]
                            for i in dic.keys()
                            for j in dic[i].keys()
                           })

The data frame looks: Current Output

How can I switch the columns Traffic and Sales into the rows? To get an output of this sort: Required Output

CodePudding user response：

Use collections.defaultdict:

from collections import defaultdict

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)
print(df)
            US            UK       
           new  repeat   new repeat
Traffic   1415     670   230    156
Sales    67068  105677  4568  10738

Your solution should be changed with DataFrame.stack and Series.unstack:

df = pd.DataFrame.from_dict({(i,j): dic[i][j]
                            for i in dic.keys()
                            for j in dic[i].keys()
                           }).stack().unstack(0)
print(df)
           UK            US        
          new repeat    new  repeat
Sales    4568  10738  67068  105677
Traffic   230    156   1415     670