Duckplyr equivalent to dplyr's separate() or separate_wider_delim()?

[adamschwing]

Hello!

I would like to take a comma separated string and put each element in its own row. This is easy to do in dplyr using the separate() or separate_wider_delim() plus pivot_longer() functions. However, my dataset is very large because each string has thousands of elements and the dataset contains thousands of these strings across many columns and rows. So doing this separation is impractical using purely dplyr.

Is there an equivalent function in duckdb-r or duckplyr for this?

[mgacc0]

  library(tidyverse)
  df1 <- tribble(
    ~ id, ~ txt,
    1, "A,B,C",
    2, "X,Y,Z",
    3, "A,A,A"
  )
  df1 |>
    separate_wider_delim(txt, delim = ",", names = c("col1", "col2", "col3"),
                         cols_remove = FALSE)
#> # A tibble: 3 × 5
#>      id col1  col2  col3  txt  
#>   <dbl> <chr> <chr> <chr> <chr>
#> 1     1 A     B     C     A,B,C
#> 2     2 X     Y     Z     X,Y,Z
#> 3     3 A     A     A     A,A,A

 
 
In SQL it should be something like

CREATE TABLE df1 AS (
    SELECT * FROM (
        VALUES
            (1, 'A,B,C'),
            (2, 'X,Y,Z'),
            (3, 'A,A,A')
    ) AS t(id, txt)
);

SELECT 
    id,
    txt,
    list_extract(string_split(txt, ','), 1) AS col1,
    list_extract(string_split(txt, ','), 2) AS col2,
    list_extract(string_split(txt, ','), 3) AS col3
FROM df1;

┌───────┬─────────┬─────────┬─────────┬─────────┐
│  id   │   txt   │  col1   │  col2   │  col3   │
│ int32 │ varchar │ varchar │ varchar │ varchar │
├───────┼─────────┼─────────┼─────────┼─────────┤
│     1 │ A,B,C   │ A       │ B       │ C       │
│     2 │ X,Y,Z   │ X       │ Y       │ Z       │
│     3 │ A,A,A   │ A       │ A       │ A       │
└───────┴─────────┴─────────┴─────────┴─────────┘