0

I have a JSON response that I am converting to a DataFrame in Python.

JSON response:

[
  {
    "id": 123456,
    "first_name": "John",
    "last_name": "Doe",
    "fields": [
      {
        "title": "ABC",
        "value": "123"
      },
      {
        "title": "DEF",
        "value": "456"
      }
    ]
  }
]

When I parse this JSON to a DataFrame, the columns appear as id, first_name, last_name and fields. Where the fields column contains the two nested JSON objects:

[{"title": "ABC","value": "123"},{"title": "DEF","value": "456"}]

How would I go about splitting the two objects in fields as their own columns?

For context, I am trying to print the results of the DataFrame to a csv file, where each object in fields has its own column.

Matt
  • 149
  • 1
  • 9

1 Answers1

2

IIUC I am using json_normalize

from pandas.io.json import json_normalize
json_normalize(j, 'fields', ['id', 'first_name', 'last_name'])
Out[1281]: 
  title value last_name first_name      id
0   ABC   123       Doe       John  123456
1   DEF   456       Doe       John  123456

Data input

j=[
  {
    "id": 123456,
    "first_name": "John",
    "last_name": "Doe",
    "fields": [
      {
        "title": "ABC",
        "value": "123"
      },
      {
        "title": "DEF",
        "value": "456"
      }
    ]

  }

]
BENY
  • 317,841
  • 20
  • 164
  • 234
  • Thanks - is there any way that this can be outputted on a single row? Say, using the `title` key as the column header? – Matt Apr 26 '19 at 01:55
  • @Matt I am not sure what you need – BENY Apr 26 '19 at 01:57
  • This is what I am trying to achieve: ABC DEF last_name first_name id 0 123 456 Doe John 123456 – Matt Apr 26 '19 at 02:03
  • Not too sure how to format the comment. But essentially I am trying to get the `title` key as columns, for example, 5 columns being `id`, `first_name`, `last_name`, `ABC`, `DEF`. – Matt Apr 26 '19 at 02:08