Pivot Longer to Multiple Fields-CodePudding

I need to pivot wide table to a long one and extract two columns for each numeric suffix.

The output needs to look like this. So the Responses and Response Categories need to follow the suffix of those fields.

I will be similar to pivot_longer multiple variables of different kinds but I'm not following how that works.

ID	Response No	Response	Category
1	1	Hot	A
1	2	Very hot	C
2	1	Very hot	A
2	2	Cold	B
3	1	Cold	C
3	2	Luke Warm	B
4	1	Luke Warm	B
4	2	Tepid	A

With the original table like this...

data <- data.frame(id = c(1,2,3,4),
                   response1 = c("Hot", "Very hot", "Cold", "Luke Warm"),
                   response2 = c("Very hot", "Cold", "Luke Warm", "Tepid"),
                   reponseCategory1 = c("A", "A", "C", "B"),
                   reponseCategory2 = c("C", "B", "B", "A")) %>%
  pivot_longer(cols = starts_with('response'),
               ...

CodePudding user response：

Use names_pattern to capture as group - (\\D )- one or more non-digits, (\\d ) one or more digits

library(tidyr)
pivot_longer(data, cols= -id, names_to = c(".value", "ResponseNo"), 
      names_pattern = "(\\D )(\\d )", names_prefix = "reponse")

-output

# A tibble: 8 × 4
     id ResponseNo response  Category
  <dbl> <chr>      <chr>     <chr>   
1     1 1          Hot       A       
2     1 2          Very hot  C       
3     2 1          Very hot  A       
4     2 2          Cold      B       
5     3 1          Cold      C       
6     3 2          Luke Warm B       
7     4 1          Luke Warm B       
8     4 2          Tepid     A

data

data <- data.frame(id = c(1,2,3,4),
                   response1 = c("Hot", "Very hot", "Cold", "Luke Warm"),
                   response2 = c("Very hot", "Cold", "Luke Warm", "Tepid"),
                   reponseCategory1 = c("A", "A", "C", "B")

CodePudding user response：

A possible solution in base R:

reshape(data, dir = "long", varying = list(c(2:3), c(4:5)), 
        v.names = c("response", "Category"), timevar = "ResponseNo")

#>     id ResponseNo  response Category
#> 1.1  1          1       Hot        A
#> 2.1  2          1  Very hot        A
#> 3.1  3          1      Cold        C
#> 4.1  4          1 Luke Warm        B
#> 1.2  1          2  Very hot        C
#> 2.2  2          2      Cold        B
#> 3.2  3          2 Luke Warm        B
#> 4.2  4          2     Tepid        A