Search code examples
pythonjsonpandasdataframejson-normalize

Python Pandas Dataframe from API JSON Response >>

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!

Solution
    • 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