Retrieving data from Statistics Netherlands

Date last run: 08Dec2019

Introduction

Recently I tried to reproduce an example in a vignette of my package odataR. See here. To my frustration this did not work any more. After some searching I found out that the layout of the table had changed :(
But this gave me reason to study the structure of OData version 3 once again.
And off course to adapt the vignette on the new layout.

Catalog

The available tables can be found with the following code. Some explanation:

• package dplyr is attached because it will be used later on
• HOQCutil::silent_library avoids package startup messages. Package HOQCutil can be found on Github
• without arguments the odataR_get_cat function returns information about all available tables
• because we know the identifier of the table we can use the query to retrieve only the information of that specific table. Annoying that the identifier of the table is 03759ned : most tables have the indication NED but sometimes it is ned.
  

HOQCutil::silent_library(dplyr)
HOQCutil::silent_library(odataR)
cbscat = odataR::odataR_get_cat()
dim(cbscat)
#> [1] 4704   26
cbscat = odataR::odataR_get_cat(
    query="?$filter=startswith(toupper(Identifier),'03759NED')"
  )
tibble::glimpse(cbscat,width=getOption('width')+10)
#> Observations: 1
#> Variables: 26
#> $ Updated             <chr> "2019-05-17T02:00:00"
#> $ ID                  <int> 407
#> $ Identifier          <chr> "03759ned"
#> $ Title               <chr> "Bevolking op 1 januari en gemiddeld; geslacht, leeftijd ...
#> $ ShortTitle          <chr> "Bevolking; geslacht, leeftijd, regio"
#> $ ShortDescription    <chr> "\nDeze tabel bevat cijfers over de bevolking op 1 januar...
#> $ Summary             <chr> "Bevolking op 1 januari en gemiddelde jaarbevolking naar ...
#> $ Modified            <chr> "2019-05-17T02:00:00"
#> $ MetaDataModified    <chr> "2019-05-17T02:00:00"
#> $ ReasonDelivery      <chr> "Correctie"
#> $ ExplanatoryText     <chr> "De cijfers over 2018 van de gemiddelde bevolking in de g...
#> $ OutputStatus        <chr> "Regulier"
#> $ Source              <chr> "CBS."
#> $ Language            <chr> "nl"
#> $ Catalog             <chr> "CBS"
#> $ Frequency           <chr> "Perjaar"
#> $ Period              <chr> "1988 - 2018"
#> $ SummaryAndLinks     <chr> "Bevolking op 1 januari en gemiddelde jaarbevolking naar ...
#> $ ApiUrl              <chr> "https://opendata.cbs.nl/ODataApi/OData/03759ned"
#> $ FeedUrl             <chr> "https://opendata.cbs.nl/ODataFeed/OData/03759ned"
#> $ DefaultPresentation <chr> "ts=1557230974927&graphtype=Table&r=RegioS,Perioden&k=Top...
#> $ DefaultSelection    <chr> "$filter=((Geslacht eq 'T001038')) and ((Leeftijd eq '100...
#> $ GraphTypes          <chr> "Table,Map"
#> $ RecordCount         <int> 45982080
#> $ ColumnCount         <int> 7
#> $ SearchPriority      <chr> "2"

The general information about the table can be found in these catalog fields. The specifics about the coding of columns can be found in tables described in the following section.

Columns in the data tables

With the following code we retrieve the (first 7) attributes of the various dimensions of the table and the topics:

odataR::odataR_get_meta(table_id='03759ned',metatype='DataProperties') %>% 
  select(1:7) %>%
  knitr::kable()

The various subtables that contain the information for these dimensions and topics have (always) the following structure:

odataR::odataR_get_meta(table_id='03759ned',metatype=NULL)  %>%
  knitr::kable()

The information in TableInfos looks similar (but not identical?) to the information about the table in the catalog:

odataR::odataR_get_meta(table_id='03759ned',metatype='TableInfos')  %>%
  tibble::glimpse()
#> Observations: 1
#> Variables: 21
#> $ ID                  <int> 0
#> $ Title               <chr> "Bevolking op 1 januari en gemiddeld; geslacht,...
#> $ ShortTitle          <chr> "Bevolking; geslacht, leeftijd, regio"
#> $ Identifier          <chr> "03759ned"
#> $ Summary             <chr> "Bevolking op 1 januari en gemiddelde jaarbevol...
#> $ Modified            <chr> "2019-05-17T02:00:00"
#> $ ReasonDelivery      <chr> "Correctie"
#> $ ExplanatoryText     <chr> "De cijfers over 2018 van de gemiddelde bevolki...
#> $ Language            <chr> "nl"
#> $ Catalog             <chr> "CBS"
#> $ Frequency           <chr> "Perjaar"
#> $ Period              <chr> "1988 - 2018"
#> $ ShortDescription    <chr> "\nDeze tabel bevat cijfers over de bevolking o...
#> $ Description         <chr> "INHOUDSOPGAVE\r\n\r\n1. Toelichting\r\n2. Defi...
#> $ DefaultPresentation <chr> "ts=1557230974927&graphtype=Table&r=RegioS,Peri...
#> $ DefaultSelection    <chr> "$filter=((Geslacht eq 'T001038')) and ((Leefti...
#> $ GraphTypes          <chr> "Table,Map"
#> $ OutputStatus        <chr> "Regulier"
#> $ Source              <chr> "CBS."
#> $ MetaDataModified    <chr> "2019-05-17T02:00:00"
#> $ SearchPriority      <chr> "2"

The information in UntypedDataset and TypedDataSet (the difference is described in the pdf Handleiding CBS Open Data Services on pages 22 and 23) contains the actual data in coded form. We display the number of ‘records’ in TypedDataSet and the contents of the ‘records’ 101 up to 103 (numbering starts with 0) :

odataR::odataR_get_table(table_id='03759ned',query='$count')
#> [1] 45982080
odataR::odataR_get_table(table_id='03759ned',decode=F,
          query='?$skip=100&$top=3')  %>%
  knitr::kable()

For each of the coded dimensions in UntypedDataset and TypedDataSet there exist a code table

odataR::odataR_get_meta(table_id='03759ned',metatype='Geslacht')  %>%
  knitr::kable()

odataR::odataR_get_meta(table_id='03759ned',metatype='Leeftijd',query='?$top=3')  %>%
  knitr::kable()

odataR::odataR_get_meta(table_id='03759ned',metatype='CategoryGroups',query='?$top=3')  %>%
  knitr::kable()

odataR::odataR_get_meta(table_id='03759ned',metatype='BurgerlijkeStaat')  %>%
  dplyr::mutate(Description=gsub('\r\n',' ',Description)) %>%
  dplyr::mutate(Description=substr(Description,1,40)) %>%
  knitr::kable()

odataR::odataR_get_meta(table_id='03759ned',metatype='RegioS',query='?$top=3')  %>%
  dplyr::mutate(Description=gsub('\r\n',' ',Description)) %>%
  dplyr::mutate(Description=substr(Description,1,40)) %>%
  knitr::kable()

odataR::odataR_get_meta(table_id='03759ned',metatype='Perioden',query='?$top=3')  %>%
  knitr::kable()

Example

To find for the year 2016 the number of single men living in Amstelveen that are in their thirties you could use the following query. Note that the keys are all character and therefore the spaces following the actual code are necessary. An alternative is using the startswith function: e.g. and startswith(Geslacht,'3000').

odataR::odataR_get_table(
  table_id='03759NED',
  query=paste0(
      "?$filter=startswith(RegioS,'GM0362')", # Amstelveen
      " and Geslacht eq '3000   '",           # men only
      " and BurgerlijkeStaat eq '1010   '",   # not married
      " and startswith(Leeftijd,'13')",       # age 30, 31, ...39
      " and Perioden eq '2016JJ00'",          # in 2016
      "&$select=Leeftijd,BevolkingOp1Januari_1,GemiddeldeBevolking_2"
  ) ) %>%
  dplyr::select(Leeftijd,BevolkingOp1Januari_1,GemiddeldeBevolking_2) %>%
  knitr::kable()

Session Info

This document was produced on 08Dec2019 with the following R environment:

  #> R version 3.6.0 (2019-04-26)
  #> Platform: x86_64-w64-mingw32/x64 (64-bit)
  #> Running under: Windows 10 x64 (build 18362)
  #> 
  #> Matrix products: default
  #> 
  #> locale:
  #> [1] LC_COLLATE=English_United States.1252 
  #> [2] LC_CTYPE=English_United States.1252   
  #> [3] LC_MONETARY=English_United States.1252
  #> [4] LC_NUMERIC=C                          
  #> [5] LC_TIME=English_United States.1252    
  #> 
  #> attached base packages:
  #> [1] stats     graphics  grDevices utils     datasets  methods   base     
  #> 
  #> other attached packages:
  #> [1] odataR_0.1.3 dplyr_0.8.3 
  #> 
  #> loaded via a namespace (and not attached):
  #>  [1] Rcpp_1.0.3       knitr_1.26       magrittr_1.5     tidyselect_0.2.5
  #>  [5] HOQCutil_0.1.15  R6_2.4.1         rlang_0.4.2      fansi_0.4.0     
  #>  [9] highr_0.8        stringr_1.4.0    httr_1.4.1       tools_3.6.0     
  #> [13] xfun_0.10        utf8_1.1.4       cli_1.1.0        htmltools_0.4.0 
  #> [17] digest_0.6.23    assertthat_0.2.1 tibble_2.1.3     crayon_1.3.4    
  #> [21] purrr_0.3.3      captioner_2.2.3  vctrs_0.2.0      zeallot_0.1.0   
  #> [25] curl_4.3         glue_1.3.1       evaluate_0.14    rmarkdown_1.18  
  #> [29] stringi_1.4.3    compiler_3.6.0   pillar_1.4.2     backports_1.1.5 
  #> [33] jsonlite_1.6     pkgconfig_2.0.3