Extracting values from JSON API using Python

Question

I use an api to obtain information on a particular share.

{
  "Meta Data": {
    "1. Information": "Daily Prices (open, high, low, close) and Volumes",
    "2. Symbol": "MSFT",
    "3. Last Refreshed": "2020-05-22",
    "4. Output Size": "Compact",
    "5. Time Zone": "US/Eastern"
  },
  "Time Series (Daily)": {
    "2020-05-22": {
      "1. open": "183.1900",
      "2. high": "184.4600",
      "3. low": "182.5400",
      "4. close": "183.5100",
      "5. volume": "20826898"
    },
    "2020-05-21": {
      "1. open": "185.4000",
      "2. high": "186.6700",
      "3. low": "183.2900",
      "4. close": "183.4300",
      "5. volume": "29032741"
    }, and more...

I would now like to extract only the date, open,high to convert it into a CSV.

import requests
import json

url = "https://alpha-vantage.p.rapidapi.com/query"

querystring = {"outputsize":"compact","datatype":"JSON","function":"TIME_SERIES_DAILY","symbol":"MSFT"}

headers = {
    'x-rapidapi-host': "alpha-vantage.p.rapidapi.com",
    'x-rapidapi-key': "API KEY"
    }

response = requests.request("GET", url, headers=headers, params=querystring)

info = response.json()

with open('data.json', 'w') as fp:
    json.dump(info, fp)

f = open('data.json',)

data = json.load(f)

meta = data["Meta Data"]

for i in data['Meta Data']:
    print(i)

# Closing file
f.close()

output:

1. Information
2. Symbol
3. Last Refreshed
4. Output Size
5. Time Zone

I thought the information was in "Meta Data" but apparently not. Can someone explain to me exactly what I'm doing wrong?

Tim · Accepted Answer · 2020-05-25 13:51:09Z

1

Your dict contains several dicts.

meta = data["Meta Data"]

# Contains dict

 "1. Information": "Daily Prices (open, high, low, close) and Volumes",
 "2. Symbol": "MSFT",
 "3. Last Refreshed": "2020-05-22",
 "4. Output Size": "Compact",
 "5. Time Zone": "US/Eastern"

timeSeries = data['Time Series (Daily)']['2020-05-22']

# Contains dict


  "1. open": "183.1900",
  "2. high": "184.4600",
  "3. low": "182.5400",
  "4. close": "183.5100",
  "5. volume": "20826898"

You can simply access values like this :

eventOpen = data['Time Series (Daily)']['2020-05-22']['1. open']

Try to play around with these objects and print their type and content so you can understand what they contain and how to access it.

edited May 25, 2020 at 13:51

answered May 25, 2020 at 13:19

Tim

4244 silver badges17 bronze badges

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

2 Comments

CLi Over a year ago

Thanks for the answer. But if I loop through "Time Series (Daily)", I only get the dates

Tim Over a year ago

Oh yeap, my bad, got mixed up with your brackets I'm updating the answer.

CLi · Accepted Answer · 2020-05-25 17:19:20Z

The outcome of Time Series (Daily) was a big mess. A conversion into a proper CSV format was not possible.

Using pandas I formatted the data and was able to transfer the data into a proper CSV format.


meta = json.load(f)
data = meta['Meta Data']
data = meta['Time Series (Daily)']

df=pd.DataFrame(columns=['date','open','high','low','close','volume'])
for d,p in data.items():
    date=datetime.datetime.strptime(d,'%Y-%m-%d').date()
    data_row=[date,float(p['1. open']),float(p['2. high']),float(p['3. low']),float(p['4. close']),int(p['5. volume'])]
    df.loc[-1,:]=data_row
    df.index=df.index+1
df =df.sort_values('date')
df.to_csv('test.csv')

          date     open     high      low    close    volume
0   2019-12-27  3776.82  3794.93  3775.26  3782.27  24437900
1   2019-12-30  3780.44  3780.44  3748.47  3748.47  18684000
2   2020-01-03  3787.57  3787.57  3745.54  3773.37  30343400
3   2020-01-06  3764.34  3764.34  3710.94  3752.52  28339400
4   2020-01-07  3760.09  3784.42  3748.13  3759.25  29853300
.... to continue ()...

Collectives™ on Stack Overflow

Extracting values from JSON API using Python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related