R: apply a simple function to specific columns using a grouped variable
I have a dataset with 2 observations for each person. There are over 100 variables in the dataset. 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 using the duter mutate function, but it will be cumbersome to do for all the variables that need to be populated.
Here's what I tried but it failed:
> # Here 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 first-person output:
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
source to share
1 answer
Here's a possible solution data.table
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
+5
source to share