Aineiston lataus ja käsittely

Ladataan aluksi aineisto ja poistetaan ne rivit, joissa on epävalidi y-tunnus hetu-paketin bid_ctrl-funktiota hyödyntämällä:

library(hetu)
library(dplyr)
helsingin_ostot <- read.csv("http://openspending.hel.ninja/files/ostot/helsingin-ostot-all.csv")
helsingin_ostot$valid_ytunnus <- bid_ctrl(helsingin_ostot$toimittaja_ytunnus)
helsingin_ostot2 <- helsingin_ostot[which(helsingin_ostot$valid_ytunnus),]

Tässä vaiheessa prosessia PRH:n APIsta ladattaisiin uniikit y-tunnukset (jotta saman y-tunnuksen tietoja ei ladattaisi turhaan useaan kertaan) ja yhdistettäisiin nämä tiedot ostodatan kanssa. Koska tietojen lataaminen y-tunnusten perusteella on kohtuuttoman aikaavievä operaatio, olen käyttänyt tässä kirjoituksessa etukäteen valmisteltua datasettiä. Seuraava koodinpätkä on ainoastaan esimerkkinä prosessista:

# Uniikit y-tunnukset sisältävä vektori
unique_ytunnus <- unique(helsingin_ostot2$toimittaja_ytunnus)

# Esimerkki: Yhden y-tunnuksen tietojen lataaminen ja haluttujen tietojen suodattaminen

yrityksen_tiedot <- jsonlite::fromJSON("http://avoindata.prh.fi/tr/v1/0494571-4", simplifyVector = TRUE)
poimitut_tiedot <- NULL
poimitut_tiedot$businessId <- yrityksen_tiedot$results$businessId
poimitut_tiedot$street <- yrityksen_tiedot$results$addresses[[1]]$street[1]
poimitut_tiedot$city <- yrityksen_tiedot$results$addresses[[1]]$city[1]
poimitut_tiedot$postCode <- yrityksen_tiedot$results$addresses[[1]]$postCode[1]

poimitut_tiedot <- as.data.frame(poimitut_tiedot)

helsingin_ostot3 <- left_join(x = helsingin_ostot2, y = poimitut_tiedot, by = c("toimittaja_ytunnus" = "businessId"))

Kun y-tunnuksia on enemmän kuin yksi, yllä olevasta koodista voidaan muodostaa oma funktionsa, jota voidaan hyödyntää lapply-funktion kanssa.

Mikäli yritykselle oli saatavissa postinumeroalue, sille oli pääsääntöisesti tallennettu myös tieto kadusta ja kaupungista. Aineiston kattavuutta voidaan näin ollen tarkastella yksinkeraisesti aineistossa olevien ja puuttuvien postinumeroiden suhdelukuna, joka visualisoidaan pylväsdiagrammeilla.

# Ladataan valmisteltu datasetti
load("~/helsingin_ostot3.RData")
library(ggplot2)
ggplot(helsingin_ostot3, aes(fill=is.na(postCode), x=year)) +
  geom_bar(position="stack", stat="count") +
  labs(x = "Vuosi", y = "Rivien lkm", fill = "Puuttuva \npostinumero")

Huomataan, että puuttuvien postinumeroiden osuus pienenee mitä lähemmäksi nykyhetkeä tulemme.

Top-20 -lista: Mistä kunnista Helsinki ostaa eniten

Yrityksen postinumeron tietäminen on hyvä lähtökohta lähteä selvittämään, mistä Helsingin kaupungin ostot tulevat. Postinumeroalueiden perusteella piirretty kartta-aineisto on kuitenkin ehkä liiankin yksityiskohtainen, joten tyydymme tässä tapauksessa kuntatason tarkasteluun.

Geofi-pakettia hyödyntämällä postinumeron yhdistäminen kuntaan on helppoa. Tarkastelu ei ole aivan täydellinen, sillä postinumeroalueiden rajat eivät aina noudata kuntarajoja täydellisesti ja postinumeroalueelle määritelty kuntatunnus riippuu siitä, “minkä kunnan rakennuksia alueella on eniten (VTJ:n mukaan)” (ks. Tilastokeskuksen sivut). Tässä tapauksessa olisi voitu hyödyntää myös aineistoon jo ladattua tietoa yrityksen kotikunnasta (poimitut_tiedot$city), mutta kuntanimien vaihtelevien kirjoitusasujen, mahdollisten kirjoitusvirheiden tai ruotsinkielisten kuntanimien vuoksi pidin varmempana ratkaisuna kunnan määrittämistä postinumeron avulla.

