Mijn regels voor het gebruik van spreadsheets

De aantrekkingskracht van spreadsheets

Laten we beginnen met wat spreadsheets zo aantrekkelijk maakt. Direct bij de start krijg je een raster van cellen die fungeren als datacontainers. Je hoeft deze containers niet te definiëren, ze geen naam te geven of ze te initialiseren; ze zijn er gewoon en wachten tot je ze naar eigen inzicht vult.

Wanneer het tijd is om bewerkingen op deze data uit te voeren, hoef je de cellen nog steeds niet te benoemen. Je klikt (of sleept) simpelweg om de argumenten van een functie in te vullen, waarna de spreadsheet-app de juiste rij/kolom-referentie invult. Als je een herinnering wilt aan waar een cel voor dient, kun je een naam of beschrijving in een aangrenzende cel typen. Ook hoef je niet na te denken over de juiste volgorde van de bewerkingen; de app berekent de afhankelijkheidsketen van de cellen en herberekent alles, overal, tegelijkertijd.

Omdat je bovendien de grootte, kleur, rand en het lettertype van elke cel kunt instellen, kan een spreadsheet mooie tabellen genereren die direct in rapporten, memo's en presentaties kunnen worden geplaatst. Een spreadsheet is dus tegelijkertijd een datastore, een logische machine en een presentatietool.

Waarom ik de regel 'Niet doen' hanteer

Als spreadsheets zo compleet zijn, waar komt mijn "Niet doen"-regel dan vandaan? Er zijn veel redenen, maar ik ben sterk beïnvloed door de laatste 10 tot 15 jaar van mijn loopbaan. In die periode moest ik tientallen datasets analyseren die mij als Excel-spreadsheets werden toegestuurd. De ingenieursbureaus die deze bestanden stuurden, gebruikten ze niet alleen als datastore; ze voegden hun eigen analyses toe (die vaak overlapten met de mijne) en formateerden de spreadsheets als tabellen voor hun eigen rapporten.

Dit maakte mijn werk om verschillende redenen moeilijker:

  1. Ondoorzichtige formules: Omdat ik wilde controleren of ik het eens was met hun analyse, moest ik alle formules beoordelen. Sommige waren complex; geneste IF-statements zijn in een traditionele programmeertaal makkelijk te volgen, maar in een spreadsheet zijn ze een chaos. Andere waren inconsistent: verschillende rijen in dezelfde tabel hadden verschillende formules, alsof ze door verschillende mensen op verschillende momenten waren geschreven of waren overgenomen uit een spreadsheet van een vorig project. Sommige formules verwezen naar cellen die ver weg lagen, waardoor er veel gescrold moest worden om ze te vinden. Geen enkele formule in meer dan tien jaar tijd maakte gebruik van celnamen om de leesbaarheid te vergroten.
  2. Fouten en discrepanties: De bovengenoemde complexe formules bevatten soms fouten. Soms klopten de formules wel, maar waren de beschrijvingen in de kopcellen onjuist. Dit betekende dat er telefoongesprekken nodig waren om de verschillen op te lossen, wat de analyse verder vertraagde.
  3. Versnipperde data: Het kwam vaak voor dat data over twee of meer tabbladen was verdeeld. Ik vermoed dat dit werd gedaan om de tabellen beter in de rapporten van de ingenieurs te laten passen. Voor hun doel was dat prima, maar voor mij niet; ik moest de data opnieuw combineren voor mijn analyses. Bovendien hadden de bladen vaak ingewikkelde koppen van meerdere regels, waardoor ik ze niet simpelweg als CSV-bestanden kon exporteren.
  4. Gebrek aan standaardisatie: Elke ingenieur met wie ik werkte, bouwde zijn spreadsheets op een andere manier. Zelfs binnen hetzelfde bureau werd er niet vastgehouden aan een huisstijl. Individuele ingenieurs veranderden hun styling zelfs per project. In feite was elke spreadsheet die binnenkwam sui generis, waardoor ik alle dataopschoning handmatig moest doen. Dit vertraagde me, niet alleen omdat ik geen automatisering kon gebruiken, maar ook omdat ik mijn werk dubbel en driedubbel moest controleren om kopieer- en plakfouten te voorkomen.

Deze ervaringen hebben mij sceptisch gemaakt over het gebruik van spreadsheets voor alles wat groot of complex is. De ingenieurs waren slim, maar hun spreadsheets niet. Mijn conclusie was dat de eenvoud van de typische "klik-en-sleep"-methode slechte organisatie en fouten in de hand werkt naarmate spreadsheets groeien of worden aangepast aan nieuwe data. Het is makkelijk om te zeggen: "Oh, dat zou ik nooit doen," maar ik ben oud genoeg om te weten dat ik het wél zou doen. De eenvoud waarmee men vandaag de dag spreadsheets bouwt, zie ik als een sirenezang die me rechtstreeks op de rotsen zal leiden.

