Complex reshaping from wide to long in R (pulling multiple things from original variable name)

Perhaps you might try using pivot_longer from tidyr.

You can specify:

• Columns to make longer (could select columns that start with "sn", such as starts_with("sn"), or all columns except for prim_key)
• Names of the new columns generated, which include the initial letter/number combination (e.g., sn016), S, and T
• And a regex pattern to split up into these columns

The code as follows:

library(tidyverse)

df %>%
  pivot_longer(cols = -prim_key, 
               names_to = c(".value", "S", "T"), 
               names_pattern = "(\\w+)_(\\d+)_(\\d+)") 

Output

# A tibble: 10 x 5
   prim_key S     T     sn016 sn017
      <dbl> <chr> <chr> <int> <int>
 1        1 1     2         5    NA
 2        1 1     3         2    NA
 3        1 2     6         5     3
 4        1 2     7         1     2
 5        1 3     5        NA     3
 6        1 1     2         2    NA
 7        1 1     3         3    NA
 8        1 2     6         3     4
 9        1 2     7         2     3
10        1 3     5        NA     5

Data

Example data made up:

df <- structure(list(prim_key = c(1, 1), sn016_1_2 = c(5L, 2L), sn016_1_3 = 2:3, 
    sn016_2_6 = c(5L, 3L), sn016_2_7 = 1:2, sn017_2_6 = 3:4, 
    sn017_2_7 = 2:3, sn017_3_5 = c(3L, 5L)), class = "data.frame", row.names = c(NA, 
-2L))

We could use melt from data.table

library(data.table)
dcast(melt(setDT(df),  id.var = 'prim_key')[, c("nm1", "S", "T") 
   := tstrsplit(variable, '_')], rowid(nm1, S, T) + prim_key + S + T 
         ~ nm1, value.var = 'value')[, nm1 := NULL][]
#     prim_key S T sn016 sn017
# 1:        1 1 2     5    NA
# 2:        1 1 3     2    NA
# 3:        1 2 6     5     3
# 4:        1 2 7     1     2
# 5:        1 3 5    NA     3
# 6:        1 1 2     2    NA
# 7:        1 1 3     3    NA
# 8:        1 2 6     3     4
# 9:        1 2 7     2     3
#10:        1 3 5    NA     5

data

df <- structure(list(prim_key = c(1, 1), sn016_1_2 = c(5L, 2L), sn016_1_3 = 2:3, 
    sn016_2_6 = c(5L, 3L), sn016_2_7 = 1:2, sn017_2_6 = 3:4, 
    sn017_2_7 = 2:3, sn017_3_5 = c(3L, 5L)), class = "data.frame", row.names = c(NA, 
-2L))

The answers using external packages are probably the way to go in terms of parsimony. It's useful, however, to be able to brute force your desired solution using base R sometimes. Below is an example. One benefit of the following is that the call to lapply can be replaced with the parallel version parLapply or mclapply both from the parallel package that ships with R.

#### First make some example data

# The column names you gave
cnames <- c("prim_key", "sn016_1_2", "sn016_1_3", "sn016_1_4", "sn016_1_5",
            "sn016_1_6", "sn016_1_7", "sn016_2_3", "sn016_2_4", "sn016_2_5",
            "sn016_2_6", "sn016_2_7", "sn016_3_4", "sn016_3_5", "sn016_3_6",
            "sn016_3_7", "sn016_4_5", "sn016_4_6", "sn016_4_7", "sn016_5_6",
            "sn016_5_7", "sn016_6_7", "sn017_1_2", "sn017_1_3", "sn017_1_4",
            "sn017_1_5", "sn017_1_6", "sn017_1_7", "sn017_2_3", "sn017_2_4",
            "sn017_2_5", "sn017_2_6", "sn017_2_7", "sn017_3_4", "sn017_3_5",
            "sn017_3_6", "sn017_3_7", "sn017_4_5", "sn017_4_6", "sn017_4_7",
            "sn017_5_6", "sn017_5_7", "sn017_6_7")

# An example matrix with random data
mat <- matrix(runif(length(cnames) * 4), nrow = 4)
# Make the column names corrcet
colnames(mat) <- cnames

### Now pretend we already had the data

# Get the column names of the input matrix
cnames <- colnames(mat)
# The column names that are not your primary key
n_primkey <- cnames[which(cnames != "prim_key")]

# Get the unique set of prefixes for the non-primkey variables
prefix <- strsplit(n_primkey, "_")
prefix <- unique(unlist(lapply(prefix, "[", 1)))

# Go row by row through the original matrix
dat <- lapply(seq_len(nrow(mat)), function(i) {
  # The row we're dealing with now
  row <- mat[i, ]

  # The column names of your output matrix
  dcnames <- c("prim_key", "S", "T", prefix)
  # A pre-allocated data.frame to hold the rehaped data for this row
  dat <- matrix(rep(NA, length(dcnames) * length(n_primkey)), ncol = length(dcnames))
  dat <- as.data.frame(dat)
  colnames(dat) <- dcnames

  # All values for this row have the same prim_key value
  dat$prim_key <- row["prim_key"]

  # Go through each of the non-prim_key variables, split them, and put the
  # values in the correct place
  for (j in seq_len(length(n_primkey))) {

    # k has the non-prim_key name we're dealing with
    k <- n_primkey[j]
    # l splits this name by underscores "_"
    l <- strsplit(k, "_")

    # The first element gives the prefix
    pref  <- l[[1]][1]
    # The second gives the "S" value
    S_val <- l[[1]][2]
    # The third gives the "T" value
    T_val <- l[[1]][3]

    # Allocate these values into the output data.frame we created ealier
    dat[j, "S"]  <- S_val
    dat[j, "T"]  <- T_val
    dat[j, pref] <- row[k]
  }
  # Return the data for row i of the input data
  dat
})
# dat is a list, so combine each element into a single data.frame
dat <- do.call(rbind, dat)

# Check a few
dat[1:2, ]
mat[1, ]