Complicated JSON to Pandas Dataframe

Question

I have a somewhat complicated json structure

[
    {
        "name": "iphone 6",
        "vol": 1600,
        "keywords": [
            {
                "positions": [
                    {
                        "date": "2020-06-18",
                        "pos": 25,
                        "change": 0,
                        "price": 0,
                        "is_map": 0,
                        "map_position": 0,
                        "paid_position": 0
                    },
                    {
                        "date": "2020-06-19",
                        "pos": 28,
                        "change": -3,
                        "price": 0,
                        "is_map": 0,
                        "map_position": 0,
                        "paid_position": 0
                    }
            }
        ]
    }
]

I would like to get this json data into a pandas dataframe with the following format

Keyword Name | Volume | Date 1 | Date 2 | Date 3 | ... | Date N
iphone 6     | 1600   | 25     | 28     | 29     | ... | Pos at Date N

I can think of a very complicated and time consuming way to do this, but I'm imagining there is a quick way to do this and I can't find a similar example on StackOverflow. Anyone able to help out here?

Answer 1

If results is your json (dictionary):

rows = []
for item in result:
    row = {
        'Keyword Name': item['name'],
        'Volume': item['vol']
    }
    for idx, pos in enumerate(item['keywords'][0]['positions'], 1):        
        row[f"Date {idx}"] = pos["pos"]
    rows.append(row)
pd.DataFrame(rows)