# 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!

-
What have you tried so far? –  Eric Fail May 16 '12 at 3:46
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.. –  user1203297 May 16 '12 at 3:56
Have you got as far as reading the data into R to make a data table? –  neilfws May 16 '12 at 4:10
I've read the data in via : abc = read.csv("dataset.csv") –  user1203297 May 16 '12 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 –  Tyler Rinker May 16 '12 at 4:13
show 3 more comments

## migrated from stackoverflow.comMay 16 '12 at 3:34

This question came from our site for professional and enthusiast programmers.

## 3 Answers

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 –  user1203297 May 16 '12 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)) –  user1203297 May 16 '12 at 4:35
Did you just delete your comments? I'm sorry I'm actually awful at this! –  user1203297 May 16 '12 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 –  user1203297 May 16 '12 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 May 16 '12 at 5:05
show 7 more comments

(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! –  user1203297 May 16 '12 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 May 16 '12 at 13:26
add comment

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! –  user1203297 May 16 '12 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 –  Tyler Rinker May 16 '12 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? –  user1203297 May 16 '12 at 5:00
I thought this wasn't HW? –  Tyler Rinker May 16 '12 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 –  user1203297 May 16 '12 at 5:08
add comment