Tips en Trucs 2019

Dataconversie

Sommige diensten maken er een sport van om data steeds opnieuw in een ander formaat aan te bieden. Zo kan je op de website van de Nationale Loterij van België, na elke trekking van de Lotto de volledige historiek van alle trekkingen downloaden. Handig om automatisch te controleren of je gewonnen hebt. Of om op deze gegevens statistische functies los te laten om uw winstkansen te verhogen!! Gegevens verwerken kan via een programma of scripts, maar dan moet je de gegevens steeds op dezelfde manier aanbieden. Zo werden de resultaten van de Lotto trekkingen al in verschillende CSV formaten (comma-separated values) en onlangs zelfs in het XLSX formaat (Microsoft Excel) aangeboden. Gelukkig hebben Linux gebruikers verschillende hulpmiddelen om bijna elk formaat naar hun hand te zetten.

Je kunt de Lotto Trekkingsresultaten downloaden op de website van De Nationale Loterij.

Van het Microsoft Excel formaat (XLSX) naar het CSV formaat

LibreOffice kan XLSX spreadsheets importeren en daarna opslaan als CSV bestand. Om een conversie uit te voeren, moet je LibreOffice niet eens volledig opstarten. Je kunt namelijk met een terminalopdracht LibreOffice alle in een map aanwezige XLSX bestanden laten omzetten naar CSV bestanden:

