-1

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")
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
Ümit Seven
  • 9
  • 4
  • What have you tried already, and which part are you having trouble with? The how many part? The august part? the yemen part? – Robin Gertenbach Mar 20 '21 at 18:43
  • @RobinGertenbach Thank you so much for your attention. I am a very beginner in R, so I first try to convert event_date variable to date because it is factor, and then try to count event types as per country for each month. But no avail.... All I want it to count monthly event type for each country! Much appreciated... – Ümit Seven Mar 20 '21 at 18:56
  • Can you post sample data in `dput` format? Please edit **the question** with the output of `dput(head(mydata, 20))`. – Rui Barradas Mar 20 '21 at 20:27
  • @RuiBarradas Many thanks Rui, ı have included the output of the dput for the sample data. Appreciate for that. – Ümit Seven Mar 21 '21 at 05:34
  • Thanks for the data but the output of `dput` doesn't seem to be well posted, can you repost, please? – Rui Barradas Mar 21 '21 at 07:12
  • @RuiBarradas appreciate for your time and support, but the data is too big – Ümit Seven Mar 21 '21 at 08:19
  • That's why I say `head(mydata, 20)`, only 20 rows of a 3 columns data.frame is not big. – Rui Barradas Mar 21 '21 at 18:04

1 Answers1

0

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)
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66