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?

The values in a are not numeric. The reason is that in R a matrix can only save values of one type: So in your case mixing numeric and character (column market) makes the matrix numeric. Maybe you want to use `data.table`'s instead. Read in your data as follows: `DF <- data.frame(c("Lilo","Chops","Henmans"), c(0.1,0.5,0.25),c(0.2,0.3,0.65),c(0.7,0.2,0.1)); colnames(DF) <- c("market","Product A","Product B","Product C")` Then use `melt(DF)` — Rentrop, Nov 29 '15 at 12:49
This is related http://stackoverflow.com/questions/22264737/using-melt-with-matrix-or-data-frame-gives-different-output — David Arenburg, Nov 29 '15 at 12:53

score 1 · Accepted Answer · edited Nov 29 '15 at 14:19

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

how to transform this dataframe to long format

1 Answers1

Linked