# Filling NAs in a dataset with column medians in R

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!

• well I was going to do the Median([name of dataset]\$[name of column]) -> 1 then do that for all the columns then finally do several replaces for each column so where ==Hi in that column replace with 1,2,3,4,etc..
– Jay
Commented May 16, 2012 at 3:56
• Have you got as far as reading the data into R to make a data table? Commented May 16, 2012 at 4:10
• I've read the data in via : abc = read.csv("dataset.csv")
– Jay
Commented May 16, 2012 at 4:12
• Why tell us it says Hi instead of NA? NA is a lot easier to deal with. If your data doesn't contain Hi but NAs it kinda infuriates me in that the answer to solving this is so much easier LINK Commented May 16, 2012 at 4:13
• OK. read.table is usually better, but read.csv is OK if you're happy with the result. Your question is unclear; first you say "median of each row", then "median of the corresponding column." Which is it? Or you really do want both? Commented May 16, 2012 at 4:15

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))

• I posted an image =D
– Jay
Commented May 16, 2012 at 4:08
• hence I'm left with this function right? df=apply(df,2,function(x) x = as.numeric <- median(as.numeric(as.character(x)), na.rm=TRUE))
– Jay
Commented May 16, 2012 at 4:35
• Did you just delete your comments? I'm sorry I'm actually awful at this!
– Jay
Commented May 16, 2012 at 4:40
• alright I do get all the medians with the headings above them! Brilliant! However they're not replacing the NA's instead it's deleteing the entire dataset and JUST giving me the medians with headings above them
– Jay
Commented May 16, 2012 at 4:45
• 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(as.numeric(as.character(x)), na.rm=TRUE) #convert the item with NA to median value from the column x #display the column } ss=apply(df,2,f) where ss will be your result in matrix, if you want, you can convert into data-frame.
– Subs
Commented May 16, 2012 at 5:05

(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.

• I'm sorry I really don't understand! I simply pasted this in replacing dfrm with the name of my dataset- and the word that needs replacing is no longer 'Hi' that was my friends screwup it's now all NA's. So i swapped Hi for NA. And I just got thrown the error: Warning message: In [<-.factor(*tmp*, x == "NA", value = 5) : invalid factor level, NAs generated not a clue what this means!
– Jay
Commented May 16, 2012 at 4:00
• Oh. It was factors. @Subs has almost the right answer. When you do column operations, it is more efficient to use lapply. The correct method for factors is described in the FAQ section 7.10 and illustrated in Subs' answer. (I will edit mine but Subs deserves the check.)
– DWin
Commented May 16, 2012 at 13:26

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)

• Sorry about the whole 'Hi' issue- I didn't mean to annoy you!
– Jay
Commented May 16, 2012 at 4:23
• @user1203297 Live and learn, we all do, check out the link I gave above as it's an easy way to impute missing values. Just switch the mean in the code for median Commented May 16, 2012 at 4:55
• Sadly we're limited to the libraries we're allowed to use- I already tried otherwise! Sorry! That other guys solution has got me 80% there. I typed : df=apply(df,2,function(x) x = as.numeric <- median(as.numeric(as.character(x)), na.rm=TRUE)) This gives me the medians for each and every column but it also deletes the data and doesn't replaces the now NA's. Easiest way about editing this code to make it work?
– Jay
Commented May 16, 2012 at 5:00
• I thought this wasn't HW? Commented May 16, 2012 at 5:03
• It's not- it's a research task we have to use R for since we're meant to be creating our own cut down functional language by the end of the year. This is one of the 'research specifications' we've been given to follow through. I hate this already, I much prefer my object orientated programming lol
– Jay
Commented May 16, 2012 at 5:08