Discrepency in dplyr in R : n() and length(variable-name) giving different answers after group by-CodePudding

I think if I have any data frame, when I use group_by and then invoke n() OR if I use group_by and invoke length(any variable_name in the data frame) they should give me the same answer.

However today I noticed that this is not the case.

I am not allowed to post this data, but here is the code.

Can someone try to understand why total count and c2 are not the same?

Please note that in the used data frame, WAVE_NO and REF_PERIOD_WAVE will give rise to the same groups. I just used this for printing nicely. Also DATE_OF_INTERVIEW is all NA in WAVE_NO = 1 to 24.

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
 >

CodePudding user response：

The problem is that while n() translates to COUNT in MySQL, length translates to length which gives the length of a string:

library(dbplyr)
library(dplyr)

md <- lazy_frame(a = gl(5, 3), b = rnorm(15), con = simulate_mysql())

md %>% 
   group_by(a) %>% 
   summarize(n = n(), len = length(b))

# <SQL>
# SELECT `a`, COUNT(*) AS `n`, length(`b`) AS `len`
# FROM `df`
# GROUP BY `a`