Replace NA in a certain column with values from equal key from same column

Question

I created a means column for a group based on a criterium C. Now I want those means to be filled out over the entire column, even when criterium C does not hold. So basically I want to replace NA's with the mean value calculated for that group. You can see the grp, val and C colum in the next Data.table

    grp val C
 1:   1  NA 0
 2:   1  NA 0
 3:   1  42 1
 4:   1  42 1
 5:   2  16 1
 6:   2  16 1
 7:   2  NA 0
 8:   2  NA 0
 9:   3  32 1
10:   3  32 1
11:   3  32 1
12:   3  32 1

So I want to replace the val NA's with the mean value in the same group. Here is sample code of how I attempt to do it. Basicly I extract another data.table, remove the NA's and duplicates and then try to merge it with the original table.

x <- data.table(grp=c(1,1,1,1,2,2,2,2,3,3,3,3),val=c(NA,NA,42,42,16,16,NA,NA,32,32,32,32),C=c(0,0,1,1,1,1,0,0,1,1,1,1))
y <- x[!is.na(val),]
y <- y[!duplicated(y),]
setkey(x,grp)
setkey(y,grp)
x[y,val:=val,by=grp]

while this does not give any errors it leaves the original column val untouched. What am I doing wrong? what would be a better approach?

Answer 1

So it seems like this question is driving lots of "noise", so I'll add this as an answer.

So data.table has an "assignment by reference operator" which is := (see here for more info and use cases/benchmarks).

This operator is assigning values to all the members of the particular group (although you can also use it without grouping by anything), similar to mutate function in dplyr or ave and transform in base R, but it does it by reference (which isn't too important for this question specifically, but is probably its greatest advantage over the equivalents in other packages/base R), i.e., it is updating the data set itself without creating copies while using the <- operator.

To sum things up, if you want to calculate some metric per group and assign it to each value in that particular group, use :=.

On other hand, if you want just the summary, use = instead (with combination with list() or just .()), or if you don't want to name the result of the aggregation, you don't have to use anything at all as in:

x[, .(val = mean(val, na.rm = TRUE)), grp] 

Or

x[, list(val = mean(val, na.rm = TRUE)), grp]

Or just

x[, mean(val, na.rm = TRUE), grp] # will call the aggregated variable `V1` by default

The equivalents for this in dplyr would be summarise and in base R it would be aggregate or sometimes tapply.

---

That being said, in your specific case you would use the := operator in order to assign the mean value per group to each value in that particular group as in:

x[, val := mean(val, na.rm = TRUE), grp]

Answer 2

For imputing the NA's with group mean, data.table and dplyr would work well (data.table vs dplyr is a separate discussion). Refer @ David Arenburg's comment for data.table method code for replacing NA with mean.

Using dplyr:

library(dplyr)
df %>% group_by(grp) %>% mutate(val= replace(val, is.na(val), mean(val, na.rm=TRUE))) # ifelse can also be tried instead of replace

Less elegant way is through a custom function combined with ddply:

library(plyr)
# function to replace NA with mean for that group
impute.mean <- function(x) replace(x, is.na(x), mean(x, na.rm = TRUE))

df <- ddply(df, ~ grp, transform, val = impute.mean(val))