1

I have the following data frame:

df <- structure(list(Setno = structure(c(1L, 1L, 1L), .Label = "set4", class = "factor"),
    Organ = structure(c(1L, 1L, 1L), .Label = "LN", class = "factor"),
    Adjn = structure(c(1L, 1L, 1L), .Label = "FOO", class = "factor"),
    Module_number = 1:3, Up_Count = c(2L, 0L, 0L), Down_count = c(1L,
    8L, 6L)), .Names = c("Setno", "Organ", "Adjn", "Module_number",
"Up_Count", "Down_count"), row.names = c(NA, 3L), class = "data.frame")

That looks like this

  Setno Organ Adjn Module_number Up_Count Down_count
1  set4    LN  FOO             1        2          1
2  set4    LN  FOO             2        0          8
3  set4    LN  FOO             3        0          6

What I want do to is to melt or collapse the data based on Up_Count and Down_count yielding this:

 Setno Organ Adjn Category Module_number Count
  set4    LN  FOO     Up_count        1     2   
  set4    LN  FOO     Up_count        2     0 
  set4    LN  FOO     Up_count        3     0
  set4    LN  FOO     Down_count      1     2   
  set4    LN  FOO     Down_count      2     8 
  set4    LN  FOO     Down_count      3     6

I tried this but didn't give what I want:

> library(reshape)
> melt(df)
Using Setno, Organ, Adjn as id variables
  Setno Organ Adjn      variable value
1  set4    LN  FOO Module_number     1
2  set4    LN  FOO Module_number     2
3  set4    LN  FOO Module_number     3
4  set4    LN  FOO      Up_Count     2
5  set4    LN  FOO      Up_Count     0
6  set4    LN  FOO      Up_Count     0
7  set4    LN  FOO    Down_count     1
8  set4    LN  FOO    Down_count     8
9  set4    LN  FOO    Down_count     6

What's the right way to do it?

neversaint
  • 60,904
  • 137
  • 310
  • 477
  • 2
    Use `melt` from package reshape2 and specify the appropriate `id.var`s. – Roland Jun 12 '15 at 11:30
  • 1
    @Roland: can you be specific. I tried this but failed `melt(df, id.vars=c("Up_Count","Down_count"))` – neversaint Jun 12 '15 at 11:32
  • No, those are the `value.vars`. – Roland Jun 12 '15 at 11:33
  • 2
    Try `melt(df, measure.vars=c('Up_Count', 'Down_count'), variable.name='Category', value.name='Count')` – akrun Jun 12 '15 at 11:34
  • 1
    And the wonderful base R `reshape` function: `reshape(df, idvar="Module_number", varying=list(c(5, 6)), times=c("Up_Count", "Down_count") , timevar="Category", v.names="Count", direction="long")` – user20650 Jun 12 '15 at 13:11
  • @akrun: Your method works fine, with small glitches the value and variable name is not changed to "Category" and "Count". Any way we can fix it? – neversaint Jun 14 '15 at 13:23
  • 1
    @neversaint It must have been the `data.table 1.9.5` `melt` as I loaded data.table and reshape2. Try `library(data.table); melt(df, measure.vars=c('Up_Count', 'Down_count'), variable.name='Category', value.name='Count')` – akrun Jun 14 '15 at 13:25

0 Answers0