Aggregating Data by County and Year in R

Question

I have a dataframe that looks like so:

year <- year
month <- c(1,1,1,1,1,2,2,2,3,3)
county <- c("Upshur", "Upshur", "Upshur", "Collin","Collin","Upshur","Upshur", "Collin","Upshur","Collin")
citation <- c(1,0,1,1,1,0,1,0,1,1)
df <- data.frame(year, month, county, citation)

#  year month county citation 
#1 2007     1 Upshur        1
#2 2007     1 Upshur        0
#3 2007     1 Upshur        1
#4 2007     1 Collin        1
#5 2007     1 Collin        1
#6 2007     2 Upshur        0
#7 2007     2 Upshur        1
#8 2007     2 Collin        0
#9 2007     3 Upshur        1
#102007     3 Collin        1
#...

Where I have individual incidents of police stops that state whether the stop ended in citation or ticket based on whether citation has a 1 or 0. What I am aiming to do is aggegate this data into a smaller dataframe where frequency of tickets and citations is stated for each county in each month. Basically, something that would look like so:

# year month county citations_given warnings_given
  2007     1 Upshur               2              1
  2007     1 Collin               2              0
  2007     2 Upshur               1              0
  2007     2 Collin               0              1
  2007     3 Upshur               1              0
  2007     3 Collin               1              0
...

Any suggestions would be appreciated!

score 2 · Answer 1 · answered Mar 04 '19 at 22:33

2

I recommend dplyr package for this.

library(dplyr)

df %>%
  group_by(year, month, county) %>%
  summarize(citation_given = sum(citation),
            warnings_given = sum(citation == 0))

answered Mar 04 '19 at 22:33

kwiscion

576
2
6

Thank you so much! This helped enormously! – M. Damon Mar 04 '19 at 22:42

Yolanda Pan · Answer 2 · 2019-03-08T18:30:08.273

1

I highly recommend using data.table package.

library(data.table)
setDT(df)
df[,list(warn_given = sum(citation==0),cite_given = sum(citation)), 
   by=c('year', 'month', 'county')]

edited Mar 08 '19 at 18:30

answered Mar 08 '19 at 18:24

Yolanda Pan

31
3

score 0 · Answer 3 · answered Mar 04 '19 at 22:43

Consider a multiple function in an aggregate call, using base package with clean up of column names:

agg_df <- setNames(do.call(data.frame,
                  aggregate(citation ~ year + month + county, df, function(x)
                              c(citations_given = sum(x),
                                warnings_given = sum(x==0))
                           )
                  ), c("year", "month", "county", "citations_given", "warnings_given")
          )

agg_df

#   year month county citations_given warnings_given
# 1 2007     1 Collin               2              0
# 2 2007     2 Collin               0              1
# 3 2007     3 Collin               1              0
# 4 2007     1 Upshur               2              1
# 5 2007     2 Upshur               1              1
# 6 2007     3 Upshur               1              0

Aggregating Data by County and Year in R

3 Answers3