cbsodataR, all data of Statistics Netherlands (CBS)

Edwin de Jonge

2020-09-14

Statistics Netherlands (CBS) is the office that produces all official statistics of the Netherlands.

For long SN has put its data on the web in its online database StatLine. Since 2014 this data base has an open data web API based on the OData protocol. The cbsodataR package allows for retrieving data right into R.

Table of Contents

A list of tables can be retrieved using the cbs_get_datasets (cbs_get_toc) function.

library(dplyr) # not needed, but used in examples below
library(cbsodataR)

datasets <- cbs_get_datasets() 

datasets %>% 
  filter(Language == "en") %>% # only English tables
  select(Identifier, ShortTitle) 

## # A tibble: 828 x 2
##    Identifier ShortTitle                             
##    <chr>      <chr>                                  
##  1 80783eng   Agriculture; general farm type, region 
##  2 80784eng   Agriculture; labour force, region      
##  3 7100eng    Arable crops; production               
##  4 70671ENG   Fruit culture; area fruit orchards     
##  5 37738ENG   Vegetables; yield per kind of vegetable
##  6 71509ENG   Yield apples and pears, 1997 - 2017    
##  7 83981ENG   Livestock manure; key figures          
##  8 80274eng   Livestock cattle                       
##  9 7373eng    Livestock pigs                         
## 10 7425eng    Milk supply and dairy production       
## # … with 818 more rows

Search for tables

Tables can be searched for using the cbs_search function.

toc_apples <- cbs_search(c("elstar", "apple"), language = "en")
toc_apples[, c("Identifier", "ShortTitle", "score")]

## # A tibble: 1 x 3
##   Identifier ShortTitle                          score
##   <chr>      <chr>                               <dbl>
## 1 71509ENG   Yield apples and pears, 1997 - 2017  14.5

Other catalogs

Other catalogs with data are available:

catalogs <- cbs_get_catalogs()
catalogs$Identifier

##  [1] "CBS"      "MKB"      "IV3"      "MLZ"      "JM"       "RIVM"    
##  [7] "Politie"  "MVstat"   "AZW"      "InterReg"

Metadata

Using an “Identifier” from cbs_get_datasets or cbs_search information on the table can be retrieved with cbs_get_meta

apples <- cbs_get_meta('71509ENG')
apples

## 71509ENG: 'Yield apples and pears, 1997 - 2017', 2017
##   FruitFarmingRegions: 'Fruit farming regions'
##   Periods: 'Periods' 
## 
## Retrieve a default data selection with:
##  cbs_get_data(id = "71509ENG", FruitFarmingRegions = c("1", "2", 
## "4", "3", "5"), Periods = c("1997JJ00", "2012JJ00", "2013JJ00", 
## "2016JJ00"), select = c("FruitFarmingRegions", "Periods", "TotalAppleVarieties_1", 
## "CoxSOrangePippin_2", "DelbarestivaleDelcorf_3", "Elstar_4", 
## "GoldenDelicious_5", "Jonagold_6", "Jonagored_7", "RodeBoskoopRennetApple_10", 
## "OtherAppleVarieties_12", "TotalPearVarieties_13", "Conference_15", 
## "DoyenneDuComice_16", "CookingPears_17", "TriompheDeVienne_18", 
## "OtherPearVarieties_19", "TotalAppleVarieties_20", "CoxSOrangePippin_21", 
## "DelbarestivaleDelcorf_22", "Elstar_23", "GoldenDelicious_24", 
## "Jonagold_25", "Jonagored_26", "RodeBoskoopRennetApple_29", "OtherAppleVarieties_31", 
## "TotalPearVarieties_32", "Conference_34", "DoyenneDuComice_35", 
## "CookingPears_36", "TriompheDeVienne_37", "OtherPearVarieties_38"
## ))

The meta object contains all metadata properties of cbsodata (see the original documentation) in the form of data.frames. Each data.frame describes properties of the SN table.

names(apples)

## [1] "TableInfos"          "DataProperties"      "CategoryGroups"     
## [4] "FruitFarmingRegions" "Periods"

