Jääkaapin data Googlesta Pythonilla Exceliin

Kirjoitin työläästä muunnoksesta, jonka älyjääkaapin Google Docsiin lähettämä tiedostomuoto tarvitsi ennen kuin Excel tunnisti sen aikamerkinnät oikein. Kommenteissa nimimerkki Zarr antoi hyvän vinkin: Pythonissa on valmiina aikamerkintöjen muuntamiseen sopivat työkalut.

Totta! Siispä töihin.

Ongelma on siinä, että älyjääkaappi lähettää tiedot oven avauksista hassussa formaatissa ja tallentaa ne IFTTT-rajapinnan kautta Google Docsin laskentamalliin näin:

Google Docsin laskentamallin alkua.

Googlen omilla toiminnoilla datan saa haettua omalle levylle Excel-tiedostoon, mutta päivämäärien muokkaus Excelin ymmärtämään muotoon on työlästä.

Niinpä tallensin datan Excelistä tekstitiedostoon:

Data tekstimuodossa 12h kellonaikoina.

Sen jälkeen tein pienen Python-ohjelman. Siitä on lukuisia vuosia, kun olen viimeksi ohjelmoinut mitään, saati opetellut kokonaan uutta kieltä. Python on kuitenkin suosittu ja sillä on helppo tehdä asioita, jotka muilla olisivat erittäin työläitä. Tässä auttavat kirjastot, joita on saatavissa liki jokaiseen tarkoitukseen. Käytin itse openpyxl-nimistä kirjastoa, jonka avulla oma Python-sovellus pystyy lukemaan ja kirjoittamaan Excel-tiedostoja. Se avaa hienoja mahdollisuuksia tiedostomassojen analysointiin tai esimerkiksi lokitiedostojen esiprosessointiin, kuten tässä.

Python-ohjelma mahtuu muutamaan riviin.

Valmiiden kirjastojen ansiosta omasta ohjelmasta tulee yksinkertainen: se vain lukee tekstimuotoisen lokitiedoston, poimii riveiltä jääkaapin tuottaman aikaleiman ja palastelee sen uudelleen, sekä kirjoittaa lopputuloksen Excel-tiedostoon. Ohjelman rivejä voisi vielä vähentää tekemällä kaiken yhdessä luupissa.

Dokumentaation mukaan %d lukee päivän numeron silloin, kun alle kymmenen numeroita edeltää nolla (01, 02, 03...) ja että normipäiviin (1, 2, 3...) pitäisi käyttää muotoa %-d. Ilmeisesti dokumentaatio on vanhentunut, sillä %-d ei toiminut ja %d tuotti oikeat numerot kaikilla päivämäärillä.

Muutaman sekunnin ajo ja C:\temp-hakemistoon syntyy tiedosto ajat.xlsx. Onneksi Excel ymmärtää suoraan Pythonin käyttämää formaattia, joten aikaleimaa ei tarvitse muokata merkkijonona ennen kirjoittamista, mutta tarvittaessa sekin olisi helppo koodata.

Data Excelissä sen ymmärtämässä päiväysformaatissa 24h kellonaikoina.

Kouluissa olisi hyvä opettaa kaikille ohjelmoinnin perusteet, sillä se auttaa ymmärtämään koneiden ja tietojärjestelmien toimintaa -- ja varsinkin toimimattomuutta. Pienellä vaivalla apuohjelmista ja omatekoisista pikkuohjelmista saa itselleen tehokkaita työkaluja, kuten tässä.

Älyjääkaappi raportoi käytöstään

Ostin vuoden 2018 alussa Boschin älyjääkaapin. Älykkääksi sen tekee mobiilisovellus, jonka kautta voi säätää jääkaapin ja pakastimen lämpötilaa, saada hälytykset liian pitkään auki olevasta ovesta sekä mahdollisuus katsoa kahdella kameralla mitä jääkaapissa on. Viimeksi mainittu on näppärä ominaisuus ruokakaupassa. Usein on vaikea muistaa, montako maitotölkkiä kaapissa olikaan. Toisaalta pelkistä kamerakuvista on vaikea nähdä koko sisältöä eikä se tietenkään kerro, onko maidon viimeinen käyttöpäivä mennyt jo umpeen.

Bosch käyttää HomeConnect-tekniikkaa, joka ei ole erityisen avoin. Siihen kuitenkin löytyy IFTTT-rajapinta, jonka kautta jääkaappi pystyy liittymään muihin sovelluksiin. Kirjoitin aiheesta aiemmin.

