[SOLVED] Using REST API JSON response as data to build reports in Python

Using REST API JSON response as data to build reports in Python

I am having really hard time converting a json response from the QuickBase API into Pandas dataframe so I can work and chart the data with Pandas and Chartly.

import requests
import json
token = "b5u67z_pgnr_dkqwwkbbmmhnzwcjd4cexbi893ux"
realm_hostname = "builderprogram-ypetrov.quickbase.com"
headers = {
    'QB-Realm-Hostname': realm_hostname,
    'User-Agent': 'Python_Reporting',
    'Authorization': 'QB-USER-TOKEN ' + token
}
body = {
    "from": "bq4zh7zip",
    "select": [
    7,
    11,
    16
    ]
}
r = requests.post(
'https://api.quickbase.com/v1/records/query',
headers = headers,
json = body
)
json_export = (json.dumps(r.json(),indent=4))
import pandas as pd
#everything works fine until here, I can print json_export and the data is there but can't continue from here onwards
pd.read_json(json_export)

The API request details are valid in a test environment so feel free to use it if you need.
Any help will be greatly appreciated!
Regards!

Solution

Assuming you want to convert the information inside data to a pandas dataframe, then instead of pd.read_json you can use pd.json_normalize

...
# your code
...

import pandas as pd
df = pd.json_normalize(json.loads(json_export)['data'])
print(df.head())