MFR MFR - 1 month ago 10
R Question

Average the value of all the variables groups except one

I have the following data

id value

a 2
b 3
c 4
a 6


I wish to ahve average of all the data that exclude a group. In other words, I'd like to set the current id apart and take the average of all the others and repeat it for each id. for instance the first one put all
a
apart and take average of
b
and
c
which is 3.5 ( average of 3 and 4) so, I wish to have the following output:

id value

a 3.5
b 4
c 3.66


My data

df <- data.frame(id = c("a", "b", "c" , "a"), value = c(2,3,4,6) )


Note that this question is different from averaging each group

Answer

One option is data.table

library(data.table)
setDT(df)[,  .(value = (sum(df$value) - sum(value))/(nrow(df)-.N)) , by =  id]
#   id    value
#1:  a 3.500000
#2:  b 4.000000
#3:  c 3.666667

If we need the 6 and 5.5 for 'ids' 'b' and 'c'

 setDT(df)[, .(value = (sum(df$value) - sum(value))/(uniqueN(df$id)-1)) , by = id]
 #   id value
 #1:  a   3.5
 #2:  b   6.0
 #3:  c   5.5
Comments