如何规整混合mm/dd/yy与dd/mm/yy格式的访谈日期数据

给定数据集：

df <- structure(list(dateofinterview = structure(c("5/29/2018", "9/3/2018", 
"6/14/2018", "30/11/2017", "20/04/2018", "1/12/2018", "25/01/2018", 
"16/03/2018", "13/03/2018", "5/17/2018", "17/08/2018", "7/3/2018", 
"29/05/2018", "2/8/2018", "1/11/2018", "8/8/2018", "2/27/2018", 
"22/02/2018", "11/12/2017", "19/07/2018", "14/08/2018", "29/11/2017", 
"29/01/2018", "12/5/2017", "20/08/2018", "29/01/2018", "5/12/2017", 
"8/20/2018", "24-05-2018", "1/11/2018", "24/07/2018", "31/05/2018", 
"7/17/2018", "30/11/2017", "4/12/2017", "24-05-2018", "23-05-2018", 
"25-05-2018", "26/02/2018", "12/5/2017", "16/08/2018", "10/1/2018", 
"10/8/2018", "12/1/2018", "8/20/2018", "5/7/2018", "7/5/2018", 
"16/08/2018", "1/17/2018", "4/18/2018", "3/13/2018", "8/5/2018", 
"19/02/2018", "5/25/2018", "12/1/2018", "31/05/2018", "7/5/2018", 
"16/05/2017", "15/12/2017", "30/11/207", "9/5/2018", "8/20/2018", 
"11/8/2018", "15/12/2017", "6/14/2018", "7/12/2018", "24-05-2018", 
"22/02/2018", "12/5/2017", "5/4/2018", "22/02/2018", "15/02/2018", 
"5/6/2018", "7/13/2018", "5/24/2018", "2/21/2018", "20/08.2018", 
"1/12/2018", "21/02/2018", "12/1/2018", "20-06-2018", "3/26/2018", 
"1/11/2018", "7/17/2018", "7/12/2018", "28/5/2018", "15/01/2018", 
"28/07/2018", "31/01/2018", "2/23/2018", "8/5/2018", "1/11/2018", 
"8/5/2018", "7/5/2018", "24-05-2018", "15/12/2017", "3/8/2018", 
"10/5/2018", "24-05-2018", "15/01/2018"), format.stata = "%10s"), 
    a = c(5, 9, 6, 30, 20, 1, 25, 16, 13, 5, 17, 7, 29, 2, 1, 
    8, 2, 22, 11, 19, 14, 29, 29, 12, 20, 29, 5, 8, 24, 1, 24, 
    31, 7, 30, 4, 24, 23, 25, 26, 12, 16, 10, 10, 12, 8, 5, 7, 
    16, 1, 4, 3, 8, 19, 5, 12, 31, 7, 16, 15, 30, 9, 8, 11, 15, 
    6, 7, 24, 22, 12, 5, 22, 15, 5, 7, 5, 2, 20, 1, 21, 12, 20, 
    3, 1, 7, 7, 28, 15, 28, 31, 2, 8, 1, 8, 7, 24, 15, 3, 10, 
    24, 15), b = c(29, 3, 14, 11, 4, 12, 1, 3, 3, 17, 8, 3, 5, 
    8, 11, 8, 27, 2, 12, 7, 8, 11, 1, 5, 8, 1, 12, 20, 5, 11, 
    7, 5, 17, 11, 12, 5, 5, 5, 2, 5, 8, 1, 8, 1, 20, 7, 5, 8, 
    17, 18, 13, 5, 2, 25, 1, 5, 5, 5, 12, 11, 5, 20, 8, 12, 14, 
    12, 5, 2, 5, 4, 2, 2, 6, 13, 24, 21, 8, 12, 2, 1, 6, 26, 
    11, 17, 12, 5, 1, 7, 1, 23, 5, 11, 5, 5, 5, 12, 8, 5, 5, 
    1), year = c("2018", "2018", "2018", "2017", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2017", "2018", "2018", "2017", 
    "2018", "2017", "2018", "2018", "2017", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2017", "2017", "2018", "2018", "2018", 
    "2018", "2017", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2017", "2017", "2017", "2018", "2018", 
    "2018", "2017", "2018", "2018", "2018", "2018", "2017", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2018", "2018", "2018", "2018", "2018", "2018", "2018", 
    "2018", "2017", "2018", "2018", "2018", "2018")), class = "data.frame", row.names = c(NA, 
-100L))

问题描述 #

dateofinterview列混合了mm/dd/yyyy和dd/mm/yyyy两种格式，同时存在多种分隔符（/、-、.）及错误年份（如207）。需要将可明确判断的日期转换为统一的mm/dd/yyyy格式，对于无法判断的（日期和月份部分均≤12）保留标记提示。

解决方案 #

使用R的dplyr和stringr包处理，步骤如下：

1. 统一日期分隔符，将-和.替换为/
2. 拆分日期为日、月、年三个部分
3. 基于数值大小判断格式，转换可确定的日期
4. 修复错误年份，标记无法判断的记录

library(dplyr)
library(stringr)

df_cleaned <- df %>%
  # 统一所有分隔符为/
  mutate(clean_date = str_replace_all(dateofinterview, "[-.]", "/")) %>%
  # 拆分日期为三个部分
  separate(clean_date, into = c("part1", "part2", "year_raw"), sep = "/", fill = "right") %>%
  # 转换为数值型用于判断，修复错误年份
  mutate(across(c(part1, part2), as.numeric),
         year = case_when(
           nchar(year_raw) == 3 ~ str_c("20", substr(year_raw, 2, 3)),
           TRUE ~ year_raw
         )) %>%
  # 判断格式并转换为mm/dd/yyyy
  mutate(standard_date = case_when(
    # 第一部分>12：确定是dd/mm/yyyy，交换月日位置
    part1 > 12 ~ str_c(part2, "/", part1, "/", year),
    # 第二部分>12：确定是mm/dd/yyyy，直接保留
    part2 > 12 ~ str_c(part1, "/", part2, "/", year),
    # 两部分都≤12：无法判断，标记提示
    TRUE ~ "无法确定"
  )) %>%
  # 保留原日期列和转换结果，其他列后置
  select(dateofinterview, standard_date, everything())

结果说明 #

• 如30/11/2017这类part1>12的记录，会转换为11/30/2017
• 如5/29/2018这类part2>12的记录，直接保留为5/29/2018
• 如7/5/2018这类两部分均≤12的记录，标记为"无法确定"
• 错误年份207会被修复为2017

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