如何筛选数据框中Request Date列的错误日期与NA值行？

给定数据集的结构如下：

structure(list(Year = c("2022", "2022", "2022", "2022", "2022", 
"2022", "2022", "2022", "2022", "2022"), `Reference Number` = c("14784", 
"14784", "14785", "14785", "14786", "14786", "14787", "14787", 
"14788", "14788"), `Request Date` = c("1/6/2022", "1/6/2022", 
"11/19/2022", "Happy New Year", "1899-12-31 02:40:00", "Ongoing", "1//12/05", 
"01/14/205", "1/25/20`22", NA)), row.names = c(NA, -10L
), class = c("tbl_df", "tbl", "data.frame"))

我们需要筛选出Request Date列中不符合M/D/YYYY或MM/DD/YYYY格式的行，同时包含缺失值（NA）。可以通过正则表达式匹配合法日期格式，再反向筛选，结合tidyverse工具实现：

library(tidyverse)

# 加载数据集
df <- structure(list(Year = c("2022", "2022", "2022", "2022", "2022", 
"2022", "2022", "2022", "2022", "2022"), `Reference Number` = c("14784", 
"14784", "14785", "14785", "14786", "14786", "14787", "14787", 
"14788", "14788"), `Request Date` = c("1/6/2022", "1/6/2022", 
"11/19/2022", "Happy New Year", "1899-12-31 02:40:00", "Ongoing", "1//12/05", 
"01/14/205", "1/25/20`22", NA)), row.names = c(NA, -10L
), class = c("tbl_df", "tbl", "data.frame"))

# 筛选错误日期和NA行
invalid_dates <- df %>%
  filter(is.na(`Request Date`) | !str_detect(`Request Date`, "^\\d{1,2}/\\d{1,2}/\\d{4}$"))

print(invalid_dates)

代码解释 #

• is.na(Request Date)：直接筛选出缺失日期的行
• !str_detect(..., regex)：反向匹配合法日期格式的行，保留不匹配的错误格式行
• 正则表达式^\\d{1,2}/\\d{1,2}/\\d{4}$的含义：
  • ^ 和 $：限定匹配整个字符串，避免部分匹配
  • \\d{1,2}：匹配1-2位数字（对应月份或日期）
  • /：匹配日期中的斜杠分隔符
  • \\d{4}：匹配4位数字（对应年份）

运行结果 #

执行后会得到所有包含错误日期（如文本、格式错误、年份位数不对等）和NA的行：

# A tibble: 7 × 3
  Year  `Reference Number` `Request Date`       
  <chr> <chr>              <chr>                
1 2022  14785              Happy New Year       
2 2022  14786              1899-12-31 02:40:00  
3 2022  14786              Ongoing              
4 2022  14787              1//12/05             
5 2022  14787              01/14/205            
6 2022  14788              1/25/20`22           
7 2022  14788              NA                   

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