CODEWITHSUNDEEP
pythonjsonpandas
Zach Cleary
Zach Cleary

Reputation: 552

Convert JSON API response to pandas Dataframe

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.

Upvotes: 17

Views: 67955

Answers (3)

Karol Kołodziejczyk
Karol Kołodziejczyk

Reputation: 61

I think most straightforward way is to use json module to convert str to dict and use DataFrame constructor function.

import requests as req
import pandas as pd
import json

res = req.get('https://jsonplaceholder.org/users')
data = res.text

df = pd.DataFrame(json.loads(data))
print(df)

Edited below Just to clarify, there are API's that can be connected to by pandas like below.

import pandas as pd

df = pd.read_json('https://jsonplaceholder.typicode.com/users')
print(df)

Thing is not every API allow that.

Upvotes: 0

RodrigoBH
RodrigoBH

Reputation: 21

You could try with pandas and StringIO from io; As follow:

import pandas as pd
from io import StringIO


response = requests.get('https://api.xxx')

result = pd.read_csv(StringIO(response.text))
print(result)

Upvotes: 2

jezrael
jezrael

Reputation: 863361

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

Upvotes: 29

Related Questions