Een verrijkte dataset maken als basis voor een data-analyse

Data verrijken zonder te programmeren met Google Sheets

Met een gemiddeld Nederlands salaris kun je als starter op de woningmarkt een hypotheek van zo’n 162.000 euro krijgen, becijferde RTL Nieuws. Dat betekent dat je in Zwolle bijvoorbeeld keuze hebt uit slechts een paar huizen. Dat is leuk, maar een visualisatie van dat feit is veel sprekender. Dan zie je in één oogopslag ook in welke wijk(en) je kans maakt op een woning. Onderstaand plaatje illustreert dat.

Wil je zo’n visualisatie maken, dan heb je eerst de data nodig in een werkbaar formaat. Woningprijzen, en coördinaten van de locaties van die woningen zodat je het op een kaart kunt tekenen. In dit artikel leg uit hoe je dat doet, door gebruik te maken van Google Spreadsheets. Hierbij doorlopen we de volgende stappen:

  • Het verzamelen van de data
  • Het opschonen en filteren van de data in Google Sheets
  • De data inladen en verrijken met externe data via een webservice (API)

Daarmee laat ik je zien dat je zelfs zonder programmeerkennis een eigen dataset kunt maken, die verrijkt is met een externe bron. In dit voorbeeld verrijken we een postcode met de coördinaten die bij de postcode horen, op basis van OpenStreetmap-gegevens.

Het verzamelen van de data

De woningen die te koop staan in Nederland zijn helaas niet in een gestructureerd formaat beschikbaar. Daarom ben ik begonnen met wat handwerk: het kopiëren van twintig pagina’s aan Funda-resultaten naar een lege Google Sheet-spreadsheet. Als je de gegevens plakt kies je voor het opslaan van alleen de waarden, en niet de bijbehorende opmaak. Dat geeft dan een resultaat zoals je hieronder ziet.

Het opschonen en filteren van de data in Google Sheets

De data is nu niet heel bruikbaar, gezien het feit dat ongeveer één op de vijf regels informatie bevat die we willen hebben. Doel is deze ongestructureerde lijst te transformeren naar een sheet waarin je een combinatie ziet van een postcode en de vraagprijs van een woning. Met een beetje handigheid in Google Sheets (In Excel werkt het ongeveer hetzelfde) is dat zo gedaan.

Zoals je ziet staan de adresgegevens in één cel, en is de postcode aan het huisnummer vastgeplakt. De vraagprijs staat altijd in de cel eronder. Om de gegevens te filteren heb ik een klein if-statement gemaakt, waarin er een waarde komt te staan (de waarde uit de linkerkolom) áls het woord Zwolle in de cel staat. Dat doe je met de methode REGEXMATCH, waarmee je kunt zoeken naar een bepaald stukje tekst in cel. Dit werkt voor dit doel, omdat 'Zwolle' voornamelijk wordt genoemd in de cellen waarin ook de postcode staat.

Vervolgens zet ik in de kolom C3 de waarde van A4 áls B3 een waarde heeft. Dat gaat met een soortgelijke regel als je in het plaatje ziet, namelijk met:

=IF(REGEXMATCH(B3,"Zwolle"),A4,"")

Als je de kolommen B en C helemaal naar beneden doortrekt met die formule (door in de hoek rechtsonder van de cel te klikken, en dat hoekje naar beneden te slepen), dan krijg je steeds regels gevuld met een adres en met een verkoopprijs.

De kolommen B en C kopieer je vervolgens naar een nieuwe sheet. Als je die filtert van Z naar A, dan zijn alle lege rijen eruit gefilterd. Je bent er nog niet, want zoals je hieronder ziet is het wel íets gestructureerder, maar nog niet goed genoeg. We hebben namelijk alleen de postcode nodig, zodat we die aan via een webservice van OpenStreetmap kunnen gebruiken om de bijbehorende coördinaten te vinden.

