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).