(Terzijde over het Reinhart/Rogoff-onderzoek: dit is het schoolvoorbeeld van elementaire spreadsheetfouten—fouten die twee Harvard-professoren zeker nooit zouden maken—en het leidde tot veel lijden door onnodige overheidsbezuinigingen. En als je nu wilt schrijven dat de fouten van Reinhart en Rogoff de essentiële waarheid van hun conclusies niet ontkrachten: laat dat maar zitten.)

Een ander probleem is dat het gemak van het combineren van data en analyselogica in één document een probleem wordt zodra je die logica moet toepassen op verschillende datasets, vooral als deze verschillen in grootte. Spreadsheet-templates zijn geweldig als de data toelaten dat je meerdere spreadsheets met exact dezelfde lay-out maakt, maar de data waar ik mee werk passen zelden in zo'n rigide patroon. Als ik bijvoorbeeld analyses en plots van tijdreeksen maak, hebben die reeksen zelden dezelfde tijdsduur of hetzelfde aantal datapunten. Het is veel eenvoudiger om met deze verschillen om te gaan wanneer de logica in een programma staat, gescheiden van de data.

Beperkingen in datavolume: Een casestudy over baby-namen

Een ander probleem is dat de hoeveelheid data die een spreadsheet kan bevatten beperkter is dan bij andere workflows voor data-analyse. Hoewel de limieten groot zijn, is "behoorlijk groot" in het tijdperk van Big Data soms niet genoeg. In haar Macstock-presentatie laat Allison zien hoe ze tegen dit probleem aanliep met een dataset van Amerikaanse babynamen.

Een van de manieren om deze dataset te downloaden is als een gecomprimeerde collectie CSV-bestanden. Elk bestand is gekoppeld aan één jaar (bijv. yob1960.txt). De inhoud ziet er als volgt uit:

Mary,F,51472
Susan,F,39208
Linda,F,37316
Karen,F,36378
Donna,F,34138
[etc]

Hierbij is het eerste item de naam, het tweede het geslacht bij geboorte en het derde het aantal baby's met die naam in dat jaar. De regels zijn gesorteerd op geslacht en daarna op aantal. Als je alle bestanden samenvoegt, kom je uit op 2.181.032 vermeldingen. Zoals Allison ontdekte, past dit niet in een Excel-spreadsheet, aangezien Excel beperkt is tot 1.048.576 rijen (het computergetal $2^{20}$ of $1024^2$). De limiet in Numbers is het minder "computery" maar meer menselijke getal van 1.000.000 rijen.

Allison loste dit op door te "valsspelen": ze verwijderde de minder populaire namen om de lijst in Excel te laten passen, waarna ze কিছু pivot table-functies demonstreerde.

Ik besloot iets soortgelijks te doen, maar zonder het valsspelen. Eerst voegde ik alle individuele bestanden samen tot één groot CSV-bestand inclusief een veld voor het jaar via deze shell-commando's:

echo 'Year,Name,Sex,Count' > all-years.csv
for f in yob*.txt; do
y=${f:3:4}
sed -e "s/\r$//;s/^/$y,/" $f >> all-years.csv
done

Het jaar wordt geëxtraheerd uit de bestandsnaam via substring expansion en vervolgens aan het begin van elke regel toegevoegd via sed. De originele bestanden zijn in Windows-formaat met CRLF-regelafbrekingen, dus verwijdert het sed-commando ook de CR-tekens. Het resultaat is een bestand genaamd all-years.csv dat er zo uitziet:

Year,Name,Sex,Count
1880,Mary,F,7065
1880,Anna,F,2604
1880,Emma,F,2003
1880,Elizabeth,F,1939
1880,Minnie,F,1746
[etc]

(Opmerking: hoewel de dataset afkomstig zou zijn van Social Security-registraties, begint deze in 1880, decennia vóór de Social Security Act. Dat kan ik niet verklaren. Ook kan ik niet verklaren hoe Minnie ooit de vijfde populairste meisjesnaam was.)

Ik gebruik Python en Pandas om de vijf populairste meisjesnamen van 2001 tot en met 2025 te extraheren. Hier is het begin van een interactieve Python-sessie:

>>> import pandas as pd
>>> df = pd.read_csv('all-years.csv')
>>> cols = ['Name', 'Count']

Dit leest het CSV-bestand in een dataframe en definieert de kolommen die we in onze output willen. Zo krijgen we de lijst met namen:

