Names in data.table variable column once melted

Hi there,
I am using the data.table package and melting a wide table to long. I am using the patterns() functionality to identify columns to be melted. I want the resulting variable column to return the stub of the column names if possible. For example:

ID   x1.N   x1.mean   x2.N   x2.mean
1     34         1.23         56        4.56
2     45         2.34         67        5.67

I want to melt this table using...
meltedDT <- melt(DT, id.vars=c("ID"), measure.vars=patterns("*.N", "*.mean"), value.name = c("N","mean"))
...to get:

ID   variable  N   mean
1         x1      34   1.23
2         x1      45   2.34
1         x2      56   4.56
2         x2      67   5.67

However, the melt function returns an integer representing the variable number (by pattern) rather than the variable stub. Is there any way to get the stub returned in the variable column of the melted table?

Regards,
Will

I think indices will always be returned when multiple columns are being melted. Thinking about it, naming the values in the variable column according to their original column names would not make sense and the code cannot infer the structure of your column naming.

An alternative would be something like this:

DT2 <- melt(DT, id.vars = "ID", measure.vars = patterns("N|mean"))
DT2[, c("x", "y") := tstrsplit(variable, "\\.", fixed = FALSE)][, variable := NULL]

dcast(DT2, ID + x ~ y)
2 Likes

Great, thanks Martin!

Will