GDELT and ICEWS counts of daily protest events in Turkey from 15 May to 15 June 2013.

turkey-daily-protests.R

# Libraries
library(RMySQL)

# Connect to server
conn <- dbConnect(MySQL(), user="ab428", password="", 
                  dbname="event_data", host="")

country <- "Turkey"
start.date <- "2013-05-15"
end.date <- "2013-06-15"

##
##    Check ICEWS counts
##

# Get event counts
sql <- paste0(
"SELECT event_date AS date, count(*) AS icews
FROM events
WHERE location_id IN 
  (SELECT location_id FROM locations WHERE country_id =
    (SELECT id FROM countries WHERE countryname = '", country, "'))
  AND (eventtype_id BETWEEN 224 AND 228
       OR eventtype_id BETWEEN 244 AND 248)
  AND event_date BETWEEN '", start.date, "' AND '", end.date, "'
GROUP BY event_date;")
icews <- dbGetQuery(conn, sql)
icews$date <- as.Date(icews$date)

##
##    Check GDELT counts
##

# Convert date to integer
start.int <- as.numeric(gsub("-", "", start.date))
end.int <- as.numeric(gsub("-", "", end.date))

# Get event counts
sql <- paste0(
"SELECT sqldate AS date, count(*) AS gdelt
FROM gdelt_dailyupdates
WHERE actiongeo_countrycode = 
  (SELECT fipscode FROM countries WHERE countryname = '", country, "')
  AND ( eventcode IN ('141', '1411', '1412', '1413', '1414')
     OR eventcode IN ('145', '1451', '1452', '1453', '1454') )
  AND sqldate BETWEEN '", start.int, "' AND '", end.int, "'
GROUP BY sqldate
ORDER BY sqldate;")
gdelt <- dbGetQuery(conn, sql)
gdelt$date <- as.Date(as.character(gdelt$date), format="%Y%m%d")


##
##    Combine results
##

res <- data.frame(
  date = seq.Date(as.Date(start.date), as.Date(end.date), "day")
  )
res <- merge(res, icews, by="date", all.x=T)
res <- merge(res, gdelt, by="date", all.x=T)
res[is.na(res$icews), "icews"] <- 0
res[is.na(res$gdelt), "gdelt"] <- 0

dput(res)

##
##    Done, close connection
##

dbDisconnect(conn)