Transpose a group of columns by time and client ID

Question

I have a dataset as shown below

time    customerID  Material
20170101    1   a
20170101    1   b
20170102    1   d
20170102    1   e
20170102    1   f
20170101    2   s
20170102    2   a
20170102    2   c

And I want to convert something like:

time    customerID  Material.1  Material.2  Material.2
20170101    1   a   b   
20170102    1   d   e   f
20170101    2   s       
20170102    2   a   c

To create a Sample table, run it in R:

time <- c(20170101, 20170101, 20170102, 20170102, 20170102, 20170101, 20170102, 20170102)
customerID <- c(1,1,1,1,1,2,2,2)
Material <- c('a','b','d','e','f','s','a','c')
df <- data.frame(time, customerID, Material)

I tried Reshape, but it did not work as I expected. Any pointers to this would be much appreciated.

Answer 1

Try the following:

library(tidyr)
df %>% spread(Material, Material)

Conclusion:

      time customerID    a    b    c    d    e    f    s
1 20170101          1    a    b <NA> <NA> <NA> <NA> <NA>
2 20170101          2 <NA> <NA> <NA> <NA> <NA> <NA>    s
3 20170102          1 <NA> <NA> <NA>    d    e    f <NA>
4 20170102          2    a <NA>    c <NA> <NA> <NA> <NA>

Answer 2

Using dplyrand tidyr::spread:

library(dplyr)
library(tidyr)
df %>% 
  group_by(time, customerID) %>% 
  mutate(grp_id = paste0("Material.", row_number())) %>% 
  spread(grp_id, Material, fill = "") %>% 
  arrange(customerID)

#> # A tibble: 4 x 5
#> # Groups:   time, customerID [4]
#>       time customerID Material.1 Material.2 Material.3
#>      <int>      <int>      <chr>      <chr>      <chr>
#> 1 20170101          1          a          b           
#> 2 20170102          1          d          e          f
#> 3 20170101          2          s                      
#> 4 20170102          2          a          c