0
votes
$\begingroup$

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.

$\endgroup$
7
  • $\begingroup$ 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.. $\endgroup$
    – Jay
    May 16 '12 at 3:56
  • 1
    $\begingroup$ Have you got as far as reading the data into R to make a data table? $\endgroup$
    – neilfws
    May 16 '12 at 4:10
  • $\begingroup$ I've read the data in via : abc = read.csv("dataset.csv") $\endgroup$
    – Jay
    May 16 '12 at 4:12
  • $\begingroup$ 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 $\endgroup$ May 16 '12 at 4:13
  • $\begingroup$ 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? $\endgroup$
    – neilfws
    May 16 '12 at 4:15
7
votes
$\begingroup$

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))
$\endgroup$
11
  • $\begingroup$ I posted an image =D $\endgroup$
    – Jay
    May 16 '12 at 4:08
  • $\begingroup$ 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)) $\endgroup$
    – Jay
    May 16 '12 at 4:35
  • $\begingroup$ Did you just delete your comments? I'm sorry I'm actually awful at this! $\endgroup$
    – Jay
    May 16 '12 at 4:40
  • $\begingroup$ 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 $\endgroup$
    – Jay
    May 16 '12 at 4:45
  • $\begingroup$ 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. $\endgroup$
    – Subs
    May 16 '12 at 5:05
6
votes
$\begingroup$

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

$\endgroup$
2
  • $\begingroup$ 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! $\endgroup$
    – Jay
    May 16 '12 at 4:00
  • $\begingroup$ 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.) $\endgroup$
    – DWin
    May 16 '12 at 13:26
1
vote
$\begingroup$

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)
$\endgroup$
5
  • $\begingroup$ Sorry about the whole 'Hi' issue- I didn't mean to annoy you! $\endgroup$
    – Jay
    May 16 '12 at 4:23
  • $\begingroup$ @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 $\endgroup$ May 16 '12 at 4:55
  • $\begingroup$ 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? $\endgroup$
    – Jay
    May 16 '12 at 5:00
  • $\begingroup$ I thought this wasn't HW? $\endgroup$ May 16 '12 at 5:03
  • $\begingroup$ 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 $\endgroup$
    – Jay
    May 16 '12 at 5:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.