Home > other >  How can I aggregate rows by orders?
How can I aggregate rows by orders?

Time:11-21

I have a tibble:

   itemID order dayR           n
    <dbl> <dbl> <date>     <int>
 1      9     1 2018-01-01     1
 2     11     1 2018-01-01     1
 3     19     1 2018-01-01     2
 4     26     1 2018-01-01    96
 5     26     2 2018-01-01     5
 6     26     3 2018-01-01     1
 7     35     1 2018-01-01   379
 8     35     2 2018-01-01    23
 9     35     3 2018-01-01     4
10     35     4 2018-01-01     1

I want to aggregate over the orders and then sum them into n to get a unique itemID, so for example the itemID 26 (1*96 2*5 3*1 = 109):

   itemID  dayR           n
    <dbl>  <date>      <int>
 1    26   2018-01-01   109 
...

Code for reproduction:

structure(list(itemID = c(9, 11, 19, 26, 26, 26, 35, 35, 35, 
                          35), order = c(1, 1, 1, 1, 2, 3, 1, 2, 3, 4), dayR = structure(c(17532, 
                                                                                           17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532, 17532
                          ), class = "Date"), n = c(1L, 1L, 2L, 96L, 5L, 1L, 379L, 23L, 
                                                    4L, 1L)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", 
                                                                                                 "data.frame"))

CodePudding user response:

You can use group_by() and summarize():

df %>% 
  group_by(itemID, dayR) %>% 
  summarize(n=sum(n*order))

Output:

  itemID dayR           n
   <dbl> <date>     <dbl>
1      9 2018-01-01     1
2     11 2018-01-01     1
3     19 2018-01-01     2
4     26 2018-01-01   109
5     35 2018-01-01   441

CodePudding user response:

Using %*%

library(dplyr)
df %>%
   group_by(itemID, dayR) %>% 
    summarise(n = c(n %*% order), .groups = 'drop')

-output

# A tibble: 5 × 3
  itemID dayR           n
   <dbl> <date>     <dbl>
1      9 2018-01-01     1
2     11 2018-01-01     1
3     19 2018-01-01     2
4     26 2018-01-01   109
5     35 2018-01-01   441
  •  Tags:  
  • r
  • Related