Is there an R function in summarize() where I can count the amount of two variables coinciding with each other?

user_6247825 0
Viewed 33

I'm not sure how I should phrase this.

Let's say I have a data frame about juvenile arrests. One of the columns describes the statutes broken, and that's what I'm doing a group_by() for.

date       sex statute_description
2015-01-01 M   ROBBERY
2015-01-01 M   ROBBERY
2015-01-01 F   ROBBERY
2015-01-01 F   ASSAULT - SIMPLE
2015-01-01 F   ASSAULT - SIMPLE
2015-01-01 M   ASSAULT - SIMPLE
2015-01-01 M   DRUG POSSESSION
2015-01-01 M   ISSUANCE OF WARRANT
2015-01-01 M   ISSUANCE OF WARRANT
2015-01-01 M   ISSUANCE OF WARRANT

arrest_reasons <- group_by(df, statute_description) %>%
   summarize(
      num_arrests = n
   )

This would return:

statute_description  num_arrests
ROBBERY              3
DRUG POSSESSION      1
ASSAULT - SIMPLE     3
ISSUANCE OF WARRANT  3

What I want to do is add another column to the data frame, that counts how many members of each sex committed a particular crime. Like this:

statute_description  num_arrests  males  females
ROBBERY              3            2      1
DRUG POSSESSION      1            1      0
ASSAULT - SIMPLE     3            1      2
ISSUANCE OF WARRANT  3            3      0

I'm not sure what function would be suitable for this.

dplyr dataframe r
3 Answers

We can use sum on a logical expression

library(dplyr)
arrest_reasons <- group_by(df, statute_description) %>%
   summarize(
  num_arrests = n(), 
  males = sum(sex == 'M'), 
  females = num_arrests - males, .groups = 'drop')

arrest_reasons
# A tibble: 4 x 4
#  statute_description num_arrests males females
#  <chr>                     <int> <int>   <int>
#1 ASSAULT - SIMPLE              3     1       2
#2 DRUG POSSESSION               1     1       0
#3 ISSUANCE OF WARRANT           3     3       0
#4 ROBBERY                       3     2       1

data

df <- structure(list(date = c("2015-01-01", "2015-01-01", "2015-01-01", 
"2015-01-01", "2015-01-01", "2015-01-01", "2015-01-01", "2015-01-01", 
"2015-01-01", "2015-01-01"), sex = c("M", "M", "F", "F", "F", 
"M", "M", "M", "M", "M"), statute_description = c("ROBBERY", 
"ROBBERY", "ROBBERY", "ASSAULT - SIMPLE", "ASSAULT - SIMPLE", 
"ASSAULT - SIMPLE", "DRUG POSSESSION", "ISSUANCE OF WARRANT", 
"ISSUANCE OF WARRANT", "ISSUANCE OF WARRANT")),
class = "data.frame", row.names = c(NA, 
-10L))
user_37322710

A simple base R solution is to use table and addmargins.

addmargins(table(data$statute_description, data$sex), margin=2)
                      F M Sum
  ASSAULT-SIMPLE      2 1   3
  DRUG_POSSESSION     0 1   1
  ISSUANCE_OF_WARRANT 0 3   3
  ROBBERY             1 2   3
user_47526750

You could count, get data in wide format and add a total column.

df %>%
  dplyr::count(statute_description, sex) %>%
  tidyr::pivot_wider(names_from = sex, values_from = n, values_fill = 0) %>%
  janitor::adorn_totals(where = 'col', name = 'num_arrests')

# statute_description F M num_arrests
#    ASSAULT - SIMPLE 2 1           3
#     DRUG POSSESSION 0 1           1
# ISSUANCE OF WARRANT 0 3           3
#             ROBBERY 1 2           3
user_39629140
Related