<jupyter_start><jupyter_text>Import Modules<jupyter_code>import pandas as pd
import numpy as np
import pickle<jupyter_output><empty_output><jupyter_text>Load Datasets<jupyter_code>DIR='/Users/tim/src/Metis/Project_3/data/raw/'
df_reign = pd.read_csv(DIR+'REIGN_2019_3.csv', encoding = 'ISO-8859-1')
df_wdi = pd.read_csv(DIR+'WDIData.csv')
df_penn = pd.read_csv(DIR+'pwt91.csv')<jupyter_output><empty_output><jupyter_text>Prepare REIGN Dataset  Filter<jupyter_code># Select relevant features
df_reign = df_reign[['country','year', 'government', 'militarycareer','tenure_months','prev_conflict', 'pt_attempt', 'exec_ant', 'irreg_lead_ant', 'exec_recent', 'lead_recent']]

# Select observations only between 1989 and 2016
df_r2 = df_reign[(df_reign['year'] > 1988) & (df_reign['year'] < 2017)]
#df_r2.info()<jupyter_output><empty_output><jupyter_text>Aggregate Data by Year<jupyter_code># Create prev_year column
df_r2['prev_year'] = df_r2.year.shift()

# Convert tenure_months to tenure by year
df_r2.loc[(df_r2.prev_year.isnull() == True) | (df_r2.year != df_r2['prev_year']), 'tenure_months'] = (df_r2.tenure_months/12)
df_r2.loc[df_r2.year == df_r2.prev_year, 'tenure_months'] = 0 

# Sum up columns by country by year
df_r3 = df_r2.groupby(['country', 'year']).sum()<jupyter_output>/anaconda3/envs/metis/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  
/anaconda3/envs/metis/lib/python3.6/site-packages/pandas/core/indexing.py:543: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self.obj[item] = s<jupyter_text>Flatten Groupby<jupyter_code>df_r3.reset_index(inplace=True)
#df_r3.info()<jupyter_output><empty_output><jupyter_text>Fix Aggregation<jupyter_code># Shift prev_conflict and coup attempts
df_r3['coup'] = df_r3.pt_attempt.shift()
df_r3['prev_year_conflict'] = df_r3.prev_conflict.shift()

