2

I have a table like this:

ID Date Status
101 2020-09-14 1
102 2020-09-14 1
103 2020-09-14 1
104 2020-09-14 2
105 2020-09-14 2
106 2020-09-14 2

But want a table like this:

Status ID Date
1 101,102,103 2020-09-14, 2020-09-14, 2020-09-14
1 104,105,106 2020-09-14, 2020-09-14, 2020-09-14

Code that i'm currently using:

note: date is in format yyyy-mm-dd before running code.

g1 <- df1 %>% 
  mutate(Date = as.Date(Date, format = '%Y%m%d')) %>%
  group_by(status) %>% 
  summarise_at(c("ID", "Date"), list)

This seems to work except for the date in the new table is not in yyyy-mm-dd. For example, 2021-06-10 is converting to 18788.

1 Answers1

2

A possible solution:

library(tidyverse)

df %>% 
  group_by(Status) %>% 
  summarise(ID = str_c(ID, collapse = ","), Date = str_c(Date, collapse = ","))

#> # A tibble: 2 × 3
#>   Status ID          Date                            
#>    <int> <chr>       <chr>                           
#> 1      1 101,102,103 2020-09-14,2020-09-14,2020-09-14
#> 2      2 104,105,106 2020-09-14,2020-09-14,2020-09-14

A more succinct alternative:

library(tidyverse)

df %>% 
  group_by(Status) %>% 
  summarise(across(c(ID, Date), str_c, collapse = ","))

#> # A tibble: 2 × 3
#>   Status ID          Date                            
#>    <int> <chr>       <chr>                           
#> 1      1 101,102,103 2020-09-14,2020-09-14,2020-09-14
#> 2      2 104,105,106 2020-09-14,2020-09-14,2020-09-14
PaulS
  • 21,159
  • 2
  • 9
  • 26
  • 1
    Thank you so much, this worked perfectly. Would you be able to explain what the code is doing in your example vs mine? Would love to understand it a little better. –  Jun 10 '22 at 19:16
  • You are welcome! First, there is no need to convert dates to date format. Second, when summarizing, I just concatenate the ID and then the dates. Moreover, `summarise_at` is superseded. And while you try to get a list, I try to get a concatenated string. I am going no to try to shorten my solution. – PaulS Jun 10 '22 at 19:22
  • See my second solution, which is closer to your attempted solution. – PaulS Jun 10 '22 at 19:26