Kirjoittajan arkistot: Aki Taanila

Frekvenssitaulukot Exceliin

Päivitetty 20.9.2017

Edellisessä artikkelissani Tunnuslukuja Exceliin kerroin miten lasket Pythonilla käden käänteessä aineiston tunnusluvut ja korrelaatiot ja tallennat tulokset Exceliin. Frekvenssitaulukoiden laskenta edellyttää enemmän Python-taitoja. Frekvenssitaulukot voin laskea Pythonissa monella eri tavalla. Yrityksen ja erehdyksen kautta olen itse päätynyt seuravassa kuvaamaani tapaan, mutta helpompia ja tehokkaampiakin tapoja voi olla olemassa.

Aluksi otan käyttöön pandas-ohjelmakirjaston, avaan aineiston ja alustan ExcelWriterin kirjoittamaan frekvenssit.xlsx -nimiseen tiedostoon. Tämä kaikki on sinulle toivottavasti tuttua jo edellisestä artikkelistani.

import pandas as pd
df=pd.read_excel('http://www.elisanet.fi/taanila/data1.xlsx',sheetname='Data')
writer = pd.ExcelWriter('frekvenssit.xlsx', engine='xlsxwriter')

Seuraavaksi alustan muuttujan rivi arvoksi 0. Tätä muuttujaa tarvitsen osoittaessani mille Excel-taulukon riville tietoja milloinkin kirjoitan. Aloitan arvosta 0, koska Python numeroi rivit alkaen arvosta 0.

rivi=0

Hyödynnän for-toistorakennetta käydäkseni läpi kaikki aineiston muuttujat (sarakkeet). Tällä tavalla toteutettuna voin käyttää ohjelmaa minkä tahansa aineiston kanssa. For-rakenteen aloitan komennolla:

for var in df:

For-rakenne käy läpi yksi kerrallaan kaikki dataframesta df löytyvät muuttujat (sarakkeet). Muuttujan nimen var sijasta olisin voinut käyttää mitä tahansa keksimääni nimeä. Kaksoispiste on pakollinen ja merkitsee for-rakenteen aloitusta. Kaksoispisteen jälkeen kaikki ohjelmarivit, jotka ovat sisennettyjä, sisältyvät for-rakenteeseen. Monissa muissa ohjelmointikielissä for-rakenne lopetetaan erillisellä komennolla, mutta Pythonissa for-rakenne loppuu kun sisennetyt ohjelmarivit loppuvat. Jos kopioit alla olevat rivit suoraan omaan ohjelmaasi, niin muista sisentää rivit. Minä sisällytin for-rakenteeseen seuraavat ohjelmarivit:

df1=pd.crosstab(df[var],'lkm')
df1=df1.assign(prosenttia=df1/df1.sum())
df1.loc['Yhteensä']=df1.sum()
df1.to_excel(writer, sheet_name='Frekvenssit', startrow=rivi)
rivi=rivi+df1.shape[0]+2

Käytän crosstab-toimintoa lukumäärien laskemiseen. Crosstab on tässä kätevä, koska sen tuloksena syntyy dataframe, jonka voin helposti kirjoittaa Excel-taulukkoon. Nimeän syntyvät dataframen nimellä df1.

Assign-toiminnolla lisään df1-dataframeen uuden sarakkeen, johon lasken prosentit. Huomaa, miten kätevästi voin suorittaa laskennan dataframessa.

df1.loc[’Yhteensä’]=df1.sum() lisää df1-dataframen viimeiseksi riviksi summa-rivin.

Seuraavaksi kirjoitan df1-dataframen Frekvenssit-taulukkovälilehdelle alkaen rivi-muuttujan osoittamalta riviltä.

Seuraavaksi kasvatan rivi-muuttujan arvoa, jotta seuraava frekvenssitaulukko kirjoitetaan edellisen alapuolelle siten että väliin jää yksi tyhjä rivi. Dataframen df1 rivimäärän saan selville df1.shape[0] -komennolla. Komento df1.shape[1] kertoisi vastaavasti dataframen df1 sarakkeiden määrän.

Lopuksi vaihdan prosentteja sisältävälle sarakkeelle prosenttimuotoilun. Seuraavia ohjelmarivejä en enää sisennä, koska en halua toistaa niitä jokaisella for-rakenteen toistokerralla:

format = writer.book.add_format({'num_format': '0.0%'})
writer.sheets['Frekvenssit'].set_column('C:C',None, format)

Aluksi lisään writer.book oliolle prosenttimuotoilun, jossa on yksi desimaali. Tämän jälkeen asetan kyseisen muotoilun Frekvenssit-taulukkovälilehden C-sarakkeelle.

