25.6.2014 • Liiketoiminnan digitalisointi ja tiedolla johtaminen
Trafin ajoneuvotiedot Excelin tietomallissa
Miten monta Teslaa Suomessa on ja missä ne on rekisteröity? Fiat 500:sten lukumäärä Espoossa? Autojen keskimääräinen ikä kunnittain? Mikä on suosituin Audin väri Helsingissä?
Trafi avasi liikenteessä olevien henkilöajoneuvojen tiedot julkiseen käyttöön toukokuussa (31.5.2014). Nyt ensimmäisessä vaiheessa on avattu ajoneuvojen teknisiä tietoja sisältävä pilottiaineisto ja Trafi tulee avaamaan avoimen datan palvelun vuoden 2014 loppuun mennessä. Trafi lisää aineistoja saataville vaiheittain sitä mukaan, kun aineistot ovat teknisesti valmiita julkaistavaksi. Tässä ensimmäisessä julkaistussa aineistossa on mm. ajoneuvotyyppi, ensirekisteröintipäivämäärä, käyttöönottopäivämäärä, väri, käyttövoima, vaihteistotyyppi ja korityyppi. Ks. Avoin data Trafissa
Hienoa, että viranomaiset avaavat dataa ja mainiota, että Excelin Power Pivotin tietomalliin voi imuroida isoja data-aineistoja. Power Pivotin pakkaussuhde on huomattava (alla Trafin data.csv on ladattuna Excelin tietomalliin):
Tein Trafin datasta valmiin Power Pivot tietomallia hyödyntävän Excel-työkirjan, jonka voit ladata itsellesi Trafin datan tutkimiseen. Jos käytössäsi on 64-bittinen Excel-versio, pystyt analysoimaan jopa koko Trafin julkaisemaa 2,6 miljoonan ajoneuvon aineistoa. Löydät blogista myös 32-bittistä Excel-versiota varten tehdyn työkirjan, johon latautuu vähemmän ajoneuvoja (2010-luvulla käyttöönotetut ajoneuvot eli hitusen yli puoli miljoonaa riviä).
Työkirjoissa on tietomallin lisäksi runsaasti valmiita pivot-taulukoita, mutta niitä sekä laskennallisia kenttiä voit luoda itse lisää. Työkirjat sisältävät vain pienen otoksen datasta, joten sinun pitää ladata aineisto myös Trafin sivuilta alla olevien ohjeiden mukaisesti.
Suomen ajoneuvot -työkirja tutkittavaksi
Työkirjat on laadittu Excelin 2013 –versiolla ja tietomalli on toteutettu Power Pivotilla, joten tarvitset Excel 2013:sta jonkun seuraavista versioista: Professional Plus, Office 365 Pro Plus tai Standalone.
Datatiedostojen lataus Trafin sivuilta
Lataa ja tallenna Trafin sivuilta sekä zipattu data-tiedosto (.csv) että datatiedostossa käytetyn koodiston kuvaus (.xlsx).
Esivalmistellun analysointityökirjan lataus
Lataa itsellesi joko täyden datan analysointiin (vaatii 64-bittisen Excelin ja Power Pivotin) tai 2010-luvun datan analysointiin luotu työkirja (pitäisi pyöriä myös 32-bittisessä, jos työasemassa riittävästi muistia):
- Kaikki liikenteessä olevan henkilöajoneuvot: Liikenteessa olevat henkiloajoneuvot
- 2010-luvulla käyttöönotetut liikenteessä olevat henkiöajoneuvot: Liikenteessa olevat henkiloajoneuvot 2010-luku
Huom! Näissä tiedostoissa on vain suppeat otokset varsinaisesta datasta.
Power Pivotin aktivointi Excelissä
Aktivoi Power Pivot käyttöön komennolla File > Options > Add-Ins (Tiedosto > Asetukset > Apuohjelmat). Valitse ikkunan alaosasta COM Add-Ins ja Go (Siirry). Rastita Microsoft Office PowerPivot for Excel 2013. Jos Power Pivot puuttuu ikkunasta, sinulla ei ole oikeaa Excel-versiota.
Ajoneuvotyökirjan valmistelu
Avaa ajoneuvotyökirja ja salli ulkoiset tietolähteet.
Siirry Power Pivotin puolelle valitsemalla PowerPivot-välilehdeltä Manage (Hallitse).
Valitse Power Pivotissa Home > Existing Connections (Aloitus > Olemassa olevat yhteydet) ja valitse vuorollaan molemmat ikkunan yläosassa olevat tietolähteet (Ajoneuvot ja Excel Koodisto) ja muokkaa tiedostopolku osoittamaan lataamiisi Trafin tiedostoihin valitsemalla Edit > Browse (Muokkaa > Selaa). Suorita myös molempien tietolähteiden päivitys Refresh (Päivitä) -painikkeella. Huomaa, että tietolähteiden muuttaminen sekä niiden päivitys (erityisesti ajoneuvodata) saattaa kestää useita minuutteja. Odota rauhassa, jos ikkunoiden painikkeet eivät heti vaikuta reagoivan.
Kun tiedot ovat päivittyneet, sulje Power Pivot -ikkuna ja testaa Excelissä olevia mallipivotraportteja. Voit luoda työkirjaan lisää pivotteja sekä lisätä laskettuja kenttiä.
Ja sitten vastauksiin:
– Helsingissä Audin suosituin väri on musta:
– Tesloja on 45 ja niitä löytyy seuraavilta paikkakunnilta:
– Fiat 500 viistoperiä löytyy Espoosta 110 ja minulla on niistä yksi
Vinkki! Jos haluat itse oppia tekemään vastaavia Power Pivot -malleja, lue myös Power Pivotiin tutustuminen – Trafin ajoneuvodata. Jutussa neuvottaan vaihe vaiheelta tietomallin luonti. Vesikulkuneuvoista laaditun Excel-tiedoston voit ladata täältä.
Heidi Enho kirjoittaa myös Excel ja Power BI –aiheista blogia HExcelligent.fi