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]]
> 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. >
Berend Hasselman
2014-Dec-20 06:45 UTC
[R] How to make Pivot table with two variables in R?
> On 20-12-2014, at 05:45, Kristi Glover <kristi.glover at hotmail.com> 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?Since you used html for your mail, it is a complete mess and not usable. You are requested to not post in HTML. See the posting guide. Berend> [[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.
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. >>> > >