Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pivot a table to make columns fro a variable row values in R

Tags:

r

reshape

pivot

I have a data.frame with the columns: Month, Store and Demand.

Month   Store   Demand
Jan     A   100
Feb     A   150
Mar     A   120
Jan     B   200
Feb     B   230
Mar     B   320

I need to pivot it around to make a new data.frame or array with columns for each month, store e.g.:

Store   Jan Feb Mar
A       100 150 120
B       200 230 320

Any help is very much appreciated. I have just started with R.

like image 405
user597551 Avatar asked Jun 24 '11 17:06

user597551


2 Answers

> df <- read.table(textConnection("Month   Store   Demand
+ Jan     A   100
+ Feb     A   150
+ Mar     A   120
+ Jan     B   200
+ Feb     B   230
+ Mar     B   320"), header=TRUE)

So in all likelihood your Month column is a factor with levels sorted alphabetically (EDIT:)

> df$Month <- factor(df$Month, levels= month.abb[1:3])
 # Just changing levels was not correct way to handle the problem. 
 # Need to use within a factor(...) call.
> xtabs(Demand ~ Store+Month, df)
      Month
 Store Jan Feb Mar
     A 100 150 120
     B 200 230 320

A slightly less obvious method (since the 'I' function returns its argument):

> with(df, tapply(Demand, list(Store, Month) , I)  )
  Jan Feb Mar
A 100 150 120
B 200 230 320
like image 60
IRTFM Avatar answered Nov 15 '22 05:11

IRTFM


Welcome to R.

There are usually many ways to get to the same end using R. Another approach would be to use Hadley's reshape package.

# create the data as explained by @Dwin
df <- read.table(textConnection("Month   Store   Demand
                                Jan     A   100
                                Feb     A   150
                                Mar     A   120
                                Jan     B   200
                                Feb     B   230
                                Mar     B   320"), 
                 header=TRUE)

# load the reshape package from Hadley -- he has created GREAT packages
library(reshape)

# reshape the data from long to wide
cast(df, Store ~ Month)

And for reference, you should check out this great tutorial. http://www.jstatsoft.org/v21/i12/paper

like image 27
Btibert3 Avatar answered Nov 15 '22 05:11

Btibert3