Python gebruiken om spreadsheets in data science aan te sturen

Excel, mijn oude vriend

Ik ben het type persoon dat data in spreadsheets bewerkt gewoon omdat het kan. Vorige week stuurde mijn teamlead een lijst rond met medewerkers die hun project-checklists niet hadden bijgewerkt, en ik opende ’m meteen, begon tabellen te maken en samenvattende statistieken per medewerker te draaien, en pingde hem vervolgens wat overzichtsdata, uitgesplitst per medewerker. Het maakte hem waarschijnlijk een beetje gek, maar ik kan het niet laten; ik hou van data-analyse en ik hou van Excel. Ik gebruik Excel sinds 2001 en tot een paar maanden geleden was het mijn favoriete tool voor data-organisatie, analyse en visualisatie. Het is geweldig om data te ordenen, berekeningen en analyses uit te voeren en werkt zelfs als een eenvoudige database. Ik heb het voor alles gebruikt: van het bijhouden van mijn persoonlijke budget, uitgaven en workouts tot meerbladige rapporten over personeelssterkte in het leger. Over het algemeen werkte Excel daar goed en het was ook de enige optie. Om veiligheidsredenen had ik geen toegang tot VBA, Python of enige andere vorm van automatisering. De datasets waren ook klein genoeg om te verwerken.

Bijna een jaar geleden begon ik in het Abilities Lab van Cerner als systeemingenieur in softwareperformancetesten. De korte versie van wat ik doe is softwareperformancetesten. We testen nieuwe en bijgewerkte software voordat die naar onze klanten gaat en we testen alles: Windows- en Unix-CPU en -geheugen, Java Virtual Machine-prestaties, responstijden van zowel backendservices als de gebruikersinterface, databaseprestaties, de impact op onze huidige software, netwerkprestaties en zo’n beetje alles wat je kunt bedenken. Geen enkele test is beperkt tot één machine; meestal is het een combinatie van Windows- en Unix-apparaten (fysiek en virtueel), eindgebruikerssystemen en servers. Voor een van de projecten waar ik bij betrokken was, hadden we meer dan 20 systemen waar data van moest worden verzameld.

Afhankelijk van hoe lang een project loopt, kunnen er meer dan 50 tests zijn die moeten worden beoordeeld, geanalyseerd en gerapporteerd. Al die data moet worden samengevat in een eindrapport met testresultaten dat op een interne Jira-projectpagina wordt geplaatst voor het softwareontwikkelingsteam. We hebben enkele interne tools die het meeste van de data kunnen aggregeren, maar niet alles, en de rapporten die daaruit komen zijn niet ver genoeg aan te passen voor het specifieke type testen dat ik doe. Mijn favoriete tool om deze rapporten te schrijven en te leveren was Excel, en waarom ook niet? Ik kom uit een sterke Excel-achtergrond en ik geloofde destijds dat ik het alles kon laten doen.

Excel, we hebben een probleem...

Ik zal niet ingaan op het saaie proces om data van onze interne tools naar Excel te krijgen, maar het kostte mij uren werk en ik ben zeer bedreven in Excel. Data moest worden gekopieerd, opgeschoond, gesorteerd en gefilterd. Hoe ik mijn formules ook schreef, ze moesten bijna altijd worden aangepast, en vervolgens moesten tabellen en grafieken worden bijgesteld afhankelijk van het aantal tests, nodes en de verschillende responstijden die tijdens het project waren vastgelegd. Daarna volgde een zoek-en-vervangronde om de testnamen goed te krijgen, zorgen dat de getalnotaties en highlights klopten in elke rij op elke pagina, en algemene data-opschoning. Ik probeerde sjablonen, macro’s en VBA-scripts te schrijven. Niets wat ik deed was flexibel genoeg om een variërend aantal tests, apparaten en prestatiestatistieken aan te kunnen zonder serieuze nabewerking voor elke test. Ik wist een sjabloon te maken dat het proces versnelde, maar nog steeds kostte het een halve dag om alles goed te krijgen.

Ik moet een beetje lachen als ik denk aan sommige van de belachelijk ingewikkelde formules en VBA-scripts die in dat sjabloon terechtkwamen. Het werd nog erger toen we Java-versies bijwerkten en onze interne tools de logs niet meer goed konden verwerken. We stapten over op een externe tool genaamd GC Easy die logs uitstekend parseert naar een JSON-bestand dat in Excel kan worden geladen. De JSON-bestanden kun je met PowerQuery laden, maar het is een traag, moeizaam proces en als je 50+ JSON-bestanden met elk meer dan een miljoen rijen probeert te parsen, breek je Excel vrij vaak. Tel daar bij op dat ik het proces wilde automatiseren en daar geen goede manier voor vond. Zelfs als ik de data in Excel kreeg, liepen de visualisaties (gestapelde oppervlakgrafieken die Heap Size in de tijd weergeven) vast en veroorzaakten ze meer crashes. En zelfs als ik dat probleem kon oplossen, waren die JSON-bestanden nog maar het begin.

