Merge rows with the same value within a column and put different values in the same row

[andresrcs]

@IsabelHx

So is there a way to merge two or more rows that have the same values in all columns except one (treatment) and have the values that are different be added in a new column (so treatment no2) so there is only one row for one patient?

You can get the desired output but you would need to find out the max number of treatments per patient in advance and, honestly, the result would be messy and less useful for future processing.

library(tidyverse)

start_df <- tribble(~patient_id,~disease_number,~treatment,
                     "p001","d001" ,"treatment A",
                     "p001","d001","treatment B",
                     "p002","d002" , "treatment C")

start_df %>% 
    group_by(patient_id, disease_number) %>% 
    summarise(treatment = paste(treatment, collapse = ",")) %>% 
    separate(treatment, into = c("treatment_1", "treatment_2"), sep = ",")
#> `summarise()` has grouped output by 'patient_id'. You can override using the
#> `.groups` argument.
#> Warning: Expected 2 pieces. Missing pieces filled with `NA` in 1 rows [2].
#> # A tibble: 2 × 4
#> # Groups:   patient_id [2]
#>   patient_id disease_number treatment_1 treatment_2
#>   <chr>      <chr>          <chr>       <chr>      
#> 1 p001       d001           treatment A treatment B
#> 2 p002       d002           treatment C <NA>

^{Created on 2022-04-24 by the reprex package (v2.0.1)}

Even merging the treatments into a character string would make more sense

start_df %>% 
    group_by(patient_id, disease_number) %>% 
    summarise(treatment = paste(treatment, collapse = ","))
#> `summarise()` has grouped output by 'patient_id'. You can override using the
#> `.groups` argument.
#> # A tibble: 2 × 3
#> # Groups:   patient_id [2]
#>   patient_id disease_number treatment              
#>   <chr>      <chr>          <chr>                  
#> 1 p001       d001           treatment A,treatment B
#> 2 p002       d002           treatment C

Note: Next time please provide a proper REPRoducible EXample (reprex) illustrating your issue.