script.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Trade Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter solely on the year 2005 and filter out irrelevant columns. Also express the import/export in more relative terms. \n",
    "If flow values are missing (-9), they are imputed with 0. If a row has zero flow in both directions, it is removed from the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "year = 2005\n",
    "cols = ['ccode1', 'ccode2', 'flow1', 'flow2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_relative_import(x, row, n):\n",
    "    \"\"\"\n",
    "    Calculates relative import of a specific flow for a country {n} (1/2).\n",
    "    \"\"\" \n",
    "    return row[f'flow{n}'] / (x[x['ccode1'] == row[f'ccode{n}']]['flow1'].sum() + x[x['ccode2'] == row[f'ccode{n}']]['flow2'].sum())\n",
    "\n",
    "\n",
    "def calc_relative_export(x, row, n):\n",
    "    \"\"\"\n",
    "    Calculates relative export of a specific flow for a country {n} (1/2).\n",
    "    \"\"\"\n",
    "    e = 2 if n == 1 else 1\n",
    "    return row[f'flow{e}'] / (x[x['ccode1'] == row[f'ccode{n}']]['flow2'].sum() + x[x['ccode2'] == row[f'ccode{n}']]['flow1'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Undirected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of trade_df: (11934, 9)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ccode1</th>\n",
       "      <th>ccode2</th>\n",
       "      <th>flow1</th>\n",
       "      <th>flow2</th>\n",
       "      <th>relative_import1</th>\n",
       "      <th>relative_import2</th>\n",
       "      <th>relative_export1</th>\n",
       "      <th>relative_export2</th>\n",
       "      <th>trade_importance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>291943.50000</td>\n",
       "      <td>195360.28000</td>\n",
       "      <td>0.168938</td>\n",
       "      <td>0.570128</td>\n",
       "      <td>0.204894</td>\n",
       "      <td>0.802721</td>\n",
       "      <td>0.436670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>726.29999</td>\n",
       "      <td>1945.79000</td>\n",
       "      <td>0.000420</td>\n",
       "      <td>0.218027</td>\n",
       "      <td>0.002041</td>\n",
       "      <td>0.297894</td>\n",
       "      <td>0.129596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>397.87000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.056731</td>\n",
       "      <td>0.000417</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.014287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>458.50000</td>\n",
       "      <td>756.90997</td>\n",
       "      <td>0.000265</td>\n",
       "      <td>0.497009</td>\n",
       "      <td>0.000794</td>\n",
       "      <td>0.815954</td>\n",
       "      <td>0.328505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>4721.39990</td>\n",
       "      <td>5005.60990</td>\n",
       "      <td>0.002732</td>\n",
       "      <td>0.469146</td>\n",
       "      <td>0.005250</td>\n",
       "      <td>0.770421</td>\n",
       "      <td>0.311887</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ccode1  ccode2         flow1         flow2  relative_import1  \\\n",
       "0       2      20  291943.50000  195360.28000          0.168938   \n",
       "1       2      31     726.29999    1945.79000          0.000420   \n",
       "2       2      40       0.00000     397.87000          0.000000   \n",
       "3       2      41     458.50000     756.90997          0.000265   \n",
       "4       2      42    4721.39990    5005.60990          0.002732   \n",
       "\n",
       "   relative_import2  relative_export1  relative_export2  trade_importance  \n",
       "0          0.570128          0.204894          0.802721          0.436670  \n",
       "1          0.218027          0.002041          0.297894          0.129596  \n",
       "2          0.056731          0.000417          0.000000          0.014287  \n",
       "3          0.497009          0.000794          0.815954          0.328505  \n",
       "4          0.469146          0.005250          0.770421          0.311887  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_df = (\n",
    "    pd.read_csv('trade_dyadic.csv')\n",
    "    .pipe(lambda x: x[x['year'] == year])\n",
    "    .pipe(lambda x: x[cols].replace({-9: 0}))\n",
    "    .pipe(lambda x: x[~((x['flow1'] == 0) & (x['flow2'] == 0))])\n",
    "    .pipe(lambda x: x.assign(\n",
    "        # value of import of country1 from country2 relative to total import of country1\n",
    "        relative_import1 = x.apply(lambda row: calc_relative_import(x, row, 1), axis=1),\n",
    "        # value of import of country2 from country1 relative to total import of country2\n",
    "        relative_import2 = x.apply(lambda row: calc_relative_import(x, row, 2), axis=1),\n",
    "        # value of export of country1 to country2 relative to total export of country1\n",
    "        relative_export1 = x.apply(lambda row: calc_relative_export(x, row, 1), axis=1),\n",
    "        # value of export of country2 to country1 relative to total export of country 2\n",
    "        relative_export2 = x.apply(lambda row: calc_relative_export(x, row, 2), axis=1),       \n",
    "    ))\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "# calculate metric of trade_importance\n",
    "trade_df['trade_importance'] = (trade_df['relative_import1'] + trade_df['relative_import2'] + trade_df['relative_export1'] + trade_df['relative_export2']) / 4\n",
    "\n",
    "print(f\"Shape of trade_df: {trade_df.shape}\\n\")\n",
    "trade_df.to_csv('results/trade_relations_undir.csv', index=False)\n",
    "trade_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Directed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of dir_trade_df: (21237, 6)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ccode1</th>\n",
       "      <th>ccode2</th>\n",
       "      <th>import_flow1</th>\n",
       "      <th>relative_import1</th>\n",
       "      <th>relative_export2</th>\n",
       "      <th>trade_importance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>291943.50000</td>\n",
       "      <td>0.168938</td>\n",
       "      <td>0.802721</td>\n",
       "      <td>0.436670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>726.29999</td>\n",
       "      <td>0.000420</td>\n",
       "      <td>0.297894</td>\n",
       "      <td>0.129596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>458.50000</td>\n",
       "      <td>0.000265</td>\n",
       "      <td>0.815954</td>\n",
       "      <td>0.328505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>4721.39990</td>\n",
       "      <td>0.002732</td>\n",
       "      <td>0.770421</td>\n",
       "      <td>0.311887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>410.89999</td>\n",
       "      <td>0.000238</td>\n",
       "      <td>0.185703</td>\n",
       "      <td>0.154586</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ccode1  ccode2  import_flow1  relative_import1  relative_export2  \\\n",
       "0       2      20  291943.50000          0.168938          0.802721   \n",
       "1       2      31     726.29999          0.000420          0.297894   \n",
       "2       2      41     458.50000          0.000265          0.815954   \n",
       "3       2      42    4721.39990          0.002732          0.770421   \n",
       "4       2      51     410.89999          0.000238          0.185703   \n",
       "\n",
       "   trade_importance  \n",
       "0          0.436670  \n",
       "1          0.129596  \n",
       "2          0.328505  \n",
       "3          0.311887  \n",
       "4          0.154586  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir_trade_df = (\n",
    "    trade_df.append(\n",
    "        trade_df\n",
    "        .pipe(lambda x: x.rename(columns={'ccode1': 'ccode2', 'ccode2': 'ccode1', \n",
    "                                          'flow1': 'flow2', 'flow2': 'flow1', \n",
    "                                          'relative_import1': 'relative_import2', 'relative_import2': 'relative_import1',\n",
    "                                          'relative_export1': 'relative_export2', 'relative_export2': 'relative_export1'})))\n",
    "    .pipe(lambda x: x.rename(columns={'flow1': 'import_flow1'}))\n",
    "    .drop(columns=['flow2', 'relative_import2', 'relative_export1'])\n",
    "    .loc[lambda x: x['import_flow1'] > 0]\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "print(f\"Shape of dir_trade_df: {dir_trade_df.shape}\\n\")\n",
    "dir_trade_df.to_csv('results/trade_relations_dir.csv', index=False)\n",
    "dir_trade_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Country Codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove the duplicates, and the countries that are not in the trade data (i.e. countries that no longer exist). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of country_codes_df: (183, 4)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cabb</th>\n",
       "      <th>ccode</th>\n",
       "      <th>cname</th>\n",
       "      <th>gdp2005</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>2</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>1.175000e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CAN</td>\n",
       "      <td>20</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1.023000e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BHM</td>\n",
       "      <td>31</td>\n",
       "      <td>Bahamas</td>\n",
       "      <td>5.295000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CUB</td>\n",
       "      <td>40</td>\n",
       "      <td>Cuba</td>\n",
       "      <td>3.392000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HAI</td>\n",
       "      <td>41</td>\n",
       "      <td>Haiti</td>\n",
       "      <td>1.205000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DOM</td>\n",
       "      <td>42</td>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>5.568000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>JAM</td>\n",
       "      <td>51</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>1.113000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TRI</td>\n",
       "      <td>52</td>\n",
       "      <td>Trinidad and Tobago</td>\n",
       "      <td>1.148000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BAR</td>\n",
       "      <td>53</td>\n",
       "      <td>Barbados</td>\n",
       "      <td>4.569000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DMA</td>\n",
       "      <td>54</td>\n",
       "      <td>Dominica</td>\n",
       "      <td>3.840000e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cabb  ccode                     cname       gdp2005\n",
       "0  USA      2  United States of America  1.175000e+13\n",
       "1  CAN     20                    Canada  1.023000e+12\n",
       "2  BHM     31                   Bahamas  5.295000e+09\n",
       "3  CUB     40                      Cuba  3.392000e+10\n",
       "4  HAI     41                     Haiti  1.205000e+10\n",
       "5  DOM     42        Dominican Republic  5.568000e+10\n",
       "6  JAM     51                   Jamaica  1.113000e+10\n",
       "7  TRI     52       Trinidad and Tobago  1.148000e+10\n",
       "8  BAR     53                  Barbados  4.569000e+09\n",
       "9  DMA     54                  Dominica  3.840000e+08"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_codes_df = (\n",
    "    pd.read_csv('country_codes_gdp.csv', sep=';')\n",
    "    .drop_duplicates()\n",
    "    .pipe(lambda x: x.rename(str.lower, axis='columns'))\n",
    "    .pipe(lambda x: x.rename(columns={'stateabb': 'cabb', 'statenme': 'cname'}))\n",
    "    .pipe(lambda x: x[x['ccode'].isin(set(dir_trade_df['ccode1'].unique().tolist()))])\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "print(f\"Shape of country_codes_df: {country_codes_df.shape}\\n\")\n",
    "country_codes_df.to_csv('results/country_data.csv', index=False)\n",
    "country_codes_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Diplomatic Relations Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter on year 2005, rename columns and remove countries that are not in the other two datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Undirected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of diplomatic_df: (33306, 5)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ccode1</th>\n",
       "      <th>ccode2</th>\n",
       "      <th>dr_at_1</th>\n",
       "      <th>dr_at_2</th>\n",
       "      <th>de</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ccode1  ccode2  dr_at_1  dr_at_2  de\n",
       "0       2      20        3        3   1\n",
       "1       2      31        3        3   1\n",
       "2       2      40        9        9   0\n",
       "3       2      41        3        3   1\n",
       "4       2      42        3        3   1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diplomatic_df = (\n",
    "    pd.read_csv('diplomatic_exchange.csv')\n",
    "    .pipe(lambda x: x[x['year'] == year])\n",
    "    .pipe(lambda x: x.rename(str.lower, axis='columns'))\n",
    "    .pipe(lambda x: x[((x['ccode1'].isin(set(dir_trade_df['ccode1'].unique().tolist()))) & \n",
    "                       (x['ccode2'].isin(set(dir_trade_df['ccode1'].unique().tolist()))))])\n",
    "    .drop(columns=['version', 'year'])\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "print(f\"Shape of diplomatic_df: {diplomatic_df.shape}\\n\")\n",
    "diplomatic_df.to_csv('results/diplomatic_relations_undir.csv', index=False)\n",
    "diplomatic_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of diplomatic_df: (33306, 3)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ccode1</th>\n",
       "      <th>ccode2</th>\n",
       "      <th>de</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ccode1  ccode2  de\n",
       "0       2      20   1\n",
       "1       2      31   1\n",
       "2       2      40   0\n",
       "3       2      41   1\n",
       "4       2      42   1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_diplomatic_df = diplomatic_df[['ccode1', 'ccode2', 'de']]\n",
    "\n",
    "print(f\"Shape of diplomatic_df: {new_diplomatic_df.shape}\\n\")\n",
    "new_diplomatic_df.to_csv('results/new_diplomatic_relations_undir.csv', index=False)\n",
    "new_diplomatic_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Directed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of dir_diplomatic_df: (66612, 3)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ccode1</th>\n",
       "      <th>ccode2</th>\n",
       "      <th>dr_at_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ccode1  ccode2  dr_at_1\n",
       "0       2      20        3\n",
       "1       2      31        3\n",
       "2       2      40        9\n",
       "3       2      41        3\n",
       "4       2      42        3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir_diplomatic_df = (\n",
    "    diplomatic_df.append(\n",
    "        diplomatic_df\n",
    "        .pipe(lambda x: x.rename(columns={'ccode1': 'ccode2', 'ccode2': 'ccode1', \n",
    "                                          'dr_at_1': 'dr_at_2', 'dr_at_2': 'dr_at_1'})))\n",
    "    .drop(columns=['de', 'dr_at_2'])\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "print(f\"Shape of dir_diplomatic_df: {dir_diplomatic_df.shape}\\n\")\n",
    "dir_diplomatic_df.to_csv('results/diplomatic_relations_dir.csv', index=False)\n",
    "dir_diplomatic_df.head()"
   ]
  }
 ],
 "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.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}