4
Customer id    Year     a      b
1              2000     10     2
1              2001     5      3
1              2002     NA     4
1              2003     NA     5
2              2000     2      NA
2              2001     NA     4  
2              2002     4      NA
2              2003     8      10
3              2000     9      NA 
3              2001     10     NA
3              2002     11     12
Rentrop
  • 20,979
  • 10
  • 72
  • 100
user7570943
  • 41
  • 2
  • 2
    `zoo` has useful functions for this type of thing... `zoo::na.aggregate(d[c("a", "b")], d$Customerid, FUN=median)` – user20650 Feb 15 '17 at 19:43

2 Answers2

5

You could do the following:

require(dplyr)
impute_median <- function(x){
  ind_na <- is.na(x)
  x[ind_na] <- median(x[!ind_na])
  as.numeric(x)
}

dat %>% 
  group_by(Customer_id) %>% 
  mutate_at(vars(a, b), impute_median)
Rentrop
  • 20,979
  • 10
  • 72
  • 100
  • I like the implementation of `dplyr` and `magrittr` here, but this doesn't seem return the correct median values when I run it. – sc_evans Feb 15 '17 at 20:00
0

A data.table solution:

dat[, `:=` (a= ifelse(is.na(a), median(a, na.rm=TRUE), a)
            b= ifelse(is.na(a), median(b, na.rm=TRUE), b)), by= "Customer_id"]

This should be, and is, faster than @Floo0's solution above, since he does two scans of each column.

library(data.table)
library(microbenchmark)
set.seed(1234L)

dat <- data.frame(id= rep(c(1:10), each= 100),
                  a= rnorm(1000),
                  b= rnorm(1000))

dat[,2:3] <- apply(dat[,2:3], 2, function(j) {
  idx <- sample.int(1000, 100, replace=F)
  j[idx] <- NA
  return(j)
})

require(dplyr)
impute_median <- function(x){
  ind_na <- is.na(x)
  x[ind_na] <- median(x[!ind_na])
  as.numeric(x)
}


dat2 <- setDT(dat)

microbenchmark(Floo0= {dat %>% 
    group_by(id) %>% 
    mutate_at(vars(a, b), impute_median)},
    alex= {dat[, `:=` (a= ifelse(is.na(a), median(a, na.rm=TRUE), a),
                      b= ifelse(is.na(a), median(b, na.rm=TRUE), b)), by= "id"]})

Unit: milliseconds
  expr      min       lq     mean   median       uq     max neval cld
 Floo0 3.703411 3.851565 4.216543 3.947955 4.167063 7.67234   100   b
  alex 1.265559 1.430002 1.704431 1.486006 1.687710 5.21753   100  a
alexwhitworth
  • 4,839
  • 5
  • 32
  • 59