0

I'm pulling FRED data through their api and I'm getting complicated JSON back. Buried in this data is liability data. Inside the JSON, is a nested 'observations' key and inside of that key is a list of dictionaries with 'date' and 'value' keys. I need the date and value as the column headers. Date could also be the index.

To get the data, one will need a free api key found here: https://fred.stlouisfed.org/. Just go to my account and follow the instructions.

For the code to pull the data, see the below.

import pandas as pd
import time
import urllib
import requests
from fred_api import fred_api #config file with my personal api
networth_endoint = r'https://api.stlouisfed.org/fred/series/observations?series_id=BOGZ1FL102090005A&api_key={}&file_type=json'.format(api)

content = requests.get(url = networth_endoint)

data = content.json()
data

If you don't want to go through that hassle, below is a snippet of what is returned:

{'realtime_start': '2020-03-03',
 'realtime_end': '2020-03-03',
 'observation_start': '1600-01-01',
 'observation_end': '9999-12-31',
 'units': 'lin',
 'output_type': 1,
 'file_type': 'json',
 'order_by': 'observation_date',
 'sort_order': 'asc',
 'count': 74,
 'offset': 0,
 'limit': 100000,
 'observations': [{'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1945-01-01',
   'value': '215967.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1946-01-01',
   'value': '232012.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1947-01-01',
   'value': '269729.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1948-01-01',
   'value': '294148.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1949-01-01',
   'value': '304352.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '1950-01-01',
   'value': '328550.0'}...

{'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '2017-01-01',
   'value': '15479906.0'},
  {'realtime_start': '2020-03-03',
   'realtime_end': '2020-03-03',
   'date': '2018-01-01',
   'value': '16491618.0'}]}

This goes one from 1945 to the last reported quarter. Actual json broken up by ...

Can anyone figure out how to pull each date and value out of the json and make a dataframe with it?

Jordan
  • 1,415
  • 3
  • 18
  • 44
  • this should help: [how to select a specific element in a JSON](https://stackoverflow.com/questions/31483655/how-to-select-specific-json-element-in-python) – Plato77 Mar 03 '20 at 16:15
  • Thank you but it doesn't get me what i want. – Jordan Mar 03 '20 at 17:28
  • so you've tried `json.loads`? – Plato77 Mar 03 '20 at 17:31
  • yes. When i pull the content from the URL, it pulls it in as a dict. I think I just figured it out: `for i in data['observations']: print(i['date'], i['value'])` – Jordan Mar 03 '20 at 17:35

0 Answers0