Search code examples
rlubridatecounting

The output of dput(head(data, 20)) in data frame in R

I have a dataset as below (name:data) that includes multiple countries with multiple event types in different dates in 3 columns and 251453 rows. I want to count monthly events for each country. For example, I want to see the number of "Battles" in "Yemen" in "August"? I have 6 different event types and 8 different countries in total.

Couldn't have any advance on it despite spending hours of it. Appreciate for any guidance.

|event_date|        |event_type|                |country|
12 March 2021   Explosions/Remote violence;     Yemen;
12 March 2021   Explosions/Remote violence      Yemen
12 March 2021   Battles                         Afghanistan;
12 March 2021   Battles                         Afghanistan
12 March 2021   Protests                        Yemen 
12 March 2021   Protests                        Yemen

The output of dput (sample)

dput(head(data, 20))
structure(list(event_date = structure(c(420L, 420L, 420L, 420L, 
420L, 420L, 420L, 420L, 420L, 420L, 420L, 420L, 420L, 420L, 420L, 
420L, 420L, 420L, 420L, 420L), .Label = c("01 April 2018", "01 April 2019", 
"01 April 2020", "01 August 2018", "01 August 2019", "01 August 2020", 
"01 December 2018", "01 December 2019", "01 December 2020", "01 February 2019", 
    event_type = structure(c(2L, 2L, 1L, 1L, 3L, 3L, 3L, 3L, 
    4L, 1L, 1L, 3L, 4L, 3L, 1L, 1L, 4L, 6L, 6L, 3L), .Label = c("Battles", 
    "Explosions/Remote violence", "Protests", "Riots", "Strategic developments", 
    "Violence against civilians"), class = "factor"), country = structure(c(8L, 
    8L, 1L, 1L, 8L, 8L, 3L, 5L, 8L, 8L, 8L, 5L, 5L, 5L, 1L, 1L, 
    5L, 8L, 8L, 4L), .Label = c("Afghanistan", "Colombia", "India", 
    "Iraq", "Lebanon", "Libya", "Mali", "Yemen"), class = "factor")), row.names = c(NA, 
20L), class = "data.frame")
Solution

  • This can be done with aggregate as long as the dates are actual dates.

    First, coerce the column event_date to class "Date".

    data$event_date <- as.Date(data$event_date, format = "%d %B %Y")

    Now, here are two ways, the first to count by month not considering the year and the second to count by year and month.

    month <- format(data$event_date, "%B")
aggregate(event_type ~ month + country, data, length)

yearmonth <- format(data$event_date, "%Y %B")
aggregate(event_type ~ yearmonth + country, data, length)