Kaiken päätteeksi tietenkin vielä tallennan edellä muodostetun Excelin:

writer.save()

Tämän jälkeen voin avata frekvenssit.xlsx-tiedoston Exceliin ja tutustua aikaansaannokseeeni.

Huomaa, että edellä kuvaamani ohjelma toimii sellaisenaan mille tahansa tilastoaineistolle kunhan vaihdan aineiston avaamiskomennossa tiedostonimen ja polun, josta tiedosto löytyy.

Mainokset

Tunnuslukuja Exceliin

Päivitetty 18.9.2017

Vaikka käyttäisit Exceliä, niin jotkin asiat sujuvat nopeammin Pythonin kautta. Seuraavassa kerron, miten lasket Pythonilla tunnuslukuja ja korrelaatioita Excel-aineistosta ja kirjoitat tulokset Excel-tiedostoon. Tämä sujuu hämmästyttävän helposti.

Otan käyttöön Pandas-ohjelmakirjaston ja avaan Excel-tiedoston:

import pandas as pd
df=pd.read_excel('http://www.elisanet.fi/taanila/data1.xlsx',sheetname='Data')

Alustan ExcelWriterin kirjoittamaan uuteen Excel-tiedostoon:

writer = pd.ExcelWriter('tunnusluvut.xlsx', engine='xlsxwriter')

Lasken tunnuslukuja describe()-funktiolla ja kirjoitan tunnusluvut Excel-tiedostoon Tunnusluvut-taulukkovälilehdelle:

df.describe().to_excel(writer, sheet_name='Tunnusluvut')

Lasken korrelaatioita corr()-funktiolla ja kirjoitan korrelaatiot Excel-tiedostoon Korrelaatiot-taulukkovälilehdelle:

df.corr().to_excel(writer, sheet_name='Korrelaatiot')

Lopuksi tallennan ja suljen Excel-tiedoston:

writer.save()
writer.close()

Tämän jälkeen voin avata Jupyterin oletuskansiosta tunnusluvut.xlsx-tiedoston Exceliin ja hämmästellä mitä sain aikaan näin vähällä vaivalla.

Pythonilla voin myös muotoilla Exceliin kirjoitettavat tiedot ja jopa luoda Excel-kaavioita. Lisätietoa ExcelWriterista: http://xlsxwriter.readthedocs.io/working_with_pandas.html

Aineiston avaaminen

Päivitetty 18.9.2017

Aineisto, joka on jo analysointiin sopivassa muodossa, on helppo avata. Lue lisää analysointiin sopivasta muodosta artikkelista Tilastoaineiston tallentaminen.

Pandas-ohjelmakirjasto sisältää read_excel ja read_csv funktiot Excel- ja csv-aineistojen avaamiseen. Ohjelmakoodissa otan Pandas-ohjelmakirjaston käyttöön komennolla

import pandas as pd

Pelkkä import pandas riittäisi, mutta yleistyneen käytännön mukaisesti määritän lyhennettä pd käytettäväksi pandas-nimen sijasta.

Aineiston luen Dataframe-olioon. Dataframe-olio on tarkoitettu taulukkomuotoisen tiedon tallentamiseen ja käsittelyyn.

Seuraavassa luen Excel-tiedoston sisältämän aineiston. Tiedoston nimi on data1.xlsx ja aineisto löytyy Data-taulukkovälilehdeltä. Aineiston tallennan dataframe-olioksi, jonka nimeksi annan df1.

df1=pd.read_excel('http://www.elisanet.fi/taanila/data1.xlsx',sheetname='Data')

Voin tarkastella dataframeen luettua aineistoa komennolla

df

Jupyterissa edellä kuvattu näytti seuraavalta:

python1

Seuraavassa avaan paikalliselta levyltä csv-muotoisen aineiston. Esimerkin tapauksessa aineistossa käytetään pilkkuerottelun sijasta puolipistettä. Tämä on usein tilanne suomalaisilla asetuksilla tallennetuissa csv-tiedostoissa. Lopuksi tulostan aineiston 3 ensimmäistä riviä head()-funktiolla.

df2=pd.read_csv('C:/Users/taaak/data1.csv',delimiter=';')
df2.head(3)

Jos halaut testata, niin löydät csv-aineiston osoitteesta http://www.elisanet.fi/taanila/data1.csv

Aineston avaamisen yhteydessä voit tehdä monia lisäasetuksia. Edellä käytimme vain lisäasetuksia sheetname ja delimiter. Lue lisää

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html

Jupyter

Päivittty 18.9.2017

