Split row value by aggregated amount in R.frame
I have the following dataframe
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 the third column dividing the y row value by the aggregated y values (based on the unique values in the x column). So, I get line 1 like this: 1,3,0.375; 0.375 was calculated as 3 / (5 + 3).
I am relatively new to R and I hope you can help me. Thank!
source to share
There are various ways to resolve this issue, here is 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 is the third
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
source to share
And of course there is a way for people who think in SQL are very verbose in this case, but generalize nicely to all sorts of other similar problems:
library(sqldf)
dat <- sqldf("
with sums as (
select
x
,sum(y) as sy
from dat
group by x
)
select
d.x
,d.y
,d.y/s.sy as z
from dat d
inner join sums s
on d.x = s.x
")
source to share
Here are some basic R solutions:
1) prop.table Use the base function prop.table
with the ave
following:
transform(dat, z = ave(y, x, FUN = prop.table))
giving:
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
2) sum This also works:
transform(dat, z = y / ave(y, x, FUN = sum))
source to share