Handmatig kopiëren, opschonen en analyseren van data leidt onvermijdelijk tot fouten. We zijn allemaal mensen: we missen dingen, raken afgeleid, klikken mis. Mijn proces werkte niet en het zou nooit voor iedereen werken, tenzij het hele team een uitgebreide Excel-training zou krijgen. Misschien is het mijn militaire achtergrond, misschien gewoon een karaktertrek, maar ik zoek altijd naar manieren om processen te verbeteren en efficiënter te maken, dus begon ik te zoeken naar een manier om het hele proces te stroomlijnen.

Onze interne tool had destijds geen API en geen werkende exportfunctie. Ik had geen manier om er direct data uit te trekken zonder copy/paste. Mijn volgende gedachte: ga rechtstreeks naar de bron, de logbestanden van elk apparaat in de test. Dat betekende een testdirectory en de vele submappen daarin doorzoeken, data uit meerdere bestandstypen in verschillende formaten halen, alle berekeningen doen en het naar Excel schrijven in de vorm die ik nodig had, inclusief visualisaties en voorwaardelijke opmaak.

De bestandstypen die ik moest parsen waren .txt, .csv, .json (diverse structuren), .xml (diverse structuren), .dat, .html, en wat data moest uit een Oracle-database komen. Ik moest rekening houden met een variërende mapstructuur, uiteenlopende aantallen en structuren van bestanden, .xml-bestanden zonder afsluitende tags en bestandsgroottes boven de 10 GB. Het proces moest ook eenvoudig te volgen zijn, zo veel mogelijk geautomatiseerd, en miljoenen rijen data aankunnen. Excel ging dat absoluut niet dekken, dus wendde ik me tot populaire BI-tools: PowerBI en Tableau. Ik wil hier geen productreview van BI-tools maken, dus laat ik het erop houden dat die categorisch niet aan mijn behoeften voldeden.

Wat is dan een tool die alles kan wat Excel kan, rapporten in Excel kan schrijven, én aan al mijn eisen voldoet?

Maak kennis met mijn nieuwe vrienden: Python en Pandas

Als je ook maar enigszins bekend bent met Excel, denk ik dat je absoluut dol zult zijn op pandas voor Python. pandas brengt de functionaliteit van Excel samen met de kracht van de Python-taal. Excel-kolommen worden pandas Series, tabellen worden DataFrames en complexe formules worden Python-functies. pandas kan alles wat Excel kan:

Data inlezen

Excel kan prima platte bestanden lezen, en met PowerQuery heeft het een beperkte capaciteit om databases te bevragen en bepaalde .xml- en .json-bestanden te lezen. Als je de documentatie leest, lijkt het de perfecte tool om bijna alles te lezen, maar in de praktijk is dat simpelweg niet zo. De interface is onhandig en vergt veel gewenning, er is geen goede manier om het proces te automatiseren, en data opschonen is bijna net zo leuk als een tandartsbezoek. pandas daarentegen maakt data inlezen een fluitje van een cent.

Deze Python-bibliotheek voor datamanipulatie kan van nature data uit talloze bronnen lezen. Bekijk de documentatiepagina voor een volledige lijst. Het mooie is: als pandas het niet native leest, is de kans groot dat je een Python-pakket vindt dat het wel kan, of dat je met een paar regels code het probleem oplost. Bekijk DataCamp’s cursusreeks Importing Data in Python, naast andere cursussen, voor een diepgaande behandeling van dit onderwerp.

Ik kon met pandas elk bestandstype lezen dat ik hierboven noemde, inclusief die .xml-bestanden zonder afsluitende tags. Met wat creatieve natural language processing kan ik ook alle testmetadata uit de ruwe .txt-bestanden halen, iets wat me in Excel niet lukte omdat ze niet als data zijn opgemaakt. De versie van het script die ik nu gebruik leest niet alleen elk bestandstype dat ik nodig had, maar met een paar extra regels Python-code kon ik ook automatisch de Java Garbage Collection-.json-logs van GC Easy ophalen via de API; iets wat in Excel nooit zou lukken. Hoe verhoudt dit zich tot Excel? Met pandas kan ik een hele testdirectory doorzoeken en alle data keurig in DataFrames terugkrijgen in slechts een paar minuten. Het verwerkt zelfs moeiteloos die logbestanden van meer dan 10 GB. Dat lijkt misschien niet zo groot, maar probeer daar in Excel maar eens iets van te lezen.

