Tagarchief: CBS

CBS en Open Data

Excel, , ,
Voorbeeldbestand: CBS2.xlsxDownloaden

In het vorige artikel hebben we laten zien hoe je ‘makkelijk’ gegevens van de CBS-site kunt overnemen; door kopiëren en door het gebruik van de download-optie.

Op het einde van dat artikel hebben we beloofd dat we deze keer zouden laten zien hoe je op een meer geautomatiseerde manier gegevens van het CBS kunt overnemen in Excel.

Het CBS en Excel kennen allebei het Open-Data-protocol; daar gaan we gebruik van maken. In onderstaand artikel allereerst daarom een korte uitleg van Open Data.

Open Data

Om gegevens tussen systemen te kunnen uitwisselen moeten uiteraard de vorm en inhoud hiervan bekend zijn. Wil je de uitwisseling automatiseren dan moeten er harde afspraken over die vorm en inhoud gemaakt worden.

In dit geval bepaalt het CBS de inhoud (welke gegevens worden beschikbaar gesteld, hoe vaak et cetera), maar kunnen we zelf op hun website min of meer de lay-out en vorm van uitwisselen bepalen (zoals we in het vorige artikel zagen).

Het CBS ondersteunt echter ook het zogenaamde Open Data-protocol (via zogenaamde API‘s). Dit is een set van afspraken waardoor de aanleverende en de ontvangende partij precies weten wat de vorm van de aangeleverde gegevens is. Ook Excel (als ontvanger) ondersteunt dit protocol.

Op dit moment gaat het CBS over van versie 3 naar versie 4 van het OData-protocol (zie www.cbs.nl/../open-data en www.cbs.nl/../statline-als-open-data). Excel kan met allebei de versies overweg.

Power Query

Wanneer je in een CBS-Statline-scherm op de Download-button klikt, zie je dat er ook een mogelijkheid is om naar het Dataportaal te gaan:

Op deze pagina kun je verschillende datasets downloaden en informatie over de (verwerking van) Open Data vinden. Voor het verder automatiseren in Excel hebben we alleen de Identifier nodig (in dit geval 85264NED):

  1. kies in de menutab Gegevens de optie Gegevens ophalen/Van andere bronnen/Uit OData feed
  2. in het vervolgscherm wordt gevraagd naar de URL; dat is in dit geval opendata.cbs.nl/ODataFeed/odata/85264NED
    Klik OK.
  1. Kies de tabel TypedDataset en kies Gegevens transformeren
  2. de kolom ID hebben we niet nodig; Verwijderen dus
  3. kies in de menutab Start de optie Sluiten en laden
  4. de tabel wordt in een Excel-tabblad geplaatst (zie het tabblad Power in het Voorbeeldbestand)

We hebben nu een zogenaamd genormaliseerde tabel van het CBS binnengehaald. Een nadeel is dat de tabel niet een zodanige vorm heeft dat je daar gemakkelijk een draaitabel ‘op los kunt laten’. Daar moeten we nog wat aan doen!

NB1 het ophalen van een Open-Data-tabel vanuit de CBS-website gaat altijd via de URL opendata.cbs.nl/ODataFeed/odata/Identifier, waarbij je zelf de juiste Identifier dient op te geven.

NB2 de query en de Excel-tabel krijgen automatisch de naam TypedDataSet.

Power Query 2

In het tabblad Power2 in het Voorbeeldbestand hebben we op dezelfde manier als hiervoor de tabel TypedDataset opgehaald.
Maar omdat we alleen geïnteresseerd zijn in jaar-cijfers zijn de op te halen rijen gefilterd:

  1. kies het vinkje achter Perioden
  2. selecteer binnen de optie Tekstfilters de keuze Bevat …
  3. vul JJ in
  4. en klik OK

Maar nu willen we nog een database-layout van de tabel maken. De eerste 4 kolommen voldoen daar al aan, maar de anderen niet:

  1. selecteer de eerste 4 kolommen
  2. kies binnen de menutab Transformeren binnen Draaitabel opheffen voor kolommen de optie Draaitabel voor andere kolommen opheffen
  3. kies Sluiten en laden en plaats het resultaat in een Excel-tabel

