<jupyter_start><jupyter_text>Prepare Data for Map of CoW vs UCDP/PRIO Warsby <NAME><jupyter_code>import pandas as pd
import numpy as np
import geopandas as gpd
from operator import attrgetter<jupyter_output><empty_output><jupyter_text>Import Country Shapes to GeoPandas DataFrame<jupyter_code>cshapes_df = gpd.read_file("../Data/CShapes/cshapes_0.6/cshapes.shp")
cshapes_df.columns
cshapes_df.head()<jupyter_output><empty_output><jupyter_text>Create GeoDataFrames for both CoW and G&W (UCDP/PRIO)Two geodataframes: one for CoW schema (used with CoW wars) and one for G&W schema (used with UCDP/PRIO wars)Get rid of columns that don't apply to that dataframe's country code schemaNeed to transform the date columns into startdate and enddate columns of datetime type - but lose the day specificity, as date granularity will only be saved at the month level<jupyter_code>cow_geo_df = cshapes_df[cshapes_df['COWCODE'] != -1].drop(columns=['GWSYEAR', 'GWSMONTH', 'GWSDAY', 'GWEYEAR', 'GWEMONTH', 'GWEDAY', 'ISONAME', 'ISO1NUM', 'ISO1AL2'])
gw_geo_df = cshapes_df[cshapes_df['GWCODE'] != -1].drop(columns=['COWSYEAR', 'COWSMONTH', 'COWSDAY', 'COWEYEAR', 'COWEMONTH', 'COWEDAY','ISONAME', 'ISO1NUM', 'ISO1AL2'])
cow_geo_df['StartDate'] = cow_geo_df['COWSYEAR'].astype(str) + '-' + cow_geo_df['COWSMONTH'].astype(str) + '-01' #+ cow_geo_df['COWSDAY'].astype(str)
cow_geo_df['StartDate'] = pd.to_datetime(cow_geo_df['StartDate'])

cow_geo_df['EndDate'] = cow_geo_df['COWEYEAR'].astype(str) + '-' + cow_geo_df['COWEMONTH'].astype(str) + '-01' #+ cow_geo_df['COWEDAY'].astype(str)
cow_geo_df['EndDate'] = pd.to_datetime(cow_geo_df['EndDate'])

gw_geo_df['StartDate'] = gw_geo_df['GWSYEAR'].astype(str) + '-' + gw_geo_df['GWSMONTH'].astype(str) + '-01' #+ gw_geo_df['GWSDAY'].astype(str)
gw_geo_df['StartDate'] = pd.to_datetime(gw_geo_df['StartDate'])

gw_geo_df['EndDate'] = gw_geo_df['GWEYEAR'].astype(str) + '-' + gw_geo_df['GWEMONTH'].astype(str) + '-01' #+ gw_geo_df['GWEDAY'].astype(str)
gw_geo_df['EndDate'] = pd.to_datetime(gw_geo_df['EndDate'])<jupyter_output><empty_output><jupyter_text>Note: These lists will be needed later to trim down the CoW and UCDP/PRIO conflict datasets<jupyter_code>cshapes_cowcodes = cow_geo_df['COWCODE'].unique().tolist()
cshapes_gwcodes = gw_geo_df['GWCODE'].unique().tolist()<jupyter_output><empty_output><jupyter_text>let's see how many countries change borders and how often<jupyter_code>cow_geo_df.groupby('COWCODE').agg({'FEATUREID': 'nunique', 'CNTRY_NAME': lambda x: ', '.join(x), 'StartDate': 'min', 'EndDate': 'max'}).sort_values(by='FEATUREID', ascending=False).head(26)
gw_geo_df.groupby('GWCODE').agg({'FEATUREID': 'nunique', 'CNTRY_NAME': lambda x: ', '.join(x), 'StartDate': 'min', 'EndDate': 'max'}).sort_values(by='FEATUREID', ascending=False).head(26)<jupyter_output><empty_output><jupyter_text>Expand GeoDataFrames into a Time-SeriesNote: cannot keep the geometries column for some reason - need to merge this in later. Time-series df is not a geodataframe, only dataframes with geometries are still geodataframes.code citation for expanding to a time-series from a time range: https://stackoverflow.com/questions/42151886/expanding-pandas-data-frame-with-date-range-in-columns<jupyter_code>cow_geo_df_geometries = cow_geo_df[['FEATUREID', 'geometry']]
type(cow_geo_df_geometries)
gw_geo_df_geometries = gw_geo_df[['FEATUREID', 'geometry']]
type(gw_geo_df_geometries)
cow_geo_df = cow_geo_df.sort_values(by=['COWCODE', 'StartDate', 'FEATUREID'])

