Toolbox 008: De reis van pdf naar fusion tables

Elke week bespreken we in Toolbox een programma of techniek om datavraagstukken op te lossen of lekker verkennend bezig te zijn. Aangezien het de allerlaatste toolbox is voor het regiohack event, leek het mij nuttig om een proces door te lopen hoe je van onwerkbare data, werkbare data kan maken. Het artikel is dus een beschrijving van mijn werkwijze en ook de mislukte stappen, om inzicht te geven hoe je een dergelijk proces zou kunnen aanpakken.

Als startpunt nemen we dit forumtopic waarin @janborgert graag de data van de topsalarissen rijksoverheid in de Publieke sector omgezet zou willen hebben in bruikbare tabellen. We eindigen met een fusion table met bruikbare data en delen deze zodat de volgende geïnteresseerde niet dezelfde zoektocht hoeft te ondergaan.

Hoogste belastingbare inkomens in 2009 in de publieke sector:

Op de pagina waaruit van het forumtopic gelinkt is, zijn een aantal documenten. Ik pak document 1a waar alle overschreidingen in staan. Helaas is het document een pdf gevuld met tabellen, die niet makkelijk zijn om te zetten naar iets werkbaars. Overigens zijn de getallen ook nog eens gedraaid waardoor ze als mens ook niet heel leesbaar zijn.

Poging 1: Word

Als eerste poging pak ik het pdf bestand selecteer alles (ctrl+a) en kopieer alle data. Vervolgens plak ik de data in een Word bestand. Het resultaat is een lange brij met korte regels en getallen. Dit is niet de oplossing, maar gelukkig heeft Word de optie “tekst naar tabel converteren”. Deze optie staat onder invoegen->tabel->tekst naar tabel converteren. Wat deze optie probeert is door middel van bepaalde scheidingstekens op patronen in een tekst te herkennen hoe de originele tabel was opgebouwd. Helaas zijn er geen herkenbare patronen waardoor zelf na opschoning de tabel niet bruikbaar is. Op zoek naar een nieuwe oplossing.

Poging 2: Google Docs
Daarna ga ik aan de slag met Google Docs, Google docs bied een aantal opties om lokale bestand om te vormen in werkbare documenten. Als eerste is zijn er OCR opties om ingescande documenten automatisch om te vormen digitale tekst. Mocht je dit doen wees er extra alert op dat je data klopt, want bij het gebruik van OCR ontstaan snel fouten. Gelukkig is de tekst in dit document al selecteerbaar dus zou een simpelere pdf naar docs conversie, die automatisch plaats vindt tijdens het uploaden, genoeg moeten zijn. Helaas geeft deze aan dat er fouten ontstaan tijdens de conversie en loopt deze weg dood.

Poging 3: PDF2Excel

Bij het googlen van pdf table to excel vind ik een commercieel bedrijf dat een pdf2Excel convertor aanbiedt, ik ben zelf niet zo van de rare programma’tjes die je moet downloaden. Maar deze dienst is volledig online dus ik leverde mijn bestandje aan en geef een e-mail adres op dat ik voor mogelijke spam diensten gebruik. Na drie minuten ontving ik een keurig excel bestand.

De dienst is zelfs zo netjes dat verschillende pagina’s op verschillende worksheets staan. Dit is niet heel handig als we alles in één tabel willen hebben en er 150 werkbladen zijn. Terug naar Google om te zoeken op: Excel merge worksheets.

Poging 3.1: Merge worksheets
Een van de resultaten is een Yahoo answers pagina, met een macro die doet wat we willen. De code blijk niet helemaal bruikbaar voor ons document na een eerste proefrun, dus is het maar goed dat ik het probeer op een backup bestand. Ik analyseer de code of ik snap wat er mis gaat, ik kan zelf geen macro’s schrijven dus ik moet regel voor regel bedenken wat de code mogelijk zou kunnen doen.

Boven aan vind ik deze regel: Const sRANGE = “A1:Z100″

Deze zin lijkt de schuldige, de macro pakt van elk werkblad cell A1 tot Z100. In onze werkbladen is de 1ste regel bedoeld voor de labels van de data en willen we die niet elke keer terug zien. Dus we veranderen “A1″ in “A2″ en runnen het script nogmaals.

Als het script klaar is controleren we of het compleet is door de ID nummers in het document naast de rijnummers in Excel te leggen en blijkt het gelukt te zijn. Voor het gemakt het ik de definitieve macro online geplaatst, zodat je deze snel kan overnemen.

We zijn er alleen nog niet, de cellen met teksten erin zijn gevuld met dubbele spaties verborgen enters en allerlei andere zaken die ons in de weg gaan zitten als we straks echt me de data aan de slag gaan. Ook zijn sommige plaatsnamen volledig in HOOFDLETTERS terwijl andere dit niet zijn. In Google Refine kunnen we dit soort slordigheden er snel uithalen.

Poging 3.2: Refine voor het opschonen van tekst

Google Refine heb ik eerder uitgebreider besproken, dit keer is ons bezoekje veel korter. Onder Common transformations staat namelijk alles wat we nodig hebben. “Trim lading and trailing whitespaces” haalt alle spaties weg die voor of achter een tekst staan. “Collapse consecutive whitespace” haalt dubbele spaties weg en “To titlecase” maakt de eerste letter een hoofdletter en de rest klein. Na deze stappen uit te voeren op de juiste kolommen zijn we klaar en kan het geheel naar fusion tables.

Deze beschrijving van hoe ik te werk ben gegaan werkt waarschijnlijk alleen op dit document. Maar hopelijk merk je door dit artikel dat je veel verschillende ingrepen kan en moet doen om data om te vormen tot iets waar mee je aan de slag kan. Laat je niet uit het veld slaan als de eerste drie pogingen niet werken en vraag rond bij anderen hoe zij het zouden aanpakken. Op deze manier vorm je langzaam een set met opties voor verschillende problemen en kan je handmatig overtypen van gegevens zoveel mogelijk vermijden.

Over Heinze Havinga

Schrijft voor regiohack over tools en technieken en is altijd lastig te vallen met vragen over data verzamelen, ordenen en visualiseren. In het dagelijkse leven zelfstandige ondernemer op het gebied van webmashups, interactieve installaties en creatieve vraagstukken. Op twitter te vinden onder @HeinzeHavinga
Dit bericht is geplaatst in Toolbox en getagd, , , . Bookmark de permalink.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *

De volgende HTML tags en attributen zijn toegestaan: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>