Koronaviruksen aiheuttaman eristäytymisen vuoksi tuli mieleen katsoa, millaista dataa jääkaappi on vuosien mittaan tuottanut. Rajapinta on toiminut luotettavasti ja kirjannut Google Docsin laskentamalliin lähes 20 000 riviä. Niiden analysointi ei tosin ollut ihan helppoa. Seuraavassa muutama idea, joita voi soveltaa muissakin tehtävissä.

Ensimmäinen työ on saada data Exceliin, sillä Google Docs ei sovellu isojen datamäärien pyörittelyyn. IFTTT oli tuottanut toistakymmentä HomeConnectFridgeLog(n) tiedostoa, missä n oli järjestysnumero. Tiedostot piti ladata omalle levylle, avata Exceliin ja kopioida manuaalisesti peräkkäin samalle laskentamallin sivulle. Otin mukaan vain datan vuoden 2019 alusta, jolloin analysoitavaksi jäi 13 632 riviä dataa.

Tästä analyysi alkaa.

IFTTT kirjoittaa avaukset omassa muodossaan, mikä ei ole suoraan Google Docsin eikä Excelin käyttämä päiväysformaatti. Se vaati melkoisen määrän manuaalista käsittelyä ja joukon tarkkaan suunniteltuja etsi-korvaa-toimintoja ennen kuin päivämäärä sekä tuntiarvo oli saatu poimittua omiin sarakkeisiinsa.

Jos merkkijonon pituus olisi ollut vakio, olisi voinut helposti poimia päivän numeron ja kellonajan. Vaihteleva pituus pakotti kikkailuihin. Tehtävää helpotti se, että pilkku, at ja kaksoispiste olivat kaikki erilaisia erottimia. Samat erottimet eri paikoissa olisivat mutkistaneet prosessia merkittävästi.

Tein ensin etsi-korvaa kuukausien nimille ja vaihdoin ne numeroiksi (January -> 01). Päivän poimiminen osoittautui vaikeimmaksi, se piti tehdä useassa osassa. Perässä oleva pilkku helpotti korvausta ( 1, -> 01,), mutta tällöin piti huomioida myös numeron edessä oleva välilyönti. Ensimmäisellä yrityksellä 31, vaihtui muotoon 301, mikä ei tietenkään ollut tarkoitus.

Kun päivämäärä oli vakiomuotoinen, vuoden poimiminen omaan sarakkeeseen oli helppoa ja koska siinä oli vain kaksi arvoa, ne olisi voinut täyttää ilman funktioitakin.

Kellonaika oli yllättävän visainen ongelma, sillä AM/PM-muunnos ei ollut ihan mekaaninen. Periaatteessa PM tarkoittaa kellonaika +12 h, mutta ensimmäinen tunti puolenpäivän (klo 12:00) jälkeen ei olekaan 0 PM vaan 12 AM.

Lopulta data oli kuitenkin halutussa järjestyksessä Excelissä.

Sarakkeet oikeilla paikoillaan

kkvv-sarake oli tarpeen x-akselin arvoja varten. Näistä olikin sitten jo helppo laatia Pivot-taulukko ja laskea eri kuukausien avausmäärät yhteen.

Jossain tapauksissa on helpompaa siirtää data csv-muodossa ensin Wordiin ja tehdä perusmuokkaus siellä esimerkiksi erotinmerkkejä muuttamalla. Vasta sen jälkeen data palautetaan Exceliin joko tiedostona tai leikepöydän kautta.

Jääkaapin oven avaukset kuukausittain.

Maaliskuun 2020 aiheuttama piikki näkyy selvästi. Sen kohdalla 1160 avausta ovat 31 prosenttia suuremmat kuin maaliskuun 2019 avaukset (885 kertaa). Todellinen nousu on vielä suurempi, sillä etätyöpakko alkoi vasta kuun puolivälissä.

Kun data oli kerran olemassa, sitä oli helppo pyöritellä. Toinen grafiikka näyttää avaukset kellonajan (tunti) mukaan:

Jääkaapin oven avaukset tunneittain.

Ei ole mitenkään yllättävää, että kuvaajan muoto täsmää esimerkiksi mobiilidatan hitauteen, joskin huippu osuu pari tuntia aikaisemmaksi.

Tämän jälkeen olisi helppo laskea korrelaatio kuukausittain jääkaapin avauksen ja painon (Withings-älyvaaka) tai askelten (Fitbit-askelmittari) kanssa. Korrelaatio olisi epäilemättä vahva. Tulos voisi kuitenkin olla sen verran masentava, että jääköön analyysi tähän.

Jääkaapin dataesimerkki on muistutus siitä, miten dataa syntyy nykyään kaikkialla, ja millaisia yllättäviäkin laskelmia sen perusteella voidaan tehdä.