Python – Flattens the nested list of dicts into a Pandas Dataframe

I’m reading the json structure below

{"response":
    {"GDUEACWF":
        {"2018-06-01":
            [{"groupwide_market":"Developed Markets",
            "weights":0.8794132316432903},
            {"groupwide_market":"Developed Markets",
            "weights":0.8794132316432903}],
        "2018-06-02":
            [{"groupwide_market":"Developed Markets",  
            "weights":0.8794132316432903},
            {"groupwide_market":"Developed Markets",
            "weights":0.8794132316432903}]}}}

and try flattening it into a Pandas data frame in the following format.

|data_date  |groupwide_market  |weights
|2018-06-01 | Developed Markets |0.08794132316432903

I tried to do this by iterating through each list in every k,v pair using the following code. It does work, however, it is also slow. 100k rows of data takes more than 30 minutes to generate.

df = pd. DataFrame()
#concatenating each line of the list within each dict cell
for k1,v1 in data['response'][mnemonic].items():
    for ele in v1:
        df_temp = pd.concat({k2: pd. Series(v2) for k2, v2 in ele.items()}).transpose()
        df_temp['data_date'] = k1
        df = df.append(df_temp,ignore_index=True)
df.columns = [x[0] for x in df.columns]

May I know if there is a more efficient way to do this? Tried reading the documentation and examples for json_normalize, but couldn’t figure out how to apply it in this case.

Thanks in advance!

Given a dictionary as data, we can do the following:

import pandas as pd
pd. DataFrame([(date, *nodes.values()) for info in data["response"].values()
              for date, values in info.items() for nodes in values],
              columns=["date", "market", "weight"])

Using the given response as input, the output is as follows: