How To Convert Nested Dictionary Into A 2d Table

How to convert nested dictionary into a 2D table data[0] is a collection of table rows data[0][0] is a table row, key:year is the column name, key:values is the values in the col

Solution 1:

import pandas

# set up data structures
columns = [
    "year",
    "actual",
    "upper",
    "upper_central",
    "lower_central",
    "lower"
]
value_getter = {
    "year"         : lambda item: item['year'],
    "actual"       : lambda item: item['values'][0]['Actual'],
    "upper"        : lambda item: item['values'][1]['Upper End of Range'],
    "upper_central": lambda item: item['values'][2]['Upper End of Central Tendency'],
    "lower_central": lambda item: item['values'][3]['Lower End of Central Tendency'],
    "lower"        : lambda item: item['values'][4]['Lower End of Range']
}
mydata = {
    "year"         : [],
    "actual"       : [],
    "upper"        : [],
    "upper_central": [],
    "lower_central": [],
    "lower"        : []
}

# repackage datafor item in data[0]:
    for column in columns:
        mydata[column].append(value_getter[column](item))

# and stuff it into pandas
df = pandas.DataFrame(mydata, columns=columns)

then df.T gives

012345678year2009  2010  2011  2012  2013  2014  2015  2016  LongerRunactual(0.2)2.82.02.02.5----upper-----3.03.53.42.4upper_central-----3.03.23.02.3lower_central-----2.83.02.52.2lower-----2.12.22.21.8

Solution 2:

For efficiency you should initialize the data frame but if your data set is small, and if you dont know all the possible strings that occur in the inner-most dictionaries, there is no need to do so.

import pandas as pd
 df=pd.DataFrame
 for dict1 indata[0]:
     for dict2 in dict1['values']:
         for key,valin zip(dict2.keys(),dict2.values()):
                df.loc[key,dict1['year']]=val
df