How can I group categorical variable that also meets another criteria in R? DPLYR?

Question

I have a task that I can't solve. My goal is to be able to figure out how many "families" have children (under 18). I only need the sum of unique familyids and I've tried doing it in R and Excel and can't figure it out.

In my data I have four families and my data is saved on a client level.

data <- data.frame(
  
  "FamilyID" = c(10,10,10,11,11,11,12,12,13,13),
  "ClientID" = c(101,102,103,111,112,113,121,122,131,132),
  "Age" = c(26,1,5,35,34,1,54,60,17,21)
)

My goal is to have something like this

Metric                             Count
Families w/ Children                3
Families w/out Children             1

In my actual dataset I have thousands of families so I really appreciate ant help. How can I do this with dplyr?

question from:https://stackoverflow.com/questions/65945197/how-can-i-group-categorical-variable-that-also-meets-another-criteria-in-r-dply

Answer 1

library(tidyverse)
counts <- data %>%
  group_by(FamilyID) %>%
  summarise(number_of_children = sum(Age<= 18), number_of_adults = sum(Age > 18)) %>%
  ungroup()

final <- counts %>%
  summarise("Families w/ children" = sum(number_of_children > 0), "Families w/o children" = sum(number_of_children < 1)) %>%
  gather() %>%
  rename("Metric" = key, "Count" = value)