library(geofi)
library(dplyr)
zipcodes <- geofi::get_zipcodes(year = 2021)

# Muutetaan sf-objekti tavalliseksi data frameksi, valitaan sopivat sarakkeet
zipcodes <- as.data.frame(zipcodes) %>% 
  select(kuntanro, posti_alue)
helsingin_ostot4 <- dplyr::left_join(x = helsingin_ostot3, y = zipcodes, by=c("postCode" = "posti_alue"))

municipalities <- geofi::get_municipalities(year = 2021)
municipalities <- municipalities %>% 
  select(kunta, kunta_name)

# Valitaan sopivat sarakkeet kuntadatasta
# Käytetään right_joinia siksi, että uuden muuttujan luokka säilyy "sf"
helsingin_ostot4 <- dplyr::right_join(x = municipalities, y = helsingin_ostot4, by=c("kunta" = "kuntanro"))

# Ryhmitellään ostot kunnan nimen mukaan
helsingin_ostot5 <- helsingin_ostot4 %>% 
  group_by(kunta_name) %>% 
  summarise(kunta_summa = sum(as.numeric(summa), na.rm = FALSE)) 

# Tarkastellaan missä kunnissa on suurimmat ostot
slice_max(helsingin_ostot5, order_by = kunta_summa, n = 20)

## Simple feature collection with 20 features and 2 fields (with 1 geometry empty)
## geometry type:  MULTIPOLYGON
## dimension:      XY
## bbox:           xmin: 215353.4 ymin: 6640920 xmax: 588843.4 ymax: 7298654
## projected CRS:  ETRS89 / TM35FIN(E,N)
## # A tibble: 20 x 3
##    kunta_name  kunta_summa                                                  geom
##    <chr>             <dbl>                                    <MULTIPOLYGON [m]>
##  1 <NA>       12164973424.                                                 EMPTY
##  2 Helsinki    5214226687. (((402737.7 6680700, 402069.8 6680535, 400326.8 6678…
##  3 Espoo        741586021. (((375773.7 6691597, 377355.9 6680366, 379983.8 6681…
##  4 Vantaa       629136624. (((392811.8 6694857, 399192.7 6692524, 396012.8 6689…
##  5 Kuopio       470253261. (((581015.6 7009317, 585462.3 7007121, 588843.4 7002…
##  6 Kouvola      149758496. (((511075 6780902, 512323 6772856, 508132 6769735, 5…
##  7 Tuusula      129772884. (((397271.6 6711736, 391885.8 6710060, 392411.8 6702…
##  8 Vaasa         90367794. (((259700 7001591, 253892.6 6990776, 242914.5 700120…
##  9 Turku         61425478. (((251038.1 6731422, 245370.1 6713651, 244865.9 6708…
## 10 Tampere       60124288. (((346626.3 6854536, 347270.1 6836030, 334112.1 6814…
## 11 Hyvinkää      51569708. (((396836.4 6726577, 392888.2 6717250, 385516.6 6715…
## 12 Kerava        49833397. (((399192.7 6692524, 392811.8 6694857, 392727.4 6700…
## 13 Raasepori     44527631. (((299881.1 6640940, 297472.1 6640920, 296412.5 6642…
## 14 Oulu          42513844. (((418101.2 7220618, 417351.5 7219858, 415092.4 7219…
## 15 Lahti         42463258. (((448838.6 6774406, 453144.3 6766188, 452204.9 6761…
## 16 Nurmijärvi    22549476. (((385516.6 6715109, 388809.1 6711136, 382590.6 6697…
## 17 Kemi          21518412. (((396561 7287772, 392601.9 7283067, 392361.4 728345…
## 18 Raisio        20195233. (((239031.6 6717088, 236093.7 6712495, 230992.2 6711…
## 19 Porvoo        18877028. (((441843.9 6673817, 440194.5 6673207, 436276.1 6673…
## 20 Padasjoki     18830945. (((422133.8 6800321, 420573.8 6797563, 415159 679860…

Suurimmat ostot sijoittuvat odotetusti Helsinkiin, Espooseen ja Vantaalle. Hieman yllättäen Kuopio kiilaa lähempänä pääkaupunkiseutua sijaitsevien Kouvolan ja Tuusulan edelle.

Kaikista suurin summa jyvittyy kuitenkin luokittelun ulkopuolelle jääneelle NA-joukolle, 12 miljardia euroa 8 vuoden ajalta. Tämä summa on itse asiassa suurempi kuin kaikkien kaupungeille jyvitettyjen hankintojen summa yhteensä, mikä osoittanee muilta kuin osakeyhtiöiltä tehtävien hankintojen merkityksen Helsingin kaupungille.

