Counting the rows in a data frame that satisfy some criteria and grouping them by the unique values in the first column of the data frame

Question

I have a data with househould id's, gender and age as follows:

mydata <- 

structure(list(ID_HH = c(1,1,1,1,2,2,3,3,3,4,5,5), 
                           GENDER = c(1,2,1,1,1,2,2,1,2,2,1,1), 
                           AGE = c(50,45,3,15,25,5,32,30,10,28,64,16)), 
                      .Names = c("ID", "GENDER", "AGE"), 
                      class = "data.frame", row.names = c(NA, -12L))

   mydata

#  HH_ID GENDER AGE
# 1  1    1    50
# 2  1    2    45
# 3  1    1    3
# 4  1    1    15
# 5  2    1    25
# 6  2    2    5
# 7  3    2    32
# 8  3    1    30
# 9  3    2    10
# 10 4    2    28
# 11 5    1    64
# 12 5    1    16

I have another dataframe, lets call it 'output', that has only the unique HH_ID values and some other columns next to it. What i would like to do is to add new columns to this data frame that shows:

• "the number of adult females (Gender=2 && Age=18)",
• "the number of adult males (Gender=1 && Age=18)",
• "the number of school children (6-18)" (Num_Sch), and
• "the number of preschpol children (0-6)"(Num_PreSch)

for each household. So 'output' should look like that:

    #  HH_ID Col1 Col2 ... Num_Fem Num_Male Num_PreSch Num_Sch
# 1  1    ..              1       1         1        1 
# 2  2    ..              0       1         1        0 
# 3  3    ..              1       1         0        1
# 4  4    ..              1       0         0        0
# 5  5    ..              0       1         0        1

I tried many different functions and packages but nothing could achieve exactly what I want. I would appreciate any help or comment.

Answer 1

You're already thinking about this in a way that translates well to logical statements (e.g. is this person female and 18 or over), so I'd do it with a series of logical vectors, utilizing the fact that because true/false translates to 1/0, you can sum them.

Set up the different categories and create logical columns for each.

library(tidyverse)

mydata %>%
  mutate(adult_female = (GENDER == 2 & AGE >= 18),
         adult_male = (GENDER == 1 & AGE >= 18),
         school = between(AGE, 6, 18),
         preschool = between(AGE, 0, 6))
#>    ID GENDER AGE adult_female adult_male school preschool
#> 1   1      1  50        FALSE       TRUE  FALSE     FALSE
#> 2   1      2  45         TRUE      FALSE  FALSE     FALSE
#> 3   1      1   3        FALSE      FALSE  FALSE      TRUE
#> 4   1      1  15        FALSE      FALSE   TRUE     FALSE
#> 5   2      1  25        FALSE       TRUE  FALSE     FALSE
#> 6   2      2   5        FALSE      FALSE  FALSE      TRUE
#> 7   3      2  32         TRUE      FALSE  FALSE     FALSE
#> 8   3      1  30        FALSE       TRUE  FALSE     FALSE
#> 9   3      2  10        FALSE      FALSE   TRUE     FALSE
#> 10  4      2  28         TRUE      FALSE  FALSE     FALSE
#> 11  5      1  64        FALSE       TRUE  FALSE     FALSE
#> 12  5      1  16        FALSE      FALSE   TRUE     FALSE

Then you can group by household and sum all the columns of the type logical.

mydata %>%
  mutate(adult_female = (GENDER == 2 & AGE >= 18),
         adult_male = (GENDER == 1 & AGE >= 18),
         school = between(AGE, 6, 18),
         preschool = between(AGE, 0, 6)) %>%
  group_by(ID) %>%
  summarise_if(is.logical, sum)
#> # A tibble: 5 x 5
#>      ID adult_female adult_male school preschool
#>   <dbl>        <int>      <int>  <int>     <int>
#> 1     1            1          1      1         1
#> 2     2            0          1      0         1
#> 3     3            1          1      1         0
#> 4     4            1          0      0         0
#> 5     5            0          1      1         0

One issue that I'll let you handle: the function between is inclusive of its endpoints. You've described preschool as ages 0 to 6, and school-aged as ages 6 to 18. That means 6 year olds are counted in both. You probably want to adjust those endpoints, which shouldn't be too hard since it seems you're working with age as an integer.

Answer 2

There could be a fancy way to do that, but you can simply do it using a for loop, as follows:

mydata  <- as.data.frame(mydata)
Num_Fem <- Num_Male <- Num_PreSch <- Num_Sch <- c()

for(ID_HH in output$ID_HH){
  curr_HH    <- mydata[mydata$ID_HH == ID_HH,]

  Num_Fem    <- c(Num_Fem,    nrow(curr_HH[curr_HH$GENDER==2 & curr_HH$AGE>=18,]))
  Num_Male   <- c(Num_Male,   nrow(curr_HH[curr_HH$GENDER==1 & curr_HH$AGE>=18,]))
  Num_PreSch <- c(Num_PreSch, nrow(curr_HH[curr_HH$AGE<6,]))
  Num_Sch    <- c(Num_Sch,    nrow(curr_HH[curr_HH$AGE>=6 & curr_HH$AGE<18,]))
}

output <- cbind(output, data.frame(Num_Fem, Num_Male, Num_PreSch, Num_Sch))

It will give you your expected results:

    #  HH_ID Col1 Col2 ... Num_Fem Num_Male Num_PreSch Num_Sch
# 1        1   ..   ..           1        1         1        1 
# 2        2   ..   ..           0        1         1        0 
# 3        3   ..   ..           1        1         0        1
# 4        4   ..   ..           1        0         0        0
# 5        5   ..   ..           0        1         0        1

Hope it helps.