Mutate_at and mutate_all with a database table

tidyverse
, ,
1

mutate_at and mutate_all work with tbl_dbi (database table reference) objects when I pass in a function name but not when I create an anonymous function with either the tilde notation or using function. What am I doing wrong?

suppressPackageStartupMessages(library(dplyr))
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, nycflights13::flights, "flights")

# these work
tbl(con, "flights") %>% 
     mutate_all(as.character) %>% 
     show_query()
#> <SQL>
#> SELECT CAST(`year` AS TEXT) AS `year`, CAST(`month` AS TEXT) AS `month`, CAST(`day` AS TEXT) AS `day`, CAST(`dep_time` AS TEXT) AS `dep_time`, CAST(`sched_dep_time` AS TEXT) AS `sched_dep_time`, CAST(`dep_delay` AS TEXT) AS `dep_delay`, CAST(`arr_time` AS TEXT) AS `arr_time`, CAST(`sched_arr_time` AS TEXT) AS `sched_arr_time`, CAST(`arr_delay` AS TEXT) AS `arr_delay`, CAST(`carrier` AS TEXT) AS `carrier`, CAST(`flight` AS TEXT) AS `flight`, CAST(`tailnum` AS TEXT) AS `tailnum`, CAST(`origin` AS TEXT) AS `origin`, CAST(`dest` AS TEXT) AS `dest`, CAST(`air_time` AS TEXT) AS `air_time`, CAST(`distance` AS TEXT) AS `distance`, CAST(`hour` AS TEXT) AS `hour`, CAST(`minute` AS TEXT) AS `minute`, CAST(`time_hour` AS TEXT) AS `time_hour`
#> FROM `flights`

tbl(con, "flights") %>% 
     mutate_at(vars(year, month, day), as.character) %>% 
     show_query()
#> <SQL>
#> SELECT CAST(`year` AS TEXT) AS `year`, CAST(`month` AS TEXT) AS `month`, CAST(`day` AS TEXT) AS `day`, `dep_time`, `sched_dep_time`, `dep_delay`, `arr_time`, `sched_arr_time`, `arr_delay`, `carrier`, `flight`, `tailnum`, `origin`, `dest`, `air_time`, `distance`, `hour`, `minute`, `time_hour`
#> FROM `flights`

# these do not work

tbl(con, "flights") %>% 
     mutate_all(~as.character(.)) %>% 
     show_query()
#> Error in (function (..., .x = ..1, .y = ..2, . = ..1) : object 'year' not found

tbl(con, "flights") %>% 
     mutate_at(vars(year, month, day), ~as.character(.)) %>% 
     show_query()
#> Error in (function (..., .x = ..1, .y = ..2, . = ..1) : object 'year' not found
Created on 2018-04-06 by the reprex package (v0.2.0).
1 Like
2

FWIW, the old funs notation works, too:

tbl(con, "flights") %>% 
    mutate_all(funs(as.character(.))) %>% 
    show_query()
#> <SQL>
#> SELECT CAST(`year` AS TEXT) AS `year`, CAST(`month` AS TEXT) AS `month`, CAST(`day` AS TEXT) AS `day`, CAST(`dep_time` AS TEXT) AS `dep_time`, CAST(`sched_dep_time` AS TEXT) AS `sched_dep_time`, CAST(`dep_delay` AS TEXT) AS `dep_delay`, CAST(`arr_time` AS TEXT) AS `arr_time`, CAST(`sched_arr_time` AS TEXT) AS `sched_arr_time`, CAST(`arr_delay` AS TEXT) AS `arr_delay`, CAST(`carrier` AS TEXT) AS `carrier`, CAST(`flight` AS TEXT) AS `flight`, CAST(`tailnum` AS TEXT) AS `tailnum`, CAST(`origin` AS TEXT) AS `origin`, CAST(`dest` AS TEXT) AS `dest`, CAST(`air_time` AS TEXT) AS `air_time`, CAST(`distance` AS TEXT) AS `distance`, CAST(`hour` AS TEXT) AS `hour`, CAST(`minute` AS TEXT) AS `minute`, CAST(`time_hour` AS TEXT) AS `time_hour`
#> FROM `flights`

...which should allow you to create arbitrarily complex calls still. As far as I can tell, the problem arises from how RSQLite (or DBI?) interprets functions created by rlang::as_function, in particular how the resulting parameters are named. I think?

1 Like