Kartta ja virtauskartta top-20-kaupungeista

Merkitään vielä nämä 20 tärkeintä kuntaa kartalle:

library(sf)
library(dplyr)

# Poistetaan NA-joukko eli yritykset, joiden kotipaikkaa ei pystytty määrittämään
helsingin_ostot6 <- helsingin_ostot5 %>% 
  filter(kunta_name %in% setdiff(helsingin_ostot5$kunta_name, c(NA)))

kunnat_top20_summat <- slice_max(helsingin_ostot6, order_by = kunta_summa, n = 20)

# Korostetaan top 20 kuntia
ggplot() +
  geom_sf(data = helsingin_ostot6, aes(fill = kunta_summa), color = alpha("white", 1/3)) +
  labs(fill = "Helsingin ostot, €") +
  scale_fill_gradient2(n.breaks = 6, trans = "log10") +
  geom_sf(data = kunnat_top20_summat, col="red", size=1)

Geofi-paketissa on mahdollisuutena piirtää kartalle kuntakeskuksia merkitsevät pisteet. Pienellä muunnoksella näistä POINT-muotoisista geometrioista saadaan muodostettua LINESTRING-muotoisia viivoja, joiden alkupiste on halutussa kunnassa ja loppupiste Helsingissä. Kartan päälle lisättynä kartta ja viivasymbolit muodostavat virtauskartan, jolla voidaan kuvata eri alueiden välisiä virtoja. Tällaiset kartat ovat parhaimmillaan erittäin näyttäviä, mutta tässä tapauksessa tyydymme hyvin yksinkertaiseen esimerkkiin:

# Luodaan kuntakeskusten välille LINESTRING-viivat
keskukset <- geofi::municipality_central_localities

# Hyödynnetään base-R:n tolower-funktion help filestä löytyvää capwords-funktiota
# Toinen vaihtoehto olisi käyttää fuzzyjoin-pakettia
capwords <- function(s, strict = FALSE) {
    cap <- function(s) paste(toupper(substring(s, 1, 1)),
                  {s <- substring(s, 2); if(strict) tolower(s) else s},
                             sep = "", collapse = " " )
    sapply(strsplit(s, split = " "), cap, USE.NAMES = !is.null(names(s)))
}

# Muutetaan kuntien nimet ALL CAPS -> Capital Case
keskukset$teksti <- capwords(keskukset$teksti, strict = TRUE)

# Lisätään aineistoon ostosummat
keskukset <- left_join(keskukset, as.data.frame(helsingin_ostot6)[,1:2], by = c("teksti" = "kunta_name"))

# Lasketaan Helsingin ja kuntakeskusten välinen välimatka (km)
keskukset$distance_to_hel <- NULL
keskukset$distance_to_hel <- st_distance(keskukset$geom, y=keskukset$geom[210,])
keskukset$distance_to_hel <- as.integer(keskukset$distance_to_hel / 1000)

keskukset_linestring <- st_cast(st_union(keskukset$geom[1,], keskukset$geom[210,], by_feature=TRUE),"LINESTRING")
for (i in 1:nrow(keskukset)) {
  keskukset_linestring[i] <- st_cast(st_union(keskukset$geom[i,], keskukset$geom[210,], by_feature=TRUE),"LINESTRING") 
}

keskukset_helsinkiin <- keskukset

keskukset_helsinkiin$geom <- keskukset_linestring

keskukset_helsinkiin <- keskukset_helsinkiin[which(keskukset_helsinkiin$teksti %in% kunnat_top20_summat$kunta_name),]

# Määritetään viivojen paksuudet siten, että Helsinki, Espoo ja Vantaa saavat 0
# ja siitä eteenpäin 4, 3, 2, 2, 1, 1...
ggplot() +
  geom_sf(data = helsingin_ostot6, aes(fill = kunta_summa), color = alpha("white", 1/3)) +
  labs(fill = "Helsingin ostot, €") +
  scale_fill_gradient2(n.breaks = 6, trans = "log10") +
  geom_sf(data = arrange(keskukset_helsinkiin, desc(kunta_summa)), col=alpha("red", 1/2), size=c(0,0,0,4,3,2,2,rep(1, 13)))