dany@pindabook:~> xxd -l 112 Downloads/LottoGameData-20190126.xlsx
00000000: 504b 0304 1400 0000 0800 fc7a 3a4e 9989  PK.........z:N..
00000010: d227 b800 0000 2301 0000 0f00 1c00 786c  .'....#.......xl
00000020: 2f77 6f72 6b62 6f6f 6b2e 786d 6c20 a218  /workbook.xml ..
00000030: 0028 a014 0000 0000 0000 0000 0000 0000  .(..............
00000040: 0000 0000 0000 0000 008d 8f4d 0e82 3010  ...........M..0.
00000050: 46af d2cc 5e0a 460d 12c0 8d1b b7de a096  F...^.F.........
00000060: a934 d20e e914 e5f8 5634 ba75 356f 7e32  .4......V4.u5o~2
dany@pindabook:~> libreoffice --headless --convert-to csv ~/Downloads/LottoGameData-*.xlsx
convert /home/dany/Downloads/LottoGameData-20190126.xlsx -> /home/dany/LottoGameData-20190126.csv using filter : Text - txt - csv (StarCalc)
dany@pindabook:~> xxd -l 112 LottoGameData-20190126.csv 
00000000: 4461 7475 6d2c 4e75 6d6d 6572 2031 2c4e  Datum,Nummer 1,N
00000010: 756d 6d65 7220 322c 4e75 6d6d 6572 2033  ummer 2,Nummer 3
00000020: 2c4e 756d 6d65 7220 342c 4e75 6d6d 6572  ,Nummer 4,Nummer
00000030: 2035 2c4e 756d 6d65 7220 362c 426f 6e75   5,Nummer 6,Bonu
00000040: 730a 3233 2d30 312d 3230 3139 2032 303a  s.23-01-2019 20:
00000050: 3030 3a30 302c 332c 372c 3133 2c31 382c  00:00,3,7,13,18,
00000060: 3239 2c33 382c 3332 0a31 392d 3031 2d32  29,38,32.19-01-2

De libreoffice opdracht converteert het XLSX bestand naar een CSV bestand. Daarbij zorgt de --headless optie ervoor dat LibreOffice niet volledig start, maar enkel voor het uitvoeren van de omzetting en daarna direct terug afsluit. De --convert-to optie bepaalt naar welk formaat de omzetting moet gebeuren (hier csv). En daarna welke bestanden, namelijk alle LottoGameData bestanden die eindigen op .xlsx. De xxd opdrachten geven de eerste 112 bytes van het XLSX bestand en het CSV bestand weer. Daaruit blijkt dat XLSX bestanden binaire bestanden zijn (met veel niet weergeefbare tekens, weergegeven door punten) en CSV bestanden tekstbestanden zijn die je eenvoudig met scripts kunt verwerken en manipuleren.

De gegevens een vaste bestandsnaam geven

Scripts worden eenvoudiger als de te verwerken gegevens steeds in hetzelfde bestand staan. Een leuke bijwerking daarvan is dat je enkel de laatste gegevens op uw computer bewaard.

dany@pindabook:~> rm ~/Downloads/LottoGameData-*.xlsx
dany@pindabook:~> mv ~/LottoGameData-*.csv ~/Downloads/LottoGameData.csv

De eerste opdracht verwijdert het originele gedownloade XLSX bestand. De tweede opdracht geeft het geconverteerde CSV bestand een vaste naam (LottoGameData.csv) en verplaats deze naar de Downloads map. De Lotto trekkingsgegevens werden vroeger namelijk in het CSV formaat gedownload.

Test

Mocht je de trekkingsgegevens nog niet gedownload hebben, dan mislukken de conversie en de verplaats opdracht en moet het script met een melding afgebroken worden.

dany@pindabook:~> if [ ! -f ~/Downloads/LottoGameData.csv ]; then
>   echo "Download de LottoGameData"
>   exit
> fi

Om bovenstaande test bij een volgende uitvoer met pas gedownloade gegevens uit te voeren, verplaatsen we het CSV bestand naar de verwerkingsmap (hier de Persoonlijke map).

dany@pindabook:~> mv ~/Downloads/LottoGameData.csv ~/LottoGameData.csv.test

Gegevens chronologisch rangschikken

dany@pindabook:~> head LottoGameData.csv.test 
Datum,Nummer 1,Nummer 2,Nummer 3,Nummer 4,Nummer 5,Nummer 6,Bonus
23-01-2019 20:00:00,3,7,13,18,29,38,32
19-01-2019 20:00:00,13,15,16,24,25,33,19
16-01-2019 20:00:00,4,16,33,37,42,44,9
12-01-2019 20:00:00,24,29,33,42,43,45,12
09-01-2019 20:00:00,3,7,12,19,31,32,15
05-01-2019 20:00:00,9,15,22,24,38,44,16
02-01-2019 20:00:00,7,15,21,28,36,38,44
29-12-2018 20:00:00,4,6,12,13,21,41,27
26-12-2018 20:00:00,14,23,29,42,43,44,22

De head opdracht leert ons dat de eerste regel de kolomnamen van de trekkingsgegevens bevat. Daarna volgende de eigenlijke trekkingsgegevens. De laatste trekking staat helemaal bovenaan, wat voor mijn statistische analyse niet geschikt is. Voor mijn statische analyse moet de eerste trekking bovenaan staan, en de laatste trekking helemaal onderaan.

dany@pindabook:~> head -1 ~/LottoGameData.csv.test > ~/lotto.resultaten
dany@pindabook:~> tac ~/LottoGameData.csv.test | head -n -1 | sed ':a;N;$!ba;s/;\r//g' >> ~/lotto.resultaten
dany@pindabook:~> head lotto.resultaten 
Datum,Nummer 1,Nummer 2,Nummer 3,Nummer 4,Nummer 5,Nummer 6,Bonus
04-02-1978 01:00:00,6,17,30,31,35,37,15
11-02-1978 01:00:00,1,5,8,17,23,28,14
18-02-1978 01:00:00,3,5,6,8,17,18,10
25-02-1978 01:00:00,2,6,8,17,28,35,16
04-03-1978 01:00:00,8,10,18,25,32,33,34
11-03-1978 01:00:00,3,5,17,23,25,31,38
18-03-1978 01:00:00,5,9,10,24,28,31,6
25-03-1978 01:00:00,2,5,10,18,22,32,30
01-04-1978 02:00:00,3,9,17,21,28,39,4

De head opdracht plaats de eerste regel met de kolomnamen in het bestand lotto.resultaten. De tac opdracht leest het bestand achterste voren in en heeft de regels dus in omgekeerde volgorde door aan de head opdracht. Deze head opdracht negeert de laatste regel met de kolomnamen en geeft alle andere regels door aan de sed opdracht. De sed opdracht verwijdert alle aanwezige return tekens. Return tekens worden samen met newline tekens gebruikt in Windows tekstbestanden, Linux tekst bestanden gebruiken alleen een newline teken op het einde van elke regel. De chronologisch omgekeerde en opgekuiste gegevens worden uiteindelijk toegevoegd aan het bestand lotto.resultaten.

Tekens vervangen door andere tekens

Datumonderdelen worden nu gescheiden door - tekens, vroeger echter door / tekens.

dany@pindabook:~> sed -i 's/-/\//g' ~/lotto.resultaten
dany@pindabook:~> head lotto.resultaten 
Datum,Nummer 1,Nummer 2,Nummer 3,Nummer 4,Nummer 5,Nummer 6,Bonus
04/02/1978 01:00:00,6,17,30,31,35,37,15
11/02/1978 01:00:00,1,5,8,17,23,28,14
18/02/1978 01:00:00,3,5,6,8,17,18,10
25/02/1978 01:00:00,2,6,8,17,28,35,16
04/03/1978 01:00:00,8,10,18,25,32,33,34
11/03/1978 01:00:00,3,5,17,23,25,31,38
18/03/1978 01:00:00,5,9,10,24,28,31,6
25/03/1978 01:00:00,2,5,10,18,22,32,30
01/04/1978 02:00:00,3,9,17,21,28,39,4

Ook hier zorgt een sed opdracht dat alle - tekens door / tekens worden vervangen.

Van DOS CRLF naar UNIX LF

Een alternatieve manier om de overtollige en storende return tekens uit een Windows tekstbestand te filteren:

dany@pindabook:~> tr -d '\r' < ~/lotto.resultaten > ~/lotto.resultaten.unix.time

De tr opdracht krijgt de gegevens van het bestand lotto.resultaten, filtert de return tekens en slaat de gefilterde tekst op in het bestand lotto.resultaten.unix.time.

Tijden wegknippen

Vroeger bevatte de trekkingsresultaten geen tijden. Deze zijn ook irrelevant.

dany@pindabook:~> cut -c 1-10,20- ~/lotto.resultaten.unix.time > ~/lotto.resultaten.unix
dany@pindabook:~> head lotto.resultaten.unix
Datum,Nummer 2,Nummer 3,Nummer 4,Nummer 5,Nummer 6,Bonus
04/02/1978,6,17,30,31,35,37,15
11/02/1978,1,5,8,17,23,28,14
18/02/1978,3,5,6,8,17,18,10
25/02/1978,2,6,8,17,28,35,16
04/03/1978,8,10,18,25,32,33,34
11/03/1978,3,5,17,23,25,31,38
18/03/1978,5,9,10,24,28,31,6
25/03/1978,2,5,10,18,22,32,30
01/04/1978,3,9,17,21,28,39,4

De cut opdracht behoud van elke regel enkel de eerste 10 tekens (1-10) en de tekens 20 en verder (20-), de tekens tussen positie 11 en 19 worden dus verwijderd. En op deze positie staan de datums.

Comma Separated Values

CSV bestanden bevatten zoals de naam doet vermoeden door komma's gescheiden waarden. CVS bestanden kunnen echter ook door gebruikers vastgelegde scheidingstekens gebruiken. Zoals ; indien de waarden zelf komma's bevatten. Het eerste gegevensbestand met trekkingsgegevens van de Lotto gebruikte ; tekens tussen de waarden.

dany@pindabook:~> sed -i 's/,/;/g' ~/lotto.resultaten.unix
dany@pindabook:~> head lotto.resultaten.unix
Datum;Nummer 2;Nummer 3;Nummer 4;Nummer 5;Nummer 6;Bonus
04/02/1978;6;17;30;31;35;37;15
11/02/1978;1;5;8;17;23;28;14
18/02/1978;3;5;6;8;17;18;10
25/02/1978;2;6;8;17;28;35;16
04/03/1978;8;10;18;25;32;33;34
11/03/1978;3;5;17;23;25;31;38
18/03/1978;5;9;10;24;28;31;6
25/03/1978;2;5;10;18;22;32;30
01/04/1978;3;9;17;21;28;39;4

Weerom gebruiken we een sed opdracht om alle , tekens door ; tekens te vervangen.

Nu is mijn gegevensbestand klaar om verder door het oorspronkelijke script verder geanalyseerd te worden.