R语言绑定含日期变量的数据框时出现类型不匹配错误求助

我需要将多个结构相似的Excel工作表导入为R数据框，并用bind_rows()合并成一个大的数据框。每个数据框的结构示例如下：

structure(list(ID = c(58, 348, 29, 426, 426, 426, 426, 62, 107, 
150, 53, 321, 503, 492, 553, 176, 431, 238, 56, 437, 451, 88, 
447, 199, 402, 542, 592, 475, 457, 324, 30, 306, 379, 251, 54, 
370, 119, 494, 81, 115, 224), Prénom = c(NA, "X", "Y", "Z", "Z", "Z", "Z", "H", "A", "B", "C", "D", "R", "F", "I", "J", "K", "L", "B", "U", "K", "G", "S", "S", "A", "J", "P", "H","D", "X", "W", "L", "F", "K", "K", "I", "M", "J", "L", "I", "F"), caller_id = c("XY", "XY", "XY", "XY", "XY", NA, NA, "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY","XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY", "XY"), Maternité = c("YHN", "YHN","OPS", "OPS", "OPS", NA, NA, "OPS", "OPS", "YHN", 
"YHN", "YHN", "YHN", "YHN", "YHN", "OPS", "OPS", "OPS", NA, NA, "OPS", "OPS", "YHN",  NA, "OPS", "OPS", "YHN", "YHN", "YHN", "YHN", "YHN", "YHN", "OPS", 
"OPS", "OPS", NA, NA, "OPS", "OPS", "YHN",NA, "OPI", "OPI", "KB"), `Date of the survey` = structure(c(1663891200, 1667952000, 1663632000, 1668988800, 1668988800, NA, NA, 1664150400, 
1664841600, 1665360000, 1663804800, 1667779200, 1669852800, 1669766400, 
1670457600, 1665446400, 1668988800, 1666224000, 1663891200, 1669075200, 
1669248000, 1664409600, 1669161600, 1665964800, 1668556800, 1670371200, 
1670976000, 1669593600, 1669248000, 1667779200, 1663632000, 1667433600, 
1668470400, 1666569600, 1663891200, 1668384000, 1664928000, 1669766400, 
1664323200, 1664841600, 1666137600), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), Date_dernier_appel = structure(c(1681430400, 1681430400, 
1681430400, 1682035200, NA, 1683849600, 1684108800, 1681430400, 
1681430400, 1681430400, 1681171200, 1681430400, 1681344000, 1681344000, 
1681344000, 1681171200, 1681344000, 1681344000, 1681344000, 1681344000, 
1681344000, 1681344000, 1681344000, NA, 1679443200, 1679616000, 
1678320000, 1679961600, 1679961600, 1679961600, 1679961600, 1681344000, 
1681171200, 1681171200, 1681171200, 1681171200, 1681171200, 1677542400, 
1679616000, 1679702400, 1679788800), class = c("POSIXct", "POSIXt"
), tzone = "UTC"),)

错误信息 #

合并时出现类型不匹配错误：

Error in `bind_rows()`:
! Can't combine `sheet1$Date_dernier_appel` <datetime<UTC>> and `sheet2$Date_dernier_appel` <character>.
Backtrace:
 1. dplyr::bind_rows(all_sheets, .id = "sheet_name")

我尝试的代码 #

我试图转换所有以"/2023"结尾的日期列，但未解决问题，代码如下：

# Import sheets from the Data folder
excel_files <- list.files("../T2/Data", pattern = "\\.xlsx", full.names = TRUE)

# Create a list to store all sheets
all_sheets <- list()

# Convert all dates within each sheet into the same format
for (file in excel_files) {
  sheet_name <- tools::file_path_sans_ext(basename(file))
  sheet <- read_excel(file)
  
  # Apply transformation to columns ending with "/2023"
  sheet <- mutate(sheet, across(ends_with("/2023"), ~ as.Date(.)))
  
  all_sheets[[sheet_name]] <- sheet
}

# Combine all sheets into one big dataset
admin_support <- bind_rows(all_sheets, .id = "sheet_name")

注：Date_dernier_appel列的值均以"/2023"结尾，请求帮助解决合并错误。

问题核心是你的列筛选逻辑错误：ends_with("/2023")匹配的是列名以"/2023"结尾，但Date_dernier_appel是列名，列值才是以"/2023"结尾，所以这个筛选根本没作用到目标列上。

你需要直接针对Date_dernier_appel列进行类型转换，确保所有工作表中的该列都是统一的日期时间类型。修改后的代码如下：

# Import sheets from the Data folder
excel_files <- list.files("../T2/Data", pattern = "\\.xlsx", full.names = TRUE)

# Create a list to store all sheets
all_sheets <- list()

# Convert all dates within each sheet into the same format
for (file in excel_files) {
  sheet_name <- tools::file_path_sans_ext(basename(file))
  sheet <- read_excel(file)
  
  # 统一转换Date_dernier_appel列为POSIXct类型（与示例中的类型一致）
  sheet <- mutate(sheet, 
                  Date_dernier_appel = as.POSIXct(Date_dernier_appel, format = "%d/%m/2023", tz = "UTC"),
                  # 同时确保Date of the survey列类型统一，避免后续问题
                  `Date of the survey` = as.POSIXct(`Date of the survey`, tz = "UTC"))
  
  all_sheets[[sheet_name]] <- sheet
}

# Combine all sheets into one big dataset
admin_support <- bind_rows(all_sheets, .id = "sheet_name")

关键说明 #

• 如果你的日期格式不是%d/%m/2023（日/月/年），请调整format参数，例如%m/%d/2023对应月/日/年格式。
• 即使部分工作表中该列已经是日期类型，as.POSIXct也会自动保留原有类型，不会报错。
• 同时统一Date of the survey列的类型，避免后续出现类似的合并冲突。

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