Imputing missing values by mean by id column in R

Question

This is fairly straight forward but i am unable to do it. My data frame has a id variable which is repeating. For same id I want to replace the NAs in other continuous variable(rating and sur) with their corresponding mean. can anyone pls suggest

ID rating Sur
101 60 0.7687
101 78 NA
101 NA 0.765
102 60 NA
102 NA 0.654 
102 75 0.435
103 NA 0.576
103 68 0.875
103 70 NA 

Answer 1

If you want the mean, you could use dplyr syntax:

df = structure(list(ID = c(101L, 101L, 101L, 102L, 102L, 102L, 103L,103L, 103L), 
                    rating = c(60L, 78L, NA, 60L, NA, 75L, NA, 68L, 70L), 
                    Sur = c(0.7687, NA, 0.765, NA, 0.654, 0.435, 0.576, 0.875, NA)), 
               .Names = c("ID", "rating", "Sur"), class = "data.frame", row.names = c(NA,-9L))


library(dplyr)
df %>% 
  group_by(ID) %>% 
  mutate(rating = ifelse(is.na(rating), mean(rating, na.rm = TRUE), rating),
         Sur = ifelse(is.na(Sur), mean(Sur, na.rm = TRUE), Sur))


     ID rating     Sur
  <int>  <dbl>   <dbl>
1   101   60.0 0.76870
2   101   78.0 0.76685
3   101   69.0 0.76500
4   102   60.0 0.54450
5   102   67.5 0.65400
6   102   75.0 0.43500
7   103   69.0 0.57600
8   103   68.0 0.87500
9   103   70.0 0.72550

for other imputations you can look at packages like mi, mice, caret, amelia or simputation. And I'm probably forgetting a lot of others. Personally I like the simplicity of simputation.

For example median imputation is just one line: impute_median(df, rating + Sur ~ ID)

Answer 2

You can use for loops for find mean and replacing them with NA for unique ids

for(i in unique(df$ID)){
  df$rating[df$ID==i] <- ifelse(is.na(df$rating[df$ID==i]), mean(df$rating[df$ID==i],na.rm=T), df$rating)
  df$Sur[df$ID==i] <- ifelse(is.na(df$Sur[df$ID==i]), mean(df$Sur[df$ID==i],na.rm=T), df$Sur)
}

 ID rating     Sur
1 101   60.0 0.76870
2 101   78.0 0.76685
3 101   69.0 0.76500
4 102   60.0 0.54450
5 102   67.5 0.76685
6 102   69.0 0.76500
7 103   69.0 0.76870
8 103   78.0 0.76685
9 103   69.0 0.72550

Answer 3

With the current version of simputation you can impute group means with the following trick:

impute_lm(df, rating ~ 1 | id)

This is linear regression imputation without predictors (hence: mean). The grouping makes sure group means are imputed.

Using simputation (>=0.2.1) [not on cran yet] you can do:

impute_proxy(df, rating ~ mean(rating,na.rm=TRUE) | id)

At the moment this is not on CRAN but you can install it from the drat repo as described here