CODEWITHSUNDEEP
pythonjsonpandasgoogle-colaboratoryalpha-vantage
dsx
dsx

Reputation: 301

What kind of JSON structure is the Alpha Vantage API data, and how to convert it to a pandas dataframe?

I have an Alpha Vantage API demo working in a Google Colab notebook, and am trying to convert that data response to a pandas dataframe.

However, I'm not clear on what the JSON data structure is, which is making it difficult to find the right python code.

This is the demo code that's working:

import requests
import pandas as pd
import json

# replace the "demo" apikey below with your own key from https://www.alphavantage.co/support/#api-key
url = 'https://www.alphavantage.co/query?function=CRYPTO_INTRADAY&symbol=ETH&market=USD&interval=15min&apikey=demo'
r = requests.get(url)
data = r.json()

print(data)

And this is a sample of the data it's returning:

{'Meta Data': {'1. Information': 'Crypto Intraday (15min) Time Series', '2. Digital Currency Code': 'ETH', '3. Digital Currency Name': 'Ethereum', '4. Market Code': 'USD', '5. Market Name': 'United States Dollar', '6. Last Refreshed': '2022-02-10 04:00:00', '7. Interval': '15min', '8. Output Size': 'Compact', '9. Time Zone': 'UTC'}, 'Time Series Crypto (15min)': {'2022-02-10 04:00:00': {'1. open': '3179.59000', '2. high': '3180.47000', '3. low': '3173.82000', '4. close': '3175.86000', '5. volume': 1031}, '2022-02-10 03:45:00': {'1. open': '3180.39000', '2. high': '3183.52000', '3. low': '3176.24000', '4. close': '3179.60000', '5. volume': 1924}, '2022-02-10 03:30:00': {'1. open': '3185.17000', '2. high': '3188.40000', '3. low': '3179.04000', '4. close': '3180.40000', '5. volume': 2018}, '2022-02-10 03:15:00': {'1. open': '3177.58000', '2. high': '3186.24000', '3. low': '3175.01000', '4. close': '3185.17000', '5. volume': 2376}, '2022-02-10 03:00:00': {'1. open': '3186.58000', '2. high': '3189.99000', '3. low': '3170.87000', '4. close': '3177.58000', '5. volume': 6907}, '2022-02-10 02:45:00': {'1. open': '3221.54000', '2. high': '3224.80000', '3. low': '3184.01000', '4. close': '3186.58000', '5. volume': 8794}, '2022-02-10 02:30:00': {'1. open': '3228.30000', '2. high': '3229.00000', '3. low': '3220.00000', '4. close': '3221.53000', '5. volume': 1552}, '2022-02-10 02:15:00': {'1. open': '3225.79000', '2. high': '3228.58000', '3. low': '3220.86000', '4. close': '3228.29000', '5. volume': 1852}, '2022-02-10 02:00:00': {'1. open': '3224.28000', '2. high': '3229.26000', '3. low': '3215.67000', '4. close': '3225.80000', '5. volume': 2698}, '2022-02-10 01:45:00': {'1. open': '3225.74000', '2. high': '3228.92000', '3. low': '3219.29000', '4. close': '3224.27000', '5. volume': 1529}, '2022-02-10 01:30:00': {'1. open': '3214.24000', '2. high': '3227.95000', '3. low': '3207.18000', '4. close': '3225.73000', '5. volume': 2239}, '2022-02-10 01:15:00': {'1. open': '3225.78000', '2. high': '3227.94000', '3. low': '3213.00000', '4. close': '3214.23000', '5. volume': 1930}, '2022-02-10 01:00:00': {'1. open': '3220.08000', '2. high': '3225.99000', '3. low': '3217.40000', '4. close': '3225.78000', '5. volume': 2333}, '2022-02-10 00:45:00': {'1. open': '3221.87000', '2. high': '3224.36000', '3. low': '3213.58000', '4. close': '3220.09000', '5. volume': 1912}, '2022-02-10 00:30:00': {'1. open': '3224.74000', '2. high': '3229.21000', '3. low': '3220.03000', '4. close': '3221.87000', '5. volume': 2299}, '2022-02-10 00:15:00': {'1. open': '3235.70000', '2. high': '3235.82000', '3. low': '3211.27000', '4. close': '3224.74000', '5. volume': 5900}, '2022-02-10 00:00:00': {'1. open': '3243.60000', '2. high': '3248.98000', '3. low': '3233.08000', '4. close': '3235.71000', '5. volume': 3388}, '2022-02-09 23:45:00': {'1. open': '3234.46000', '2. high': '3244.75000', '3. low': '3232.95000', '4. close': '3243.94000', '5. volume': 3475}, '2022-02-09 23:30:00': {'1. open': '3236.47000', '2. high': '3240.00000', '3. low': '3231.41000', '4. close': '3234.82000', '5. volume': 2475}, '2022-02-09 23:15:00': {'1. open': '3242.38000', '2. high': '3245.76000', '3. low': '3232.13000', '4. close': '3236.62000', '5. volume': 2145}, '2022-02-09 23:00:00': {'1. open': '3251.01000', '2. high': '3251.03000', '3. low': '3239.50000', '4. close': '3242.37000', '5. volume': 2438}, '2022-02-09 22:45:00': {'1. open': '3242.50000', '2. high': '3254.57000', '3. low': '3241.18000', '4. close': '3251.02000', '5. volume': 1700}, '2022-02-09 22:30:00': {'1. open': '3233.52000', '2. high': '3245.39000', '3. low': '3232.34000', '4. close': '3242.50000', '5. volume': 1597}, '2022-02-09 22:15:00': {'1. open': '3237.01000', '2. high': '3245.59000', '3. low': '3230.83000', '4. close': '3233.53000', '5. volume': 2708}, '2022-02-09 22:00:00': {'1. open': '3236.09000', '2. high': '3247.00000', '3. low': '3234.70000', '4. close': '3236.85000', '5. volume': 3090}, '2022-02-09 21:45:00': {'1. open': '3249.00000', '2. high': '3249.99000', '3. low': '3234.64000', '4. close': '3236.08000', '5. volume': 3263}, '2022-02-09 21:30:00': {'1. open': '3253.56000', '2. high': '3256.00000', '3. low': '3247.01000', '4. close': '3249.01000', '5. volume': 2060}, '2022-02-09 21:15:00': {'1. open': '3254.33000', '2. high': '3256.80000', '3. low': '3245.05000', '4. close': '3253.56000', '5. volume': 1957}, '2022-02-09 21:00:00': {'1. open': '3258.74000', '2. high': '3261.22000', '3. low': '3248.37000', '4. close': '3254.34000', '5. volume': 4320}, '2022-02-09 20:45:00': {'1. open': '3259.41000', '2. high': '3263.21000', '3. low': '3250.30000', '4. close': '3258.74000', '5. volume': 4215}, '2022-02-09 20:30:00': {'1. open': '3260.67000', '2. high': '3269.70000', '3. low': '3253.49000', '4. close': '3259.42000', '5. volume': 5505}, '2022-02-09 20:15:00': {'1. open': '3244.45000', '2. high': '3264.97000', '3. low': '3240.60000', '4. close': '3260.67000', '5. volume': 4368}, '2022-02-09 20:00:00': {'1. open': '3246.94000', '2. high': '3260.75000', '3. low': '3244.41000', '4. close': '3244.41000', '5. volume': 4653}, '2022-02-09 19:45:00': {'1. open': '3250.72000', '2. high': '3256.63000', '3. low': '3243.77000', '4. close': '3246.94000', '5. volume': 3484}, '2022-02-09 19:30:00': {'1. open': '3247.01000', '2. high': '3258.00000', '3. low': '3237.17000', '4. close': '3250.72000', '5. volume': 5654}, '2022-02-09 19:15:00': {'1. open': '3226.37000', '2. high': '3254.00000', '3. low': '3220.07000', '4. close': '3247.01000', '5. volume': 10292}, '2022-02-09 19:00:00': {'1. open': '3210.13000', '2. high': '3238.50000', '3. low': '3207.01000', '4. close': '3226.38000', '5. volume': 10794}, '2022-02-09 18:45:00': {'1. open': '3204.34000', '2. high': '3210.99000', '3. low': '3197.89000', '4. close': '3210.13000', '5. volume': 2054}, '2022-02-09 18:30:00': {'1. open': '3193.03000', '2. high': '3205.00000', '3. low': '3187.13000', '4. close': '3204.35000', '5. volume': 2349}, '2022-02-09 18:15:00': {'1. open': '3193.37000', '2. high': '3200.56000', '3. low': '3183.02000', '4. close': '3193.00000', '5. volume': 2203}, '2022-02-09 18:00:00': {'1. open': '3199.76000', '2. high': '3204.07000', '3. low': '3191.63000', '4. close': '3193.76000', '5. volume': 1864}, '2022-02-09 17:45:00': {'1. open': '3203.52000', '2. high': '3207.57000', '3. low': '3192.66000', '4. close': '3199.76000', '5. volume': 2315}, '2022-02-09 17:30:00': {'1. open': '3206.02000', '2. high': '3211.36000', '3. low': '3196.97000', '4. close': '3203.52000', '5. volume': 3534}, '2022-02-09 17:15:00': {'1. open': '3205.73000', '2. high': '3217.69000', '3. low': '3205.73000', '4. close': '3206.01000', '5. volume': 3901}, '2022-02-09 17:00:00': {'1. open': '3202.95000', '2. high': '3208.80000', '3. low': '3198.49000', '4. close': '3205.74000', '5. volume': 3619}, '2022-02-09 16:45:00': {'1. open': '3193.45000', '2. high': '3212.28000', '3. low': '3193.06000', '4. close': '3202.95000', '5. volume': 6311}, '2022-02-09 16:30:00': {'1. open': '3183.83000', '2. high': '3194.75000', '3. low': '3179.19000', '4. close': '3193.46000', '5. volume': 3741}, '2022-02-09 16:15:00': {'1. open': '3172.45000', '2. high': '3189.38000', '3. low': '3169.50000', '4. close': '3183.83000', '5. volume': 2725}, '2022-02-09 16:00:00': {'1. open': '3178.44000', '2. high': '3195.00000', '3. low': '3163.02000', '4. close': '3172.45000', '5. volume': 10011}, '2022-02-09 15:45:00': {'1. open': '3166.00000', '2. high': '3179.80000', '3. low': '3165.78000', '4. close': '3178.45000', '5. volume': 2626}, '2022-02-09 15:30:00': {'1. open': '3177.69000', '2. high': '3184.61000', '3. low': '3152.44000', '4. close': '3166.00000', '5. volume': 7079}, '2022-02-09 15:15:00': {'1. open': '3181.27000', '2. high': '3181.50000', '3. low': '3166.54000', '4. close': '3177.68000', '5. volume': 4888}, '2022-02-09 15:00:00': {'1. open': '3188.49000', '2. high': '3200.91000', '3. low': '3179.47000', '4. close': '3181.27000', '5. volume': 3970}, '2022-02-09 14:45:00': {'1. open': '3202.56000', '2. high': '3209.75000', '3. low': '3188.00000', '4. close': '3188.50000', '5. volume': 5743}, '2022-02-09 14:30:00': {'1. open': '3186.82000', '2. high': '3216.99000', '3. low': '3182.91000', '4. close': '3202.61000', '5. volume': 6893}, '2022-02-09 14:15:00': {'1. open': '3179.02000', '2. high': '3190.91000', '3. low': '3171.48000', '4. close': '3186.83000', '5. volume': 2638}, '2022-02-09 14:00:00': {'1. open': '3180.69000', '2. high': '3186.70000', '3. low': '3161.62000', '4. close': '3179.01000', '5. volume': 5528}, '2022-02-09 13:45:00': {'1. open': '3198.63000', '2. high': '3202.50000', '3. low': '3179.34000', '4. close': '3180.94000', '5. volume': 5184}, '2022-02-09 13:30:00': {'1. open': '3198.74000', '2. high': '3204.45000', '3. low': '3186.76000', '4. close': '3198.64000', '5. volume': 4447}, '2022-02-09 13:15:00': {'1. open': '3200.08000', '2. high': '3206.00000', '3. low': '3191.43000', '4. close': '3198.74000', '5. volume': 7063}, '2022-02-09 13:00:00': {'1. open': '3181.70000', '2. high': '3207.99000', '3. low': '3174.43000', '4. close': '3200.07000', '5. volume': 9626}, '2022-02-09 12:45:00': {'1. open': '3190.59000', '2. high': '3193.53000', '3. low': '3174.70000', '4. close': '3181.49000', '5. volume': 5283}, '2022-02-09 12:30:00': {'1. open': '3154.62000', '2. high': '3192.78000', '3. low': '3151.18000', '4. close': '3190.59000', '5. volume': 11267}, '2022-02-09 12:15:00': {'1. open': '3154.60000', '2. high': '3159.75000', '3. low': '3150.14000', '4. close': '3154.62000', '5. volume': 3160}, '2022-02-09 12:00:00': {'1. open': '3152.89000', '2. high': '3158.83000', '3. low': '3140.00000', '4. close': '3154.61000', '5. volume': 5264}, '2022-02-09 11:45:00': {'1. open': '3139.54000', '2. high': '3158.37000', '3. low': '3139.37000', '4. close': '3152.89000', '5. volume': 5061}, '2022-02-09 11:30:00': {'1. open': '3138.69000', '2. high': '3146.00000', '3. low': '3134.57000', '4. close': '3139.53000', '5. volume': 4378}, '2022-02-09 11:15:00': {'1. open': '3126.04000', '2. high': '3139.87000', '3. low': '3124.39000', '4. close': '3138.70000', '5. volume': 3621}, '2022-02-09 11:00:00': {'1. open': '3136.30000', '2. high': '3142.33000', '3. low': '3119.04000', '4. close': '3126.04000', '5. volume': 3827}, '2022-02-09 10:45:00': {'1. open': '3131.03000', '2. high': '3137.00000', '3. low': '3123.14000', '4. close': '3136.30000', '5. volume': 3638}, '2022-02-09 10:30:00': {'1. open': '3135.25000', '2. high': '3142.97000', '3. low': '3127.20000', '4. close': '3131.02000', '5. volume': 3903}, '2022-02-09 10:15:00': {'1. open': '3123.50000', '2. high': '3143.83000', '3. low': '3118.56000', '4. close': '3135.24000', '5. volume': 6848}, '2022-02-09 10:00:00': {'1. open': '3099.46000', '2. high': '3128.43000', '3. low': '3099.11000', '4. close': '3123.50000', '5. volume': 5342}, '2022-02-09 09:45:00': {'1. open': '3105.92000', '2. high': '3111.37000', '3. low': '3096.00000', '4. close': '3099.21000', '5. volume': 2038}, '2022-02-09 09:30:00': {'1. open': '3109.85000', '2. high': '3115.96000', '3. low': '3091.92000', '4. close': '3105.93000', '5. volume': 3369}, '2022-02-09 09:15:00': {'1. open': '3110.20000', '2. high': '3116.00000', '3. low': '3105.85000', '4. close': '3109.83000', '5. volume': 2234}, '2022-02-09 09:00:00': {'1. open': '3094.09000', '2. high': '3115.00000', '3. low': '3094.09000', '4. close': '3110.21000', '5. volume': 5781}, '2022-02-09 08:45:00': {'1. open': '3081.81000', '2. high': '3099.51000', '3. low': '3081.43000', '4. close': '3094.10000', '5. volume': 2801}, '2022-02-09 08:30:00': {'1. open': '3079.49000', '2. high': '3084.93000', '3. low': '3069.00000', '4. close': '3081.81000', '5. volume': 2372}, '2022-02-09 08:15:00': {'1. open': '3097.48000', '2. high': '3103.96000', '3. low': '3069.61000', '4. close': '3079.48000', '5. volume': 5602}, '2022-02-09 08:00:00': {'1. open': '3105.93000', '2. high': '3113.69000', '3. low': '3094.51000', '4. close': '3097.49000', '5. volume': 2361}, '2022-02-09 07:45:00': {'1. open': '3088.19000', '2. high': '3112.61000', '3. low': '3087.68000', '4. close': '3105.93000', '5. volume': 3146}, '2022-02-09 07:30:00': {'1. open': '3087.07000', '2. high': '3091.80000', '3. low': '3079.69000', '4. close': '3088.16000', '5. volume': 2029}, '2022-02-09 07:15:00': {'1. open': '3101.27000', '2. high': '3101.34000', '3. low': '3085.87000', '4. close': '3087.07000', '5. volume': 2136}, '2022-02-09 07:00:00': {'1. open': '3099.93000', '2. high': '3106.57000', '3. low': '3092.94000', '4. close': '3101.26000', '5. volume': 2478}, '2022-02-09 06:45:00': {'1. open': '3101.43000', '2. high': '3107.29000', '3. low': '3098.00000', '4. close': '3099.93000', '5. volume': 2642}, '2022-02-09 06:30:00': {'1. open': '3091.52000', '2. high': '3105.41000', '3. low': '3089.74000', '4. close': '3101.49000', '5. volume': 2442}, '2022-02-09 06:15:00': {'1. open': '3087.91000', '2. high': '3099.99000', '3. low': '3085.49000', '4. close': '3091.52000', '5. volume': 1486}, '2022-02-09 06:00:00': {'1. open': '3089.75000', '2. high': '3096.00000', '3. low': '3084.43000', '4. close': '3087.90000', '5. volume': 2402}, '2022-02-09 05:45:00': {'1. open': '3100.22000', '2. high': '3106.40000', '3. low': '3087.84000', '4. close': '3089.75000', '5. volume': 3642}, '2022-02-09 05:30:00': {'1. open': '3072.85000', '2. high': '3101.64000', '3. low': '3071.27000', '4. close': '3100.23000', '5. volume': 3586}, '2022-02-09 05:15:00': {'1. open': '3066.72000', '2. high': '3074.87000', '3. low': '3055.00000', '4. close': '3072.85000', '5. volume': 2808}, '2022-02-09 05:00:00': {'1. open': '3080.40000', '2. high': '3084.02000', '3. low': '3057.68000', '4. close': '3066.72000', '5. volume': 4779}, '2022-02-09 04:45:00': {'1. open': '3085.53000', '2. high': '3094.37000', '3. low': '3079.70000', '4. close': '3080.41000', '5. volume': 2142}, '2022-02-09 04:30:00': {'1. open': '3083.75000', '2. high': '3088.77000', '3. low': '3078.20000', '4. close': '3085.53000', '5. volume': 1813}, '2022-02-09 04:15:00': {'1. open': '3089.00000', '2. high': '3091.52000', '3. low': '3081.56000', '4. close': '3083.67000', '5. volume': 1639}, '2022-02-09 04:00:00': {'1. open': '3076.52000', '2. high': '3090.13000', '3. low': '3075.04000', '4. close': '3089.00000', '5. volume': 2153}, '2022-02-09 03:45:00': {'1. open': '3074.50000', '2. high': '3078.82000', '3. low': '3072.34000', '4. close': '3076.52000', '5. volume': 1178}, '2022-02-09 03:30:00': {'1. open': '3079.31000', '2. high': '3084.81000', '3. low': '3073.05000', '4. close': '3074.49000', '5. volume': 1746}, '2022-02-09 03:15:00': {'1. open': '3073.70000', '2. high': '3081.01000', '3. low': '3073.70000', '4. close': '3079.32000', '5. volume': 2243}}}

