Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save alfcrisci/b3c96ca59a65c9a06a302c99fcf090b8 to your computer and use it in GitHub Desktop.
Save alfcrisci/b3c96ca59a65c9a06a302c99fcf090b8 to your computer and use it in GitHub Desktop.
GDELT and ICEWS counts of daily protest events in Turkey from 15 May to 15 June 2013.
# 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment