<jupyter_start><jupyter_text>Imports<jupyter_code>import pandas as pd
import numpy as np
pd.set_option('display.max_colwidth',500)

import matplotlib.pyplot as plt
import matplotlib as mpl
%matplotlib inline
plt.style.use('seaborn-white') # use plt.style.available to see list of available styles

import pickle
from datetime import datetime

from google.cloud import bigquery
import sys
sys.version
print("date ran:", datetime.today())
print("matplotlib version:", mpl.__version__)
print("pandas version:", pd.__version__)
project_id = 'spark-project-254623'
bucket_name = 'spark-projects'<jupyter_output><empty_output><jupyter_text>Provide credentials to the runtime<jupyter_code># authenticates Colab to the Google Cloud account
from google.colab import auth
auth.authenticate_user()
print('Authenticated')<jupyter_output>Authenticated<jupyter_text>BigQuery dry run Dry run query to check query size across different date ranges:<jupyter_code>client = bigquery.Client(project=project_id)

job_config = bigquery.QueryJobConfig()
job_config.dry_run = True
job_config.use_query_cache = False

sql =   '''
        SELECT *
        FROM `gdelt-bq.gdeltv2.events_partitioned`
        WHERE
            _PARTITIONDATE BETWEEN '2018-04-04' AND '2018-04-04'
        '''

query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query.
    location="US",
    job_config=job_config,
)  # API request

# A dry run query completes immediately.
assert query_job.state == "DONE"
assert query_job.dry_run

print("This query will process {:,} bytes.".format(query_job.total_bytes_processed))<jupyter_output>This query will process 144,510,139 bytes.<jupyter_text>Query Events Table<jupyter_code>client = bigquery.Client(project=project_id)

sql =   '''
        SELECT *
        FROM `gdelt-bq.gdeltv2.events_partitioned`
        WHERE
            _PARTITIONDATE BETWEEN '2019-12-01' AND '2019-12-07'
        '''

df = client.query(sql, location="US").to_dataframe()<jupyter_output><empty_output><jupyter_text>Save DataFrame to Pickle and then copy to existing Cloud Storage bucket<jupyter_code>df.to_pickle("./dec19_wk1_events.pkl")
# upload file to GCS bucket
!gsutil cp ./dec19_wk1_events.pkl gs://{bucket_name}/dec19_wk1_events.pkl<jupyter_output>Copying file://./dec19_wk1_events.pkl [Content-Type=application/octet-stream]...
/ [0 files][    0.0 B/689.8 MiB]                                                
==> NOTE: You are uploading one or more large file(s), which would run
significantly faster if you enable parallel composite uploads. This
feature can be enabled by editing the
"parallel_composite_upload_threshold" value in your .boto
configuration file. However, note that if you do this large files will
be uploaded as `composite objects
<https://cloud.google.com/storage/docs/composite-objects>`_,which
means that any user who downloads such objects will need to have a
compiled crcmod installed (see "gsutil help crcmod"). This is because
without a compiled crcmod, computing checksums on composite objects is
so slow that gsutil disables downloads of composite objects.

-
Operation completed over 1 objects/689.8 MiB.<jupyter_text>Copy Pickle file back to Colab instance<jupyter_code># run authentication before running this cell
!gsutil cp gs://{bucket_name}/dec19_wk1_events.pkl ./dec19_wk1_events.pkl