>>> df[(df.Sex=='F') & (df.Year>2000)][cols].groupby('Name')\
... .sum().sort_values('Count', ascending=False)[:5]
Count
Name
Emma      449576
Olivia    423613
Isabella  381577
Sophia    368619
Emily     353077

Als we dit willen vergelijken met de populaire meisjesnamen van een eeuw eerder, is het commando zeer vergelijkbaar:

>>> df[(df.Sex=='F') & (df.Year>1900) & (df.Year<=1925)][cols].groupby('Name')\
... .sum().sort_values('Count', ascending=False)[:5]
Count
Name
Mary      1056333
Helen      505522
Dorothy    475151
Margaret   402317
Ruth       364923

Voor wie bekend is met databases, is de Pandas groupby-functie een kopie van het SQL GROUP BY-construct. Laten we dit opnieuw doen in een interactieve sessie met SQLite:

sqlite> .mode csv
sqlite> .import all-years.csv names
sqlite> .mode columns

Nu halen we de top vijf meisjesnamen uit de 21e eeuw op in aflopende volgorde:

sqlite> select Name, sum(Count) from names
...> where Sex is "F" and Year > 2000
...> group by Name order by sum(Count) desc limit 5;
Name      sum(Count)
--------  ----------
Emma      449576
Olivia    423613
Isabella  381577
Sophia    368619
Emily     353077

SQL is zeker meer "Engels-achtig", maar de parallellen met Pandas zijn duidelijk. Voor het begin van de 20e eeuw:

sqlite> select Name, sum(Count) from names
...> where Sex is "F" and Year > 1900 and Year <= 1925
...> group by Name order by sum(Count) desc limit 5;
Name      sum(Count)
--------  ----------
Mary      1056333
Helen     505522
Dorothy   475151
Margaret  402317
Ruth      364923

Allison doet soortgelijke dingen met haar ingekorte Excel-bestand via draaitabellen (pivot tables). Ik haat de term "pivot table", omdat ik denk dat het een obscure term is voor de simpele bewerkingen van groeperen en samenvatten. Om welke reden dan ook maakt mijn mening hier niet uit; pivot tables zijn hier om te blijven. Pandas heeft zelfs een pivottable-functie toegevoegd om mensen die overstappen van Excel tegemoet te komen. Onder de motorkap roept pivottable simpelweg groupby aan.

De uitzonderingen

Ik sta uitzonderingen toe. Mijn twee belangrijkste uitzonderingen zijn:

  1. Kleine, eenvoudige problemen: Wanneer het probleem klein genoeg is om op het scherm te zien zonder bijna geen scrolling, en de bewerkingen simpel genoeg zijn om te begrijpen zonder komma's en haakjes te hoeven tellen. Dat was wat ik deed voor mijn differentietabellen van de som van machten. De formules bestonden hoofdzakelijk uit aftrekkingen, met hier en daar wat machts- en delingsoperaties. Alleen het oplossen van de simultane vergelijkingen rechtsonder vereiste daadwerkelijke functieaanroepen, en er waren geen geneste aanroepen.
  2. Tussenstations voor data-editing: Wanneer ik de spreadsheet gebruik als een tijdelijk station om data te bewerken voordat ik deze doorstuur. Dit deed ik bij een post over de voortgang van honkbalteams om de grote en onhandige seizoensresultaten van Baseball Reference in te korten. Het was snel en gemakkelijk om de tabel in Safari te selecteren, in Numbers te plakken en vervolgens de kolommen en rijen die ik niet nodig had te verwijderen. Ik deed dit echter alleen omdat dit een eenmalig project was. Als ik de taak zou krijgen om elke dag van het seizoen voortgangskaarten voor alle 30 teams te maken, zou ik dat nooit handmatig doen. Dan zou ik de Pandas read_html-functie gebruiken om de HTML-tabel in een dataframe te laden en diverse drop-commando's gebruiken om deze in te korten.

Vroeger gebruikte ik spreadsheets ook voor data-invoer, maar niet meer. Dat was ooit de enige betrouwbare manier om een tabel met getallen uit een boek in elektronische vorm te krijgen. Maar OCR is zo veel beter geworden dat ik me niet eens meer kan herinneren wanneer ik dit voor het laatst heb gedaan.

Ik weet dat er veel mensen zijn die graag spreadsheets gebruiken en veel tijd hebben gestoken in het leren van alle fijne kneepjes. Dat is prima. Deze post gaat over mijn regels, niet over die van anderen. Ik zeg niet dat er geen goed, accuraat en complex werk in spreadsheets kan worden verricht. Het zal alleen niet door mij worden gedaan.