R tidyverse管道流日期筛选在中小数据集失效问题排查

数据集与代码背景 #

我拥有一个包含700条记录、65列的数据集，其中包含若干日期列。以下是数据集前20条记录及日期转换代码：

library(tidyverse)

# 示例数据集
df <- data.frame(
  IDs = c(600, 688, 592, 589, 685, 653, 460, 547, 520, 744, 457, 666, 502, 636, 655, 719, 565, 734, 539, 593),
  estimated_end_date = c("18 June 2021", "15 March 2022", "07 April 2021", "19 July 2021", "24 February 2022",
                         "04 October 2021", "09 December 2020", "12 April 2021", "NA", "NA",
                         "18 November 2020", "27 October 2021", "09 February 2021", "30 September 2021",
                         "07 October 2021", "26 April 2022", "24 May 2021", "08 September 2022", "11 March 2021",
                         "19 July 2021"),
  finalise_date = c("17 June 2021", "15 March 2022", "15 April 2021", "21 July 2021", "16 February 2022",
                    "04 October 2021", "03 December 2020", "27 April 2021", "03 March 2021", "03 August 2022",
                    "08 October 2020", "22 November 2021", "12 January 2021", "30 September 2021", "07 October 2021",
                    "28 April 2022", "24 May 2021", "20 September 2022", "08 March 2021", "24 July 2021")
)

# 转换为字符格式（模拟数据接收时的格式）
df$IDs <- as.character(df$IDs)
df$estimated_end_date <- as.character(df$estimated_end_date)
df$finalise_date <- as.character(df$finalise_date)

# 转换所有日期列（原始数据集还有其他_date列）
df <- df %>% 
  mutate_at(vars(matches('_date')), funs(as.Date(.,'%d %B %Y')))

筛选逻辑与示例结果 #

尝试筛选estimated_end_date或finalise_date处于指定日期范围内的记录：

start_date <- as.Date('2022-06-01')
end_date <- as.Date('2022-12-31')

checklist <- df %>% 
  filter((estimated_end_date >= start_date & estimated_end_date <= end_date) | 
           (finalise_date >= start_date & finalise_date <= end_date))

示例数据运行结果正常：

IDs estimated_end_date finalise_date
1 744               <NA>    2022-08-03
2 734         2022-09-08    2022-09-20

原始数据集的异常现象 #

在原始数据集full_ds上运行相同代码时，筛选结果完全错误，包含大量不符合条件的记录（如2021年的日期）：

select(head(full_ds %>% filter((estimated_end_date >= start_date & estimated_end_date <= end_date) | 
                               (finalise_date >= start_date & finalise_date <= end_date)), n=20), 
       IDs,estimated_end_date,finalise_date)

输出结果：

# A tibble: 20 x 3
               IDs estimated_end_date finalise_date
             <dbl> <date>             <date>       
 1             600 2021-06-18         2021-06-17   
 2             688 2022-03-15         2022-03-15   
 3             592 2021-04-07         2021-04-15   
 4             589 2021-07-19         2021-07-21   
 5             685 2022-02-24         2022-02-16   
 6             653 2021-10-04         2021-10-04   
 7             460 2020-12-09         2020-12-03   
 8             547 2021-04-12         2021-04-27   
 9             520 NA                 2021-03-03   
10             744 2022-08-03         2022-08-03   
11             457 2020-11-18         2020-10-08   
12             666 2021-10-27         2021-11-22   
13             502 2021-02-09         2021-01-12   
14             636 2021-09-30         2021-09-30   
15             655 2021-10-07         2021-10-07   
16             719 2022-04-26         2022-04-28   
17             565 2021-05-24         2021-05-24   
18             734 2022-09-08         2022-09-20   
19             539 2021-03-11         2021-03-08   
20             593 2021-07-19         2021-07-24 

排查过程 #

进一步排查发现，在管道流中创建标记列时结果完全错误：

check <- full_ds %>%
  mutate(est_start_flag = estimated_end_date >= start_date)

head(check$est_start_flag, n =20)

输出：

[1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE   NA TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE

但直接在控制台运行比较语句时结果正确：

head(full_ds$estimated_end_date >= start_date, n=20)

输出：

[1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE    NA  TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE FALSE FALSE

问题原因 #

原始数据集中存在名为start_date的列，与用于筛选的全局变量start_date产生命名冲突。在tidyverse的管道函数（如filter、mutate）中，会优先引用数据框内的列名，而非全局变量，导致比较逻辑错误。

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