reshape grouped data in R-CodePudding

I have the following data:

    id <- c(1,1,1,1,2,2,2,2,2,2)
    date <-as.Date(c("2007-06-22", "2007-06-22", "2007-07-13","2007-07-13", 
                     "2019-10-05", "2019-10-05", "2019-11-07", "2019-11-07",
                     "2007-06-22","2007-06-22"))
    value <-c(0,3,2,4,0,1,4,2,6,8)
    
    mydata_1 <- data.frame(id, date, value)
    mydata_1

id    date        value
1    2007-06-22     0
1    2007-06-22     3
1    2007-07-13     2
1    2007-07-13     4
2    2019-10-05     0
2    2019-10-05     1
2    2019-11-07     4
2    2019-11-07     2
2    2007-06-22     6
2    2007-06-22     8

I would like the data to look like this:

id <- c(1,1,2,2,2)
date <-as.Date(c("2007-06-22", "2007-07-13", "2019-10-05", "2019-11-07","2007-06-22"))
value.1 = c(0,2,0,4,6)
value.2 = c(3,4,1,2,8)

mydata_2 <- data.frame(id, date, value.1, value.2)
mydata_2

id    date       value.1   value.2
1     2007-06-22   0       3
1     2007-07-13   2       4
2     2019-10-05   0       1
2     2019-11-07   4       2
2     2007-06-22   6       8

I have tried below from (Reshaping data matrix in R) but since some of the dates are the same in the two different id's it is not working as intended

dateno <- with(mydata_1, ave(id, date, FUN = seq_along))

test2 <- transform(mydata_1, dateno = dateno)
reshape(test2, dir = "wide", idvar = c("id","date"), timevar = "dateno")

CodePudding user response：

I think I have come up with an answer following this guide How to transpose a data frame by group using reshape2 library?

mydata_1 = mydata_1 %>% group_by(id,date) %>% mutate(id_2 = paste0("V",row_number()))
        
        library(tidyr)
        mydata_2 = spread(data = my, key = id_2, value = value)
    
        mydata_2
    
    id date          V1    V2
      <dbl> <date>     <dbl> <dbl>
    1     1 2007-06-22     0     3
    2     1 2007-07-13     2     4
    3     2 2007-06-22     6     8
    4     2 2019-10-05     0     1
    5     2 2019-11-07     4     2

CodePudding user response：

Maybe sth. like this:

library(tidyverse)

id <- c(1, 1, 1, 1, 2, 2, 2, 2, 2, 2)
date <- as.Date(c(
  "2007-06-22", "2007-06-22", "2007-07-13", "2007-07-13",
  "2019-10-05", "2019-10-05", "2019-11-07", "2019-11-07",
  "2007-06-22", "2007-06-22"
))
value <- c(0, 3, 2, 4, 0, 1, 4, 2, 6, 8)

mydata_1 <- data.frame(id, date, value)
mydata_1

mydata_1 %>%
  group_by(id, date) %>%
  mutate(visit = row_number()) %>%
  complete(id, date, fill = list(value = 0)) %>%
  pivot_wider(names_from = visit, values_from = value, names_prefix = "value.")

^{Created on 2021-11-25 by the reprex package (v2.0.1)}

CodePudding user response：

Another possible solution:

library(tidyverse)

id <- c(1,1,1,1,2,2,2,2,2,2)
date <-as.Date(c("2007-06-22", "2007-06-22", "2007-07-13","2007-07-13", 
                 "2019-10-05", "2019-10-05", "2019-11-07", "2019-11-07",
                 "2007-06-22","2007-06-22"))
value <-c(0,3,2,4,0,1,4,2,6,8)
mydata_1 <- data.frame(id, date, value)

mydata_1 %>% 
  group_by(id, date) %>% 
  summarise(value = str_c(value, collapse = ","), .groups = "drop") %>% 
  separate(value, into=c("value1", "value2"), sep=",", convert = T)

#> # A tibble: 5 × 4
#>      id date       value1 value2
#>   <dbl> <date>      <int>  <int>
#> 1     1 2007-06-22      0      3
#> 2     1 2007-07-13      2      4
#> 3     2 2007-06-22      6      8
#> 4     2 2019-10-05      0      1
#> 5     2 2019-11-07      4      2