python - Convert JSON API response to pandas Dataframe

Question

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.

Answer 1

I think you need json_normalize:

from pandas import json_normalize 

df = json_normalize(d, '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