Turning variables in a column into a single row

[trangility]

Hi there! I am new to R (cannot even figure how to reprex my list/table below).

I have an excel sheet with grouped diagnosis, similar to this below:

To use these diagnosis as a filter through a much larger dataset, I need to turn the variables in each column into a single row like so:

I know I can manually go through each column of grouped diagnosis and transpose them into a single row on excel...but that would mean so many read.csv's and so many unecessary tables...and time! If there is a way...please help!

[jms]

Hi!

I think this should work for your problem. By using pivot_wider, you'll get a data frame with one row that contains all the diagnosis.

suppressPackageStartupMessages(library(tidyr))

# sample data
infections<-tibble("Viral infections"=c("Influenca", "RSV","Adenovirus"),
                   "Bacterial infections"=c("Staph","Strep",NA),
                   "Resp diagnosis"=c("Pneumonia","Pleural effusion", "Pneumothorax"),
                   "GI Diagnosis"=c("Gastrostomy", "IBD", "Ileus"))

# pivot wide
infections_wide<-infections%>%pivot_wider(names_from = colnames(.),
                                          values_from = colnames(.))
#output:
infections_wide
#> # A tibble: 1 x 12
#>   `Viral infectio~ `Viral infectio~ `Viral infectio~ `Bacterial infe~
#>   <chr>            <chr>            <chr>            <chr>           
#> 1 Influenca        RSV              Adenovirus       Staph           
#> # ... with 8 more variables: `Bacterial infections_RSV_Strep_Pleural
#> #   effusion_IBD` <chr>, `Bacterial
#> #   infections_Adenovirus_NA_Pneumothorax_Ileus` <chr>, `Resp
#> #   diagnosis_Influenca_Staph_Pneumonia_Gastrostomy` <chr>, `Resp
#> #   diagnosis_RSV_Strep_Pleural effusion_IBD` <chr>, `Resp
#> #   diagnosis_Adenovirus_NA_Pneumothorax_Ileus` <chr>, `GI
#> #   Diagnosis_Influenca_Staph_Pneumonia_Gastrostomy` <chr>, `GI
#> #   Diagnosis_RSV_Strep_Pleural effusion_IBD` <chr>, `GI
#> #   Diagnosis_Adenovirus_NA_Pneumothorax_Ileus` <chr>

# remove NAs 
infections_wide[!is.na(infections_wide)[1,]]
#> # A tibble: 1 x 11
#>   `Viral infectio~ `Viral infectio~ `Viral infectio~ `Bacterial infe~
#>   <chr>            <chr>            <chr>            <chr>           
#> 1 Influenca        RSV              Adenovirus       Staph           
#> # ... with 7 more variables: `Bacterial infections_RSV_Strep_Pleural
#> #   effusion_IBD` <chr>, `Resp
#> #   diagnosis_Influenca_Staph_Pneumonia_Gastrostomy` <chr>, `Resp
#> #   diagnosis_RSV_Strep_Pleural effusion_IBD` <chr>, `Resp
#> #   diagnosis_Adenovirus_NA_Pneumothorax_Ileus` <chr>, `GI
#> #   Diagnosis_Influenca_Staph_Pneumonia_Gastrostomy` <chr>, `GI
#> #   Diagnosis_RSV_Strep_Pleural effusion_IBD` <chr>, `GI
#> #   Diagnosis_Adenovirus_NA_Pneumothorax_Ileus` <chr>

^{Created on 2020-11-12 by the reprex package (v0.3.0)}