from IPython.core.display import HTML
styles = open("Style.css").read()
HTML(styles)

import pandas as pd
import numpy as np

from mpl_toolkits.basemap import Basemap

%load_ext sql

%sql mysql://root:mysqlpassword@localhost:3306/gdelt

qry = '''select * from Events 
         where 
         ((substring(Actor1Code, 1, 3)="IDN" or substring(Actor2Code, 1, 3)="IDN") 
         and substring(Day, 1, 4)="2012")'''

res = %sql $qry

len(res)

indn = pd.DataFrame(res, columns=res.keys)

# automatic datatype conversion stopped working somehow
float_cols = ['Actor1Geo_Lat', 'Actor1Geo_Long',
              'Actor2Geo_Lat', 'Actor2Geo_Long',
              'ActionGeo_Lat', 'ActionGeo_Long',
              'GoldsteinScale']

indn.QuadCategory = indn.QuadCategory.astype(int)
indn[float_cols] = indn[float_cols].astype(float)

# those starting verbal and material conflict with indonesia
to_indn = indn[(indn.Actor2Code.apply(lambda x: x[:3])=='IDN') & 
               ((indn.QuadCategory==3) | (indn.QuadCategory==4))]

to_indn.iloc[:5,:6]

pivot = pd.pivot_table(to_indn, values='Day', rows=['Actor1Geo_Lat', 'Actor1Geo_Long'],
                       cols='QuadCategory', aggfunc=len)

pivot = pivot.fillna(0)
pivot = pivot.reset_index()
pivot.head(10)

top_100_1 = pivot.sort(columns=[3], ascending=False)[1:101]
top_100_2 = pivot.sort(columns=[4], ascending=False)[1:101]

top_100_2[:10]

top_100_1 = top_100_1.rename(columns={3: 'Verbal Conflict',
                                      4: 'Material Conflict'})

top_100_2 = top_100_2.rename(columns={3: 'Verbal Conflict',
                                      4: 'Material Conflict'})

figure(figsize=(15,15))

# create the map object
id_map = Basemap()

# draw important features
id_map.drawcoastlines(linewidth=.3) 
id_map.drawcountries(linewidth=.2)
id_map.fillcontinents(color='0.8') # Light gray
id_map.drawmapboundary()

# transform from (lat, lon) to (x, y) 
x1, y1 = id_map(top_100_1['Actor1Geo_Long'].values, top_100_1['Actor1Geo_Lat'].values)
x2, y2 = id_map(top_100_2['Actor1Geo_Long'].values, top_100_2['Actor1Geo_Lat'].values)

# plot material conflict locations
for i in xrange(len(x2)):
    id_map.plot(x2[i], y2[i], 'or', 
                markersize=top_100_2['Material Conflict'].values[i] / 2, 
                alpha=.3) 

# plot verbal conflict locations
for i in xrange(len(x1)):
    id_map.plot(x1[i], y1[i], 'oy', 
                markersize=top_100_1['Verbal Conflict'].values[i] / 2, 
                alpha=.8)

figure(figsize=(10,10))

# çreate the map object with boundaries
id_map = Basemap(llcrnrlon=30, llcrnrlat=-40, # lower left corner
                 urcrnrlon=155, urcrnrlat=50) # upper right corner

# draw important features
id_map.drawcoastlines(linewidth=.3) 
id_map.drawcountries(linewidth=.2)
id_map.fillcontinents(color='0.8') # Light gray
id_map.drawmapboundary()

x1, y1 = id_map(top_100_1['Actor1Geo_Long'].values, top_100_1['Actor1Geo_Lat'].values)
x2, y2 = id_map(top_100_2['Actor1Geo_Long'].values, top_100_2['Actor1Geo_Lat'].values)

# plot material conflict locations
for i in xrange(len(x2)):
    id_map.plot(x2[i], y2[i], 'or', 
                markersize=top_100_2['Material Conflict'].values[i] / 2, 
                alpha=.3) 
    
# plot verbal conflict locations
for i in xrange(len(x1)):
    id_map.plot(x1[i], y1[i], 'oy', 
                markersize=top_100_1['Verbal Conflict'].values[i] / 2, 
                alpha=.8)