Using tidyr, when spread values get NA

Question

I have a problem, I have used tidyr to try to convert data, both from long to wide and wide to long. All functions as gather, unite works fine, but when I apply spread I get the right format but a big proportions of the values turn to NA. I cant upload this data. Do yu have ideas about why it turn out like this and what I can do about it?

Answer 1

A common mistake (that I also make) is that, when applying the function spread there is still a column left in the data that is not identical for each intended row.

An example:

data

df <- data.frame(id = rep(1:5,2),
                 g = rep(1:2,5),
                 gname = rep(letters[1:2],5),
                 x1 = letters[1:10],
                 x2 = letters[11:20])

code

df %>%
  gather(key,value,-id,-g,-gname) %>%
  unite(dummy,key,g) %>%
  spread(dummy,value)

result

# A tibble: 10 x 6
      id gname x1_1  x1_2  x2_1  x2_2 
   <int> <fct> <chr> <chr> <chr> <chr>
 1     1 a     a     NA    k     NA   
 2     1 b     NA    f     NA    p    
 3     2 a     g     NA    q     NA   
 4     2 b     NA    b     NA    l    
 5     3 a     c     NA    m     NA   
 6     3 b     NA    h     NA    r    
 7     4 a     i     NA    s     NA   
 8     4 b     NA    d     NA    n    
 9     5 a     e     NA    o     NA   
10     5 b     NA    j     NA    t    

because gname is left in the data before using spread, things go wrong.

so the following:

df %>%
  gather(key,value,-id,-g,-gname) %>%
  unite(dummy,key,g) %>%
  select(-gname) %>%
  spread(dummy,value)

gives the correct result:

  id x1_1 x1_2 x2_1 x2_2
1  1    a    f    k    p
2  2    g    b    q    l
3  3    c    h    m    r
4  4    i    d    s    n
5  5    e    j    o    t