在R中按年份合并指定CSV文件并基于相同列执行全连接

核心思路 #

先按年份对CSV文件名分组，再批量读取每组内的文件，最后基于共同列执行全连接（full join），得到分年份的合并数据框。

实现步骤 #

1. 加载数据处理所需工具包
2. 按年份对文件名进行分组
3. 读取每组内的CSV文件，并通过全连接合并为单一年份的数据框

完整代码 #

# 加载必要包
library(dplyr)
library(purrr)
library(readr)
library(stringr)

# 原始文件名列表
features <- c("ASB_aggregated_by_lsoa_2015.csv",  "ASB_aggregated_by_lsoa_2019.csv",  "ASB_aggregated_by_lsoa_2022.csv", 
              "Bicycle_theft_aggregated_by_lsoa_2015.csv",  "Bicycle_theft_aggregated_by_lsoa_2019.csv", 
              "Bicycle_theft_aggregated_by_lsoa_2022.csv",  "Burglary_aggregated_by_lsoa_2015.csv", 
              "Burglary_aggregated_by_lsoa_2019.csv",  "Burglary_aggregated_by_lsoa_2022.csv", 
              "Criminal_damage_and_arson_aggregated_by_lsoa_2015.csv",  "Criminal_damage_and_arson_aggregated_by_lsoa_2019.csv", 
              "Criminal_damage_and_arson_aggregated_by_lsoa_2022.csv",  "Drugs_aggregated_by_lsoa_2015.csv", 
              "Drugs_aggregated_by_lsoa_2019.csv",  "Drugs_aggregated_by_lsoa_2022.csv",  "Other_crime_aggregated_by_lsoa_2015.csv", 
              "Other_crime_aggregated_by_lsoa_2019.csv",  "Other_crime_aggregated_by_lsoa_2022.csv", 
              "Other_theft_aggregated_by_lsoa_2015.csv",  "Other_theft_aggregated_by_lsoa_2019.csv", 
              "Other_theft_aggregated_by_lsoa_2022.csv")

# 按年份分组文件名
grouped_files <- features %>%
  tibble(filename = .) %>%
  mutate(year = str_extract(filename, "\\d{4}")) %>%
  group_split(year, .keep = FALSE) %>%
  set_names(c("2015", "2019", "2022"))

# 定义合并函数：读取文件并按共同列全连接
merge_year_data <- function(file_list) {
  file_list$filename %>%
    map(read_csv) %>%
    reduce(full_join, by = intersect(names(.), names(.y)))
}

# 执行合并，得到分年份的数据框列表
yearly_data <- map(grouped_files, merge_year_data)

# 单独提取各年份数据框（可选）
data_2015 <- yearly_data[["2015"]]
data_2019 <- yearly_data[["2019"]]
data_2022 <- yearly_data[["2022"]]

关键说明 #

• str_extract从文件名中提取4位年份数字，确保分组准确
• reduce(full_join, ...)会依次将组内所有数据框按全部共同列执行全连接，保留所有行和列
• 如果需要指定特定连接键（比如仅按LSOA_code），可修改by参数为by = "LSOA_code"，替换为你的实际共同列名

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