如何基于R语言两个数据框计算资源组合的平均价格（忽略NA）

我有两个R语言数据框：

df 记录ID、年份及对应资源（多资源用逗号分隔）：

df <- data.frame(ID = 1:10, year = c(2005, 2005, 2006, 2007, 2007, 2005, 2006, 2007, 2006, 2005), resources = c("gold", "silver", "bronze", "gold, bronze", "silver, bronze", "gold", "gold, silver, bronze", "bronze", "gold, silver", "silver"))

结构展示：

ID year            resources
1   1 2005                 gold
2   2 2005               silver
3   3 2006               bronze
4   4 2007         gold, bronze
5   5 2007       silver, bronze
6   6 2005                 gold
7   7 2006 gold, silver, bronze
8   8 2007               bronze
9   9 2006         gold, silver
10 10 2005               silver

prices 记录各年份资源的价格：

prices <- data.frame(year = c(2005:2007, 2005:2007, 2005:2007), resource = c("gold", "gold", "gold", "silver", "silver", "silver", "bronze", "bronze", "bronze"), price = c(10, 11, 12, 8, 6, 7, 5, NA, NA))

结构展示：

year resource price
1 2005     gold    10
2 2006     gold    11
3 2007     gold    12
4 2005   silver     8
5 2006   silver     6
6 2007   silver     7
7 2005   bronze     5
8 2006   bronze    NA
9 2007   bronze    NA

需求：从prices匹配价格，为df每个观测计算平均价格：

• 单一资源：取对应年份该资源的价格
• 多资源：取对应年份各资源价格的均值（忽略NA）
• 最终得到指定结果。

使用tidyverse工具包处理，步骤清晰：

1. 加载工具包

library(tidyverse)

2. 拆分多资源字段为多行

df_split <- df %>% separate_rows(resources, sep = ", ")

3. 关联价格数据

df_joined <- df_split %>% left_join(prices, by = c("year", "resources" = "resource"))

4. 分组计算平均价格，同时处理全NA的情况

result <- df_joined %>%
  group_by(ID, year, resources) %>%
  summarise(new_price = mean(price, na.rm = TRUE), .groups = "drop") %>%
  mutate(new_price = ifelse(is.nan(new_price), NA, new_price))

运行后result即为目标结果。

如果不想依赖第三方包，用Base R实现：

1. 定义计算平均价格的函数

calc_avg_price <- function(year_val, resources_str) {
  resources <- strsplit(resources_str, ", ")[[1]]
  price_sub <- prices[prices$year == year_val & prices$resource %in% resources, "price"]
  avg <- mean(price_sub, na.rm = TRUE)
  if (is.nan(avg)) avg <- NA
  return(avg)
}

2. 应用函数到每一行

df$new_price <- mapply(calc_avg_price, df$year, df$resources)

此时df已包含所需的new_price列。

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