Pivoting data can be a pain point in bioinformatics workflows. Lots of bioinformatics software are tied to the wide format with data spread out among multiple columns while the whole tidyverse/ggplot system requires long data with as few columns as possible. Becoming proficient at switching your data to long format has several benefits. (1) It provides a unified format for any required data manipulations and summarizations making them faster to write and easier to read and (2) it is the required input format for the ggplot system. In R the tidyverse provides the tools to interchange wide and long data.

The Problem:

Typically subjects in bioinformatics datasets (columns) will have associated metadata like treatments and indicators of groups or replicates. Any metadata that corresponds to rows can be easily added to the data.frame to be pivoted (eg. with cbind). But if there are column metadata they have to be added manually after the pivot.

The Solution:

There are a couple of ways to do this. The way I've settled on is to have a table of target meta-data and use a join after the pivot to connect it to the data. I find myself doing this repeatedly in almost all of my analyses but it's a solution I stumbled on by trial and error. I have never seen it spelled out explicitly anywhere so here it is.

head(relig_income)
## # A tibble: 6 x 12
##   religion `<$10k` `$10-20k` `$20-30k` `$30-40k` `$40-50k` `$50-75k` `$75-100k` `$100-150k`
##   <chr>      <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>      <dbl>       <dbl>
## 1 Agnostic      27        34        60        81        76       137        122         109
## 2 Atheist       12        27        37        52        35        70         73          59
## 3 Buddhist      27        21        30        34        33        58         62          39
## 4 Catholic     418       617       732       670       638      1116        949         792
## 5 Don’t k~      15        14        15        11        10        35         21          17
## 6 Evangel~     575       869      1064       982       881      1486        949         723
## # ... with 3 more variables: `>150k` <dbl>, `Don't know/refused` <dbl>, religionClass <chr>

First, create the metadata.

I'll use the relig_income dataset as an example. I will demonstrate how to add both row metadata (easy) and column metadata (bit tricky). For row metadata I will add a new column for religion class that will be defined randomly and for column metadata I will group income levels into low, medium, high and unknown listed in a separate data.frame. Note that this method relies on linking data column names to metadata so check the metadata table carefully!

To add the row metadata I simply add a new column to the relig_income table with my random values. For the column metadata I will make a new data.frame.

## Row metadata
set.seed(10)
relig_income$religionClass <- 
  sample(c("A", "B", "C"), nrow(relig_income), replace = TRUE)

## Column metadata
columnMetadata <- data.frame(
  income = c(colnames(relig_income)[
    grepl("0", colnames(relig_income))],
    "Don't know/refused"),
  incomeGroup = c(rep("low", 3), rep("medium", 3), 
            rep("high", 3), "Don't know/refused"))
columnMetadata
##                income        incomeGroup
## 1               <$10k                low
## 2             $10-20k                low
## 3             $20-30k                low
## 4             $30-40k             medium
## 5             $40-50k             medium
## 6             $50-75k             medium
## 7            $75-100k               high
## 8           $100-150k               high
## 9               >150k               high
## 10 Don't know/refused Don't know/refused

Step 1: pivot_longer as usual

Don't forget to exclude the new religionClass column from the pivot.

relig_income %>%
  pivot_longer(-c(religion, religionClass), names_to = "income", values_to = "count")
## # A tibble: 180 x 4
##    religion religionClass income             count
##    <chr>    <chr>         <chr>              <dbl>
##  1 Agnostic C             <$10k                 27
##  2 Agnostic C             $10-20k               34
##  3 Agnostic C             $20-30k               60
##  4 Agnostic C             $30-40k               81
##  5 Agnostic C             $40-50k               76
##  6 Agnostic C             $50-75k              137
##  7 Agnostic C             $75-100k             122
##  8 Agnostic C             $100-150k            109
##  9 Agnostic C             >150k                 84
## 10 Agnostic C             Don't know/refused    96
## # ... with 170 more rows

Step 2: join the column metadata

All metadata columns will be added automatically with this step.

relig_income %>%
  pivot_longer(-c(religion, religionClass), names_to = "income", values_to = "count") %>%
  inner_join(columnMetadata, by = "income")
## # A tibble: 180 x 5
##    religion religionClass income             count incomeGroup       
##    <chr>    <chr>         <chr>              <dbl> <chr>             
##  1 Agnostic C             <$10k                 27 low               
##  2 Agnostic C             $10-20k               34 low               
##  3 Agnostic C             $20-30k               60 low               
##  4 Agnostic C             $30-40k               81 medium            
##  5 Agnostic C             $40-50k               76 medium            
##  6 Agnostic C             $50-75k              137 medium            
##  7 Agnostic C             $75-100k             122 high              
##  8 Agnostic C             $100-150k            109 high              
##  9 Agnostic C             >150k                 84 high              
## 10 Agnostic C             Don't know/refused    96 Don't know/refused
## # ... with 170 more rows

Step 3 (optional): Convert character data to ordered factors to control plotting order

relig_income %>%
  pivot_longer(-c(religion, religionClass), names_to = "income", values_to = "count") %>%
  inner_join(columnMetadata, by = "income") %>%
  mutate(income = ordered(income, levels = columnMetadata$income))
## # A tibble: 180 x 5
##    religion religionClass income             count incomeGroup       
##    <chr>    <chr>         <ord>              <dbl> <chr>             
##  1 Agnostic C             <$10k                 27 low               
##  2 Agnostic C             $10-20k               34 low               
##  3 Agnostic C             $20-30k               60 low               
##  4 Agnostic C             $30-40k               81 medium            
##  5 Agnostic C             $40-50k               76 medium            
##  6 Agnostic C             $50-75k              137 medium            
##  7 Agnostic C             $75-100k             122 high              
##  8 Agnostic C             $100-150k            109 high              
##  9 Agnostic C             >150k                 84 high              
## 10 Agnostic C             Don't know/refused    96 Don't know/refused
## # ... with 170 more rows

Finally look at the mapping to ensure it worked.

relig_income %>%
  pivot_longer(-c(religion, religionClass), names_to = "income", values_to = "count") %>%
  inner_join(columnMetadata, by = "income") %>%
  mutate(income = ordered(income, levels = columnMetadata$income))
  select(income, incomeGroup) %>%
  table()
                    incomeGroup
income               Don't know/refused high low medium
  <$10k                               0    0  18      0
  $10-20k                             0    0  18      0
  $20-30k                             0    0  18      0
  $30-40k                             0    0   0     18
  $40-50k                             0    0   0     18
  $50-75k                             0    0   0     18
  $75-100k                            0   18   0      0
  $100-150k                           0   18   0      0
  >150k                               0   18   0      0
  Don't know/refused                 18    0   0      0

The metadata columns are now available

We can plot the data summarized by our arbitrary grouping of religions and colored by our grouped income levels. Order the income classes to make a sensible presentation

relig_income %>%
  pivot_longer(-c(religion, religionClass), names_to = "income", values_to = "count") %>%
  inner_join(columnMetadata, by = "income") %>%
  mutate(income = ordered(income, levels = columnMetadata$income)) %>%
  mutate(incomeGroup = ordered(incomeGroup, levels = c("low", "medium", "high", "Don't know/refused"))) %>%
  group_by(religionClass, income, incomeGroup) %>%
  summarize(meanCount = mean(count), .groups =  "drop_last") %>%
  ggplot(aes(x = income, y = meanCount, fill = incomeGroup)) +
  geom_col() +
  facet_wrap(vars(religionClass)) + 
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1))