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
CodePudding user response：
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