I'm struggling to convert a JSON API response into a pandas Dataframe object. I've read answers to similar questions/documentation but nothing has helped. My closest attempt is below:
r = requests.get('https://api.xxx')
data = r.text
df = pd.read_json(data, orient='records')
Which returns the following format:
0 {'type': 'bid', 'price': 6.193e-05, ...},
1 {'type': 'bid', 'price': 6.194e-05, ...},
3 {'type': 'bid', 'price': 6.149e-05, ...} etc
The original format of the data is:
{'abc': [{'type': 'bid',
'price': 6.194e-05,
'amount': 2321.37952545,
'tid': 8577050,
'timestamp': 1498649162},
{'type': 'bid',
'price': 6.194e-05,
'amount': 498.78993587,
'tid': 8577047,
'timestamp': 1498649151},
...]}
I'm happy to be directed to good documentation.
I think you need json_normalize
:
from pandas import json_normalize
import requests
r = requests.get('https://api.xxx')
data = r.text
df = json_normalize(data, 'abc')
print (df)
amount price tid timestamp type
0 2321.379525 0.000062 8577050 1498649162 bid
1 498.789936 0.000062 8577047 1498649151 bid
For multiple keys is possible use concat
with list comprehension
and DataFrame
constructor:
d = {'abc': [{'type': 'bid', 'price': 6.194e-05, 'amount': 2321.37952545, 'tid': 8577050, 'timestamp': 1498649162}, {'type': 'bid', 'price': 6.194e-05, 'amount': 498.78993587, 'tid': 8577047, 'timestamp': 1498649151}],
'def': [{'type': 'bid', 'price': 6.194e-05, 'amount': 2321.37952545, 'tid': 8577050, 'timestamp': 1498649162}, {'type': 'bid', 'price': 6.194e-05, 'amount': 498.78993587, 'tid': 8577047, 'timestamp': 1498649151}]}
df = pd.concat([pd.DataFrame(v) for k,v in d.items()], keys=d)
print (df)
amount price tid timestamp type
abc 0 2321.379525 0.000062 8577050 1498649162 bid
1 498.789936 0.000062 8577047 1498649151 bid
def 0 2321.379525 0.000062 8577050 1498649162 bid
1 498.789936 0.000062 8577047 1498649151 bid