'How to correctly aggregate dates by weeks for each group separately

dput()

a=structure(list(sales_point_id = c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 15L, 15L, 15L, 15L, 15L, 15L), calendar_id_operday = c(20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 20210102L, 
20210102L, 20210102L, 20210102L), line_fact_amt = c(23749.14, 
1000, 3050, 1550, 8900, 1550, 0, 300, 0, 499, 5450, 300, 0, 499, 
599, 599, 6050, 300, 599, 1400, 300, 0, 2000, 700, 0, 5990, 8877, 
1999, 257.5, 200, 361, 300, 1990, 2453, 3140, 0, 0, 199, 599, 
10990, 7990, 773, 400, 6000, 2269, 2000, 1999, 999, 300, 0, 200, 
11990, 300, 200, 3599, 200, 50, 100, 100, 100, 50, 0, 1836, 19749, 
1399, 1266, 1538, 6031, 34846, 200, 1799, 250, 899, 4049, 379, 
799, 200, 200, 200, 599, 999, 0, 0, 300, 300, 3499, 4211, 626, 
12801, 999, 899, 799, 299, 1218, 200, 99, 999, 590, 200, 17990
)), row.names = c(NA, 100L), class = "data.frame")

calendar_id_operday is date column. Format 20210102 is (year|mon|day). I need aggregate line_fact_amt column by week for each sales_point_id separately by sum. I try do so

df2<-a %>% group_by( weekk = week(ymd(calendar_id_operday)), line_fact_amt ) %>% 
  summarise(new = sum(sales_point_id))

but i get not needed result

   weekk line_fact_amt   new
   <dbl>         <dbl> <int>
 1     1            0     40
 2     1           50     10
 3     1           99     15
 4     1          100     15
 5     1          199      2
 6     1          200     76
 7     1          250      8
 8     1          258.     2
 9     1          299      8
10     1          300     36

I need result like this

sales_point_id  calendar_id_operday line_fact_amt
1   20210102    100
1   20210109    200

where 100 and 200 is sum for week for this sales_point_id by line_fact_amt. How can i get needed result?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source