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
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).