Create new column based on column name and transform order of rows-CodePudding

I have this Dataframe called TV-Channels which looks like this:

Channel      Code    14-D1    14-D2    15-D1    15-D2     16-D1    16-D2
Tv1           1       0,32     0,51    0,65      0,43      0,43     0,23
Tv2           2       0,46     0,24    0,25      0,53      0,53     0,85
Tv3           3       0,58     0,45    0,67      0,78      0,23     0,46
BBC           B       0,53     0,56    0,76      0,32      0,65     0,87
CNN           C       0,24     0,65    0,76      0,34      0,43     0,76
TheGuardian   T       0,11     0,76    0,14      0,32      0,32     0,43
#With 25 more rows

I have a quest where I need to first create two new columns with rows connected to the column name and then make the numbers in falling rows. It's difficult to explain but this is what my final result should look like:

Channel    Code   Number  D-code   Cost
Tv1         1       14      D1     0,32
Tv1         1       14      D2     0,51
Tv1         1       15      D1     0,65
Tv1         1       15      D2     0,43
Tv1         1       16      D1     0,43
Tv1         1       16      D2     0,23
Tv2         2       14      D1     0,46
Tv2         2       14      D2     0,24
Tv2         2       15      D1     0,25
Tv2         2       15      D2     0,53
Tv2         2       16      D1     0,53
Tv2         2       16      D2     0,85
#With 150 more rows

If anyone has any ideas that would be tremendous!

CodePudding user response：

library(tidyverse)

df %>%  
  pivot_longer(-c(Channel, Code), 
               values_to = "Cost") %>% 
  separate(col = name, 
           into = c("Number", "D-code"), 
           sep = "-")

# A tibble: 36 x 5
   Channel Code  Number `D-code` Cost 
   <chr>   <chr> <chr>  <chr>    <chr>
 1 Tv1     1     14     D1       0,32 
 2 Tv1     1     14     D2       0,51 
 3 Tv1     1     15     D1       0,65 
 4 Tv1     1     15     D2       0,43 
 5 Tv1     1     16     D1       0,43 
 6 Tv1     1     16     D2       0,23 
 7 Tv2     2     14     D1       0,46 
 8 Tv2     2     14     D2       0,24 
 9 Tv2     2     15     D1       0,25 
10 Tv2     2     15     D2       0,53 
# ... with 26 more rows
# i Use `print(n = ...)` to see more rows