Jupyter on kätevä väline Python-ohjelmien testaamiseen ja dokumentointiin.

Jupyterin käynnistät varmimmin Anaconda Navigatorin kautta. Jupyterin käynnistäminen onnistuu myös suoraan ilman Anaconda Navigatorin käynnistämistä, mutta ainakin minulla on Windows 10 -koneessani ollut tämän kanssa aika ajoin ongelmia. Jupyter käynnistyy oletusselaimeesi Home-nimiselle välilehdelle.

Voit luoda uuden tyhjän notebookin Jupyterin etusivun oikean yläreunan New – Python 3 -toiminnolla. Uusi notebook avautuu uudelle välilehdelle.

jupyter1

Notebookin yläreunasta löydät työkalupainikkeita ja valikoita, joiden sisältöön kannattaa heti alussa tutustua. Notebookin voit nimetä uudelleen File-valikon Rename-toiminnolla.

Jupyter-notebook koostuu soluista. Koodisoluun voit kirjoittaa Python-koodia ja kommentteja. Kommenttirivi alkaa aina #-merkillä. Koodin voit suorittaa muiden muassa Cell-valikon kautta. On myös hyvä muistaa näppäinyhdistelmä ctrl-enter, jolla voit suorittaa aktiivisen solun koodin. Lisää pikanäppäimiä löydät Googlaamalla hakusanoilla Jupyter keyboard shortcuts.

jupyter2

Koodin suorituksesta mahdollisesti seuraavat tulosteet tai virheilmoitukset tulostuvat solun alapuolelle. Voit milloin tahansa muuttaa solun koodia ja suorittaa koodin uudelleen.

Uusia soluja voit lisätä Insert-valikon kautta ja soluja voit tuhota Edit-valikon kautta.

Aiemmin suorittamasi koodin tallentamat muuttujat ym. tiedot säilyvät koko istunnon ajan. Voit näin ollen suorittaa osan ohjelmasta yhdessä solussa ja jatkaa ohjelman suoritusta toisessa solussa.

Tarvittaessa voit tyhjentää istunnon tallentamat tiedot valitsemalla Kernel-valikosta Restart & Run All. Jokaisella notebookilla on oma Kernel ja Restart & Run All  vaikuttaa ainostaan aktiiviseen notebookiin. Työskentelyn lopuksi kannattaa valita File-valikosta Close and Halt. Pelkkä välilehden sulkeminen jättää ohjelman suorituksen kesken eikä poista muistiin tallennettuja ohjelman tietoja.

Notebook tallentuu Jupyterin oletuskansioon. Minun Windows 10 -koneella oletuskansiona on C:/Users/’käyttäjänimi’/. Jupyter notebook -tiedoston tunnistat tiedostonimen loppuosasta .ipynb. Tallentamasi notbookit löydät Jypyterin etusivulta (Home-välilehti) ja voit avata notebookin omalle välilehdelleen napsauttamalla notebookin nimeä.

Otettuasi Jupyterin käyttöön voitkin harjoitella Pythonin perusasioita vaikkapa Teemu Sirkiän mainion materiaalin avulla: http://www.cs.hut.fi/~ttsirkia/Python.pdf

Asenna Anaconda

Päivittty 17.9.2017

Anaconda on valmis paketti Pythonin käyttöön datojen analysoinnissa. Anaconda sisältää kaiken tarvittavan:

  • Python-tulkin.
  • datojen analysointiin tarvittavat ohjelmakirjastot
  • grafiikan luontiin tarvittavat ohjelmakirjastot
  • Jupyter-notebookin ohjelmien testaamiseen ja dokumentointiin.

Anacondan voit asentaa koneellesi vaikka sinulla ei olisikaan koneen pääkäyttäjän oikeuksia.
Asennuspaketin löydät Windowsille, Macille ja Linuxille osoitteesta

https://www.anaconda.com/download/

Asenna uusin versio (tätä kirjoittaessani Python 3.6). Asennuksen voit tehdä oletusvalinnoilla.

Käynnistä Anaconda Navigator -niminen ohjelma. Anaconda Navigatorin kautta voit käynnistää Jupyter-notebookin.

Seuraavassa artikkelissani kirjoitan Jupyter-notebookin käytöstä ohjelmien kirjoittamiseen, testaamiseen ja tallentamiseen.

Määrälliset muuttujat pivot-kaaviona

Päivitetty 9.12.2015

Olethan opetellut artikkelissa Pivot-kaaviot kuvatut toimet ennen tämän artikkelin lukemista?

Määrälliset muuttujat mittaavat määrää: euroja, vuosia, metrejä, kiloja jne.