Ik ben zelfs begonnen om in plaats van ruwe platte bestanden data uit een multiterabyte Oracle-database te halen. Dat kan ik niet met Excel vergelijken; op een kort experiment na heb ik databasequeries in Excel vermeden. Voor zover ik me herinner was het geen ervaring die ik wilde herhalen. Verbinden via Python is echter een makkie met SQLAlchemy. Verbinden is eenvoudig en de querysnelheid is vele malen hoger dan in Excel. Bekijk Introduction to Relational Databases in Python voor een fijne introductie.

Data opschonen

Visualisaties daargelaten, geloof ik heilig dat data in tabellen moet staan die schoon, netjes, opgemaakt en gelabeld zijn. Pandas heeft uitstekende opties voor het opschonen van data en het presenteren van fraai opgemaakte tabellen. De Styling-pagina op de officiële Pandas-website is mijn favoriete gids voor het opmaken van DataFrames. Met een paar korte regels code stel je decimalen in, formatteer je nummers, voeg je rij- en voorwaardelijke opmaak toe, en het proces is eindeloos herhaalbaar over een onbeperkt aantal documenten. De mogelijkheid om bepaalde delen van data-opschoning te automatiseren, ongeacht de omvang van de data, heeft me ontelbare uren bespaard.

Eerlijk is eerlijk: je kunt zeker data opschonen in Excel. Het heeft veel ingebouwde tools precies voor dat doel, en dat is ook wat ik elke keer moest doen wanneer ik (uiteindelijk) alle benodigde data erin had geladen. Het was grotendeels handwerk met veel te veel muisklikken en, wederom, erg foutgevoelig en tijdrovend. Zelfs als je extreem bedreven bent met alle tools die Excel biedt, zullen er fouten insluipen. Er zijn manieren om het te versnellen met formules, macro’s en VBA-scripts (daar kom ik zo op), maar het is verre van een perfecte oplossing.

Data analyseren

Ik zal hier heel direct zijn: pandas verslaat Excel met gemak als het gaat om data-analyse. Er is geen eerlijke manier om die twee te vergelijken. Je kunt in Excel formules schrijven en ingebouwde functies gebruiken om data te analyseren; dat heb ik jaren gedaan en ik kreeg het werkend. Ik gaf wat lessen in data-analyse met Excel, pleitte meerdere keren voor het gebruik ervan, en volgde een statistiekcursus waarin Excel de tool bij uitstek was. Ik zeg niet dat het niet kan worden gebruikt voor analyse. Maar het vergelijken met pandas is als een potlood en papier vergelijken met een rekenmachine; ze spelen niet in dezelfde competitie.

In augustus vorig jaar meldde Kaggle dat Python R had ingehaald in Data Science, en in december noemde Quartz pandas het ‘belangrijkste hulpmiddel in Data Science’. Ik hoef er niet veel meer over te zeggen.

Macro’s en VBA

In 2012 zat ik nog in actieve dienst en moest ik voor een routinecontrole naar de tandarts op Fort Bliss in El Paso, Texas. Toen ik in de stoel zat, zei de tandarts dat mijn verstandskiezen eruit moesten, en omdat hij tijd had, voerde hij de ingreep ter plekke uit met alleen een plaatselijke verdoving. Het was een vreselijke ervaring en ik had dagenlang pijn. Als ik moest kiezen tussen dat opnieuw doen of werken met Excel-macro’s of Visual Basic, dan laat ik liever nog wat tanden trekken.

Er zijn genoeg mensen die daar zeer bedreven in zijn en ik heb geweldige dingen gezien met slimme VBA-scripts, en dat waardeer ik. Ik ben er duidelijk geen fan van en raad het niemand aan, zeker niet als je in plaats daarvan Python kunt gebruiken. Het is zo’n populaire vervanger voor VBA dat Microsoft overweegt om Python als scripttaal in Excel toe te voegen.

Maar ik moet Excel nog steeds gebruiken

Hoe graag ik ook alles in Python zou doen, dat is in mijn situatie gewoon niet praktisch. Voor nu moeten de resultaten van mijn analyse nog steeds in Excel worden aangeleverd. Gelukkig zijn er pakketten zoals OpenPyXl en XlsxWriter die daar precies voor gemaakt zijn. Ik gebruik XlsxWriter dagelijks om DataFrames om te zetten in zeer professionele rapporten in Excel, zonder formules, macro’s of VBA-scripts. Het doet zelfs visualisaties.

