R reshape function

Question

I have a problem with the reshape function. I tired it for while now, but it just doesn't work. My data looks like this:

    KeyItem    Year    Value
    Income     2011     10000
    Income     2010     29299
    Depth      2010     29393
    Market Cap 2010     39393
    Depth      2011     20000
    Market Cap 2011     30000

and I need it to look like this for my function:

   KeyItem        2011         2010
   Income         10000        29299
   Depth          20000        29393
   Market Cap     30000        39393

See this answer for a collection of various ways to do this: http://stackoverflow.com/a/9617424/210673 — Aaron left Stack Overflow, Dec 28 '12 at 03:45

csgillespie · Accepted Answer · 2012-11-06T09:15:57.057

The easiest way is to use the dcast function in the reshape2 package. First, load in the data:

dd = read.table(textConnection("KeyItem Year Value
Income 2011 10000
Income 2010 29299
Depth 2010 29393
Market 2010 39393
Depth 2011 20000
Market 2011 30000"), header=TRUE)

then load the package:

library(reshape2)

Finally, just the dcast function:

dcast(dd, KeyItem ~ Year)

to get:

R> dcast(dd, KeyItem ~ Year)
Using Value as value column: use value.var to override.
  KeyItem  2010  2011
1   Depth 29393 20000
2  Income 29299 10000
3  Market 39393 30000

To go the other way, just use the melt function:

melt(dcast(dd, KeyItem ~ Year))

You can reorder your columns in the usual way:

dd1 = dcast(dd, KeyItem ~ Year) 
dd1[,c("KeyItem", sort(colnames(dd1[, 2:ncol(dd1)]),TRUE))]

thank you that is exactly what I wanted. Right now the first column is my last or earliest year. is there a way to do it the other way. So that my first column is my latest year? — MarMarko, Nov 06 '12 at 09:12

score 3 · Answer 2 · answered Nov 06 '12 at 08:55

3

df <- read.table(text="KeyItem    Year    Value
Income     2011     10000
Income     2010     29299
Depth      2010     29393
Market_Cap 2010     39393
Depth      2011     20000
Market_Cap 2011     30000",header=TRUE)

library(reshape2)
df2 <- dcast(df,KeyItem~Year)

#     KeyItem  2010  2011
#1      Depth 29393 20000
#2     Income 29299 10000
#3 Market_Cap 39393 30000

answered Nov 06 '12 at 08:55

Roland

127,288
10
191
288

thank you that is exactly what I wanted. Right now the first column is my last or earliest year. is there a way to do it the other way. So that my first year is my latest year? – MarMarko Nov 06 '12 at 09:05
1

Quick and dirty: `df2 <- df2[, c(1,3,2)]` – seancarmody Nov 06 '12 at 09:13

score 0 · Answer 3 · answered Dec 27 '12 at 18:04

The "reshape" and "reshape2" packages generally get all the love, but there are options in base R too. Assuming your data.frame is called "df", here are two (and a half) options in base R:

## Using base R reshape()
reshape(df, direction = "wide", idvar="KeyItem", timevar="Year")
#      KeyItem Value.2011 Value.2010
# 1     Income      10000      29299
# 3      Depth      20000      29393
# 4 Market_Cap      30000      39393

## Using xtabs()
xtabs(Value ~ KeyItem + Year, df)
#             Year
# KeyItem       2010  2011
#   Depth      29393 20000
#   Income     29299 10000
#   Market_Cap 39393 30000

## Here's the "half": Using xtabs(), but as a data.frame
as.data.frame.matrix(xtabs(Value ~ KeyItem + Year, df))
#             2010  2011
# Depth      29393 20000
# Income     29299 10000
# Market_Cap 39393 30000

R reshape function

3 Answers3