R-ohjelmointi.org

Tilastotieteellistä ohjelmointia R-kielellä

Excel-tiedostojen tuottaminen R:stä

Data-analyytikko toimii tyypillisesti liiketoiminnan kanssa yhteistyössä. Tällöin on tarpeen pystyä tuottamaan raportteja ja erilaisia listauksia sellaisessa tiedostomuodossa, että liiketoiminnan edustajatkin pystyvät niitä käyttämään omilla tietokoneillaan. Microsoftin Office -perheen ohjelmistot ovat laajalti levinneitä, joten etenkin niiden kanssa yhteensopivien tiedostomuotojen tuottaminen on käytännössä osoittautunut varsin hyödylliseksi ominaisuudeksi.

Raportteja on nykyään melko yksinkertaista tuottaa Microsoft Word -muotoon esimerkiksi paketeilla ReporteRs ja officer. Samoilla paketeilla on mahdollista myös tuottaa PowerPoint-muotoisia esityksiä. Lisäksi muun muassa rmarkdown-paketti tarjoaa mahdollisuuden sijoittaa R-kielistä koodia vapaan tekstin joukkoon. Tällainen tiedosto voidaan sitten kääntään vaikkapa HTML- tai Microsoft Word-muotoon. Tämä on osoittautunut erittäin näppäräksi konstiksi 1) organisoida data-analyysi järkevästi ja 2) dokumentoida analyysin kulku ja tulokset myös liiketoiminnan tarpeisiin.

Yllä mainitusta yhtälöstä puuttuu vielä Excel-yhteensopivuus, mutta siihen on olemassa leegio erilaisia R-paketteja. R:n saa keskustelemaan Excel:in kanssa suoraan muun muassa ODBC-yhteydellä ja RExcel-paketilla. Lisäksi muun muassa paketit xlsx, XLConnect, ja gdata mahdollistavat Excel-tiedostojen lukemisen ja kirjoittamisen R:stä käsin. Uusimpina paketteina readxl, openxlsx ja excel.link tarjoavat ominaisuuksia Excel-tiedostojen käsittelyyn.

Olen viime aikoina tykästynyt erityisesti xlsx-pakettiin, koska se on kohtuullisen helppokäyttöinen, ja mahdollistaa monenlaisten Excel-tiedostojen luomisen. Se on kuitenkin toisinaan hidas, etenkin jos on tarpeen käsitellä suuria Excel-tiedostoja.

Katsotaanpa seuraavaksi, miten xlsx -paketilla voidaan luoda Excel-tiedosto, jossa sarakeotsikoille on käytetty varsinaisesta aineistosta eroavaa tyyliä, ja Excel-tiedosto on salasanalla suojattu editointia vasten, lukuunottamatta yhtä saraketta johon voidaan kerätä muistiinpanoja.

Ladataan tarvittava laajennuspaketti, asetetaan polku työkansioon, johon Excel-tiedosto lopuksi syntyy, ja luodaan esimerkkidata R:n mtcars-datasetistä:

# Ladataan paketti
library(xlsx)
 
# Asetetaan työkansio
setwd("C:/Users/lenovo/Desktop")
 
# Luodaan data
mtcars2 <- cbind(make_and_model = rownames(mtcars), mtcars)
mtcars2$notes <- rep("", nrow(mtcars2))
rownames(mtcars2) <- NULL

Tämän jälkeen luodaan uusi Excel-tiedosto, ja lisätään sen yhdelle välilehdelle yllä luotu aineisto mtcars2:

# Luodaan välilehti ja lisätään sille data
wb = createWorkbook()
s1 = createSheet(wb, "mtcars2")
colnames(mtcars2) <- toupper(colnames(mtcars2))
addDataFrame(mtcars2, s1, row.names=FALSE)

Tämän jälkeen muotoillaan Excel-tiedoston soluja halutulla tavalla. Periaatteessa tämä toimii aina samalla tavalla. Ensin luodaan jokin tyyli, tässä siis vain otsikkoriville, alla nimeltään cs_o. Tämän jälkeen haetaan ne rivit, sarakkeet tai solut, joihin tyyli halutaan kohdistaa (alla rows_o ja cells_o), ja lopuksi näihin kohdistetaan luotu tyyli. Seuraavassa muutetaan otsikkokentän solujen taustaväriksi harmaa:

# Otsikkokenttien väritys ja muotoilu
otsikkokentät <- Fill(foregroundColor="grey75")
cs_o <- CellStyle(wb, fill=otsikkokentät)
rows_o <- getRows(s1, rowIndex=1)
cells_o <- getCells(rows_o, colIndex = 1:ncol(mtcars2))
lapply(names(cells_o), function(ii) setCellStyle(cells_o[[ii]], cs_o))

