I have an excessively big data saved as SQLite database. I need to recode the values of some of the columns but unfortunately my code generate error that i really can't debug. Here is the code i'm using.
library(RSQLite)
library(inborutils)
library(dplyr)
library(dbplyr)
db <- dbConnect(SQLite(), dbname = "ukbb.sqlite")
dbListTables(db)
bd <- tbl(db, "Uk_Bb")
bd %>%
mutate(f.19.0.0 = recode(bd, f.19.0.0, '1' = "Direct entry",
'2' = "Manual entry",
'3' = "Not performed",
'6' = "Not performed - equipment failure",
'7' = "Not performed - other reason",
.default = NULL))
f.19.0.0 is a column in the table and has those values: -
bd %>% select(f.19.0.0)
# Source: lazy query [?? x 1]
# Database: sqlite 3.36.0 [C:\Users\*****\Downloads\UkBB\ukbb.sqlite]
f.19.0.0
<dbl>
1 1
2 1
3 1
4 NA
5 1
6 NA
7 NA
8 1
9 1
10 1
# ... with more rows
The error i'm getting is
Error in UseMethod("escape") : no applicable method for 'escape' applied to an object of class "c('tbl_SQLiteConnection', 'tbl_dbi', 'tbl_sql', 'tbl_lazy', 'tbl')" In addition: Warning message: Named arguments ignored for SQL recode
Any help/explanation to solve this problem is much appreciated!
From what I understand about your error, your code is not being translated back into SQL properly by ::dbplyr(), I'm not sure this will work but I have attempted to provide the mutate function a more SQL like alternative to recode(), case_when(). Give this a crack and see how you go:
library(tidyverse)
bd %>%
mutate(
f.19.0.0 =
case_when(
f.19.0.0 == 1 ~ "Direct entry",
f.19.0.0 == 2 ~ "Manual entry",
f.19.0.0 == 3 ~ "Not performed",
f.19.0.0 == 6 ~ "Not performed - equipment failure",
f.19.0.0 == 7 ~ "Not performed - other reason",
TRUE ~ NA_character_
)
)