sum count across multiple variables

user_7475830 0
Viewed 174

I feel like this should be very easy, but I can't get it to work.

Data are the three columns, fourth column is what I am looking for that I can't get to render out:

eg_data <- data.frame(
id = c(1,1,1,2,2,3,3,3,3,3,3,4,4,5,5,5,5),
date = c("11/1", "11/1", "11/2", "11/1", "11/5", "11/5", "11/4", "11/1", 
"11/1", "11/2", "11/4", "11/3", "11/3", "11/2", "11/3", "11/2", "11/1"),
sales = c(2,5,4,1,2,1,4,5,3,8,1,2,4,1,1,3,2),
sum_id_day = c(7,7,4,1,2,1,5,8,8,8,5,6,6,4,1,4,2))

In this example, for each ID/day combination, I need to see total sales. So for ID 1 on date 11/1, there were a total of 7 sales. I do not need a cumulative sum by row, I need a total sum for each combination.

I have tried aggregate and variations of ave/count, but I keep running into issues where the factor levels aren't the same, replacement has X rows, data has X + Y rows, arguments must have same length, etc. I have tried transforming factor to character, date, etc...no dice.

Also, I need this as a new variable in the dataframe, I do not need it to merely display in the console; dplyr is great for that but I need it as a variable.

Any help is appreciated, sorry for what it probably an elementary question.

Thank you!

summarize r group-by average
1 Answers

We can use mutate after grouping by 'id', 'date'

library(dplyr)
eg_data <- eg_data %>%
             group_by(id, date) %>%
             mutate(TotalSum = sum(sales))

Or with ave

eg_data$TotalSum = with(eg_data, ave(sales, id, date, FUN = sum))
user_37322710
Related