Python: List Nested Dictionary to pandas DataFrame Issue

Question

I am struggling with some simple from_dict conversion. I have a list nested dictionaries in dictionary as below. (quite confusing to me as well)

dict_total = {'Jane' : {'a1' : [1.1,1.3,1.4,1.9],
                        'a2' : [3.1,2.4,2.3,1.2],
                        'a3' : [4.3,2.3,1.5,5.3],
                        'st' : ['d','dc','sc','sc']},
              'Mark' : {'a1' : [3.1,2.3,1.3,1.9],
                        'a2' : [1.2,2.3,9.3,1.2],
                        'a3' : [1.1,5.5,1.2,5.3],
                        'st' : ['cs','s','wc','cd']}
             }

Above is just simple example, but my original contains more then 20000+ keys in dict_total. I want to convert this dictionary to dataframe (hopefully on loops) like below.

df_total = 

         a1     a2      a3      st
Jane     1.1    3.1     4.3     d
Jane     1.3    2.4     2.3     dc
Jane     1.4    2.3     1.5     sc
Jane     1.9    1.2     5.3     sc
Mark     3.1    1.2     1.1     cs
Mark     2.3    2.3     5.5     sc
Mark     1.3    9.3     1.2     wc
Mark     1.9    1.2     5.3     cd

As you can see the keys for dict_total would be the index of dataframe, and each keys for "Jane" and "Mark" will be the column name, and lists for values.

Hope there is a pythonic way to solve this. Thanks

jezrael · Accepted Answer · 2018-03-13T08:24:55.860

3

I think need concat with dict comprehension, last remove first level by reset_index:

df_total = (pd.concat({k: pd.DataFrame(v) for k, v in dict_total.items()})
             .reset_index(level=1, drop=True))
print (df_total)
       a1   a2   a3  st
Jane  1.1  3.1  4.3   d
Jane  1.3  2.4  2.3  dc
Jane  1.4  2.3  1.5  sc
Jane  1.9  1.2  5.3  sc
Mark  3.1  1.2  1.1  cs
Mark  2.3  2.3  5.5   s
Mark  1.3  9.3  1.2  wc
Mark  1.9  1.2  5.3  cd

edited Mar 13 '18 at 08:24

answered Mar 13 '18 at 07:13

jezrael

822,522
95
1,334
1,252

1

Thank you! One curiosity. Is it possible to change from DataFrame like df above to my origianal dict_total? – EJ Kang Mar 13 '18 at 08:24
Umm I think my question was confusing. I have tried to use " df_total.to_dict() " in order to go back to my original dict_total format, but it did not work. Do you know why? – EJ Kang Mar 13 '18 at 08:35
@Kang - I understand what you need, but in my opinion there is no pythonic solution for it. The most pythonic are [dictionaries](https://stackoverflow.com/q/1373164) – jezrael Mar 13 '18 at 08:39

Python: List Nested Dictionary to pandas DataFrame Issue

1 Answers1