R语言中批量转换多格式日期至yyyymmdd格式的技术求助

问题描述 #

我是R语言新手，手上有一份40万行、多国家来源的CSV数据集，其中MS_RegistrationDate字段存在四种日期格式：

• dd.mm.yyyy（如30.10.2019）
• Excel日期序列号（如43766）
• yyyymmdd（如20191129）
• yyyy-mm-dd及带时分秒的变种（如2019-09-26T00:00:00.000、2019-09-17）

尝试用as.Date()直接转换时所有值都变成了NA，需要把所有格式统一转为yyyymmdd格式用于后续筛选，数据集dput示例：

structure(list(Model = c("P 280 B6x2/4NA", "P 280 B6x2_4NB", "P 320 B6x2_4NA", "P 320 B6x2_4NA", "P 450 B6x2_4NB", "P 280 B6x2NA", "P 280 B6x2NA", "P 340 B6x2_4NB", "P 280 B6x2NA", "P 280 B6x2NA", "P 450 B6x2NA", "P 450 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 320 B6x2NA", "P 500 B6x2_4NB", "P 500 B6x2*4NB"), Make = c("Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania", "Scania"), Manufacturer = c("Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB", "Scania CV AB"), VehicleGroup = c(9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9), VehicleSubgroup = c("9-RD", "9-RD", "9-RD", "9-RD", "9-LH", "9-RD", "9-RD", "9-RD", "9-RD", "9-RD", "9-LH", "9-LH", "9-RD", "9-RD", "9-RD", "9-RD", "9-RD", "9-RD", "9-RD", "9-LH", "9-LH"), MS_RegistrationDate = c("20191129", "2019-09-26T00:00:00.000", "2019-09-17", "2019-10-08", "30.10.2019", "2019-09-25", "2019-10-17", "2019-11-21", "2019-10-08", "2019-10-17", NA, NA, "2019-10-07", "2019-10-07", "2019-10-04", "2019-10-07", "2019-10-07", "2019-10-07", "2019-10-07", "43766", "43787"), RegDate_Corrected = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Match = c("Match", "Match", "Match", "Match", "Match", "Match", "Match", "Match", "Match", "Match", "OEM_only", "OEM_only", "Match", "Match", "Match", "Match", "Match", "Match", "Match", "Match", "Match"), Baseline = c("No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No"), VocationalVehicle = c("No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No"), ZeroEmissionVehicle = c("No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No", "No"), HybridElectricHDV = c(NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_), DualFuelVehicle = c(NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, NA_character_), ExemptedVehicle = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), CO2v = c(710.88, 629.9, 683.52, 683.52, 841.48, 682.21, 682.21, 778.85, 682.21, 682.21, 844.79, 844.79, 613.28, 613.28, 679.51, 613.28, 613.28, 613.28, 613.28, 831.71, 831.71), Engine_Displacement_ltr = c(9.3, 9.3, 9.3, 9.3, 12.7, 9.3, 9.3, 9.3, 9.3, 9.3, 12.7, 12.7, 9.3, 9.3, 9.3, 9.3, 9.3, 9.3, 9.3, 12.7, 12.7), Engine_FuelType = c("Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "NG PI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI", "Diesel CI"), AirDrag_CdxA_range = c("A11", "A9", "A14", "A14", "A11", "A14", "A14", "A13", "A14", "A14", "A11", "A11", "A9", "A9", "A13", "A9", "A9", "A9", "A9", "A9", "A9"), WHTC_FuelConsumption_gkwh = c(216, 216, 216, 216, 201, 216, 216, 290, 216, 216, 201, 201, 216, 216, 216, 216, 216, 216, 216, 201, 201), Engine_RatedPower_kw = c(206, 206, 235, 235, 331, 206, 206, 251, 206, 206, 331, 331, 235, 235, 235, 235, 235, 235, 235, 368, 368)), row.names = c(NA, -21L), class = c("tbl_df", "tbl", "data.frame"))

解决方案 #

1. 加载工具包 #

使用lubridate处理多格式日期，dplyr做数据操作：

library(lubridate)
library(dplyr)

2. 批量转换逻辑 #

先识别Excel日期序列号，再用parse_date_time自动匹配其他格式，最后统一转为yyyymmdd字符串：

df <- df %>%
  mutate(
    # 处理Excel日期序列号：纯数字字符串转数值后，以1899-12-30为起始日期转换
    temp_date = case_when(
      grepl("^\\d+$", MS_RegistrationDate, na.rm = TRUE) ~ as.Date(as.numeric(MS_RegistrationDate), origin = "1899-12-30"),
      # 自动识别dmy、ymd格式，兼容带时分秒的变种
      TRUE ~ parse_date_time(MS_RegistrationDate, orders = c("dmy", "ymd"), quiet = TRUE)
    ),
    # 格式化为yyyymmdd字符串
    RegDate_Corrected = format(temp_date, "%Y%m%d")
  ) %>%
  select(-temp_date) # 删除临时列

代码说明 #

• grepl("^\\d+$", ...)：判断字符串是否为纯数字，匹配Excel序列号
• as.Date(..., origin = "1899-12-30")：Excel日期以1899-12-30为计数起点，需指定该参数
• parse_date_time：自动识别多种日期格式，无需手动逐个指定
• format(..., "%Y%m%d")：将日期对象转为目标格式字符串

大数据量优化（40万行） #

如果用data.table效率更高，代码如下：

library(data.table)
library(lubridate)

setDT(df)
df[, temp_date := ifelse(grepl("^\\d+$", MS_RegistrationDate, na.rm = TRUE),
                         as.Date(as.numeric(MS_RegistrationDate), origin = "1899-12-30"),
                         parse_date_time(MS_RegistrationDate, orders = c("dmy", "ymd"), quiet = TRUE))]
df[, RegDate_Corrected := format(temp_date, "%Y%m%d")]
df[, temp_date := NULL]

验证结果 #

示例数据转换后，RegDate_Corrected列结果如下：

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