Define a program to read json file from API

[Navi Reddy]

Hi all,

I need to define a program to read a URL from the EIA API with the data provided in json format. I am using the jsonio package from https://wbuchanan.github.io/StataJSON

The below code reads the json data and outputs a stata dataset. Each series that is listed in the URL part (facets[series][]=C010000001&facets[series][]=C010010001) is output in the resulting stata dataset along with the date.

clear all
jsonio kv, file("https://api.eia.gov/v2/petroleum/cons/prim/data/?frequency=monthly&data[0]=value&facets[series][]=C010000001&facets[series][]=C010010001&start=2019-01&sort[0][column]=period&sort[0][direction]=desc&offset=0&length=5000&api_key=273cebf41acf38547e56cbba6b91765f")

cap drop id key_var
gen id = ustrregexs(2) if ustrregexm(key, "(id_)([0-9]+)(/)(.*)")
gen key_var = ustrregexs(4) if ustrregexm(key, "(id_)([0-9]+)(/)(.*)")
keep if inlist(key_var, "period", "series", "value")
drop key
destring id, replace
reshape wide value, i(id) j(key_var) string
gen date = ym(real(substr(valueperiod, 1,4)),real(substr(valueperiod,6,2)))
format date %tm
destring valuevalue, replace
drop id valueperiod
reshape wide valuevalue, i(date) j(valueseries) string
rename(valuevalue*) *

I would like to take this code and define a program to read other data series provided by EIA. I am looking for something like this.

program define read_eia_url
[above code with necessary changes goes here]
end

And read a different URL using the program defined above. For example:

read_eia_url("https://api.eia.gov/v2/petroleum/cons/prim/data/?frequency=monthly&data[0]=value&facets[series][]=C500000001&facets[series][]=C720000001&start=2019-01&sort[0][column]=period&sort[0][direction]=desc&offset=0&length=5000&api_key=273cebf41acf38547e56cbba6b91765f")

The number of series that are read each time can vary from a single series to more than 10 series.

Any help is appreciated.

[Navi Reddy]

I did some more reading on defining programs and using local macro variables inside the program definition. Below is what I could come up with, but the program breaks with return code 1.

cap program drop read_eia_url
program read_eia_url, rclass
    version 17.0
    args url

    jsonio kv, file(`"`url'"')

    cap drop id key_var
    gen id = ustrregexs(2) if ustrregexm(key, "(id_)([0-9]+)(/)(.*)")
    gen key_var = ustrregexs(4) if ustrregexm(key, "(id_)([0-9]+)(/)(.*)")
    keep if inlist(key_var, "period", "series", "value")
    drop key
    destring id, replace
    reshape wide value, i(id) j(key_var) string
    gen date = ym(real(substr(valueperiod, 1,4)), real(substr(valueperiod,6,2)))
    format date %tm
    destring valuevalue, replace
    drop id valueperiod
    reshape wide valuevalue, i(date) j(valueseries) string
    rename(valuevalue*) *
end


read_eia_url "https://api.eia.gov/v2/petroleum/sum/mkt/data/?frequency=monthly&data[0]=value&facets[series][]=C100000001&start=2019-01&sort[0][column]=period&sort[0][direction]=asc&offset=0&length=5000&api_key=273cebf41acf38547e56cbba6b91765f"

Can you please point me to what's wrong with the code? I appreciate your help.