Difficulty with tables

[sdeepj]

I’m trying to group my table by one variable and then have it summarise a different column by counting the number of occurrences by variable

What I have

Year    Sex
2020    Female
2020    Female
2021    Male
2022    Male

What I want

Year     Female    Male
2020        2        0 
2021        0        1
2022        0        1

I tried using group_by(Year) %>% summarise(table(Sex)), however that resulted in one column for Sex.

[jrkrideau]

Will this work?

dat1 <- data.frame(
  stringsAsFactors = FALSE,
              Year = c(2020L, 2020L, 2021L, 2022L),
               Sex = c("Female", "Female", "Male", "Male")
        )

with(dat1, table(Year, Sex))

[strengejacke]

The datawizard package has a function for frequency and crosstables, data_tabulate(), which also allows to include missings, or calculate proportions etc.

dat1 <- data.frame(
  Year = c(2020L, 2020L, 2021L, 2022L),
  Sex = c("Female", "Female", "Male", "Male"),
  stringsAsFactors = FALSE
)

datawizard::data_tabulate(dat1, "Sex", by = "Year")
#> Sex    | 2020 | 2021 | 2022 | <NA> | Total
#> -------+------+------+------+------+------
#> Female |    2 |    0 |    0 |    0 |     2
#> Male   |    0 |    1 |    1 |    0 |     2
#> <NA>   |    0 |    0 |    0 |    0 |     0
#> -------+------+------+------+------+------
#> Total  |    2 |    1 |    1 |    0 |     4

datawizard::data_tabulate(dat1, "Year", by = "Sex")
#> Year  | Female | Male | <NA> | Total
#> ------+--------+------+------+------
#> 2020  |      2 |    0 |    0 |     2
#> 2021  |      0 |    1 |    0 |     1
#> 2022  |      0 |    1 |    0 |     1
#> <NA>  |      0 |    0 |    0 |     0
#> ------+--------+------+------+------
#> Total |      2 |    2 |    0 |     4

datawizard::data_tabulate(dat1, "Year", by = "Sex", include_na = FALSE)
#> Year  | Female | Male | Total
#> ------+--------+------+------
#> 2020  |      2 |    0 |     2
#> 2021  |      0 |    1 |     1
#> 2022  |      0 |    1 |     1
#> ------+--------+------+------
#> Total |      2 |    2 |     4

^{Created on 2024-05-02 with reprex v2.1.0}

[sdeepj]

Thanks, that was helpful