DataFrame from list with nested dicts where key of first dict is column and key, value of second dict is row and value

Question

I have a datastructure which looks like this:

my_structure = [{'description': 'description',
  'network_element': 'network-elem1',
  'data_json': {'2018-01-31 00:00:00': 10860,
   '2018-02-28 00:00:00': 11530,
   '2018-03-31 00:00:00': 11530,
   '2018-04-30 00:00:00': 8100,
   '2018-05-31 00:00:00': 5060,
   '2018-06-30 00:00:00': 4470,
   '2018-07-31 00:00:00': 4390,
   '2018-08-31 00:00:00': 6620,
   '2018-09-30 00:00:00': 3070,
   '2018-10-31 00:00:00': 18670,
   '2018-11-30 00:00:00': 19880,
   '2018-12-31 00:00:00': 4700}},
 {'description': 'description',
  'network_element': 'network-elem-2',
  'data_json': {'2015-01-01 00:00:00': 92, '2016-01-01 00:00:00': 109}},
 {'description': 'description',
  'network_element': 'network-elem3',
  'data_json': {'2018-01-31 00:00:00': 0,
   '2018-02-28 00:00:00': 0,
   '2018-03-31 00:00:00': 0,
   '2018-04-30 00:00:00': 0,
   '2018-05-31 00:00:00': 0,
   '2018-06-30 00:00:00': 0,
   '2018-07-31 00:00:00': 0,
   '2018-08-31 00:00:00': 1000,
   '2018-09-30 00:00:00': 0,
   '2018-10-31 00:00:00': 0,
   '2018-11-30 00:00:00': 7230,
   '2018-12-31 00:00:00': 28630}},
 {'description': 'description',
  'network_element': 'network-elem...',
  'data_json': {'2015-01-01 00:00:00': 264, '2016-01-01 00:00:00': 37}},
 {'description': 'description',
  'network_element': 'network-elem5',
  'data_json': {'2018-01-31 00:00:00': 69220,
   '2018-02-28 00:00:00': 80120,
   '2018-03-31 00:00:00': 80800,
   '2018-04-30 00:00:00': 60560,
   '2018-05-31 00:00:00': 35250,
   '2018-06-30 00:00:00': 0,
   '2018-07-31 00:00:00': 290,
   '2018-08-31 00:00:00': 0,
   '2018-09-30 00:00:00': 540,
   '2018-10-31 00:00:00': 69350,
   '2018-11-30 00:00:00': 59410,
   '2018-12-31 00:00:00': 70670}},
 {'description': 'descr',
  'network_element': 'network-elem',
  'data_json': {'2015-01-01 00:00:00': 498, '2016-01-01 00:00:00': 526}},
 .....

So basically a list with dicts that contain other dicts.

What I want to create from this is a DataFrame where the values of the network_elements are the columns of my DataFrame. The keys of the nested dicts should be my indices and the values of the nested dict should be my values.

I actually got this to work using two list comprehensions and then filling the df an transposing it like so:

    columns = [elem["network_element"] for elem in my_structure]
    df_data = [elem["data_json"] for elem in my_structure]

    result = pd.DataFrame(df_data, index=columns).T.sort_index()

But I would think this is not too good of a solution since I am splitting the data up into two lists. I am looking for a pandas-solution that does that in a single loop.

Doing a loc like so

df = pd.DataFrame()
for elem in my_structure:
    result.loc[elem["data_json"].keys(), elem["network_element"]] = elem["data_json"].values()

throws me a key error:

KeyError: "None of [Index .... ] are in the [index]"

Is there a simple solution to achieve this? A helping 🤚would be appreciated :) Thanks in advance!

Output of pd.DataFrame.from_dict(....) as suggested

                     ne1     ne2    ne3    ne4    ne5   ne6     ne7   ne8  \
2015-01-01 00:00:00  92      264    498    1086   1022   116    713    40      
2016-01-01 00:00:00  109     37     526    1177   1168   123    733    40      

                     ne9    ne10    ne11    ne12   ne13    ne14    ne15  \
2015-01-01 00:00:00  123     61      21      159    14      37      756      
2016-01-01 00:00:00  117     115     23      160    8       22      777      

                     ne16  
2015-01-01 00:00:00  132    
2016-01-01 00:00:00  124

Answer 1

Would something like this work:

pd.DataFrame.from_dict({elem['network element']: elem['data_json'] for elem in my_structure})

I can't test as your my_structure isn't big enough.

EDIT: You can pass orient='index' if you want to make the data as rows