Tilastokeskuksen Paavo – postinumeroittainen avoin data Exceliin

Tilastokeskuksen Paavo – postinumeroittainen avoin data Exceliin

Tilastokeskuksen sivuilta voi ladata mielenkiintoista postinumeroittain luokiteltua dataa, kuten postinumeroalueiden asukasrakenteen, asukkaiden koulutusasteen, talouksien käytettävissä olevat rahatulot, rakennukset ja asunnot, jne. Jos olet kiinnostunut tutkimaan aineistoja, voit tehdä sen helposti Excelillä. Sellaisenaan Tilastokeskuksen sivuilta ladatun aineiston käsittely on hieman kömpelöä, sillä jokaista muuttujaa varten on oma sarakkeensa. Aineiston tutkiminen esimerkiksi pivot-taulukoiden, Power View -kaavioiden tai Power Map 3D animointien avulla helpottuu, kun käännät datan ensin raportoinnin näkökulmasta sopivaan muotoon Power Queryllä.

Tässä jutussa opastetaan vaihe vaiheelta aineiston muokkaaminen raportoitavaan muotoon. Löydät lisätietoja Power Query apuohjelman käyttöönotosta Power Query Oppaasta. Lisäksi sinun pitää aktivoida Power Pivot, jos et ole käyttänyt sitä aiemmin.

 Tietoaineiston valitseminen Tilastokeskuksen sivuilla

    1. Siirry osoitteeseen: http://pxnet2.stat.fi/PXWeb/pxweb/fi/Postinumeroalueittainen_avoin_tieto/ ja laajenna kansio auki ja valitse haluamasi tietojoukko. Tässä esimerkissä käytetään esimerkkinä asukkaiden koulutusastetta vuodelta 2012.Tilastokeskuksen avoin data
    2. Valitse sekä Postinumero että Tiedot -kentistä halutessasi kaikki tiedot ruuduissa olevilla kuittausmerkeillä. Valitse tulostiedostoksi Excel (xlsx) (koodi ja teksti sarakkeilla) ja Jatka.asukkaidenkoulutusaste
    3. Tallenna muodostuva kohdetiedosto.

