Create array based on column values in dbplyr

I would like to able to do something equivalent to this using dbplyr.

library(magrittr)
library(tidyverse)
library(bigrquery)
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

bq_deauth()
bq_auth()

bq_conn = dbConnect(
  bigquery(),
  project = "eacri-genomics"
)


df = tibble(
  chr =   c(1,1,1,2,2,3),
  start = c(0, 10, 12, 0, 5, 1),
  end =   c(2, 11, 15, 1, 8, 3)
)
df
#> # A tibble: 6 x 3
#>     chr start   end
#>   <dbl> <dbl> <dbl>
#> 1     1     0     2
#> 2     1    10    11
#> 3     1    12    15
#> 4     2     0     1
#> 5     2     5     8
#> 6     3     1     3

df %>% 
  rowwise() %>% mutate(range = list(seq(start, end)))
#> # A tibble: 6 x 4
#> # Rowwise: 
#>     chr start   end range    
#>   <dbl> <dbl> <dbl> <list>   
#> 1     1     0     2 <int [3]>
#> 2     1    10    11 <int [2]>
#> 3     1    12    15 <int [4]>
#> 4     2     0     1 <int [2]>
#> 5     2     5     8 <int [4]>
#> 6     3     1     3 <int [3]>

df %>% 
  rowwise() %>% mutate(range = list(seq(start, end))) %>% 
  unnest(range)
#> # A tibble: 18 x 4
#>      chr start   end range
#>    <dbl> <dbl> <dbl> <int>
#>  1     1     0     2     0
#>  2     1     0     2     1
#>  3     1     0     2     2
#>  4     1    10    11    10
#>  5     1    10    11    11
#>  6     1    12    15    12
#>  7     1    12    15    13
#>  8     1    12    15    14
#>  9     1    12    15    15
#> 10     2     0     1     0
#> 11     2     0     1     1
#> 12     2     5     8     5
#> 13     2     5     8     6
#> 14     2     5     8     7
#> 15     2     5     8     8
#> 16     3     1     3     1
#> 17     3     1     3     2
#> 18     3     1     3     3



dbWriteTable(
  bq_conn,
  name = "balter.range_test",
  value = df,
  overwrite = T
)

df_bq = tbl(bq_conn, "balter.range_test")

df_bq %>% 
  rowwise() %>% mutate(range = list(seq(start, end)))
#> Error in UseMethod("rowwise"): no applicable method for 'rowwise' applied to an object of class "c('tbl_BigQueryConnection', 'tbl_dbi', 'tbl_sql', 'tbl_lazy', 'tbl')"

df_bq %>% 
  mutate(range = generate_series(start, end))
#> Error: Job 'eacri-genomics.job_dnmwBmRtY9j0heYY1AtmwEtgblGp.US' failed
#> x Function not found: generate_series at [1:31] [invalidQuery]

Created on 2021-02-18 by the reprex package (v1.0.0)



Read more here: https://stackoverflow.com/questions/66267170/create-array-based-on-column-values-in-dbplyr

Content Attribution

This content was originally published by abalter at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: