R count by group-CodePudding

I am trying to count the observations for combinations of groups for a dataset like this:

  id Gender Breakfast Lunch Dinner
1  1      M       Yes   Yes    Yes
2  2      F        No   Yes    Yes
3  3      M       Yes    No    Yes
4  4      M       Yes   Yes    Yes
5  5      F       Yes   Yes    Yes
6  6      F        No    No    Yes
7  7      M       Yes   Yes     No
8  8      F       Yes   Yes    Yes

I am hoping to get a count output like this:

        Meal Eat Gender Count
1  Breakfast Yes      M    NA
2  Breakfast Yes      F    NA
3  Breakfast  No      M    NA
4  Breakfast  No      F    NA
5      Lunch Yes      M    NA
6      Lunch Yes      F    NA
7      Lunch  No      M    NA
8      Lunch  No      F    NA
9     Dinner Yes      M    NA
10    Dinner Yes      F    NA
11    Dinner  No      M    NA
12    Dinner  No      F    NA
>

I have used group_by and count functions before but it seems like I might need to do some wrangling before I get to those. What would be the best way to get the counts I am looking for?

CodePudding user response：

You may try

library(reshape2)
library(dplyr)
df %>%
  melt(id = c("id", "Gender"), variable.name = "meal", value.name = "eat") %>%
  group_by(Gender, meal, eat) %>%
  summarise(count = n())

   Gender meal      eat   count
   <chr>  <fct>     <chr> <int>
 1 F      Breakfast No        2
 2 F      Breakfast Yes       2
 3 F      Lunch     No        1
 4 F      Lunch     Yes       3
 5 F      Dinner    Yes       4
 6 M      Breakfast Yes       4
 7 M      Lunch     No        1
 8 M      Lunch     Yes       3
 9 M      Dinner    No        1
10 M      Dinner    Yes       3

df %>%
  pivot_longer(cols = c(Breakfast, Lunch, Dinner), names_to = "meal", values_to = "eat") %>%
  group_by(Gender, meal, eat) %>%
  summarise(count = n())

CodePudding user response：

library(tidyverse)

df <- read_table("id Gender Breakfast Lunch Dinner
  1      M       Yes   Yes    Yes
  2      F        No   Yes    Yes
  3      M       Yes    No    Yes
  4      M       Yes   Yes    Yes
  5      F       Yes   Yes    Yes
  6      F        No    No    Yes
  7      M       Yes   Yes     No
  8      F       Yes   Yes    Yes") 

df %>% 
  pivot_longer(c(3:5), 
               names_to = "meal", 
               values_to = "eat") %>% 
  count(meal, Gender, eat) %>% 
  arrange(meal, Gender, eat)
#> # A tibble: 10 x 4
#>    meal      Gender eat       n
#>    <chr>     <chr>  <chr> <int>
#>  1 Breakfast F      No        2
#>  2 Breakfast F      Yes       2
#>  3 Breakfast M      Yes       4
#>  4 Dinner    F      Yes       4
#>  5 Dinner    M      No        1
#>  6 Dinner    M      Yes       3
#>  7 Lunch     F      No        1
#>  8 Lunch     F      Yes       3
#>  9 Lunch     M      No        1
#> 10 Lunch     M      Yes       3

^{Created on 2022-06-29 by the reprex package (v2.0.1)}