I have a large numeric dataset (~700 rows, 350,000 columns, reading in as a data.table in R) containing some NA's that I would like to replace with column means as quickly as possible. I found a previous post that replaces NA's with 0, but when I modify the solution to instead input column means, I get j, the column number. It seems like I must be missing something obvious...Any suggestions on how to calculate column means using this method?
Fastest way to replace NAs in a large data.table
#original code
f_dowle3 = function(DT) {
for(j in seq_len(ncol((DT)))
set(DT,which(is.na(DT[[j]])),j,0)
}
#modified code
impute = function(DT) {
for(j in 2:ncol(DT))
set(DT,which(is.na(DT[[j]])),j,mean(DT[,j],na.rm = TRUE))
}
test_impute = fread("test_impute.csv")
test_impute
ID snp1 snp2 snp3 snp4
1: 1 2 1 1 0
2: 2 2 2 0 0
3: 3 2 NA 0 NA
4: 4 2 1 2 0
5: 5 2 NA 2 0
6: 6 2 1 1 0
7: 7 1 1 NA 0
8: 8 NA 1 0 0
9: 9 2 2 2 NA
10: 10 1 1 0 0
impute(test_impute)
test_impute
ID snp1 snp2 snp3 snp4
1: 1 2 1 1 0
2: 2 2 2 0 0
3: 3 2 3 0 5
4: 4 2 1 2 0
5: 5 2 3 2 0
6: 6 2 1 1 0
7: 7 1 1 4 0
8: 8 2 1 0 0
9: 9 2 2 2 5
10: 10 1 1 0 0
You can't use dt1[, j] to grab a column from a data table.
dt1[, 1]
# [1] 1
dt1[, 2342]
# [1] 2342
Change DT[, j] to DT[[j]] to fix.
First some data:
set.seed(47)
n = 10
ncol = 10
dt1 = data.table(replicate(ncol, expr = {
ifelse(runif(n) < 0.2, NA_real_, rpois(n, 10))
}))
impute1 = function(DT) {
for (j in 2:ncol(DT))
set(DT, which(is.na(DT[[j]])), j, mean(DT[[j]], na.rm = TRUE))
}
dt1
# V1 V2 V3 V4 V5 V6 V7 V8 V9 V10
# 1: 6 11 10 7 13 10 12 8 13 12
# 2: 10 8 NA 7 16 10 10 8 5 5
# 3: 14 7 9 9 NA 13 9 NA 10 NA
# 4: 4 4 13 10 7 10 14 8 13 15
# 5: 7 NA 8 NA 12 NA 15 10 11 8
# 6: 6 9 7 15 NA 5 12 15 10 5
# 7: 4 9 5 NA 10 12 9 8 12 14
# 8: 12 8 NA 9 7 NA 11 4 8 11
# 9: 8 10 12 14 10 NA 11 9 10 10
# 10: 7 6 NA 13 8 14 11 6 10 NA
impute1(dt1)
dt1
# V1 V2 V3 V4 V5 V6 V7 V8 V9 V10
# 1: 6 11 10.000000 7.0 13.000 10.00000 12 8.000000 13 12
# 2: 10 8 9.142857 7.0 16.000 10.00000 10 8.000000 5 5
# 3: 14 7 9.000000 9.0 10.375 13.00000 9 8.444444 10 10
# 4: 4 4 13.000000 10.0 7.000 10.00000 14 8.000000 13 15
# 5: 7 8 8.000000 10.5 12.000 10.57143 15 10.000000 11 8
# 6: 6 9 7.000000 15.0 10.375 5.00000 12 15.000000 10 5
# 7: 4 9 5.000000 10.5 10.000 12.00000 9 8.000000 12 14
# 8: 12 8 9.142857 9.0 7.000 10.57143 11 4.000000 8 11
# 9: 8 10 12.000000 14.0 10.000 10.57143 11 9.000000 10 10
# 10: 7 6 9.142857 13.0 8.000 14.00000 11 6.000000 10 10
Another option would be to pre-compute the column means. colMeans is quite fast, so this might be faster overall, especially with as many column as you have.
impute2 = function(DT) {
means = colMeans(DT, na.rm = T)
for (j in 2:ncol(DT))
set(DT, which(is.na(DT[[j]])), j, means[j])
}