How to calculate the average monthly cost for each product separately in R-CodePudding

Here example data.

sales=structure(list(Product = c(775L, 775L, 775L, 775L, 775L, 775L, 
775L, 775L, 775L, 7756L, 7756L, 7756L, 7756L, 7756L, 7756L, 7756L, 
7756L, 7756L), Day = c("16.01.2020", "17.01.2020", "18.01.2020", 
"19.01.2020", "20.01.2020", "21.01.2020", "22.01.2020", "23.01.2020", 
"24.01.2020", "16.01.2020", "17.01.2020", "18.01.2020", "19.01.2020", 
"20.01.2020", "21.01.2020", "22.01.2020", "23.01.2020", "24.01.2020"
), Price.book. = c(652.79, 1046.55, 5264.37, 3756.38, 7724.47, 
3908.69, 2241.46, 1221.06, 3733.03, 272, 195, 447, 699, 689, 
375, 762, 796, 571), Sales.value.without.VAT..without.bonus. = c(1488.35, 
1431.9, 9735.84, 7239.6, 11859.4, 5700, 3273, 2295.78, 5203.32, 
629, 813, 312, 730, 768, 118, 902, 601, 479), Quantity.in.base.units = c(5L, 
18L, 144L, 15L, 20L, 1500L, 100L, 6L, 12L, 915L, 760L, 738L, 
139L, 339L, 516L, 685L, 221L, 652L), Sales_value = c(1786.02, 
1718.28, 11683.01, 8687.52, 14231.28, 6840, 3927.6, 2754.94, 
6243.98, 701, 619, 554, 821, 619, 850, 886, 714, 583), Gross_profit = c(835.56, 
385.35, 4471.47, 3483.22, 4134.93, 1791.31, 1031.54, 1074.72, 
1470.29, 749, 848, 824, 876, 203, 975, 261, 575, 984), Cost.of.accounting = c(652.79, 
1046.55, 5264.37, 3756.38, 7724.47, 3908.69, 2241.46, 1221.06, 
3733.03, 491, 758, 312, 379, 586, 266, 746, 544, 684), unit.cost = c(130.558, 
58.1416, 36.5581, 250.4253, 386.2235, 2.6057, 22.4146, 203.51, 
311.0858, 307, 732, 442, 467, 309, 854, 139, 440, 635)), class = "data.frame", row.names = c(NA, 
-18L))

I need to calculate the average cost per month for each product separately. Now i do it via excel,but i need via R. To calculate this i use simple formula (sum for month by Price book)/(sum for month by Quantity in base units). For example let's take `Product=775.

Sum for January at Price book=29548.8 and sum for January Quantity in base units=1820. Thus 29548.8/1820=16.23. These values for the month of January 2020 will appear on all lines.

Similarly for product =7756. sum for January at Price book=4806 and sum for January Quantity in base units=4965. Thus 4806/4965=0.96. These values for the month of January 2020 will appear on all lines (Final_cost column)

so as the output

   Product        Day Price.book.
1      775 16.01.2020      652.79
2      775 17.01.2020     1046.55
3      775 18.01.2020     5264.37
4      775 19.01.2020     3756.38
5      775 20.01.2020     7724.47
6      775 21.01.2020     3908.69
7      775 22.01.2020     2241.46
8      775 23.01.2020     1221.06
9      775 24.01.2020     3733.03
10    7756 16.01.2020      272.00
11    7756 17.01.2020      195.00
12    7756 18.01.2020      447.00
13    7756 19.01.2020      699.00
14    7756 20.01.2020      689.00
15    7756 21.01.2020      375.00
16    7756 22.01.2020      762.00
17    7756 23.01.2020      796.00
18    7756 24.01.2020      571.00
   Sales.value.without.VAT..without.bonus. Quantity.in.base.units
1                                  1488.35                      5
2                                  1431.90                     18
3                                  9735.84                    144
4                                  7239.60                     15
5                                 11859.40                     20
6                                  5700.00                   1500
7                                  3273.00                    100
8                                  2295.78                      6
9                                  5203.32                     12
10                                  629.00                    915
11                                  813.00                    760
12                                  312.00                    738
13                                  730.00                    139
14                                  768.00                    339
15                                  118.00                    516
16                                  902.00                    685
17                                  601.00                    221
18                                  479.00                    652
   Sales_value Gross_profit Cost.of.accounting unit.cost Final_cost
1      1786.02       835.56             652.79  130.5580 16.2356044
2      1718.28       385.35            1046.55   58.1416 16.2356044
3     11683.01      4471.47            5264.37   36.5581 16.2356044
4      8687.52      3483.22            3756.38  250.4253 16.2356044
5     14231.28      4134.93            7724.47  386.2235 16.2356044
6      6840.00      1791.31            3908.69    2.6057 16.2356044
7      3927.60      1031.54            2241.46   22.4146 16.2356044
8      2754.94      1074.72            1221.06  203.5100 16.2356044
9      6243.98      1470.29            3733.03  311.0858 16.2356044
10      701.00       749.00             491.00  307.0000  0.9679758
11      619.00       848.00             758.00  732.0000  0.9679758
12      554.00       824.00             312.00  442.0000  0.9679758
13      821.00       876.00             379.00  467.0000  0.9679758
14      619.00       203.00             586.00  309.0000  0.9679758
15      850.00       975.00             266.00  854.0000  0.9679758
16      886.00       261.00             746.00  139.0000  0.9679758
17      714.00       575.00             544.00  440.0000  0.9679758
18      583.00       984.00             684.00  635.0000  0.9679758

How to calculate the average monthly cost for each product separately to get desired output. Thank you for your help.

CodePudding user response：

You may use group_by() with mutate from dplyr

library(dplyr)

sales %>%
  group_by(Product) %>%
  mutate(FinalCost = sum(Price.book.)/sum(Quantity.in.base.units))