join strings by group by in dplyr

Question

I have following dataframe in R

  Name      Weekday      Block     Count
  ABC_1       1           5B         12
  ABC_1       1           5B         12
  ABC_1       1           5C         10
  ABC_1       1           5B         10
  DER_1       2           5B         10 
  DER_1       2           5C         10 
  DER_1       2           5B         10
  DER_1       2           5C         10

I want following dataframe as a output

  Name      Weekday      Block           5B       5C     Cont            
  ABC_1       1           5B,5B,5C,5B    34       10     12,12,10,10
  DER_1       2           5B,5C,5B,5C    20       20     10,10,10,10

I am using following code to do it.

 df_new<- df %>% 
 group_by(Weekday,Name) %>% 
 mutate(yard_blocks = paste0(Block, collapse = ",")) %>% 
 as.data.frame()

But,it does not give me desired output

Answer 1

After grouping by 'Name', 'Weekday', and 'Block', get the frequency as a column ('n'), then by grouping with 'Name', 'Weekday', we mutate to paste the contents of 'Block' in a new column 'Block1', get the unique rows (distinct), and spread from 'long' to 'wide'

library(dplyr)
library(tidyr)
df %>%
  group_by(Name, Weekday, Block) %>%
  mutate(n = n()) %>%
  group_by(Name, Weekday) %>% 
  mutate(Block1 = toString(Block)) %>%
  distinct %>% 
  spread(Block, n) %>%
  rename(Block = Block1)
# A tibble: 2 x 5
# Groups: Name, Weekday [2]
#    Name  Weekday Block           `5B`  `5C`
#* <chr>   <int> <chr>          <int> <int>
#1 ABC_1       1 5B, 5B, 5C, 5B     3     1
#2 DER_1       2 5B, 5C, 5B, 5C     2     2

Update

Based on the updated dataset and question

df %>%
    group_by(Name, Weekday) %>%
    mutate(Block1 = toString(Block), Cont = toString(Count)) %>% 
    group_by(Block, add = TRUE) %>% 
    mutate(Count = sum(Count)) %>% 
    distinct  %>% 
    spread(Block, Count)
# A tibble: 2 x 6
# Groups: Name, Weekday [2]
#   Name  Weekday Block1         Cont            `5B`  `5C`
#*  <chr>   <int> <chr>          <chr>          <int> <int>
#1  ABC_1       1 5B, 5B, 5C, 5B 12, 12, 10, 10    34    10
#2  DER_1       2 5B, 5C, 5B, 5C 10, 10, 10, 10    20    20