如何在R语言中单独计算每个产品的月度平均成本

我之前用Excel计算每个产品的月度平均成本，现在要转用R实现，计算逻辑很明确：每个产品当月的Price.book.总和除以当月Quantity.in.base.units总和，把这个结果填充到该产品当月所有行的Final_cost列里。

给定的示例数据集如下：

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))

比如产品775，2020年1月Price.book.总和是29548.8，Quantity.in.base.units总和是1820，计算得29548.8/1820≈16.23，这个值要填充到该产品1月所有行的Final_cost；产品7756同理得到≈0.96并填充。

方法一：用dplyr（tidyverse）实现 #

这是tidy风格的常用写法，步骤清晰：

1. 把Day列转换成日期格式，提取年月信息
2. 按Product和年月分组，计算每个组的Final_cost值
3. 将计算结果合并回原数据集完成填充

代码如下：

# 未安装dplyr/lubridate的话先执行安装
# install.packages(c("dplyr", "lubridate"))

library(dplyr)
library(lubridate)

sales_final <- sales %>%
  # 转换日期格式并提取年月
  mutate(YearMonth = dmy(Day) %>% format("%Y-%m")) %>%
  # 按产品+年月分组计算平均成本
  group_by(Product, YearMonth) %>%
  mutate(Final_cost = sum(Price.book.) / sum(Quantity.in.base.units)) %>%
  # 取消分组并移除临时列
  ungroup() %>%
  select(-YearMonth)

# 查看结果
print(sales_final)

方法二：基础R实现 #

如果不想加载额外包，用基础R的ave函数也能完成需求：

# 转换日期并提取年月
sales$YearMonth <- format(as.Date(sales$Day, format = "%d.%m.%Y"), "%Y-%m")

# 按产品+年月分组计算并填充Final_cost
sales$Final_cost <- with(sales, 
                         ave(Price.book., Product, YearMonth, 
                             FUN = function(x) sum(x) / sum(Quantity.in.base.units[seq_along(x)])))

# 移除临时列
sales$YearMonth <- NULL

# 查看结果
print(sales)

两种方法都能得到预期输出：产品775的Final_cost统一为≈16.2356，产品7756的统一为≈0.96798。

内容的提问来源于stack exchange，提问作者psysky