Gelukkig is het adres-formaat wel consequent: als je vanaf rechts telt bevatten 14 karakters altijd de postcode en de plaatsnaam. Zwolle (6 letters), EL (2 letters) en 8032 (4 getallen) met twee spaties maakt veertien karakters. Dus met de methode =RIGHT(A1,14) kun je de postcode-woonplaatscombinatie in een losse cel zetten. Met eenzelfde soort constructie, zoals je in de formulebalk hieronder ziet, kun je de vraagprijs naar een echt getal (dus zonder k.k.) omzetten. Dat zie je in de afbeelding hieronder.

In kolom C zie je dat 'Zwolle' nog wel overal staat. Om alleen de postcode over te houden maken we nog een nieuwe kolom, waarin je met '=LEFT(C1,7)' de eerste 7 karakters, de postcode dus, in een losse cel krijgt.

Verrijken van de data met een externe bron

We zijn nu op het punt aangekomen dat je een nette dataset hebt. De postcodes en woningprijzen zijn gefilterd uit de lange lijst met ongestructureerde data. De volgende stap is het bepalen van de coördinaten die bij de postcode horen.

Er is een webservice van OpenStreetmap, genaamd ‘Nominatim’, die op basis van een zoekopdracht geo-resultaten kan teruggeven. Stuur je er een postcode heen, dan krijg je bijvoorbeeld de straatnaam en de coördinaten als resultaat.

https://nominatim.openstreetmap.org/search?postalcode=8032EL&format=xml

Het is een gestructureerd (XML) formaat. Dat betekent dat het makkelijk te interpreteren is voor een computer. En omdat het in XML-formaat is, betekent het ook dat we dit resultaat automatisch kunnen inladen in Google Sheets. Je kan namelijk met de ‘ImportXML’-methode een XML-bestand importeren, en één veld eruit plaatsen in een cel. Bijvoorbeeld het geel gemarkeerde ‘lat’ of ‘lon’ veld. Dit zijn ook de twee velden die we nodig hebben: het zijn de coördinaten die bij de postcode horen.

Allereerst moeten we een kolom maken waarbij die link naar de webservice wordt samengesteld voor iedere postcode. Dat kan in een losse kolom, zoals je ziet op de afbeelding hieronder.

De laatste stap is dan het aanroepen van deze link, met de importXML-functie. Dat is niet zo ingewikkeld als het klinkt, en kan met de methode: =IMPORTXML(G1, "//place/@lat"). Als eerste parameter geef je een referentie aan naar de cel met de link, en de tweede parameter is een specificatie van welk specifiek veld je wilt ophalen. Het betekent eigenlijk ‘ik wil de eigenschap ‘lat’ hebben van het element ’place’. Als je kijkt naar het plaatje met de inhoud van het webservice-resultaat hierboven, dan zul je begrijpen wat ik bedoel.

Dit kan je toepassen voor iedere regel in je spreadsheet. Zo krijg je volautomatisch de coördinaten die bij een postcode horen. Uiteraard moet je het twee keer doen, één keer voor de lengtegraad en één keer voor de breedtegraad.

Gefeliciteerd, je hebt een gestructureerde én verrijkte dataset!

Je hebt nu een gestructureerde dataset gemaakt, op basis van ongestructureerde gegevens (namelijk 20 pagina’s gekopieerde Funda-teksten) en die ook nog eens verrijkt op basis van een externe bron.

Deze gegevens kun je vervolgens gebruiken om een visualisatie te maken. Voor het plaatje helemaal bovenaan het artikel heb ik bijvoorbeeld LeafletJS gebruikt, waarmee je met Javascript pinnetjes kunt tekenen op een OpenStreetmap-kaart. Dat gaat iets verder dan de strekking van dit artikel, omdat er ook wat programmeerwerk bij komt kijken.

Denk je bij het lezen van dit artikel: interessant, maar voor mij gaat dit wat te ver? Ik help jouw organisatie graag met data-vraagstukken. Dan hoef je het niet zelf te doen, maar kun je wel profiteren van de waarde die data voor je organisatie kan opleveren. Neem gerust eens contact op, je vindt mijn gegevens op deze pagina.