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

Question

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

Possible duplicate: [Pandas read nested json](/q/40588852/4518341) — wjandrea, Aug 09 '23 at 21:52
Does this answer your question? [Split / Explode a column of dictionaries into separate columns with pandas](https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas) The top answer covers how to do what you want verbatim (you would just need to `explode` first), but the second answer covers `json_normalize`. — wjandrea, Aug 10 '23 at 16:07

score 0 · Accepted Answer · edited Aug 10 '23 at 16:12

0

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

edited Aug 10 '23 at 16:12

wjandrea

28,235
9
60
81

answered Aug 09 '23 at 21:38

Andre Araujo

2,348
2
27
41

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

1 Answers1