reshape grouped data in R

Question

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")

you tried https://tidyr.tidyverse.org/reference/pivot_wider.html ? — StupidWolf, Nov 25 '21 at 09:38
Does this answer your question? ["spread" multiple variables using pivot\_wider()](https://stackoverflow.com/questions/57300512/spread-multiple-variables-using-pivot-wider) — Limey, Nov 25 '21 at 09:42
Why do you want to have the id column in your result? You just have two values per date and id, right? so in your result it is just one row per date and not per id? — danlooo, Nov 25 '21 at 09:44
Thanks for the tips. I have tried to work it out with pivot_wider but with no luck. In the "real data" I am working with the "value" column represents answers on a 34 items self-assessment form filled in at different dates. So I need to know: who rated the form (id) what the scores were (values) and the date each person rated the form (date). — Carl, Nov 25 '21 at 10:41

danlooo · Answer 1 · 2021-11-25T11:02:30.267

0

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)}

edited Nov 25 '21 at 11:02

answered Nov 25 '21 at 09:42

danlooo

10,067
2
8
22

Thanks for the help but this is not the result I want. Person 1 rated the "form" two times (at 2007-06-22 and 2007-07-13). Person 2 rated the "form" three times (2007-06-22, 2019-10-05, 2019-11-07) – Carl Nov 25 '21 at 10:43

score 0 · Answer 2 · answered Nov 25 '21 at 10:58

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

score 0 · Answer 3 · answered Nov 25 '21 at 12:05

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

reshape grouped data in R

3 Answers3