pandas dataframe from several levels nested dictionary

Question

I am trying to convert a nested dictionary with more than one level of nesting to a Pandas data frame, so I followed this solution:

new_df = pd.DataFrame.from_dict(nested_dict, orient="index")

but I got some columns as other dictionaries:

                                      Saturday  ...                                   Friday
00501               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-1', 'data2': '1'}
00544               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-1', 'data2': '1'}
01001    {'data1': 'DATA1-1', 'data2': '2'}  ...  {'data1': 'DATA1-1', 'data2': '1'}
01002    {'data1': 'DATA1-2', 'data2': '2'}  ...  {'data1': 'DATA1-1', 'data2': '1'}
01003               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-1', 'data2': '1'}
...                                        ...  ...                                      ...
99363  {'data1': 'DATA1-2', 'data2': '2'}  ...  {'data1': 'DATA1-0', 'data2': '2'}
99371               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-0', 'data2': '2'}
99401               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-0', 'data2': '2'}
99402               {'data1': '', 'data2': ''}  ...  {'data1': 'DATA1-0', 'data2': '2'}
99403  {'data1': 'DATA1-2', 'data2': '2'}  ...  {'data1': 'DATA1-0', 'data2': '2'}

[41302 rows x 7 columns]

I can see that first-level keys of the dictionary ("zipcode") became the index of each row instead of another column, and the content of each second-level key became a stringified inner dictionary.

EDIT

This is the model for the original dictionary to convert to a data frame:

{
    '00501': {
        'Saturday': {'data1': 'DATA1-1', 'data2': ''},
        'Sunday': {'data1': '', 'data2': ''},
        ...
    },
    '00544' {
        'Saturday': {'data1': 'DATA1-1', 'data2': ''},
        'Sunday': {'data1': '', 'data2': ''},
        ...
    },
    ...
}

I tried this also, but I got a data frame with a single line and 289,114 columns (I can't find my mistake):

new_df = pd.json_normalize(nested_dict, max_level=1)

How can I convert the nested dictionary to a pandas data frame?

you're likely looking for `.json_normalize()` https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html — ti7, Aug 20 '22 at 20:05
Does this answer your question? [Split / Explode a column of dictionaries into separate columns with pandas](https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas) — ti7, Aug 20 '22 at 20:05

score 0 · Answer 1 · answered Aug 20 '22 at 23:35

This code gave me the result I needed:

new_df = pd.Dataframe.from_dict(nested_dict, orient="index")
new_df["zipcode"] = new_df.index
new_df = new_df.melt(["zipcode"]).sort_values("zipcode")
new_df = pd.json_normalize(new_df.to_dict("records"))
new_df = new_df.rename(
    columns={"variable": "weekday", "value.data1": "data1", "value.data2": "data2"}
)

pandas dataframe from several levels nested dictionary

1 Answers1