0

Within a dataframe I need to sum one column (val) by group to compare the result with some planned values (plan). 

# sample data    
df <- data.frame(ID = c(1:10), val = rep(10,10), group = c(1,2,3,2,4,5,5,5,6,7), plan =c(10,22,10,0,11,28,0,0,10,10))

One could calculate the new column "total_val" in several steps, e.g. 

df2 <- aggregate(df$val, by=list(df$group), "sum")
df3 <- merge(df, df2, by.x = "group", by.y = "Group.1",all.x = TRUE)
df3$total_val <- ifelse(df3$plan == 0, 0, df3$x)
df3$x <- NULL

Note: in total_val the calculated group_sum must not be repeated, but appear ONLY in the first line of group like in the column "plan" (see marked lines with red). enter image description here

But I am wondering whether someone knows a function or any other more efficient way to calculate a group_sum WITHIN an existing data frame?

user2006697
  • 1,107
  • 2
  • 11
  • 25
  • 1
    Try `library(dplyr); df %>% group_by(ID) %>% mutate(total_al = sum(val))` – akrun Jun 13 '17 at 12:45
  • Is the second line of your second code snippet supposed to begin with `df3` ? – neilfws Jun 13 '17 at 12:49
  • 1
    As you can learn from the duplicate link, you can use : `df$plan <- ifelse(df$plan==0,0,ave(df$val,df$group,FUN=sum))` – digEmAll Jun 13 '17 at 12:56
  • 2
    Similar to @digEmAll's solution, but using `replace` instead of `ifelse`, we'd do `df$total <- replace(ave(df$val, df$group, FUN=sum), df$plan == 0, 0)`. – lmo Jun 13 '17 at 12:58
  • Thanks, that's great help from both you! Sorry that i missed the other link. – user2006697 Jun 13 '17 at 13:02

0 Answers0