Over data visualiseren gesproken...

Geen saaie grafieken meer

Ik ben nooit een visualisatiefanaat geweest. Ik zag data het liefst in rijen en kolommen met highlights op interessante punten, misschien hier en daar een staafdiagram. Excel doet het prima en biedt genoeg opties om data te visualiseren, maar mijn kennis beperkte zich tot gestapelde oppervlakgrafieken, staafdiagrammen en lijndiagrammen. Voor elk daarvan selecteerde ik de data, klikte op “Grafiek invoegen” en zocht wat ik nodig had. Daarna deed ik wat filtering en voegde een titel toe, maar daar bleef het bij.

Voor de volledigheid: er zijn heel veel opties om grafieken in Excel aan te passen, maar het proces kan behoorlijk lastig zijn en de leercurve is steil. Ik maak nog steeds niet graag grafieken in Excel, maar met Python is het een ander verhaal.

Ik ben inmiddels een overtuigd voorstander van datavisualisatie en steek veel moeite in goede visualisaties voor analyses. Toen ik hier bij DataCamp cursussen begon te volgen en kennismaakte met pakketten als Matplotlib en Bokeh, begon ik de echte kracht en het doel van visualiseren te begrijpen. Met Matplotlib maak je eenvoudig visualisaties vanuit pandas of door pyplot te importeren. De lijst met mogelijke visualisaties binnen pyplot is op z’n zachtst gezegd uitgebreid, en vrijwel elk aspect van een plot is aan te passen. Introduction to Data Visualization With Python laat zien hoe je uitstekende visualisaties met Matplotlib maakt.

Hoe goed Matplotlib ook is, ik had soms wat meer interactiviteit nodig in mijn visualisaties. Ik wilde de mogelijkheid om te selecteren, zoomen, hoveren, sorteren en filteren in een visualisatie, en ik wilde zeker geen react.js of D3 leren. Python leren was en is een genoegen, maar JavaScript is niet mijn sterke kant. Gelukkig biedt Python opnieuw uitkomst met pakketten als Bokeh en Dash.

Bokeh, ondersteund door Anaconda, combineert D3.js en Python om volledig interactieve grafieken te maken die je kunt exporteren als op zichzelf staande html-documenten, kunt insluiten in webpagina’s of kunt draaien op een Bokeh-server. Hoewel de leercurve steiler is dan bij Matplotlib, denk ik dat je het ermee eens zult zijn dat de visualisaties de moeite waard zijn; kijk maar in de Gallery. Ik ben nog lang niet zo bedreven in Bokeh als ik wil worden, maar het is sinds de eerste les van Interactive Data Visualization With Bokeh een van mijn favoriete visualisatiepakketten. Past Bokeh niet bij je behoeften, dan is er Dash van de mensen bij Plotly.

Dash is een andere gratis en open-source visualisatietool voor Python die React.js in plaats van D3 gebruikt. Ik ontdekte Dash pas recent en het levert ook fantastische, interactieve visualisaties zonder dat je een regel Javascript hoeft te leren. Het is lastig om Dash met Bokeh te vergelijken, want beide kunnen geweldige visualisaties produceren. Mijn enige struikelpunt met Dash tot nu toe is dat ik de visualisaties alleen op een Dash-server aan de praat kreeg. Het is me nog niet gelukt om ze in zelfstandige documenten, html-pagina’s of Jupyter-notebooks in te sluiten. Desondanks ziet het er veelbelovend uit, zoals je in hun gallery kunt zien.

Bespaar tijd met plannen en taakautomatisering

In de zomer van 2014 hadden we een probleem in de organisatie waar ik toen werkte. De Army Reserve wordt aangestuurd door een reeks cijfers die samen “Readiness” worden genoemd. Het is het percentage personeel dat aan een organisatie is toegewezen en dat kan worden opgeroepen voor een inzet. Het cijfer wordt bepaald door verschillende categorieën data, waaronder medisch, tandheelkundig, opleiding, fysieke fitheid en nog een hele reeks andere cijfers. Ik kan het getal hier niet noemen, maar het lag bij ons lang niet waar het moest zijn.

Mijn oplossing was een meerbladig rapport in Excel met verschillende tabellen en grafieken, samengesteld uit diverse personeelsdatabases. Deze informatie omvatte samenvattende gegevens over de organisatie, informatie over elke persoon in elke meetcategorie en projecties van waar dat getal over de komende drie maanden zou liggen op basis van de acties die die week werden ondernomen. Het was een groot succes en onze ‘Readiness’ steeg aanzienlijk en bleef lang op peil.

