<jupyter_start><jupyter_text>Analysis of GDELT violence data  Query ```sqlSELECT Year, 	   ActionGeo_CountryCode AS CountryCode,	   EventRootCode,	   EventCode,	   COUNT(GLOBALEVENTID) AS SumEvents,	   ANY_VALUE(GoldsteinScale) AS GoldsteinScale,	   AVG(NumMentions) as AvgNumMentions,	   SUM(NumMentions) as SumNumMentions,	   AVG(AvgTone) as AvgAvgTone,FROM `gdelt-bq.full.events`WHERE 	EventRootCode IN ("17", "18", "19") 	AND Year >= 1979GROUP BY Year, ActionGeo_CountryCode, EventRootCode, EventCodeORDER BY Year```<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Get CAMEO event codes.<jupyter_code>!curl -o data/CAMEO_codes.txt https://www.gdeltproject.org/data/lookups/CAMEO.eventcodes.txt 
cameo_codes = pd.read_csv('data/CAMEO_codes.txt', sep='\t', 
                          names=['code', 'descr'], skiprows=1,
                         dtype=str)

cameo_codes['code'] = cameo_codes['code'].astype(str)

cameo_codes.head()<jupyter_output><empty_output><jupyter_text>Load query data.<jupyter_code>data = pd.read_csv('data/query_result.csv')
data['EventCode'] = data['EventCode'].astype(str)
data['EventRootCode'] = data['EventRootCode'].astype(str)

if 'f0_' in data.columns:
    data.rename({'f0_': 'GoldsteinScale'},
                axis=1, inplace=True)

data.shape
data.head()<jupyter_output><empty_output><jupyter_text>Merge event and event root description.<jupyter_code>data = data.merge(cameo_codes, left_on='EventCode', right_on='code', how='left')
data = data.merge(cameo_codes, left_on='EventRootCode', right_on='code', how='left')

data.drop(['code_x', 'code_y'], axis=1, inplace=True)

data.rename({'descr_x': 'EventDescr',
             'descr_y': 'EventRootDescr'},
            axis=1, inplace=True)
data.head()<jupyter_output><empty_output><jupyter_text>Some countries are NULL.<jupyter_code>data.isnull().sum()<jupyter_output><empty_output><jupyter_text>Download and merge country names.<jupyter_code>!curl -o data/country_names.csv https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv
cn = pd.read_csv('data/country_names.csv')
cn = cn.drop('ISO 3166', axis=1)
cn.columns = ['CountryCode', 'CountryName']
cn.head()
data = data.merge(cn, on='CountryCode', how='left')<jupyter_output><empty_output><jupyter_text>Downloading and merging total values.<jupyter_code>!curl -o data/yearly_country_totals.csv http://data.gdeltproject.org/normfiles/yearly_country.csv
totals = pd.read_csv('data/yearly_country_totals.csv', names=['Year', 'CountryCode', 'TotalEvents'])
totals.head()
data = data.merge(totals, on=['Year', 'CountryCode'], how='left')<jupyter_output><empty_output><jupyter_text>Compute normalized events count (over 1000 events).<jupyter_code>data['NormalizedEvents1000'] = data['SumEvents'] / data['TotalEvents'] * 10**3<jupyter_output><empty_output><jupyter_text>Removing rows with NULL values.<jupyter_code>before = data.shape[0]
data = data.dropna()
after = data.shape[0]
removed = before - after
percent_removed = round(removed / before, 2) * 100
print(f"Removed {before - after} rows ({percent_removed}% of {before} rows.)")
data.isnull().sum().sum()
data.shape<jupyter_output><empty_output><jupyter_text>Reordering columns.<jupyter_code>prev = len(data.columns)
data = data[['Year', 'CountryCode', 'CountryName', 
             'SumEvents', 'TotalEvents', 'NormalizedEvents1000', 
             'EventRootCode', 'EventRootDescr',
             'EventCode', 'EventDescr', 
             'GoldsteinScale', 'AvgNumMentions', 'SumNumMentions', 'AvgAvgTone'
             ]]

assert len(data.columns) == prev
data.head()
data.isna().sum().sum()<jupyter_output><empty_output><jupyter_text>Export<jupyter_code>data.to_csv('gdelt_conflict/gdelt_conflict_1_0.csv', index=None)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1520544360115153, 'fraction_numerical': 0.0123004449097095, 'mean_word_length': 5.961748633879782, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 46, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '531590', 'license': 'MIT', 'n_tokens_mistral': 1416, 'n_tokens_neox': 1371, 'n_words': 210, 'path': 'processing_1_0.ipynb', 'repo_name': 'vlad-ds/gdelt-conflict', 'size': '3821'}