Beyond ‘saved queries’

Use case ‘permanent links!’

the session URL is not a permanent link!

… explaining what happens when you ‘Save your query’ is ok (not perfect)

… users are happy, when they understand the feature

but try to explain ‘API query for this table’ ?

So I ask:

Did you ever wonder, what to do?

Statistics Sweden has the basic information on how to use the Pxweb API on:

https://www.scb.se/api_en/

Link to programmers guide

The filter specifies how the values are given. Supported filters are:

Item. This filter lists valid values in the values collection.
All. This filter uses a wildcard selector on the values. Each wildcard selector is given in the values collection. Only one wild card is allowed. E.g. 01* gives all values that starts with 01, * gives all values.
Top. This is used to select the first number of values. The amount of values is given by the first value in the values collection. If the variable is a time variable, then this will select the latest number of time periods.
Agg. This states that the values listed in the values collection are aggregated. The identity of the aggregation is given after the colon, e.g. agg:ageG5.
Vs. This states that the values listed in the values collection are from a different value set. The identity of the value set is given after the colon, e.g. vs:regionX.

output formats

And as output formats, where ‘px’ is default there is:

• csv • csv2 • csv3 • json • xlsx • json-stat • json-stat2 • sdmx •

Run this R-script in RStudio

Copy/Paste from Pxweb

url <- "https://bank.stat.gl:443/api/v1/en/Greenland/BE/BE01/BE0120/BEXSTA.px"

data <- '
{
  "query": [
    {
      "code": "place of birth",
      "selection": {
        "filter": "all",
        "values": [
          "*"
        ]
      }
    },
    {
      "code": "gender",
      "selection": {
        "filter": "all",
        "values": [
          "*"
        ]
      }
    },
    {
      "code": "age",
      "selection": {
        "filter": "all",
        "values": [
          "*"
        ]
      }
    },
    {
      "code": "residence type",
      "selection": {
        "filter": "all",
        "values": [
          "*"
        ]
      }
    },
    {
      "code": "time",
      "selection": {
        "filter": "top",
        "values": [
          "10"
        ]
      }
    }
  ],
  "response": {
    "format": "px"
  }
}
'

perhaps do a little query editing

# filter 'all' and wildcard

        "filter": "all",
        "values": ["*"]

# is the same as filter 'item' and all items in a variable         

        "filter": "item",
        "values": [
          "18",
          "19",
          "20",
          "25",
          "26",
          "27",
          "28",
          "29"

the POST part

 # httr makes http easy - get help type ?http in console
library(httr)

response <- POST(
    url, 
    body = data,
    encode = "raw", 
    content_type("application/json"))
    
hi <- content(response, as = "text")

writeLines(hi, con = "hi.px")

library(pxmake)

x <- px("hi.px")

# px_save(x,"hi.xlsx")

… or …

in Excel - do a makro

# Sub PostRequest()
#     Dim http As Object
#     Dim url As String
#     Dim payload As String
#     Dim responseText As String
# 
#     url = "https://bank.stat.gl:443/api/v1/en/Greenland/BE/BE01/BE0120/BEXSTA.px"
#     payload = "{""query"": [],""response"": {""format"": ""csv2""}}"
# 
#     Set http = CreateObject("MSXML2.XMLHTTP.6.0")
# 
#     ' Set up POST request
#     http.Open "POST", url, False
#     http.setRequestHeader "Content-Type", "application/json"
#     http.Send payload
# 
#     If http.Status = 200 Then
#         responseText = http.responseText
#         ThisWorkbook.Sheets("Ark1").Range("A1").Value = responseText
#     Else
#         MsgBox "Request failed with status: " & http.Status
#     End If
# 
#     Set http = Nothing
# End Sub

2 CRAN packages for getting data

pxweb, rOpenGov The R package can interact with all PX-Web/PC-Axis APIs to fetch information about the data hierarchy, extract metadata and extract and parse statistics to R data.frame format.
Link getting started with pxweb

PxWebApiData, by SSB.no
Link introduction to PxWebApiData

pxweb::interactive

pxweb::interactive_pxweb()
pxweb::pxweb_interactive()
===================================================================================
 R PXWEB API CATALOGUE:
===================================================================================
 [  1 ] : Statistics Sweden
 [  2 ] : Statistics Finland
 [  3 ] : Statistics Finland (old version)
 [  4 ] : The Swedish Agricultural Agency
 [  5 ] : The Public Health Agency of Sweden
 [  6 ] : The Swedish National Institute of Economic Research
 [  7 ] : The Swedish national institute of economic research, forecast database
 [  8 ] : LUKE Natural Resources Institute Finland
 [  9 ] : Verohallinto - Finnish Tax Administration
 [ 10 ] : Statistics Iceland
 [ 11 ] : Linköping municipality in Sweden
 [ 12 ] : Vastra Gotaland Region in Sweden
 [ 13 ] : Statbank Greenland
 [ 14 ] : Icelandic Centre for Retail Studies
 [ 15 ] : Statistics Faroe Islands
 [ 16 ] : Statistics Norway
 [ 17 ] : Statistics Aland
 [ 18 ] : State Statistical Office of the Republic of Macedonia
 [ 19 ] : Latvia - official statistics
 [ 20 ] : Statistics Moldova
 [ 21 ] : Statistics Switzerland
 [ 22 ] : Statistics Kosovo
 [ 23 ] : Finnish Transport Safety Agency
 [ 24 ] : United Nations Economic Commission for Europe
 [ 25 ] : Portail statistique de la Grande Région
 [ 26 ] : Visit Finland (Rudolf service)
 [ 27 ] : Helsingin seudun aluesarjat -tilastotietokanta
 [ 28 ] : Estonia - official statistics
 [ 29 ] : Nordic Statistics Database
 [ 30 ] : SiStat Database
===================================================================================
Enter your choice:
('esc' = Quit, 'i' = Show id)

NOT on the list?

Our package on Github

statgl - to extract tables from our px-file based Statbank, and do presentations

UN Demographic Yearbook

General public landing page

Link to more awesomeofficialstatistics.org