如何在R中按ID_Plot与Old_ID关联合并Excel内多年树木调查工作表

依赖包安装与加载 #

需要先安装并加载以下工具包：

• readxl：用于读取Excel工作簿数据
• tidyverse：用于数据关联、清洗与格式转换

install.packages(c("readxl", "tidyverse"))
library(readxl)
library(tidyverse)

步骤1：批量读取所有工作表数据 #

首先读取工作簿内的6个目标工作表，同时为每条数据增加对应的调查年份标识：

# 替换为你的Excel文件实际存储路径
file_path <- "树木调查数据.xlsx"
# 筛选出符合命名规则的树木调查工作表
sheet_names <- excel_sheets(file_path) %>% 
  str_subset("^Trees_\\d{4}$")
# 批量读取所有工作表，合并为初始长表
raw_data <- map_dfr(sheet_names, function(sheet) {
  year <- str_extract(sheet, "\\d{4}") %>% as.numeric()
  read_excel(file_path, sheet = sheet) %>% 
    mutate(year = year, .before = everything())
})

步骤2：生成同株树的全局唯一ID映射 #

按调查年份从早到晚排序，在同一样地范围内关联新旧树木ID，生成每株树的唯一标识符：

# 按年份升序排列数据，优先处理最早的调查记录
sorted_data <- raw_data %>% arrange(year)
# 提取所有调查年份的升序列表
year_list <- sort(unique(sorted_data$year))
# 初始化ID映射：最早年份的树木直接用「样地ID_树木当年编号」作为唯一ID
id_mapping <- sorted_data %>% 
  filter(year == year_list[1]) %>% 
  mutate(unique_tree_id = paste0(ID_Plot, "_", ID_tree)) %>% 
  select(ID_Plot, year, ID_tree, unique_tree_id)
# 迭代关联后续年份的树木ID，补全映射表
for (i in 2:length(year_list)) {
  current_year <- year_list[i]
  prev_year <- year_list[i-1]
  current_year_data <- sorted_data %>% filter(year == current_year)
  # 关联上一次调查的映射表，通过Old_ID匹配上一年的树木编号
  current_mapped <- current_year_data %>% 
    left_join(
      id_mapping %>% filter(year == prev_year) %>% select(ID_Plot, ID_tree, unique_tree_id),
      by = c("ID_Plot" = "ID_Plot", "Old_ID" = "ID_tree")
    ) %>% 
    # 当年首次调查的无Old_ID树木，直接生成新的唯一ID
    mutate(unique_tree_id = ifelse(is.na(unique_tree_id), paste0(ID_Plot, "_", ID_tree), unique_tree_id)) %>% 
    select(ID_Plot, year, ID_tree, unique_tree_id)
  # 将当前年份的映射关系合并到总映射表
  id_mapping <- bind_rows(id_mapping, current_mapped)
}

步骤3：生成最终合并宽表 #

将唯一ID映射回原始数据，转换为按年列示胸径数据的目标格式：

final_result <- raw_data %>% 
  left_join(id_mapping, by = c("ID_Plot", "year", "ID_tree")) %>% 
  select(unique_tree_id, ID_Plot, year, DBH_mm) %>% 
  pivot_wider(
    names_from = year,
    values_from = DBH_mm,
    names_prefix = "DBH_"
  )

结果说明 #

输出的final_result即为所需合并表，每行对应一株唯一树木，各年份的DBH数据对应单独列，无对应调查数据的年份单元格为NA值。

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