NB de naam van de query en de Exceltabel worden TypedDataSet (2) respectievelijk TypedDataSet_2, omdat de naam TypedDataSet al voorkomt.

Wanneer je nu een draaitabel op basis van de tabel maakt moet je wel goed uitkijken welke selectie je maakt:

De tabel staat nog vol met codes; dat is ook niet wat we willen.
Het CBS heeft ieder Open-Data-item netjes uitgenormaliseerd: “Normalisatie is het proces van het ordenen van gegevens in een database. Dat betreft het maken van tabellen en het leggen van relaties tussen die tabellen op basis van regels die als doel hebben de gegevens te beveiligen en de database flexibeler te maken door redundantie en inconsistente afhankelijkheid te voorkomen“.

Willen we dus af van al die codes dan zullen we ook de gerelateerde tabellen moeten binnenhalen.

Power Query 3

We gaan opnieuw de Arbeidsdeelname, kerncijfers ophalen:

  1. kies in de menutab Gegevens de optie Gegevens ophalen/Van andere bronnen/Uit OData feed
  2. in het vervolgscherm wordt gevraagd naar de URL; dat is in dit geval ook weer opendata.cbs.nl/ODataFeed/odata/85264NED
    Klik OK.
  3. plaats een vinkje bij Meerdere items selecteren
  4. vink alle tabellen aan behalve UntypedDataset en kies Gegevens transformeren
  5. alle aangevinkte tabellen worden tegelijkertijd in Power Query binnengehaald
  6. nu gaan we de codes in de kolom Geslacht “vertalen”:
    * kies binnen de menutab Start de optie Query’s samenvoegen
    * vul het vervolgscherm als volgt in:

    * klik OK
    * klik dan op de 2 pijltjes in de kop van de nieuwe kolom achteraan

    * zorg dat in het vervolgscherm alleen Title is aangevinkt en klik OK
  1. hetzelfde doen we met Leeftijd en HoogstBehaaldOnderwijsNiveau (zie de stappen in Power Query in het Voorbeeldbestand; voor uitleg over het werken in Power Query, zie het artikel Power Query)
  2. in de volgende stappen wijzigen we de volgorde van de kolommen, verwijderen we overbodige kolommen en voeren nog wat cosmetische acties uit.
    Daarna zorgen we er weer voor dat we een mooie database-layout krijgen (Draaitabel voor andere kolommen opheffen).
  3. ook de codes in de Kenmerk kolom “vertalen” we.
  1. we sluiten Power Query, maar let op: kies Sluiten en laden naar … en zorg dat er alleen een verbinding gemaakt wordt:

    Op deze manier blijven alle gegevens in het geheugen, maar worden niet nog eens dubbel in Excel zelf vastgelegd.
  1. klik dan rechts op de tabel TypedDataset (3)
  2. kies de optie Laden naar … en vul het scherm als volgt in

    Door de tabel in het gegevensmodel op te nemen kunnen we toch analyses uitvoeren met behulp van draaitabellen.

Als voorbeeld maken we een draaitabel:

  1. open een nieuw tabblad
  2. selecteer de cel waar de draaitabel moet komen
  3. kies in de menutab Invoegen de optie Draaitabel
  4. in het volgende scherm staan alle opties goed:

    Excel kiest als basis voor de draaitabel het Gegevensmodel.
  1. vul het Filters-, Kolommen-, Rijen- en Waarden-gebied in met velden uit de 3e TypedDataSet zoals hiernaast weergegeven.
    Het resultaat (zie het tabblad Power3 van het Voorbeeldbestand):

Twee andere voorbeelden, die je nu snel op basis van de CBS-gegevens kunt maken:

en

Zijn de gegevens bij het CBS gewijzigd of aangevuld? Kies in de menutab Gegevens de optie Alles vernieuwen en alle overzichten en grafieken worden automatisch aangepast!

CBS-cijfers

Excel, ,
Voorbeeldbestand: CBS1.xlsxDownloaden

