R语言dplyr分组后n()与length()结果不一致问题排查

问题背景 #

原本认为数据框分组后，调用n()和length(任意变量名)的结果应完全一致，但实际测试发现二者差异明显。补充说明：

• 数据框中WAVE_NO和REF_PERIOD_WAVE生成的分组完全相同，同时使用仅为显示更清晰；
• WAVE_NO为1到24时，DATE_OF_INTERVIEW字段全为NA。

代码与输出 #

library(dplyr)
library(RMySQL)

con <- dbConnect(dbDriver("MySQL"), host = Sys.getenv("mydb"), db = "hhd", user = Sys.getenv("MY_USER"), password = Sys.getenv("MY_PASSWORD"))

dbListTables(con)

asp <- tbl(con,"my_table")

asp %>%  group_by(WAVE_NO,REF_PERIOD_WAVE) %>%                                                                                                     
  summarise(total_count = n(), c2 = length(DATE_OF_INTERVIEW)) %>% as.data.frame                                                                 
# 输出：
# `summarise()` has grouped output by 'WAVE_NO'. You can override using the `.groups` argument.
#    WAVE_NO REF_PERIOD_WAVE total_count c2
# 1        1         W1 2014      166744 NA
# 2        2         W2 2014      160705 NA
# 3        3         W3 2014      157442 NA
# 4        4         W1 2015      158443 NA
# 5        5         W2 2015      158666 NA
# 6        6         W3 2015      158624 NA
# 7        7         W1 2016      158624 NA
# 8        8         W2 2016      159778 NA
# 9        9         W3 2016      160511 NA
# 10      10         W1 2017      161167 NA
# 11      11         W2 2017      160847 NA
# 12      12         W3 2017      168165 NA
# 13      13         W1 2018      169215 NA
# 14      14         W2 2018      172365 NA
# 15      15         W3 2018      173181 NA
# 16      16         W1 2019      174405 NA
# 17      17         W2 2019      174405 NA
# 18      18         W3 2019      174405 NA
# 19      19         W1 2020      174405 NA
# 20      20         W2 2020      174405 NA
# 21      21         W3 2020      174405 NA
# 22      22         W1 2021      176661 NA
# 23      23         W2 2021      178677 NA
# 24      24         W3 2021      178677 NA
# 25      25         W1 2022      178677 11
# 26      26         W2 2022      178677 11

核心原因：远程数据源的函数翻译差异 #

你使用的asp是MySQL远程表（tbl_sql），而非本地数据框，dplyr会将R函数翻译成对应SQL语句在数据库端执行，二者逻辑完全不同：

• n()的逻辑：被翻译成SQL的COUNT(*)，统计分组内所有行数（包含字段为NA/NULL的行），因此total_count是分组的真实总行数。
• length(DATE_OF_INTERVIEW)的逻辑：被翻译成SQL的COUNT(DATE_OF_INTERVIEW)，统计的是分组内该字段非NA/非NULL的行数：
  • 当WAVE_NO为1-24时，DATE_OF_INTERVIEW全为NA（对应SQL的NULL），COUNT(DATE_OF_INTERVIEW)返回0，因dplyr与MySQL的交互逻辑，最终在R中显示为NA；
  • 当WAVE_NO为25-26时，DATE_OF_INTERVIEW有11个非NA值，因此COUNT(DATE_OF_INTERVIEW)返回11，与输出结果一致。

解决方法 #

若要让二者结果一致，可选择以下两种方式：

1. 直接使用n()统计总行数，这是针对远程数据源最可靠的方式；
2. 将数据拉取到本地数据框后再操作，此时length()会统计向量总长度，与n()结果一致：

asp %>% 
  collect() %>% # 将远程数据拉取到本地
  group_by(WAVE_NO, REF_PERIOD_WAVE) %>%
  summarise(total_count = n(), c2 = length(DATE_OF_INTERVIEW))

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