Hello,

Function tidyr::pivot_wider is meant for this kind of problem. It is package tidyr's new way of reshaping from long to wide format. QUoting from the help page:

Details

pivot_wider() is an updated approach to spread(), designed to be both simpler to use and to handle more use cases. We recomend you use pivot_wider() for new code; spread() isn't going away but is no longer under active development.


In what follows I have use column 'date' to fill the cells, not column 'ObsDate' like in your question. Just change this and you'll get your expected result.


library(dplyr)
library(tidyr)
library(lubridate)

daT %>%
  arrange(ObsSite) %>%
  pivot_wider(id_cols = id,
              names_from = ObsSite,
              values_from = date,
              values_fn = list(date = function(x){
                paste(x, collapse = ",")
              })) %>%
  mutate_all(function(x) ifelse(is.na(x), 0, x)) %>%
  arrange(id)


Hope this helps,

Rui Barradas



Às 00:37 de 06/02/20, Marna Wagley escreveu:
Hi R users,
I was trying to create a pivot table for the following data, in which I
wanted to put "id" in  rows and "ObsSite" in columns and "Obsdate" is in
the cells.

I used the following code but it took only one date among the two dates.
For example, the animal (Id2) which was observed in the site7  two time or
days (07/03/14 & 05/17/2014). see below
id ObsSite ObsDate
id1 site7 06/13/13
id2 site7 07/03/14
id2 site7 05/17/14
id4 site4 05/08/14
id5 site5 06/13/14
id6 site1 05/30/14
id6 site1 06/28/13
id7 site5 06/25/13

I wanted to put both dates in the cell if there is any multiple dates, as
similar shown below





   site1 site4 site5 site7
id1 0 0 0 6/13/13
id2 0 0 0 7/3/2014, 5/17/2014
id4 0 5/8/14 0 0
id5 0 0 6/13/14 0
id6 5/30/2014, 6/28/2013 0 0 0
id7 0 0 6/25/13 0

the code I used is given below but it gave me only one date in that cells.
Is there any way to get both dates in these cells?
Thanks,

###
library(lubridate)
daT<-structure(list(id = structure(c(1L, 2L, 2L, 3L, 4L, 5L, 5L, 6L
), .Label = c("id1", "id2", "id4", "id5", "id6", "id7"), class = "factor"),
     ObsSite = structure(c(4L, 4L, 4L, 2L, 3L, 1L, 1L, 3L), .Label =
c("site1",
     "site4", "site5", "site7"), class = "factor"), ObsDate =
structure(c(4L,
     8L, 2L, 1L, 5L, 3L, 7L, 6L), .Label = c("05/08/14", "05/17/14",
     "05/30/14", "06/13/13", "06/13/14", "06/25/13", "06/28/13",
     "07/03/14"), class = "factor")), .Names = c("id", "ObsSite",
"ObsDate"), class = "data.frame", row.names = c(NA, -8L))
daT
daT$date <- mdy(daT$ObsDate)
tmp <- split(daT, daT$id)
head(tmp)

pivotTable <- do.call(rbind, lapply(tmp, function(daT){
   tb <- table(daT$ObsSite)
   idx <- which(tb>0)
   tb1 <- replace(tb, idx, as.character(daT$date))
}))


data.frame(pivotTable)

        [[alternative HTML version deleted]]

______________________________________________
R-help@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.


______________________________________________
R-help@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.

Reply via email to