df = pd.read_pickle("./dec19_wk1_events.pkl")<jupyter_output>Copying gs://spark-projects/dec19_wk1_events.pkl...
\ [1 files][689.8 MiB/689.8 MiB]   55.8 MiB/s                                   
Operation completed over 1 objects/689.8 MiB.<jupyter_text>Exploratory Analysis of Queried Events  Dataset Properties<jupyter_code>print(df.shape)
df.columns
df.info()
#CHECK FOR PRESENCE OF MISSING VALUES
miss_vls = pd.DataFrame(df.isnull().sum(axis=0).sort_values(ascending=False), columns=['Count'])
miss_vls['Percent'] = miss_vls['Count'].apply(lambda x: '{:.2f}%'.format((float(x) / df.shape[0]) * 100))
miss_vls[miss_vls.Count > 0]<jupyter_output><empty_output><jupyter_text>Actor and Event Codes First, we see that Actor1Code is not always recorded (missing on about 10% of events).  Let's check if there is any correlation between EventCode and whether Actor1Code is present or null. Let's import the Event Descriptions lookup table.<jupyter_code>event_lt = pd.read_csv("https://gdeltproject.org/data/lookups/CAMEO.eventcodes.txt", sep='\t', dtype={'CAMEOEVENTCODE':str})
event_lt.set_index('CAMEOEVENTCODE',inplace=True)
event_lt.head()
# tabulate number of unique EventCode's in cases where Actor1Code is null
df[df.Actor1Code.isnull()]['EventCode'].unique().shape
# plot frequencies of EventCode values for events when Actor1Code is null
plt.figure(figsize=(30,5))
df[df.Actor1Code.isnull()]['EventCode'].value_counts().plot(kind="bar")
plt.show()
# display top 10 most frequent event codes when Actor1Code is null
event_code_cts = df[df.Actor1Code.isnull()]['EventCode'].value_counts().nlargest(10)
event_lt.merge(event_code_cts, left_index=True, right_index=True, how='right').rename({'EventCode':'N_EVENTS'},axis=1)
# plot frequencies of EventCode values for events when Actor1Code is NOT null
plt.figure(figsize=(30,5))
df[df.Actor1Code.notnull()]['EventCode'].value_counts().plot(kind="bar")
plt.show()
# display top 10 most frequent event codes when Actor1Code is NOT null
event_code_cts = df[df.Actor1Code.notnull()]['EventCode'].value_counts().nlargest(10)
event_lt.merge(event_code_cts, left_index=True, right_index=True, how='right').rename({'EventCode':'N_EVENTS'},axis=1)
#get counts of '010' code in the EventCode column, grouped by whether Actor1Code is null or not null
print("count of 010 code when Actor1Code is null:", int(df[(df.Actor1Code.isnull()) & (df.EventCode == '010')]['EventCode'].value_counts().values))
print("count of 010 code when Actor1Code is not null:", int(df[(df.Actor1Code.notnull()) & (df.EventCode == '010')]['EventCode'].value_counts().values))<jupyter_output>count of 010 code when Actor1Code is null: 1485
count of 010 code when Actor1Code is not null: 112387<jupyter_text>010 Code appears quite frequently when Actor1Code is NOT null, but rarely when it IS null.  Per https://www.gdeltproject.org/data/lookups/CAMEO.eventcodes.txt, 010 is "Make statement, unspecified".  So, there is this one noticeable difference, but many codes still appear in large numbers regardless of Actor1Code being present. Let's look into Actor2Code.  Maybe it can help us better understand the relationship between EventCode and presence of one or both of the ActorCode's.<jupyter_code># Check for overlaps between presence of Actor1Code and Actor2Code
print("Actor1Code NULL, Actor2Code NOT NULL:", df[df.Actor1Code.isnull() & df.Actor2Code.notnull()].shape[0], f"{df[df.Actor1Code.isnull() & df.Actor2Code.notnull()].shape[0] / df.shape[0] :.2%}")
print("Actor1Code NULL, Actor2Code NULL:", df[df.Actor1Code.isnull() & df.Actor2Code.isnull()].shape[0], f"{df[df.Actor1Code.isnull() & df.Actor2Code.isnull()].shape[0] / df.shape[0] :.2%}")
print("Actor1Code NOT NULL, Actor2Code NOT NULL:", df[df.Actor1Code.notnull() & df.Actor2Code.notnull()].shape[0], f"{df[df.Actor1Code.notnull() & df.Actor2Code.notnull()].shape[0] / df.shape[0] :.2%}")
print("Actor1Code NOT NULL, Actor2Code NULL:", df[df.Actor1Code.notnull() & df.Actor2Code.isnull()].shape[0], f"{df[df.Actor1Code.notnull() & df.Actor2Code.isnull()].shape[0] / df.shape[0] :.2%}")<jupyter_output>Actor1Code NULL, Actor2Code NOT NULL: 155514 10.39%
Actor1Code NULL, Actor2Code NULL: 2 0.00%
Actor1Code NOT NULL, Actor2Code NOT NULL: 877322 58.62%
Actor1Code NOT NULL, Actor2Code NULL: 463713 30.99%<jupyter_text>We see that when Actor1Code is null, Actor2Code is almost always not null.  So at least we can get one actor in those cases.When Actor1Code is not null, Actor2Code is present only about 60% of the time.It will need to be determined what it means for one of the ActorCode fields to be present and for the second to be null. Let's check if there is a noticeable difference in EventCode values when Actor2Code is null versus not null, with Actor1Code present.<jupyter_code># plot frequencies of EventCode values for events when Actor1Code is NOT null and Actor2Code IS null
plt.figure(figsize=(30,5))
df[df.Actor1Code.notnull() & df.Actor2Code.isnull()]['EventCode'].value_counts().plot(kind="bar")
plt.show()
# plot frequencies of EventCode values for events when Actor1Code is NOT null and Actor2Code is NOT null
plt.figure(figsize=(30,5))
df[df.Actor1Code.notnull() & df.Actor2Code.notnull()]['EventCode'].value_counts().plot(kind="bar")
plt.show()
# let's plot the counts side by side for each EventCode (just the top 10 for case when Actor2Code is present and when it is null)
# first, subset the dataframe to rows where Actor1Code is not null
temp_df = df[df.Actor1Code.notnull()][['EventCode','Actor2Code']]

