nested dict into a dataframe

Question

hi i have a python dictionary data structure like the below

defaultdict(<function __main__.<lambda>()>,
            {'AMZN': [{'improv_sharpe': -4.9719673781114265e-05},
              {'stan_sharpe': 0.5995152805688786},
              {'prob_sharpe': 1.0159440451398998e-19},
              {},
              {},
              {}],
             'GOOGL': [{'improv_sharpe': 2.6114809517957284e-05},
              {'stan_sharpe': 0.16322865760123956},
              {'prob_sharpe': 6.376598832386561e-72},
              {},
              {},
              {}]})

how to i turn the above into the dataframe below:

                      AMZN                                     GOOGL
improv_sharpe': -4.9719673781114265e-05                   2.6114809517957284e-05

etc

Does this answer your question? [Construct pandas DataFrame from items in nested dictionary](https://stackoverflow.com/questions/13575090/construct-pandas-dataframe-from-items-in-nested-dictionary) — C8H10N4O2, Aug 06 '20 at 15:20
apologies i am trying the reference but i cannot seem to get it to work. @C8H10N4O2 — Andrew Czeizler, Aug 06 '20 at 15:34

C8H10N4O2 · Accepted Answer · 2020-08-06T16:13:49.113

This can be solved with list and dict comprehensions as can many similar questions, but I couldn't find an exact duplicate.

If your example data is:

example_data = {'AMZN': [{'improv_sharpe': -4.9719673781114265e-05},
              {'stan_sharpe': 0.5995152805688786},
              {'prob_sharpe': 1.0159440451398998e-19},
              {},
              {},
              {}],
             'GOOGL': [{'improv_sharpe': 2.6114809517957284e-05},
              {'stan_sharpe': 0.16322865760123956},
              {'prob_sharpe': 6.376598832386561e-72},
              {},
              {},
              {}]
 }

then:

import pandas as pd
pd.DataFrame({comp_nm :     # company name is the column name
               pd.Series({  # company data is the column vector
                  k:v for d in comp_data # since your company entries are lists of length one 
                      for k,v in d.items() if d # remove empty {}'s which will throw error
                 }) 
              # each company is a dict with name as key and list of length one as value
              for comp_nm, comp_data in example_data.items() 
})

returns:

                       AMZN         GOOGL
improv_sharpe -4.971967e-05  2.611481e-05
stan_sharpe    5.995153e-01  1.632287e-01
prob_sharpe    1.015944e-19  6.376599e-72

You can learn more about lists, dicts, and comprehensions here.

Beautiful elegant code, thank you good sir, I am in your debt. I will mark the question as answered. — Andrew Czeizler, Aug 07 '20 at 13:17

nested dict into a dataframe

1 Answers1