Convert columns into multiple rows per entry in R

Question

This question already has an answer here:

• Convert columns to rows keeping the name of the column 2 answers

I have the following data:

word   Jan-2013  Feb-2013  Mar-2013
A      1         2         3 
B      5         2         4

I want to convert the multiple date columns into one, named date and add an additional column for the value.

word date       value
A    Jan-2013   1
A    Feb-2013   2
A    Mar-2013   3
B    Jan-2013   5
B    Feb-2013   2
B    Mar-2013   4

Can anyone assist?

Thanks

Answer 1

Additional R options

In addition to Metrics's answer, here are two additional options for R (assuming your data.frame is called "mydf"):

cbind(mydf[1], stack(mydf[-1]))

library(reshape)
melt(mydf, id.vars="word")

Excel option

I am not an Excel user, but since this question is tagged "Excel" as well, I would suggest the Tableau Reshaper Excel add-on.

For your example, it's pretty straightforward:

1. Go to the "Tableau" menu after installing the add-on and activating it.

2. Select the cells which contain the values you want to unstack. Click on OK.

3. View the result.

   

Answer 2

Using reshape from base R (df1 is your dataframe)

 reshape(df1,times=names(df1)[-1],timevar="date",varying=names(df1)[-1],v.names="value",new.row.names=1:6,ids=NULL,direction="long")
  word     date value
1    A Jan.2013     1
2    B Jan.2013     5
3    A Feb.2013     2
4    B Feb.2013     2
5    A Mar.2013     3
6    B Mar.2013     4