Seuraavien esimerkkien aineistot ja pivot-taulukot löydät tiedostosta pivotchart3.xlsx.

Ryhmittely

Jos haluan laskea määrällisen muuttujan arvojen esiintymiskertoja, niin yleensä tarvitsen ryhmittelyä. Tarkastelen esimerkkinä ikäjakauman esittämistä. Esimerkkiaineistona käytän tiedoston pivotchart3.xlsx Data-aineistoa.

  • Valitsen yhden ja vain yhden solun aineiston alueelta.
  • Valitsen Insert-välilehdeltä PivotChart. Jos en vaihda Create PivotChart -ikkunan asetuksia, niin pivot-kaaviota varten muodostuu uusi taulukko (Sheet). Uudessa taulukossa ovat paikkavaraukset pivot-taulukolle ja pivot-kaaviolle.
  • Pivot-kaavion rakenteen määrittelen PivotChart Fields -kenttäluettelossa, joka on näkyvillä pivot-kaavion ollessa valittuna.
  • Raahaan ikä-muuttujan Values-ruutuun.
  • Vaihdan laskentaperusteeksi Sum sijasta Count.
  • Raahaan ikä-muuttujan Axis (Categories) -ruutuun.
  • Valitsen pivot-taulukosta solun, jossa on ensimmäinen ikä (20).
  • Valitsen Analyze-välilehdeltä Group Field (Ryhmän kenttä).

pivotkaavio11

  • Grouping-ikkunassa voin tarvittaessa vaihtaa Excelin ehdottamia ryhmittelyn aloituskohtaa, päättymiskohtaa ja ryhmävälin suuruutta.

Räätälöity ryhmittely

