Using pivot_longer on two sets of columns

I've managed to achieve what I need, but the code is messy and I'm looking for a cleaner way to do it. I have two sets of columns that need pivoting in parallel.

data <- structure(list(well_short = c("A1", "A2", "A3", "A4", "A5", "A6", 
"A7", "A8", "A9", "A10", "A11", "A12", "B1", "B2", "B3", "B4", 
"B5", "B6", "B7", "B8"), reporter1 = c("FAM", "FAM", "FAM", "FAM", 
"FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM", 
"FAM", "FAM", "FAM", "FAM", "FAM", "FAM", "FAM"), reporter2 = c("VIC", 
"VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", 
"VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", "VIC", 
"VIC"), target1 = c(NA, NA, NA, NA, NA, "EIF4A2", "EIF4A2", "EIF4A2", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "EIF4A2", "EIF4A2", "EIF4A2"
), target2 = c("GAPDH", "GAPDH", "GAPDH", NA, NA, "ATP5B", "ATP5B", 
"ATP5B", NA, NA, NA, NA, "GAPDH", "GAPDH", "GAPDH", NA, NA, "ATP5B", 
"ATP5B", "ATP5B")), row.names = c(NA, -20L), class = "data.frame")

Above is a simplified example that looks like this:

> head(data)
  well_short reporter1 reporter2 target1 target2
1         A1       FAM       VIC    <NA>   GAPDH
2         A2       FAM       VIC    <NA>   GAPDH
3         A3       FAM       VIC    <NA>   GAPDH
4         A4       FAM       VIC    <NA>    <NA>
5         A5       FAM       VIC    <NA>    <NA>
6         A6       FAM       VIC  EIF4A2   ATP5B

I'd like to pivot_longer the two reporter columns together and the two target columns together. I can achieve this with a two step pivot_longer, and then cleaning up the resulting data frame like this:

data_long <- data %>%
  pivot_longer(cols = starts_with("reporter"),
               names_to = "reporter_n",
               names_prefix = "reporter",
               values_to = "reporter") %>%
  pivot_longer(cols = starts_with("target"),
               names_to = "target_n",
               names_prefix = "target",
               values_to = "target") %>%
  filter(reporter_n == target_n,
         !is.na(target)) %>%
  select(-c(reporter_n, target_n))

Which produces this:

> head(data_long)
# A tibble: 6 × 3
  well_short reporter target
  <chr>      <chr>    <chr> 
1 A1         VIC      GAPDH 
2 A2         VIC      GAPDH 
3 A3         VIC      GAPDH 
4 A6         FAM      EIF4A2
5 A6         VIC      ATP5B 
6 A7         FAM      EIF4A2

However, I feel there must be a cleaner and tidier way to achieve this?

You can use name_pattern in pivot_longer to extract the "reporter" and "target" labels, and assign them to columns using the special keyword ".value" passed as an argument to names_to. Then just remove the NA values by filtering complete.cases

library(tidyverse)

pivot_longer(data, -1, names_pattern = "(.*)\\d$", names_to = ".value") %>%
  filter(complete.cases(.))
#> # A tibble: 18 x 3
#>    well_short reporter target
#>    <chr>      <chr>    <chr> 
#>  1 A1         VIC      GAPDH 
#>  2 A2         VIC      GAPDH 
#>  3 A3         VIC      GAPDH 
#>  4 A6         FAM      EIF4A2
#>  5 A6         VIC      ATP5B 
#>  6 A7         FAM      EIF4A2
#>  7 A7         VIC      ATP5B 
#>  8 A8         FAM      EIF4A2
#>  9 A8         VIC      ATP5B 
#> 10 B1         VIC      GAPDH 
#> 11 B2         VIC      GAPDH 
#> 12 B3         VIC      GAPDH 
#> 13 B6         FAM      EIF4A2
#> 14 B6         VIC      ATP5B 
#> 15 B7         FAM      EIF4A2
#> 16 B7         VIC      ATP5B 
#> 17 B8         FAM      EIF4A2
#> 18 B8         VIC      ATP5B

^{Created on 2023-02-15 with reprex v2.0.2}