Converting to wide format from long with multiple id and value columns

Question

I am stuck trying to convert from wide to long format with multiple ID and value columns. I'd prefer a tidyr solution as dcast as been defaulting to length.

Here's what I've tried so far:

df_wide <- df %>%
    melt(id.vars = c(Route, Address, Week)) %>%
    dcast(Route + Address ~ variable + Week)

Data:

df <- read.table(text = "
    Route    Week    Address    V1    V2    V3    V4    V5
    A    Week1    12345_SE_Court    0    1    0    0    0
    A    Week2    12345_SE_Court    0    0    1    1    1
    B    Week1    98765_NW_Drive    1    1    0    0    1
    B    Week2    98765_NW_Drive    0    1    0    1    0
    C    Week1    10293_SW_Road     0    0    0    0    1
    C    Week2    10293_SW_Road     1    0    0    0    1
    A    Week1    33333_NE_Street   0    1    1    0    0
    A    Week2    33333_NE_Street   1    0    1    0    0"
    , header = TRUE)

Desired output:

Route    Address    V1.Week1    V2.Week1    V3.Week1    V4.Week1    V5.Week1    V1.Week1    V2.Week2    V3.Week2    V4.Week2    V5.Week2
A    12345_SE_Court    0           1           0          0            0           0           0           1           1           1
A    33333_NE_Street   0           1           1          0            1           0           1           0           0           0
B    98765_NW_Drive    1           1           0          0            1           0           1           0           1           0              
C    10293_SW_Road     0           0           0          0            1           1           0           0           0           1

score 2 · Accepted Answer · answered Jun 27 '18 at 05:37

Here's the way to do this using tidyr. The trick is that you need to do a gather first:

library(tidyr)
df_wide <- df %>%
  gather(key, value, V1:V5) %>%
  unite("key", key, Week, sep = ".") %>%
  spread(key, value)

df_wide
#>   Route         Address V1.Week1 V1.Week2 V2.Week1 V2.Week2 V3.Week1
#> 1     A  12345_SE_Court        0        0        1        0        0
#> 2     A 33333_NE_Street        0        1        1        0        1
#> 3     B  98765_NW_Drive        1        0        1        1        0
#> 4     C   10293_SW_Road        0        1        0        0        0
#>   V3.Week2 V4.Week1 V4.Week2 V5.Week1 V5.Week2
#> 1        1        0        1        0        1
#> 2        1        0        0        0        0
#> 3        0        0        1        1        0
#> 4        0        0        0        1        1

Created on 2018-06-27 by the reprex package (v0.2.0).

Converting to wide format from long with multiple id and value columns

1 Answers1