1

My data looks like this

mydf = data.frame(length=c(1,1,2,2,3), 
                  type=c("A","B","A","B","A"), 
                  val1=1:5, 
                  val2=6:10)


> mydf
length type val1 val2
     1    A    1    6
     1    B    2    7
     2    A    3    8
     2    B    4    9
     3    A    5   10

In this example there is no type "B" for length 3. That is typical: every length has at least one type but not always both. There are only the two types.

My objective is to put this data into a "wider" form by type so it looks like this, with zeros (or NAs) for the missing values:

  length  val1.A  val1.B  val2.A   val2.B
       1       1       2       6        7
       2       3       4       8        9
       3       5       0      10        0

I've been trying to do this with spread() but cannot get it to work--I thought I would be able to specify type and length as the key and the other columns as the values but that doesn't seem possible. Is there a tidyverse way to do this?

Thank you in advance!

Ben S.
  • 3,415
  • 7
  • 22
  • 43
  • 1
    Possible dupe [Convert data from long format to wide format with multiple measure columns](https://stackoverflow.com/questions/10589693/convert-data-from-long-format-to-wide-format-with-multiple-measure-columns) – markus Mar 17 '19 at 18:55

1 Answers1

2

We can do a gather on 'val1', 'val2' to convert the data into 'long' format, unite the 'key', 'type' columns to a single column and then spread to 'wide' format

library(tidyverse)
gather(mydf, key, val, val1:val2) %>% 
   unite(key, key, type, sep=".") %>% 
   spread(key, val, fill = 0)
akrun
  • 874,273
  • 37
  • 540
  • 662