Pandas.read_json(JSON_URL)

Question

I am using Pandas to get data from an API. The API returns data in JSON format. However the json has some values that I don't want in the dataframe. Because of these values, I am not able to assign an index to data frame. Following is the format.

{
"Success": true,
"message": "",
"result": [{"id":12312312, "TimeStamp":"2017-10-04T17:39:53.92","Quantity":3.03046306,},{"id": 2342344, "TimeStamp":"2017-10-04T17:39:53.92","Quantity":3.03046306,}]}

I am only interested in the "result" part. One way to do this is to import json with request.get(request_URL) and then after extracting the "result" part, convert the result into the dataframe. 2nd way can be to import the data with Pandas.read_json(JSON_URL) convert the returning dataframe back to a json, then after extracting "result" part, convert the result into the dataframe.

Is there any other way to do this? What is the best approach and why? Thanks.

Ryan Skene · Accepted Answer · 2020-05-18 05:28:09Z

18

Use json_normalize:

import pandas as pd

df = pd.json_normalize(json['result'])
print (df)

   Quantity               TimeStamp        id
0  3.030463  2017-10-04T17:39:53.92  12312312
1  3.030463  2017-10-04T17:39:53.92   2342344

Also here working:

df = pd.DataFrame(d['result'])
print (df)
   Quantity               TimeStamp        id
0  3.030463  2017-10-04T17:39:53.92  12312312
1  3.030463  2017-10-04T17:39:53.92   2342344

For DatetimeIndex convert column to_datetime and set_index:

df['TimeStamp'] = pd.to_datetime(df['TimeStamp'])
df = df.set_index('TimeStamp')
print (df)

                         Quantity        id
TimeStamp                                  
2017-10-04 17:39:53.920  3.030463  12312312
2017-10-04 17:39:53.920  3.030463   2342344

EDIT:

Solution with load data:

from urllib.request import urlopen
import json
import pandas as pd

response = urlopen("https://bittrex.com/api/v1.1/public/getmarkethistory?market=BTC-ETC")
json_data = response.read().decode('utf-8', 'replace')

d = json.loads(json_data)
df = pd.json_normalize(d['result'])
df['TimeStamp'] = pd.to_datetime(df['TimeStamp'])
df = df.set_index('TimeStamp')

print (df.head())
                          Quantity     Total  
TimeStamp                                     
2017-10-05 06:05:06.510   3.579201  0.010000  
2017-10-05 06:04:34.060  45.614760  0.127444  
2017-10-05 06:04:34.060   5.649898  0.015785  
2017-10-05 06:04:34.060   1.769847  0.004945  
2017-10-05 06:02:25.063   0.250000  0.000698

Another solution:

df = pd.read_json('https://bittrex.com/api/v1.1/public/getmarkethistory?market=BTC-ETC')
df = pd.DataFrame(df['result'].values.tolist())
df['TimeStamp'] = pd.to_datetime(df['TimeStamp'])
df = df.set_index('TimeStamp')
print (df.head())

                          Quantity     Total  
TimeStamp                                     
2017-10-05 06:11:25.100   5.620957  0.015704  
2017-10-05 06:11:11.427  22.853546  0.063851  
2017-10-05 06:10:30.600   6.999213  0.019555  
2017-10-05 06:10:29.163  20.000000  0.055878  
2017-10-05 06:10:29.163   0.806039  0.002252

edited May 18, 2020 at 5:28

Ryan Skene

9341 gold badge13 silver badges33 bronze badges

answered Oct 5, 2017 at 5:00

jezrael

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

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

4 Comments

Sarfraz Over a year ago

Thanks @jezrael, how can I make TimeStamp the index.

Sarfraz Over a year ago

So some thing strange is happening I am using the pd.DataFrame(d['result']) get the data frame, however it only returns a single column buy the name of "result". bittrex.com/api/v1.1/public/getmarkethistory?market=BTC-ETC is where the data is coming from . Appreciate the help.

jezrael Over a year ago

I think first solution is more general, the best use it. df = json_normalize(json['result'])

jezrael Over a year ago

I add 2 possible solutions, please check edited answer.

Anton vBR · Accepted Answer · 2017-10-05 06:18:06Z

5

Another solution, based on jezrael's using requests:

import requests
import pandas as pd

d = requests.get("https://bittrex.com/api/v1.1/public/getmarkethistory?market=BTC-ETC").json()
df = pd.DataFrame.from_dict(d['result'])
df['TimeStamp'] = pd.to_datetime(df['TimeStamp'])
df = df.set_index('TimeStamp')

df

answered Oct 5, 2017 at 6:18

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

1 Comment

Sarfraz Over a year ago

What if the json data from the "get" request is too large, is that handled easily. or is there some other better approach?

Collectives™ on Stack Overflow

Pandas.read_json(JSON_URL)

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related