collapse specific column and row within a group as a new column-CodePudding

Trying to collapse rows and join column in a group. Not sure, how to collapse 2 columns in specific combination.

Tried the following

df1 %>%
  dplyr::group_by(ID) %>%
  dplyr::mutate(
    agency = toString(unique(agency)),
    bucket = toString(unique(bucket))
  ) %>%
  dplyr::distinct()

Not sure, how to create the combination while pasting the column values.

data input

structure(list(ID = c(1, 1.1, 3.3, 3.3, 3.3, 3.3), time = c("M", 
"M", "M", "M", "M", "M"), restriction = c("18 ", "18 ", "N/A", 
"N/A", "N/A", "N/A"), county = c("Checked", "Checked", "Checked", 
"Checked", "Checked", "Checked"), zip_code = c("Checked", "Checked", 
NA, NA, NA, NA), age = c("Checked", "Checked", NA, NA, NA, NA
), delay = c("2 months", "2 months", "1 month", "1 month", "1 month", 
"1 month"), agency = c("SA", "Australia", "Africa", "Africa", 
"USA", "USA"), agency_response = c("Checked", "Checked", "Checked", 
"Checked", "Checked", "Checked"), bucket = c("DC", "DC", "Marvel", 
"Pixar", "Marvel", "Pixar"), bucket_response = c("Checked", "Checked", 
"Checked", "Checked", "Checked", "Checked")), class = "data.frame", row.names = c(NA, 
-6L))

expected output

 > df2
   ID time restriction  county zip_code     age    delay                agency.bucket
1 1.0    M         18  Checked  Checked Checked 2 months                        SA:DC
2 1.1    M         18  Checked  Checked Checked 2 months               Australia : DC
3 3.3    M         N/A Checked     <NA>    <NA>  1 month Africa: Marvel & USA : Pixar

CodePudding user response：

Use summarise to collapse by pasting the unique values of agency and bucket. For the other columns, use across to get the unique value.

library(dplyr)
df %>% 
  group_by(ID) %>% 
  summarise(across(time:delay, unique),
            agency.bucket = paste(unique(agency), unique(bucket), sep = ":", collapse = ", "))

   ID time restriction  county zip_code     age    delay            agency.bucket
1 1.0    M         18  Checked  Checked Checked 2 months                    SA:DC
2 1.1    M         18  Checked  Checked Checked 2 months             Australia:DC
3 3.3    M         N/A Checked     <NA>    <NA>  1 month Africa:Marvel, USA:Pixar