I ran this code to try and put that response into a dataframe:

df = pd.DataFrame(data["Time Series (15min)"])
df = df.T  # Transpose Dataframe for desired results

My understanding is the data response is a JSON dictionary data structure, but that produced this error message:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-9-ba79690310fb> in <module>()
----> 1 df = pd.DataFrame(data["Time Series (15min)"])
      2 df = df.T  # Transpose Dataframe for desired results

KeyError: 'Time Series (15min)'

I don't understand the error message, but am thinking it must be something from pandas and not the AlphaVantage API, though I haven't been able to find any clear information on what I should be using instead of "Time Series (15min)"

Greatly appreciate any input on how to approach this.

Upvotes: 0

Views: 357

Answers (1)

Corralien
Corralien

Reputation: 120519

You don't use the right key: it's not Time Series (15min) but Time Series Crypto (15min):

df = pd.DataFrame(data["Time Series Crypto (15min)"]).T
print(df)

# Output
                        1. open     2. high      3. low    4. close 5. volume
2022-02-10 04:00:00  3179.59000  3180.47000  3173.82000  3175.86000      1031
2022-02-10 03:45:00  3180.39000  3183.52000  3176.24000  3179.60000      1924
2022-02-10 03:30:00  3185.17000  3188.40000  3179.04000  3180.40000      2018
2022-02-10 03:15:00  3177.58000  3186.24000  3175.01000  3185.17000      2376
2022-02-10 03:00:00  3186.58000  3189.99000  3170.87000  3177.58000      6907
...                         ...         ...         ...         ...       ...
2022-02-09 04:15:00  3089.00000  3091.52000  3081.56000  3083.67000      1639
2022-02-09 04:00:00  3076.52000  3090.13000  3075.04000  3089.00000      2153
2022-02-09 03:45:00  3074.50000  3078.82000  3072.34000  3076.52000      1178
2022-02-09 03:30:00  3079.31000  3084.81000  3073.05000  3074.49000      1746
2022-02-09 03:15:00  3073.70000  3081.01000  3073.70000  3079.32000      2243

