[R] How to make Pivot table with two variables in R?

[Chel Hee Lee]

 > x <- dat
 > x$time <- as.factor(as.Date(x$time, format="%Y-%B%d"))
 > tmp <- split(x, x$tag)
 > tmp1 <- do.call(rbind, lapply(tmp, function(x){
+ tb <- table(x$time)
+  idx <- which(tb>0)
+  tb1 <- replace(tb, idx, as.character(x$states))
+ }))
 > print(tmp1, quote=FALSE)
    2010-05-27 2011-06-27 2011-06-28 2012-06-25 2013-06-21 2014-01-05 
2014-07-27
x1 A          B          B          0          0          0          0 

x2 A          0          0          0          0          0          0 

x3 0          0          0          D          0          0          0 

x4 0          0          0          0          C          0          0 

x5 0          0          0          0          B          0          0 

x6 0          0          0          0          Out        0          0 

x7 0          0          0          C          0          0          Out 

x8 0          0          0          0          0          B          0 

    2015-04-07
x1 0
x2 0
x3 0
x4 0
x5 0
x6 A
x7 0
x8 0
 >

Is this what you are looking for?  I hope this helps.

Chel Hee Lee


On 12/19/2014 10:45 PM, Kristi Glover wrote:
> Hi R User, Would you suggest me on how I can build a pivot table using two variables? I want to put "text" in the table instead of value.  I have attached an example data and the type of table (FinalTable) I was looking for. I am looking for your suggestions. ThanksKG=====dat<-structure(list(tag = structure(c(1L, 1L, 1L, 2L, 3L, 4L, 5L, 6L, 6L, 7L, 7L, 8L), .Label = c("x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8"), class = "factor"), time = structure(c(1L, 2L, 3L, 1L, 4L, 5L, 5L, 5L, 8L, 4L, 7L, 6L), .Label = c("2010-May 27", "2011-June 27", "2011-June 28", "2012-June 25", "2013-June 21", "2014-Jan 05", "2014-July 27", "2015-April 07"), class = "factor"),     states = structure(c(1L, 2L, 2L, 1L, 4L, 3L, 2L, 5L, 1L,     3L, 5L, 2L), .Label = c("A", "B", "C", "D", "Out"), class = "factor")), .Names = c("tag", "time", "states"), class = "data.frame", row.names = c(NA, -12L))
> dat###table(dat$tag,dat$time)# it gives value but I want the name of the states in the table instead of value.#For examplefinalTable<-structure(list(tag = structure(1:8, .Label = c("x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8"), class = "factor"), X2010.May.27 = structure(c(2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("0", "A"), class = "factor"),     X2011.June.27 = structure(c(2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L    ), .Label = c("0", "B"), class = "factor"), X2011.June.28 = structure(c(2L,     1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("0", "B"), class = "factor"),     X2012.June.25 = structure(c(1L, 1L, 3L, 1L, 1L, 1L, 2L, 1L    ), .Label = c("0", "C", "D"), class = "factor"), X2013.June.21 = structure(c(1L,     1L, 1L, 3L, 2L, 4L, 1L, 1L), .Label = c("0", "B", "C", "Out"    ), class = "factor"), X2014.Jan.05 = structure(c(1L, 1L,     1L, 1L, 1L, 1L, 1L, 2L), .Label = c("0", "B"), class = "factor"),     X2014.July.27 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L    ), .Label = c("!
>   0", "Out"), class = "factor"), X2015.April.07 = c(0L,     0L, 0L, 0L, 0L, 1L, 0L, 0L)), .Names = c("tag", "X2010.May.27", "X2011.June.27", "X2011.June.28", "X2012.June.25", "X2013.June.21", "X2014.Jan.05", "X2014.July.27", "X2015.April.07"), class = "data.frame", row.names = c(NA,-8L))
> finalTable
> #How is it possible to get the finalTable as shown above?Any suggestions? 		 	   		
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>