Wide to long format for more than two variables

[karthikbalajee]

I'm a beginner in R using dplyr and tidyverse. I Need to convert this wide format into long form as shown in this picture. gather function is not helpful. Kindly give suggestions.
Thank you
I have dataset in the wide format as below:-

antibiotic <- tibble::tribble(
  ~ID, ~Organism, ~Antibiotic.One, ~Result.One, ~Antibiotic.Two, ~Result.Two, ~Antibiotic.Three, ~Result.Three,
  1L, "Staphylococcus aureus", "Ciprofloxacin", "Sensitive", "Trimethoprim-sulfamethoxazole", "Sensitive", "Erythromycin", "Sensitive",
  2L, "Staphylococcus aureus", "Amikacin", "Sensitive", "Gentamycin", "Sensitive", "Trimethoprim-sulfamethoxazole", "Sensitive",
  3L, "Escherichia coli", "Nitrofurantoin", "Sensitive", "Meropenem", "Sensitive", "Amikacin", "Resistant"
)

head(antibiotic)
#> # A tibble: 3 x 8
#>      ID Organism       Antibiotic.One Result.One Antibiotic.Two       Result.Two
#>   <int> <chr>          <chr>          <chr>      <chr>                <chr>     
#> 1     1 Staphylococcu… Ciprofloxacin  Sensitive  Trimethoprim-sulfam… Sensitive 
#> 2     2 Staphylococcu… Amikacin       Sensitive  Gentamycin           Sensitive 
#> 3     3 Escherichia c… Nitrofurantoin Sensitive  Meropenem            Sensitive 
#> # … with 2 more variables: Antibiotic.Three <chr>, Result.Three <chr>

I need to convert this into the following long format:-

antibiotic_long <-tibble::tribble(
  ~ID,               ~Organism,                     ~Antibiotic,     ~Result,
   1L, "Staphylococcus aureus",                 "Ciprofloxacin", "Sensitive",
   1L, "Staphylococcus aureus", "Trimethoprim-sulfamethoxazole", "Sensitive",
   1L, "Staphylococcus aureus",                  "Erythromycin", "Sensitive",
   2L, "Staphylococcus aureus",                      "Amikacin", "Sensitive",
   2L, "Staphylococcus aureus",                    "Gentamycin", "Sensitive",
   2L, "Staphylococcus aureus", "Trimethoprim-sulfamethoxazole", "Sensitive",
   3L,      "Escherichia coli",                "Nitrofurantoin", "Sensitive",
   3L,      "Escherichia coli",                     "Meropenem", "Sensitive",
   3L,      "Escherichia coli",                      "Amikacin", "Resistant"
  )
head(antibiotic_long)
#> # A tibble: 6 x 4
#>      ID Organism              Antibiotic                    Result   
#>   <int> <chr>                 <chr>                         <chr>    
#> 1     1 Staphylococcus aureus Ciprofloxacin                 Sensitive
#> 2     1 Staphylococcus aureus Trimethoprim-sulfamethoxazole Sensitive
#> 3     1 Staphylococcus aureus Erythromycin                  Sensitive
#> 4     2 Staphylococcus aureus Amikacin                      Sensitive
#> 5     2 Staphylococcus aureus Gentamycin                    Sensitive
#> 6     2 Staphylococcus aureus Trimethoprim-sulfamethoxazole Sensitive

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

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

[andresrcs]

Can you please share a small part of the data set in a copy-paste friendly format?

In case you don't know how to do it, there are many options, which include:

1. If you have stored the data set in some R object, dput function is very handy.

2. In case the data set is in a spreadsheet, check out the datapasta package. Take a look at this link.

[karthikbalajee]

Thanks. I have used the datapasta package and shared a part of the data set.
Thank you

[andresrcs]

This is a way to do it

library(tidyverse)

antibiotic <- tibble::tribble(
    ~ID, ~Organism, ~Antibiotic.One, ~Result.One, ~Antibiotic.Two, ~Result.Two, ~Antibiotic.Three, ~Result.Three,
    1L, "Staphylococcus aureus", "Ciprofloxacin", "Sensitive", "Trimethoprim-sulfamethoxazole", "Sensitive", "Erythromycin", "Sensitive",
    2L, "Staphylococcus aureus", "Amikacin", "Sensitive", "Gentamycin", "Sensitive", "Trimethoprim-sulfamethoxazole", "Sensitive",
    3L, "Escherichia coli", "Nitrofurantoin", "Sensitive", "Meropenem", "Sensitive", "Amikacin", "Resistant"
)

antibiotic %>% 
    pivot_longer(c(-ID, -Organism), names_to = "variable", values_to = "value") %>% 
    mutate(variable = str_remove(variable, '\\..+$')) %>% 
    pivot_wider(id_cols = c(ID, Organism),
                names_from = variable,
                values_from = value,
                values_fn = list) %>% 
    unnest(c(Antibiotic, Result))
#> # A tibble: 9 × 4
#>      ID Organism              Antibiotic                    Result   
#>   <int> <chr>                 <chr>                         <chr>    
#> 1     1 Staphylococcus aureus Ciprofloxacin                 Sensitive
#> 2     1 Staphylococcus aureus Trimethoprim-sulfamethoxazole Sensitive
#> 3     1 Staphylococcus aureus Erythromycin                  Sensitive
#> 4     2 Staphylococcus aureus Amikacin                      Sensitive
#> 5     2 Staphylococcus aureus Gentamycin                    Sensitive
#> 6     2 Staphylococcus aureus Trimethoprim-sulfamethoxazole Sensitive
#> 7     3 Escherichia coli      Nitrofurantoin                Sensitive
#> 8     3 Escherichia coli      Meropenem                     Sensitive
#> 9     3 Escherichia coli      Amikacin                      Resistant

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