I've got a huge dataset in R which contains (among other things) 2 columns indicating "Term Code", which are "Term Code" and "Term Code1".
"Term Code" is the mostly-correct column, which usually contains a 6-digit code for year and academic term:
> head(data$Term_code)
[1] 201230 201230 201230 201230 201230 201230
however, over the years that this field was populated, on occassion this field was either left blank or populated with only the last 2 digits:
> head(data$Term_code[nchar(data$Term_code) < 6], 100)
[1] NA NA NA 70 NA NA 10 NA NA 30 NA 30 NA 40 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
[35] NA 10 NA 30 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
[69] NA NA NA 50 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
A SAS analyst did his best to fill construct another field, "Term_code1", with the preferred 6-digit version of the code where it's missing or truncated in "Term_code".
Now I need to combine these 2 columns into a single, maximally-complete column vector within the dataframe.
I did this using a for loop, which works on the small scale, but is slow at best with Big Data and at worse breaks by hitting the max memory size (even when that value is maxed out).
This is how I wrote it:
for(i in 1:nrow(data)){
if(is.na(data$Term_code[i]) && !(is.na(data$Term_code1[i]))){
data$Term_code[i] <- data$Term_code1[i]
}
} # Took a very long time to run, but improves data a lot
for(i in 1:nrow(data)){
if(nchar(data$Term_code[i]) < (nchar(data$Term_code1[i]))){
data$Term_code[i] <- data$Term_code1[i]
}
} # Broke after hours due to memory constraint
How should I have written it?
While I can't provide the actual data for reproducibility, if the description of these 2 vectors is insufficient perhaps this can approximate it close enough for testing purposes:
a <- data.frame(matrix(nrow = 999999, ncol = 2))
a[, 1] <- runif(min = 201210, max = 201560, n = 999999)
a[,1] <- round(a[,1])
b <- sample(a[,1], 1000)
a[a[,1] %in% b, 1] <- NA
b <- sample(a[,1], 250)
a[a[,1] %in% b, 1] <- substr(b, 5, 6)
a[,2] <- runif(min = 201210, max = 201560, n = 999999)
b <- sample(a[,2], 150)
a[a[,2] %in% b, 2] <- NA