[100 rows x 5 columns]

Bonus rename columns:

df.columns = df.columns.str.split().str[1].str.capitalize()
print(df)

# Output
                           Open        High         Low       Close Volume
2022-02-10 04:00:00  3179.59000  3180.47000  3173.82000  3175.86000   1031
2022-02-10 03:45:00  3180.39000  3183.52000  3176.24000  3179.60000   1924
2022-02-10 03:30:00  3185.17000  3188.40000  3179.04000  3180.40000   2018
2022-02-10 03:15:00  3177.58000  3186.24000  3175.01000  3185.17000   2376
2022-02-10 03:00:00  3186.58000  3189.99000  3170.87000  3177.58000   6907
...                         ...         ...         ...         ...    ...
2022-02-09 04:15:00  3089.00000  3091.52000  3081.56000  3083.67000   1639
2022-02-09 04:00:00  3076.52000  3090.13000  3075.04000  3089.00000   2153
2022-02-09 03:45:00  3074.50000  3078.82000  3072.34000  3076.52000   1178
2022-02-09 03:30:00  3079.31000  3084.81000  3073.05000  3074.49000   1746
2022-02-09 03:15:00  3073.70000  3081.01000  3073.70000  3079.32000   2243

[100 rows x 5 columns]

Upvotes: 1

Related Questions