Määritetään seuraavaksi lukittaville soluille oma tyylinsä ja muistiinpanokentälle, joka jätetään editoitavaksi, oma tyylinsä. Excelissä on huomattavaa, että jos välilehti tai työkirja suojataan salasanalla, kaikki muut kentät lukitaan paitsi ne jotka on eksplisiittisesti määritetty ei-lukituiksi. Siksi solujen muotoilussa pitää antaa cellProtection -argumentti vain niiden solujen osalta, jotka halutaan jättää editoitaviksi:

# Lukittujen kenttien väritys ja muotoilu
# Lukitaan kaikki muut kentät paitsi viimeinen "notes"
lukitutsolut <- Fill(foregroundColor="cornsilk", pattern="SOLID_FOREGROUND")
cs_l <- CellStyle(wb, fill=lukitutsolut)
rows_l <- getRows(s1, rowIndex=2:(nrow(mtcars2)+1))
cells_l <- getCells(rows_l, colIndex = 1:(ncol(mtcars2)-1)) 
lapply(names(cells_l), function(ii) setCellStyle(cells_l[[ii]], cs_l))
 
# Avoimien kenttien väritys ja muotoilu
avoimetsolut <- Fill(foregroundColor="#8DB4E2", pattern="SOLID_FOREGROUND")
cs = CellStyle(wb, fill=avoimetsolut, cellProtection = CellProtection(locked=F))
rows <- getRows(s1, rowIndex=2:(nrow(mtcars2)+1))
cells <- getCells(rows, colIndex = ncol(mtcars2)) #getting the cells to unlock
lapply(names(cells), function(ii) setCellStyle(cells[[ii]], cs))

Kun tyylit on määritelty, ja soluihin kohdistetty, voidaan vielä tehdä yleisiä säätöjä, kuten lisätä suodatus-mahdollisuus kuhunkin sarakkeeseen, asettaa sarakkeiden leveydet automaattisesti tekstin levyisiksi ja lukita rivi- ja sarakeotsikot paikoilleen (freeze panes), jotta tiedoston selaileminen on helpompaa:

# Suodatuksen lisäys
# Lukitus estää filtteröinnin käytön
ran <- paste(LETTERS[c(1,ncol(mtcars2))], collapse=":")
addAutoFilter(sheet=s1, cellRange="A:M")
 
# sarakkeiden leveyden säätö
autoSizeColumn(s1, 1)
autoSizeColumn(s1, 2)
autoSizeColumn(s1, 3)
autoSizeColumn(s1, 4)
autoSizeColumn(s1, 5)
autoSizeColumn(s1, 6)
autoSizeColumn(s1, 7)
autoSizeColumn(s1, 8)
autoSizeColumn(s1, 9)
autoSizeColumn(s1, 10)
autoSizeColumn(s1, 11)
 
# Lukitaan tietyt paikoilleen selailun helpottamiseksi
createFreezePane(s1, rowSplit=2, colSplit=2, startRow=2, startColumn=2)

Lopuksi voidaan vielä lisätä vaikkapa hyperlinkki osoittamaan aineiston lähteeseen:

# Lisätään hyperlinkki
rows   <- createRow(s1, nrow(mtcars2)+2)              
cells  <- createCell(rows, colIndex=1)      
cell <- cells[[1,1]]
address <- "https://stat.ethz.ch/R-manual/R-devel/library/datasets/html/mtcars.html"
setCellValue(cell, "Link to the dataset description")  
addHyperlink(cell, address)

Kun tiedosto muotoiltu valmiiksi, voidaan yllä suojattaviksi määritetyt solut lukita salasanan taakse:

# Suojataan tiedosto salasanalla "mypassword"
.jcall(s1, "V", "protectSheet", "mypassword")

Tämä lukitsee välilehden salasanalla, ja ainoastaan erikseen editoivaksi määritettyjä soluja voi sen jälkeen muokata ilman että tarvitaan salasanaa.

Lopuksi tiedosto on kirjoitettava vielä levylle:

# Kirjoitetaan tiedosto levylle
saveWorkbook(wb, "mtcars.xlsx")

Koska Excel-tiedostoja ei näemmä saa upotettua käyttämälleni blogialustalle, joudutte tyytymään kuvaan lopputuloksesta:
mtcars

Käyttökelpoisten Excel-tiedostojen tuottaminen moniin tarkoituksiin on R:stä käsin yllättävän helppoa. Etenkin jos tuotettavia tiedostoja on paljon, tai sama tiedosto on tarpeen vaikkapa päivittää tietyllä syklillä, on näppärää, jos homman voi automatisoida. Olin jo aiemminkin käyttänyt xlsx-pakettia vastaaviin tarkoituksiin, mutta solujen lukitusmahdollisuus tuli minulle vastikään uutena ominaisuutena.