CODEWITHSUNDEEP
r
Julia
Julia

Reputation: 176

program to pivot data in R

I have a dataset that looks like this: 

item     01/02/2016   01/03/2016   01/04/2016
-------     ---------    ---------    ---------- 
 juice         0           0            4 
 cola          1           3            6
 bananas       5           9            1

I would like to pivot the data in such a way that the end result looks like this: 

 item   date             amount 
-------  -----          ------
 juice   01/02/2016       0
 juice   01/03/2016       0
 juice   01/04/2016       4
 cola    01/02/2016       1
 cola    01/03/2016       3
 cola    01/04/2016       6
 bananas 01/02/2016       5
 bananas 01/03/2016       9
 bananas 01/04/2016       1

I've been doing it in excel, but i'd like to write a program to automate doing it in R. How would I go about this?

Thanks!

Upvotes: 0

Views: 79

Answers (1)

Vikram Venkat
Vikram Venkat

Reputation: 671

Try this, but the variable name cannot be a date, so remap the variable names to the dates after reshaping:

 > df
         item d01 d02 d03
    1   juice   0   0   4
    2    cola   1   3   6
    3 bananas   5   9   1
    > library(reshape2)
    > df2 <- melt(df, id.vars=1)
    > df2
         item variable value
    1   juice      d01     0
    2    cola      d01     1
    3 bananas      d01     5
    4   juice      d02     0
    5    cola      d02     3
    6 bananas      d02     9
    7   juice      d03     4
    8    cola      d03     6
    9 bananas      d03     1
    > df2[order(df2$item),]
         item variable value
    3 bananas      d01     5
    6 bananas      d02     9
    9 bananas      d03     1
    2    cola      d01     1
    5    cola      d02     3
    8    cola      d03     6
    1   juice      d01     0
    4   juice      d02     0
    7   juice      d03     4

Upvotes: 1

Related Questions