How to use pivot_longer on multiple columns without separators?

user_13973103 0
Viewed 35

I have the below example dataset and I'm trying to use pivot_longer() so I have a column for test/retest, a column for color and a column for change/pct_change. I'm unsure how to use names_pattern = given my column headings don't have separators. Any suggestions as to how I can go about this will be appeciated.

subject <- paste("Subject", 1:10)
test <- round(rnorm(10, 42, 2), 1)
retest <- round(test + runif(10, -3, 4), 1)
change <- retest - test
pct_change <- round(((test - retest) / test) * -100, 1)
color <- rep(c("darkgreen", "green", "orange", "red", "darkred"),
                times = 2)


ex_dat <- data.frame(subject, test, retest, change, pct_change, color)

This is what I'd like the result to be.

     subject     color   time       type value
1  Subject 1 darkgreen   test     change value
2  Subject 1 darkgreen retest pct_change value
3  Subject 2     green   test     change value
4  Subject 2     green retest pct_change value
5  Subject 3    orange   test     change value
6  Subject 3    orange retest pct_change value
7  Subject 4       red   test     change value
8  Subject 4       red retest pct_change value
9  Subject 5   darkred   test     change value
10 Subject 5   darkred retest pct_change value
dplyr r
1 Answers

in Base R:

 df <- reshape(ex_dat, list(c('test', 'retest'), c('change', 'pct_change')), 
                       dir='long', idvar = c('subject', 'color'))
 rownames(df) <-NULL
 df
      subject     color time test change
1   Subject 1 darkgreen    1 41.8    0.9
2   Subject 2     green    1 42.5   -1.3
3   Subject 3    orange    1 39.9    0.7
4   Subject 4       red    1 40.8   -2.2
5   Subject 5   darkred    1 42.6    3.9
6   Subject 6 darkgreen    1 44.1    3.3
7   Subject 7     green    1 41.5    2.2
8   Subject 8    orange    1 42.0   -0.4
9   Subject 9       red    1 42.0    1.2
10 Subject 10   darkred    1 44.7    0.6
11  Subject 1 darkgreen    2 42.7    2.2
12  Subject 2     green    2 41.2   -3.1
13  Subject 3    orange    2 40.6    1.8
14  Subject 4       red    2 38.6   -5.4
15  Subject 5   darkred    2 46.5    9.2
16  Subject 6 darkgreen    2 47.4    7.5
17  Subject 7     green    2 43.7    5.3
18  Subject 8    orange    2 41.6   -1.0
19  Subject 9       red    2 43.2    2.9
20 Subject 10   darkred    2 45.3    1.3

Using data.table:

library(data.table)
setDT(ex_dat)
melt(ex_dat, c('subject', 'color'),patterns(test = 'test', change = 'change'))
      subject     color variable test change
 1:  Subject 1 darkgreen        1 41.8    0.9
 2:  Subject 2     green        1 42.5   -1.3
 3:  Subject 3    orange        1 39.9    0.7
 4:  Subject 4       red        1 40.8   -2.2
 5:  Subject 5   darkred        1 42.6    3.9
 6:  Subject 6 darkgreen        1 44.1    3.3
 7:  Subject 7     green        1 41.5    2.2
 8:  Subject 8    orange        1 42.0   -0.4
 9:  Subject 9       red        1 42.0    1.2
10: Subject 10   darkred        1 44.7    0.6
11:  Subject 1 darkgreen        2 42.7    2.2
12:  Subject 2     green        2 41.2   -3.1
13:  Subject 3    orange        2 40.6    1.8
14:  Subject 4       red        2 38.6   -5.4
15:  Subject 5   darkred        2 46.5    9.2
16:  Subject 6 darkgreen        2 47.4    7.5
17:  Subject 7     green        2 43.7    5.3
18:  Subject 8    orange        2 41.6   -1.0
19:  Subject 9       red        2 43.2    2.9
20: Subject 10   darkred        2 45.3    1.3

Edit:

In order to get the required output, we could use:

library(tidyverse)
ex_dat %>%
  select(subject, color) %>%
  left_join( select(ex_dat, c(subject, matches('test'))) %>%
               pivot_longer(-subject, names_to='test', 
                            values_to = 'test_val') %>%
               cbind(select(ex_dat,matches('change'))%>%
              pivot_longer(everything(),names_to = 'change',
                           values_to = 'change_val')))
      subject     color   test test_val     change change_val
 1:  Subject 1 darkgreen   test     41.8     change        0.9
 2:  Subject 1 darkgreen retest     42.7 pct_change        2.2
 3:  Subject 2     green   test     42.5     change       -1.3
 4:  Subject 2     green retest     41.2 pct_change       -3.1
 5:  Subject 3    orange   test     39.9     change        0.7
 6:  Subject 3    orange retest     40.6 pct_change        1.8
 7:  Subject 4       red   test     40.8     change       -2.2
 8:  Subject 4       red retest     38.6 pct_change       -5.4
 9:  Subject 5   darkred   test     42.6     change        3.9
10:  Subject 5   darkred retest     46.5 pct_change        9.2
11:  Subject 6 darkgreen   test     44.1     change        3.3
12:  Subject 6 darkgreen retest     47.4 pct_change        7.5
13:  Subject 7     green   test     41.5     change        2.2
14:  Subject 7     green retest     43.7 pct_change        5.3
15:  Subject 8    orange   test     42.0     change       -0.4
16:  Subject 8    orange retest     41.6 pct_change       -1.0
17:  Subject 9       red   test     42.0     change        1.2
18:  Subject 9       red retest     43.2 pct_change        2.9
19: Subject 10   darkred   test     44.7     change        0.6
20: Subject 10   darkred retest     45.3 pct_change        1.3
user_83802720
Related