Aineiston muuntaminen raportoitavaan muotoon

  1. Luo uusi tyhjä työkirja, johon Tilastokeskuksen sivuilta ladattu työkirja luetaan ja muunnetaan Power Queryn avulla.
  2. Valitse Excel 2010 tai 2013 -versiossa Power Query > From File > From Excel (Power Query > Tiedostosta > Excelistä) ja valitse Tilastokeskuksen sivuilta tallentamasi Excel-tiedosto. Valitse vielä tiedostossa oleva taulukko (esim. paavo_2_ko) ja siirry Power Queryn puolelle Edit (Muokkaa) -komennolla. Excel 2016 -versiossa aloitat tuonnin komennolla Data > New Query > From File > From Workbook. edit
  3.  Kun kyselyeditori on käynnistynyt, poista ensin aineiston yläpuolelta turhat rivit Home > Remove Rows > Remove Top Rows (Aloitus > Poista rivit > Poista ylimmät rivit) -komennolla ja syöttämällä poistettavien rivien lukumäärän (2).poista ylimmät rivit
  4. Nosta sen jälkeen ylin rivi otsikkoriviksi Use First Row As Headers (Käytä ensimmäistä riviä otsikkoina) -komennolla.use first row as headers
  5. Suorita tämän jälkeen muiden kuin kahden ensimmäisen sarakkeiden kääntäminen valitsemalla Ctrl-näppäimen avulla kaksi ensimmäistä saraketta (Power Queryssä ei voi valita sarakkeita maalaamalla) ja käyttämällä komentoa Transform > Unpivot Columns > Unpivot Other Columns (Muunna > Poista sarakkeiden pivot-asetus > Poista muiden sarakkeiden pivot-asetus).
    unpivot other columns
  6. Kaikista muista kuin valituista sarakkeista muodostuu kääntämisen seurauksena kaksi saraketta: Attribute (Attribuutti) ja Value (Arvo). Aineiston pitäisi näyttää tässä vaiheessa tällaiselta:välivaihe kun käännetty
  7. Numeeriselta näyttävässä Value-sarakkeessa on lukujen lisäksi myös joitakin soluja, joissa lukee joko yksi piste (.) tai kaksi pistettä (..). Tilastokeskus ei näytä sellaisten alueiden tietoja, joissa 18 vuotta täyttäneiden asukkaiden lkm on alle 30. Jotta pisteet eivät aiheuta virheitä, ne on hyvä korvata esimerkiksi nollilla. Jotta puolestaan korvaamisen voi tehdä, sarake pitää muuntaa väliaikaisesti teksti-muotoon. Valitse siis Values-sarakkeen pikavalikosta Change Type > Text (Muuta tyyppi > Teksti).
  8. Suorita tämän jälkeen sekä pisteen korvaaminen nollalla että kahden pisteen korvaaminen nollalla. Käytä pikavalikon komentoa Replace Values (Korvaa arvot). Rastita molemmilla korvauskerroilla Match entire cell contents (Vastaa koko solun sisältöä).
    korvaaminen
  9. Muunna korvausten jälkeen sarake takaisin numeeriseksi pikavalikosta komennolla Change Type > Decimal Number (Muuta tyyppi > Desimaaliluku ).
  10. Erota seuraavaksi toisessa sarakkeessa lukeva kaupungin nimi omaan sarakkeeseensa jakamalla sarakkeen sisältö välilyönnin ja sulkumerkin kohdalta. Valitse erottimeksi Custom (Mukautettu) ja syötä erotinmerkkikenttään peräkkäin sekä välilyönti että sulkumerkki ” (”. Voit valita jaon tehtäväksi joko vasemmalta tai oikealta lukien.
    kaupungin erottaminen
  11. Poista vielä turha sulkumerkki muodostuneesta sarakkeesta Replace Values (Korvaa arvot) -komennolla. Kirjoita Value to Find -kenttään sulkumerkki ja jätä Replace With -kenttä tyhjäksi, jolloin sulkeet korvataan pois.
    korvaa sulkumerkki pois
  12. Jaa vielä Column 2.1 -sarake Split Column > By Number of Characters (Jaa sarake osiin > Merkkien määrän mukaan) -komennolla viidennen merkin kohdalta, jotta kaupunginosasta muodostuu oma sarakkeensa.
    split by position
  13. Jaon seurauksena sarakkeeseen jää edeltävä välilyönti, jonka voit siivota pois komennolla Transform > Trim (Muunna > Rajaa). Komento siivoaa tyhjät merkit pois sekä solun alusta että lopusta.
    trim
  14. Anna lopulta sarakkeille kuvaavat nimet kaksoisnapsauttamalla sarakeotsikoita ja poista jompikumpi turhaksi jääneistä postinumerosarakkeista.
    sarakenimet ja poistot
  15. Mikäli aiot tehdä aineistosta karttakaavioita Excel 2013 -version Power View -kaavioina, sinun kannattaa lisätä aineistoon kaksi saraketta. Yhden, joka sisältää postiosoitteen eli sekä postinumeron että kaupungin ja toisen, joka sisältää kaupungin sekä maan. Voit lisätä sarakkeita komennolla Add Column > Add Custom Column (Lisää sarake > Lisää mukautettu sarake).
    Postiosoite kaava
    Sijaintikunta kaava
  16. Power Map -karttakaavioissa on hyvä olla myös maa-sarake, jonka voit myös muodostaa kaavasarakkeena.
    maa
  17. Lataa tiedot lopulta Exceliin komennolla Home > Close & Load > Close & Load to (Aloitus > Sulje ja lataa > Sulje ja lataa kohteeseen). Valitse Table (taulukko), jos lataat tiedot normaalisti Exceliin. Lataa tiedot Power Pivotiin valitsemalla Add This Data to the Data Model (LIsää nämä tiedot tietomalliin), jos käytössäsi on Excel 2013 ja jos aiot tehdä aineistosta Power View tai Power Map -visualisointeja. Vaihda tällöin Table-vaihtoehdon tilalle Only Create Connetion (Luo vain yhteys).

Tämän jälkeen voit ryhtyä tekemään pivot-taulukoita ynnä muita visualisointeja. Huomaa, että karttavisualisointeja varten sinun pitää tehdä muutamia esivalmisteluita Power Pivot -tietomallin puolella.

Tietomallin valmistelu karttavisualisointeja varten

Jotta Power View ja 3D Map (ent. Power Map) tunnistavat suomenkielisiä kaupunkien nimiä, tietomallissa pitää käydä tekemässä muutamia määrityksiä.

  1. Valitse Excelissä Power Pivot > Manage (Hallitse).
  2. Valitse vuorollaan solu kustakin maantieteellisestä sarakkeesta (postinumero, kaupunki ja maa) ja määritä sen tietoluokka (data category) Advanced (Lisäasetukset) -välilehdeltä.
    määritykset tietomallissa
  3. Postiosoite ja sijaintikunta pitää määrittää Place (Paikka) -tyyppisiksi valitsemalla Data Category (Tietoluokka) listalta alimmainen vaihtoehto More categories (Lisää luokkia) ja sen jälkeen Place (Paikka). Suomenkielisessä versiossa voi tulla virheilmoitus, mutta siitä ei tarvitse piitata. Tyypin määritys onnistuu siitä huolimatta.

Visualisoinnin testaus

Siirry Excelin puolelle ja valitse tyhjä solu ja anna komento Insert > Power View (Lisää > Power View).

Jos et ole vielä aktivoinut Power View -kaavioita käyttöön, alla linkit eri versioiden ohjeisiin:

Raahaa tyhjälle arkille ensin postiosoite (huomaa kenttäluettelossa näkyvä maapallon symboli, sillä kenttä on määritetty maantieteelliseksi kentäksi). Rahaa saman arkille ilmestyneen ruudun sisään myös Arvo-kenttä. Vedä tämän jälkeen arkilla näkyvään suodatuspaneeliin Kaupunki ja Koulutusaste. Suodata näkyviin kaikki muut paitsi tyhjät (blank) kaupungit ja valitse vain yksi koulutusasteista.

raahaa arkille

Napsauta tämän jälkeen arkilla näkyvää tulostaulukkoa ja kokeile muuttaa taulukko esimerkiksi kartaksi (Map) tai palkkikaavioksi (Bar Chart) Design (Rakenne) -välilehdeltä.

visualisointivaihtoehdot

Suodata vain yksi kaupunki esiin ja vie hiiri kartalla näkyvän postinumeron päälle, jotta saat esiin luvun.

suodata yksi kaupunki
Katso myös visualisointi-idea aiemmasta jutustani: Excelin People Graph apusovellus

people graphin ulkoasu

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s