Kuidas riigiraha.fin.ee andmete abil asutustevahelisi rahavooge vaadata

Tartu Ülikooli Infotehnoloogia mõju-uuringute keskuse (CITIS) üks tegevusi on avaandmete kasutamise populariseerimine tudengite ja avalikkuse seas. Seekord vaataks, kuidas saab kergesti kasutada riigiraha.fin.ee lehel toodud valitsussektorisse kuuluvate üksuste raamatupidamise andmeid asutustevaheliste seosete visualiseerimiseks ja analüüsimiseks. Näite toon enda lemmikuurimisvaldkonnast – Eesti tervishoiust.

Järgmisel joonisel on esitatud riigiraha andmete põhjal Eesti haiglatevahelised rahavood aastal 2013, mis on tasutud teineteisele tervishoiuteenuse osutamise eest. (Joonisel on esitatud nooled vaid juhul, kui aastased summad on suuremad kui 10 tuhat eurot.)

tulemus

Nagu arvata võiski on absoluutsummadena suuremad tervishoiuteenuste osutamise rahavood ka suuremate haiglate vahel: Lastehaiglast ja Lääne-Tallinna Keskhaiglast PERHi; PERHist ja Ida-Viru Keskhaiglast TÜ Kliinikumi: TÜ Kliinikumist PERHi ja Ida-Tallina Keskhaiglasse jne.

Selle joonise alusandmete kättesaamine ja lihtsama joonise tegemine on tegelikult imelihtne, eriti kui teil on sama nupukad ja abivalmis kolleegid nagu mul.

