将含5位数字与标准日期的变量转为日期值时遇NA问题

问题背景 #

数据集的Registrationdate字段同时包含YYYY-MM-DD格式的标准日期字符串和5位数字格式的Excel日期字符串，使用openxlsx::convertToDate直接转换时，标准日期全部变为NA，仅数字格式日期转换成功。

原始数据集 #

dt <- data.frame(id=c(1,1,1,1,1,1,2,2,2,2,2),
  Registrationdate=c('2019-01-09','2019-01-09','2019-01-09','2019-01-09','2019-01-09',
'2019-01-09',"44105","44105","44105","44105","44105"))

期望输出 #

id Registrationdate
1   1       2019-01-09
2   1       2019-01-09
3   1       2019-01-09
4   1       2019-01-09
5   1       2019-01-09
6   1       2019-01-09
7   2       2020-10-01
8   2       2020-10-01
9   2       2020-10-01
10  2       2020-10-01
11  2       2020-10-01

失败尝试代码 #

library(openxlsx)
dt$Registrationdate <- convertToDate(dt$Registrationdate, origin = "1900-01-01")

解决方法 #

核心原因 #

convertToDate函数专门用于解析Excel的数字日期（以1900-01-01为原点的天数），无法识别YYYY-MM-DD格式的字符串，因此需要先区分两种格式，分别处理后再合并。

方法1：使用dplyr的case_when（推荐） #

library(dplyr)
library(openxlsx)

dt$Registrationdate <- case_when(
  # 匹配5位数字的日期字符串
  grepl("^\\d{5}$", dt$Registrationdate) ~ convertToDate(as.integer(dt$Registrationdate), origin = "1900-01-01"),
  # 处理标准日期格式
  TRUE ~ as.Date(dt$Registrationdate)
)

方法2：Base R原生实现 #

library(openxlsx)

# 标记哪些是数字格式的日期
is_excel_date <- grepl("^\\d{5}$", dt$Registrationdate)

# 分别转换两种格式
dt$Registrationdate[is_excel_date] <- convertToDate(as.integer(dt$Registrationdate[is_excel_date]), origin = "1900-01-01")
dt$Registrationdate[!is_excel_date] <- as.Date(dt$Registrationdate[!is_excel_date])

# 统一转为Date类型
dt$Registrationdate <- as.Date(dt$Registrationdate)

运行上述代码后，即可得到符合期望的日期格式结果。

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