Get CIDNE/GDELT/ICEWS series in the Ground Truth comparison blog post in order to calculate correlations. This is based on Ben's R code for the plots. There are some small differences because I am subsetting the events in SQL and using the data fields, not with grep of actual event type values.

gt-post-correlations.R

## Correlations for blog post

library(plyr)

# from GT
# source directories and first few lines of pull-data.R

sql <- 
  "SELECT dateoccured AS date, count(*) AS sigacts
FROM sigacts
WHERE dateoccured >= '2009-01-01' 
AND dateoccured <= '2010-03-31'
AND title IN ('Explosive Hazard')
GROUP BY dateoccured 
ORDER BY dateoccured;"
sigacts.expl <- dbGetQuery(conn, sql)

sql <-
  "SELECT sqldate AS date, count(*) AS gdelt
FROM gdeltafg
WHERE sqldate >= '2009-01-01'
AND sqldate <= '2010-03-31'
AND eventcode IN ('183', '1831', '1832', '1833')
GROUP BY sqldate
ORDER BY sqldate"
gdelt.expl <- dbGetQuery(conn, sql)

sql <-
  "SELECT event_date AS date, count(*) AS icews
FROM events
WHERE location_id IN
(SELECT location_id FROM locations WHERE country_id = 1)
AND event_date BETWEEN '2009-01-01' AND '2010-03-31'
AND eventtype_id IN
(SELECT eventtype_id FROM eventtypes 
WHERE code IN ('183', '1831', '1832', '1833'))
GROUP BY event_date
ORDER BY event_date;"
icews.expl <- dbGetQuery(mysql, sql)

afg.expl <- data.frame(
  date=seq.Date(as.Date("2009-01-01"), as.Date("2010-03-31"), by="day"))
afg.expl <- join(afg.expl, sigacts.expl, by="date", type="left")
afg.expl <- join(afg.expl, gdelt.expl, by="date", type="left")
afg.expl <- join(afg.expl, icews.expl, by="date", type="left")

afg.expl[is.na(afg.expl)] <- 0

# Get correlations
with(afg.expl, cor(sigacts, gdelt))
with(afg.expl, cor(sigacts, icews))