Most efficient way of converting RESTful output to dataframe

Question

I have output from a REST call that I've converted to JSON.

It's a highly nested collection of dicts and lists, but I'm eventually able to convert it to dataframe as follows:

import panads as pd
from requests import get 

url = 'http://stats.oecd.org/SDMX-JSON/data/MEI_FIN/IR3TIB.GBR+USA.M/all'
params = { 
        'startTime' : '2008-06',
        'dimensionAtObservation' : 'TimeDimension'
        }

r = get(url, params = params)
x = r.json()

d = x['dataSets'][0]['series']
a = pd.DataFrame(d['0:0:0']['observations'])
b = pd.DataFrame(d['0:1:0']['observations'])

This works absent some manipulation to make it easier to work with, and as there are multiple time series, I can do a version of the same for each, but it goes without saying it's kind of clunky.

Is there a better/cleaner way to do this.

score 2 · Answer 1 · answered Dec 04 '19 at 10:57

2

The pandasdmx library makes this super-simple:

import pandasdmx as sdmx

df = sdmx.Request('OECD').data(
  resource_id='MEI_FIN',
  key='IR3TIB.GBR+USA.M',
  params={'startTime': '2008-06', 'dimensionAtObservation': 'TimeDimension'},
).write()

answered Dec 04 '19 at 10:57

Fredrik Erlandsson

1,279
1
13
22

score 1 · Accepted Answer · answered Jul 06 '19 at 06:36

Absent any responses, here's the solution I came up with. I added a list comprehension to deal with getting each series into a dataframe, and then a transpose as this source resulted in the series being aligned across rows instead of down columns.

import panads as pd
from requests import get 

url = 'http://stats.oecd.org/SDMX-JSON/data/MEI_FIN/IR3TIB.GBR+USA.M/all'
params = { 
        'startTime' : '2008-06',
        'dimensionAtObservation' : 'TimeDimension'
        }

r = get(url, params = params)
x = r.json()

d = x['dataSets'][0]['series']
df = [pd.DataFrame(d[i]['observations']).loc[0] for i in d]
df = pd.DataFrame(df).T

Most efficient way of converting RESTful output to dataframe

2 Answers2

Linked