My example dataset is wide and contains these values:
olddata_wide <- read.table(header=TRUE, text='
subject sex a b c a1 b1 c1 a2 b2 c2
1 M 7.9 12.3 10.7 7.5 12.1 10.3 8.1 12.5 10.9
2 F 6.3 10.6 11.1 6.0 10.4 11.0 6.5 10.9 11.4
3 F 9.5 13.1 13.8 9.3 13.0 13.5 9.8 13.5 13.9
4 M 11.5 13.4 12.9 11.2 13.5 12.7 11.7 13.6 13.9
')
I would like to convert it to a long dataset. The problem is that I want to use multiple keycols at the same time - I want the columns a & b & c to become one long column called value, as well as columns a1 & b1 & c1 to value1 and a2 & b2 & c2 to value3. So the desired outcome is:
subject sex value valueType value1 valueType1 value2 valueType2
1: 1 M 7.9 a 7.5 a1 8.1 a2
2: 2 F 6.3 a 6.0 a1 6.5 a2
3: 3 F 9.5 a 9.3 a1 9.8 a2
4: 4 M 11.5 a 11.2 a1 11.7 a2
5: 1 M 12.3 b 12.1 b1 12.5 b2
6: 2 F 10.6 b 10.4 b1 10.9 b2
7: 3 F 13.1 b 13.0 b1 13.5 b2
8: 4 M 13.4 b 13.5 b1 13.6 b2
9: 1 M 10.7 c 10.3 c1 10.9 c2
10: 2 F 11.1 c 11.0 c1 11.4 c2
11: 3 F 13.8 c 13.5 c1 13.9 c2
12: 4 M 12.9 c 12.7 c1 13.9 c2
I know how to get the desired outcome programmatically for one key column:
keycol <- "valueType"
valuecol <- "value"
gathercols <- c("a", "b", "c")
gather_(olddata_wide, keycol, valuecol, gathercols)
But how can I do this for multiple keycols at the same time?
A data.table idea:
olddata_wide[, melt(
.SD,
id.vars = c("subject", "sex"),
measure.vars = patterns(valueType = "[a-c]$", valueType1 = '1$', valueType2 = '2$'))
][, variable := letters[variable]][]
# subject sex variable valueType valueType1 valueType2
# <int> <char> <char> <num> <num> <num>
# 1: 1 M a 7.9 7.5 8.1
# 2: 2 F a 6.3 6.0 6.5
# 3: 3 F a 9.5 9.3 9.8
# 4: 4 M a 11.5 11.2 11.7
# 5: 1 M b 12.3 12.1 12.5
# 6: 2 F b 10.6 10.4 10.9
# 7: 3 F b 13.1 13.0 13.5
# 8: 4 M b 13.4 13.5 13.6
# 9: 1 M c 10.7 10.3 10.9
# 10: 2 F c 11.1 11.0 11.4
# 11: 3 F c 13.8 13.5 13.9
# 12: 4 M c 12.9 12.7 13.9