如何将多文件混合格式日期时间列转为POSIXct并合并数据集

1. 编写通用混合日期转换函数 #

核心是做一个能同时处理日期字符串和Excel序列号小数的转换工具，逻辑是先尝试解析字符串，失败的再用Excel日期规则转换：

convert_mixed_datetime <- function(x) {
  # 解析常见格式的日期字符串（支持月/日/年 时:分、月/日/两位年 时等格式）
  date_str_result <- lubridate::parse_date_time(x, orders = c("mdy HM", "mdy H", "md HM", "md H"), tz = "UTC")
  
  # 处理Excel序列号：Excel日期基准是1899-12-30（修正闰年bug）
  excel_num <- suppressWarnings(as.numeric(x))
  excel_date_result <- as.POSIXct("1899-12-30", tz = "UTC") + as.difftime(excel_num, units = "days")
  
  # 合并结果：取解析成功的数值，失败的自动用另一种方式的结果
  dplyr::coalesce(date_str_result, excel_date_result)
}

2. 批量处理并合并Excel文件 #

假设所有目标Excel文件都放在./excel_data目录下，用以下代码批量读取、处理、合并：

library(tidyverse)
library(readxl)

# 获取所有Excel文件路径
file_paths <- list.files("./excel_data", pattern = "\\.xlsx$|\\.xls$", full.names = TRUE)

# 批量读取+处理每个文件
processed_data <- map(file_paths, function(path) {
  # 读取单文件数据
  raw_df <- read_excel(path)
  
  # 自动识别可能是日期的字符列：包含日期分隔符或纯数字
  date_col_names <- raw_df %>%
    select(where(is.character)) %>%
    select(where(~ any(str_detect(., "^\\d{1,2}/\\d{1,2}/\\d{2,4}") | suppressWarnings(!is.na(as.numeric(.)))))) %>%
    colnames()
  
  # 转换识别出的日期列
  raw_df %>% mutate(across(all_of(date_col_names), convert_mixed_datetime))
})

# 合并所有数据集：列名匹配自动对齐，缺失列补NA
final_merged_data <- bind_rows(processed_data)

3. 示例数据验证 #

用你提供的示例数据测试，结果和目标完全匹配：

# 处理示例数据框
df1_processed <- df1 %>% mutate(across(c(v1, v3), convert_mixed_datetime))
df2_processed <- df2 %>% mutate(across(c(v1, v5), convert_mixed_datetime))

# 合并结果
merged_result <- bind_rows(df1_processed, df2_processed)
print(merged_result)

注意事项 #

• 时区参数tz可根据实际需求修改，比如国内可用"Asia/Shanghai"
• 如果已经明确哪些列是日期，直接指定列名（如across(c(v1, v3, v5), ...)）比自动识别更高效
• 若有其他日期格式，可在parse_date_time的orders参数中补充，比如"dmy HM"等

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