Search code examples
pythonjsonpandasnormalize

Python json normalize API request

I receive data in JSON format and have a hard time converting them into a suitable format. Hope you can help me. 

import pandas as pd

from pandas.io.json import json_normalize

import requests

dataAPI = requests.get('here is the API URL')

print(dataAPI.json())

give me the following output:

{'c': [277.775, 277.76, 277.65, 277.64, 277.5215], 'h': [277.89, 278.06, 277.98, 277.
76, 277.98], 'l': [277.67, 277.71, 277.59, 277.42, 277.472], 'o': [277.69, 277.795, 277.77, 277.66, 277.72], 's': 'ok', 't': [1587412320, 1587412380, 1587412440, 1587412500, 1587412560, 1587412620, ], 'v': [0, 142752, 133100, 259539, 0]}

I'd like to create a dataframe with the following columns (skip column s) and float cell values:

c| h| l| o| t| v

277.775| 277.89| 277.67| 277.69| 1587412320| 0

...

I tried something along these lines json_normalize(dataAPI, 'c')

but that gave me an error message TypeError: byte indices must be integers or slices, not str

Appreciate your help a lot

Solution

  • you have to define your wanted columns and than just use pandas.concat:

    j = {'c': [277.775, 277.76, 277.65, 277.64, 277.5215], 'h': [277.89, 278.06, 277.98, 277.76, 277.98], 'l': [277.67, 277.71, 277.59, 277.42, 277.472], 'o': [277.69, 277.795, 277.77, 277.66, 277.72], 's': 'ok', 't': [1587412320, 1587412380, 1587412440, 1587412500, 1587412560, 1587412620, ], 'v': [0, 142752, 133100, 259539, 0]}
columns = {'c', 'h', 'l',  'o', 't', 'v'}
pd.concat([pd.DataFrame({k: v}) for k, v in j.items() if k in columns], axis=1)

    output:

    enter image description here