merge json results and use properties as pandas columns

I have a Json and I’d like to merge the result objects together and have a dataframe that uses "properties" as columns (ID, Title, Site, Last edited time, Link, Status).

Here is what I tried:

    import pandas as pd
    import json
    data = json.load(open('db.json',encoding='utf-8'))
    df = pd.DataFrame(data["results"])
    df2 = pd.DataFrame(df["properties"])

    print(df2)

Here is the json: https://dpaste.com/GV94XD64Y

Here is the result I am expecting:

                Site          Last edited time                       Link Status ID       Title
0  stackoverflow.com  2023-01-16T20:44:00.000Z  https://stackoverflow.com   None  1      page 0
1  stackoverflow.com  2023-01-16T20:44:00.000Z  https://stackoverflow.com   None  1      page 1

>Solution :

You can use apply pd.Series on properties:

df2 = df.properties.apply(pd.Series)

Leave a Reply