R-ohjelmointi.org

Tilastotieteellistä ohjelmointia R-kielellä

R, Excel ja rivinvaihdot soluissa

Rivinvaihdot kentissä
Esimerkiksi Excel-tiedostoissa voi olla rivinvaihtoja solujen sisällä. Tyypillisesti tällainen tilanne syntyy vaikkapa, kun soluun halutaan tallentaa osoitetieto, jossa nimi, katuosoite ja postinumero ovat kukin omilla riveillään. Jos tällaisen tiedoston luo Excelissä (Book1.xlsx), ja tallentaa sen csv-muodossa (Book1.csv), tulee rivinvaihtoja sisältävien solujen sisältö tiedostoon lainausmerkeissä, mikä taas mahdollistaa tiedoston lukemisen oikein esimerkiksi R:ään. Sama pätee myös sarkaineroteltuun tiedostoon (Book1.txt). Tämä vastaa myös CSV:n standardointiyrityksen (RFC 4180) kuvausta.

Tiedoston lukeminen R:ään
Yllä mainitun CSV-tiedoston lukeminen R:ään onnistuu vaikkapa read.csv2()-komennolla:

dat<-read.csv2("Book1.csv")
dat
 
  ID                                                   osoite
1  1          Ville Korhonen\nHämeentie 1 a 3\n00100 Helsinki
2  2                                                     <NA>
3  3 Kalle Virtanen\nMannerheimintie 121  b 6\n00480 Helsinki

Huomaa sarakkeen osoite sisäiset rivinvaihdot (”line feed” eli R:ssä ”\n”)!

Tiedoston luominen R:stä
Samaan tapaan R:stä voidaan kirjoittaa tiedosto, jossa on solun tai kentän sisällä rivinvaihtoja, ja jonka Excel osaa lukea oikein. Keskeistä on muistaa laittaa tekstikentät lainausmerkkeihin:

write.table(dat, "dat.csv", sep=";", quote=T, col.names=T, row.names=F)

Tuloksena syntyvässä tiedostossa (dat.csv) osoitekentän muotoilu säilyy oikeana.

Käytä vain puolipistettä, jos avaat tiedoston Excelissä
Mielenkiintoisena knoppina todettakoon, etten ole saanut R:stä kirjoitettuja tiedostoja avattua oikein Excelin (2010) päässä, jos CSV-tiedostossa sarake-erottimena on ollut pilkku tai tabulaattori, ainoastaan silloin kun sarake-erottimena on ollut puolipiste. Excelin maa-asetusten muuttamisella ei vaikuttanut olevan mitään vaikutusta asiaan.

Hankalampi juttu
Lisäksi, jos tiedostojen rivinvaihtoja sisältävät kentät olisivat aina lainausmerkeissä, elämä olisi paljon helpompaa. Olen toistuvasti tavannut tiedostoja, jotka on poimittu tietokannoista, mutta joissa rivinvaihtoja sisältävät kentät eivät ole lainausmerkeissä (Book2).

Tiedoston eri riveillä olevien kenttien lukumäärä voidaan selvittää vaikkapa funktiolla count.fields():

cf<-count.fields("Book2.csv", sep=";")
cf
 
[1] 2 2 1 1 2 2 1 1

Ylläolevan tulosteen perusteella on siis odotettavissa ongelmia, koska tiedostossa pitäisi olla vain kolme riviä, joilla jokaisella on kaksi kenttää tai saraketta.

Jos tiedosto on näin pieni, ongelman pystyisi korjaamaan käsin, mutta useimmiten näissä tapaamissani ongelmallisissa tiedostoissa on miljoonia rivejä, ja mahdollisesti satoja sarakkeitakin. Käytännössä tyydyn usemmiten lukemaan ongelmallisen tiedoston readLines()-funktiolla, lasken kenttien lukumäärän count.field()-funktiolla, poistan luestusta datasta poikkeavat rivit kenttien lukumäärän perusteella, kirjoitan siistin datan uudelleen tiedostoon, ja luen sen lopuksi tiedostoon.

Yllä esitetty on varsin epätyydyttävä ratkaisu, jos usealle riville jakautuvia kenttiä on tiedostossa suuri määrä, jolloin puuttuvuutta tulisi paljon. Parempi ratkaisu voisi olla vaikkapa seuraava. Funktiolla readChar() luetaan tiedosto yhdeksi tekstikentäksi, jossa esiintyvät line feed-merkit (kenttien sisäiset rivinvaihdot) korvataan pystypalkeilla. Tekstikenttä kirjoitetaan sitten levylle tiedostoksi, ja luetaan se uudelleen taulukoksi:

rc<-readChar("Book2.csv", nchars=file.info("Book2.csv")$size)
rc2<-gsub("\r|", "\r\n", gsub("\n", "|", rc), fixed=T)
write(rc2, "rc2.csv")
read.csv2("rc2.csv")
 
  ID                                                 osoite
1  1          Ville Korhonen|Hämeentie 1 a 3|00100 Helsinki
2  2                                                   <NA>
3  3 Kalle Virtanen|Mannerheimintie 121  b 6|00480 Helsinki

Eräs haittapuoli tässäkin versiossa on, että jos tiedoston on niin suuri, ettei se mahdu R:ssä yhteen merkkijonoon, pitää tiedosto käsitellä osissa. Tämä on kuitenkin tähän mennessä paras keksimäni yksinomaan R:ään perustuva ratkaisu. Suurin osa muista tekstitiedostojen lukemiseen keskittyvistä funktioista (readLines, scan, …) poistavat LF-merkit kenttien keskeltä, ja tulkitsevat lennossa Windows-rivinvaihdoiksi (CRLF).

EDIT (2014-01-28)
R:ää voi yllä kuvatulla tavalla käyttää rivinvaihtojen konvertointiin eri käyttöjärjestelmien välillä. Windows:ssa kunkin rivin lopussa ovat merkit CR+LF (\r\n), Linux:ssa puolestaan vain LF (\n). Jos siis halutaan konvertoida tekstitiedosto vaikkapa Windows:sta Linux:iin, korvataan tiedostossa esiintyvät \r\n -merkinnät pelkillä \n-merkinnöillä. Tämän jälkeen muodostuvaa tiedostoa ei saa kirjoittaa levylle esimerkiksi tavanomaisella write.table()-komennolla, koska tällöin Windows lisää rivien loppuun oman rivinvaihto merkistönsä uudelleen. Sen sijaan komento writeChar() osaa muodostaa tulostiedoston oikein.