Nested dictionary of lists to dataframe

Question

I have a dictionary like this:

 {'a': {'col_1': [1, 2], 'col_2': ['a', 'b']},
 'b': {'col_1': [3, 4], 'col_2': ['c', 'd']}}

When I try to convert this to a dataframe a get this:

     col_1  col_2
a   [1, 2]  [a, b]
b   [3, 4]  [c, d]

But what I need is this:

     col_1  col_2
a      1      a
       2      b
b      3      c
       4      d

How can I get this format. Maybe I should change my input format as well? Thanks for help=)

_Maybe I should change my input format as well?_ By that do you mean modify the data before creating the DataFrame, or modify the source of the data itself? — AMC, Apr 14 '20 at 21:50
Does this answer your question? [Pandas column of lists, create a row for each list element](https://stackoverflow.com/questions/27263805/pandas-column-of-lists-create-a-row-for-each-list-element) — AMC, Apr 14 '20 at 23:00

yatu · Accepted Answer · 2020-04-14T21:46:10.123

1

You can use pd.DataFrame.from_dict setting orient='index' so the dictionary keys are set as the dataframe's indices, and then explode all columns by applying pd.Series.explode:

pd.DataFrame.from_dict(d, orient='index').apply(pd.Series.explode)

  col_1 col_2
a     1     a
a     2     b
b     3     c
b     4     d

edited Apr 14 '20 at 21:46

answered Apr 14 '20 at 21:40

yatu

86,083
12
84
139

sammywemmy · Answer 2 · 2021-01-08T08:03:46.447

you could run a generator comprehension and apply pandas concat ... the comprehension works on the values of the dictionary, which are themselves dictionaries :

pd.concat(pd.DataFrame(entry).assign(key=key) for key,entry in data.items()).set_index('key')

    
    col_1   col_2
key     
a    1       a
a    2       b
b    3       c
b    4       d

update:

Still uses concatenation; no need to assign key to individual dataframes:

 (pd.concat([pd.DataFrame(entry) 
             for key, entry in data.items()], 
             keys=data)
  .droplevel(-1))

Nested dictionary of lists to dataframe

2 Answers2