R语言统计每周唯一日期数：n_distinct等方法失效求助

现有名为test的tibble数据集，包含Week和Dates两列，其中Dates列是用逗号分隔的日期字符串。需要统计每周的唯一日期数量（例如第2周应统计出4个唯一日期），但使用n_distinct、nlevels(as.factor())、str_count()等方法均失效——这些方法将每个Dates字符串视为一个整体，即使使用str_split拆分后也无法正确统计唯一日期数。

数据集示例 #

> test
# A tibble: 30 × 2
# Groups:   Week [30]
    Week Dates                                                                                                                 
   <dbl> <chr>                                                                                                                 
 1     2 2023-10-04, 2023-10-05, 2023-10-05, 2023-10-06, 2023-10-06, 2023-10-06, 2023-10-08, 2023-10-08                        
 2     3 2023-10-11, 2023-10-12, 2023-10-12, 2023-10-14, 2023-10-15                                                            
 3     4 2023-10-18, 2023-10-19, 2023-10-20, 2023-10-20, 2023-10-21, 2023-10-21, 2023-10-22, 2023-10-22                        
 4     5 2023-10-25, 2023-10-25, 2023-10-26, 2023-10-27, 2023-10-28, 2023-10-29, 2023-10-29, 2023-10-30                        
 5     6 2023-11-01, 2023-11-01, 2023-11-01, 2023-11-01, 2023-11-02, 2023-11-02, 2023-11-03, 2023-11-04, 2023-11-05, 2023-11-05
 6     7 2023-11-09, 2023-11-10, 2023-11-13                                                                                    
 7     8 2023-11-16, 2023-11-17, 2023-11-18, 2023-11-19, 2023-11-21                                                            
 8     9 2023-11-22, 2023-11-22, 2023-11-23                                                                                    
 9    10 2023-11-29, 2023-11-30, 2023-12-02, 2023-12-03, 2023-12-04                                                            
10    11 2023-12-06, 2023-12-07, 2023-12-08, 2023-12-08, 2023-12-09, 2023-12-10, 2023-12-10                                    
# ℹ 20 more rows

尝试的失效代码及结果 #

> with(test, tapply(Dates, Week, function(x) nlevels(unique(as.factor(x))))) 
 2  3  4  5  6  7  8  9 10 11 12 13 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 
 1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1  1 
> with(test, sapply(Dates, function(x) nlevels(unique(as.factor(x))))) 
                                    2023-10-04, 2023-10-05, 2023-10-05, 2023-10-06, 2023-10-06, 2023-10-06, 2023-10-08, 2023-10-08 
                                                                                                                                 1 
                                                                        2023-10-11, 2023-10-12, 2023-10-12, 2023-10-14, 2023-10-15 
                                                                                                                                 1 
                                    2023-10-18, 2023-10-19, 2023-10-20, 2023-10-20, 2023-10-21, 2023-10-21, 2023-10-22, 2023-10-22 
                                                                                                                                 1 
                                    2023-10-25, 2023-10-25, 2023-10-26, 2023-10-27, 2023-10-28, 2023-10-29, 2023-10-29, 2023-10-30 
                                                                                                                                 1 
            2023-11-01, 2023-11-01, 2023-11-01, 2023-11-01, 2023-11-02, 2023-11-02, 2023-11-03, 2023-11-04, 2023-11-05, 2023-11-05 
                                                                                                                                 1 
                                                                                                2023-11-09, 2023-11-10, 2023-11-13 
                                                                                                                                 1 
> n_distinct(unique(as.factor(test$Dates[1]))) 
[1] 1

> unique(factor(str_split(test$Dates[1], ','))) 
[1] c("2023-10-04", " 2023-10-05", " 2023-10-05", " 2023-10-06", " 2023-10-06", " 2023-10-06", " 2023-10-08", " 2023-10-08") 
Levels: c("2023-10-04", " 2023-10-05", " 2023-10-05", " 2023-10-06", " 2023-10-06", " 2023-10-06", " 2023-10-08", " 2023-10-08") 
> unique(str_split(test$Dates[1], ',')) 
[[1]] 
[1] "2023-10-04"  " 2023-10-05" " 2023-10-05" " 2023-10-06" " 2023-10-06" " 2023-10-06" " 2023-10-08" " 2023-10-08" 

> nlevels(factor(str_split(test$Dates[1], ','))) 
[1] 1

正确解决方案 #

方法1：tidyverse工具链实现

核心逻辑是先将逗号分隔的日期拆分为独立行，清理日期字符串前后的空格，再按周分组统计唯一日期数：

library(tidyverse)

test %>%
  separate_rows(Dates, sep = ",") %>%
  mutate(Dates = str_trim(Dates)) %>%
  group_by(Week) %>%
  summarise(unique_date_count = n_distinct(Dates))

方法2：base R实现

# 按周分组处理日期
result <- tapply(test$Dates, test$Week, function(x) {
  # 合并当前周所有日期字符串并拆分
  all_dates <- strsplit(paste(x, collapse = ","), ",")[[1]]
  # 清理日期前后空格
  all_dates_trimmed <- trimws(all_dates)
  # 统计唯一日期数量
  length(unique(all_dates_trimmed))
})

# 转换为数据框方便查看
as.data.frame(result) %>% rename(unique_date_count = result)

失效原因说明：之前的操作均将整个Dates字符串视为单个元素，即使使用str_split拆分后，得到的列表会被当作整体处理，而非逐个解析拆分后的日期。必须先将拆分后的日期展开为独立元素，清理空格后再进行去重统计。

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