melt to two variable columns

Question

I have the following variables in a data frame:

[1] "Type"   "I.alt"  "idx06"  "idx07"  "idx08" "farve1" "farve2" 

If I do:

dm <- melt(d, id=c("Type","I.alt")) 

I get these variables:

"Type"     "I.alt"    "variable" "value"    

Where "idx06", "idx07", "idx08", "farve1", "farve2" are represented in "variable".

But what I really want is something like this:

"Type"     "I.alt"    "variable" "value" "variable2" "value2" 

Where "farve1" and "farve2" are represented in variable2 and value2.

The reason I want to do this, is that I'd like something where the line color is green if the value is falling and red if rising. EDIT: Shane has shown how to reshape the data via two melts merged. But my strategy seams to be ill conceived from the beginning - WRONG in one word. See my comment to Shane's solution.

ggplot(dm, aes(x=variable,y=value,group=Type,col=variable2, label=Type,size=I.alt))+ geom_line()+ geom_text(data=subset(dm, variable=="idx08"),hjust=-0.2, size=2.5)+ theme_bw()+ scale_x_discrete(expand=c(0,1))+ opts(legend.position="none") 

I assume I need to cast the molten frame - but I can't figure it out. The data:

d <- structure(list(Type = structure(c(8L, 21L, 23L, 20L, 6L, 14L,  3L, 24L, 2L, 28L, 32L, 22L, 15L, 29L, 1L, 17L, 18L, 33L, 25L,  13L, 30L, 11L, 26L, 9L, 12L, 4L, 5L, 27L, 16L, 19L, 10L, 31L,  7L), .Label = c("Alvorligere vold", "Andre strafferetlige særlove",  "Andre tyverier", "Bedrageri", "Brandstiftelse", "Butikstyverier m.v.",  "Dokumentfalsk", "Færdselslovovertræd. i øvrigt", "Færdselsuheld med spiritus",  "Falsk forklaring i øvrigt", "Forbr. mod off. myndighed m.v.",  "Freds- og ærekrænkelser", "Hæleri", "Hærværk", "Indbrud i bank, forretn. m.v.",  "Indbrud i fritidshuse, garager mv", "Indbrud i villaer, lejligheder mv",  "Love vedr. forsvaret og lign.", "Love vedr. spil, bev., næring",  "Lov om euforiserende stoffer", "Mangler ved køretøj", "Røveri",  "Simpel vold", "Spiritus- og promillekørsel", "Trusler", "Tyv./brugstyv. af andet",  "Tyv./brugstyv. af cykel", "Tyv./brugstyv. af indr. køretøj",  "Tyv/brugstyv. af knallert", "Tyveri fra bil, båd m.v.", "Ulovlig omgang med hittegods",  "Våbenloven", "Vold o.l. mod off. myndighed"), class = "factor"),  I.alt = c(16137L, 9519L, 5930L, 5502L, 4887L, 3582L, 3101L,  1738L, 1660L, 1649L, 1551L, 1412L, 1338L, 1164L, 1154L, 1057L,  931L, 907L, 857L, 724L, 681L, 644L, 641L, 505L, 450L, 419L,  405L, 328L, 324L, 324L, 320L, 281L, 262L), idx06 = c(1, 1,  1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,  1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), idx07 = c(0.972675591417568,  0.766866371450899, 0.931743805516597, 0.813047711781889,  0.88728323699422, 0.96420233463035, 0.855743544078362, 1.03710247349823,  0.751470588235294, 0.90443686006826, 0.808403361344538, 0.902834008097166,  0.718181818181818, 0.904555314533623, 1.02717391304348, 0.68957345971564,  1.10324483775811, 0.93030303030303, 0.805309734513274, 0.843003412969283,  0.74914089347079, 0.824786324786325, 1.04060913705584, 1.09150326797386,  0.977941176470588, 0.892405063291139, 0.966666666666667,  0.828125, 0.696, 0.813559322033898, 0.697841726618705, 0.88235294117647,  0.62280701754386), idx08 = c(0.986612873647533, 0.712685595207085,  0.840579710144927, 0.865628042843233, 0.93757225433526, 0.823346303501945,  0.905609973285841, 1.03356890459364, 0.689705882352941, 0.909556313993174,  0.798319327731092, 0.955465587044534, 0.714545454545455,  0.620390455531453, 1.10869565217391, 0.815165876777251, 0.64306784660767,  0.818181818181818, 0.722713864306785, 0.627986348122867,  0.59106529209622, 0.927350427350427, 1.21319796954315, 1.20915032679739,  1.33088235294118, 0.759493670886076, 1.40833333333333, 0.734375,  0.896, 0.932203389830508, 0.60431654676259, 0.872549019607843,  0.675438596491228), farve1 = c("green", "green", "green",  "green", "green", "green", "green", "red", "green", "green",  "green", "green", "green", "green", "red", "green", "red",  "green", "green", "green", "green", "green", "red", "red",  "green", "green", "green", "green", "green", "green", "green",  "green", "green"), farve2 = c("red", "green", "green", "red",  "red", "green", "red", "green", "green", "red", "green",  "red", "green", "green", "red", "red", "green", "green",  "green", "green", "green", "red", "red", "red", "red", "green",  "red", "green", "red", "red", "green", "green", "red")), .Names = c("Type",  "I.alt", "idx06", "idx07", "idx08", "farve1", "farve2"), class = "data.frame", row.names = c(NA, -33L)) 

Answer 1

This doesn't answer your question about cast, but you could certainly subset and do two melts, followed by a merge:

dm1 <- melt(d[,c("Type","I.alt","idx06","idx07","idx08")], id=c("Type","I.alt")) dm2 <- melt(d[,c("Type","I.alt","farve1","farve2")], id=c("Type","I.alt")) colnames(dm2) <- c("Type", "I.alt", "variable2", "value2") dm <- merge(dm1, dm2) 

Or, equivalently, do one melt (like you're currently doing) then subset the melted dataframe twice (idx <- variable %in% c("idx06","idx07","idx08") as one and !idx as the other) and merge that output.

Either way you get what you want:

> head(dm)               Type I.alt variable    value variable2 value2 1 Alvorligere vold  1154    idx08 1.108696    farve1    red 2 Alvorligere vold  1154    idx08 1.108696    farve2    red 3 Alvorligere vold  1154    idx07 1.027174    farve1    red 4 Alvorligere vold  1154    idx07 1.027174    farve2    red 5 Alvorligere vold  1154    idx06 1.000000    farve1    red 6 Alvorligere vold  1154    idx06 1.000000    farve2    red