How to group and combine data

I am unable to combine rows in a data frame.

I have input like this:

  element tool time    disease
    AAK1   SE   12       SARS
    AAK1   RI   12       SARS
    AAK1 A5SS   12       SARS
    AAK1   SE   12       MERS
    AAK1   RI   12       MERS
    AAK1  MXE   12       MERS
   ABCC1   SE   12 SARS-CoV-2
   ABCC1  MXE   12 SARS-CoV-2
  ABCC10   SE   12       MERS
  ABCC10   A3SS 12       MERS

I want output like this:

element  time    disease                  SE   RI    MXE   A3SS  A5SS
AAK1       12    SARS and MERS             1     1     0     0     0
AAK1       12    SARS                      0     0     0     0     1
AAK1       12    MERS                      0     0     1     0     0
ABCC1      12    SARS-CoV-2                1     0     1     0     0
ABCC10     12    MERS                      1     0     0     1     0

What I expect the function to do is, first group_by element, you will get this:

element tool time    disease
AAK1   SE   12       SARS
AAK1   RI   12       SARS
AAK1 A5SS   12       SARS
AAK1   SE   12       MERS
AAK1  MXE   12       MERS
AAK1   RI   12       MERS

• Split the tool column into 5 columns,
  SE RI MXE A3SS A5SS

• check whether you can combine the disease column or not, using SE RI MXE A3SS A5SS.

In this example, only SE and RI can be combined, because they are present in both SARS and MERS (disease column), the observations which cannot be combined, write them separately.

element  time    disease                     SE   RI    MXE   A3SS  A5SS
AAK1      12    SARS and MERS                1     1     0     0     0
AAK1      12    SARS                         0     0     0     0     1
AAK1      12    MERS                         0     0     1     0     0

In this way run the loop on each element, and give one dataframe as output, which I mentioned above.

You can use summarize with a collapse in paste to stick disease that shares element, tool and time together. Then pivot_wider to spread out the columns, with their length in the tool column as the fill parameter (also missing values are filled with zero).

library(tidyverse)

df |> 
  group_by(element, tool, time) |> 
  summarize(disease = paste0(sort(disease), collapse = " and ")) |> 
  pivot_wider(names_from = tool, values_from = tool, values_fn = length, values_fill = 0) |> 
  ungroup()

# A tibble: 5 × 8
  element  time disease        A5SS   MXE    RI    SE  A3SS
  <chr>   <int> <chr>         <int> <int> <int> <int> <int>
1 AAK1       12 SARS              1     0     0     0     0
2 AAK1       12 MERS              0     1     0     0     0
3 AAK1       12 SARS and MERS     0     0     1     1     0
4 ABCC1      12 SARS-CoV-2        0     1     0     1     0
5 ABCC10     12 MERS              0     0     0     1     1

</details>