Long pivot for multiple variables using Pivot long

Question

I am trying to reshape wide table data to long using Pivot_longer from Tidyr. However, not able to achieve the result - tried searching but could not find the exact scenario.

Example:

    x<- read.table(header=T, text='
   Dt1 V1_cur V2_cur V1_count V2_count  Other_1 
     A   10    6     50     10      Abc
     A   12    5     70     11      Xyz
     B   20    7     20     8       Axy
     B   22    8     22     9       Ax
   ')

# The result which I am trying to get is, to have one Character column with values Category-> values (V1,V2) and two measure columns Cur, Count.

# Dt1 Other_1 Category Cur Count
# A   Abc     V1       10   50
# A   Xyz     V1       12   70
# A   Abc     V2       6    10
# A   Xyz     V2       5    11
# B   Abc     V1       20   20
# B   Xyz     V1       22   22
# B   Abc     V2       7    8
# B   Xyz     V2       8    9

I have tried using Reshape/Gather as well but, it is causing other issues. However, if someone could let me know if it is possible to achieve above result using tidyr Pivot Longer method. Thank you !

Code used:

pivot_longer(x,cols=c("V1_cur","V2_cur","V1_count","V2_count"),names_to=c("Category"),values_to=c("Cur","Count"))

I am not able to understand how to separate them correctly.

Answer 1

It might be easier if you change variables names:

x <- x %>% 
  rename(cur_V1 = V1_cur, 
         cur_V2 = V2_cur, 
         count_V1 = V1_count,
         count_V2 = V2_count)

Then, you could use pivot_longer like this:

x %>% 
  pivot_longer(-c(Dt1, Other_1),
               names_to = c(".value", "Category"), 
               names_sep = "_")

# A tibble: 8 x 5
  Dt1   Other_1 Category   cur count
  <fct> <fct>   <chr>    <int> <int>
1 A     Abc     V1          10    50
2 A     Abc     V2           6    10
3 A     Xyz     V1          12    70
4 A     Xyz     V2           5    11
5 B     Axy     V1          20    20
6 B     Axy     V2           7     8
7 B     Ax      V1          22    22
8 B     Ax      V2           8     9

Answer 2

You need separate steps for this when using tidyr as far as I can see

x %>%
  pivot_longer(c(-Dt1, -Other_1)) %>%
  separate(name, "_", into = c("Category", "measure")) %>%
  spread(measure, value)