How to parse a JSON result of an array of object and print in excel?

Question

This is a multi-part question. I am looking to make a request and print the result in excel, with the headers at the top all across and the content data in the row below. When I run the following code, I only get the headers printed vertically and no data is shown. So my questions are: 1. How do I get the data to come in? and 2. How can I transform the output so the headers are horizontal in the excel sheet (along with the data to come underneath)?

import requests
import json
import pandas
import xlwt

r = requests.get("https://testapp.deribit.com/api/v2/public/get_trade_volumes?")

db=[]

resp = json.loads(r.text)
for data in resp['result'][0]:
    db.append(data)
for data in resp['result'][1]:
    db.append(data)

df1 = pandas.DataFrame(db)
df1.to_excel('C:/Test/daily_volume.xls')

I recommend the following article: ericlippert.com/2014/03/05/how-to-debug-small-programs. Why are you writing to an XLS file? — AMC
– AMC, Commented Dec 5, 2019 at 21:36

Kirk · Accepted Answer · 2019-12-05 18:14:39Z

Your for data in resp doesn't work quite like you think it does.

In [26]: db=[] 
    ...:  
    ...: resp = response 
    ...: for data in resp['result'][0]: 
    ...:     db.append(data) 
    ...: for data in resp['result'][1]: 
    ...:     db.append(data) 
    ...:                                                                                                                                                                                                                                       

In [27]: response                                                                                                                                                                                                                              
Out[27]: 
{'jsonrpc': '2.0',
 'result': [{'puts_volume': 1246.1,
   'futures_volume': 1807294.0335,
   'currency_pair': 'btc_usd',
   'calls_volume': 767.4},
  {'puts_volume': 64.0,
   'futures_volume': 130999.2335,
   'currency_pair': 'eth_usd',
   'calls_volume': 79.0}],
 'usIn': 1575569314303219,
 'usOut': 1575569314304869,
 'usDiff': 1650,
 'testnet': True}

In [28]: db                                                                                                                                                                                                                                    
Out[28]: 
['puts_volume',
 'futures_volume',
 'currency_pair',
 'calls_volume',
 'puts_volume',
 'futures_volume',
 'currency_pair',
 'calls_volume']

You don't have to loop through it that way anyway. With the given JSON structure, you can pass that directly into pandas and get what you're after.

import requests
import pandas as pd

response = requests.get("https://testapp.deribit.com/api/v2/public/get_trade_volumes?").json()
pd.DataFrame(response["result"]).to_excel("daily_volume.xls")

Produces

Collectives™ on Stack Overflow

How to parse a JSON result of an array of object and print in excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related