Eri muuttujia yhdisteltäessä on hyödyllistä säilyttää luotavien muuttujien luokkana “sf”, mistä johtuen yllä olevassa esimerkissä käytettiin sekä left_join että right_join funktioita. Tämä johtuu siitä, että muuten ggplot2 ei välttämättä osaa löytää geom-saraketta, jossa karttojen piirtämiseen tarvittavat koordinaatit sijaitsevat.

Kunnan etäisyyden ja kunnassa sijaitsevien yritysten lukumäärän vaikutus

Tarkastellaan lopuksi yksinkertaisilla sirontakuvioihin sovitetuilla suorilla, miten kunnassa sijaitsevien yritysten lukumäärä sekä kunnan etäisyys Helsingistä vaikuttaa Helsingin kaupungin tekemien ostojen suuruuteen.

library(sf)

# Ladataan Statfinista yritysten lukumäärä Suomen kunnissa
# /PXWeb/api/v1/fi/StatFin/yri/alyr/statfin_alyr_pxt_11dc.px
library(pxweb)
library(fuzzyjoin)

pxweb_query_list <- 
  list("Vuosi"=c("2019"),
       "Kunta"=c("*"),
       "Tiedot"=c("Tplukumaara2"))

# Download data 
px_data <- 
  pxweb_get(url = "https://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/yri/alyr/statfin_alyr_pxt_11dc.px",
            query = pxweb_query_list)

# Convert to data.frame 
px_data_frame <- as.data.frame(px_data, column.name.type = "text", variable.value.type = "text")

# Yhdistetään dataa
yritykset <- left_join(x = px_data_frame, y = as.data.frame(helsingin_ostot5), by=c("Kunta"="kunta_name"))

# Poistetaan "kunnat" KOKO SUOMI, Tuntematon ja kunnat joista ei ole tilauksia
yritykset <- yritykset[which(!is.na(yritykset$kunta_summa)),]
# Poistetaan kuntarajojen geometria
yritykset <- as.data.frame(yritykset)
yritykset <- yritykset[,-5]

# Ladataan kuntakeskusten sijaintitiedot
keskukset <- geofi::municipality_central_localities

# Lisätään aineistoon ostosummat
keskukset <- left_join(keskukset, as.data.frame(helsingin_ostot6)[,1:2], by = c("teksti" = "kunta_name"))

# Lasketaan Helsingin ja kuntakeskusten välinen välimatka (km)
keskukset$distance_to_hel <- NULL
keskukset$distance_to_hel <- st_distance(keskukset$geom, y=keskukset$geom[210,])
keskukset$distance_to_hel <- as.integer(keskukset$distance_to_hel / 1000)

# Valitaan ainoastaan tarvittavat sarakkeet
keskukset <- keskukset %>% 
  dplyr::select(teksti, distance_to_hel, geom)
  
# Hyödynnetään fuzzyjoin-pakettia, jotta kuntien nimiä ei tarvitse erikseen siistiä
yritykset <- fuzzyjoin::regex_left_join(x = yritykset, y = keskukset, by=c("Kunta" = "teksti"), ignore_case = TRUE)

# Poistetaan aineistosta outlier, Helsinki
yritykset <- yritykset[-which(yritykset$Kunta == "Helsinki"),]

# Piirretään scatter-plotit, joihin on sovitettu LOESS-menetelmällä tasoitettu sovitekäyrä
par(mfrow=c(1,2))
scatter.smooth(x=yritykset$`Yritysten toimipaikat (lkm)`, y=log10(yritykset$kunta_summa), span = 1/5)
scatter.smooth(x=yritykset$distance_to_hel, y=log10(yritykset$kunta_summa))

# Vertaillaan kahta eri regressiomallia
fit1 <- lm(log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)`, data=yritykset)
fit2 <- lm(log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)` + distance_to_hel, data=yritykset)

# Haluttaessa voidaan katsoa myös regressiosuorat
# abline(lm(log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)`, data=yritykset))
# abline(lm(log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)` + distance_to_hel, data=yritykset))

summary(fit1)

## 
## Call:
## lm(formula = log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)`, 
##     data = yritykset)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -3.2431 -0.7991  0.0834  0.9631  2.4039 
## 
## Coefficients:
##                                 Estimate Std. Error t value            Pr(>|t|)
## (Intercept)                   4.75557723 0.07845464   60.62 <0.0000000000000002
## `Yritysten toimipaikat (lkm)` 0.00039387 0.00003411   11.55 <0.0000000000000002
##                                  
## (Intercept)                   ***
## `Yritysten toimipaikat (lkm)` ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 1.159 on 294 degrees of freedom
## Multiple R-squared:  0.312,  Adjusted R-squared:  0.3097 
## F-statistic: 133.3 on 1 and 294 DF,  p-value: < 0.00000000000000022

