Reformatting dataframe across multiple columns with pivot

I have a dataframe that looks like below:

current.df <- 
  data.frame(
    Level = "A2",
    Subject = "Psychology",
    C1Num = 60,
    C1Name = "PoE",
    C1Lvl = "A2",
    C2Num = 80,
    C2Name = "Carryover",
    C2Lvl = "AS" )

The real dataframe actually contains more data (C1 to C5).

I am trying to use pivot_longer to put this into a more usable format. Specifically, I want it to look like this one:

desired.df <- 
  data.frame(
    Level = c("A2", "A2"),
    Subject = c("Psychology", "Psychology"),
    Component = c("PoE", "Carryover"),
    ComponentNumber = c(60, 80), 
    ComponentLevel = c("A2", "AS")
  )

I have tried to use pivot_longer to get this desired result, but I can't seem to make it work.

CodePudding user response：

library(tidyverse)
current.df %>%
   pivot_longer(-c(Level, Subject), names_to = c('name', '.value'),
             names_pattern = "(.*\\d)(. )")

# A tibble: 2 x 6
  Level Subject    name    Num Name      Lvl  
  <chr> <chr>      <chr> <dbl> <chr>     <chr>
1 A2    Psychology C1       60 PoE       A2   
2 A2    Psychology C2       80 Carryover AS

CodePudding user response：

library(data.table)
setDT(current.df)

melt(current.df, 1:2, patterns(Component = 'Name', ComponentNumber = 'Num',
               ComponentLevel = 'Lvl'))[, variable:= NULL][]

   Level    Subject Component ComponentNumber ComponentLevel
1:    A2 Psychology       PoE              60             A2
2:    A2 Psychology Carryover              80             AS