[SOLVED] With pandas, how can I split a JSON into multiple lines, and then split each line into multiple columns?

With pandas, how can I split a JSON into multiple lines, and then split each line into multiple columns?

Given this JSON:

sample = [
    {
        "id": 1,
        "name": "Tiago",
        "activities": [
            {
                "task_id": 1,
                "task_name": "Clean the house",
                "date": 1683687600000
            },
            {
                "task_id": 2,
                "task_name": "Play piano",
                "date": 1683687600000
            }
        ]
    },
    {
        "id": 2,
        "name": "Frank",
        "activities": [
            {
                "task_id": 1,
                "task_name": "Walk with the dog",
                "date": 1683687600000
            },
            {
                "task_id": 2,
                "task_name": "Go to the gym",
                "date": 1683687600000
            }
        ]
    },
]

I tried this:

df = pd.DataFrame.from_records(sample)

   id   name                                         activities
0   1  Tiago  [{'task_id': 1, 'task_name': 'Clean the house'...
1   2  Frank  [{'task_id': 1, 'task_name': 'Walk with the do...

How can I explode first in lines and then in columns like that:

   id   name task_id          task_name           date
0   1  Tiago       1    Clean the house  1683687600000
1   1  Tiago       2         Play piano  1683687600000
2   2  Frank       1  Walk with the dog  1683687600000
3   2  Frank       2      Go to the gym  1683687600000

Solution

I solved it with one line of code using json_normalize:

pd.json_normalize(sample, "activities", ["id", "name"])

   task_id          task_name           date id   name
0        1    Clean the house  1683687600000  1  Tiago
1        2         Play piano  1683687600000  1  Tiago
2        1  Walk with the dog  1683687600000  2  Frank
3        2      Go to the gym  1683687600000  2  Frank