ACLED_data_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, datetime, timedelta\n",
    "import matplotlib.pyplot as plt\n",
    "from functools import reduce\n",
    "from time import strptime\n",
    "import datetime as dt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import calendar\n",
    "import swifter\n",
    "import random"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ACLED Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df = pd.read_csv(\"data/2013-01-01-2018-12-31.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(acled_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df.drop(\n",
    "    [\"data_id\",\"iso\",\"event_id_cnty\",\"event_id_no_cnty\",\\\n",
    "    \"time_precision\",\"actor1\",\"assoc_actor_1\",\"inter1\",\\\n",
    "    \"actor2\",\"assoc_actor_2\",\"inter2\",\"interaction\",\"region\",\\\n",
    "    \"admin1\",\"admin2\",\"admin3\",\"geo_precision\",\"source\",\\\n",
    "    \"source_scale\",\"timestamp\"]\\\n",
    "    ,axis = 1,inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_week_day(date_string):\n",
    "    date_object = datetime.strptime(date_string, \"%d %B %Y\")\n",
    "    return calendar.day_name[(date_object).weekday()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_month(date_string):\n",
    "    return date_string.split(\" \")[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "acled_df[\"week_day\"] = acled_df[\"event_date\"].swifter.apply(get_week_day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df[\"month\"] = acled_df[\"event_date\"].swifter.apply(get_month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df.to_csv(\"Output/acled_data.csv\")\n",
    "acled_df.to_csv(\"Output/acled_data.js\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(acled_df[\"country\"].unique()))\n",
    "country = acled_df[\"country\"].unique()\n",
    "country.sort()\n",
    "country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled_df[\"sub_event_type\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violent = acled_df.loc[acled_df[\"event_type\"]=='Protests'].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nig_violent_df = violent.loc[violent[\"country\"]==\"Nigeria\"].reset_index(drop=True)\n",
    "nig_violent_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled2018_data = acled_df.loc[acled_df[\"year\"]==2018].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_grouped(term):\n",
    "    grouped = acled2018_data[[\"country\",\"iso3\",\"event_type\"]]\\\n",
    "                .groupby( [\"country\",\"iso3\",\"event_type\"] ).size().to_frame(name = 'count').reset_index()\n",
    "    return grouped.loc[grouped[\"event_type\"]==f'{term}'].drop(\"event_type\",axis=1)\\\n",
    "                .rename(columns = {\"count\":f'{term}'}).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "event_types = list(acled2018_data[\"event_type\"].unique())\n",
    "event_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_events = [get_grouped(f'{event}') for event in event_types]\n",
    "merged_df = reduce(lambda x,y: pd.merge(x,y, on = [\"country\",\"iso3\"]), list_events).\\\n",
    "                rename(columns={\"iso3\":\"country_code\",\n",
    "                                \"Violence against civilians\":\"violence\",\n",
    "                                \"Strategic developments\":\"development\",\n",
    "                                \"Battles\":\"battles\",\n",
    "                               })\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = get_grouped(event_types[0])\n",
    "merged_df2 = pd.merge(a,get_grouped(event_types[2])).rename(columns={\"iso3\":\"country_code\",\n",
    "                                \"Violence against civilians\":\"Human Trafficking Events\",\n",
    "                                \"Battles\":\"Battles Fought\",\n",
    "                               })\n",
    "merged_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv(\"Output/acled2018_events_type_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function to parse UNICEF Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def drop_and_rename(data,value):\n",
    "    return data[[\"Geographic Area\",\"Observation Value\"]].dropna().\\\n",
    "            rename(columns={\"Geographic Area\":\"country\",\n",
    "                            \"Observation Value\":f\"{value}\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Asylum seekers, by country of destination in thousands of persons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "asylm_df = pd.read_csv(\"data/GLOBAL_ASYLM_CNTRY_DEST._T._.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Asylum of seekers by country of destination, observation seekers in thousands\n",
    "asylm_df = drop_and_rename(asylm_df,\"Asylum Seekers\")\n",
    "for index, row in asylm_df.iterrows():\n",
    "    try:\n",
    "        if asylm_df.loc[index, \"Asylum Seekers\"] == \"<1\":\n",
    "            asylm_df.loc[index, \"Asylum Seekers\"] = round(random.random(),2)\n",
    "    except:\n",
    "        pass    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "asylm_df.head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total population by country in thousands of persons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_df = pd.read_csv(\"data/GLOBAL_POP_TOT._2018.csv\")\n",
    "pop_df = drop_and_rename(pop_df,\"Population\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_df[\"Population\"]=round(pop_df[\"Population\"],2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Estimated number of annual AIDS-related deaths (adolescents and young people aged 15-24 years) in persons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hiv_df = pd.read_csv(\"data/GLOBAL_HIV.csv\")\n",
    "hiv_df = hiv_df.loc[hiv_df[\"Time Period\"] == 2018]\n",
    "hiv_df = drop_and_rename(hiv_df,\"Aids Related Death\").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in hiv_df.iterrows():\n",
    "    try:\n",
    "        if hiv_df.loc[index, \"Aids Related Death\"] == \"<100\":\n",
    "            hiv_df.loc[index, \"Aids Related Death\"] = round((random.random()+4)*20,2)\n",
    "        if hiv_df.loc[index, \"Aids Related Death\"] == \"<200\":\n",
    "            hiv_df.loc[index, \"Aids Related Death\"] = round((random.random()+4)*40,2)\n",
    "        if hiv_df.loc[index, \"Aids Related Death\"] == \"<500\":\n",
    "            hiv_df.loc[index, \"Aids Related Death\"] = round((random.random()+4)*100,2)\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "hiv_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Smoking death by country https://ourworldindata.org/smoking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "smoking_death_df = pd.read_csv(\"data/share-deaths-smoking.csv\")\n",
    "smoking_death_df = smoking_death_df.loc[smoking_death_df[\"Year\"]==2017].reset_index(drop=True)\n",
    "smoking_death_df.rename(columns={\"Entity\":\"country\",\n",
    "                            \"Share of deaths from smoking (%)\":\"Death From Smoking\"},inplace=True)\n",
    "smoking_death_df = smoking_death_df[[\"country\",\"Death From Smoking\"]]\n",
    "smoking_death_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Global Life Expectancy in Years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_exp = pd.read_csv(\"data/GLOBAL_LIFE_EXP._T._.csv\")\n",
    "life_exp_df = drop_and_rename(life_exp,\"Life Expectancy\").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_exp_df[\"Life Expectancy\"] = round(life_exp_df[\"Life Expectancy\"],2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_exp_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge everything"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_list_events = [merged_df2,pop_df,asylm_df,hiv_df,life_exp_df,smoking_death_df]\n",
    "new_merged_df = reduce(lambda x,y: pd.merge(x,y, on = \"country\"), new_list_events)\n",
    "new_merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_merged_df.to_csv(\"Output/merge_dataset.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_dict = new_merged_df.to_dict('list')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymongo\n",
    "# Initialize PyMongo to work with MongoDBs\n",
    "conn = 'mongodb://localhost:27017'\n",
    "client = pymongo.MongoClient(conn)\n",
    "# Define database and collection\n",
    "db = client.merged_data\n",
    "merged_data = db.merged_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/toluwalasheomotunde/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: DeprecationWarning: remove is deprecated. Use delete_one or delete_many instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/Users/toluwalasheomotunde/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: DeprecationWarning: insert is deprecated. Use insert_one or insert_many instead.\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ObjectId('5e87799911d91805f3725475')"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_data.remove()\n",
    "merged_data.insert(merge_dict,check_keys=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_result = merged_data.find_one()\n",
    "if '_id' in merge_result:\n",
    "    del merge_result['_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'country': ['Afghanistan',\n",
       "  'Algeria',\n",
       "  'Angola',\n",
       "  'Armenia',\n",
       "  'Bangladesh',\n",
       "  'Benin',\n",
       "  'Bosnia and Herzegovina',\n",
       "  'Burkina Faso',\n",
       "  'Burundi',\n",
       "  'Cambodia',\n",
       "  'Cameroon',\n",
       "  'Central African Republic',\n",
       "  'Chad',\n",
       "  'Egypt',\n",
       "  'Equatorial Guinea',\n",
       "  'Ethiopia',\n",
       "  'Gabon',\n",
       "  'Ghana',\n",
       "  'Guinea',\n",
       "  'Indonesia',\n",
       "  'Israel',\n",
       "  'Jordan',\n",
       "  'Kazakhstan',\n",
       "  'Kenya',\n",
       "  'Kyrgyzstan',\n",
       "  'Lebanon',\n",
       "  'Liberia',\n",
       "  'Libya',\n",
       "  'Madagascar',\n",
       "  'Malaysia',\n",
       "  'Mali',\n",
       "  'Mauritania',\n",
       "  'Morocco',\n",
       "  'Mozambique',\n",
       "  'Myanmar',\n",
       "  'Nepal',\n",
       "  'Niger',\n",
       "  'Nigeria',\n",
       "  'Pakistan',\n",
       "  'Philippines',\n",
       "  'Rwanda',\n",
       "  'Senegal',\n",
       "  'Sierra Leone',\n",
       "  'Somalia',\n",
       "  'South Africa',\n",
       "  'South Sudan',\n",
       "  'Sri Lanka',\n",
       "  'Sudan',\n",
       "  'Thailand',\n",
       "  'Togo',\n",
       "  'Tunisia',\n",
       "  'Uganda',\n",
       "  'Ukraine',\n",
       "  'Yemen',\n",
       "  'Zambia',\n",
       "  'Zimbabwe'],\n",
       " 'country_code': ['AFG',\n",
       "  'DZA',\n",
       "  'AGO',\n",
       "  'ARM',\n",
       "  'BGD',\n",
       "  'BEN',\n",
       "  'BIH',\n",
       "  'BFA',\n",
       "  'BDI',\n",
       "  'KHM',\n",
       "  'CMR',\n",
       "  'CAF',\n",
       "  'TCD',\n",
       "  'EGY',\n",
       "  'GNQ',\n",
       "  'ETH',\n",
       "  'GAB',\n",
       "  'GHA',\n",
       "  'GIN',\n",
       "  'IDN',\n",
       "  'ISR',\n",
       "  'JOR',\n",
       "  'KAZ',\n",
       "  'KEN',\n",
       "  'KGZ',\n",
       "  'LBN',\n",
       "  'LBR',\n",
       "  'LBY',\n",
       "  'MDG',\n",
       "  'MYS',\n",
       "  'MLI',\n",
       "  'MRT',\n",
       "  'MAR',\n",
       "  'MOZ',\n",
       "  'MMR',\n",
       "  'NPL',\n",
       "  'NER',\n",
       "  'NGA',\n",
       "  'PAK',\n",
       "  'PHL',\n",
       "  'RWA',\n",
       "  'SEN',\n",
       "  'SLE',\n",
       "  'SOM',\n",
       "  'ZAF',\n",
       "  'SSD',\n",
       "  'LKA',\n",
       "  'SDN',\n",
       "  'THA',\n",
       "  'TGO',\n",
       "  'TUN',\n",
       "  'UGA',\n",
       "  'UKR',\n",
       "  'YEM',\n",
       "  'ZMB',\n",
       "  'ZWE'],\n",
       " 'Human Trafficking Events': [332,\n",
       "  27,\n",
       "  19,\n",
       "  11,\n",
       "  198,\n",
       "  14,\n",
       "  10,\n",
       "  120,\n",
       "  658,\n",
       "  3,\n",
       "  307,\n",
       "  235,\n",
       "  16,\n",
       "  49,\n",
       "  4,\n",
       "  265,\n",
       "  2,\n",
       "  20,\n",
       "  16,\n",
       "  66,\n",
       "  14,\n",
       "  3,\n",
       "  9,\n",
       "  118,\n",
       "  21,\n",
       "  8,\n",
       "  1,\n",
       "  113,\n",
       "  35,\n",
       "  10,\n",
       "  264,\n",
       "  1,\n",
       "  11,\n",
       "  73,\n",
       "  123,\n",
       "  17,\n",
       "  64,\n",
       "  705,\n",
       "  319,\n",
       "  975,\n",
       "  7,\n",
       "  7,\n",
       "  21,\n",
       "  610,\n",
       "  34,\n",
       "  327,\n",
       "  35,\n",
       "  298,\n",
       "  170,\n",
       "  1,\n",
       "  12,\n",
       "  101,\n",
       "  126,\n",
       "  419,\n",
       "  15,\n",
       "  153],\n",
       " 'Battles Fought': [9549,\n",
       "  16,\n",
       "  4,\n",
       "  1317,\n",
       "  68,\n",
       "  3,\n",
       "  1,\n",
       "  81,\n",
       "  42,\n",
       "  1,\n",
       "  265,\n",
       "  215,\n",
       "  28,\n",
       "  166,\n",
       "  2,\n",
       "  142,\n",
       "  1,\n",
       "  16,\n",
       "  7,\n",
       "  41,\n",
       "  10,\n",
       "  3,\n",
       "  3,\n",
       "  85,\n",
       "  2,\n",
       "  33,\n",
       "  1,\n",
       "  248,\n",
       "  30,\n",
       "  4,\n",
       "  203,\n",
       "  2,\n",
       "  1,\n",
       "  17,\n",
       "  456,\n",
       "  4,\n",
       "  41,\n",
       "  474,\n",
       "  315,\n",
       "  463,\n",
       "  3,\n",
       "  6,\n",
       "  4,\n",
       "  1250,\n",
       "  6,\n",
       "  441,\n",
       "  8,\n",
       "  153,\n",
       "  149,\n",
       "  1,\n",
       "  20,\n",
       "  19,\n",
       "  8021,\n",
       "  3457,\n",
       "  1,\n",
       "  9],\n",
       " 'Population': [37171.92,\n",
       "  42228.41,\n",
       "  30809.79,\n",
       "  2951.74,\n",
       "  161376.71,\n",
       "  11485.04,\n",
       "  3323.92,\n",
       "  19751.47,\n",
       "  11175.37,\n",
       "  16249.79,\n",
       "  25216.27,\n",
       "  4666.37,\n",
       "  15477.73,\n",
       "  98423.6,\n",
       "  1308.97,\n",
       "  109224.41,\n",
       "  2119.28,\n",
       "  29767.1,\n",
       "  12414.29,\n",
       "  267670.54,\n",
       "  8381.52,\n",
       "  9965.32,\n",
       "  18319.62,\n",
       "  51392.56,\n",
       "  6304.03,\n",
       "  6859.41,\n",
       "  4818.97,\n",
       "  6678.56,\n",
       "  26262.31,\n",
       "  31528.03,\n",
       "  19077.75,\n",
       "  4403.31,\n",
       "  36029.09,\n",
       "  29496.0,\n",
       "  53708.32,\n",
       "  28095.71,\n",
       "  22442.82,\n",
       "  195874.68,\n",
       "  212228.29,\n",
       "  106651.39,\n",
       "  12301.97,\n",
       "  15854.32,\n",
       "  7650.15,\n",
       "  15008.23,\n",
       "  57792.52,\n",
       "  10975.93,\n",
       "  21228.76,\n",
       "  41801.53,\n",
       "  69428.45,\n",
       "  7889.09,\n",
       "  11565.2,\n",
       "  42729.04,\n",
       "  44246.16,\n",
       "  28498.68,\n",
       "  17351.71,\n",
       "  14438.8],\n",
       " 'Asylum Seekers': ['334',\n",
       "  '6',\n",
       "  '8',\n",
       "  '15',\n",
       "  '68',\n",
       "  '1',\n",
       "  '2',\n",
       "  '6',\n",
       "  '60',\n",
       "  '2',\n",
       "  '25',\n",
       "  '13',\n",
       "  '3',\n",
       "  '15',\n",
       "  0.33,\n",
       "  '134',\n",
       "  '1',\n",
       "  '18',\n",
       "  '35',\n",
       "  '5',\n",
       "  '1',\n",
       "  '3',\n",
       "  '4',\n",
       "  '6',\n",
       "  '3',\n",
       "  '9',\n",
       "  '3',\n",
       "  '7',\n",
       "  0.46,\n",
       "  '9',\n",
       "  '15',\n",
       "  '8',\n",
       "  '7',\n",
       "  '13',\n",
       "  '41',\n",
       "  '13',\n",
       "  '2',\n",
       "  '92',\n",
       "  '77',\n",
       "  '8',\n",
       "  '13',\n",
       "  '25',\n",
       "  '6',\n",
       "  '58',\n",
       "  '2',\n",
       "  '6',\n",
       "  '19',\n",
       "  '52',\n",
       "  '1',\n",
       "  '4',\n",
       "  '2',\n",
       "  '10',\n",
       "  '38',\n",
       "  '25',\n",
       "  0.32,\n",
       "  '22'],\n",
       " 'Aids Related Death': [81.74,\n",
       "  87.09,\n",
       "  '740',\n",
       "  98.65,\n",
       "  99.31,\n",
       "  197.6,\n",
       "  93.83,\n",
       "  451.98,\n",
       "  405.76,\n",
       "  86.59,\n",
       "  '1100',\n",
       "  423.96,\n",
       "  463.02,\n",
       "  97.64,\n",
       "  85.35,\n",
       "  '1000',\n",
       "  91.81,\n",
       "  '960',\n",
       "  451.78,\n",
       "  '1800',\n",
       "  90.89,\n",
       "  84.71,\n",
       "  94.56,\n",
       "  '3100',\n",
       "  84.82,\n",
       "  83.19,\n",
       "  169.6,\n",
       "  84.77,\n",
       "  185.32,\n",
       "  87.73,\n",
       "  476.35,\n",
       "  94.02,\n",
       "  85.24,\n",
       "  '4300',\n",
       "  '550',\n",
       "  91.1,\n",
       "  90.62,\n",
       "  '3700',\n",
       "  186.33,\n",
       "  436.3,\n",
       "  473.47,\n",
       "  81.07,\n",
       "  440.73,\n",
       "  94.41,\n",
       "  '5300',\n",
       "  409.14,\n",
       "  86.31,\n",
       "  90.22,\n",
       "  446.77,\n",
       "  441.76,\n",
       "  92.83,\n",
       "  '2200',\n",
       "  82.56,\n",
       "  98.69,\n",
       "  '1600',\n",
       "  '2400'],\n",
       " 'Life Expectancy': [64.49,\n",
       "  76.69,\n",
       "  60.78,\n",
       "  74.94,\n",
       "  72.32,\n",
       "  61.47,\n",
       "  77.26,\n",
       "  61.17,\n",
       "  61.25,\n",
       "  69.57,\n",
       "  58.92,\n",
       "  52.8,\n",
       "  53.98,\n",
       "  71.82,\n",
       "  58.4,\n",
       "  66.24,\n",
       "  66.19,\n",
       "  63.78,\n",
       "  61.18,\n",
       "  71.51,\n",
       "  82.82,\n",
       "  74.4,\n",
       "  73.24,\n",
       "  66.34,\n",
       "  71.32,\n",
       "  78.88,\n",
       "  63.73,\n",
       "  72.72,\n",
       "  66.68,\n",
       "  76.0,\n",
       "  58.89,\n",
       "  64.7,\n",
       "  76.45,\n",
       "  60.16,\n",
       "  66.87,\n",
       "  70.48,\n",
       "  62.02,\n",
       "  54.33,\n",
       "  67.11,\n",
       "  71.1,\n",
       "  68.7,\n",
       "  67.67,\n",
       "  54.31,\n",
       "  57.07,\n",
       "  63.86,\n",
       "  57.6,\n",
       "  76.81,\n",
       "  65.1,\n",
       "  76.93,\n",
       "  60.76,\n",
       "  76.5,\n",
       "  62.97,\n",
       "  71.95,\n",
       "  66.1,\n",
       "  63.51,\n",
       "  61.2],\n",
       " 'Death From Smoking': [4.08,\n",
       "  11.08,\n",
       "  4.78,\n",
       "  17.22,\n",
       "  10.23,\n",
       "  2.46,\n",
       "  22.92,\n",
       "  1.83,\n",
       "  3.34,\n",
       "  12.45,\n",
       "  2.74,\n",
       "  3.93,\n",
       "  1.13,\n",
       "  13.88,\n",
       "  2.25,\n",
       "  1.76,\n",
       "  5.8,\n",
       "  2.9,\n",
       "  2.1,\n",
       "  13.85,\n",
       "  13.34,\n",
       "  12.13,\n",
       "  14.92,\n",
       "  3.39,\n",
       "  11.21,\n",
       "  19.08,\n",
       "  2.98,\n",
       "  7.98,\n",
       "  3.15,\n",
       "  14.32,\n",
       "  1.4,\n",
       "  2.63,\n",
       "  8.33,\n",
       "  3.82,\n",
       "  16.61,\n",
       "  12.89,\n",
       "  0.94,\n",
       "  0.85,\n",
       "  9.08,\n",
       "  14.06,\n",
       "  5.56,\n",
       "  2.84,\n",
       "  3.42,\n",
       "  3.35,\n",
       "  6.3,\n",
       "  2.45,\n",
       "  10.07,\n",
       "  1.37,\n",
       "  12.03,\n",
       "  3.01,\n",
       "  16.2,\n",
       "  2.39,\n",
       "  16.97,\n",
       "  7.61,\n",
       "  3.68,\n",
       "  7.41]}"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"country\": [\"Afghanistan\", \"Algeria\", \"Angola\", \"Armenia\", \"Bangladesh\", \"Benin\", \"Bosnia and Herzegovina\", \"Burkina Faso\", \"Burundi\", \"Cambodia\", \"Cameroon\", \"Central African Republic\", \"Chad\", \"Egypt\", \"Equatorial Guinea\", \"Ethiopia\", \"Gabon\", \"Ghana\", \"Guinea\", \"Indonesia\", \"Israel\", \"Jordan\", \"Kazakhstan\", \"Kenya\", \"Kyrgyzstan\", \"Lebanon\", \"Liberia\", \"Libya\", \"Madagascar\", \"Malaysia\", \"Mali\", \"Mauritania\", \"Morocco\", \"Mozambique\", \"Myanmar\", \"Nepal\", \"Niger\", \"Nigeria\", \"Pakistan\", \"Philippines\", \"Rwanda\", \"Senegal\", \"Sierra Leone\", \"Somalia\", \"South Africa\", \"South Sudan\", \"Sri Lanka\", \"Sudan\", \"Thailand\", \"Togo\", \"Tunisia\", \"Uganda\", \"Ukraine\", \"Yemen\", \"Zambia\", \"Zimbabwe\"], \"country_code\": [\"AFG\", \"DZA\", \"AGO\", \"ARM\", \"BGD\", \"BEN\", \"BIH\", \"BFA\", \"BDI\", \"KHM\", \"CMR\", \"CAF\", \"TCD\", \"EGY\", \"GNQ\", \"ETH\", \"GAB\", \"GHA\", \"GIN\", \"IDN\", \"ISR\", \"JOR\", \"KAZ\", \"KEN\", \"KGZ\", \"LBN\", \"LBR\", \"LBY\", \"MDG\", \"MYS\", \"MLI\", \"MRT\", \"MAR\", \"MOZ\", \"MMR\", \"NPL\", \"NER\", \"NGA\", \"PAK\", \"PHL\", \"RWA\", \"SEN\", \"SLE\", \"SOM\", \"ZAF\", \"SSD\", \"LKA\", \"SDN\", \"THA\", \"TGO\", \"TUN\", \"UGA\", \"UKR\", \"YEM\", \"ZMB\", \"ZWE\"], \"Human Trafficking Events\": [332, 27, 19, 11, 198, 14, 10, 120, 658, 3, 307, 235, 16, 49, 4, 265, 2, 20, 16, 66, 14, 3, 9, 118, 21, 8, 1, 113, 35, 10, 264, 1, 11, 73, 123, 17, 64, 705, 319, 975, 7, 7, 21, 610, 34, 327, 35, 298, 170, 1, 12, 101, 126, 419, 15, 153], \"Battles Fought\": [9549, 16, 4, 1317, 68, 3, 1, 81, 42, 1, 265, 215, 28, 166, 2, 142, 1, 16, 7, 41, 10, 3, 3, 85, 2, 33, 1, 248, 30, 4, 203, 2, 1, 17, 456, 4, 41, 474, 315, 463, 3, 6, 4, 1250, 6, 441, 8, 153, 149, 1, 20, 19, 8021, 3457, 1, 9], \"Population\": [37171.92, 42228.41, 30809.79, 2951.74, 161376.71, 11485.04, 3323.92, 19751.47, 11175.37, 16249.79, 25216.27, 4666.37, 15477.73, 98423.6, 1308.97, 109224.41, 2119.28, 29767.1, 12414.29, 267670.54, 8381.52, 9965.32, 18319.62, 51392.56, 6304.03, 6859.41, 4818.97, 6678.56, 26262.31, 31528.03, 19077.75, 4403.31, 36029.09, 29496.0, 53708.32, 28095.71, 22442.82, 195874.68, 212228.29, 106651.39, 12301.97, 15854.32, 7650.15, 15008.23, 57792.52, 10975.93, 21228.76, 41801.53, 69428.45, 7889.09, 11565.2, 42729.04, 44246.16, 28498.68, 17351.71, 14438.8], \"Asylum Seekers\": [\"334\", \"6\", \"8\", \"15\", \"68\", \"1\", \"2\", \"6\", \"60\", \"2\", \"25\", \"13\", \"3\", \"15\", 0.33, \"134\", \"1\", \"18\", \"35\", \"5\", \"1\", \"3\", \"4\", \"6\", \"3\", \"9\", \"3\", \"7\", 0.46, \"9\", \"15\", \"8\", \"7\", \"13\", \"41\", \"13\", \"2\", \"92\", \"77\", \"8\", \"13\", \"25\", \"6\", \"58\", \"2\", \"6\", \"19\", \"52\", \"1\", \"4\", \"2\", \"10\", \"38\", \"25\", 0.32, \"22\"], \"Aids Related Death\": [81.74, 87.09, \"740\", 98.65, 99.31, 197.6, 93.83, 451.98, 405.76, 86.59, \"1100\", 423.96, 463.02, 97.64, 85.35, \"1000\", 91.81, \"960\", 451.78, \"1800\", 90.89, 84.71, 94.56, \"3100\", 84.82, 83.19, 169.6, 84.77, 185.32, 87.73, 476.35, 94.02, 85.24, \"4300\", \"550\", 91.1, 90.62, \"3700\", 186.33, 436.3, 473.47, 81.07, 440.73, 94.41, \"5300\", 409.14, 86.31, 90.22, 446.77, 441.76, 92.83, \"2200\", 82.56, 98.69, \"1600\", \"2400\"], \"Life Expectancy\": [64.49, 76.69, 60.78, 74.94, 72.32, 61.47, 77.26, 61.17, 61.25, 69.57, 58.92, 52.8, 53.98, 71.82, 58.4, 66.24, 66.19, 63.78, 61.18, 71.51, 82.82, 74.4, 73.24, 66.34, 71.32, 78.88, 63.73, 72.72, 66.68, 76.0, 58.89, 64.7, 76.45, 60.16, 66.87, 70.48, 62.02, 54.33, 67.11, 71.1, 68.7, 67.67, 54.31, 57.07, 63.86, 57.6, 76.81, 65.1, 76.93, 60.76, 76.5, 62.97, 71.95, 66.1, 63.51, 61.2], \"Death From Smoking\": [4.08, 11.08, 4.78, 17.22, 10.23, 2.46, 22.92, 1.83, 3.34, 12.45, 2.74, 3.93, 1.13, 13.88, 2.25, 1.76, 5.8, 2.9, 2.1, 13.85, 13.34, 12.13, 14.92, 3.39, 11.21, 19.08, 2.98, 7.98, 3.15, 14.32, 1.4, 2.63, 8.33, 3.82, 16.61, 12.89, 0.94, 0.85, 9.08, 14.06, 5.56, 2.84, 3.42, 3.35, 6.3, 2.45, 10.07, 1.37, 12.03, 3.01, 16.2, 2.39, 16.97, 7.61, 3.68, 7.41]}'"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from bson import json_util\n",
    "\n",
    "\n",
    "\n",
    "json.dumps(merge_result,default=json_util.default)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploratory Data Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_merged_df[\"Asylum Seekers\"] = pd.to_numeric(new_merged_df[\"Asylum Seekers\"], errors ='ignore')\n",
    "new_merged_df[\"Aids Related Death\"] = pd.to_numeric(new_merged_df[\"Aids Related Death\"], errors ='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "correlations = new_merged_df.drop([\"country\",\"country_code\"],axis=1).corr()\n",
    "# plot correlation matrix\n",
    "fig = plt.figure(figsize=(15,10))\n",
    "ax = fig.add_subplot(111)\n",
    "cax = ax.matshow(correlations, vmin=-1, vmax=1)\n",
    "fig.colorbar(cax)\n",
    "ticks = np.arange(0,9,1)\n",
    "ax.set_xticks(ticks)\n",
    "ax.set_yticks(ticks)\n",
    "names = list( new_merged_df.drop([\"country\",\"country_code\"],axis=1).columns)\n",
    "ax.set_xticklabels(names)\n",
    "ax.set_yticklabels(names)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.plotting import scatter_matrix\n",
    "scatter_matrix(new_merged_df.drop([\"country\",\"country_code\"],axis=1),figsize=(15,10))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pov_df = pd.read_csv(\"data/Poverty.csv\").dropna().rename(columns= {'Unnamed: 0':'country', \n",
    "                                    'Unnamed: 2':'Pop. Below $1.90 per day',\n",
    "                                    'Unnamed: 3':'Pop. Below $3.20 per day',\n",
    "                                    'Unnamed: 4':'Pop. Below $5.50 per day'\n",
    "                                   }).replace(\"..\",\"NA\")[[\"country\",\"Population below $1.90 a day\"]]\n",
    "pov_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open(\"Output/normal_cases.json\", 'r') as fp:\n",
    "    normal_data = json.load(fp)\n",
    "with open(\"Output/special_cases.json\", 'r') as fp:\n",
    "    special_data = json.load(fp)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country = [list(i.keys())[0].title() for i in normal_data]\n",
    "intro = [list(i.values())[0][0]['INTRO'] for i in normal_data]\n",
    "tier = [list(i.values())[0][1]['TIER'] for i in normal_data]\n",
    "recommendation = []\n",
    "prosecution = []\n",
    "protection = []\n",
    "prevention = []\n",
    "traffick_profile = []\n",
    "for i in normal_data:\n",
    "    try:\n",
    "        recommendation.append(list(i.values())[0][2]['RECOMMENDATIONS'])\n",
    "        prosecution.append(list(i.values())[0][3]['PROSECUTIONS'])\n",
    "        protection.append(list(i.values())[0][4]['PROTECTION'])\n",
    "        prevention.append(list(i.values())[0][5]['PREVENTION'])\n",
    "        traffick_profile.append(list(i.values())[0][6]['TRAFFICKING PROFILE'])\n",
    "    except:\n",
    "        recommendation.append(\"NA\")\n",
    "        prosecution.append(\"NA\")\n",
    "        protection.append(\"NA\")\n",
    "        prevention.append(\"NA\")\n",
    "        traffick_profile.append('NA')\n",
    "        pass\n",
    "    \n",
    "special_countries = [list(i.keys())[0].title() for i in special_data]\n",
    "special_intro = [list(i.values())[0][0]['INTRO'] for i in special_data]\n",
    "special_tier = [list(i.values())[0][1]['TIER'] for i in special_data]\n",
    "special_government_effort = [list(i.values())[0][2]['GOVERNMENT EFFORTS'] for i in special_data]\n",
    "special_traffick_profile = [list(i.values())[0][3]['TRAFFICKING PROFILE'] for i in special_data]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country.extend(special_countries)\n",
    "intro.extend(special_intro)\n",
    "tier.extend(special_tier)\n",
    "recommendation.extend([\"NA\",\"NA\",\"NA\",\"NA\"])\n",
    "prosecution.extend([\"NA\",\"NA\",\"NA\",\"NA\"])\n",
    "protection.extend([\"NA\",\"NA\",\"NA\",\"NA\"])\n",
    "prevention.extend(special_government_effort)\n",
    "traffick_profile.extend(special_traffick_profile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tip_report = pd.DataFrame({\"country\":country,\n",
    "              \"introduction\":intro,\n",
    "              \"tier\":tier,\n",
    "              \"recommendation\":recommendation,\n",
    "              \"prosecution\":prosecution,\n",
    "              \"protection\":protection,\n",
    "              \"prevention\":prevention,\n",
    "              \"traffick_profile\":traffick_profile})\n",
    "tip_report.sort_values(by=['country'], inplace=True)\n",
    "tip_report.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled2018vio_data = acled2018_data.loc[acled2018_data[\"event_type\"]==\"Violence against civilians\"]\n",
    "acled2018vio_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_group = acled2018vio_data[[\"country\",\"iso3\",\"event_type\",\"location\",\"latitude\",\"longitude\",\"notes\",\"fatalities\",\"week_day\",\"month\"]]\\\n",
    "                .groupby( [\"country\",\"week_day\"] ).size().to_frame(name = 'count').reset_index()\n",
    "week_group = week_group.pivot(index='country', columns='week_day', values='count').fillna(0)\n",
    "week_group = week_group[[\"Monday\",\"Tuesday\",\"Wednesday\",\"Thursday\",\"Friday\",\"Saturday\",\"Sunday\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "week_group.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_group = acled2018vio_data[[\"country\",\"iso3\",\"event_type\",\"location\",\"latitude\",\"longitude\",\"notes\",\"fatalities\",\"week_day\",\"month\"]]\\\n",
    "                .groupby( [\"country\",\"month\"] ).size().to_frame(name = 'count').reset_index()\n",
    "month_group = month_group.pivot(index='country', columns='month', values='count').fillna(0)\n",
    "month_group = month_group[[\"January\",\"February\",\"March\",\"April\",\"May\",\"June\",\"July\",\"August\",\"September\",\"October\",\"November\",\"December\"]]\n",
    "month_group.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acled2018vio_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "listEvents = [get_grouped(event_types[0]),pop_df,asylm_df,life_exp_df,pov_df,tip_report,week_group,month_group]\n",
    "complete_df = reduce(lambda x,y: pd.merge(x,y, on = \"country\"), listEvents)\n",
    "complete_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_notes(country):\n",
    "    a = acled2018vio_data.loc[acled2018vio_data[\"country\"]==f\"{country}\"]\n",
    "    notes = a[\"notes\"].tolist()\n",
    "    location=a[\"location\"].tolist()\n",
    "    latitude=[str(i) for i in a[\"latitude\"].tolist()]\n",
    "    longitude = [str(i) for i in a[\"longitude\"].tolist()]\n",
    "    fatalities = [str(i) for i in a[\"fatalities\"].tolist()]\n",
    "    return [\" \".join(notes),\"  \".join(location),\"  \".join(latitude),\"  \".join(longitude),\"  \".join(fatalities)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_df[\"notes\"] = \" \"\n",
    "complete_df[\"location\"] = \" \"\n",
    "complete_df[\"latitude\"] = \" \"\n",
    "complete_df[\"longitude\"] = \" \"\n",
    "complete_df[\"fatalities\"] = \" \"\n",
    "for index, row in complete_df.iterrows():\n",
    "    try:\n",
    "        complete_df.loc[index, \"notes\"] = get_notes(complete_df.loc[index, \"country\"])[0]\n",
    "        complete_df.loc[index, \"location\"] = get_notes(complete_df.loc[index, \"country\"])[1]\n",
    "        complete_df.loc[index, \"latitude\"] = get_notes(complete_df.loc[index, \"country\"])[2]\n",
    "        complete_df.loc[index, \"longitude\"] = get_notes(complete_df.loc[index, \"country\"])[3]\n",
    "        complete_df.loc[index, \"fatalities\"] = get_notes(complete_df.loc[index, \"country\"])[4]\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_df = complete_df.set_index(\"country\")\n",
    "complete_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_dict = complete_df.T.to_dict()\n",
    "b = list(complete_dict.values())\n",
    "for i in range(len(b)):\n",
    "    b[i][\"location\"] = b[i][\"location\"].split(\"  \")\n",
    "    b[i][\"latitude\"] = b[i][\"latitude\"].split(\"  \")\n",
    "    b[i][\"longitude\"] = b[i][\"longitude\"].split(\"  \")\n",
    "    b[i][\"fatalities\"] = b[i][\"fatalities\"].split(\"  \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_complete_df = pd.DataFrame(complete_dict).T\n",
    "new_complete_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_complete_df.to_csv(\"Output/Complete_dataset.csv\")\n",
    "new_complete_df.T.to_json(\"Output/Complete_dataset.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.3 64-bit ('base': conda)",
   "language": "python",
   "name": "python37364bitbasecondaf7339001cfe34b54ae1ffacfd6447597"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}