R: apply simple function to specific columns by gr

Question

I have a data set with 2 observations for each person. There are more than 100 variables in the data set. I would like to fill in the missing data for each person, with the available data for the same variable. I can do this manually with dplyr mutate function, but it will be cumbersome to do that for all the variables that needs to be filled in.

Here is what I tried, but it failed:

> # Here's data example
> # https://www.dropbox.com/s/a0bc69xgxhaeguc/data_xlsc.xlsx?dl=0
> # I have already attached it to my working space
> 
> names(data)
 [1] "ID"   "Age"  "var1" "var2" "var3" "var4" "var5" "var6" "var7" "var8" "var9"
> head(data)
Source: local data frame [6 x 11]

  ID Age var1 var2  var3 var4 var5 var6  var7 var8 var9
1  1  50 27.5 1.83  92.0   NA   NA   NA    NA   NA  5.1
2  1  NA   NA   NA    NA 3.54 30.2 27.9 64.34 60.8   NA
3  2  51 33.7 1.77 105.6   NA   NA   NA    NA   NA  5.2
4  2  NA   NA   NA    NA 4.05 36.4 38.7 67.75 63.7   NA
5  3  43 26.3 1.84  89.1   NA   NA   NA    NA   NA  4.8
6  3  NA   NA   NA    NA 3.77 24.4 21.9 67.97 64.2   NA

> # As you can see above, for each person (ID) there are missing values for age and other variables.
> # I'd like to fill in missing data with the available data for each variable, for each ID
> 
> #These are the variables that I need to fill in
> desired_variables <- names(data[,2:11])
> 
> # this is my attempt that failed
> 
> data2 <- data %>% group_by(ID) %>% 
+      do(
+      for (i in seq_along(desired_variables)) {
+           i=max(i, na.rm=T)
+      }
+ )
Error: Results are not data frames at positions: 1, 2, 3

Desired output for the first person:

  ID Age var1 var2  var3 var4 var5 var6  var7 var8 var9

1  1  50 27.5 1.83  92.0 3.54 30.2 27.9 64.34 60.8  5.1

2  1  50 27.5 1.83  92.0 3.54 30.2 27.9 64.34 60.8  5.1

Answer 1

Here's a possible data.table solution

library(data.table)  
setattr(data, "class", "data.frame") ## If your data is of `tbl_df` class
setDT(data)[, (desired_variables) := lapply(.SD, max, na.rm = TRUE), by = ID] ## you can also use `.SDcols` if you want to specify specific columns
data
#    ID Age var1 var2  var3 var4 var5 var6  var7 var8 var9
# 1:  1  50 27.5 1.83  92.0 3.54 30.2 27.9 64.34 60.8  5.1
# 2:  1  50 27.5 1.83  92.0 3.54 30.2 27.9 64.34 60.8  5.1
# 3:  2  51 33.7 1.77 105.6 4.05 36.4 38.7 67.75 63.7  5.2
# 4:  2  51 33.7 1.77 105.6 4.05 36.4 38.7 67.75 63.7  5.2
# 5:  3  43 26.3 1.84  89.1 3.77 24.4 21.9 67.97 64.2  4.8
# 6:  3  43 26.3 1.84  89.1 3.77 24.4 21.9 67.97 64.2  4.8