cow_geo_ts_df = pd.concat([pd.DataFrame({'month': pd.date_range(row.StartDate, row.EndDate, freq='MS'),
                                         'cow_id': row.COWCODE,
                                         'gw_id': row.GWCODE,
                                         'iso_alpha3': row.ISO1AL3,
                                         'country': row.CNTRY_NAME, 
                                         'FEATUREID': row.FEATUREID}, 
                                columns=['month', 'cow_id', 'iso_alpha3', 'gw_id', 'country', 'FEATUREID']) 
                           for i, row in cow_geo_df.iterrows()], ignore_index=True)
cow_geo_ts_df
gw_geo_df = gw_geo_df.sort_values(by=['GWCODE', 'StartDate', 'FEATUREID'])

gw_geo_ts_df = pd.concat([pd.DataFrame({'month': pd.date_range(row.StartDate, row.EndDate, freq='MS'),
                                        'gw_id': row.GWCODE,
                                        'cow_id': row.COWCODE,
                                        'iso_alpha3': row.ISO1AL3,
                                        'country': row.CNTRY_NAME, 
                                        'FEATUREID': row.FEATUREID}, 
                                columns=['month', 'gw_id', 'iso_alpha3', 'cow_id', 'country', 'FEATUREID']) 
                           for i, row in gw_geo_df.iterrows()], ignore_index=True)
gw_geo_ts_df<jupyter_output><empty_output><jupyter_text>create a variable that marks when the borders changeNote: dropping the date specificity to the month level resulted in duplicates in the time series. Keep=last will keep the row with the border change = 1<jupyter_code>def find_border_change(x):
    marker = 1
    current_shape = -1
    for i, v in x.iterrows():
        this_shape = x.loc[i, 'FEATUREID']
        if this_shape == current_shape:
            x.loc[i, 'border_change'] = 0
        else:
            x.loc[i, 'border_change'] = 1
            current_shape = this_shape
    return x
cow_geo_ts_df = cow_geo_ts_df.groupby(['cow_id']).apply(find_border_change)
cow_geo_ts_df = cow_geo_ts_df.drop_duplicates(subset=['month', 'cow_id'], keep='last')
gw_geo_ts_df = gw_geo_ts_df.groupby(['gw_id']).apply(find_border_change)
gw_geo_ts_df = gw_geo_ts_df.drop_duplicates(subset=['month', 'gw_id'], keep='last')<jupyter_output><empty_output><jupyter_text>add back in the geometry columns and make sure they are geodataframes and haven't lost any data<jupyter_code>cow_geo_ts_df.shape, gw_geo_ts_df.shape
cow_geo_ts_df = cow_geo_df_geometries.merge(cow_geo_ts_df, on='FEATUREID')
cow_geo_ts_df
cow_geo_ts_df.shape, type(cow_geo_ts_df)
gw_geo_ts_df = gw_geo_df_geometries.merge(gw_geo_ts_df, on='FEATUREID')
gw_geo_ts_df
gw_geo_ts_df.shape, type(gw_geo_ts_df)<jupyter_output><empty_output><jupyter_text>Import CoW conflict data<jupyter_code>cow_par = pd.read_csv("../Data/CorrelatesOfWar/war_participants.csv")
cow_par['StartDate'] = pd.to_datetime(cow_par['StartDate'])
cow_par['EndDate'] = cow_par['EndDate'].fillna('2008-12-31')
cow_par['EndDate'] = pd.to_datetime(cow_par['EndDate'])
cow_par['StartDate'] = cow_par['StartDate'].apply(lambda dt: dt.replace(day=1))
cow_par['EndDate'] = cow_par['EndDate'].apply(lambda dt: dt.replace(day=1))