Joonise tegemise esimeseks sammuks on tõmmata vajalikud lähteandmed sobival kujul alla riigiraha lehelt. Näiteks kasutades järgmist otseaadressi, saab andmed CSV või Exceli kujul alla laadida hiire paremkliki abil. (href=http://riigiraha.fin.ee/geoqlik/proxy/QvAJAXZfc/singleobject.htm?document=Riigiraha.qvw&host=local&anonymous=true&object=CH320&select=AASTA,(2013)&select=PERIOOD,1&select=UKSUSID,015312,016303,016305,016306,100307,100308,100309,179302, 185304,310307,357301,358301,402306,561306,588303,591303,594303,599303) (NB! Kui klikid, siis oota, andmete laadimine tabelisse võtab aega ligi 10-15 sekundit!)

Ülal toodud aadressi olulisteks komponentideks on:

  • objekti nimetus (object) – Meie näites CH320, kus on toodud kõik asutuste väljaminekud. Vajatava tabeli numbri leidmine on pisut keeruline, sest nende loetelu ma ei suutnud riigiraha.fin.ee lehelt leida. Meie saime numbri esmalt otse rahandusministeeriumist. Hiljem kirjutasin Pythonis koodi, mis otsib aadressidelt tabelite moodi lehed üles ja teeb meile võimaliku tabelite sisukorra.
  • aasta (AASTA) – meie näites 2013.
  • andmete sagedus (PERIOOD) – võib olla kas aasta nagu meie näites (1), kvartal (2) või kuu (3).
  • üksused (UKSUSID), mille kohta soovime infot saada – meie näites on loetletud valitsussektorisse kuuluvate haiglate koodid. Need koodid leiab üles saldo.fin.ee-l lehel asuvast failist Tehingupartnerid.xls

Kui andmete allalaadimist tuleb teha sageli või on soov katsetada eri perioode ja üksusi, siis saab andmete allalaadimist ka mõningal määral automatiseerida. Näiteks mina jooksutasin selleks Chrome’i veebilehitsejat, mis Seleniumi abiga Pythonis automaatselt Exceli faili alla laadis (minu näitefail). Otse veebilehelt andmeid R-i mugavalt ei õnnestunud laadida. Kui mõni R-i friik selle ära lahendab, andke teada.

Allalaetud Exceli andmefail ise näeb välja järgmisel kujul:

 sisendandmedexcelis

 

Kui andmed on allalaetud, siis on vaid vaja mõne tarkvaraga neid sobivalt filtreerida ja agregeerida ning ongi tulemus käes.Kõige kiirem on seda teha muidugi Excelis tavapärase Pivot tabeliga, kasutades sobivaid filtreid.

rahavoodexcel2.png

Kuid kui analüüsi teha sageli ning tahta ka tulemusi graafiliselt kuvada, siis võib kasutada R-i abi, mida omal viisil olen allosas püüdnud teha. (Palun R-i ekspertidel olla kriitikaga leebe kuid konstruktiivne).

R-i vajalike lisapakettidena läheb vaja, midagi millega Exceli fail alla laadida, nt  readxl. Graafiku tegemiseks kasutame lisapaketti igraph. Ning andmete teisendamiseks muidugi paketti dplyr .

#Exceli importimiseks
install.packages("devtools")
devtools::install_github("hadley/readxl")
#Andmete töötlemiseks
install.packages("dplyr")
#Jooniste kuvamiseks
install.packages("igraph") 
library(readxl)
library(dplyr)
library(igraph)
#avame ülaltoodud riigiraha.fin.ee aadressilt allalaetud Exceli faili
sisendfail<-read_excel("andmed2013.xls")

Paketi igraphpuhul on vajalik, et andmed oleksid esitatud voogude failis, kus esimeses kahes veerus on objektide id tunnused, kust vood algavad ja kuhu lähevad ning soovi korral järgnevates veergudes vooge iseloomustavad näitajad.

Voogude faili struktuur

from to Voo suurus
Id1 Id2 Summa1
Id2 Id3 Summa2
Id2 Id1 Summa3

Teine fail, mida on otstarbekas kasutada, on objekte kirjeldav fail, kus esimeses veerus peab olema id tunnus ja järgmistes muud omadused, mida tahame joonisel kuvada, näiteks asutuse nimi ja suurus.

Otspunktide faili struktuur

asutuseID nimi lisatunnus
Id1 PERH Käive1
Id2 SA TÜ Käive2
Id3 ITK Käive3

Alljärgnevalt ongi tehtud riigiraha lehelt alla laetud Exceli failist mõlemad otspunktide ja voogude failid. Esmalt tuleb tähele panna, et alla laetud lähtefailis oli lisaridasid kommentaaridega või kogusummaga, millest soovime lahti saada. Seetõttu kasutame filtrit, et jätame alles vaid need read, kus on olemas meie poolt valitud aastaarv. Lisaks lühendame natuke muutujate nimesid.

Sisseloetud Exceli fail näeb R-s välja järgmine.

head(sisendfail)
## # A tibble: 6 × 10
##   Aasta   Kuu                                 Üksus
##                                     
## 1 Kokku Kokku                                 Kokku
## 2  2013    12         179302 SA Ida-Viru Keskhaigla
## 3  2013    12 016305 SA Põhja-Eesti Regionaalhaigla
## 4  2013    12         179302 SA Ida-Viru Keskhaigla
## 5  2013    12         179302 SA Ida-Viru Keskhaigla
## 6  2013    12             016306 SA Viljandi Haigla
## # ... with 7 more variables: Konto , Valdkond ,
## #   Tegevusala , Tehingupartner , Finantseerimisallikas ,
## #   Rahavoog , `Väljaminekud absoluutarv tuhandetes` 
#esmane puhastus
andmed%
  filter(grepl("2013", Aasta)) %>%
  select(Üksus, Tehingupartner, `Väljaminekud absoluutarv tuhandetes`, Konto)
#kui jäi puuduvaid väärtuseid sisse, viskame välja
andmed=na.omit(andmed)
#ja uued pealkirjad
colnames(andmed)<-c("yksus", "partner", "makse", "konto")

Seejärel saame valmis teha objekte kirjeldava andmestiku nodes. Jätame alles id-tunnuse, haigla nime pisut puhastatud kujul ja teeme ka haigla väljaminekute kogusumma, et pärast oleks parem joonisel kuvada haiglate suurust.

#leiame haigla suurust iseloomustava näitaja
nodes%
  select(yksus, makse) %>%
  group_by(yksus) %>%
  summarize(suurus=sum(makse))
#haigla tekstiline nimi algab peale numbrit
nodes$nimi<-substr(nodes$yksus, 7, 100)
#haigla nimest viskame välja lühendid AS, SA ja As 
nodes$nimi<-gsub("SA", "", nodes$nimi)
nodes$nimi<-gsub("AS", "", nodes$nimi)
nodes$nimi<-gsub("As ", "", nodes$nimi)  
nodes$id=substr(nodes$yksus, 1, 6)  
#ja jätame alles vajalikud muutujad ning järjestame id järgi, 
#et oleks mugav vaadata  
nodes <- nodes %>% select(id, nimi, suurus) %>% arrange(id) 
#tulemuseks on haiglate loetelu id ja väljamaksete kogusummaga (tuh eurot)
nodes
## # A tibble: 17 × 3
##        id                          nimi     suurus
##                                    
## 1  015312                Hiiumaa Haigla   2071.850
## 2  016303      Tartu Ülikooli Kliinikum 141248.552
## 3  016305   Põhja-Eesti Regionaalhaigla 149405.065
## 4  016306               Viljandi Haigla  14935.021
## 5  100307      Ida-Tallinna Keskhaigla   78389.361
## 6  100308    Lääne-Tallinna Keskhaigla   44014.310
## 7  100309          Tallinna Lastehaigla  19759.723
## 8  179302           Ida-Viru Keskhaigla  32389.628
## 9  185304                 Narva Haigla   19080.819
## 10 310307                  Pärnu Haigla  34706.197
## 11 357301                 Põlva Haigla    4884.977
## 12 402306            Kuressaare Haigla    8386.363
## 13 561306                 Valga Haigla    5656.267
## 14 588303              Järvamaa Haigla    6775.377
## 15 591303                Rakvere Haigla  14417.688
## 16 594303          Rapla Maakonnahaigla   4489.612
## 17 599303            Lõuna-Eesti Haigla   7819.659

Seejärel teeme valmis haiglatevaheliste tervishoiuteenuste vaheliste voogude andmestiku. Alljärgnevalt on lihtsuse huvides kuvatud joonisel ainult aastaseid rahavoogusid, mis on suuremad kui 10 000 eurot.

#leiame üksuse ja partneri id numbrid nimest
andmed$from=substr(andmed$yksus, 1, 6)
andmed$to=substr(andmed$partner, 1, 6)

#vaja jätte alles id-d ja summad ning agregeerida, 
#nii et jäävad alles vaid omavahelised. 
links %>%
  #jääb alles vaid tervishoiuteenuste eest maksmine
  filter(grepl("552230 Tervishoiuteenused", konto)) %>%
  #valime vajalikud tunnused
  select(from, to, makse) %>%
  #summeerime vood konkreetsete haiglate vahel
  group_by(from, to) %>%
  summarize(vood=sum(makse)) %>%
  #jätame alles vaid need vood, kus ka tehingupartneriks oleks haigla, 
  #kuid mitte ise
  filter(to %in% andmed$from,to!=from) %>%
  #jätame alles vaid vood, kus aastane summa on vähemalt 10000 eurot
  filter(vood>10)

#tulemus
links
## Source: local data frame [46 x 3]
## Groups: from [17]
## 
##      from     to      vood
##            
## 1  015312 016305  10.68026
## 2  015312 100307  24.93643
## 3  016303 016305  91.25026
## 4  016303 100307  25.91082
## 5  016303 100308  10.53907
## 6  016303 100309  10.00512
## 7  016303 179302  71.27568
## 8  016303 599303  15.54878
## 9  016305 016303 393.11765
## 10 016305 100307  15.29962
## # ... with 36 more rows

Kasutades lisapaketti igraph saame kujutada haiglate vahelisi seoseid graafiliselt või vaadata maatrikskujul. Käsus anname info, mis on seoste fail (d=links), mis on otspunktide fail (vertices=nodes) ja kas tahame nooltega jooni. Joonisel saab hiljem soovi korral erinevaid seadeid muuta.

#teisendame failid igraph objektiks
haiglatevork <- graph_from_data_frame(d=links, vertices=nodes, directed=TRUE) 

Võimalikke paigutuse tüüpe on palju. Sõltuvalt objektide ja seoste arvust saab leida sobivaima graafiku. Alljärneval joonisel on joone paksus võrdeline rahavoo suurusega ja haiglat iseloomustava ringi suurus positiivses seos haigla summaarsete väljamaksetega.

#üks võimalik joonis
par(mfrow=c(1,1), mar=c(0.5,0.5,0.5,0.5))
#et graafik oleks reprotseeritav
set.seed(3000)
plot(haiglatevork, layout=layout_randomly, edge.arrow.size=.3, 
edge.curved=1, edge.width=links$vood/100, edge.color="blue", 
vertex.size=nodes$suurus^(1/4),vertex.color="orange", 
vertex.label=nodes$nimi, vertex.label.color="black", vertex.label.cex=0.6) 

ver5

Käsitsi saab jooni ja otspunkti tõmmata paremini graafikul, mis on saadud käsuga tkplot. Üks võimalikke realisatsioone ongi esitatud sissekande alguses .

tkplot(haiglatevork, layout=layout_randomly, edge.arrow.size=.7, 
edge.curved=1,   edge.width=links$vood/50, edge.color="blue", 
vertex.size=nodes$suurus^(1/3), vertex.color="orange", 
vertex.label=nodes$nimi,vertex.label.color="black", vertex.label.cex=1) 

Analoogselt saab siis ilma suurema vaevata analüüsida ja kuvada ka teiste valitsemissektorisse kuuluvate asutuste omavahelisi rahavooge kasutades riigiraha.fin.ee andmeid.

Käesoleva postituse valmimisele olid mulle suureks abiks Taavi Unt ja Märten Veskimäe CITISest ning Eesti kõige entusiastlikuma R-i õpetaja Indrek Seppo konspektid. Finantstoe eest tänan Tartu Ülikooli rektori fondi.

Advertisements
Rubriigid: Näpunäide, Uncategorized. Salvesta püsiviide oma järjehoidjasse.

Lisa kommentaar

Täida nõutavad väljad või kliki ikoonile, et sisse logida:

WordPress.com Logo

Sa kommenteerid kasutades oma WordPress.com kontot. Logi välja /  Muuda )

Google+ photo

Sa kommenteerid kasutades oma Google+ kontot. Logi välja /  Muuda )

Twitter picture

Sa kommenteerid kasutades oma Twitter kontot. Logi välja /  Muuda )

Facebook photo

Sa kommenteerid kasutades oma Facebook kontot. Logi välja /  Muuda )

Connecting to %s