3

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!

Adam_S
  • 687
  • 2
  • 12
  • 24

1 Answers1

1

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))
thelatemail
  • 91,185
  • 12
  • 128
  • 188
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    I edited the answers slightly, I need to produce the sum_id_day column; it was an example of what I wanted but could not get. Both these work, thank you so much! – Adam_S Nov 08 '18 at 22:38