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.

a7dc · Accepted Answer · 2023-01-11 13:36:40Z

29

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

edited Jan 11, 2023 at 13:36

a7dc

3,4368 gold badges38 silver badges58 bronze badges

answered Jun 28, 2017 at 12:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Njord Soevik Over a year ago

What is d in: df = json_normalize(d, 'abc')

Laveena Over a year ago

d is API response to, which is r in the question posted.

a7dc Over a year ago

I updated this to make it clearer. Thank you for the answer, jezrael, it's been helpful to me

RodrigoBH · Accepted Answer · 2023-08-08 00:19:47Z

2

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)

edited Aug 8, 2023 at 0:19

user4136999

answered Aug 7, 2023 at 22:20

RodrigoBH

211 bronze badge

Comments

Karol Kołodziejczyk · Accepted Answer · 2024-09-24 17:41:01Z

0

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.

edited Sep 24, 2024 at 17:41

answered Sep 24, 2024 at 15:58

Karol Kołodziejczyk

614 bronze badges

Collectives™ on Stack Overflow

Convert JSON API response to pandas Dataframe

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related