cow_cut = cow_par[cow_par['PolityID'].isin(cshapes_cowcodes)]
cow_cut = cow_cut[(cow_cut['StartDate'] >= '1945-01-01') & (cow_cut['StartDate'] <= '2007-12-31')]
cow_war_ts = pd.concat([pd.DataFrame({'month': pd.date_range(row.StartDate, row.EndDate, freq='MS'),
                                      'cow_id': row.PolityID,
                                      'WarID': row.WarID}, 
                                columns=['month', 'cow_id', 'WarID']) 
                           for i, row in cow_cut.iterrows()], ignore_index=True)
cow_war_ts = cow_war_ts[cow_war_ts['month'] <= '2007-12-31']
cow_war_ts = cow_war_ts.drop_duplicates()
cow_war_ts
cow_war_ts_gb = cow_war_ts.groupby(['month', 'cow_id']).agg({'WarID': 'count'})
cow_war_ts_gb = cow_war_ts_gb.reset_index().rename(columns={'WarID':'war_count'})
cow_war_ts_gb
cow_war_ts_gb['war_count'].value_counts()<jupyter_output><empty_output><jupyter_text>Import UCDP/PRIO conflict data<jupyter_code>ucdp_par = pd.read_csv("../Data/UCDP-PRIO_ArmedConflict/participants_gw.csv")
ucdp_obs = pd.read_csv("../Data/UCDP-PRIO_ArmedConflict/observations.csv")
ucdp_eps = pd.read_csv("../Data/UCDP-PRIO_ArmedConflict/episodes.csv")
ucdp_merged = ucdp_par.merge(ucdp_obs[['obs_id', 'intensity_level', 'ep_id']], on=['obs_id']) \
                      .merge(ucdp_eps[['ep_id', 'start_date2', 'ep_end_date']], on=['ep_id'])

ucdp_merged = ucdp_merged[ucdp_merged['gw_id'].isin(cshapes_gwcodes)]
ucdp_merged = ucdp_merged[ucdp_merged['intensity_level'] == 'War']
ucdp_merged = ucdp_merged[ucdp_merged['role'] == 'primary']

ucdp_merged['start_date2'] = pd.to_datetime(ucdp_merged['start_date2'])
ucdp_merged['ep_end_date'] = ucdp_merged['ep_end_date'].fillna('2018-12-31')
ucdp_merged['ep_end_date'] = pd.to_datetime(ucdp_merged['ep_end_date'])
ucdp_merged['start_date2'] = ucdp_merged['start_date2'].apply(lambda dt: dt.replace(day=1))
ucdp_merged['ep_end_date'] = ucdp_merged['ep_end_date'].apply(lambda dt: dt.replace(day=1))
ucdp_merged
ucdp_war_ts = pd.concat([pd.DataFrame({'month': pd.date_range(row.start_date2, row.ep_end_date, freq='MS'),
                                        'gw_id': row.gw_id,
                                        'WarID': row.conflict_id}, 
                                columns=['month', 'gw_id', 'WarID']) 
                           for i, row in ucdp_merged.iterrows()], ignore_index=True)
