多格式日期标准化为yyyy-mm-dd格式及缺失日期处理问询

可复现数据 #

首先将你的日期数据整理为R字符向量：

dates <- c(
  "October 28, 2021", "April 7, 2014", "November 2009", "January 17, 2018",
  "January 2023", "February 2012", "December 2022", "July 1999",
  "November 2006", "June 2011", "July 2014", "January 2015",
  "July 1, 2020", "October 15, 2018", "September 27, 2019", "February 14, 2022",
  "June 28, 2021", "June 2016", "March 2013", "October 2014",
  "January 2023", "July 6, 2022", "January 2014", "March 22, 2001",
  "October 10, 2019", "May 1, 2008", "December 2008", "November 2023",
  "August 2005", "May 1, 2022", "January 8, 2014", "July 2011",
  "August 15, 2022", "May 2004", "November 2012", "October 1999",
  "March 2010", "May 2014", "October 2006", "March 1, 2017",
  "June 25, 2019", "October 2004", "September 2016", "June 10, 2019",
  "April 4, 2017", "", "August 30, 2018", "July 1, 2017",
  "November 14, 2019", "November 2006", "September 1, 2022", "April 2007",
  "July 12, 2013", "August 14, 2015", "March 2013", "January 2014",
  "March 2013", "June 27, 2019", "April 2008", "July 2007",
  "February 2007", "May 2013", "April 2011", "December 2007",
  "July 2007", "December 2008", "May 5, 2017", "December 2007",
  "February 27, 2007", "February 13, 2018", "", "August 2014",
  "September 9, 2019", "October 2010", "January 30, 2013", "January 2010",
  "September 15, 2015", "March 2006", "April 2016", "March 2014",
  "April 2010", "February 20, 2017", "October 2015", "March 2012",
  "December 2014", "May 4, 2022", "October 27, 2020", "September 22, 2017",
  "November 2009", "July 2003", "August 2006", "March 3, 2017"
)

一、日期标准化为yyyy-mm-dd格式 #

使用lubridate包可以高效识别多种日期格式，并自动补全缺失的日为当月15日：

1. 安装并加载依赖包

install.packages("lubridate")
library(lubridate)

2. 解析并标准化日期

# 自动识别"月-日-年"和"月-年"两种格式
parsed_dates <- parse_date_time(dates, orders = c("mdy", "my"))

# 将仅含年月的日期（默认日为1）替换为当月15日
parsed_dates <- ifelse(day(parsed_dates) == 1 & is.na(dmy(dates)),
                       parsed_dates + days(14),
                       parsed_dates)

# 转换为yyyy-mm-dd格式的字符向量
standardized_dates <- format(as.Date(parsed_dates), "%Y-%m-%d")

验证前6个结果：

head(standardized_dates)
# 输出：
# [1] "2021-10-28" "2014-04-07" "2009-11-15" "2018-01-17" "2023-01-15" "2012-02-15"

二、缺失日期（空字符串）的处理 #

输入中的空字符串解析后会转为NA，可根据业务需求选择以下处理方式：

1. 保留NA #

直接保留NA，后续分析时通过na.rm = TRUE等参数忽略缺失值即可。

2. 线性插值填充 #

利用前后日期的平均值填充缺失值，需依赖zoo包：

install.packages("zoo")
library(zoo)

# 将日期转为数值型（自1970-01-01以来的天数）
date_numeric <- as.numeric(parsed_dates)
# 线性插值填充NA
date_numeric_filled <- na.approx(date_numeric)
# 转回日期格式并标准化
filled_dates <- format(as.Date(date_numeric_filled, origin = "1970-01-01"), "%Y-%m-%d")

3. 固定值填充 #

用指定默认日期填充缺失值：

standardized_dates[is.na(standardized_dates)] <- "1900-01-01"

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