Constructing a pandas DataFrame with columns and sub-columns from dict

Question

I have a dict of the following form

dict = {
    "Lightweight_model_20221103_downscale_1536px_RecOut": {
        "CRR": "75.379",
        "Sum Time": 33132,
        "Sum Detection Time": 18406,
        "images": {
            "uk_UA_02  (1).jpg": {
                "Time": "877",
                "Time_detection": "469"
            },
            "uk_UA_02  (10).jpg": {
                "Time": "914",
                "Time_detection": "323"
            },
            "uk_UA_02  (11).jpg": {
                "Time": "1169",
                "Time_detection": "428"
            },
            "uk_UA_02  (12).jpg": {
                "Time": "881",
                "Time_detection": "371"
            },
            "uk_UA_02  (13).jpg": {
                "Time": "892",
                "Time_detection": "335"
            }
        }
    },
    "Lightweight_model_20221208_RecOut": {
        "CRR": "71.628",
        "Sum Time": 41209,
        "Sum Detection Time": 25301,
        "images": {
            "uk_UA_02  (1).jpg": {
                "Time": "916",
                "Time_detection": "573"
            },
            "uk_UA_02  (10).jpg": {
                "Time": "927",
                "Time_detection": "442"
            },
            "uk_UA_02  (11).jpg": {
                "Time": "1150",
                "Time_detection": "513"
            },
            "uk_UA_02  (12).jpg": {
                "Time": "1126",
                "Time_detection": "531"
            },
            "uk_UA_02  (13).jpg": {
                "Time": "921",
                "Time_detection": "462"
            }
        }
    }
}

and I want to make DataFrame with sub-columns in output like on image

[![enter image description here][1]][1]

but I don't understand how to open subdicts in ['images'] when I use code

df = pd.DataFrame.from_dict(dict, orient='index')
df_full = pd.concat([df.drop(['images'], axis=1), df['images'].apply(pd.Series)], axis=1)

receive dictionaries in columns whit filenames

[![result][2]][2]

how to open nested dicts and convert them to sub-columns [1]: https://i.sstatic.net/hGrKo.png [2]: https://i.sstatic.net/8LlUW.png

Answer 1

Here is one way to do it with the help of Pandas json_normalize, MultiIndex.from_product, and concat methods:

import pandas as pd


df = pd.DataFrame.from_dict(dict, orient='index')

# Save first columns and add a second empty level header
tmp = df[["CRR", "Sum Time", "Sum Detection Time"]]
tmp.columns = [tmp.columns, ["", "", ""]]
dfs= [tmp]

# Process "images" column
df = pd.DataFrame.from_dict(df["images"].to_dict(), orient='index')

# Create new second level column header for each column in df
for col in df.columns:
    tmp = pd.json_normalize(df[col])
    tmp.index = df.index
    tmp.columns = pd.MultiIndex.from_product([[col], tmp.columns])
    dfs.append(tmp)

# Concat everything in a new dataframe
new_df = pd.concat(dfs, axis=1)

Then:

print(new_df)

Outputs: