Creating a pivot table

Question

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.

score 2 · Answer 1 · answered Mar 29 '13 at 21:25

> xtabs(N~id+year, data=dataset)
   year
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

Your dataset is in "molten" form using the reshape2 terminology. Also known in data query circles as "first normal form".

Jilber Urbina · Accepted Answer · 2013-03-29T21:35:23.953

2

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

edited Mar 29 '13 at 21:35

answered Mar 29 '13 at 21:27

Jilber Urbina

58,147
10
114
138

1

In reshape2 (d/a)cast functions you can supply a `fill=0` argument to "fill-in" the zeros. – IRTFM Mar 29 '13 at 21:31
Thank you both for useful answers. I selected this version as the preferred answer because it also works when id is a factor. – Luke Mar 29 '13 at 21:41

score 1 · Answer 3 · answered Oct 01 '17 at 20:26

1

these days using tidyverse is more the rage:

library(tidyr)

dataset %>% 
  spread(year, N, fill = 0)

answered Oct 01 '17 at 20:26

Guy Manova

434
4
7

Creating a pivot table

3 Answers3

Linked

Related