How to convert a nested dictionary to pandas dataframe?

Question

I have a dictionary "my_dict" in this format:

{'l1':{'c1': {'a': 0, 'b': 1, 'c': 2},
       'c2': {'a': 3, 'b': 4, 'c': 5}},
 'l2':{'c1': {'a': 0, 'b': 1, 'c': 2},
       'c2': {'a': 3, 'b': 4, 'c': 5}}
}

Currently, I am using pd.DataFrame.from_dict(my_dict, orient='index') and get a df like this:

                             c2                           c1
l1  {u'a': 3, u'c': 5, u'b': 4}  {u'a': 0, u'c': 2, u'b': 1}
l2  {u'a': 3, u'c': 5, u'b': 4}  {u'a': 0, u'c': 2, u'b': 1}

However, what I want is both l1/l2 and c2/c3 as indexes and a/b/c as columns.
Something like this:

       a   b   c
l1 c1  0   1   2
   c2  3   4   5
l2 c1  0   1   2
   c2  3   4   5

What's the best way to do this?

duplicate of this? http://stackoverflow.com/questions/13575090/construct-pandas-dataframe-from-items-in-nested-dictionary — JohnE, Nov 16 '16 at 20:34
@JohnE, not an exact duplicate, I tried that one, and although it gets very close, the columns still need sorting, but excellent suggestion! — chickity china chinese chicken, Nov 16 '16 at 20:38

score 7 · Accepted Answer · answered Nov 16 '16 at 21:37

Consider a dictionary comprehension to build a dictionary with tuple keys. Then, use pandas' MultiIndex.from_tuples. Below ast is used to rebuild you original dictionary from string (ignore the step on your end).

import pandas as pd
import ast

origDict = ast.literal_eval("""
{'l1':{'c1': {'a': 0, 'b': 1, 'c': 2},
       'c2': {'a': 3, 'b': 4, 'c': 5}},
 'l2':{'c1': {'a': 0, 'b': 1, 'c': 2},
       'c2': {'a': 3, 'b': 4, 'c': 5}}
}""")

# DICTIONARY COMPREHENSION
newdict = {(k1, k2):v2 for k1,v1 in origDict.items() \
                       for k2,v2 in origDict[k1].items()}
print(newdict)
# {('l1', 'c2'): {'c': 5, 'a': 3, 'b': 4},
#  ('l2', 'c1'): {'c': 2, 'a': 0, 'b': 1},
#  ('l1', 'c1'): {'c': 2, 'a': 0, 'b': 1},
#  ('l2', 'c2'): {'c': 5, 'a': 3, 'b': 4}}

# DATA FRAME ASSIGNMENT
df = pd.DataFrame([newdict[i] for i in sorted(newdict)],
                  index=pd.MultiIndex.from_tuples([i for i in sorted(newdict.keys())]))    
print(df)
#        a  b  c
# l1 c1  0  1  2
#    c2  3  4  5
# l2 c1  0  1  2
#    c2  3  4  5

Fantastic! Thanks @Parfait! Just wondering why you used ast.literal_eval? — George Liu, Nov 16 '16 at 21:47
Great! Using `ast.literal_eval` avoids having to redefine your dict but create it directly from string just as you posted. — Parfait, Nov 17 '16 at 00:44

How to convert a nested dictionary to pandas dataframe?

1 Answers1