inconsistency between dplyr and dbplyr summarise_at when variable names reused

[tidyverse]

inconsistency between dplyr and dbplyr summarise_at when variable names reused

@romainfrancois commented on Apr 28, 2018, 8:14 AM UTC:

🤔 I can see how this is confusing, but this is by design, i.e. the summarise_at call essentially generate that:

library(dplyr)
mtcars %>% 
  summarise( 
    cyl   = sum(cyl *  cyl * mpg), 
    disp  = sum(disp *  cyl * mpg), 
    hp    = sum(hp *  cyl * mpg), 
    drat  = sum(drat *  cyl * mpg)
  )
#>       cyl       disp         hp     drat
#> 1 23197.6 2985648964 1957012170 55216714

So the first expression overwrites cyl and this is what subsequent expressions use. One way around it would be to move it to last:

library(dplyr)
mtcars %>% 
  summarise_at(vars(disp,hp,drat,cyl), funs(sum(. * cyl * mpg))) 
#>       disp       hp     drat     cyl
#> 1 859455.2 546183.6 13246.21 23197.6

or first create the cyl*mpg as a separate variable in a mutate.

library(dplyr)

mtcars %>% 
  mutate( ..cylmpg.. = cyl * mpg) %>% 
  summarise_at(vars(cyl,disp,hp,drat), funs(sum(. * ..cylmpg..))) 
#>       cyl     disp       hp     drat
#> 1 23197.6 859455.2 546183.6 13246.21

Another approach could be to give a name to the function:

library(dplyr)
mtcars %>% 
  summarise_at(vars(cyl,disp,hp,drat), funs(f = sum(. * cyl * mpg))) 
#>     cyl_f   disp_f     hp_f   drat_f
#> 1 23197.6 859455.2 546183.6 13246.21

@hadley any thoughts on the issue ?

@romainfrancois commented on Apr 28, 2018, 8:24 AM UTC:

Related to this test case #138

test_that("summarise can refer to variables that were just created (#138)", {
  res <- summarise(tbl_df(mtcars), cyl1 = mean(cyl), cyl2 = cyl1 + 1)
  expect_equal(res$cyl2, mean(mtcars$cyl) + 1)

  gmtcars <- group_by(tbl_df(mtcars), am)
  res <- summarise(gmtcars, cyl1 = mean(cyl), cyl2 = cyl1 + 1)
  res_direct <- summarise(gmtcars, cyl2 = mean(cyl) + 1)
  expect_equal(res$cyl2, res_direct$cyl2)
})

@hadley commented on Apr 28, 2018, 1:38 PM UTC:

It’s definitely intended behaviour - this matches how summarise and tibble work (which you might not like, but we’re definitely locked into now).

It is supposed to behave the same way in dbplyr too - I’ll have to look into what’s going wrong.

@tslumley commented on Apr 28, 2018, 11:54 PM UTC:

I can cope with it either way, but having it different in dplyr and dbplyr is more of a problem.

[hadley]

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

mtcars1 <- as_tibble(mtcars)
mtcars2 <- tbl_memdb(mtcars)

# Same
mtcars1 %>% summarise_at(vars(cyl, disp), funs(sum(. * cyl * mpg, na.rm = TRUE)))
#> # A tibble: 1 x 2
#>      cyl        disp
#>    <dbl>       <dbl>
#> 1 23198. 2985648964.
mtcars2 %>% summarise_at(vars(cyl, disp), funs(sum(. * cyl * mpg, na.rm = TRUE)))
#> # Source:   lazy query [?? x 2]
#> # Database: sqlite 3.25.3 [:memory:]
#>      cyl    disp
#>    <dbl>   <dbl>
#> 1 23198. 859455.

# Different
mtcars1 %>% head() %>% mutate_at(vars(cyl, disp, hp), funs(. * cyl * mpg))
#> # A tibble: 6 x 11
#>     mpg   cyl     disp       hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl>    <dbl>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21    756  2540160  1746360   3.9   2.62  16.5     0     1     4     4
#> 2  21    756  2540160  1746360   3.9   2.88  17.0     0     1     4     4
#> 3  22.8  365.  898284.  773522.  3.85  2.32  18.6     1     1     4     1
#> 4  21.4  770. 4253532. 1813522.  3.08  3.22  19.4     1     0     3     1
#> 5  18.7 1197. 8056858. 3916528   3.15  3.44  17.0     0     0     3     2
#> 6  18.1  652. 2653641  1238366.  2.76  3.46  20.2     1     0     3     1
mtcars2 %>% head() %>% mutate_at(vars(cyl, disp, hp), funs(. * cyl * mpg))
#> # Source:   lazy query [?? x 11]
#> # Database: sqlite 3.25.3 [:memory:]
#>     mpg   cyl     disp       hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl>    <dbl>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21    756  2540160  1746360   3.9   2.62  16.5     0     1     4     4
#> 2  21    756  2540160  1746360   3.9   2.88  17.0     0     1     4     4
#> 3  22.8  365.  898284.  773522.  3.85  2.32  18.6     1     1     4     1
#> 4  21.4  770. 4253532. 1813522.  3.08  3.22  19.4     1     0     3     1
#> 5  18.7 1197. 8056858. 3916528   3.15  3.44  17.0     0     0     3     2
#> 6  18.1  652. 2653641  1238366.  2.76  3.46  20.2     1     0     3     1

^{Created on 2019-02-06 by the reprex package (v0.2.1.9000)}

So this is just an example of #114 failing to create subqueries where needed.