Preprocessing Step 1 – Participant and Dyadic Setup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandasql import sqldf\n",
    "pysqldf = lambda q: sqldf(q, globals())\n",
    "from copy import deepcopy\n",
    "from importlib import reload\n",
    "from traceback import format_exc\n",
    "# from pprint import pprint\n",
    "import the_networks_of_war_python_functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.float_format', lambda x: '%.5f' % x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_output_directory = '../cyaris.github.io/assets/csv/the_networks_of_war/'\n",
    "json_output_directory = '../cyaris.github.io/assets/json/the_networks_of_war/'\n",
    "csv_directory = '/Users/charlieyaris/Personal/data_sources/the_networks_of_war/csvs/'\n",
    "pickle_directory = '/Users/charlieyaris/Personal/data_sources/the_networks_of_war/pickles/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "war_type_df = deepcopy(pd.read_csv('war_types.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup for Identifying Countries by Code\n",
    "### Note: This is helpful for when different names are used for the same country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Country Codes: 217\n"
     ]
    }
   ],
   "source": [
    "c_code_dic = the_networks_of_war_python_functions.define_c_code_dic()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_code_df = pd.read_csv(csv_directory + 'COW country codes.csv', encoding='latin-1')\n",
    "\n",
    "query_text = \"\"\"\n",
    "\n",
    "select\n",
    "    ccode as c_code,\n",
    "    statenme as state_name,\n",
    "    stateabb as state_name_abbreviation\n",
    "from c_code_df\n",
    "group by 1, 2, 3\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "c_code_df = deepcopy(pysqldf(query_text))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "part_query_template = \"\"\"\n",
    "    \n",
    "select\n",
    "    cast(a.war_num as float) as war_num,\n",
    "    a.war_name,\n",
    "    cast(a.war_type as integer) as war_type_code,\n",
    "    wt.war_type,\n",
    "    coalesce(wt.war_subtype, wt.war_type) as war_subtype,\n",
    "    a.C_CODE as c_code,\n",
    "    coalesce(cc.state_name, a.PARTICIPANT) as participant,\n",
    "    case when min(a.SIDE) = 1 and max(a.SIDE) = 2 then 3\n",
    "        else max(a.SIDE) end as side,\n",
    "    sum(case when a.BATTLE_DEATHS >= 0 then a.BATTLE_DEATHS else null end) as battle_deaths,\n",
    "    cast(strftime('%Y', min(a.start_date)) as integer) as start_year,\n",
    "    cast(strftime('%m', min(a.start_date)) as integer) as start_month,\n",
    "    cast(strftime('%d', min(a.start_date)) as integer) as start_day,\n",
    "    min(start_date) as start_date,\n",
    "    cast(strftime('%Y', max(a.end_date)) as integer) as end_year,\n",
    "    cast(strftime('%m', max(a.end_date)) as integer) as end_month,\n",
    "    cast(strftime('%d', max(a.end_date)) as integer) as end_day,\n",
    "    max(a.end_date) as end_date,\n",
    "    max(a.ongoing_conflict) as ongoing_conflict,\n",
    "    max(a.start_date_estimated) as start_date_estimated,\n",
    "    max(a.end_date_estimated) as end_date_estimated,\n",
    "    sum(a.days_not_at_war) as days_not_at_war,\n",
    "    max(a.LAGGING_WAR) as lagging_war,\n",
    "    max(a.LEADING_WAR) as leading_war,\n",
    "    sum(case when a.DEATHS_BOTH_SIDES >= 0 then a.DEATHS_BOTH_SIDES else null end) as deaths_both_sides,\n",
    "    sum(case when a.PEAK_FORCES_AVAILABLE >= 0 then a.PEAK_FORCES_AVAILABLE else null end) as peak_forces_available,\n",
    "    sum(case when a.PEAK_BATTLE_FORCES >= 0 then a.PEAK_BATTLE_FORCES else null end) as peak_battle_forces\n",
    "from SOURCE_TABLE a\n",
    "left join c_code_df cc on a.C_CODE = cc.c_code\n",
    "left join war_type_df wt on a.war_type = wt.war_type_code\n",
    "group by 1, 2, 3, 4, 5, 6, 7\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Participant DataFrames\n",
    "### Note: Only inter-state wars have different source files for dyadic and participant data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inter-State Wars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Rows With Both Dates Found: 337\n",
      "Total Rows With At Least One Date Not Found: 0\n",
      "Total Estimated Start Dates: 0\n",
      "Total Estimated End Dates: 0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "part_df_1 = pd.read_csv(csv_directory + 'Inter-StateWarData_v4.0.csv', encoding='latin-1')\n",
    "\n",
    "part_df_1_renaming = {\n",
    "\n",
    "    'WarNum': 'war_num',\n",
    "    'WarName': 'war_name',\n",
    "    'WarType': 'war_type',\n",
    "    'ccode': 'c_code',\n",
    "    'StateName': 'participant',\n",
    "    'Side': 'side',\n",
    "    'BatDeath': 'battle_deaths',\n",
    "    'StartYear1': 'start_year_1',\n",
    "    'StartMonth1': 'start_month_1',\n",
    "    'StartDay1': 'start_day_1',\n",
    "    'StartYear2': 'start_year_2',\n",
    "    'StartMonth2': 'start_month_2',\n",
    "    'StartDay2': 'start_day_2',\n",
    "    'EndYear1': 'end_year_1',\n",
    "    'EndMonth1': 'end_month_1',\n",
    "    'EndDay1': 'end_day_1',\n",
    "    'EndYear2': 'end_year_2',\n",
    "    'EndMonth2': 'end_month_2',\n",
    "    'EndDay2': 'end_day_2'\n",
    "}\n",
    "part_df_1.rename(part_df_1_renaming, axis=1, inplace=True)\n",
    "part_df_1 = deepcopy(part_df_1[list(part_df_1_renaming.values())])\n",
    "part_df_1 = deepcopy(the_networks_of_war_python_functions.start_and_end_dates(part_df_1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Manually changing USSR to side 3 for WWII based on dyadic data.\n",
      "Manually changing Finland to side 3 for WWII based on dyadic data.\n"
     ]
    }
   ],
   "source": [
    "print('Manually changing USSR to side 3 for WWII based on dyadic data.')\n",
    "## manually changing USSR to side 3 for WWII because they invaded Poland before fighting against Germany.\n",
    "part_df_1.loc[(part_df_1['war_num']==139) & (part_df_1['c_code']==365), 'side'] = 3\n",
    "\n",
    "print('Manually changing Finland to side 3 for WWII based on dyadic data.')\n",
    "## manually changing Finald to side 3 for WWII because they fought with Germany before vighting against Germany.\n",
    "part_df_1.loc[(part_df_1['war_num']==139) & (part_df_1['c_code']==375), 'side'] = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "template_replacement_dic = {\n",
    "\n",
    "    'SOURCE_TABLE': 'part_df_1',\n",
    "    'C_CODE': 'c_code',\n",
    "    'PARTICIPANT': 'participant',\n",
    "    'BATTLE_DEATHS': 'battle_deaths',\n",
    "    'a.LAGGING_WAR': 'null',\n",
    "    'a.LEADING_WAR': 'null',\n",
    "#     'a.DAYS_AT_WAR': 'null',\n",
    "    'a.DEATHS_BOTH_SIDES': 'null',\n",
    "    'a.PEAK_FORCES_AVAILABLE': 'null',\n",
    "    'a.PEAK_BATTLE_FORCES': 'null',\n",
    "    'SIDE': 'side'\n",
    "}\n",
    "query_text = deepcopy(part_query_template)\n",
    "for replacement in template_replacement_dic.keys():\n",
    "    query_text = deepcopy(query_text.replace(replacement, template_replacement_dic[replacement]))\n",
    "part_df_1 = deepcopy(pysqldf(query_text))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intra-State Wars\n",
    "#### Using the same file to create the participant dataframe and dyadic dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "## creating new dataframe to union to interstate wars part_df\n",
    "## note: the code below is very intefficient because something is off with integer formatting in this file.\n",
    "## this did not occur in the interstate war file.\n",
    "dyad_df_2 = pd.read_csv(csv_directory + 'INTRA-STATE_State_participants v5.1.csv', encoding='latin-1')\n",
    "## either one of these a or b may not actually be states.\n",
    "## this wil be fixed later on\n",
    "dyad_df_2_renaming = {\n",
    "\n",
    "    'WarNum': 'war_num',\n",
    "    'WarName': 'war_name',\n",
    "    'WarType': 'war_type',\n",
    "    'CcodeA': 'c_code_a',\n",
    "    'CcodeB': 'c_code_b',\n",
    "    'SideA': 'participant_a',\n",
    "    'SideB': 'participant_b',\n",
    "    'StartYr1': 'start_year_1',\n",
    "    'StartMo1': 'start_month_1',\n",
    "    'StartDy1': 'start_day_1',\n",
    "    'StartYr2': 'start_year_2',\n",
    "    'StartMo2': 'start_month_2',\n",
    "    'StartDy2': 'start_day_2',\n",
    "    'StartYr3': 'start_year_3',\n",
    "    'StartMo3': 'start_month_3',\n",
    "    'StartDy3': 'start_day_3',\n",
    "    'StartYr4': 'start_year_4',\n",
    "    'StartMo4': 'start_month_4',\n",
    "    'StartDy4': 'start_day_4',\n",
    "    'EndYr1': 'end_year_1',\n",
    "    'EndMo1': 'end_month_1',\n",
    "    'EndDy1': 'end_day_1',\n",
    "    'EndYr2': 'end_year_2',\n",
    "    'EndMo2': 'end_month_2',\n",
    "    'EndDy2': 'end_day_2',\n",
    "    'EndYr3': 'end_year_3',\n",
    "    'EndMo3': 'end_month_3',\n",
    "    'EndDy3': 'end_day_3',\n",
    "    'EndYr4': 'end_year_4',\n",
    "    'EndMo4': 'end_month_4',\n",
    "    'EndDy4': 'end_day_4',\n",
    "    'TransFrom': 'lagging_war',\n",
    "    'TransTo': 'leading_war',\n",
    "    ## unsure if these are the same as battle deaths, or include civilians\n",
    "    'Deaths A': 'battle_deaths_a',\n",
    "    'Deaths B': 'battle_deaths_b',\n",
    "    ## according to documentation, this includes both sides\n",
    "    'TotalBDeaths': 'deaths_both_sides',\n",
    "#     'WDuratDays': 'days_at_war',\n",
    "    'SideAPeakTotForces': 'peak_forces_available_a',\n",
    "    'SideBPeakTotForces': 'peak_forces_available_b',\n",
    "    'SideAPeak TheatForces': 'peak_battle_forces_a',\n",
    "    'SideBPeakTheatForces': 'peak_battle_forces_b'\n",
    "}\n",
    "dyad_df_2.rename(dyad_df_2_renaming, axis=1, inplace = True)\n",
    "dyad_df_2 = deepcopy(dyad_df_2[list(dyad_df_2_renaming.values())])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "## adjusting for wrong start year\n",
    "## this needs to be automated (check for  'of ___' in war_name where start_year <> ___)\n",
    "dyad_df_2.loc[dyad_df_2['war_num']==976, 'start_year_1'] = 2011\n",
    "\n",
    "## two syrian arab spring wars starting on the same date.\n",
    "## I believe one of these is a data entry error.\n",
    "## combining both war_nums into one war\n",
    "dyad_df_2.loc[dyad_df_2['war_num']==977, 'war_num'] = 979\n",
    "\n",
    "## fixing Unknown end_year for ongoing_wars.\n",
    "negative_7_wars = [992.5, 942, 991, 990.4, 991.4]\n",
    "dyad_df_2.loc[([s in negative_7_wars for s in dyad_df_2['war_num']]), 'end_year_1'] = -7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Rows With Both Dates Found: 593\n",
      "Total Rows With At Least One Date Not Found: 0\n",
      "Total Estimated Start Dates: 80\n",
      "Total Estimated End Dates: 92\n",
      "\n"
     ]
    }
   ],
   "source": [
    "format_part_df_return = deepcopy(the_networks_of_war_python_functions.process_dyadic_data(dyad_df_2))\n",
    "part_df_2 = deepcopy(format_part_df_return[0])\n",
    "dyad_df_2 = deepcopy(format_part_df_return[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "template_replacement_dic = {\n",
    "    'SOURCE_TABLE': 'part_df_2',\n",
    "    'C_CODE': 'c_code',\n",
    "    'PARTICIPANT': 'participant',\n",
    "    'BATTLE_DEATHS': 'battle_deaths',\n",
    "    'LAGGING_WAR': 'lagging_war',\n",
    "    'LEADING_WAR': 'leading_war',\n",
    "#     'DAYS_AT_WAR': 'days_at_war',\n",
    "    'DEATHS_BOTH_SIDES': 'deaths_both_sides',\n",
    "    'PEAK_FORCES_AVAILABLE': 'peak_forces_available',\n",
    "    'PEAK_BATTLE_FORCES': 'peak_battle_forces',\n",
    "    'SIDE': 'side'\n",
    "}\n",
    "query_text = deepcopy(part_query_template)\n",
    "for replacement in template_replacement_dic.keys():\n",
    "    query_text = deepcopy(query_text.replace(replacement, template_replacement_dic[replacement]))\n",
    "part_df_2 = deepcopy(pysqldf(query_text))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extra State Wars\n",
    "#### Using the same file to create the participant dataframe and dyadic dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Rows With Both Dates Found: 198\n",
      "Total Rows With At Least One Date Not Found: 0\n",
      "Total Estimated Start Dates: 46\n",
      "Total Estimated End Dates: 51\n",
      "\n"
     ]
    }
   ],
   "source": [
    "## creating new dataframe to union to extra-state wars part_df\n",
    "## inefficient pipe_Zline from above was used to accomodate integer formatting\n",
    "## unsure if that problem occurs for this one too though\n",
    "dyad_df_3 = pd.read_csv(csv_directory + 'Extra-StateWarData_v4.0.csv', encoding='latin-1')\n",
    "## either one of these a or b may not actually be states.\n",
    "## this wil be fixed later on\n",
    "dyad_df_3_renaming = {\n",
    "\n",
    "    'WarNum': 'war_num',\n",
    "    'WarName': 'war_name',\n",
    "    'WarType': 'war_type',\n",
    "    'ccode1': 'c_code_a',\n",
    "    'ccode2': 'c_code_b',\n",
    "    'SideA': 'participant_a',\n",
    "    'SideB': 'participant_b',\n",
    "    'StartYear1': 'start_year_1',\n",
    "    'StartMonth1': 'start_month_1',\n",
    "    'StartDay1': 'start_day_1',\n",
    "    'StartYear2': 'start_year_2',\n",
    "    'StartMonth2': 'start_month_2',\n",
    "    'StartDay2': 'start_day_2',\n",
    "    'EndYear1': 'end_year_1',\n",
    "    'EndMonth1': 'end_month_1',\n",
    "    'EndDay1': 'end_day_1',\n",
    "    'EndYear2': 'end_year_2',\n",
    "    'EndMonth2': 'end_month_2',\n",
    "    'EndDay2 ': 'end_day_2',\n",
    "    'TransFrom': 'lagging_war',\n",
    "    'TransTo': 'leading_war',\n",
    "    ## unsure if these are the same as battle deaths, or include civilians\n",
    "    'BatDeath': 'battle_deaths_a',\n",
    "    'NonStateDeaths': 'battle_deaths_b'\n",
    "}\n",
    "dyad_df_3.rename(dyad_df_3_renaming, axis=1, inplace=True)\n",
    "dyad_df_3 = deepcopy(dyad_df_3[list(dyad_df_3_renaming.values())])\n",
    "\n",
    "format_part_df_return = deepcopy(the_networks_of_war_python_functions.process_dyadic_data(dyad_df_3))\n",
    "part_df_3 = deepcopy(format_part_df_return[0])\n",
    "dyad_df_3 = deepcopy(format_part_df_return[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "template_replacement_dic = {\n",
    "    \n",
    "    'SOURCE_TABLE': 'part_df_3',\n",
    "    'C_CODE': 'c_code',\n",
    "    'PARTICIPANT': 'participant',\n",
    "    'BATTLE_DEATHS': 'battle_deaths',\n",
    "    'LAGGING_WAR': 'lagging_war',\n",
    "    'LEADING_WAR': 'leading_war',\n",
    "#     'a.DAYS_AT_WAR': 'null',\n",
    "    'a.DEATHS_BOTH_SIDES': 'null',\n",
    "    'a.PEAK_FORCES_AVAILABLE': 'null',\n",
    "    'a.PEAK_BATTLE_FORCES': 'null',\n",
    "    'SIDE': 'side'\n",
    "}\n",
    "query_text = deepcopy(part_query_template)\n",
    "for replacement in template_replacement_dic.keys():\n",
    "    query_text = deepcopy(query_text.replace(replacement, template_replacement_dic[replacement]))\n",
    "part_df_3 = deepcopy(pysqldf(query_text))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining Participant Sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total War Participants After Merging All War Types: 1,705\n",
      "Total Participants With Start Date > End Date: 0\n"
     ]
    }
   ],
   "source": [
    "## removing non applicable participants\n",
    "## don't need to do this for inter-state war because all is applicable\n",
    "    \n",
    "query_text = \"\"\"\n",
    "\n",
    "select\n",
    "    *\n",
    "from part_df_1\n",
    "union\n",
    "select\n",
    "    *\n",
    "from part_df_2\n",
    "union\n",
    "select\n",
    "    *\n",
    "from part_df_3\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "part_df = deepcopy(pysqldf(query_text))\n",
    "\n",
    "print('Total War Participants After Merging All War Types: {}'.format(format(len(part_df), ',d')))\n",
    "print('Total Participants With Start Date > End Date: {}'.format(format(int(len(part_df[part_df['start_date'] > part_df['end_date']])/2), ',d')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Dictionary to Lookup Wars Individually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "## creating war_dic that will be used to fill in missing values\n",
    "war_dic = the_networks_of_war_python_functions.dictionary_from_field(part_df, 'war_num', 'war_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Inter-State Dyadic DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note: The other dyadic dataframes have already been defined above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in the data and unioning each participant so they both appear as a and b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Rows With Both Dates Found: 1,364\n",
      "Total Rows With At Least One Date Not Found: 0\n",
      "Total Estimated Start Dates: 0\n",
      "Total Estimated End Dates: 0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "## battle deaths and start/end dates are in this file too, but it's more confusing than the part_df.\n",
    "## this will just be used to get the combinations of countries directly at war with each other.\n",
    "dyad_df_1 = pd.read_csv(csv_directory + 'directed_dyadic_war.csv', encoding='latin-1')\n",
    "\n",
    "## including columns that need to be included later on (that don't need name changes)\n",
    "dyad_df_1_renaming = {\n",
    "\n",
    "    'warnum': 'war_num',\n",
    "    'statea': 'c_code_a',\n",
    "    'stateb': 'c_code_b',\n",
    "    'batdtha': 'battle_deaths_a',\n",
    "    'batdthb': 'battle_deaths_b',\n",
    "    'batdths': 'deaths_both_sides',\n",
    "    'warstrtyr': 'start_year_1',\n",
    "    'warstrtmnth': 'start_month_1',\n",
    "    'warstrtday': 'start_day_1',\n",
    "    'warendyr': 'end_year_1',\n",
    "    'warendmnth': 'end_month_1',\n",
    "    'warenday': 'end_day_1',\n",
    "    'disno': 'disno'\n",
    "}\n",
    "dyad_df_1.rename(dyad_df_1_renaming, axis=1, inplace = True)\n",
    "dyad_df_1 = deepcopy(dyad_df_1[list(dyad_df_1_renaming.values())])\n",
    "\n",
    "## fixing data entry errors\n",
    "dyad_df_1.loc[dyad_df_1['start_month_1']==24, 'start_month_1'] = 12\n",
    "dyad_df_1.loc[dyad_df_1['end_year_1']==19118, 'end_year_1'] = 1918\n",
    "\n",
    "format_part_df_return = deepcopy(the_networks_of_war_python_functions.process_dyadic_data(dyad_df_1))\n",
    "part_df_1_1 = deepcopy(format_part_df_return[0])\n",
    "dyad_df_1 = deepcopy(format_part_df_return[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining Dyadic Sources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Combine dyadic tables for 3 war types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Unique Dyads After Merging All War Types: 878\n",
      "Note: This is step 1 of 3 to getting the actual total for unique dyads.\n"
     ]
    }
   ],
   "source": [
    "dyad_df = deepcopy(pd.concat([dyad_df_1, dyad_df_2], sort=True, ignore_index=True).reset_index(drop=True))\n",
    "dyad_df = deepcopy(pd.concat([dyad_df, dyad_df_3], sort=True, ignore_index=True).reset_index(drop=True))\n",
    "print('Total Unique Dyads After Merging All War Types: {}'.format(format(int(len(dyad_df)/2), ',d')))\n",
    "print('Note: This is step 1 of 3 to getting the actual total for unique dyads.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Add missing dyads from MIDs marked as war==1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Rows With Both Dates Found: 948\n",
      "Total Rows With At Least One Date Not Found: 0\n",
      "Total Estimated Start Dates: 0\n",
      "Total Estimated End Dates: 0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "### Checking for any missing dyads that can be extracted from MID data\n",
    "### Adding in Dyads (Not Included) Marked as War==1 in MID Data\n",
    "mid_df = pd.read_csv(csv_directory + 'dyadic_mid_4.02.csv', encoding='latin-1')\n",
    "\n",
    "# data source documentation: Dyadic MID Codebook V4.0.pdf\n",
    "battle_deaths_mapping = {\n",
    "\n",
    "    -8: None,\n",
    "    -9: None,\n",
    "    0: 0,\n",
    "    1: 25,\n",
    "    2: 100,\n",
    "    3: 250,\n",
    "    4: 500,\n",
    "    5: 999,\n",
    "    6: 1000\n",
    "}\n",
    "## these will need to be made priority 2 in join with dyadic data.\n",
    "for mapping_key in list(battle_deaths_mapping.keys()):\n",
    "    mid_df.loc[mid_df['fatleva']==mapping_key, 'fatleva'] = battle_deaths_mapping[mapping_key]\n",
    "    mid_df.loc[mid_df['fatlevb']==mapping_key, 'fatlevb'] = battle_deaths_mapping[mapping_key]\n",
    "    \n",
    "## including columns that need to be included later on (that don't need name changes)\n",
    "mid_df_renaming = {\n",
    "    \n",
    "    'war': 'war',\n",
    "    'statea': 'c_code_a',\n",
    "    'stateb': 'c_code_b',\n",
    "    'fatleva': 'battle_deaths_est_a',\n",
    "    'fatlevb': 'battle_deaths_est_b',\n",
    "    'strtyr': 'start_year_1',\n",
    "    'strtmnth': 'start_month_1',\n",
    "    'strtday': 'start_day_1',\n",
    "    'endyear': 'end_year_1',\n",
    "    'endmnth': 'end_month_1',\n",
    "    'endday': 'end_day_1',\n",
    "    'disno': 'disno'\n",
    "}\n",
    "\n",
    "mid_df.rename(mid_df_renaming, axis=1, inplace=True)\n",
    "mid_df = deepcopy(mid_df[list(mid_df_renaming.values())])\n",
    "mid_df = deepcopy(mid_df[mid_df['war']==1].reset_index(drop=True))\n",
    "mid_df = deepcopy(the_networks_of_war_python_functions.union_opposite_columns(mid_df))\n",
    "mid_df = deepcopy(the_networks_of_war_python_functions.start_and_end_dates(mid_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "## FIX TABLE BELOW TO MAKE RESULTS COMPLETE\n",
    "\n",
    "query_text = \"\"\"\n",
    "    \n",
    "select\n",
    "    a.disno,\n",
    "    a.war_num,\n",
    "    b.war_name\n",
    "from dyad_df_1 a\n",
    "left join part_df b on a.war_num = b.war_num\n",
    "group by 1, 2, 3\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "disno_war_num_df = deepcopy(pysqldf(query_text))\n",
    "\n",
    "query_text = \"\"\"\n",
    "\n",
    "select\n",
    "    coalesce(dwn.war_num, -1) as war_num,\n",
    "    a.c_code_a,\n",
    "    cca.state_name as participant_a,\n",
    "    a.c_code_b,\n",
    "    ccb.state_name as participant_b,\n",
    "    sum(max(a.battle_deaths_est_a, 0)) as battle_deaths_est_a,\n",
    "    sum(max(a.battle_deaths_est_b, 0)) as battle_deaths_est_b,\n",
    "    min(a.start_date) as start_date,\n",
    "    cast(strftime('%Y', min(a.start_date)) as integer) as start_year,\n",
    "    max(a.end_date) as end_date,\n",
    "    cast(strftime('%Y', max(a.end_date)) as integer) as end_year\n",
    "from mid_df a\n",
    "left join disno_war_num_df dwn on a.disno = dwn.disno\n",
    "left join c_code_df cca on a.c_code_a = cca.c_code\n",
    "left join c_code_df ccb on a.c_code_b = ccb.c_code\n",
    "group by 1, 2, 3, 4, 5\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "mid_df = deepcopy(pysqldf(query_text))\n",
    "\n",
    "mid_df.to_pickle(pickle_directory + 'mid_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/Cellar/jupyterlab/3.0.10/libexec/lib/python3.9/site-packages/pandas/core/reshape/merge.py:1141: UserWarning: You are merging on int and float columns where the float values are not equal to their int representation\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Unique Dyads Added From MIDs: 14\n",
      "Note: These have all been manually reviewed in assigning war numbers.\n"
     ]
    }
   ],
   "source": [
    "dyad_df = deepcopy(pd.merge(dyad_df, mid_df, how='outer', on=['war_num',\n",
    "                                                              'c_code_a',\n",
    "                                                              'c_code_b',\n",
    "                                                              'participant_a',\n",
    "                                                              'participant_b',\n",
    "                                                              'start_year',\n",
    "                                                              'start_date',\n",
    "                                                              'end_year',\n",
    "                                                              'end_date']))\n",
    "\n",
    "aggregations = {\n",
    "    \n",
    "    'battle_deaths_a': 'sum',\n",
    "    'battle_deaths_b': 'sum',\n",
    "    'battle_deaths_est_a': 'sum',\n",
    "    'battle_deaths_est_b': 'sum',\n",
    "    'start_date': 'min',\n",
    "    'start_year': 'min',\n",
    "    'end_date': 'max',\n",
    "    'end_year': 'max'\n",
    "}\n",
    "dyad_df = deepcopy(dyad_df.groupby(['war_num', 'c_code_a', 'c_code_b', 'participant_a', 'participant_b']).agg(aggregations).reset_index())\n",
    "\n",
    "total_dyads_added = deepcopy(len(dyad_df[dyad_df['war_num']==-1]))\n",
    "\n",
    "## these have been manually reviewed to all be WWII dyads\n",
    "dyad_df.loc[(dyad_df['war_num']==-1) & (dyad_df['start_year']<=1945), 'war_num'] = 139\n",
    "\n",
    "missing_dyads = [\n",
    "    \n",
    "    [483, 500],\n",
    "    [483, 517],\n",
    "    [490, 500],\n",
    "    [490, 517],\n",
    "    [500, 540],\n",
    "    [500, 552],\n",
    "    [500, 565],\n",
    "    [517, 540],\n",
    "    [517, 552],\n",
    "    [517, 565]\n",
    "]\n",
    "\n",
    "for dyad in missing_dyads:\n",
    "    ## independently verified to all be part of Africa's World War\n",
    "    dyad_df.loc[(dyad_df['war_num']==-1) & (dyad_df['c_code_a']==dyad[0]) & (dyad_df['c_code_b']==dyad[1]), 'war_num'] = 905\n",
    "    dyad_df.loc[(dyad_df['war_num']==-1) & (dyad_df['c_code_a']==dyad[1]) & (dyad_df['c_code_b']==dyad[0]), 'war_num'] = 905\n",
    "\n",
    "## should be 14 (28 if counted with unioned c_codes)\n",
    "if total_dyads_added!=28:\n",
    "    raise Exception('There are usually 28 MIDs added here.')\n",
    "\n",
    "print('Total Unique Dyads Added From MIDs: {}'.format(format(int(float(total_dyads_added)/2), ',d')))\n",
    "print('Note: These have all been manually reviewed in assigning war numbers.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Unique Dyads After Merging All War Types AND Adding Missing Dyads from MIDs: 891\n",
      "Note: This is step 2 of 3 to getting the actual total for unique dyads.\n",
      "\n",
      "Total Unique Dyads With Unknown War Numbers: 1\n",
      "Total Unique Dyads With Battle Deaths A or B Estimated from MIDs: 18\n",
      "Total Dyads With Start Date > End Date: 0\n"
     ]
    }
   ],
   "source": [
    "## combining the two battle deaths fields with the estimated value for battle deaths as priority 2.\n",
    "## battle_deaths_est_a and battle_deaths_est_b will become binaries.\n",
    "query_text = \"\"\"\n",
    "\n",
    "select\n",
    "    war_num,\n",
    "    c_code_a,\n",
    "    c_code_b,\n",
    "    participant_a,\n",
    "    participant_b,\n",
    "    coalesce(nullif(battle_deaths_a, 0), battle_deaths_est_a, 0) as battle_deaths_a,\n",
    "    coalesce(nullif(battle_deaths_b, 0), battle_deaths_est_b, 0) as battle_deaths_b,\n",
    "    case when nullif(battle_deaths_a, 0) is null and battle_deaths_est_a > 0 then 1 else 0 end as battle_deaths_est_a,\n",
    "    case when nullif(battle_deaths_b, 0) is null and battle_deaths_est_b > 0 then 1 else 0 end as battle_deaths_est_b,\n",
    "    min(start_date) as start_date,\n",
    "    min(start_year) as start_year,\n",
    "    max(end_date) as end_date,\n",
    "    max(end_year) as end_year\n",
    "from dyad_df\n",
    "group by 1, 2, 3, 4, 5, 6, 7, 8, 9\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "dyad_df = deepcopy(pysqldf(query_text))\n",
    "print('Total Unique Dyads After Merging All War Types AND Adding Missing Dyads from MIDs: {}'.format(format(int(len(dyad_df)/2), ',d')))\n",
    "print('Note: This is step 2 of 3 to getting the actual total for unique dyads.')\n",
    "\n",
    "print('\\nTotal Unique Dyads With Unknown War Numbers: {}'.format(format(int(len(dyad_df[dyad_df['war_num']==-1])/2), ',d')))\n",
    "print('Total Unique Dyads With Battle Deaths A or B Estimated from MIDs: {}'.format(format(int(len(dyad_df[(dyad_df['battle_deaths_est_a']==1) | (dyad_df['battle_deaths_est_b']==1)])/2), ',d')))\n",
    "print('Total Dyads With Start Date > End Date: {}'.format(format(int(len(dyad_df[dyad_df['start_date'] > dyad_df['end_date']])), ',d')))\n",
    "\n",
    "dyad_df.to_pickle(pickle_directory + 'dyad_df.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final Data Adjustments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adjusting participant names for part_df and dyad_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adjusting and consolidating participant names for part_df.\n",
      "Adjusting and consolidating participant names for dyad_df.\n"
     ]
    }
   ],
   "source": [
    "part_df = deepcopy(the_networks_of_war_python_functions.adjust_participant_names(part_df, 'participant'))\n",
    "dyad_df = deepcopy(the_networks_of_war_python_functions.adjust_participant_names(dyad_df, 'dyad'))\n",
    "\n",
    "part_df.to_pickle(pickle_directory + 'part_df.pkl')\n",
    "dyad_df.to_pickle(pickle_directory + 'dyad_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# part_df = pd.read_pickle(pickle_directory + 'part_df.pkl')\n",
    "# dyad_df = pd.read_pickle(pickle_directory + 'dyad_df.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Addressing Missing Values from both Participant and Dyadic Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding in Missing Participants that Appear in Dyadic Data for War"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Total Participants Added from Dyadic Data: 10\n",
      "Total Participants With Start Date > End Date: 0\n"
     ]
    }
   ],
   "source": [
    "original_part_df_length = deepcopy(len(part_df))\n",
    "\n",
    "## combining part_df with dyad_df (for participants in dyad_df not appearing in part_df)\n",
    "query_text = \"\"\"\n",
    "\n",
    "with\n",
    "\n",
    "war_num_table as (\n",
    "\n",
    "    select\n",
    "        war_num,\n",
    "        war_name,\n",
    "        war_type_code,\n",
    "        war_type,\n",
    "        war_subtype\n",
    "    from part_df\n",
    "    group by 1, 2, 3, 4, 5)\n",
    "\n",
    "\n",
    "select\n",
    "    war_num,\n",
    "    war_name,\n",
    "    war_type_code,\n",
    "    war_type,\n",
    "    war_subtype,\n",
    "    c_code,\n",
    "    participant,\n",
    "    side,\n",
    "    battle_deaths,\n",
    "    0 as battle_deaths_estimated,\n",
    "    start_year,\n",
    "    start_date,\n",
    "    end_year,\n",
    "    end_date,\n",
    "    ongoing_conflict,\n",
    "    start_date_estimated,\n",
    "    end_date_estimated,\n",
    "    days_not_at_war,\n",
    "    lagging_war,\n",
    "    leading_war,\n",
    "    deaths_both_sides,\n",
    "    peak_forces_available,\n",
    "    peak_battle_forces\n",
    "from part_df\n",
    "union\n",
    "select\n",
    "    a.war_num,\n",
    "    coalesce(wn.war_name, '') as war_name,\n",
    "    wn.war_type_code,\n",
    "    wn.war_type,\n",
    "    wn.war_subtype,\n",
    "    a.c_code_a as c_code,\n",
    "    a.participant_a as participant,\n",
    "    null as side,\n",
    "    sum(a.battle_deaths_a) as battle_deaths,\n",
    "    max(a.battle_deaths_est_a) as battle_deaths_estimated,\n",
    "    min(a.start_year) as start_year,\n",
    "    min(a.start_date) as start_date,\n",
    "    min(a.end_year) as end_year,\n",
    "    min(a.end_date) as end_date,\n",
    "    null as ongoing_conflict,\n",
    "    null as start_date_estimated,\n",
    "    null as end_date_estimated,\n",
    "    null as days_not_at_war,\n",
    "    null as lagging_war,\n",
    "    null as leading_war,\n",
    "    null as deaths_both_sides,\n",
    "    null as peak_forces_available,\n",
    "    null as peak_battle_forces\n",
    "from dyad_df a\n",
    "left join part_df b on a.war_num = b.war_num and a.c_code_a = b.c_code and a.participant_a = b.participant\n",
    "left join war_num_table wn on a.war_num = wn.war_num\n",
    "where\n",
    "    b.c_code is null\n",
    "group by 1, 2, 3, 4, 5, 6, 7\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "part_df = deepcopy(pysqldf(query_text))\n",
    "\n",
    "participants_added = deepcopy(len(part_df) - original_part_df_length)\n",
    "print('\\nTotal Participants Added from Dyadic Data: {}'.format(format(participants_added, ',d')))\n",
    "print('Total Participants With Start Date > End Date: {}'.format(format(int(len(part_df[part_df['start_date'] > part_df['end_date']])/2), ',d')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Assigning a side to Lebanon for .\n",
      "No single side found for Lebanon in .\n",
      "\n",
      "Assigning a side to Israel for .\n",
      "No single side found for Israel in .\n",
      "\n",
      "Assigning a side to United Kingdom for Latvian Liberation.\n",
      "Assigning a side to France for Latvian Liberation.\n",
      "Assigning a side to Poland for Latvian Liberation.\n",
      "Assigning a side to Spain for World War II.\n",
      "Assigning a side to Thailand for World War II.\n",
      "Assigning a side to New Zealand for Korean.\n",
      "Assigning a side to Iraq for Six Day War.\n",
      "Assigning a side to Greece for Turco-Cypriot.\n"
     ]
    }
   ],
   "source": [
    "## giving this to the opposite side of the participant they fought against in the war (grabs first record)\n",
    "## troublesome if they swithced sides but this would be very rare\n",
    "opposing_side_dic = {\n",
    "    \n",
    "    1: 2,\n",
    "    2: 1,\n",
    "    3: 3\n",
    "}\n",
    "\n",
    "added_participants = part_df[part_df['side'].isnull()][['war_num', 'c_code']].values\n",
    "\n",
    "for i, added_participant in enumerate(added_participants):\n",
    "\n",
    "    print('Assigning a side to {} for {}.'.format(part_df[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==added_participant[1])]['participant'].values[0], part_df[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==added_participant[1])]['war_name'].values[0]))\n",
    "    \n",
    "    opposing_parties = dyad_df[(dyad_df['war_num']==added_participant[0]) & (dyad_df['c_code_a']==added_participant[1])]['c_code_b'].values\n",
    "    part_sides = []\n",
    "    for opposing_party in opposing_parties:\n",
    "        try:\n",
    "            part_sides.append(opposing_side_dic[part_df[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==opposing_party)]['side'].values[0]])\n",
    "        except:\n",
    "            pass\n",
    "        \n",
    "    part_side = list(set(part_sides))\n",
    "    if len(part_side)==1:\n",
    "        part_df.loc[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==added_participant[1]), 'side'] = part_side\n",
    "    else:\n",
    "        print('No single side found for {} in {}.\\n'.format(part_df[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==added_participant[1])]['participant'].values[0], part_df[(part_df['war_num']==added_participant[0]) & (part_df['c_code']==added_participant[1])]['war_name'].values[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Manually adding battle deaths from Wikipedia for Thailand during WWII.\n"
     ]
    }
   ],
   "source": [
    "# missing value for thailand battle deaths in WWII obtained from wikipedia\n",
    "# https://en.wikipedia.org/wiki/Thailand_in_World_War_II#:~:text=Thailand%20suffered%20about%205%2C569%20military,the%20brief%20Franco%2DThai%20War.\n",
    "print('Manually adding battle deaths from Wikipedia for Thailand during WWII.')\n",
    "part_df.loc[(part_df['war_num']==139) & (part_df['c_code']==800), 'battle_deaths'] = 5569"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding in Missing Dyads for Wars with Only One Possible Adverary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking for floating participants with only one possible adversary, or one definite adversary.\n",
      "\n",
      "Total Unique Dyads After Step 3 of 3: 1,866\n",
      "Total Unique Dyads Added in Current Cell: 975\n",
      "\n",
      "Total Dyads with Null Start Years: 0\n",
      "Total Dyads with Null End Years: 0\n",
      "Total Dyads With Start Date > End Date: 0\n",
      "\n",
      "Total Particpants with Null Start Years: 0\n",
      "Total Particpants with Null End Years: 0\n",
      "Total Participants With Start Date > End Date: 0\n"
     ]
    }
   ],
   "source": [
    "## need to figure out a way to add dyadic data when it's missing.\n",
    "## these are clear cases where it should be added because one side on the war is only one country.\n",
    "## or, both sides are only one country\n",
    "## it'll be trickier when each side isn't just one country.\n",
    "## that will lead to a floating noad that isn't grounded in the network analysis graph\n",
    "\n",
    "original_dyad_df_length = deepcopy(len(dyad_df))\n",
    "\n",
    "for war_num in list(part_df['war_num'].unique()):\n",
    "    \n",
    "    part_df_copy = deepcopy(part_df[part_df['war_num']==war_num].reset_index(drop=True))\n",
    "    total_side_1 = len(list(part_df_copy[part_df_copy['side']==1]['participant'].unique()))\n",
    "    total_side_2 = len(list(part_df_copy[part_df_copy['side']==2]['participant'].unique()))\n",
    "    ## non-state only\n",
    "    total_side_1_non_state = len(list(part_df_copy[(part_df_copy['side']==1) & (part_df_copy['c_code']==-8)]['participant'].unique()))\n",
    "    total_side_2_non_state = len(list(part_df_copy[(part_df_copy['side']==2) & (part_df_copy['c_code']==-8)]['participant'].unique()))\n",
    "\n",
    "    ## state only\n",
    "    total_side_1_state = len(list(part_df_copy[(part_df_copy['side']==1) & (part_df_copy['c_code']>0)]['c_code'].unique()))\n",
    "    total_side_2_state = len(list(part_df_copy[(part_df_copy['side']==2) & (part_df_copy['c_code']>0)]['c_code'].unique()))\n",
    "        \n",
    "    if total_side_1==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 1, 'all_participants', None).reset_index(drop=True))\n",
    "    elif total_side_2==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 2, 'all_participants', None).reset_index(drop=True))\n",
    "    ## if the above processes fail, at the very least we can link all opposing parties to the non-state participant on the other side\n",
    "    ## if there is only on non-state participant on the other side, we know that all opposing forces fought against them\n",
    "    ## the thought here is that if there is only one non-state participant on a particular side, we can assume all members of the other side fought against them because this isn't an inter-state war\n",
    "    elif total_side_1_non_state==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 1, 'non-state', None).reset_index(drop=True))\n",
    "    elif total_side_2_non_state==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 2, 'non-state', None).reset_index(drop=True))\n",
    "    else:\n",
    "        pass\n",
    "\n",
    "    ## below is the case where only one state participant was on a particular side in a non-state war.\n",
    "    ## assuming that this country fought with all opposing parties.\n",
    "    ## filling in any networks with only one state participant on a particular side\n",
    "    ## see Eritrea in Third Somalia War\n",
    "    if total_side_1_state==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 1, 'state', None).reset_index(drop=True))\n",
    "    elif total_side_2_state==1:\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, 2, 'state', None).reset_index(drop=True))\n",
    "    else:\n",
    "        pass\n",
    "\n",
    "    ## manually intervening to link specific participants with ALL opposing participants.\n",
    "    if war_num==820:\n",
    "        participant_name = 'France'\n",
    "        side = part_df_copy[part_df_copy['participant']==participant_name]['side'].values[0]\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, side, 'manual', participant_name).reset_index(drop=True))\n",
    "        \n",
    "        participant_name = 'Democratic Republic of the Congo'\n",
    "        side = part_df_copy[part_df_copy['participant']==participant_name]['side'].values[0]\n",
    "        dyad_df = deepcopy(the_networks_of_war_python_functions.add_missing_dyads(part_df_copy, dyad_df, war_num, side, 'manual', participant_name).reset_index(drop=True))\n",
    "\n",
    "## unioning to obtain all combinations of dyads that were added\n",
    "dyad_df = deepcopy(the_networks_of_war_python_functions.union_opposite_columns(dyad_df))\n",
    "\n",
    "dyads_added = deepcopy(len(dyad_df) - original_dyad_df_length)\n",
    "print('Checking for floating participants with only one possible adversary, or one definite adversary.\\n')\n",
    "print('Total Unique Dyads After Step 3 of 3: {}'.format(format(int(len(dyad_df)/2), ',d')))\n",
    "print('Total Unique Dyads Added in Current Cell: {}'.format(format(int(dyads_added/2), ',d')))\n",
    "print('\\nTotal Dyads with Null Start Years: {}'.format(len(dyad_df[dyad_df['start_year'].isnull()])))\n",
    "print('Total Dyads with Null End Years: {}'.format(len(dyad_df[dyad_df['end_year'].isnull()])))\n",
    "print('Total Dyads With Start Date > End Date: {}'.format(format(int(len(dyad_df[dyad_df['start_date'] > dyad_df['end_date']])), ',d')))\n",
    "print('\\nTotal Particpants with Null Start Years: {}'.format(len(part_df[part_df['start_year'].isnull()])))\n",
    "print('Total Particpants with Null End Years: {}'.format(len(part_df[part_df['end_year'].isnull()])))\n",
    "print('Total Participants With Start Date > End Date: {}'.format(format(int(len(part_df[part_df['start_date'] > part_df['end_date']])), ',d')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Total Dyads Added From Adding Years Between Start_Year and End_Year: 7,956\n"
     ]
    }
   ],
   "source": [
    "years_df = pd.DataFrame(np.arange(1500, 2100), columns=['year'])\n",
    "\n",
    "original_dyad_df_length = deepcopy(len(dyad_df))\n",
    "\n",
    "query_text = \"\"\"\n",
    "\n",
    "select\n",
    "    a.war_num,\n",
    "    a.c_code_a,\n",
    "    a.c_code_b,\n",
    "    a.participant_a,\n",
    "    a.participant_b,\n",
    "    a.battle_deaths_a,\n",
    "    a.battle_deaths_b,\n",
    "    a.battle_deaths_est_a,\n",
    "    a.battle_deaths_est_b,\n",
    "    a.start_date,\n",
    "    a.start_year,\n",
    "    a.end_date,\n",
    "    a.end_year,\n",
    "    y.year\n",
    "from dyad_df a\n",
    "inner join years_df y on y.year between a.start_year and a.end_year\n",
    "group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "dyad_df = deepcopy(pysqldf(query_text))\n",
    "\n",
    "dyads_added = deepcopy(len(dyad_df) - original_dyad_df_length)\n",
    "print('\\nTotal Dyads Added From Adding Years Between Start_Year and End_Year: {}'.format(format(dyads_added, ',d')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Defining War DataFrame (One row for each war)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Wars Manually Changed to Ongoing: 0\n",
      "Total Wars Marked as Ongoing: 12\n",
      "Total Wars With Name Adjustments: 422\n"
     ]
    }
   ],
   "source": [
    "## part_df_copy will be used to calculate war_df.\n",
    "part_df_copy = deepcopy(part_df)\n",
    "part_df_copy.rename({'participant': 'participants'}, axis=1, inplace=True)\n",
    "\n",
    "war_name_changes = {}\n",
    "wars_changed_to_ongoing = []\n",
    "for i, war in enumerate(part_df_copy['war_name']):\n",
    "    original_war_name = part_df_copy.loc[i, 'war_name']\n",
    "    if part_df_copy.loc[i, 'ongoing_conflict']==1:\n",
    "        pass\n",
    "    elif 'present' in part_df_copy.loc[i, 'war_name'].lower() or 'ongoing' in part_df_copy.loc[i, 'war_name'].lower():\n",
    "        part_df_copy.loc[i, 'ongoing_conflict'] = 1\n",
    "        wars_changed_to_ongoing.append(part_df_copy.loc[i, 'war_num'])\n",
    "    if ' of 1' in part_df_copy.loc[i, 'war_name']:\n",
    "        part_df_copy.loc[i, 'war_name'] = part_df_copy.loc[i, 'war_name'].split(' of 1')[0].replace('  ', ' ')\n",
    "    elif ' of 2' in part_df_copy.loc[i, 'war_name']:\n",
    "        part_df_copy.loc[i, 'war_name'] = part_df_copy.loc[i, 'war_name'].split(' of 2')[0].replace('  ', ' ')\n",
    "    if len(part_df_copy.loc[0, 'war_name'])==0:\n",
    "        pass\n",
    "    elif str(part_df_copy.loc[i, 'war_name'])[0]==' ':\n",
    "        part_df_copy.loc[i, 'war_name'] = part_df_copy.loc[i, 'war_name'][1:]\n",
    "    if original_war_name!=part_df_copy.loc[i, 'war_name']:\n",
    "        war_name_changes[original_war_name] = part_df_copy.loc[i, 'war_name']\n",
    "        \n",
    "print('Total Wars Manually Changed to Ongoing: {}'.format(format(len(list(set(wars_changed_to_ongoing))), ',d')))\n",
    "print('Total Wars Marked as Ongoing: {}'.format(format(len(list(part_df_copy[part_df_copy['ongoing_conflict']==1]['war_num'].unique())), ',d')))\n",
    "print('Total Wars With Name Adjustments: {}'.format(format(len(war_name_changes.keys()), ',d')))\n",
    "# pprint(war_name_changes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Wars with Estimated Start Dates: 109\n",
      "Total (Non-Ongoing) Wars with Estimated End Dates: 94\n"
     ]
    }
   ],
   "source": [
    "### Creating Dictionary to Lookup Estimated Dates That Have Affected Days_At_War\n",
    "estimated_start_df = deepcopy(part_df_copy[part_df_copy['start_date_estimated']==1][['war_num', 'start_date']])\n",
    "estimated_start_dates = the_networks_of_war_python_functions.dictionary_from_field(estimated_start_df, 'war_num', 'start_date')\n",
    "\n",
    "estimated_end_df = deepcopy(part_df_copy[(part_df_copy['end_date_estimated']==1) & (part_df_copy['ongoing_conflict']==0)][['war_num', 'end_date']])\n",
    "estimated_end_dates = the_networks_of_war_python_functions.dictionary_from_field(estimated_end_df, 'war_num', 'end_date')\n",
    "\n",
    "print('Total Wars with Estimated Start Dates: {}'.format(len(list(estimated_start_df['war_num'].unique()))))\n",
    "print('Total (Non-Ongoing) Wars with Estimated End Dates: {}'.format(len(list(estimated_end_df['war_num'].unique()))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Wars with Null Start Years: 0\n",
      "Total (Non-Ongoing) Wars with Null End Years: 0\n",
      "Total Ongoing Wars: 12\n"
     ]
    }
   ],
   "source": [
    "aggregations = {\n",
    "    \n",
    "    'participants': 'count',\n",
    "    'start_date': 'min',\n",
    "    'start_year': 'min',\n",
    "    'end_date': 'max',\n",
    "    'end_year': 'max',\n",
    "    ## this will not be accurate if there are more than one lagging/leading wars per war.\n",
    "    'lagging_war': 'max',\n",
    "    'leading_war': 'max',\n",
    "    'ongoing_conflict': 'max'\n",
    "    ## not sure how to add this one just yet\n",
    "#     'total_deaths_both_sides': 'max'\n",
    "}\n",
    "war_df = deepcopy(part_df_copy.groupby(['war_num',\n",
    "                                        'war_name',\n",
    "                                        'war_type_code',\n",
    "                                        'war_type',\n",
    "                                        'war_subtype']).agg(aggregations).reset_index())\n",
    "        \n",
    "war_df = deepcopy(war_df.sort_values(by=['start_year', 'ongoing_conflict', 'end_year', 'start_date', 'war_name'], ascending=(False, False, False, False, True)))\n",
    "\n",
    "print('Total Wars with Null Start Years: {}'.format(format(int(len(war_df[war_df['start_year'].isnull()])), ',d')))\n",
    "print('Total (Non-Ongoing) Wars with Null End Years: {}'.format(format(int(len(war_df[(war_df['end_year'].isnull()) & (war_df['ongoing_conflict']==0)])), ',d')))\n",
    "print('Total Ongoing Wars: {}'.format(format(int(len(war_df[war_df['ongoing_conflict']==1])), ',d')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Evaluating for each war, whether the start/end date is based on an estimation.\n",
      "Total Estimated Start Dates: 88\n",
      "Total Estimated End Dates: 89\n"
     ]
    }
   ],
   "source": [
    "print('Evaluating for each war, whether the start/end date is based on an estimation.')\n",
    "\n",
    "for i, war in enumerate(war_df['war_num']):\n",
    "    if war_df.loc[i, 'war_num'] in list(estimated_start_dates.keys()) and estimated_start_dates[war_df.loc[i, 'war_num']]==war_df.loc[i, 'start_date']:\n",
    "        war_df.loc[i, 'start_date_estimated'] = 1\n",
    "    if war_df.loc[i, 'war_num'] in list(estimated_end_dates.keys()) and estimated_end_dates[war_df.loc[i, 'war_num']]==war_df.loc[i, 'end_date']:\n",
    "        war_df.loc[i, 'end_date_estimated'] = 1\n",
    "        \n",
    "war_df.loc[war_df['start_date_estimated'].isnull(), 'start_date_estimated'] = 0\n",
    "war_df.loc[war_df['end_date_estimated'].isnull(), 'end_date_estimated'] = 0\n",
    "\n",
    "print(\"Total Estimated Start Dates: {}\".format(format(len(war_df[war_df['start_date_estimated']==1]), ',d')))\n",
    "print(\"Total Estimated End Dates: {}\".format(format(len(war_df[war_df['end_date_estimated']==1]), ',d')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "integer_columns_dic = {\n",
    "    \n",
    "    'battle_deaths_est_a': 0,\n",
    "    'battle_deaths_est_b': 0,\n",
    "    'c_code': 0,\n",
    "    'c_code_a': 0,\n",
    "    'c_code_b': 0,\n",
    "    'end_date_estimated': 0,\n",
    "    'end_year': 0,\n",
    "    'lagging_war': -8,\n",
    "    'leading_war': -8,\n",
    "    'ongoing_conflict': 0,\n",
    "    'start_date_estimated': 0,\n",
    "    'start_year': 0,\n",
    "    'war_type_code': 0,\n",
    "    'days_not_at_war': 0\n",
    "}\n",
    "dataframe_list = [war_df, dyad_df, part_df]\n",
    "\n",
    "for column_name in integer_columns_dic.keys():\n",
    "    for dataframe in dataframe_list:\n",
    "        if column_name in dataframe.columns:\n",
    "            dataframe.loc[dataframe[column_name].isnull(), column_name] = integer_columns_dic[column_name]\n",
    "            dataframe[column_name] = dataframe[column_name].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Participants: 1,715\n",
      "Total Dyadic Combinations: 11,688\n",
      "Total Wars: 678\n"
     ]
    }
   ],
   "source": [
    "print('Total Participants: {}'.format(format(len(part_df), ',d')))\n",
    "print('Total Dyadic Combinations: {}'.format(format(len(dyad_df), ',d')))\n",
    "print('Total Wars: {}'.format(format(len(war_df), ',d')))\n",
    "\n",
    "part_df.to_pickle(pickle_directory + 'initial_part_df.pkl')\n",
    "dyad_df.to_pickle(pickle_directory + 'initial_dyad_df.pkl')\n",
    "war_df.to_pickle(pickle_directory + 'initial_war_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}