Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading