`dplyr` way to break variable into multiple columns acording to layout-dictionary (with varname and length)

Question

I am looking for a dplyr way to break variable into multiple columns according to dictionary:

vardic <- data.frame(varname=c('a','b','c','d'),
                     length=c(2,6,3,1) ) %>% 
               mutate(end=cumsum(length),start=end-length+1)

d <- data.frame(orig_string=c('11333333444A',
                              '22444444111C',
                              '55666666000B'))

The desired output is:

d2 <- data.frame(a=c(11,22,55),b=c(333333,444444,666666),c=c(444,111,000),d=c('A','C','B')

This has to be done using only dplyr commands because this will be implemented via arrow on a larger than memory dataset (asked in this other question)

UPDATE (responding to comments): functions outside dplyr could be used, as long as supported by arrow. arrow's list of R/dplyr supported functions describes what has been implemented so far. Hopefully this pseudocode illustrates the pipeline:

library(tidyverse)
library(arrow)
d %>% write_dataset('myfile',format='parquet')
'myfile' %>% open_dataset %>% 
    sequence_of_arrowsupported_commands_to_split_columns

Update2: added cols indicating start and end position in vardic

Update3: made the arrow pipeline, above, more reproducible. then tested @akrun's solution. But separate is not supported by arrow

Answer 1

Not sure, as others, what exactly you mean by 'only' dplyr. If by that you mean only tidyverse, here's a solution that relies on dplyr, tidyr and stringr:

library(dplyr)
library(stringr)
library(tidyr)
d2 <- d %>%
  mutate(orig_string = str_extract_all(orig_string, "(.)\\1+(?!\\1)|[A-Z]$")) %>%
  unnest_wider(orig_string)
names(d2) <- vardic$varname
# A tibble: 3 × 4
  a     b      c     d    
  <chr> <chr>  <chr> <chr>
1 11    333333 444   A    
2 22    444444 111   C    
3 55    666666 000   B  

EDIT:

Here's a fully automated tidyverse solution:

library(tidyr)
d %>%
  separate(orig_string, 
           into = vardic$varname, 
           sep = cumsum(vardic$lenght))
   a      b   c d
1 11 333333 444 A
2 22 444444 111 C
3 55 666666 000 B

Answer 2

OP here. Tks for all the support. All other answers are great and work well with purr + dplyr. However, these loop/map through the variables have not been implemented in arrow yet. One solution, however, is to have the loop outside and the arrow command be repeated for eah variable:

For instance (a hard coded sequence would be):

ds <- 'file' %>% open_dataset 
ds <- ds %>% mutate(a=str_sub(orig_string,1,2))
ds <- ds %>% mutate(b=str_sub(orig_string,3,8))
...
ds %>% collect

Now reimplement this as a function + a loop:

extract_var_arrow <- function(ds,var){
  s <- vardic[varname==var]$start
  e <- vardic[varname==var]$end
  ds %>% mutate("{var}" := str_sub(orig_string,s,e)) %>% return
}
for(v in vardic$varname){
  ds <- ds %>% extract_var_arrow(v)
}

Note that, until it sees a collect statement arrow, is just compiling a query. So the above loop is equivalent to:

   # ds <- ds %>% extract_var_arrow('a') 
   #             %>% extract_var_arrow('b')
   #                %>% extract_var_arrow('c')
   #                   %>% extract_var_arrow('d')

Finally we can collect

ds %>% select(-orig_string) %>% collect

   a      b   c d
1 11 333333 444 A
2 22 444444 111 C
3 55 666666 000 B

Answer 3

Base R solution:

# instantiate d2 with nrow(d) rows and 0 columns
d2 <- d
d2$orig_string <- NULL

for (i in seq(to = nrow(vardic))) {
  d2[[vardic$varname[[i]]]] <- substr(
    d$orig_string, 
    vardic$start[[i]], 
    vardic$end[[i]]
  )
}

d2

   a      b   c d
1 11 333333 444 A
2 22 444444 111 C
3 55 666666 000 B

If you can use other tidyverse packages, here's a solution using purrr:pmap_dfc():

library(dplyr)
library(purrr)
library(stringr)

pmap_dfc(vardic, \(varname, start, end, ...) tibble(
  !!varname := str_sub(d$orig_string, start = start, end = end)
))

# A tibble: 3 × 4
  a     b      c     d    
  <chr> <chr>  <chr> <chr>
1 11    333333 444   A    
2 22    444444 111   C    
3 55    666666 000   B