How to stack all values in two column into a single column

January 26, 2022

I have a data set like this:

dt <- tibble(
  TRIAL = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
  GYF = c(3, 2, 3, 1, 6, 3, 2, 3, 1),
  AD = c(7.54, 1.564, 10, 0.89, 0, , , 1.5, 0)
)

# # A tibble: 9 x 3
#   TRIAL    GYF    AD
#   <chr> <dbl> <dbl>
# 1 A         3   7.54  
# 2 A         2   1.564
# 3 A         3   10  
# 4 B         1   0  .89
# 5 B         6   0  
# 6 B         3     
# 7 C         2     
# 8 C         3   1.5
# 9 C         1   0

This is what I want to achieve: I want corresponding values of GYF and AD in a new column (TraitValue) and another column (Trait) that will show the Variable name GYF and AD in order all grouped by trial value.

# # A tibble: 18 x 3
#   TRIAL Trait Traitvalue
#   <chr> <chr> <dbl> 
# 1  A      GYF   3
# 2  A      GYF   2
# 3  A      GYF   3
# 4  A      AD    7.54
# 5  A      AD    1.564
# 6  A      AD    10
# 7  B      GYF   1
# 8  B      GYF   6
# 9  B      GYF   3
# 10 B      AD    0.89
# 11 B      AD    0
# 12 B      AD    
# 13 C      GYF   2      
# 14 C      GYF   3
# 15 C      GYF   1
# 16 C      AD 
# 17 C      AD    1.5
# 18 C      AD    0

Thank you.

>Solution :

You can use pivot_longer from tidyr and dplyr pipe for renaming and ordering:

dt %>% pivot_longer(., c("GYF", "AD")) %>% 
  arrange(., TRIAL, name) %>% 
  rename(Traitvalue=value,
         Trait=name)

  TRIAL Trait Traitvalue
   <chr> <chr>      <dbl>
 1 A     AD          7.54
 2 A     AD          1.56
 3 A     AD         10   
 4 A     GYF         3   
 5 A     GYF         2   
 6 A     GYF         3   
 7 B     AD          0.89
 8 B     AD         NA   
 9 B     AD         NA   
10 B     GYF         1   
11 B     GYF         6   
12 B     GYF         3   
13 C     AD          1   
14 C     AD          1.5 
15 C     AD          0   
16 C     GYF         2   
17 C     GYF         3   
18 C     GYF         1