Edellä kuvaamallani Group Field -toiminnolla saan vain tasavälisiä ryhmittelyitä, joissa ryhmävälin suuruus on sama kaikissa ryhmissä. Seuraavassa määrittelen iälle ryhmittelyn 20-29, 30-39, 40-49, 50+. Jatkan suoraan edellisen esimerkin pivot-kaaviosta.

  • Poistan aiemman iän ryhmittelyn (valitsen pivot-taulukosta ensimmäisen ikäryhmän solun ja valitsen Analyze-Ungroup (Pura ryhmittely).
  • Valitsen pivot-taulukosta solut, joissa on iät 20-29.
  • Valitsen Analyze-Group Selection (Ryhmän valinta).
  • Valitsen pivot-taulukosta solut, joissa on iät 30-39.
  • Valitsen Analyze-Goup Selection.
  • Toistan edellä kuvattuja vaiheita kunnes kaikki ryhmät on luotu.

Excel nimeää ryhmät Group1, Group2, jne. Voin kirjoittaa nimien tilalle kuvaavammat nimet. Ryhmän nimen vieressä on -/+ -painike, josta voin piilottaa tai näyttää ryhmän yksityiskohtaiset tiedot.

Seuraavassa olen nimennyt ensimmäisen ryhmän 20-29 ja olen piilottanut kolmen ensimmäisen ryhmän yksityiskohtaiset tiedot. Huomaa, että pivot-kaavio näyttää tiedot sellaisena kuin ne ovat pivot-taulukossa.

pivotkaavio12

Itselläni on tapana tehdä ryhmittelyt etukäteen alkuperäiseen aineistoon. Lue lisää artikkelista Muuttujan arvojen ryhmittely ja muuttaminen.

Keskiarvo

Seuraavassa lasken palkkakeskiarvot koulutuksen mukaan:

  • Valitsen yhden ja vain yhden solun aineiston alueelta.
  • Valitsen Insert-välilehdeltä PivotChart. Jos en vaihda Create PivotChart -ikkunan asetuksia, niin pivot-kaaviota varten muodostuu uusi taulukko (Sheet). Uudessa taulukossa ovat paikkavaraukset pivot-taulukolle ja pivot-kaaviolle.
  • Pivot-kaavion rakenteen määrittelen PivotChart Fields -kenttäluettelossa, joka on näkyvillä pivot-kaavion ollessa valittuna.
  • Raahaan palkka-muuttujan Values-ruutuun.
  • Vaihdan laskentaperusteeksi Count sijasta Average.
  • Raahaan koulutus-muuttujan Axis (Categories) -ruutuun.
  • Siirrän pivot-taulukon Peruskoulu-solun 2. aste -solun yläpuolelle, jolloin koulutukset menevät koulutuksen pituuden mukaiseen järjestykseen.

Viimeistelyn jälkeen pivot-kaavio voisi näyttää seuraavalta:

pivotkaavio13

Vastausten lukumäärät (n) kirjoitin riviotsikoihin. Sain lukumäärät selville vaihtamalla hetkeksi laskentaperusteeksi Average sijasta Count.

Summa

Summa on käyttökelpoinen yhteenvetotapa esimerkiksi myyntitietojen yhteydessä. Seuraavassa käytän tiedoston pivotchart3.xlsx aineistoa Myynnit.

  • Valitsen yhden ja vain yhden solun aineiston alueelta.
  • Valitsen Insert-välilehdeltä PivotChart. Jos en vaihda Create PivotChart -ikkunan asetuksia, niin pivot-kaaviota varten muodostuu uusi taulukko (Sheet). Uudessa taulukossa ovat paikkavaraukset pivot-taulukolle ja pivot-kaaviolle.
  • Pivot-kaavion rakenteen määrittelen PivotChart Fields -kenttäluettelossa, joka on näkyvillä pivot-kaavion ollessa valittuna.
  • Raahaan Hinta-muuttujan Values-ruutuun. Laskentaperuste on valmiiksi Sum, koska jokaisella rivillä on arvo Hinta-muuttujalla.
  • Raahaan Myyjä-muuttujan Axis (Categories) -ruutuun.

Voin järjestää myyntisummat suuruusjärjestykseen valitsemalla Pivot-taulukon tai Pivot-kaavion pudotusvalikosta More Sort Options ja järjestämällä Sum of Hinta -mukaiseen järjestykseen. Viimeistelyn jälkeen pivot-kaavio voisi näyttää seuraavalta:

pivotchart21

Monivalinta pivot-kaaviona

Päivitetty 26.11.2015

Olethan opetellut artikkelissa Pivot-kaaviot kuvatut toimet ennen tämän artikkelin lukemista?

Monivalintakysymyksessä tarjotaan useita vaihtoehtoja, joista vastaaja voi valita useammankin kuin yhden. Jokainen monivalinnan vaihtoehto tallennetaan aineistoon omana sarakkeenaan. Jos vaihtoehto on valittu, niin aineistossa on arvo 1, muussa tapauksessa aineistoon voidaan jättää tyhjä kohta.

Seuraavassa käytän esimerkkinä kyselytutkimusaineistoa, jossa on kysytty työntekijän hyödyntämiä etuisuuksia: työterveyshuolto, lomaosake, kuntosali, hieroja. Esimerkkiaineisto ja pivot-kaaviot löytyvät tiedostosta pivotchart2.xlsx.

  • Valitsen yhden ja vain yhden solun aineiston alueelta.
  • Valitsen Insert-välilehdeltä PivotChart. Jos en vaihda Create PivotChart -ikkunan asetuksia, niin pivot-kaaviota varten muodostuu uusi taulukko (Sheet). Uudessa taulukossa ovat paikkavaraukset pivot-taulukolle ja pivot-kaaviolle.
  • Pivot-kaavion rakenteen määrittelen PivotChart Fields -kenttäluettelossa, joka on näkyvillä pivot-kaavion ollessa valittuna.
  • Raahaan työterveyshuolto-muuttujan Values-ruutuun.
  • Raahaan lomaosake-muuttujan Values-ruutuun.
  • Raahaan kuntosali-muuttujan Values-ruutuun.
  • Raahaan hieroja-muuttujan Values-ruutuun.

pivotkaavio7

Tässä vaiheessa lasketut arvot ovat pivot-taulukossa sarakkeittain, koska Excel on sijoittanut Values-palikan Legend (Series)-ruutuun. Pivot-kaaviossa tämä näkyy siten, että jokainen pylväs muodostaa oman arvosarjansa ja näkyy omalla värillään.

  • Raahaan Legend (Series)-ruudun Values-palikan Axis (Categories)-ruutuun.
  • Kirjoitan pivot-taulukkoon monivalinnan vaihtoehtojen nimet nimien Count of työterveyshuolto jne. tilalle. Excel ei kelpuuta muuttujan nimenä esiintyvää työterveyshuolto pivot-taulukon riviotsikoksi, joten lisään nimen perään välilyönnin.

pivotkaavio9

Yleensä monivalinnan pylväät kannattaa järjestää pituusjärjestykseen.

  • Napsautan hiiren oikeaa painiketta jonkin pivot-taulukon lukumäärän päällä ja valitsen Sort – Sort Largest to Smallest.

Pienen viimeistelyn jälkeen pivot-kaavio voisi näyttää seuraavalta.

pivotkaavio10

Jos haluan tarkastella asiaa ryhmittäin esimerkiksi sukupuolen mukaan, niin voin vielä raahata ryhmittelevän muuttujan Legend (Series) -ruutuun.