合并不同索引平台同变量异表头数据集的技术求助

问题分析 #

你需要保留BehaviouralEconomicsTourism.xlsx的结构，将scopus.csv中变量含义相同但表头拼写不同的数据作为新行追加。原代码的核心问题在于列名映射方式错误，以及数据类型不统一导致的绑定失败，同时没有严格对齐目标数据集的列结构。

修正后的代码 #

# 安装并加载必要包
if (!requireNamespace("tidyverse", quietly = TRUE)) install.packages("tidyverse")
if (!requireNamespace("readxl", quietly = TRUE)) install.packages("readxl")
if (!requireNamespace("writexl", quietly = TRUE)) install.packages("writexl")
if (!requireNamespace("janitor", quietly = TRUE)) install.packages("janitor")

library(tidyverse)
library(readxl)
library(writexl)
library(janitor)

# 加载数据集
behavioral_economics_df <- read_excel("/Users/s5320381/Downloads/BehaviouralEconomicsTourism.xlsx", sheet = "Sheet2")
scopus_df <- read.csv("/Users/s5320381/Downloads/scopus.csv")

# 标准化列名（转为小写下划线格式）
scopus_df <- janitor::clean_names(scopus_df)
behavioral_economics_df <- janitor::clean_names(behavioral_economics_df)

# 列名映射：key是behavioral的列名，value是scopus对应的列名
column_mapping <- c(
  "authors" = "authors",
  "author_full_names" = "author_full_names",
  "article_title" = "title",
  "publication_year" = "year",
  "volume" = "volume",
  "issue" = "issue",
  "publisher" = "publisher",
  "source_title" = "source_title",
  "author_keywords" = "author_keywords",
  "abstract" = "abstract",
  "issn" = "issn",
  "isbn" = "isbn",
  "start_page" = "page_start",
  "end_page" = "page_end",
  "doi" = "doi",
  "language" = "language_of_original_document",
  "doi_link" = "link",
  "affiliations" = "affiliations"
)

# 1. 重命名scopus的列，对齐behavioral的列名
scopus_renamed <- scopus_df %>%
  # 只保留映射中的列 + 需要额外保留的references列
  select(all_of(unname(column_mapping)), references) %>%
  # 重命名为behavioral的列名
  rename(!!!column_mapping)

# 2. 统一数据类型：将两个数据集中的数值/字符列类型对齐
# 先获取behavioral的列类型参考
behavioral_types <- behavioral_economics_df %>%
  summarise(across(everything(), class)) %>%
  pivot_longer(everything(), names_to = "col", values_to = "type")

# 对scopus重命名后的列，按照behavioral的类型转换
scopus_aligned <- scopus_renamed %>%
  mutate(across(
    .cols = all_of(behavioral_types$col),
    .fns = function(x) {
      target_type <- behavioral_types$type[behavioral_types$col == cur_column()]
      switch(target_type,
             "character" = as.character(x),
             "numeric" = as.numeric(x),
             "integer" = as.integer(x),
             x)
    }
  ))

# 3. 追加行：保留behavioral的列顺序，绑定两个数据集
merged_df <- bind_rows(behavioral_economics_df, scopus_aligned)

# 保存结果
writexl::write_xlsx(merged_df, "/Users/s5320381/Downloads/test201223.xlsx")

关键修改说明 #

• 列名映射修正：使用rename(!!!column_mapping)将scopus的列名精准替换为behavioral的列名，确保列完全匹配；同时用select(all_of(unname(column_mapping)))只保留需要的列，避免冗余。
• 数据类型统一：通过读取behavioral数据集的列类型，自动将scopus对应列转换为相同类型，解决类型不兼容导致的绑定失败。
• 严格对齐结构：绑定前确保scopus的列顺序、列名、类型都与behavioral一致，完全保留目标数据集的原始结构。

内容的提问来源于stack exchange，提问作者Valério Souza Neto