如何向已有DataFrame绑定列数据及多CSV合并问题排查

核心问题诊断 #

• 你用map_df把所有CSV行堆叠，导致不同文件的非共同列全部为NA，这就是除pre_contact外其他列无数据的根源。
• status列逻辑错误：应该判断all_v$pre_contact是否存在于gained_v$gained_variation或lost_v$lost_variation的集合中，而非同一行的列值相等。
• 各DataFrame行数不一致，需以all_v（包含核心标识列pre_contact）为基础，通过按行对齐补NA或关联键左连接整合其他表的指定列。

修正代码实现 #

library(tidyverse)
library(data.table)

# 设置工作路径
folder_path <- "/directory/"
setwd(folder_path)

# 读取各文件，仅保留目标列
all_v <- read.csv("all.csv", header = TRUE) %>%
  select(pre_contact)

gained_v <- read.csv("gained.csv", header = TRUE) %>%
  select(gained_variation)

lost_v <- read.csv("lost.csv", header = TRUE) %>%
  select(lost_variation)

SOUTH <- read.csv("SOUTH.csv", header = TRUE) %>%
  select(coord.lat, coord.long, country, Date)

# 1. 处理status列逻辑
status_tbl <- tibble(
  status = case_when(
    all_v$pre_contact %in% gained_v$gained_variation ~ "Gained",
    all_v$pre_contact %in% lost_v$lost_variation ~ "Lost",
    TRUE ~ "Neutral"
  )
)

# 2. 整合所有数据：按行对齐，行数不足补NA
combined_data <- bind_cols(
  all_v,
  status_tbl,
  # 确保其他表行数与all_v一致，多余行截断，不足补NA
  gained_v %>% slice(1:nrow(all_v)) %>% mutate_all(~replace_na(., NA)),
  lost_v %>% slice(1:nrow(all_v)) %>% mutate_all(~replace_na(., NA)),
  SOUTH %>% slice(1:nrow(all_v)) %>% mutate_all(~replace_na(., NA))
)

# 调整为目标列顺序
combined_data <- combined_data %>%
  select(pre_contact, status, gained_variation, lost_variation, coord.lat, coord.long, country, Date)

# 输出结果
write.csv(combined_data, "subset_data.csv", row.names = FALSE)

关联键版本（如果pre_contact是各表的匹配标识） #

如果gained_variation/lost_variation是pre_contact的子集，且SOUTH表也通过pre_contact关联，用左连接更合理：

combined_data <- all_v %>%
  # 关联gained表
  left_join(gained_v, by = c("pre_contact" = "gained_variation")) %>%
  # 关联lost表
  left_join(lost_v, by = c("pre_contact" = "lost_variation")) %>%
  # 关联SOUTH表（假设SOUTH有pre_contact列）
  left_join(SOUTH, by = "pre_contact") %>%
  # 生成status列
  mutate(status = case_when(
    !is.na(gained_variation) ~ "Gained",
    !is.na(lost_variation) ~ "Lost",
    TRUE ~ "Neutral"
  )) %>%
  # 调整列顺序
  select(pre_contact, status, gained_variation, lost_variation, coord.lat, coord.long, country, Date)

结果验证 #

运行后查看subset_data.csv：

• 所有目标列均存在，非pre_contact列会根据行数自动补NA
• status列会正确标记Gained（pre_contact在gained_v中）、Lost（在lost_v中）、Neutral（均不在）

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