Skip to contents

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 <82>, Age in single years <96> <7>, Sex <2> <3>, Commune
#>   <2383> (Province-District) <10>
#> 
#> Request: [2022-08-29 09:52:04]
#> 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: 17,220 x 5
   Quarter    `Age in single years <96>` `Sex <2>` Commune <2383> (Pro…¹ Numbe…²
   <date>     <fct>                      <fct>     <fct>                   <dbl>
 1 2002-01-01 Up to 14 years old         male      Burgenland <AT11>       21287
 2 2002-01-01 Up to 14 years old         male      Carinthia <AT21>        47230
 3 2002-01-01 Up to 14 years old         male      Vienna <AT13>          117920
 4 2002-01-01 Up to 14 years old         male      Vorarlberg <AT34>       34798
 5 2002-01-01 Up to 14 years old         male      Tyrol <AT33>            62794
 6 2002-01-01 Up to 14 years old         male      Styria <AT22>           97538
 7 2002-01-01 Up to 14 years old         male      Salzburg <AT32>         46955
 8 2002-01-01 Up to 14 years old         male      Upper Austria <AT31>   127316
 9 2002-01-01 Up to 14 years old         male      Lower Austria <AT12>   133928
10 2002-01-01 Up to 14 years old         male      Total                  689766
# … with 17,210 more rows, and abbreviated variable names
#   ¹​`Commune <2383> (Province-District)`, ²​`Number of persons`

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: 17,220 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
# … with 17,210 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()
#> Accomodation statistics as of 1974 according to seasons
#> 
#> Database: detouextregsai (STATcube)
#> Measures: Nights spent, Arrivals
#> Fields: Season/Tourism Month <273>, Country of origin <4>, Accomodation
#>   establishment <4>
#> 
#> Request: [2022-08-29 09:47:07]
#> STATcubeR: 0.5.0
sc_example("economic_atlas.json") %>% sc_table()
#> 02 Key data Federal provinces
#> 
#> Database: dewatlas2 (STATcube)
#> Measures: Umemployment rate - ILO-definition, Employment rate (15 - 64 y.) -
#>   ILO-definition, Overnight stays, Average duration of stay (in nights),
#>   Private households, Total area (km²), Population (annual average), R&D
#>   intensity (in % of GDP), Employed persons - ILO-definition, Unemployed -
#>   ILO-definition, … (38 more)
#> Fields: Year (starting 1995) <28>, Federal provices <11>
#> 
#> Request: [2022-09-21 11:18:57]
#> STATcubeR: 0.5.0
sc_example("foreign_trade.json") %>% sc_table()
#> Trade by commodity (CPA) and activity sector (NACE)
#> 
#> Database: denatec06 (STATcube)
#> Measures: Import, number of enterprises, Import, value in Euro, Export,
#>   number of enterprises, Export, value in Euro
#> Fields: Commodities (CPA) <4>, Reference year <13>, Activity Sector (NACE)
#>   [partly ABO] <4>
#> 
#> Request: [2022-08-30 19:08:33]
#> STATcubeR: 0.5.0
sc_example("gross_regional_product.json") %>% sc_table()
#> Gross regional product by ESA 1995, NUTS2+NUTS3 - finished time
#> series
#> 
#> Database: devgrrgr004 (STATcube)
#> Measures: Gross regional product; current prices in million Euro, Gross
#>   regional product per inhabitant, Gross regional product per person
#>   employed
#> Fields: NUTS-3 <11>, Time <13>
#> 
#> Request: [2022-08-30 19:06:56]
#> STATcubeR: 0.5.0
sc_example("labor_force_survey.json") %>% sc_table()
#> Working hours (Labour Force Survey)
#> 
#> Database: deake005 (STATcube)
#> Measures: Average hours actually worked per week, Average hours usually
#>   worked per week
#> Fields: Time section <8>, Gender <3>, Highest level of training completed
#>   (national classification) <6>, Province (NUTS 2 unit) <10>
#> 
#> Request: [2022-08-31 12:48:22]
#> STATcubeR: 0.5.0
sc_example("agriculture_prices.json") %>% sc_table()
#> EAA01_Economic accounts for agriculture at current prices in million
#> euros
#> 
#> Database: delgr001 (STATcube)
#> Measures: Values (for output items and value added: values at basic prices),
#>   Taxes on products (for output items), Values at producer prices (for
#>   output items), Subsidies on products (for output items)
#> Fields: Year <28>, Item <6>
#> 
#> Request: [2022-09-21 11:19:01]
#> STATcubeR: 0.5.0
sc_example("economic_trend_monitor.json") %>% sc_table()
#> Economic Trend Monitor
#> 
#> Database: dekonjunkturmonitor (STATcube)
#> Measures: Production Index Industry (wd; 2015=100), Technical total
#>   production Industry (in 1.000 €), Turnover Index Industry (2015=100),
#>   Turnover Industry (in 1.000 €), Index of new orders Industry (2015=100),
#>   Index of persons employed Industry (2015=100), Persons employed Industry,
#>   Productivity Index Industry per employee (2015=100), Productivity Index
#>   Industry per hours worked (2015=100), Industrial Output Price Index
#>   (2021=100), … (53 more)
#> Fields: reporting period <165>, value indication <2>
#> 
#> Request: [2022-09-21 11:19:05]
#> STATcubeR: 0.5.0

Further reading