Luke Luke - 3 years ago 129
R Question

Creating a pivot table

Suppose I have a dataset that looks like the following, in which years are listed down rows:

id<-c(1,1,1,2,2,2,3,3,3)
year<-c(1990, 1991, 1992, 1992, 1993, 1994, 1993, 1994, 1995)
N<-c(7,8,9,7,1,2,5,4,3)
dataset<-data.frame(cbind(id, year, N))


I'd like to have the output look like the following, with years as column headings:

id 1990 1991 1992 1993 1994 1995
1 7 8 9 0 0 0
2 0 0 7 1 2 0
3 0 0 0 5 4 3


I know this is a fairly easy problem, but I've been messing around with xtabs() and with melt() and cast(), but I can't quite get it right.

Answer Source

Another way to do it:

> library(reshape2)
> dcast(dataset, id ~ year, fill=0)
# Using N as value column: use value.var to override.
  id 1990 1991 1992 1993 1994 1995
1  1    7    8    9    0    0    0
2  2    0    0    7    1    2    0
3  3    0    0    0    5    4    3
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download