Filling NAs in a dataset with column medians in R

Question

I have a massive dataset with titles across the top for the row. All the values in the dataset are number minus about 50 of them which are NA.

I need to find the median of each column whilst somehow not selecting the title of the column. Then once I have the median of that row I need to replace all potential 'NA's with the median of the corresponding column!

Link to image of dataset.

Answer 1

I couldn't comment here due to low reputation score but please post your sample data how it looks like, what you want along with your question. Just words is too much confusing...

Also this question should belong to stackoverflow.

Edit: Use Dwin's method:

f=function(x){
   x<-as.numeric(as.character(x)) #first convert each column into numeric if it is from factor
   x[is.na(x)] =median(x, na.rm=TRUE) #convert the item with NA to median value from the column
   x #display the column
}
ss=data.frame(apply(df,2,f))

Answer 2

(Some moderator must have a warped sense of what is R and what is statistics. This is a coding question if I ever saw one.) Since the columns are of necessity "character" the values will be "character".

new <- lapply( dfrm, function(x) x[x=="Hi"] <- median(as.numeric(as.character(x)), na.rm=TRUE) )

If they need to be numeric you can do this afterwards:

newnum <- lapply(new, function(x) as.numeric(as.character(x))

It's possible you may need to also use as.data.frame to get then back to the original class.

Answer 3

This is long and likely a much better response is available. This also assumes that the columns with His are factors. If not you'll have to make them as such. Here it is with a fake data set so we can play along at home (I tried to include corner cases with NA):

dat <- structure(list(id = structure(1:6, .Label = c("101", "102", "103", 
    "104", "105", "106"), class = "factor"), taxa = structure(c(1L, 
    2L, 2L, 1L, 1L, 2L), .Label = c("collembola", "mite"), class = "factor"), 
        length = structure(c(4L, 1L, NA, 5L, 3L, 5L), .Label = c("0.9", 
        "1.1", "1.5", "2.1", "Hi"), class = "factor"), width = structure(c(4L, 
        2L, 3L, 5L, 1L, 5L), .Label = c("0.5", "0.7", "0.8", "0.9", 
        "Hi"), class = "factor")), .Names = c("id", "taxa", "length", 
    "width"), row.names = c(NA, -6L), class = "data.frame")

#        id       taxa length width
#     1 101 collembola    2.1   0.9
#     2 102       mite    0.9   0.7
#     3 103       mite   <NA>   0.8
#     4 104 collembola     Hi    Hi
#     5 105 collembola    1.5   0.5
#     6 106       mite     Hi    Hi

replacer <- function(dat, replace=NA, with=0){
    h <- is.vector(dat); i <- is.matrix(dat); j <- is.data.frame(dat)
    y <- as.matrix(dat)
    if (is.na(replace)) {
          y[is.na(y)] <- with
    } else { 
          y[y==replace] <- with
    }
    if(h) y <- as.vector(y)
    if(i) y <- as.matrix(y)
    if(j) y <- as.data.frame(y)
    return(y)
} #silly replacer function


dat2 <- replacer(dat, "Hi", NA)
L1 <- lapply(dat2[, 3:4], as.numeric)
meds <- lapply(L1 , median, na.rm = TRUE)
datsub <- dat[, 3:4]

L2 <- lapply(seq_len(2), function(i) 
    as.numeric(replacer(datsub[, i], "Hi", meds[[i]])))
names(L2) <- names(L1)
L3 <- list(dat[, 1:2], L2)
do.call(data.frame, L3)

Which yields:

   id       taxa length width
1 101 collembola    2.1   0.9
2 102       mite    0.9   0.7
3 103       mite     NA   0.8
4 104 collembola    2.0   2.5
5 105 collembola    1.5   0.5
6 106       mite    2.0   2.5

Here's solution using plyr filling in NA not Hi:

#fake data
dat <- read.table(text = "id    taxa        length  width
101   collembola  2.1     0.9
102   mite        0.9     0.7
103   mite        1.1     0.8
104   collembola  NA      NA
105   collembola  1.5     0.5
106   mite        NA      NA", header=TRUE)


library(plyr)
impute.med <- function(x) replace(x, is.na(x), median(x, na.rm = TRUE))
dat2 <- sapply(dat, function(x){
    if(is.numeric(x)){
            impute.med(x)
        } else {
            x
        }
    }
)

data.frame(dat2)

A non package dependent solution (on the data above):

impute.med <- function(x) {
    z <- median(x, na.rm = TRUE)
    x[is.na(x)] <- z
    return(x)
}


dat2 <- sapply(dat, function(x){
    if(is.numeric(x) & any(is.na(x))){
            impute.med(x)
        } else {
            x
        }
    }
)

data.frame(dat2)