Data download

With cbs_get_data data can be retrieved. By default all data for this table will be downloaded in a temporary directory.

cbs_get_data('71509ENG') %>% 
  select(1:4) %>%  # demonstration purpose
  head()

## # A tibble: 6 x 4
##   FruitFarmingRegions Periods  TotalAppleVarieties_1 CoxSOrangePippin_2
##   <chr>               <chr>                    <int>              <int>
## 1 1                   1997JJ00                   420                 43
## 2 1                   1998JJ00                   518                 40
## 3 1                   1999JJ00                   568                 39
## 4 1                   2000JJ00                   461                 27
## 5 1                   2001JJ00                   408                 30
## 6 1                   2002JJ00                   354                 17

Select and filter`

It is possible restrict the download using filter statements. This may shorten the download time considerably.

Filter

Filter statements for the columns can be used to restrict the download. Note the following:

apples <- cbs_get_meta('71509ENG')
names(apples)

## [1] "TableInfos"          "DataProperties"      "CategoryGroups"     
## [4] "FruitFarmingRegions" "Periods"

# meta data for column Periods
head(apples$Periods[,1:2])

##        Key Title
## 1 1997JJ00  1997
## 2 1998JJ00  1998
## 3 1999JJ00  1999
## 4 2000JJ00  2000
## 5 2001JJ00  2001
## 6 2002JJ00  2002

#meta data for column FruitFarmingRegions
head(apples$FruitFarmingRegions[,1:2 ])

##   Key             Title
## 1   1 Total Netherlands
## 2   2      Region North
## 3   4       Region West
## 4   3    Region Central
## 5   5      Region South
  cbs_get_data( '71509ENG'
              , Periods=c('2000JJ00','2001JJ00') # selection on Periods column
              , FruitFarmingRegions = "1" # selection on FruitFarmingRegions
              #
              # restrict the columns to the following as found in
              # apples$DataProperties with "select"
              , select = c("FruitFarmingRegions", "Periods", "TotalAppleVarieties_1")  
              ) %>% 
  cbs_add_label_columns()

## # A tibble: 2 x 5
##   FruitFarmingRegi… FruitFarmingRegion… Periods  Periods_label TotalAppleVariet…
##   <chr>             <fct>               <chr>    <fct>                     <int>
## 1 1                 Total Netherlands   2000JJ00 2000                        461
## 2 1                 Total Netherlands   2001JJ00 2001                        408
  cbs_get_data( '71509ENG'
              , Periods = has_substring('2000') # selection on Periods column
              , FruitFarmingRegions = "1" # selection on FruitFarmingRegions
              #
              # restrict the columns to the following as found in
              # cbs_get_meta("71509ENG")$DataProperties with "select"
              , select = c("FruitFarmingRegions", "Periods", "TotalAppleVarieties_1")  
              ) %>% 
    cbs_add_label_columns()

## # A tibble: 1 x 5
##   FruitFarmingRegi… FruitFarmingRegion… Periods  Periods_label TotalAppleVariet…
##   <chr>             <fct>               <chr>    <fct>                     <int>
## 1 1                 Total Netherlands   2000JJ00 2000                        461
  cbs_get_data( '71509ENG'
              , Periods = eq("2010JJ00") | has_substring('2000') # selection on Periods column
              , FruitFarmingRegions = "1" # selection on FruitFarmingRegions
              #
              # restrict the columns to the following as found in
              # cbs_get_meta("71509ENG")$DataProperties with "select"
              , select = c("FruitFarmingRegions", "Periods", "TotalAppleVarieties_1")  
              ) %>% 
    cbs_add_label_columns()

## # A tibble: 2 x 5
##   FruitFarmingRegi… FruitFarmingRegion… Periods  Periods_label TotalAppleVariet…
##   <chr>             <fct>               <chr>    <fct>                     <int>
## 1 1                 Total Netherlands   2000JJ00 2000                        461
## 2 1                 Total Netherlands   2010JJ00 2010                        334

Download data

Data can also be downloaded explicitly by using cbs_download_table