-
Notifications
You must be signed in to change notification settings - Fork 1
/
sc_table.Rmd
193 lines (135 loc) · 4.88 KB
/
sc_table.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
---
title: "Get Tables via JSON Requests"
description: >
Hands-on guide about transfering data from the STATcube GUI (table view) to R sessions
via a the download option "open data api request".
link_text: JSON requests article
---
```{r, echo = FALSE}
source("R/setup.R")$value
```
```{r, include = FALSE}
if (!sc_key_exists())
knitr::opts_chunk$set(eval = FALSE)
```
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 `r ticle("sc_key")`.
## 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 documentation] 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.
```{r,fig.align='center', out.width='50%', echo=FALSE}
knitr::include_graphics("img/download_json.png")
```
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()`.
``` r
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()`.
```{r, results='hide'}
(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.
```{r}
my_table
```
## Convert the response into a data frame
The return value of `sc_table()` can be converted into a `data.frame` with `as.data.frame()`.
```{r}
as.data.frame(my_table)
```
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.
```{r}
my_table$data
```
## Example datasets {.tabsert .tabset-pills}
This article used a dataset about the austrian populatio n via `sc_example()`.
`r STATcubeR` contains more example jsons to get started.
The datasets can be listed with `sc_examples_list()`.
### Accomodation
```{r}
sc_example("accomodation.json") %>% sc_table()
```
### STATatlas
```{r}
sc_example("economic_atlas.json") %>% sc_table()
```
### Trade
```{r}
sc_example("foreign_trade.json") %>% sc_table()
```
### GDP
```{r}
sc_example("gross_regional_product.json") %>% sc_table()
```
### Working Hours
```{r}
sc_example("labor_force_survey.json") %>% sc_table()
```
### Agriculture
```{r}
sc_example("agriculture_prices.json") %>% sc_table()
```
### monitor.statistik.at
```{r}
sc_example("economic_trend_monitor.json") %>% sc_table()
```
## Choosing the Language {.tabsert .tabset-pills}
The language which is used for labelling can be changed via the `language`
parameter of `sc_table()`.
### Accomodation
```{r}
sc_example("accomodation.json") %>% sc_table("de")
```
### STATatlas
```{r}
sc_example("economic_atlas.json") %>% sc_table("de")
```
### Trade
```{r}
sc_example("foreign_trade.json") %>% sc_table("de")
```
### GDP
```{r}
sc_example("gross_regional_product.json") %>% sc_table("de")
```
### Working Hours
```{r}
sc_example("labor_force_survey.json") %>% sc_table("de")
```
### Agriculture
```{r}
sc_example("agriculture_prices.json") %>% sc_table("de")
```
### monitor.statistik.at
```{r}
sc_example("economic_trend_monitor.json") %>% sc_table("de")
```
## Further reading
* Functionalities of the returned object are explained in the `r ticle("sc_data")`.
* `sc_tabulate()` provides a more flixble way of turning STATcube tables into
`data.frame`s. See the `r ticle("sc_tabulate")` for more details.
* The `r ticle("sc_table_saved")` shows an alternative way of importing tables.
* If you are interested in other API endpoints, see the `r ticle("sc_schema")`
ot the `r ticle("sc_info")`
[`/table` endpoint]: https://docs.wingarc.com.au/superstar/9.12/open-data-api/open-data-api-reference/table-endpoint
[download options]: https://docs.wingarc.com.au/superstar/9.12/superweb2/user-guide/download-tables
[STATcube]: https://www.statistik.at/datenbanken/statcube-statistische-datenbank/login
[STATcube documentation]: https://www.statistik.at/datenbanken/statcube-statistische-datenbank/dokumente-downloads