i am still new to R and many things are still hard to execute. The community here has been very helpful! I have yet another problem. 1. Creating a new observation for each group that would be the sum(or weighted sum) of certain variables 2. Creating a weighted sum for a variable that has sometimes NA in it
My dataset:
df = structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L, 4L), ID_name = c("AA", "AA", "BB", "BB", "CC","CC", "DD","DD","DD"),
Volume = c(10L, 20L, 30L, 50L, 50L, 40L, 20L,
30L, 10L), Score= c(0.1L, 0.3L, 0.5L, NA, 0.6L, NA,
0.6L, 0.2L, 0.6L)).Names = c("ID", "ID_name","Volume","Score"), class = "data.frame", row.names = c(NA, -9L))
I want to 1.Create a new observation for each unique ID, that is ID 1, ID 2, ID 3, and ID 4
2. Have these new observations be as follows: ID ID_name Volume Score (weighted average) 1 AA 30 (that is 10+20) (10*0.1+0.3*20)/(10+20) = 0.23 2 BB 80 (30+50) (30*0.5)/30=0.5 (NA row is ignored in score calculation) 3 CC 90 (50+40) (60*0.6)/60=0.6 (NA row is ignored in score calculation) 4 DD 60 (20+30+10) (20*0.6+30*0.2+10*0.6)/60=0.4
I tried mutate function but that doesn't seem to work. Any leads would be very appreciated. Thanks
library(dplyr)
df = data.frame(ID = c(1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L, 4L),
ID_name = c("AA", "AA", "BB", "BB", "CC", "CC", "DD", "DD", "DD"),
Volume = c(10L, 20L, 30L, 50L, 50L, 40L, 20L, 30L, 10L),
Score = c(0.1, 0.3, 0.5, NA, 0.6, NA, 0.6, 0.2, 0.6))
df %>%
mutate(HasScore = ifelse(is.na(Score), 0, 1)) %>%
group_by(ID, ID_name) %>%
summarise(WA = sum(Volume*Score, na.rm = T)/sum(Volume*HasScore),
Volume = sum(Volume)) %>%
ungroup()
# # A tibble: 4 x 4
# ID ID_name WA Volume
# <int> <fctr> <dbl> <int>
# 1 1 AA 0.2333333 30
# 2 2 BB 0.5000000 80
# 3 3 CC 0.6000000 90
# 4 4 DD 0.4000000 60