How to use the summarise function to create a summary in R using dplyr package?

Question

I have the following table which represents a child, his siblings and the case they are assigned under. The resource ids represent the house where they were placed together.

child_id|sibling_id|case_id|resource_id|placed_together
    1       8         123      12856     Y
    1       9         123      12856     Y
    3      11         321      12555     N
    4      12         323      10987     N
    4      13         323      10956     N
    6      14         156      10554     N
    6      15         156      10554     N
   10      16         156      10553     N
   10      17         145      18986     Y
   10      18         145      18986     Y

I want to create a summary which shows the total count for those children that were placed together based on their case_ids and those who were not. So my result should look like this

Total Groups|sibling placed together|siblings not placed together
        5             2                   3      
   

Any help would be appreciated. I have tried to use the summarise function but that gives me a total of each case id seperately.

Answer 1

I'm inferring that your logic is "any "Y" in `placed_together", since id 10 has one "N" and two "Y" for sibling placement.

library(dplyr)
dat %>%
  group_by(child_id) %>%
  summarize(tog = "Y" %in% unique(placed_together)) %>%
  ungroup() %>%
  summarize(TotalGroups = n(), Together = sum(tog), NotTogether = sum(!tog))
# # A tibble: 1 x 3
#   TotalGroups Together NotTogether
#         <int>    <int>       <int>
# 1           5        2           3

---

Data

dat <- read.table(header=T, text="
child_id sibling_id case_id resource_id placed_together
    1       8         123      12856     Y
    1       9         123      12856     Y
    3      11         321      12555     N
    4      12         323      10987     N
    4      13         323      10956     N
    6      14         156      10554     N
    6      15         156      10554     N
   10      16         156      10553     N
   10      17         145      18986     Y
   10      18         145      18986     Y")