How to combine tidyr::pivot_longer with stringr::separate_rows in one shot

Perhaps there is a shorter and more elegant solution but in the meanwhile you can try this. Basic idea is to

1. Add a batch identifier (batch) (by id) before calling tidyr::separate_rows
2. Filter after tidyr::separate_rows for the obs where the batch_number and the batch identifier are equal. For this last step I first convert the batch_number to a factor using forcats::fct_inorder and then to a numeric which gives me the position of batch_number which can then be matched with batch identifier

set.seed(42)

df <- data.frame(
  id = 1:3,
  blue_type1 = 110:112,
  purple_type5 = 5:7,
  black_type1 = 28:30,
  batch_number = c("bgd | ddg | qwe",
                   "afp | qqw | edt",
                   "pqr | khp | rty")
)
library(dplyr)
library(tidyr)
library(forcats)

df %>% 
  pivot_longer(-c(id, batch_number)) %>% 
  group_by(id) %>% 
  mutate(batch = row_number()) %>% 
  separate_rows(batch_number) %>% 
  filter(batch == as.numeric(forcats::fct_inorder(batch_number)))
#> # A tibble: 9 x 5
#> # Groups:   id [3]
#>      id batch_number name         value batch
#>   <int> <chr>        <chr>        <int> <int>
#> 1     1 bgd          blue_type1     110     1
#> 2     1 ddg          purple_type5     5     2
#> 3     1 qwe          black_type1     28     3
#> 4     2 afp          blue_type1     111     1
#> 5     2 qqw          purple_type5     6     2
#> 6     2 edt          black_type1     29     3
#> 7     3 pqr          blue_type1     112     1
#> 8     3 khp          purple_type5     7     2
#> 9     3 rty          black_type1     30     3

You can subset the strings in batch_number after you pivot.

library(dplyr)
library(tidyr)
library(stringr)

df %>%
  group_by(id) %>%
  mutate(batch_index = seq.int(1, n()*6, 6)) %>%
  ungroup() %>%
  mutate(batch_number = str_sub(batch_number, batch_index, batch_index+2)) %>%
  select(-batch_index)

#      id batch_number color  type   vals
#   <int> <chr>        <chr>  <chr> <int>
# 1     1 bgd          blue   1       110
# 2     1 ddg          purple 5         5
# 3     1 qwe          black  1        28
# 4     2 afp          blue   1       111
# 5     2 qqw          purple 5         6
# 6     2 edt          black  1        29
# 7     3 pqr          blue   1       112
# 8     3 khp          purple 5         7
# 9     3 rty          black  1        30

Please note that mutate(batch_index = seq.int(1, n()*6, 6)) adds a column named batch_index to the data frame (grouped by id). Within each row, batch_index is used to subset the string in batch_number. batch_index is produced by seq.int(1, n()*6, 6) and consists of integers from 1 to n()*6 (i.e. the number of rows in the current group multiplied by six - note that the end value does not have to be this high). The numbers in the sequence are six apart: 1 , 7, 13 ...

mutate(batch_number = str_sub(batch_number, batch_index, batch_index+2) uses batch_index to subset the string in batch_number in each row. The first row in a group requires the first substring from batch_number - the function thus subsets batch_number from batch_index (i.e. 1) to batch_index+2 (i.e. 3). The second row in a group needs the second substring from batch_number - the function thus subsets batch_number from batch_index (i.e. 7) to batch_index+2 (i.e. 9) etc.

This works provided that all the substrings in batch_number consist of three letters.

You can use separate_rows and pull inside a mutate, after pivot_longer:

df %>% 
  pivot_longer(-c(id, batch_number), 
               names_to = c("color", "type"), 
               names_pattern = "(.*)_type(.)", 
               values_to = "vals") %>%
  mutate(batch_number = 
           df %>% 
           separate_rows(batch_number, sep = " \\| ") %>% 
           pull(batch_number)
         ) 

# A tibble: 9 x 5
     id batch_number color  type   vals
  <int> <chr>        <chr>  <chr> <int>
1     1 bgd          blue   1       110
2     1 ddg          purple 5         5
3     1 qwe          black  1        28
4     2 afp          blue   1       111
5     2 qqw          purple 5         6
6     2 edt          black  1        29
7     3 pqr          blue   1       112
8     3 khp          purple 5         7
9     3 rty          black  1        30

Try this option using separate_rows() from tidyr and the use of a sequential id:

library(tidyverse)
#Code
df <- df %>% separate_rows(batch_number,sep='\\|') %>%
  mutate(batch_number=trimws(batch_number)) %>%
  group_by(id) %>% mutate(Val=1:n()) %>%
  pivot_longer(-c(id,batch_number,Val)) %>%
  separate(name,c('color','type'),sep='_') %>%
  mutate(type=gsub('type','',type),Flag=ifelse(id==Val,1,0)) %>%
  filter(Flag==1) %>% select(-c(Flag,Val))

Output:

# A tibble: 9 x 5
# Groups:   id [3]
     id batch_number color  type  value
  <int> <chr>        <chr>  <chr> <int>
1     1 bgd          blue   1       110
2     1 bgd          purple 5         5
3     1 bgd          black  1        28
4     2 qqw          blue   1       111
5     2 qqw          purple 5         6
6     2 qqw          black  1        29
7     3 rty          blue   1       112
8     3 rty          purple 5         7
9     3 rty          black  1        30