How to use gather on dependent columns

Question

I am trying to use gather function on the data but it's not giving the required output.

Sample raw data -

id  listen_A  listen_B  speak_A  speak_B  
 1     11        21        41       51
 2     12        22        42       52

Required output -

id  type  listen_value  speak_value
 1    A         11           41
 1    B         21           51
 2    A         12           42
 2    B         22           52

Thanks for the help.

Answer 1

One option would be to gather the data into 'long' format, then separate the 'key' column at the _ into two columns and spread back to 'wide' format by the 'key1' with the 'val' column

library(tidyverse)
df1 %>%
    gather(key, val, -id) %>%
    separate(key, into = c("key1", "type")) %>%
    spread(key1, val)
#  id type listen speak
#1  1    A     11    41
#2  1    B     21    51
#3  2    A     12    42
#4  2    B     22    52

---

Or use melt from data.table, which can take multiple measure patterns

library(data.table)
melt(setDT(df1), measure = patterns("listen", "speak"), 
       value.name = c("listen_value", "speak_value"))

data

df1 <- structure(list(id = 1:2, listen_A = 11:12, listen_B = 21:22, 
speak_A = 41:42, speak_B = 51:52), class = "data.frame", 
 row.names = c(NA, -2L))