# Convert categories back to binary form
df_r3.loc[df_r3.militarycareer > 0, 'militarycareer'] = 1 
df_r3.loc[df_r3.exec_ant > 0, 'exec_ant'] = 1
df_r3.loc[df_r3.irreg_lead_ant > 0, 'irreg_lead_ant'] = 1
df_r3.loc[df_r3.exec_recent > 0, 'exec_recent'] = 1
df_r3.loc[df_r3.lead_recent > 0, 'lead_recent'] = 1
df_r3.loc[df_r3.coup > 0, 'coup'] = 1<jupyter_output><empty_output><jupyter_text>Rename variables<jupyter_code>df_r3.rename(columns={'militarycareer':'mil_career',
                      'tenure_months':'tenure', 'prev_conflict':'prev_conflict2',
                      'prev_year_conflict':'prev_conflict'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Filter Again<jupyter_code># Remove obsolete columns
df_r3.drop(columns = ['prev_year', 'prev_conflict2', 'pt_attempt'], inplace=True)

# Select only observations from 1990 and on
df_r4 = df_r3[(df_r3['year'] >= 1990)]
# Convery binary categories to integers
df_r4.coup = df_r4.coup.astype(int)
df_r4.year = df_r4.year.astype(int)
df_r4.mil_career = df_r4.mil_career.astype(int)
df_r4.prev_conflict = df_r4.prev_conflict.astype(int)
#df_r4.info()<jupyter_output><empty_output><jupyter_text>Prepare WDI Dataset  Filter<jupyter_code># Select country aggregated data
df_w2 = df_wdi[74965:]

# Drop Country Code, Indicator Name, and Unnamed Column
df_w2 = df_w2.drop(['Country Code', 'Indicator Name'], axis=1).drop(df_w2.columns[[-1]], axis=1).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Transform<jupyter_code># Unpivot by Country Name, Indicator Code, and Date
df_w3 = df_w2.melt(['Country Name', 'Indicator Code'], var_name='Date', value_name='Value')

# Combine Date and Country Columns
df_w3['Date-Country'] = df_w3['Date'] +'-'+ df_w3['Country Name']

#Drop Date and Country Columns
df_w3.drop(['Date', 'Country Name'], axis=1, inplace=True)

# Pivot by Date-Country and Indicator Code
df_w3 = df_w3.pivot(index='Date-Country', columns='Indicator Code', values='Value')
df_w3.reset_index(level=0, inplace=True)<jupyter_output><empty_output><jupyter_text>Filter Again<jupyter_code># Select Relevant Features
df_w4 = df_w3[['Date-Country', 'NY.GDP.TOTL.RT.ZS', 'SN.ITK.DEFC.ZS', 'SE.XPD.TOTL.GD.ZS', 'SH.XPD.GHED.GD.ZS', 'FI.RES.TOTL.DT.ZS', 'GC.REV.XGRT.GD.ZS', 'EG.ELC.ACCS.ZS', 'DT.DFR.DPPG.CD', 'SP.DYN.LE00.IN', 'MS.MIL.XPND.GD.ZS', 'SH.STA.BASS.ZS', 'SH.H2O.BASW.ZS', 'GC.TAX.TOTL.GD.ZS', 'MS.MIL.TOTL.TF.ZS']]

# Split Date-Country Column 
df_w5 = pd.DataFrame(df_w4['Date-Country'].str.split("-", 1).tolist(), columns = ['year','country']).join(df_w4)

# Remove Date-Country Column
df_w5 = df_w5.drop(['Date-Country'], axis=1)

# Select Relevant Years
df_w5['year'] = df_w5['year'].apply(pd.to_numeric) 
df_w5 = df_w5[(df_w5['year'] >= 1990) & (df_w5['year'] < 2017)]<jupyter_output><empty_output><jupyter_text>Rename Features<jupyter_code>df_w5.rename(columns={'NY.GDP.TOTL.RT.ZS':'nat_resources', 'SN.ITK.DEFC.ZS':'food_scarcity',
                      'SE.XPD.TOTL.GD.ZS':'education', 'SH.XPD.GHED.GD.ZS':'healthcare',
                      'FI.RES.TOTL.DT.ZS':'reserves', 'GC.REV.XGRT.GD.ZS':'revenue',
                      'EG.ELC.ACCS.ZS': 'electricity', 'DT.DFR.DPPG.CD':'debt_forgiven', 
                      'SP.DYN.LE00.IN': 'life_exp', 'MS.MIL.XPND.GD.ZS': 'mil_spending', 
                      'SH.STA.BASS.ZS': 'sanitation', 'SH.H2O.BASW.ZS': 'water',
                      'GC.TAX.TOTL.GD.ZS':'tax', 'MS.MIL.TOTL.TF.ZS': 'military_pop'}, inplace=True)
#df_w5.info()<jupyter_output><empty_output><jupyter_text>Clean Penn Table Dataset  Filter<jupyter_code># Select relevant features
df_p2 = df_penn[['country', 'year', 'rgdpe', 'hc']]

# Select target years
df_p2 = df_p2[(df_p2['year'] >= 1990) & (df_p2['year'] < 2017)]
#df_p2.info()<jupyter_output><empty_output><jupyter_text>Join Datasets<jupyter_code>df_merge2 = pd.merge(df_r4, df_w5, how='left', on=['country', 'year'])
df_merge3 = pd.merge(df_merge2, df_p2, how='left', on=['country', 'year'])
#df_merge3.info()
# filepath = '/Users/tim/src/Metis/Project_3/data/interim/df_merge3.pkl'
# with open(filepath, 'wb') as pkl:
#     pickle.dump(df_merge3, pkl)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1661509900990099, 'fraction_numerical': 0.029084158415841586, 'mean_word_length': 5.798107255520505, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 66, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '271652', 'license': 'MIT', 'n_tokens_mistral': 2822, 'n_tokens_neox': 2680, 'n_words': 403, 'path': 'Notebooks/1.Combine-Datasets.ipynb', 'repo_name': 'broco347/coup-predictor', 'size': '6464'}