Maar hoewel het werkte, was het een rommeltje. Ik kwam maandagochtend vroeg binnen en haalde handmatig de informatie uit alle verschillende bronnen, waarna ik de data opschoonde en kopieerde naar het rapport van die week. Daarna besteedde ik minstens een uur aan het controleren op fouten en nog meer opschonen. Vervolgens moest het per e-mail naar het secretariaat zodat zij de data konden verifiëren en updates konden toevoegen die nog niet in de databases stonden. Gemiddeld kostte het drie dagen om dit rapport te versturen. De kosten in manuren waren buitensporig en onnodig.

Met een beetje Python-scripting had ik automatisch alle data uit de verschillende bronnen kunnen halen, alles kunnen samenvoegen in pandas-DataFrames, het hele rapport naar Excel kunnen schrijven en het automatisch naar het secretariaat kunnen mailen. Het mooiste is: dit had ingepland kunnen worden om één minuut na middernacht op maandagochtend te draaien en zou ontelbare uren werk hebben bespaard. Op Linux-systemen is het kinderlijk eenvoudig om Python-scripts op bepaalde tijden te laten draaien door ze toe te voegen aan de crontab. In mijn huidige baan werken we met zoveel systemen dat crontab een essentieel onderdeel van het werk is en taken inplannen daar een kernvaardigheid. Dus als je elke nacht om middernacht data moest ophalen en in een rapport laden, dan is crontab op een Linux-systeem de aangewezen weg.

Ik ben een Linux-fan, maar het merendeel van mijn werk doe ik op Windows, en dat is mijn favoriete besturingssysteem. Daar heb je verschillende opties om taken te plannen via de Windows Taakplanner, Schtask.exe of Powershell. Met elk van die opties kun je je scripts plannen tijdens de rustigste uren wanneer de meeste systeembronnen vrij zijn, of ze laten draaien direct nadat je weet dat een databron is bijgewerkt. Het is een optie waarvan ik wou dat ik die een paar jaar eerder had gehad.

Mijn favoriete tool: Jupyter Notebooks

Omdat ik zo lang met Excel heb gewerkt, voelde Python leren alsof ik mijn hele leven alleen een combinatietang had en op een dag een complete gereedschapskist vol glimmende nieuwe tools ontdekte. Elk daarvan is geweldig en heeft z’n plaats, maar als er één tool is die ik meer gebruik dan alle andere, dan is het Jupyter Notebooks. Alles wat ik in Python heb geschreven, is in een Jupyter Notebook geschreven, hetzij voor directe consumptie, hetzij voor later gebruik als los Python-script. Jupyter is geweldig voor het schrijven van functies, het testen van code, verkennende data-analyse en zelfs het presenteren van een eindproduct. Je kunt er zelfs blogposts mee schrijven (zoals deze) of op Github met Pages. Als je de tutorials op DataCamp bekijkt, zie je dat veel ervan op dezelfde manier zijn gemaakt.

Waarom noem ik dit in een discussie over Excel?

Jupyter Notebooks kunnen dienen als eindproduct van je analyse in plaats van Excel. Je kunt je code schrijven, pandas-DataFrames, visualisaties en conclusies allemaal op één pagina tonen die met één klik naar HTML is te converteren. Een van mijn persoonlijke doelen is om volledig los te komen van Excel en in plaats daarvan Jupyter Notebooks te gebruiken voor het versturen van afgeronde rapporten. Dat wordt nog haalbaarder nu JupyterLab is uitgebracht. Daarmee en met widgets kun je een Jupyter Notebook omtoveren tot een interactieve data-analyseapplicatie.

Tot slot

Ik gebruik Excel nog dagelijks; het is een geweldige tool die diep verankerd is in veel organisaties, waaronder die van mij. Bijna iedereen kent het en kan het gebruiken om informatie te consumeren en eenvoudige data-analyses te doen. Wat volgens mij veel mensen niet weten, is dat dezelfde analyse met Pandas en Python niet alleen efficiënter is, maar ook makkelijker.

Mijn doel is om tegen het einde van het jaar volledig los te komen van Excel, en dat kan ik doen met de tools die ik in dit artikel noemde. Je wordt niet van de ene op de andere dag een expert, maar dat geldt ook voor Excel. Ik hoop dat dit je heeft geïnspireerd om er afstand van te nemen en enkele van de nieuwe tools te proberen die ik noemde. Het maakt je efficiënter, productiever en versnelt uiteindelijk je workflow.