1

I am new to Python, Can i please seek some help from experts here?

I wish to construct a dataframe from https://api.cryptowat.ch/markets/summaries JSON response. based on following filter criteria

  1. Kraken listed currency pairs (Please take note, there are kraken-futures i dont want those)
  2. Currency paired with USD only, i.e aaveusd, adausd....

Ideal Dataframe i am looking for is (somehow excel loads this json perfectly screenshot below) Dataframe_Excel_Screenshot

resp = requests.get(https://api.cryptowat.ch/markets/summaries) kraken_assets = resp.json() df = pd.json_normalize(kraken_assets) print(df)

Output:

result.binance-us:aaveusd.price.last result.binance-us:aaveusd.price.high ...
0 264.48 267.32 ...

[1 rows x 62688 columns]

When i just paste the link in browser JSON response is with double quotes ("), but when i get it via python code. All double quotes (") are changed to single quotes (') any idea why?. Though I tried to solve it with json_normalize but then response is changed to [1 rows x 62688 columns]. i am not sure how do i even go about working with 1 row with 62k columns. i dont know how to extract exact info in the dataframe format i need (please see excel screenshot).

Any help is much appreciated. thank you!

Improve this question

2 Answers 2

Reset to default
1
  • the result JSON is a dict
  • load this into a dataframe
  • decode columns into products & measures
  • filter to required data
import requests
import pandas as pd
import numpy as np

# load results into a data frame
df = pd.json_normalize(requests.get("https://api.cryptowat.ch/markets/summaries").json()["result"])

# columns are encoded as product and measure.  decode columns and transpose into rows that include product and measure
cols = np.array([c.split(".", 1) for c in df.columns]).T
df.columns = pd.MultiIndex.from_arrays(cols, names=["product","measure"])
df = df.T

# finally filter down to required data and structure measures as columns
df.loc[df.index.get_level_values("product").str[:7]=="kraken:"].unstack("measure").droplevel(0,1)

sample output

product price.last price.high price.low price.change.percentage price.change.absolute volume volumeQuote
kraken:aaveaud 347.41 347.41 338.14 0.0274147 9.27 1.77707 613.281
kraken:aavebtc 0.008154 0.008289 0.007874 0.0219326 0.000175 403.506 3.2797
kraken:aaveeth 0.1327 0.1346 0.1327 -0.00673653 -0.0009 287.113 38.3549
kraken:aaveeur 219.87 226.46 209.07 0.0331751 7.06 1202.65 259205
kraken:aavegbp 191.55 191.55 179.43 0.030559 5.68 6.74476 1238.35
kraken:aaveusd 259.53 267.48 246.64 0.0339841 8.53 3623.66 929624
kraken:adaaud 1.61792 1.64602 1.563 0.0211692 0.03354 5183.61 8366.21
kraken:adabtc 3.757e-05 3.776e-05 3.673e-05 0.0110334 4.1e-07 252403 9.41614
kraken:adaeth 0.0006108 0.00063 0.0006069 -0.0175326 -1.09e-05 590839 367.706
kraken:adaeur 1.01188 1.03087 0.977345 0.0209986 0.020811 1.99104e+06 1.98693e+06
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Hello Try the below code. I have understood the structure of the Dataset and modified to get the desired output. `

resp = requests.get("https://api.cryptowat.ch/markets/summaries")
a=resp.json()
a['result']
#creating Dataframe froom key=result
da=pd.DataFrame(a['result'])
#using Transpose to get required Columns and Index
da=da.transpose()
#price columns contains a dict which need to be seperate Columns on the data frame
db=da['price'].to_dict()
da.drop('price', axis=1, inplace=True)
#intialising seperate Data frame for price
z=pd.DataFrame({})
for i in db.keys():
    i=pd.DataFrame(db[i], index=[i])
    z=pd.concat([z,i], axis=0 )
da=pd.concat([z, da], axis=1)
da.to_excel('nex.xlsx')`
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.