# create boolean column indicating whether Actor2Code is null or not null
temp_df['Actor2CodeIsNull'] = temp_df['Actor2Code'].apply(lambda x: x is None)

# create column that includes counts of each EventCode value, grouped by Actor2CodeIsNull, in the corresponding rows
temp_df['Count'] = temp_df.groupby(['EventCode','Actor2CodeIsNull']).transform(len)

# create column ranking count values within each Actor2CodeIsNull category (largest counts receive smallest(top) rank values)
temp_df['Rank'] = temp_df.groupby('Actor2CodeIsNull')['Count'].rank(ascending=False, method='min')

# subset dataframe to rows where Rank value is at or below the 10th smallest Rank value in Actor2CodeIsNull group
cond_null = (temp_df.Actor2CodeIsNull == 1) & (temp_df.Rank <= np.sort(temp_df[temp_df.Actor2CodeIsNull == 1]['Rank'].unique())[9])
cond_notnull = (temp_df.Actor2CodeIsNull == 0) & (temp_df.Rank <= np.sort(temp_df[temp_df.Actor2CodeIsNull == 0]['Rank'].unique())[9])

# verify the results
vc = pd.DataFrame(temp_df[cond_null | cond_notnull].groupby('Actor2CodeIsNull')['EventCode'].value_counts())
vc.rename(columns={'EventCode':"N_EVENTS"}, inplace=True)
# merge with event code descriptions lookup table
event_lt.index.names = ['EventCode']
event_lt.join(vc,how='inner')
# plot frequencies of EventCode values for events when Actor1Code is NOT null, split by whether Actor2Code is null or NOT null
temp_df[cond_null | cond_notnull].groupby('EventCode')['Actor2CodeIsNull'] \
    .value_counts().unstack().plot(kind="bar")
