json_normalize in Python

Question

I am trying here to use json_normalize to somehow format the output of an API, but I keep getting a faulty and empty csv file. I tried to change df2 = pd.json_normalize(response, record_path=['LIST']) , but keep getting this error message:

TypeError: byte indices must be integers or slices, not str

Could you please guide me on what am I doing wrong ?

Thanks a lot !

import requests
import json
import pandas as pd

url = "https://*hidden*Results/"

payload = json.dumps({
  "id": 12345
})
headers = {
  'Authorization': 'Basic *hidden*',
  'Content-Type': 'application/json'
}

response = requests.request("POST", url, headers=headers, data=payload)


df1 = pd.DataFrame(response).iloc[:,:-2]
df2 = pd.json_normalize(response, record_path=None)

df = pd.concat([df1, df2], axis=1)
df.to_csv("test.csv", index=False)

I'm not expert with pandas. Just to understand, it works with record_path=None bot not when you pass record_path=['LIST']? The documentation says that can receive record_path: str or list of str, default None, have you tried only with 'LIST'? — Jony_23
– Jony_23, Commented Apr 11, 2022 at 19:38
Do you have the sample of the json file you are working with? — Batselot
– Batselot, Commented Apr 11, 2022 at 19:42
It is not working neither with record_path=['LIST'], neither with =None. Unfortunately, cannot really share the json file. — Alexander Porcescu
– Alexander Porcescu, Commented Apr 11, 2022 at 19:55

anibal jasin · Accepted Answer · 2022-04-11 20:34:41Z

3

You are passing the variable response in the call:

df2 = pd.json_normalize(response, record_path=None)

Which is an a requests.models.Response Object and you need to pass a dict, so you need to do something like pd.json_normalize(response.json(), record_path=['LIST'])

I tried it with this example and works:

>>> import pandas as pd
>>> data = [
...     {
...         "state": "Florida",
...         "shortname": "FL",
...         "info": {"governor": "Rick Scott"},
...         "counties": [
...             {"name": "Dade", "population": 12345},
...             {"name": "Broward", "population": 40000},
...             {"name": "Palm Beach", "population": 60000},
...         ],
...     },
...     {
...         "state": "Ohio",
...         "shortname": "OH",
...         "info": {"governor": "John Kasich"},
...         "counties": [
...             {"name": "Summit", "population": 1234},
...             {"name": "Cuyahoga", "population": 1337},
...         ],
...     },
... ]
>>> result = pd.json_normalize(data, ["counties"])
>>> result
         name  population
0        Dade       12345
1     Broward       40000
2  Palm Beach       60000
3      Summit        1234
4    Cuyahoga        1337

EDIT I will try to do this:

import requests
import json
import pandas as pd

url = "https://*hidden*Results/"

payload = json.dumps({
  "id": 12345
})
headers = {
  'Authorization': 'Basic *hidden*',
  'Content-Type': 'application/json'
}

response = requests.request("POST", url, headers=headers, data=payload)

json_response = response.json()

df1 = pd.DataFrame(json_response).iloc[:,:-2]
df2 = pd.json_normalize(json_response, record_path=['LIST'])

df = pd.concat([df1, df2], axis=1)
df.to_csv("test.csv", index=False)

edited Apr 11, 2022 at 20:34

answered Apr 11, 2022 at 19:44

anibal jasin

1411 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alexander Porcescu Over a year ago

Hm.. seems like the same error message on line: result = pd.json_normalize(data, ["counties"]) TypeError: byte indices must be integers or slices, not str

anibal jasin Over a year ago

This example is adapted from the pandas docs so you can try the one from the docs, but I have checked it again and it works fine for me. Are you sure this error is coming from the example I sent you?

Alexander Porcescu Over a year ago

Update: After running the below, I got the message: Empty DataFrame Columns: [] Index: [0] //code// df = pd.DataFrame(response) df = pd.json_normalize(df) print(df)

anibal jasin Over a year ago

is the variable response a dictionary? because remember that pd.DataFrame(...) is waiting for a dictionary where the 3 dots are.

anibal jasin Over a year ago

In order to get a dictionary from the response object do this: pd.DataFrame(response.json())

|

Collectives™ on Stack Overflow

json_normalize in Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related