Inlezen van gegevens in R
John Bastiaansen
Albart Coster
Inlezen van gegevens in R
In deze sessie gaat het over inlezen van gegevens uit verschilllende bestanden in R
Regelmatige databastanden
Regelmatige bestanden zijn eenvoudig in te lezen met functie read.table. Belangrijk is dat het aantal kolommen in elke rij gelijk is en dat de scheiding van de kolommen duidelijk is, met bijvoorbeeld een spatie, een comma, een ; of een tab.
Het bestand melkproductie.dat is een bestand waarin de kolommen met een spatie zijn gescheiden. De eerste regel van het bestand bevat de namen van de kolommen:
df <- read.table("melkproductie.dat", sep = " ", head = TRUE)
Het bestand melkproductie.csv is hetzelfde bestand, maar nu zijn de kolommen gescheiden dmv een comma. Opnieuw bevat de eerste regel de namen van de kolommen:
df <- read.table("melkproductie.csv", sep = ",", head = TRUE)
Het bestand melkproductie.tab is opnieuw hetzelfde bestand, nu zijn de kolommen gescheiden dmv een tab (in R wordt een tab aangegeven met een "\t" teken). De eerste regel bevat de namen van de kolommen.
df <- read.table("melkproductie.tab", sep = "\t", head = TRUE)
Bestanden met een onduidelijke indeling
Het is belangrijk om ook bestanden met een onregelmatige opmaak in te kunnen lezen. We gebruiken hier een bestand met kalfdata uit Dairyplan 5 (GEA WestfaliaSurge DP5): data_kalfdata.ads. De eerste regel is de definition header (DH) en bevat de codes van de gegevens in ADS formaat, de tweede regel is de value header (VH) van de codes in de DH regel. De CN regel is een commentaar. De regel met DN is de definition van de normal data (DN) en bevat de code van de normale data in het bestand. De regels met VN bevatten de werkelijke gegevens en beginnen met VN. Vervolgens komt er weer een commentaar regel (CN), dan een terminal regel (TN) en ZN geeft het einde van het bestand aan.
We moeten het bestand met functie readLines inlezen:
ll <- readLines("data_kalfdata.ads")
## of direct van internet:
ll <- readLines("http://rcursus.dairyconsult.nl/bestanden/data_kalfdata.ads")
Functie readLines maakt een vector van de verschillende regels in het bestand zonder er verder iets mee te doen:
class(ll)
## [1] "character"
length(ll)
## [1] 1988
head(ll)
## [1] "DH000000000000000800090000624000900008080009000030800000999201000900004060"
## [2] "VH000000DD Westfalia DP5 5.240 20121122,130300"
## [3] "CN DPD#2544 This header was created by Dairyplan "
## [4] "DN000000000099920100090007006000009992010009000280800000999201000003322030"
## [5] "VN000000, 2,19830421, 1"
## [6] "VN000000, 4,19740715, 1"
tail(ll)
## [1] "VN000000, 2101,20121114, 1"
## [2] "VN000000, 9999,20100111, 1"
## [3] "VN000000, 9999,20100908, 2"
## [4] "CN Produced 1981 Records "
## [5] "TN"
## [6] "ZN"
We moeten dus eigenlijk de regels die worden voorgegaan door VN000000 uit het bestand selecten en verder verwerken, we moeten daarvoor deze regels vinden in het object ll en die kopieeren naar een nieuwe vector van gegevens. Dit vinden doen we met functie grep:
ww <- grep("^VN000000,", ll)
ll1 <- ll[ww]
length(ll1)
## [1] 1981
head(ll1)
## [1] "VN000000, 2,19830421, 1"
## [2] "VN000000, 4,19740715, 1"
## [3] "VN000000, 6,19830209, 1"
## [4] "VN000000, 162,20000330, 1"
## [5] "VN000000, 203,19980821, 1"
## [6] "VN000000, 208,19970403, 1"
tail(ll1)
## [1] "VN000000, 2072,20121116, 1"
## [2] "VN000000, 2082,20121105, 1"
## [3] "VN000000, 2085,20121113, 1"
## [4] "VN000000, 2101,20121114, 1"
## [5] "VN000000, 9999,20100111, 1"
## [6] "VN000000, 9999,20100908, 2"
We hebben nu een vector met de schone gegevens, de eerste kolom bevat geen informatie, de andere kolommen zijn belangrijk. We moeten deze vector omzetten naar een tabel. Dit doen we met functie strsplit. De vector ll1 bestaat namelijk uit 1981 elementen die weer bestaan uit een opeenvolging van gegevens. In programmeerjargon heten deze elementen strings, reeksen, en we moeten eigenlijk deze strings opsplitsen. Dit doen we met functie strsplit (stringsplit). Hiermee krijgen we een veel langere vector omdat alle oorspronkelijk strings zijn opgesplitst. Deze elementen moeten we dan op een goede manier in een tabel zetten. We beginnen met opsplitsen, dit moet gebeuren bij de comma’s:
ll2 <- strsplit(ll1, split = ",")
class(ll2)
## [1] "list"
head(ll2)
## [[1]]
## [1] "VN000000" " 2" "19830421" " 1"
##
## [[2]]
## [1] "VN000000" " 4" "19740715" " 1"
##
## [[3]]
## [1] "VN000000" " 6" "19830209" " 1"
##
## [[4]]
## [1] "VN000000" " 162" "20000330" " 1"
##
## [[5]]
## [1] "VN000000" " 203" "19980821" " 1"
##
## [[6]]
## [1] "VN000000" " 208" "19970403" " 1"
class(ll2[[1]])
## [1] "character"
We zien nu dat ll2 een list is en dat elk element uit de list weer een vector van strings is. We moeten deze elementen op een goede manier in een tabel zetten. De tabel moet 1981 rijen hebben omdat we 1981 elementen in ll2 hebben en drie kolommen omdat we Dier, Datum en laktatienummer willen opslaan.
We kunnen eerst controleren of elk element van ll2 een vector van 4 elementen is, dit is verstanding omdat het een voorwaarde is voor onze eerste oplossing:
ne <- sapply(ll2, length)
table(ne)
## ne
## 4
## 1981
Omdat we zien dat ll2 uit vectoren van 4 elementen bestaat kunnen we een tabel maken en die vullen met de gegevens in ll2. In dit
tab <- matrix(unlist(ll2), nrow = length(ll2), ncol = 4, byrow = TRUE)
head(tab)
## [,1] [,2] [,3] [,4]
## [1,] "VN000000" " 2" "19830421" " 1"
## [2,] "VN000000" " 4" "19740715" " 1"
## [3,] "VN000000" " 6" "19830209" " 1"
## [4,] "VN000000" " 162" "20000330" " 1"
## [5,] "VN000000" " 203" "19980821" " 1"
## [6,] "VN000000" " 208" "19970403" " 1"
We gebruiken functie unlist om ll2 om te zetten in een vector van 4*1981 = 7924 elementen. Het argument byrow = TRUE geeft aan dat tabel tab per rij moet worden gevuld met de gegevens in ll2, probeer wat er gebeurt als de tabel gevuld wordt met byrow = FALSE. We hebben nu een tabel van de classe matrix, en moeten nog een paar dingen aanpassen om er een goede tabel van de classe data.frame van de te maken:
- De eerste kolom van
tab moet weg;
- De tabel moet omgezet worden naar een
data.frame, zodat kolommen logische namen kunnen krijgen;
- De spaties uit de tweede en viede kolom moeten weg, zodat R kan snappen dat deze kolommen nummers bevatten;
- De derde kolom moet ongezet worden naar een datum in een bruikbaarder formaat;
tab <- data.frame(tab[, -1])
colnames(tab) <- c("DIER", "DATUM", "NLACT")
tab$DIER <- gsub(" ", "", tab$DIER)
tab$DIER <- as.numeric(tab$DIER)
tab$NLACT <- as.numeric(gsub(" ", "", tab$NLACT))
tab$DATUM <- paste(substr(tab$DATUM, 1, 4), substr(tab$DATUM,
5, 6), substr(tab$DATUM, 7, 8), sep = "-")
tab$DATUM <- as.Date(tab$DATUM, format = "%Y-%m-%d")
summary(tab)
## DIER DATUM NLACT
## Min. : 2 Min. :1974-07-15 Min. :1.00
## 1st Qu.: 901 1st Qu.:2006-11-19 1st Qu.:1.00
## Median : 1392 Median :2010-07-22 Median :1.00
## Mean : 1285 Mean :2009-01-11 Mean :1.44
## 3rd Qu.: 1660 3rd Qu.:2011-10-02 3rd Qu.:2.00
## Max. : 9999 Max. :2012-11-22 Max. :5.00
We kunnen nu een mooi overzicht maken van het aantal afkalvingen per jaar:
tab$jaar <- format.Date(tab$DATUM, "%Y")
tab$NLACTcat <- cut(tab$NLACT, breaks = c(0, 1, 2, 20))
tapply(tab$NLACT, list(tab$jaar, tab$NLACTcat), length)
## (0,1] (1,2] (2,20]
## 1974 1 NA NA
## 1983 2 NA NA
## 1997 4 NA NA
## 1998 20 NA NA
## 1999 39 NA NA
## 2000 22 NA NA
## 2001 23 NA NA
## 2002 47 NA NA
## 2003 88 NA NA
## 2004 71 NA NA
## 2005 115 NA NA
## 2006 71 NA NA
## 2007 78 NA NA
## 2008 72 NA NA
## 2009 222 10 1
## 2010 248 87 9
## 2011 136 222 55
## 2012 85 118 135
Inlezen van excelbestanden
Inlezen van excelbestanden is lastig, maar mogelijk. De eenvoudigste optie is om het excelbestand op te slaan als .csv-bestand en dat de openen in R, zoals hierboven is uitgelegd. Daarnaast kun je gebruik maken van het pakket XLConnect om excel bestanden direct in te lezen. Voor een (uitgebreide) handleiding, zie de vignettes op de website van het pakket http://cran.r-project.org/web/packages/XLConnect/index.html. Op sommige computers werkt het alleen in de 32-bits versie van R. We gebruiken een excelbestand met BCS-scores en kalfdata:
install.packages("XLConnect")
require(XLConnect)
wb <- loadWorkbook("bcs_kalfdata_20120123.xls", create = FALSE)
## of direct vanaf internet
wb <- loadWorkbook("http://rworkshop.dairyconsult.nl/bestanden/bcs_kalfdata_20120123.xls",
create = FALSE)
bcs <- readWorksheet(wb, sheet = "bcs", startRow = 0, endRow = 0,
startCol = 0, endCol = 0)
bcs <- bcs[!duplicated(bcs$REG_ID) & !is.na(bcs$REG_ID), ]
kd <- readWorksheet(wb, sheet = "kalfdata", startRow = 0, endRow = 0,
startCol = 0, endCol = 0)
kd <- kd[!duplicated(kd$REG_ID) & !is.na(kd$REG_ID), ]
.dbf bestanden
.dbf bestanden kunnen met functie read.dbf uit het pakket foreign worden ingelezen. We gebruiken hier het bestand mlp.dbf om een tabel van de gemiddelde melkproducties per jaar te maken:
install.packages("foreign")
require(foreign)
mlp <- read.dbf("mlp.dbf")
## of direct van internet
mlp <- read.dbf("http://rworkshop.dairyconsult.nl/bestanden/mlp.dbf")
head(mlp)
## OHR AE GRUPPE MKG FETT EIWEISS LAKTOSE
## 1 DIERNUMMER 623 01 GROEP 6 30.7 31.9 2.39 0
## 2 DIERNUMMER 623 01 GROEP 6 31.3 32.7 2.74 0
## 3 DIERNUMMER 623 01 GROEP 6 27.3 27.6 3.92 0
## 4 DIERNUMMER 623 01 GROEP 6 27.5 28.9 5.20 0
## 5 DIERNUMMER 623 01 GROEP 6 25.2 26.5 4.82 0
## 6 DIERNUMMER 623 01 GROEP 6 23.7 23.5 5.25 0
## ZELLZAHL STATUS HARNSTOFF DATUM KZ PRUEFNR
## 1 85 <NA> 0 2002-01-09 <NA> 1
## 2 60 <NA> 0 2002-02-07 <NA> 2
## 3 67 <NA> 227 2002-03-11 <NA> 3
## 4 64 <NA> 0 2002-04-10 <NA> 4
## 5 56 <NA> 0 2002-05-14 <NA> 5
## 6 82 <NA> 0 2001-06-08 <NA> 6
## LAKTATION MT N_GEMELK
## 1 2 114 NA
## 2 2 143 NA
## 3 2 175 NA
## 4 2 205 NA
## 5 2 239 NA
## 6 1 249 NA
sapply(mlp, class)
## OHR AE GRUPPE MKG FETT EIWEISS
## "factor" "factor" "factor" "numeric" "numeric" "numeric"
## LAKTOSE ZELLZAHL STATUS HARNSTOFF DATUM KZ
## "numeric" "integer" "factor" "integer" "Date" "factor"
## PRUEFNR LAKTATION MT N_GEMELK
## "integer" "integer" "integer" "integer"
We maken nu twee nieuwe kolommen, jaar en NLACTcat en maken vervolgens een tabel van de gemiddelde melkproductie per jaar. Omdat kolom DATUM al van classe Date is hoeven we daar niets meer aan te veranderen:
class(mlp$DATUM)
## [1] "Date"
mlp$jaar <- format.Date(mlp$DATUM, "%Y")
mlp$NLACTcat <- cut(mlp$LAKTATION, breaks = c(0, 1, 2, 20))
tapply(mlp$MKG, list(mlp$jaar, mlp$NLACTcat), mean, na.rm = TRUE)
## (0,1] (1,2] (2,20]
## 2000 19.3 NA 1.59
## 2001 25.2 34.2 28.84
## 2002 22.9 26.5 35.13
## 2003 28.3 30.8 32.58
## 2004 24.7 30.6 31.04
## 2005 24.9 28.0 28.49
## 2006 22.6 27.2 27.04
## 2007 23.6 30.3 30.50
## 2008 24.0 25.2 26.14
## 2009 21.4 25.2 25.60
## 2010 26.5 23.4 28.53
## 2011 25.9 27.3 25.79
## 2012 27.1 30.0 28.44
## 2013 27.7 29.4 30.18
Microsoft Access bestanden
Microft Access is een database programma, gegevens worden in een verschillende tabellen van de database opgeslagen en bewaard. In dit voorbeeld gebruiken we de database van Winpulsa, een melkmetingsprogramma (http://www.ifu-diagnostic.de/software_winpulsa6.html). Het bestand staat hier.
Om gegevens uit de database te halen moeten we een verbinding (in jargon connection) met de database maken. We gebruiken het pakket RODBC om de verbinding met de database te maken. Deze verbinding werkt alleen in de 32bits versie van R. Om de juiste versie van R te kiezen kun je bij het opstarten van Rstudio CTRL ingedrukt houden.
We slaan de verbinding met de database op in een object genaamd conA:
if (!"RODBC" %in% installed.packages()[, 1]) install.packages("RODBC")
require(RODBC)
conA <- odbcConnectAccess("Winpulsa.mdb")
class(conA)
## [1] "RODBC"
De database bevat veel informatie, maar stel dat we graag een grafiek willen maken van de gemiddelde melkproductie van de dagen die erin staan. Het is verstandig om de database eerst in Access te openen om een beeld te krijgen van de structuur van de database. In R kunnen we ook de tabellen inzien, met functie sqlTables(conA):
We willen gegevens uit tabellen animals en milkings halen, voor we de gegevensoverdracht gaan regelen is het verstandig om informatie over de tabellen te verkrijgen, door sqlColumns(conA,'animals') en sqlColumns(conA,‘milkings’)` in de console in te typen. We willen de dagelijkse melkproductie van de individuele koeien en verder nog wat informatie over die dieren krijgen, dit doen we met een query:
query <- paste("SELECT a.herdbuch_no AS dier,\n",
"a.group_no AS groep,\n",
"a.calving AS KALF_DATUM,\n",
"a.lactation AS NLACT,\n",
"m.day AS DATUM,DateDiff('d',a.calving,m.day) AS DIL,\n",
"m.amount AS KG_MELK\n",
"FROM animals AS a\n",
"INNER JOIN milkings AS m ON a.herdbuch_no = m.herdbuch_no\n",
"WHERE m.amount IS NOT NULL AND m.amount > 0\n")
prod <- sqlQuery(conA,query)
odbcClose(conA)
rm(conA)
prod$NLACTcat <- cut(prod$NLACT,breaks = c(0,1,2,20),
labels = c('1 lakt.','2 lakts.','3+ lakts.'))
We gebruiken functie ggplot om een grafiek te maken, maar vooraf vatten we de gegevens samen in een tabel per dag. We moeten de tabel wel een beetje aanpassen met functies melt en cast uit het pakket reshape:
tab <- cast(melt(tapply(prod$KG_MELK, list(prod$DATUM, prod$NLACTcat),
mean, na.rm = TRUE)), formula = X1 + X2 ~ ., fun.aggregate = mean,
na.rm = TRUE)
colnames(tab) <- c("DATUM", "NLACTcat", "KG_MELK")
tab$DATUM <- as.Date(as.character(tab$DATUM))
p <- ggplot(data = tab, aes(x = DATUM, y = KG_MELK, colour = NLACTcat)) +
geom_line(size = 1.5)
p
Voorbeeld van verbinding met een MSSQL database
MS-Access is een zogenaamd serverless databasesysteem. Hierdoor hoeft er geen server te worden opgezet en kan het database bestand op elke computer worden gebruikt. De meeste (grote) databases gebruiken echter een server-systeem, waarbij de gegevens en worden opgeslagen op een centrale server en andere computers, clients, met de database kunnen verbinden en gegevens ophalen of aanleveren. Omdat het voor deze cursus niet mogelijk was om een centrale MSSQL-server op te zetten geef ik hier allen de opzet van een verbinding met een MSSQL database weer. Hiervoor gebruik ik opnieuw het RODBC pakket, dat we ook in het Access voorbeeld hebben gebruikt.
We moeten eerst een verbinding met de database maken, in dit geval is het de database van een lely melkrobot, en de verbinding wordt bewaard in het object msch:
## haal eerst de gegevens uit SQL-Server database.
driver <- "Driver=SQL Server"
server <- "Server=EIGENAAR-TOSH\\SQLEXPRESS"
database <- "Database=lely"
{
if (FALSE) {
UID <- "UID=sa"
PASSWORD <- "PWD=Adifosa1"
tc <- "Trusted_Connection=No"
auth <- paste(UID, PASSWORD, tc, sep = ";")
} else auth <- "Trusted_Connection=Yes"
}
## connectie
constr <- paste(driver, server, database, auth, sep = ";")
msch <- odbcDriverConnect(constr)
Als we de verbinding met de database in orde hebben kunnen we queries op de database uitvoeren, bijvoorbeeld deze query om de stamboom van de dieren uit de database te halen. Let op de manier van schrijven van de queries, ik schrijf een \n aan het eind van elke regel, hiermee geef ik aan dat de regel hier eindigt en dit zorgt ervoor dat hij er goed uitziet als ik hem in R wil bekijken (cat(stat)):
stat <- paste("SELECT a.AniId AS ID,a.AniUserNumber as BEDRIJF_ID,\n",
"CONCAT(a.AniLifeNumber,'_',a.AniId) AS REG_ID,a.AniName AS NAAM,\n",
"CONVERT(VARCHAR,a.AniBirthDay,111) AS GEB_DATUM,g.GenName AS SEX,\n",
"a.AniMotherLifeNumber AS REG_ID_MOEDER,a.AniFatherLifeNumber AS REG_ID_VADER\n",
"FROM lely.dbo.HemAnimal AS a\n",
"INNER JOIN lely.dbo.LimGender AS g ON a.AniGenID = g.GenID\n",
"WHERE a.AniLifeNumber IS NOT NULL AND a.AniLifeNumber != '' ORDER BY REG_ID")
ped <- sqlQuery(msch,stat)
Als we de query hebben uitgevoerd moeten we de verbinding verbreken:
close(msch)
Samenvatting
In dit onderdeel van de workshop hebben we het volgende behandeld:
- Eenvoudige tekstbestanden inlezen met functie
read.table
- Minder eenvoudige tekstbestanden inlezen met functie
readLines
- dbf-bestanden inlezen met functie
read.dbf
- Access databases benaderen met het
RODBC pakket en eenvoudige SQL-queries schrijven
- MSSQL database benadere met het
RODBC pakket
Terug naar hoofdpagina van de cursus
