How to count the data and mutate a new column for it in R-CodePudding

For example my table is shown as below

Job	Gender
CEO	Male
Manager	Male
Manager	Female
Manager	Male
Supervisor	Female

Then I would like to organize it to something like below

Job	Male	Female
CEO	1	0
Manager	2	1
Supervisor	0	1

How can I make it right?

CodePudding user response：

Just pivot_wider() with values_fn = length:

library(tidyr)

df %>%
  pivot_wider(names_from = Gender, values_from = Gender, values_fn = length, values_fill = 0)

# # A tibble: 3 × 3
#   Job         Male Female
#   <chr>      <int>  <int>
# 1 CEO            1      0
# 2 Manager        2      1
# 3 Supervisor     0      1

CodePudding user response：

You need to group_by the Job column, then count the Gender in each Job. After that, transform the dataframe into a "wide" format by expanding the count result.

library(tidyverse)

df %>% 
  group_by(Job) %>% 
  count(Gender) %>% 
  pivot_wider(names_from = Gender, values_from = n, values_fill = 0) %>% 
  ungroup()

# A tibble: 3 × 3
  Job         Male Female
  <chr>      <int>  <int>
1 CEO            1      0
2 Manager        2      1
3 Supervisor     0      1

Or more simply, a single table function.

table(df$Job, df$Gender)

             Female Male
  CEO             0    1
  Manager         1    2
  Supervisor      1    0

CodePudding user response：

Another option using group_by with count and spread like this:

library(dplyr)
library(tidyr)
df %>%
  group_by(Job, Gender) %>%
  count() %>%
  spread(Gender, n, fill = 0)
#> # A tibble: 3 × 3
#>   Job        Female  Male
#>   <chr>       <dbl> <dbl>
#> 1 CEO             0     1
#> 2 Manager         1     2
#> 3 Supervisor      1     0

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

CodePudding user response：

One possible way to solve your problem:

xtabs(~ Job   Gender, data=df)

            Gender
Job          Female Male
  CEO             0    1
  Manager         1    2
  Supervisor      1    0