基于Vessel_ID识别两个DataFrame的行增减并生成标记结果

一、生成标记增减行的df3 #

核心思路是通过setdiff精准定位新旧版本独有的Vessel_ID，分别标记删除/新增后合并结果：

# 1. 提取df1独有的Vessel_ID（对应需删除的行）
remove_rows <- df1[df1$Vessel_ID %in% setdiff(df1$Vessel_ID, df2$Vessel_ID), ]
remove_rows$add_remove <- "remove"

# 2. 提取df2独有的Vessel_ID（对应需新增的行）
add_rows <- df2[df2$Vessel_ID %in% setdiff(df2$Vessel_ID, df1$Vessel_ID), ]
add_rows$add_remove <- "add"

# 3. 合并生成df3并调整列顺序
df3 <- rbind(remove_rows, add_rows)
df3 <- df3[, c("Name", "Vessel_ID", "special_NO", "add_remove")]

运行后得到的df3与你期望的输出完全一致：

Name Vessel_ID special_NO add_remove
2  Vessel2         2         20     remove
4  Vessel4         4         40     remove
2        x         6         NA        add
3        y         7         NA        add
6  Vessel6        10         NA        add

二、匹配Vessel_ID时用df1的special_NO替换df2中的NA #

提供两种实现方式，按需选择：

基础R实现 #

# 合并df2与df1的Vessel_ID和special_NO字段
merged_df <- merge(df2, df1[, c("Vessel_ID", "special_NO")], 
                   by = "Vessel_ID", suffixes = c("_df2", "_df1"), 
                   all.x = TRUE)
# 替换NA：优先取df1的special_NO，无匹配则保留原df2值
merged_df$special_NO <- ifelse(is.na(merged_df$special_NO_df2), 
                               merged_df$special_NO_df1, 
                               merged_df$special_NO_df2)
# 清理冗余列
merged_df <- merged_df[, c("Name", "Vessel_ID", "special_NO")]

dplyr实现（更简洁） #

library(dplyr)

updated_df2 <- df2 %>%
  left_join(df1[, c("Vessel_ID", "special_NO")], by = "Vessel_ID") %>%
  mutate(special_NO = coalesce(special_NO.x, special_NO.y)) %>%
  select(Name, Vessel_ID, special_NO)

运行后，updated_df2中匹配Vessel_ID的行（如Vessel1、Vessel3、Vessel5）的special_NO会被df1的值替换，未匹配行保留NA。

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