summary(fit2)

## 
## Call:
## lm(formula = log10(kunta_summa) ~ `Yritysten toimipaikat (lkm)` + 
##     distance_to_hel, data = yritykset)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -3.3314 -0.7943  0.1029  0.9196  2.7113 
## 
## Coefficients:
##                                  Estimate  Std. Error t value
## (Intercept)                    5.24186205  0.13519748  38.772
## `Yritysten toimipaikat (lkm)`  0.00036832  0.00003366  10.943
## distance_to_hel               -0.00158104  0.00036166  -4.372
##                                           Pr(>|t|)    
## (Intercept)                   < 0.0000000000000002 ***
## `Yritysten toimipaikat (lkm)` < 0.0000000000000002 ***
## distance_to_hel                          0.0000172 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 1.126 on 292 degrees of freedom
##   (1 observation deleted due to missingness)
## Multiple R-squared:  0.3543, Adjusted R-squared:  0.3499 
## F-statistic: 80.13 on 2 and 292 DF,  p-value: < 0.00000000000000022

Huomataan, että kunnassa sijaitsevien yritysten lukumäärä ja kunnan etäisyys Helsingistä ovat yhteydessä siihen, kuinka paljon ostoja Helsingin kaupunki tekee kunnassa sijaitsevilta yrityksiltä. Jo visuaalisesta tarkastelusta kuitenkin huomaamme, että myös hieman kauempana sijaitsevista ja pienemmistä kunnista (esimerkiksi Kemi ja Padasjoki) tulevien yritysten on mahdollista myydä palveluitaan Helsingin kaupungille.

Kunnan yritystiheyden kasvattaminen on hidasta ja sijainnin muuttaminen mahdotonta, joten kuntien elinvoimaisuutta pohtivien poliitikkojen, viranhaltijoiden ja konsulttien kannattaa tarkastella sitä, mikä erottaa Kemin ja Padasjoen kaltaisten kuntien yritykset muista. Helsingin kaupungin ostot -aineisto onkin otollinen tämän kaltaisen yritystason tarkastelun lähtöpisteeksi.

Lopuksi

Tieto eri kunnissa sijaitsevien yritysten menestymisestä Helsingin kaupungin tarjouskilpailuissa on mielenkiintoista monesta eri näkökulmasta. Huomaamme, että yrityksen sijainnilla on merkitystä, mutta aineisto ei yksinään vastaa kysymykseen, miksi näin on.

Asiaa voitaisiin selittää pk-seudulla toimivien yritysten ja Helsingin kaupungin hankinnoista päättävien ihmisten verkostoilla, muualla Suomessa sijaitsevien yritysten korkeammilla transaktiokustannuksilla ja vähäisemmällä informaatiolla. Myös se, että suurin osa yrityksistä on keskittynyt Suomessa pääkaupunkiseudulle (ks. Manninen & Tölli 2019) voi selittää sitä, miksi suuri osa Helsingin kaupungin hankinnoistakin keskittyy pääkaupunkiseudulle. Muiden kaupunkien tapauksissa merkittävä osa ostoista saattaisi tulla pääkaupunkiseudulta ja Helsingistä nimenomaan tästä syystä.

Viime kädessä kyse ei tietenkään ole pelkästään Helsinki vs. muu Suomi -asetelmasta vaan siitä, millaisia todellisia tai kuviteltuja esteitä suomalaisilla yrityksillä on laajemmassa mittakaavassa osallistua koko EU:n sisämarkkina-alueen toimintaan. Vaikka EU:n sisämarkkinat ovat teoriassa avoimet, kaupankäynnin esteet tulevat eri tavalla vastaan, kun yhteistä kieltä ei välttämättä ole ja kansalliset lainsäädännöt vaihtelevat. Suomi liittyi EU:n jäseneksi vuonna 1995, mutta vielä yli 25 vuotta myöhemmin suomalaisten pk-yritysten valmiuksista osallistua sisämarkkinoille kannetaan huolta (ks. esim. Teknologiateollisuus 2021).

Ulkomaankaupan tilastoihin liittyvää aineistoa ja asiaan liittyviä analyyseja on saatavilla runsaasti eri lähteistä, joten tähän aihepiiriin tuskin pureudutaan tämän blogin kontekstissa - paitsi jos asia saadaan yhdistettyä jonkin R-paketin yhteyteen.