Skip to contents

Get Tables via JSON Requests

Source: vignettes/sc_table.Rmd
sc_table.Rmd
## ✔ Key could be verified via a test request
## ℹ The provided key will be available for this R session
## ℹ Add `STATCUBE_KEY_EXT = XXXX` to "~/.Renviron" to set the key
##   persistently. Replace `XXXX` with your key

In the following example, a table will be exported from STATcube into an R session. This process involves four steps

  • create a table with the STATcube GUI (table view)
  • download an “API request” for the table (format: *.json).
  • send the json file to the API using sc_table().
  • convert the return value into a data.frame

It is assumed that you already provided your API key as described in the API key article.

Create a table with the STATcube GUI

Use the graphical user interface of STATcube to create a table. Visit STATcube and select a database. This will open the table view where you can create a table. See the STATcube manual for details.

Download an API request

Choose “Open Data API Query (.json)” in the download options. This will save a json file on your local file system.

It might be the case that this download option is not listed as a download format. This means that the current user is not permitted to use the API.

Send the json to the API

Provide the path to the downloaded json file as a string in sc_table().

my_table <- sc_table(json_file = "path/to/api_request.json")

This will send the json-request to the /table endpoint of the API and return an object of class sc_table. We will demonstrate this with an example json via sc_example().

(json_path <- sc_example("population_timeseries.json"))
## [1] "~/R/3.6/STATcubeR/json_examples/population_timeseries.json"
my_table <- sc_table(json_path)

Printing the object my_table will summarize the data contained in the response.

my_table
#> Population at the beginning of the quarter since 2002
#> 
#> Database: debevstand (STATcube)
#> Measures: Number of persons
#> Fields: Quarter <86>, Age in single years <96> <7>, Sex <2> <3>, Commune
#>   <2383> (Province-District) <10>
#> 
#> Request: [2024-04-18 10:07:38]
#> STATcubeR: 0.5.0

Convert the response into a data frame

The return value of sc_table() can be converted into a data.frame with as.data.frame().

as.data.frame(my_table)
# A STATcubeR tibble: 18,060 x 5
   Quarter    `Age in single years <96>` `Sex <2>` Commune <2383> (Province-Di…¹
   <date>     <fct>                      <fct>     <fct>                        
 1 2002-01-01 Up to 14 years old         male      Burgenland <AT11>            
 2 2002-01-01 Up to 14 years old         male      Carinthia <AT21>             
 3 2002-01-01 Up to 14 years old         male      Vienna <AT13>                
 4 2002-01-01 Up to 14 years old         male      Vorarlberg <AT34>            
 5 2002-01-01 Up to 14 years old         male      Tyrol <AT33>                 
 6 2002-01-01 Up to 14 years old         male      Styria <AT22>                
 7 2002-01-01 Up to 14 years old         male      Salzburg <AT32>              
 8 2002-01-01 Up to 14 years old         male      Upper Austria <AT31>         
 9 2002-01-01 Up to 14 years old         male      Lower Austria <AT12>         
10 2002-01-01 Up to 14 years old         male      Total                        
# ℹ 18,050 more rows
# ℹ abbreviated name: ¹​`Commune <2383> (Province-District)`
# ℹ 1 more variable: `Number of persons` <dbl>

This will produce a data.frame, which contains a column for each classification field of the table. Furthermore, one column will be present for each measure. In other words, the data uses a long format. If you prefer to use codes rather than labels, use my_table$data instead.

my_table$data
# A STATcubeR tibble: 18,060 x 5
   `C-A10-0` `C-BESC51-0` `C-BESC11-0` `C-C41-2` `F-ISIS-1`
   <fct>     <fct>        <fct>        <fct>          <dbl>
 1 A10-20021 BESN07-1     1            B00-1          21287
 2 A10-20021 BESN07-1     1            B00-2          47230
 3 A10-20021 BESN07-1     1            B00-9         117920
 4 A10-20021 BESN07-1     1            B00-8          34798
 5 A10-20021 BESN07-1     1            B00-7          62794
 6 A10-20021 BESN07-1     1            B00-6          97538
 7 A10-20021 BESN07-1     1            B00-5          46955
 8 A10-20021 BESN07-1     1            B00-4         127316
 9 A10-20021 BESN07-1     1            B00-3         133928
10 A10-20021 BESN07-1     1            SC_TOTAL      689766
# ℹ 18,050 more rows

Example datasets

This article used a dataset about the austrian populatio n via sc_example(). STATcubeR contains more example jsons to get started. The datasets can be listed with sc_examples_list().

sc_example("accomodation.json") %>% sc_table()
sc_example("economic_atlas.json") %>% sc_table()
sc_example("foreign_trade.json") %>% sc_table()
sc_example("gross_regional_product.json") %>% sc_table()
sc_example("labor_force_survey.json") %>% sc_table()

{r, eval = FALSE sc_example("agriculture_prices.json") %>% sc_table()

sc_example("economic_trend_monitor.json") %>% sc_table()

Further reading