how to transform this dataframe to long format

Question

I have a dataframe looking like this:

a <- c("Lilo","Chops","Henmans")
a <- cbind(a,c(0.1,0.5,0.25),c(0.2,0.3,0.65),c(0.7,0.2,0.1))
colnames(a) <- c("market","Product A","Product B","Product C")

and would like to melt it:

b <- melt(a, varnames = c("market"))

this gives the following:

> b
   market        NA   value
1       1    market    Lilo
2       2    market   Chops
3       3    market Henmans
4       1 Product A     0.1
5       2 Product A     0.5
6       3 Product A    0.25
7       1 Product B     0.2
8       2 Product B     0.3
9       3 Product B    0.65
10      1 Product C     0.7
11      2 Product C     0.2
12      3 Product C     0.1
> 

However, want I'm looking for is

> b
    market        NA value
4     Lilo Product A   0.1
5    Chops Product A   0.5
6  Henmans Product A  0.25
7     Lilo Product B   0.2
8    Chops Product B   0.3
9  Henmans Product B  0.65
10    Lilo Product C   0.7
11   Chops Product C   0.2
12 Henmans Product C   0.1

How do I achieve this using melt?

Answer 1

Try using rownames instead of a seperate column market. This way you get a numeric matrix and you can use melt very simple as follows:

a <- cbind(c(0.1,0.5,0.25),c(0.2,0.3,0.65),c(0.7,0.2,0.1))
rownames(a) <- c("Lilo","Chops","Henmans")
colnames(a) <- c("Product A","Product B","Product C")

a now looks like this:

        Product A Product B Product C
Lilo         0.10      0.20       0.7
Chops        0.50      0.30       0.2
Henmans      0.25      0.65       0.1

You can access the "markets" by using rownames(a).

Melt now works as follows (which uses melt.array to perform the reshape):

melt(a)
     Var1      Var2 value
1    Lilo Product A  0.10
2   Chops Product A  0.50
3 Henmans Product A  0.25
4    Lilo Product B  0.20
5   Chops Product B  0.30
6 Henmans Product B  0.65
7    Lilo Product C  0.70
8   Chops Product C  0.20
9 Henmans Product C  0.10