In sommige situaties is het handig om niet alleen het CBS als bron te noemen maar om ook de onderliggende info in je eigen analyse over te nemen en/of te gebruiken.

Er zijn verschillende methoden om die cijfers in Excel te krijgen. Hierna zullen we er enkele bespreken: allereerst het simpelweg kopiëren, daarna het op verschillende manieren downloaden van cijfers.

In een volgend artikel zullen we ook het gebruik van de Open Data-optie bespreken, waarmee het ophalen van cijfers verder gestandaardiseerd en geautomatiseerd kan worden.

Aanleiding

Aanleiding voor dit artikel was een verhaal in de Volkskrant van 3 mei jl. Dat ging over de resultaten van een rapport van het CBS, De arbeidsmarkt in cijfers 2022.

Bij het lezen van het artikel en het rapport kreeg ik de behoefte om op bepaalde onderwerpen wat verder in te zoomen. Daarvoor had ik wel de onderliggende cijfers nodig.

Een samenvatting met de belangrijkste resultaten van het CBS-onderzoek was snel gekopieerd:

  • selecteer de gewenste gegevens op de website
  • kies Kopiëren, Ctrl-C
  • selecteer de cel in Excel waar de linkerbovenhoek moet komen
  • kies Plakken, Ctrl-V
  • op de juiste plaatsen een lege regel toevoegen, de kopjes vet maken en de onderliggende items laten inspringen

Zie het tabblad Bron in het Voorbeeldbestand.

Download

In het CBS-rapport wordt regelmatig verwezen naar onderliggende cijfers.

Een van de links (zie het tabblad Download in het Voorbeeldbestand) is naar de kerncijfers van de Arbeidsdeelname.

We gaan nu de gegevens niet kopiëren, maar maken een download.
Kies de button

  1. open de download door in de Verkenner in de Download-map op het bestand te dubbelklikken (het bestand heeft in dit geval de naam table__85264NED.csv).
    In de Chrome-browser komt de naam van de download ook onder in het scherm te staan; de download kun je dan openen door daar te klikken.
    NB wordt het bestand niet in Excel geopend dan moet je er voor zorgen dat de extensie CSV gekoppeld is aan Excel of de optie Openen met gebruiken
  1. alle gegevens komen in één kolom terecht. Die moeten we dus nog ‘knippen’.
  2. selecteer alle betreffende cellen in de kolom.
  3. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen.
  4. zorg dat in het vervolgscherm de optie Gescheiden is aangevinkt. Klik op de button Volgende.
  5. zorg dat Komma is aangevinkt en kies Voltooien

Alle gegevens, zoals ze in de tabel op het scherm stonden, worden in een overzichts-vorm aangeleverd.

Wat als we meer en andere gegevens er bij willen hebben?

Download 2

Door op de web-pagina de optie te klikken kom je vanzelf in het Statline-gebied van het CBS, waar je veel meer opties hebt om gegevens te kiezen.

Maar we gaan eerst eens kijken wat er gebeurt als je nu Download kiest:
Kies de optie CSV volgens tabindeling.

Open de download; het resultaat zal direct in Excel getoond worden (zie het tabblad Download2 in het Voorbeeldbestand):

Je kunt allerlei filteringen aan of uit zetten. Maar je kunt ook onderdelen verplaatsen.

Wanneer je bijvoorbeeld de button Geslacht met de muis ‘vastpakt’ kun je die verplaatsen naar het rijengebied:

Plaats Geslacht boven Onderwerp en zorg bij de filtering dat alle opties zijn aangevinkt

Kies weer de Download-button en open het bestand:

Na wat oefening zul je merken dat je op deze manier redelijk snel gewenste gegevens in Excel kunt overnemen. Een nadeel is dat er veel ‘handwerk’ bij komt kijken en dat de lay-out vaak niet direct geschikt is om verschillende analyses uit te voeren. En als de cijfers door het CBS worden geactualiseerd zullen alle handelingen opnieuw moeten worden gedaan!

In het volgende artikel zullen we dan ook het gebruik van de zogenaamde Open-Data-optie van het CBS en Excel bespreken.