melt multiple groups of measure.vars
10,375
Solution 1
You can do this easily with reshape
from base R
reshape(dt, varying = 2:7, sep = "_", direction = 'long')
This will give you the following output
id time A B C
1.2011 1 2011 -0.1602428 0.428154271 0.384892382
2.2011 2 2011 1.4493949 0.178833067 2.404267878
3.2011 3 2011 -0.1952697 1.072979813 -0.653812311
1.2012 1 2012 1.7151334 0.007261567 1.521799983
2.2012 2 2012 1.0866426 0.060728118 -1.158503305
3.2012 3 2012 1.0584738 -0.508854175 -0.008505982
Solution 2
From ?melt samples:
melt(DT, id=1:2, measure=patterns("^f_", "^d_"), value.factor=TRUE)
Author by
Scott Ritchie
Updated on June 07, 2022Comments
-
Scott Ritchie almost 2 years
I have a
data.table
containing a number of variables across multiple years, i.e:> dt <- data.table(id=1:3, A_2011=rnorm(3), A_2012=rnorm(3), B_2011=rnorm(3), B_2012=rnorm(3), C_2011=rnorm(3), C_2012=rnorm(3)) > dt id A_2011 A_2012 B_2011 B_2012 C_2011 C_2012 1: 1 -0.8262134 0.832013744 -2.320136 0.1275409 -0.1344309 0.7360329 2: 2 0.9350433 0.279966534 -0.725613 0.2514631 1.0246772 -0.2009985 3: 3 1.1520396 -0.005775964 1.376447 -1.2826486 -0.8941282 0.7513872
I would like to melt this table into variable groups by year, i.e:
> dtLong <- data.table(id=rep(dt[,id], 2), year=c(rep(2011, 3), rep(2012, 3)), A=c(dt[,A_2011], dt[,A_2012]), B=c(dt[,B_2011], dt[,B_2012]), C=c(dt[,C_2011], dt[,C_2012])) > dtLong id year A B C 1: 1 2011 -0.826213405 -2.3201355 -0.1344309 2: 2 2011 0.935043336 -0.7256130 1.0246772 3: 3 2011 1.152039595 1.3764468 -0.8941282 4: 1 2012 0.832013744 0.1275409 0.7360329 5: 2 2012 0.279966534 0.2514631 -0.2009985 6: 3 2012 -0.005775964 -1.2826486 0.7513872
I can easily do this for one set of variables easily using
melt.data.frame
from thereshape2
package:> melt(dt[,list(id, A_2011, A_2012)], measure.vars=c("A_2011", "A_2012"))
But haven't been able to achieve this for multiple
measure.vars
with a common "factor". -
David Arenburg over 8 yearsMore like
melt(dt, measure = patterns("^A_", "^B_", "^C"))
probably, it still doesn't return the exact output though. -
nesvarbu over 8 yearsYeah I just threw that in if someone finds this question and maybe this option might be sufficient for them. Didn't try with OP dataset sorry...
-
Eduardo Wada almost 8 yearsthis gives me an error "Error in melt_check(data, id.vars, measure.vars) : could not find function "patterns""
-
Etienne Racine almost 8 yearsIt refers to
data.table::melt
(anddata.table::patterns
)