2

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

A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
João
  • 121
  • 1
  • 6

2 Answers2

7

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.

    Basic "wide" to "long" reshaping with Tableau's Excel add-on

A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • +1: I didn't know that there is `reshape` in excel. – Metrics Oct 23 '13 at 11:22
  • 1
    @Metrics, I don't actually even own a copy of Excel. Had to borrow a computer to write this answer. But, I had remembered reading something on the Tableau blog that was similar in concept to Hadley's "Tidy Data" paper, and they also mentioned this add-on. So, it was new for me too :-) – A5C1D2H2I1M1N2O1R2T1 Oct 23 '13 at 11:48
  • 2
    Answers are so much better with GIFs. :) – Thomas Dec 16 '13 at 17:36
3

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
Metrics
  • 15,172
  • 7
  • 54
  • 83
  • When did `reshape` in base R appear? I'm so much used to do this with `reshape2::melt`... – krlmlr Oct 23 '13 at 07:16
  • 2
    @krlmlr, `reshape` has been part of base R for as long as I've been using it. It's generally much faster than the current `melt` + `*cast` approaches, though I understand those are going to get some speed boosts of their own. Metrics, +1 for representing base R :-) – A5C1D2H2I1M1N2O1R2T1 Oct 23 '13 at 07:33