ucdp_war_ts = ucdp_war_ts[ucdp_war_ts['month'] <= '2018-12-31']
ucdp_war_ts = ucdp_war_ts.drop_duplicates()
ucdp_war_ts
ucdp_war_ts_gb = ucdp_war_ts.groupby(['month', 'gw_id']).agg({'WarID': 'count'})
ucdp_war_ts_gb = ucdp_war_ts_gb.reset_index().rename(columns={'WarID':'war_count'})
ucdp_war_ts_gb
ucdp_war_ts_gb['war_count'].value_counts()<jupyter_output><empty_output><jupyter_text>Import PolityIV data<jupyter_code>pol = pd.read_csv("../Data/PolityIV/Raw/p4v2018.csv", usecols=['ccode', 'year', 'polity2'])
pol = pol[(pol['year'] > 1945) & (pol['year'] < 2017)]
pol = pol.rename(columns={'ccode':'cow_id'})<jupyter_output><empty_output><jupyter_text>Final data wrangling<jupyter_code>cow_final_gdf = cow_geo_ts_df.merge(cow_war_ts_gb, on=['cow_id', 'month'], how='left')
cow_final_gdf['war_count'] = cow_final_gdf['war_count'][cow_final_gdf['month'] <= '2007-12-31'].fillna(0)
cow_final_gdf['month_count'] = (cow_final_gdf.month.dt.to_period('M') - pd.to_datetime('1946-01-01').to_period('M')).apply(attrgetter('n'))
cow_final_gdf = cow_final_gdf.sort_values('month_count')
cow_final_gdf['year'] = cow_final_gdf.month.dt.year
cow_final_gdf['war_count_cum'] = cow_final_gdf.groupby(['cow_id'])['war_count'].cumsum()
cow_final_gdf = cow_final_gdf.merge(pol, on=['cow_id', 'year'])
cow_final_gdf
ucdp_final_gdf = gw_geo_ts_df.merge(ucdp_war_ts_gb, on=['gw_id', 'month'], how='left')
ucdp_final_gdf['war_count'] = ucdp_final_gdf['war_count'].fillna(0).astype('int')
ucdp_final_gdf['month_count'] = (ucdp_final_gdf.month.dt.to_period('M') - pd.to_datetime('1946-01-01').to_period('M')).apply(attrgetter('n'))
ucdp_final_gdf = ucdp_final_gdf.sort_values('month_count')
ucdp_final_gdf['year'] = ucdp_final_gdf.month.dt.year
ucdp_final_gdf['war_count_cum'] = ucdp_final_gdf.groupby(['gw_id'])['war_count'].cumsum()
ucdp_final_gdf
type(cow_final_gdf), type(ucdp_final_gdf)
cow_final_gdf.columns
cow_final_gdf_shapes = cow_final_gdf[['FEATUREID', 'geometry']].drop_duplicates()
cow_final_gdf_data = cow_final_gdf.drop(columns=['geometry'])
ucdp_final_gdf_shapes = ucdp_final_gdf[['FEATUREID', 'geometry']].drop_duplicates()
ucdp_final_gdf_data = ucdp_final_gdf.drop(columns=['geometry'])<jupyter_output><empty_output><jupyter_text>Export files<jupyter_code>cow_final_gdf_shapes.to_file('./cow_wars_shapes.gpkg', driver="GPKG")
cow_final_gdf_data.to_csv('./cow_wars_data.csv', index=False, encoding='utf-8')
ucdp_final_gdf_shapes.to_file('./ucdp_wars_shapes.gpkg', driver="GPKG")
ucdp_final_gdf_data.to_csv('./ucdp_wars_data.csv', index=False, encoding='utf-8')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15592551274349953, 'fraction_numerical': 0.012271518064017849, 'mean_word_length': 5.0352149145520455, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 56, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '166454', 'license': 'MIT', 'n_tokens_mistral': 4624, 'n_tokens_neox': 4553, 'n_words': 615, 'path': 'Notebooks_Part3/Create Data for War Maps.ipynb', 'repo_name': 'jenna-jordan/IS590DV-FinalProject', 'size': '11659'}