dplyr::case_when() combined with mutate() does not work when applied to tbl

[tidyverse]

dplyr::case_when() combined with mutate() does not work when applied to tbl

[karldw]

Very unimportant dbplyr feature request: allow dplyr::case_when and dplyr::if_else instead of case_when and if_else?

[krlmlr]

It would be nice if the dplyr:: prefix was supported eventually. We do in dplyr, it just helps reduce surprise.

@krlmlr commented on Aug 2, 2018, 8:33 PM UTC:

Thanks, this seems related to the database backend only.

/move to dbplyr

[cderv]

When working with dplyr and database, I think you need to load dbplyr also.
Can you try by calling first

library(dbplyr)
library(dplyr)

Then your code?

All the "magic" of dplyr with database happens thanks to a clever translation of dplyr verbs to SQL offered by dbplyr. It is why you need to load it. Then, I wonder if dplyr::case_when is not forcing the evaluation of a data.frame case_when preventing the translation.

I let you try. Thanks!

[CarolineBarret]

In my first comment, dbplyr was already loaded - sorry I did not mention that.
But dplyr was not loaded. And indeed I think the issue is related to the use of dplyr::case_when:

# Create the temp folder
dir.create("tmp")
# Copy datasets.sqlite in the temp folder
# It is a sqlite database containing a table called iris with the iris data frame in it
file.copy("data/datasets.sqlite", "tmp")

# Connect to the database
con <- DBI::dbConnect(RSQLite::SQLite(), "tmp/datasets.sqlite")

# Create the table
ir <- dplyr::tbl(con, "iris")

First, I load the dbplyr library.

library(dbplyr)

If I do not call library(dplyr), I get an error related to the use of dplyr::case_when:

  # Apply the case_when() function
  cake <- dplyr::mutate(
    ir,
    type = dplyr::case_when(
      Sepal.Length > 5.8 | Petal.Length > 3.7 ~ "long",
      Sepal.Width > 3  |
        Petal.Width > 1.2 ~ "wide",
      TRUE ~  "other"
    )
  )
  
  # This returns an error: `Error in eval_bare(f[[2]], env) : object 'Sepal.Length' not found`

If I call library(dplyr), the case_when function works fine:

  library(dplyr)
  
  # Apply the case_when() function
  cake <- dplyr::mutate(
    ir,
    type = case_when(
      Sepal.Length > 5.8 | Petal.Length > 3.7 ~ "long",
      Sepal.Width > 3  |
        Petal.Width > 1.2 ~ "wide",
      TRUE ~  "other"
    )
  )

  # This works fine

[cderv]

I think using the :: syntax to call the function forces the evaluation of case_when and is preventing the SQL translation. I think it is by design, because sometime you need to evaluate in R before translating to SQL.

Following the intro vignette, you should work this way with dplyr and database. No need to load dbplyr, only dplyr.

library(dplyr, warn.conflicts = FALSE)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, iris)

iris <- tbl(con, "iris")
iris %>% mutate(
  type = case_when(
    Sepal.Length > 5.8 | Petal.Length > 3.7 ~ "long",
    Sepal.Width > 3  | Petal.Width > 1.2 ~ "wide",
    TRUE ~  "other"
  )
)
#> # Source:   lazy query [?? x 6]
#> # Database: sqlite 3.22.0 []
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width Species type 
#>           <dbl>       <dbl>        <dbl>       <dbl> <chr>   <chr>
#>  1          5.1         3.5          1.4         0.2 setosa  wide 
#>  2          4.9         3            1.4         0.2 setosa  other
#>  3          4.7         3.2          1.3         0.2 setosa  wide 
#>  4          4.6         3.1          1.5         0.2 setosa  wide 
#>  5          5           3.6          1.4         0.2 setosa  wide 
#>  6          5.4         3.9          1.7         0.4 setosa  wide 
#>  7          4.6         3.4          1.4         0.3 setosa  wide 
#>  8          5           3.4          1.5         0.2 setosa  wide 
#>  9          4.4         2.9          1.4         0.2 setosa  other
#> 10          4.9         3.1          1.5         0.1 setosa  wide 
#> # ... with more rows

DBI::dbDisconnect(con)

Created on 2018-08-06 by the reprex package (v0.2.0).

I am not so sure it is an issue anymore. Just a question that may indicate further documentation is needed if it is not clear.
Also, know that https://community.rstudio.com is a great place to ask questions and find help. Sometime, it is efficient to investigate a topic and see if a bug issue has to be opened on github or not.

[karldw]

For what it's worth, you don't even have to load dplyr, you just can't use the dplyr::case_when.
(But this might be a bad idea, since it will make your code fail if you later decide to collect from the database before the mutate.)

Demo:

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
dplyr::copy_to(con, iris)

iris <- dplyr::tbl(con, "iris")
dplyr::mutate(iris,
  type = case_when(  # no `dplyr::` here!
    Sepal.Length > 5.8 | Petal.Length > 3.7 ~ "long",
    Sepal.Width  > 3   | Petal.Width  > 1.2 ~ "wide",
    TRUE ~  "other"
  ))

[edgararuiz-zz]

Hi @CarolineBarret , can we close this issue? It looks like simply removing the dplyr:: fixes the problem you were having.

[hadley]

Duplicate of #197