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

[Chel Hee Lee]

You are welcome, I am glad that I was able to help.

Chel Hee Lee, PhD
Biostatistician and Manager
Clinical Research Support Unit
College of Medicine
University of Saskatchewan


On 12/20/2014 03:53 AM, Kristi Glover wrote:
> Thank you Prof. Lee for your code. I am sorry that I noticed that I sent the email in Httml format and realized that it was almost impossible to read it. However, you gave an effort to help me. Now I changed the format.
> I really appreciated for your help. It seems the code you wrote works for me.
> Sincerely,
> KG
>
>
> ----------------------------------------
>> Date: Sat, 20 Dec 2014 00:09:21 -0600
>> From: chl948 at mail.usask.ca
>> Subject: Re: [R] How to make Pivot table with two variables in R?
>> To: kristi.glover at hotmail.com; r-help at r-project.org
>>
>>
>>> 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.
>>>
>   		 	   		
>