dplyr: reshape wide to long [duplicate]

Question

HAVE = data.frame("STUDENT"=c(1, 2, 3),
"CLASS"=c('A', 'B', 'C'),
"SCORE1"=c(50, 79, 61),
"SCORE2"=c(74, 100, 70),
"SCORE3"=c(78, 65, 87),
"TEST1"=c(80, 96, 93),
"TEST2"=c(59, 57, 89),
"TEST3"=c(63, 53, 92))


WANT = data.frame("STUDENT"=c(1, 1, 1, 2, 2, 2, 3, 3, 3),
"CLASS"=c('A','A','A','B','B','B','C','C','C'),
"SEMESTER"=c(1, 2, 3, 1, 2, 3, 1, 2, 3),
"SCORE"=c(50, 74, 78, 79, 100, 65, 61, 70, 87),
"TEST"=c(80, 59, 63, 96, 57, 53, 93, 89, 92))

Trial-

WANT = tidyr::pivot_longer(HAVE, cols = -c("STUDENT", "CLASS"), names_to = c('SEMESTER', '.value'),
names_prefix = c("SCORE", "TEST"))

Answer 1

We need the names_sep or names_pattern to find the delimiter in the column names. Here, the column names should be split between the non-digit (\\D) and a digit (\\d) - we use regex lookaround for that (or use names_pattern = "^(\\D+)(\\d+)$") to capture the characters)

library(tidyr)
pivot_longer(HAVE, cols = -c(STUDENT, CLASS),
    names_to = c(".value", "SEMESTER"), names_sep = "(?<=\\D)(?=\\d)")

-output

# A tibble: 9 × 5
  STUDENT CLASS SEMESTER SCORE  TEST
    <dbl> <chr> <chr>    <dbl> <dbl>
1       1 A     1           50    80
2       1 A     2           74    59
3       1 A     3           78    63
4       2 B     1           79    96
5       2 B     2          100    57
6       2 B     3           65    53
7       3 C     1           61    93
8       3 C     2           70    89
9       3 C     3           87    92