如何按采样日期计算化合物占比并对3棵树数据取平均值？

API 探路者

2026-6-16

数据与需求说明

原始数据格式

Date	Tree	Compound	Compound_mg	%mg
13.1	a	C21	5
13.1	b	C21	4	x
13.1	c	C21	9
20.2	a	C21	6
20.2	b	C21	5	x
20.2	c	C21	10
13.1	a	C23	6
13.1	b	C23	6	x
13.1	c	C23	10
20.2	a	C23	5
20.2	b	C23	4	x
20.2	c	C23	9

仅最后一列%mg为待计算列。对3棵树（N=3）采样，在不同日期提取多种化合物，需计算：

每个时间点下，C21占所有化合物的%mg比例（按3棵树取平均值）
C23的计算逻辑与C21完全一致

示例数据集（R代码）

Df <- data.frame(
  rating = 1:12,
  Date = c('13.1','13.1','13.1','20.2','20.2','20.2','13.1','13.1','13.1','20.2','20.2','20.2'),
  Tree = c('a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c'),
  Compound = c("C21","C21","C21","C21","C21","C21","C23","C23","C23","C23","C23","C23"),
  Compound_mg = c(5, 4, 9, 6, 5, 10, 6, 6, 10, 5, 4, 9)
)

尝试的代码（存在问题）

Df2 <- Df %>%
group_by(Compound) %>%
mutate(perc = Compound(mg) / sum(Compound(mg))

解决方案

核心逻辑是先计算单棵树在单个日期下的化合物占比，再按日期+化合物分组取3棵树的平均占比：

library(dplyr)

# 1. 计算单棵树在单个日期下的化合物占比
Df_with_perc <- Df %>%
  group_by(Date, Tree) %>%
  mutate(perc = Compound_mg / sum(Compound_mg)) %>%  # 单树单日的化合物占比
  ungroup()

# 2. 按日期+化合物分组，取3棵树的平均占比（转成百分比格式）
avg_perc <- Df_with_perc %>%
  group_by(Date, Compound) %>%
  summarise(avg_%mg = mean(perc) * 100, .groups = "drop")

# 可选：将平均占比合并回原始数据，方便对应查看
Df_final <- Df_with_perc %>%
  left_join(avg_perc, by = c("Date", "Compound"))