Skip to content
Advertisement

Pandas dataframe from nested dictionary

My dictionary looks like this:

{'x': {'b': 10, 'c': 20}, 'y': {'b': '33', 'c': 44}}

I want to get a dataframe that looks like this:

index   col1    col2    val
0        x       b      10
1        x       c      20
2        y       b      33
3        y       c      44

I tried calling pandas.from_dict(), but it did not give me the desired result. So, what is the most elegant, practical way to achieve this?

EDIT: In reality, my dictionary is of depth 4, so I’d like to see a solution for that case, or ideally, one that would work for arbitrary depth in a general setup.

Here is an example of a deeper dictionary: {'x':{'a':{'m':1, 'n':2}, 'b':{'m':10, 'n':20}}, 'y':{'a':{'m':100, 'n':200}, 'b':{'m':111, 'n':222}} } The appropriate dataframe should have 8 rows.

ANSWER:

df = pd.DataFrame([(k1, k2, k3, k4, k5, v) for k1, k2345v in dict.items()
                           for k2, k345v in k2345v.items()
                           for k3, k45v in k345v.items()
                           for k4, k5v in k45v.items()
                           for k5, v in k5v.items()])

Advertisement

Answer

You can use a list comprehension to reorder your dict into a list of tuples where each tuple is a row and then you can sort your dataframe

import pandas as pd

d = {'x': {'b': 10, 'c': 20}, 'y': {'b': '33', 'c': 44}}

df = pd.DataFrame([(k,k1,v1) for k,v in d.items() for k1,v1 in v.items()], columns = ['Col1','Col2','Val'])
print df.sort(['Col1','Col2','Val'], ascending=[1,1,1])

  Col1 Col2 Val
3    x    b  10
2    x    c  20
1    y    b  33
0    y    c  44
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement