dplyr::spread avoid NA results from common rows

Question

I would like to reshape the below data.

# A tibble: 184 x 5
# Groups:   variable [46]
 
   variable         WAVELENGTH_DIVISION X
   <chr>            <fct>               <dbl>
 1 A                BLUE                0.0762
 2 A                GREEN               0.137 
 3 A                YELLOW              0.354 
 4 A                RED                 0.433 
 5 B                BLUE                0.0712
 6 B                GREEN               0.175 
 7 B                YELLOW              0.360 
 8 B                RED                 0.394 
 9 C                BLUE                0.125 
10 C                GREEN               0.143
...

I tried using mytable %>% spread(WAVELENGTH_DIVISION, X) from which I obtained the below.

   variable           BLUE   GREEN  YELLOW RED
   <chr>              <dbl>  <dbl>  <dbl>  <dbl>
 1 A                  0.0762 NA     NA     NA    
 2 A                  NA     0.137  NA     NA    
 3 A                  NA     NA     0.354  NA    
 4 A                  NA     NA     NA     0.433
 5 B                  0.0712 NA     NA     NA    
 6 B                  NA     0.175  NA     NA    
 7 B                  NA      NA    0.360  NA    
 8 B                  NA      NA    NA     0.394
 9 C                  0.125   NA    NA     NA    
10 C                  NA      0.143 NA     NA
...

But I am trying to achieve a table without the NAs, what should I do to achieve the expected table shown here.

   variable           BLUE   GREEN  YELLOW RED
   <chr>              <dbl>  <dbl>  <dbl>  <dbl>
 1 A                  0.0762 0.137  0.354  0.433  
 2 B                  0.0712 0.175  0.360  0.394
 3 C                  0.125  0.143  ...
 ...

`spread` has been replaced by `pivot_wider` now. When I copy the data you have shown and use `mytable %>% spread(WAVELENGTH_DIVISION, X)` it gives the expected output. Can you add your data using `dput` ? — Ronak Shah, Jul 05 '20 at 12:07

score 0 · Accepted Answer · answered Jul 05 '20 at 12:09

0

You can use pivot_wider. However, your code works for me.

df %>%
      pivot_wider(names_from = WAVELENGTH_DIVISION, values_from = X)

# variable   BLUE GREEN YELLOW    RED
# <chr>     <dbl> <dbl>  <dbl>  <dbl>
# 1 A        0.0762 0.137  0.354  0.433
# 2 B        0.0712 0.175  0.36   0.394
# 3 C        0.125  0.143 NA     NA

answered Jul 05 '20 at 12:09

AlexB

3,061
2
17
19

Your right, I do get the expected answer if I drop two columns I have not included here as I assumed they were irrelevant. Dropping the excess columns gave the expected result. – Ivor Denham-Dyson Jul 05 '20 at 12:18

dplyr::spread avoid NA results from common rows

1 Answers1