How to Nested dicts, lists & OrderedDicts to DataFrame

Question

How can I parse the following data structure:

data = {0: None,
     1: [OrderedDict([('N', '1192'),
                   ('Date', '2009-10-08T00:00:00'),
                   ('Rule', '0'),
                   ('N_Dip', '0'),
                   ('Leg',
                    OrderedDict([('Id', '33'),
                                 ('Name', 'Ports'),
                                 ('Type',
                                  OrderedDict([('Id', '2'),
                                               ('Name', 'Dip')]))])),
                   ('Id', '69')]),
      OrderedDict([('N', '382'),
                   ('Date', '2012-11-23T00:00:00'),
                   ('Rule', '0'),
                   ('N_Dip', '0'),
                   ('Leg',
                    OrderedDict([('Id', '33'),
                                 ('Name', 'Ports'),
                                 ('Type',
                                  OrderedDict([('Id', '2'),
                                               ('Name', 'Dip')]))])),
                   ('Id', '70')])],
     2: None,
     3: None,
     4: None,
     5: OrderedDict([('N', '5º'),
                  ('Article', '36º'),
                  ('Date', '0001-01-01T00:00:00'),
                  ('Rule', '0'),
                  ('N_Dip', '0'),
                  ('Leg',
                   OrderedDict([('Id', '7'),
                                ('Name', 'DEVA'),
                                ('Type',
                                 OrderedDict([('Id', '1'),
                                              ('Name', 'Code')]))])),
                  ('Id', '127')]),
     6: None,
     7: None}

into a pandas DataFrame with the columns (not necessarily in this order):

N | Date | Rule | N_Dip | Leg_Id | Leg_Name | Leg_Type | Leg_Type_Id | Leg_Type_Name | Leg_Type_Name | Id | Article

and where each dict key (each number) becomes a row?

Example desired output:

I'm not seeing how to solve this without the use of multilevel indexing, but if you have another idea, feel free.
I don't care about the order of the columns.

Thanks in advance,

EDITS:

corrected "Nome" to "Name" (18/06/2021);
added desired output (18/06/2021).

user_10035985 · Answer

If data is your dictionary:

df = pd.DataFrame(
    [l for v in data.values() for l in (v if isinstance(v, list) else [v]) if v]
)
x = df.pop("Leg").apply(pd.Series).add_prefix("Leg_")
y = x.pop("Leg_Type").apply(pd.Series).add_prefix("Leg_Type_")

out = pd.concat([df, x, y], axis=1)
print(out)

Prints:

      N                 Date Rule N_Dip   Id Article Leg_Id Leg_Name Leg_Nome Leg_Type_Id Leg_Type_Nome Leg_Type_Name
0  1192  2009-10-08T00:00:00    0     0   69     NaN     33    Ports      NaN           2           Dip           NaN
1   382  2012-11-23T00:00:00    0     0   70     NaN     33      NaN    Ports           2           NaN           Dip
2    5º  0001-01-01T00:00:00    0     0  127     36º      7     DEVA      NaN           1           NaN          Code

user_218663 · Answer

After leveraging @andrej-kesely's great list comprehension, I might use json_normalize() to do the work for me. Note that in my test I assumed that there is a typo between "Name" and "Nome" and I made them both "Name".

import pandas
data = [l for v in data.values() for l in (v if isinstance(v, list) else [v]) if v]
print(pandas.json_normalize(data, sep="_"))

Gives:

      N                 Date Rule N_Dip   Id Leg_Id Leg_Name Leg_Type_Id Leg_Type_Name Article
0  1192  2009-10-08T00:00:00    0     0   69     33    Ports           2           Dip     NaN
1   382  2012-11-23T00:00:00    0     0   70     33    Ports           2           Dip     NaN
2    5º  0001-01-01T00:00:00    0     0  127      7     DEVA           1          Code     36º

How to Nested dicts, lists & OrderedDicts to DataFrame

2 Answers