Recode sqlite column values using dplyr

Question

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!

hello_friend · Accepted Answer · 2021-10-15 02:15:05Z

3

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

answered Oct 15, 2021 at 2:15

hello_friend

5,8381 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DreamNet Over a year ago

Thanks for the help!

Collectives™ on Stack Overflow

Recode sqlite column values using dplyr

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related