plt.show()<jupyter_output><empty_output><jupyter_text>There is a bit of a difference in distributions of EventCode values in those situations, but not significant enough or easily interpretable to understand how it relates to the presence of Actor2Code or lack thereof.<jupyter_code>#check number of unique Actor1Code values
df[df.Actor1Code.notnull()].Actor1Code.value_counts().shape[0]
# plot frequencies of 100 most frequent Actor1Code values
plt.figure(figsize=(30,5))
df[df.Actor1Code.notnull()]['Actor1Code'].value_counts().nlargest(100).plot(kind="bar")
plt.show()
# Check the most frequent combinations of Actor1Code and Actor2Code
df.groupby([df.Actor1Code, df.Actor2Code]).size().nlargest(40)<jupyter_output><empty_output><jupyter_text>Many domestic group codes above (like LEG (Legislature) or MED (Media)) do not include a country code, so it is not clear which country's group is being referenced.<jupyter_code># Check which Actor1CountryCode values exist when Actor1Code just has one of the primary or secondary role codes
df[df.Actor1Code.isin(['GOV','COP','EDU','MIL','JUD','LEG','BUS','CVL','MED'])]['Actor1CountryCode'].value_counts()<jupyter_output><empty_output><jupyter_text>It does not look like Actor1CountryCode can assist in identifying the country of group in the Actor1Code column.  Let's check if Actor1Name can assist.<jupyter_code>df[df.Actor1Code.isin(['GOV','COP','EDU','MIL','JUD','LEG','BUS','CVL','MED'])]['Actor1Name'].value_counts()
# plot frequencies of 100 most frequent Actor1Name values when Actor1Code has one of the main domestic group values
plt.figure(figsize=(30,5))
df[df.Actor1Code.isin(['GOV','COP','EDU','MIL','JUD','LEG','BUS','CVL','MED'])]['Actor1Name'].value_counts().nlargest(100).plot(kind="bar")
plt.show()<jupyter_output><empty_output><jupyter_text>Even though documentation says that Actor1Name contains the "actual name of the Actor1", Actor1Name values appear to be just more specific categories of Actor1Code, not actual names. Let's investigate the Actor1CountryCode field a bit more.<jupyter_code># Check what values the field generally takes.
df.Actor1CountryCode.value_counts(dropna=False)
# Check what Actor1Code values exist when Actor1CountryCode is USA
df[df.Actor1CountryCode == "USA"]['Actor1Code'].value_counts(dropna=False)
# plot frequencies of 100 most frequent Actor1Code values when Actor1CountryCode is USA
plt.figure(figsize=(30,5))
df[df.Actor1CountryCode == 'USA']['Actor1Code'].value_counts().nlargest(25).plot(kind="bar")
plt.show()<jupyter_output><empty_output><jupyter_text>Actor1CountryCode (even when not missing) does not appear to provide much more value over just the Actor1Code column.  Geography Codes<jupyter_code># Check values of Actor1Geo_Type
geo_type_vls = df.Actor1Geo_Type.value_counts(dropna=False).to_frame(name='Count')
geo_type_vls['Percent'] = geo_type_vls['Count'].apply(lambda x: '{:.2f}%'.format((float(x) / geo_type_vls.Count.sum()) * 100))
geo_type_vls<jupyter_output><empty_output><jupyter_text>It is not clear what the 0 value represents (documentation only identifies 1 through 5). To try and understand what the 0 value represents, let's check the Actor1Geo_Fullname column for those values.  We saw earlier that Actor1Geo_FullName is null roughly the same number of times as there are 0 values in Actor1Geo_Type.<jupyter_code>df[df.Actor1Geo_Type == 0]['Actor1Geo_FullName'].value_counts()
df[df.Actor1Geo_FullName.isnull()]['Actor1Geo_Type'].value_counts()<jupyter_output><empty_output><jupyter_text>So, in all cases when Geo_Type is 0, Geo_FullName is null.  There is a small number of cases when Geo_FullName is null, but Geo_Type takes on the value 1 (Country).<jupyter_code>df[df.Actor1Geo_FullName.isnull() & df.Actor1Geo_Type == 1]['Actor1Geo_CountryCode'].value_counts(dropna=False)<jupyter_output><empty_output><jupyter_text>So, at least country name is available in the Geo_CountryCode field when Geo_FullName is null, except that these two codes (RB and YI are not found in the FIPS10-4 country code list (https://en.wikipedia.org/wiki/List_of_FIPS_country_codes, https://www.gdeltproject.org/data/lookups/FIPS.country.txt)<jupyter_code># Check what ActionGeo_CountryCode values exist for those RB and YI Actor1 values
df[df.Actor1Geo_FullName.isnull() & (df.Actor1Geo_Type == 1)].groupby(['Actor1Geo_CountryCode','ActionGeo_CountryCode']).size()
# Download country code FIPS crosswalk file to check against
url = 'https://www.gdeltproject.org/data/lookups/FIPS.country.txt'

country_fips_df = pd.read_csv(url, header=None, delimiter='\t', names=['code','country'])

country_fips_df.head()
# Check whether FIPS10-4 country codes found in the Events table can all be mapped to the crosswalk file
event_countries = df.Actor1Geo_CountryCode.values.tolist()
xwalk_countries = country_fips_df.code.values.tolist()

list(set(event_countries) - set(xwalk_countries))<jupyter_output><empty_output><jupyter_text>So, YI, RB and OC are country codes found in the Events table but not in the code-to-country_name crosswalk.  (The same results as above are observed for Actor2Geo_CountryCode and ActionGeo_CountryCode values.  And these three values are also not found in the GENC country code list that superseded FIPS codes.)<jupyter_code># Check
df[df.Actor1Code.isin(['GOV','COP','EDU','MIL','JUD','LEG','BUS','CVL','MED'])]['Actor1Geo_Type'].value_counts()
df[df.Actor1Code.isin(['GOV','COP','EDU','MIL','JUD','LEG','BUS','CVL','MED'])]['Actor1Geo_CountryCode'].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11935139211866004, 'fraction_numerical': 0.027402425994594935, 'mean_word_length': 6.222877893781208, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 74, 'https://': 6, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '149211', 'license': 'MIT', 'n_tokens_mistral': 5439, 'n_tokens_neox': 5110, 'n_words': 1428, 'path': 'GDELT_EDA.ipynb', 'repo_name': 'sasha-kap/Events-Analytics', 'size': '15911'}