Multilevel custom json response to pandas-CodePudding

I'm trying to convert the output below into pandas with only these columns:

List (whitelist, blacklist)
QueryWord (gun, trench concrete, tripod....)
ContextWord (1, 2, 3, 4...)
Score (0.1, 0.2....)

api_output = {"whitelist":
                        [{"queryWord": "gun",
                        "topn":[{"contextWord": "1", "score": 0.1},
                                {"contextWord": "2", "score": 0.2}]},

                                        
                        {"queryWord": "trench concrete",
                        "topn":[{"contextWord": "1", "score": 0.11},
                                {"contextWord": "5", "score": 0.6}]}],

                "blacklist":
                        [{"queryWord": "tripod",
                        "topn":[{"contextWord": "1", "score": 0.5},
                                {"contextWord": "5", "score": 0.4}]},

                        {"queryWord": "nail",
                        "topn":[{"contextWord": "1", "score": 0.2},
                                {"contextWord": "5", "score": 0.43}]},
                                        
                        {"queryWord": "cover plastic",
                        "topn":[{"contextWord": "1", "score": 0.65},
                                {"contextWord": "5", "score": 0.95}]}]}

The idea is to have something like this

https://i.stack.imgur.com/ECDD6.png

Any idea ?

CodePudding user response：

Try:

df = pd.DataFrame(
    {"List": api_output.keys(), "data": api_output.values()}
).explode("data")

df["QueryWord"] = df["data"].str["queryWord"]
df["data"] = df["data"].str["topn"]

df = df.explode("data")
df = pd.concat([df, df.pop("data").apply(pd.Series)], axis=1)

print(df)

Prints:

        List        QueryWord contextWord  score
0  whitelist              gun           1   0.10
0  whitelist              gun           2   0.20
0  whitelist  trench concrete           1   0.11
0  whitelist  trench concrete           5   0.60
1  blacklist           tripod           1   0.50
1  blacklist           tripod           5   0.40
1  blacklist             nail           1   0.20
1  blacklist             nail           5   0.43
1  blacklist    cover plastic           1   0.65
1  blacklist    cover plastic           5   0.95