Gerdin Gerdin - 4 months ago 21
R Question

Divide row value by aggregated sum in R data.frame

I have the following data frame

dat <- data.frame(x=c(1,2,3,3,2,1), y=c(3,4,4,5,2,5))


Now I would like to get a third column dividing the y row value by the aggregated y values (based on the unique values in column x). So, then I get row 1 as following: 1,3,0.375; 0.375 has been calculated as 3 / (5+3).

I'm relatively new to R and I hope you can help me. Thank you!

Answer

There are various ways of solving this, here's one

with(dat, ave(y, x, FUN = function(x) x/sum(x)))
## [1] 0.3750000 0.6666667 0.4444444 0.5555556 0.3333333 0.6250000

Here's another possibility

library(data.table)
setDT(dat)[, z := y/sum(y), by = x]
dat
#    x y         z
# 1: 1 3 0.3750000
# 2: 2 4 0.6666667
# 3: 3 4 0.4444444
# 4: 3 5 0.5555556
# 5: 2 2 0.3333333
# 6: 1 5 0.6250000

Here's a third one

library(dplyr)
dat %>%
  group_by(x) %>%
  mutate(z = y/sum(y))

# Source: local data frame [6 x 3]
# Groups: x
# 
# x y         z
# 1 1 3 0.3750000
# 2 2 4 0.6666667
# 3 3 4 0.4444444
# 4 3 5 0.5555556
# 5 2 2 0.3333333
# 6 1 5 0.6250000