GT data pre-processing and modeling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a937b476",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/00/0s56_3496fb7660g8b7bwcyh0000gn/T/ipykernel_70881/2654467892.py:4: DtypeWarning: Columns (4,6,31,33,61,62,63,76,79,90,92,94,96,114,115,121) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv('./Dataset/globalterrorismdb_0718dist.csv', encoding = \"ISO-8859-1\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , country , country_txt , region , region_txt , provstate , city , latitude , longitude , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , attacktype1 , attacktype1_txt , attacktype2 , attacktype2_txt , attacktype3 , attacktype3_txt , targtype1 , targtype1_txt , targsubtype1 , targsubtype1_txt , corp1 , target1 , natlty1 , natlty1_txt , targtype2 , targtype2_txt , targsubtype2 , targsubtype2_txt , corp2 , target2 , natlty2 , natlty2_txt , targtype3 , targtype3_txt , targsubtype3 , targsubtype3_txt , corp3 , target3 , natlty3 , natlty3_txt , gname , gsubname , gname2 , gsubname2 , gname3 , gsubname3 , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weaptype1 , weaptype1_txt , weapsubtype1 , weapsubtype1_txt , weaptype2 , weaptype2_txt , weapsubtype2 , weapsubtype2_txt , weaptype3 , weaptype3_txt , weapsubtype3 , weapsubtype3_txt , weaptype4 , weaptype4_txt , weapsubtype4 , weapsubtype4_txt , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv('./Dataset/globalterrorismdb_0718dist.csv', encoding = \"ISO-8859-1\")\n",
    "\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cc24b7cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(181691, 135)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a24bf16a",
   "metadata": {},
   "source": [
    "---\n",
    "## 1-DimLocation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "11df1855",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(40676, 5)\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>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>Provenance/State</th>\n",
       "      <th>City</th>\n",
       "      <th>Location Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>Central America &amp; Caribbean</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Santo Domingo</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>North America</td>\n",
       "      <td>Federal</td>\n",
       "      <td>Mexico City</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philippines</td>\n",
       "      <td>Southeast Asia</td>\n",
       "      <td>Tarlac</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Greece</td>\n",
       "      <td>Western Europe</td>\n",
       "      <td>Attica</td>\n",
       "      <td>Athens</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Japan</td>\n",
       "      <td>East Asia</td>\n",
       "      <td>Fukouka</td>\n",
       "      <td>Fukouka</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Country                       Region Provenance/State  \\\n",
       "0  Dominican Republic  Central America & Caribbean          Unknown   \n",
       "1              Mexico                North America          Federal   \n",
       "2         Philippines               Southeast Asia           Tarlac   \n",
       "3              Greece               Western Europe           Attica   \n",
       "4               Japan                    East Asia          Fukouka   \n",
       "\n",
       "            City  Location Id  \n",
       "0  Santo Domingo            1  \n",
       "1    Mexico City            2  \n",
       "2        Unknown            3  \n",
       "3         Athens            4  \n",
       "4        Fukouka            5  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_location = df.loc[:,\n",
    "    ['country_txt',\n",
    "     'region_txt',\n",
    "     'provstate',\n",
    "     'city']\n",
    "]\n",
    "\n",
    "replace_regex = '\\(((\\s*\\w*\\s*)*)\\)|-*|\\,|\\'|\\/|\\.*|\\(*\\)*|\\?|\\=|\\*'\n",
    "\n",
    "dim_location['city'] = dim_location['city'].\\\n",
    "    replace(to_replace=replace_regex, value=\"\", regex=True).\\\n",
    "    fillna('Unkonwn').\\\n",
    "    str.replace('&', 'and').\\\n",
    "    str.replace('Unkknown', 'Unknown').\\\n",
    "    str.title().\\\n",
    "    str.strip()\n",
    "\n",
    "dim_location['provstate'] = df['provstate'].\\\n",
    "    replace(to_replace=replace_regex, value=\"\", regex=True).\\\n",
    "    fillna('Unknown').\\\n",
    "    str.replace('é', 'e').\\\n",
    "    str.replace('&', 'and').\\\n",
    "    str.replace('temouchent', 'tecmouchent').\\\n",
    "    str.replace('Bosnia-Herzegovina', 'Bosnia And Herzegovina').\\\n",
    "    str.replace('ñ', 'n').\\\n",
    "    str.title().\\\n",
    "    str.strip()\n",
    "\n",
    "dim_location.drop_duplicates(inplace=True)\n",
    "dim_location['Location Id'] = dim_location.reset_index(drop=True).index + 1\n",
    "\n",
    "\n",
    "dim_location.rename(columns={\n",
    "                'country_txt': 'Country',\n",
    "                'region_txt': 'Region',\n",
    "                'provstate': 'Provenance/State',\n",
    "                'city': 'City',}, inplace=True)\n",
    "print(dim_location.shape)\n",
    "dim_location.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "06a1beee",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>scite1</th>\n",
       "      <th>scite2</th>\n",
       "      <th>scite3</th>\n",
       "      <th>dbsource</th>\n",
       "      <th>INT_LOG</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 128 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity location  ... scite1  scite2  scite3  dbsource  \\\n",
       "0          1.0         0      NaN  ...    NaN     NaN     NaN      PGIS   \n",
       "1          1.0         0      NaN  ...    NaN     NaN     NaN      PGIS   \n",
       "2          4.0         0      NaN  ...    NaN     NaN     NaN      PGIS   \n",
       "3          1.0         0      NaN  ...    NaN     NaN     NaN      PGIS   \n",
       "4          1.0         0      NaN  ...    NaN     NaN     NaN      PGIS   \n",
       "\n",
       "   INT_LOG  INT_IDEO INT_MISC  INT_ANY  related  Location Id  \n",
       "0        0         0        0        0      NaN          NaN  \n",
       "1        0         1        1        1      NaN          NaN  \n",
       "2       -9        -9        1        1      NaN          3.0  \n",
       "3       -9        -9        1        1      NaN          4.0  \n",
       "4       -9        -9        1        1      NaN          5.0  \n",
       "\n",
       "[5 rows x 128 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_location,\n",
    "              how='left',\n",
    "              left_on=['country_txt', 'region_txt', 'provstate', 'city'],\n",
    "              right_on=['Country', 'Region', 'Provenance/State', 'City',])\n",
    "df.drop([\n",
    "    'country',\n",
    "    'country_txt',\n",
    "    'region',\n",
    "    'region_txt',\n",
    "    'provstate',\n",
    "    'city',\n",
    "    'latitude',\n",
    "    'longitude',\n",
    "    'Country',\n",
    "    'Region',\n",
    "    'Provenance/State',\n",
    "    'City'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9ed7b63b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 128) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , attacktype1 , attacktype1_txt , attacktype2 , attacktype2_txt , attacktype3 , attacktype3_txt , targtype1 , targtype1_txt , targsubtype1 , targsubtype1_txt , corp1 , target1 , natlty1 , natlty1_txt , targtype2 , targtype2_txt , targsubtype2 , targsubtype2_txt , corp2 , target2 , natlty2 , natlty2_txt , targtype3 , targtype3_txt , targsubtype3 , targsubtype3_txt , corp3 , target3 , natlty3 , natlty3_txt , gname , gsubname , gname2 , gsubname2 , gname3 , gsubname3 , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weaptype1 , weaptype1_txt , weapsubtype1 , weapsubtype1_txt , weaptype2 , weaptype2_txt , weapsubtype2 , weapsubtype2_txt , weaptype3 , weaptype3_txt , weapsubtype3 , weapsubtype3_txt , weaptype4 , weaptype4_txt , weapsubtype4 , weapsubtype4_txt , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47220960",
   "metadata": {},
   "source": [
    "---\n",
    "## 2-DimAttackType\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2de450f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(9, 2)\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>Attack Type</th>\n",
       "      <th>Attack Type Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Assassination</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hostage Taking (Kidnapping)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bombing/Explosion</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Facility/Infrastructure Attack</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Armed Assault</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Hijacking</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>Unarmed Assault</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>Hostage Taking (Barricade Incident)</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Attack Type  Attack Type Id\n",
       "0                          Assassination               1\n",
       "1            Hostage Taking (Kidnapping)               2\n",
       "3                      Bombing/Explosion               3\n",
       "4         Facility/Infrastructure Attack               4\n",
       "5                          Armed Assault               5\n",
       "12                             Hijacking               6\n",
       "16                               Unknown               7\n",
       "298                      Unarmed Assault               8\n",
       "336  Hostage Taking (Barricade Incident)               9"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_attack_type = df.loc[:, ['attacktype1_txt']]\n",
    "\n",
    "dim_attack_type['attacktype1_txt'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_attack_type.drop_duplicates(inplace=True)\n",
    "dim_attack_type['Attack Type Id'] = dim_attack_type.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_attack_type.rename(columns={'attacktype1_txt': 'Attack Type'}, inplace=True)\n",
    "\n",
    "print(dim_attack_type.shape)\n",
    "dim_attack_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "47b7dce5",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>scite2</th>\n",
       "      <th>scite3</th>\n",
       "      <th>dbsource</th>\n",
       "      <th>INT_LOG</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 123 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity location  ... scite2  scite3  dbsource  INT_LOG  \\\n",
       "0          1.0         0      NaN  ...    NaN     NaN      PGIS        0   \n",
       "1          1.0         0      NaN  ...    NaN     NaN      PGIS        0   \n",
       "2          4.0         0      NaN  ...    NaN     NaN      PGIS       -9   \n",
       "3          1.0         0      NaN  ...    NaN     NaN      PGIS       -9   \n",
       "4          1.0         0      NaN  ...    NaN     NaN      PGIS       -9   \n",
       "\n",
       "   INT_IDEO  INT_MISC INT_ANY  related  Location Id  Attack Type Id  \n",
       "0         0         0       0      NaN          NaN               1  \n",
       "1         1         1       1      NaN          NaN               2  \n",
       "2        -9         1       1      NaN          3.0               1  \n",
       "3        -9         1       1      NaN          4.0               3  \n",
       "4        -9         1       1      NaN          5.0               4  \n",
       "\n",
       "[5 rows x 123 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_attack_type,\n",
    "              how='left',\n",
    "              left_on=['attacktype1_txt'],\n",
    "              right_on=['Attack Type'])\n",
    "\n",
    "df.drop([\n",
    "    'attacktype1',\n",
    "    'attacktype1_txt',\n",
    "    'attacktype2',\n",
    "    'attacktype2_txt',\n",
    "    'attacktype3',\n",
    "    'attacktype3_txt',\n",
    "    'Attack Type'], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5aaed30b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 123) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , targtype1 , targtype1_txt , targsubtype1 , targsubtype1_txt , corp1 , target1 , natlty1 , natlty1_txt , targtype2 , targtype2_txt , targsubtype2 , targsubtype2_txt , corp2 , target2 , natlty2 , natlty2_txt , targtype3 , targtype3_txt , targsubtype3 , targsubtype3_txt , corp3 , target3 , natlty3 , natlty3_txt , gname , gsubname , gname2 , gsubname2 , gname3 , gsubname3 , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weaptype1 , weaptype1_txt , weapsubtype1 , weapsubtype1_txt , weaptype2 , weaptype2_txt , weapsubtype2 , weapsubtype2_txt , weaptype3 , weaptype3_txt , weapsubtype3 , weapsubtype3_txt , weaptype4 , weaptype4_txt , weapsubtype4 , weapsubtype4_txt , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91932818",
   "metadata": {},
   "source": [
    "---\n",
    "## 3-DimTargetType & DimTarget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bc1820cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(139, 3)\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>Target Type</th>\n",
       "      <th>Target Subtype</th>\n",
       "      <th>Target Type Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Private Citizens &amp; Property</td>\n",
       "      <td>Named Civilian</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Government (Diplomatic)</td>\n",
       "      <td>Diplomatic Personnel (outside of embassy, cons...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Journalists &amp; Media</td>\n",
       "      <td>Radio Journalist/Staff/Facility</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Government (Diplomatic)</td>\n",
       "      <td>Embassy/Consulate</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Police</td>\n",
       "      <td>Police Building (headquarters, station, school)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Target Type  \\\n",
       "0  Private Citizens & Property   \n",
       "1      Government (Diplomatic)   \n",
       "2          Journalists & Media   \n",
       "3      Government (Diplomatic)   \n",
       "5                       Police   \n",
       "\n",
       "                                      Target Subtype  Target Type Id  \n",
       "0                                     Named Civilian               1  \n",
       "1  Diplomatic Personnel (outside of embassy, cons...               2  \n",
       "2                    Radio Journalist/Staff/Facility               3  \n",
       "3                                  Embassy/Consulate               4  \n",
       "5    Police Building (headquarters, station, school)               5  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_target_type = df.loc[:, ['targtype1_txt', 'targsubtype1_txt']]\n",
    "\n",
    "dim_target_type['targtype1_txt'].fillna('Unknown', inplace=True)\n",
    "dim_target_type['targsubtype1_txt'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_target_type.drop_duplicates(inplace=True)\n",
    "dim_target_type['Target Type Id'] = dim_target_type.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_target_type.rename(columns={\n",
    "    'targtype1_txt': 'Target Type',\n",
    "    'targsubtype1_txt': 'Target Subtype',\n",
    "}, inplace=True)\n",
    "\n",
    "print(dim_target_type.shape)\n",
    "dim_target_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4069ff14",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(114866, 4)\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>Target</th>\n",
       "      <th>Corporate</th>\n",
       "      <th>Nationality</th>\n",
       "      <th>Target Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Julio Guzman</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nadine Chaval, daughter</td>\n",
       "      <td>Belgian Ambassador Daughter</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Employee</td>\n",
       "      <td>Voice of America</td>\n",
       "      <td>United States</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U.S. Embassy</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>United States</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>U.S. Consulate</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>United States</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Target                    Corporate         Nationality  \\\n",
       "0             Julio Guzman                      Unknown  Dominican Republic   \n",
       "1  Nadine Chaval, daughter  Belgian Ambassador Daughter             Belgium   \n",
       "2                 Employee             Voice of America       United States   \n",
       "3             U.S. Embassy                      Unknown       United States   \n",
       "4           U.S. Consulate                      Unknown       United States   \n",
       "\n",
       "   Target Id  \n",
       "0          1  \n",
       "1          2  \n",
       "2          3  \n",
       "3          4  \n",
       "4          5  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_target = df.loc[:, [\n",
    "    'target1',\n",
    "    'corp1',\n",
    "    'natlty1_txt'\n",
    "]]\n",
    "\n",
    "dim_target['target1'].fillna('Unknown', inplace=True)\n",
    "dim_target['corp1'].fillna('Unknown', inplace=True)\n",
    "dim_target['corp1'] = dim_target['corp1'].str.replace('\\\"', '')\n",
    "dim_target['natlty1_txt'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_target.drop_duplicates(inplace=True)\n",
    "dim_target['Target Id'] = dim_target.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_target.rename(columns={\n",
    "    'target1': 'Target',\n",
    "    'corp1': 'Corporate',\n",
    "    'natlty1_txt': 'Nationality'\n",
    "}, inplace=True)\n",
    "\n",
    "print(dim_target.shape)\n",
    "dim_target.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "92ce650e",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>dbsource</th>\n",
       "      <th>INT_LOG</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PGIS</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 101 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity location  ... dbsource  INT_LOG  INT_IDEO  INT_MISC  \\\n",
       "0          1.0         0      NaN  ...     PGIS        0         0         0   \n",
       "1          1.0         0      NaN  ...     PGIS        0         1         1   \n",
       "2          4.0         0      NaN  ...     PGIS       -9        -9         1   \n",
       "3          1.0         0      NaN  ...     PGIS       -9        -9         1   \n",
       "4          1.0         0      NaN  ...     PGIS       -9        -9         1   \n",
       "\n",
       "   INT_ANY  related Location Id  Attack Type Id  Target Type Id  Target Id  \n",
       "0        0      NaN         NaN               1             1.0        NaN  \n",
       "1        1      NaN         NaN               2             2.0        2.0  \n",
       "2        1      NaN         3.0               1             3.0        3.0  \n",
       "3        1      NaN         4.0               3             4.0        NaN  \n",
       "4        1      NaN         5.0               4             4.0        NaN  \n",
       "\n",
       "[5 rows x 101 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_target_type,\n",
    "              how='left',\n",
    "              left_on=['targtype1_txt', 'targsubtype1_txt'],\n",
    "              right_on=['Target Type', 'Target Subtype'])\n",
    "df = df.merge(dim_target,\n",
    "              how='left',\n",
    "              left_on=['target1', 'corp1', 'natlty1_txt'],\n",
    "              right_on=['Target', 'Corporate', 'Nationality'])\n",
    "\n",
    "df.drop([\n",
    "    'targtype1', 'targtype1_txt', 'targsubtype1', 'targsubtype1_txt',\n",
    "    'corp1', 'target1', 'natlty1', 'natlty1_txt', \n",
    "    'targtype2', 'targtype2_txt', 'targsubtype2', 'targsubtype2_txt', \n",
    "    'corp2', 'target2', 'natlty2', 'natlty2_txt', \n",
    "    'targtype3', 'targtype3_txt', 'targsubtype3', 'targsubtype3_txt', \n",
    "    'corp3', 'target3', 'natlty3', 'natlty3_txt',\n",
    "    'Target Type', 'Target Subtype',\n",
    "    'Target', 'Corporate', 'Nationality'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6d4d2560",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 101) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , gname , gsubname , gname2 , gsubname2 , gname3 , gsubname3 , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weaptype1 , weaptype1_txt , weapsubtype1 , weapsubtype1_txt , weaptype2 , weaptype2_txt , weapsubtype2 , weapsubtype2_txt , weaptype3 , weaptype3_txt , weapsubtype3 , weapsubtype3_txt , weaptype4 , weaptype4_txt , weapsubtype4 , weapsubtype4_txt , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7307d697",
   "metadata": {},
   "source": [
    "---\n",
    "## 4- DimWeapon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7797681f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(45, 3)\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>Weapon Type</th>\n",
       "      <th>Weapon Subtype</th>\n",
       "      <th>Weapon Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Explosives</td>\n",
       "      <td>Unknown Explosive Type</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Incendiary</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Firearms</td>\n",
       "      <td>Unknown Gun Type</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Firearms</td>\n",
       "      <td>Automatic or Semi-Automatic Rifle</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Weapon Type                     Weapon Subtype  Weapon Id\n",
       "0     Unknown                            Unknown          1\n",
       "3  Explosives             Unknown Explosive Type          2\n",
       "4  Incendiary                            Unknown          3\n",
       "5    Firearms                   Unknown Gun Type          4\n",
       "6    Firearms  Automatic or Semi-Automatic Rifle          5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_weapon = df.loc[:, [\n",
    "    'weaptype1_txt',\n",
    "    'weapsubtype1_txt'\n",
    "]]\n",
    "\n",
    "dim_weapon['weaptype1_txt'].fillna('Unknown', inplace=True)\n",
    "dim_weapon['weapsubtype1_txt'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_weapon.drop_duplicates(inplace=True)\n",
    "dim_weapon['Weapon Id'] = dim_weapon.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_weapon.rename(columns={\n",
    "    'weaptype1_txt': 'Weapon Type',\n",
    "    'weapsubtype1_txt': 'Weapon Subtype'\n",
    "}, inplace=True)\n",
    "\n",
    "print(dim_weapon.shape)\n",
    "dim_weapon.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1a0ecb40",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>INT_LOG</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 86 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity location  ... INT_LOG  INT_IDEO  INT_MISC  INT_ANY  \\\n",
       "0          1.0         0      NaN  ...       0         0         0        0   \n",
       "1          1.0         0      NaN  ...       0         1         1        1   \n",
       "2          4.0         0      NaN  ...      -9        -9         1        1   \n",
       "3          1.0         0      NaN  ...      -9        -9         1        1   \n",
       "4          1.0         0      NaN  ...      -9        -9         1        1   \n",
       "\n",
       "   related  Location Id Attack Type Id  Target Type Id  Target Id  Weapon Id  \n",
       "0      NaN          NaN              1             1.0        NaN        NaN  \n",
       "1      NaN          NaN              2             2.0        2.0        NaN  \n",
       "2      NaN          3.0              1             3.0        3.0        NaN  \n",
       "3      NaN          4.0              3             4.0        NaN        2.0  \n",
       "4      NaN          5.0              4             4.0        NaN        NaN  \n",
       "\n",
       "[5 rows x 86 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_weapon,\n",
    "              how='left',\n",
    "              left_on=['weaptype1_txt','weapsubtype1_txt'],\n",
    "              right_on=['Weapon Type', 'Weapon Subtype'])\n",
    "\n",
    "df.drop([\n",
    "    'weaptype1', 'weaptype1_txt', 'weapsubtype1', 'weapsubtype1_txt', \n",
    "    'weaptype2', 'weaptype2_txt', 'weapsubtype2', 'weapsubtype2_txt', \n",
    "    'weaptype3', 'weaptype3_txt', 'weapsubtype3', 'weapsubtype3_txt', \n",
    "    'weaptype4', 'weaptype4_txt', 'weapsubtype4', 'weapsubtype4_txt',\n",
    "    'Weapon Type', 'Weapon Subtype'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4e9b8031",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 86) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , gname , gsubname , gname2 , gsubname2 , gname3 , gsubname3 , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c543dc1e",
   "metadata": {},
   "source": [
    "---\n",
    "## 5-DimGroup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e798320c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3537, 2)\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>Group</th>\n",
       "      <th>Group Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MANO-D</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23rd of September Communist League</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Black Nationalists</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tupamaros (Uruguay)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Group  Group Id\n",
       "0                              MANO-D         1\n",
       "1  23rd of September Communist League         2\n",
       "2                             Unknown         3\n",
       "5                  Black Nationalists         4\n",
       "6                 Tupamaros (Uruguay)         5"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_group = df.loc[:, ['gname']]\n",
    "\n",
    "dim_group['gname'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_group.drop_duplicates(inplace=True)\n",
    "dim_group['Group Id'] = dim_group.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_group.rename(columns={\n",
    "    'gname': 'Group',\n",
    "}, inplace=True)\n",
    "\n",
    "print(dim_group.shape)\n",
    "dim_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "24e6257c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Group Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 81 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity location  ... INT_IDEO  INT_MISC  INT_ANY  related  \\\n",
       "0          1.0         0      NaN  ...        0         0        0      NaN   \n",
       "1          1.0         0      NaN  ...        1         1        1      NaN   \n",
       "2          4.0         0      NaN  ...       -9         1        1      NaN   \n",
       "3          1.0         0      NaN  ...       -9         1        1      NaN   \n",
       "4          1.0         0      NaN  ...       -9         1        1      NaN   \n",
       "\n",
       "   Location Id  Attack Type Id Target Type Id  Target Id  Weapon Id  Group Id  \n",
       "0          NaN               1            1.0        NaN        NaN         1  \n",
       "1          NaN               2            2.0        2.0        NaN         2  \n",
       "2          3.0               1            3.0        3.0        NaN         3  \n",
       "3          4.0               3            4.0        NaN        2.0         3  \n",
       "4          5.0               4            4.0        NaN        NaN         3  \n",
       "\n",
       "[5 rows x 81 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_group, how='left', left_on=['gname'], right_on=['Group'])\n",
    "\n",
    "df.drop([\n",
    "    'gname',\n",
    "    'gsubname',\n",
    "    'gname2',\n",
    "    'gsubname2',\n",
    "    'gname3',\n",
    "    'gsubname3',\n",
    "    'Group'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9c7b26c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 81) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , location , summary , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , motive , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , weapdetail , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , propcomment , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , ransomnote , hostkidoutcome , hostkidoutcome_txt , nreleased , addnotes , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f76a56f",
   "metadata": {},
   "source": [
    "---\n",
    "## 6-DimEventDesc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "bc513f06",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 8)\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>eventid</th>\n",
       "      <th>Location</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Motive</th>\n",
       "      <th>Weapon Details</th>\n",
       "      <th>Ransom Note</th>\n",
       "      <th>Property Damage Comment</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Explosive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Incendiary</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid Location Summary Motive Weapon Details Ransom Note  \\\n",
       "0  197000000001      NaN     NaN    NaN            NaN         NaN   \n",
       "1  197000000002      NaN     NaN    NaN            NaN         NaN   \n",
       "2  197001000001      NaN     NaN    NaN            NaN         NaN   \n",
       "3  197001000002      NaN     NaN    NaN      Explosive         NaN   \n",
       "4  197001000003      NaN     NaN    NaN     Incendiary         NaN   \n",
       "\n",
       "  Property Damage Comment Notes  \n",
       "0                     NaN   NaN  \n",
       "1                     NaN   NaN  \n",
       "2                     NaN   NaN  \n",
       "3                     NaN   NaN  \n",
       "4                     NaN   NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_event_desc = df.loc[:, [\n",
    "    'eventid',\n",
    "    'location',\n",
    "    'summary',\n",
    "    'motive',\n",
    "    'weapdetail',\n",
    "    'ransomnote',\n",
    "    'propcomment',\n",
    "    'addnotes'\n",
    "]]\n",
    "\n",
    "dim_event_desc.rename(columns={\n",
    "    'location': 'Location',\n",
    "    'summary': 'Summary',\n",
    "    'motive': 'Motive',\n",
    "    'weapdetail': 'Weapon Details',\n",
    "    'ransomnote': 'Ransom Note', \n",
    "    'propcomment': 'Property Damage Comment',\n",
    "    'addnotes': 'Notes'\n",
    "}, inplace=True)\n",
    "\n",
    "print(dim_event_desc.shape)\n",
    "dim_event_desc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2f0543f3",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>crit1</th>\n",
       "      <th>...</th>\n",
       "      <th>INT_IDEO</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Group Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity  crit1  ...  INT_IDEO  INT_MISC  INT_ANY  related  \\\n",
       "0          1.0         0      1  ...         0         0        0      NaN   \n",
       "1          1.0         0      1  ...         1         1        1      NaN   \n",
       "2          4.0         0      1  ...        -9         1        1      NaN   \n",
       "3          1.0         0      1  ...        -9         1        1      NaN   \n",
       "4          1.0         0      1  ...        -9         1        1      NaN   \n",
       "\n",
       "  Location Id  Attack Type Id  Target Type Id  Target Id  Weapon Id  Group Id  \n",
       "0         NaN               1             1.0        NaN        NaN         1  \n",
       "1         NaN               2             2.0        2.0        NaN         2  \n",
       "2         3.0               1             3.0        3.0        NaN         3  \n",
       "3         4.0               3             4.0        NaN        2.0         3  \n",
       "4         5.0               4             4.0        NaN        NaN         3  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop([\n",
    "    'location',\n",
    "    'summary',\n",
    "    'motive',\n",
    "    'weapdetail',\n",
    "    'ransomnote',\n",
    "    'propcomment',\n",
    "    'addnotes'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "07fc53fd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 74) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claimmode_txt , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , hostkidoutcome , hostkidoutcome_txt , nreleased , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb532cd0",
   "metadata": {},
   "source": [
    "___\n",
    "## 7- DimCalimMode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "64a9c9cf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10, 2)\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>Calim Mode</th>\n",
       "      <th>Claim Mode Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Letter</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Call (pre-incident)</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Other</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Personal claim</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>Note left at scene</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>786</th>\n",
       "      <td>Call (post-incident)</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68253</th>\n",
       "      <td>Posted to website, blog, etc.</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68281</th>\n",
       "      <td>E-mail</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70371</th>\n",
       "      <td>Video</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Calim Mode  Claim Mode Id\n",
       "0                            Unknown              1\n",
       "8                             Letter              2\n",
       "129              Call (pre-incident)              3\n",
       "139                            Other              4\n",
       "225                   Personal claim              5\n",
       "606               Note left at scene              6\n",
       "786             Call (post-incident)              7\n",
       "68253  Posted to website, blog, etc.              8\n",
       "68281                         E-mail              9\n",
       "70371                          Video             10"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_claim_mode = df.loc[:, ['claimmode_txt']]\n",
    "\n",
    "dim_claim_mode['claimmode_txt'].fillna('Unknown', inplace=True)\n",
    "\n",
    "dim_claim_mode.drop_duplicates(inplace=True)\n",
    "dim_claim_mode['Claim Mode Id'] = dim_claim_mode.reset_index(drop=True).index + 1\n",
    "\n",
    "dim_claim_mode.rename(columns={'claimmode_txt': 'Calim Mode'}, inplace=True)\n",
    "\n",
    "print(dim_claim_mode.shape)\n",
    "dim_claim_mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "75001f16",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>crit1</th>\n",
       "      <th>...</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Group Id</th>\n",
       "      <th>Claim Mode Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity  crit1  ...  INT_MISC  INT_ANY  related  Location Id  \\\n",
       "0          1.0         0      1  ...         0        0      NaN          NaN   \n",
       "1          1.0         0      1  ...         1        1      NaN          NaN   \n",
       "2          4.0         0      1  ...         1        1      NaN          3.0   \n",
       "3          1.0         0      1  ...         1        1      NaN          4.0   \n",
       "4          1.0         0      1  ...         1        1      NaN          5.0   \n",
       "\n",
       "  Attack Type Id  Target Type Id  Target Id  Weapon Id  Group Id  \\\n",
       "0              1             1.0        NaN        NaN         1   \n",
       "1              2             2.0        2.0        NaN         2   \n",
       "2              1             3.0        3.0        NaN         3   \n",
       "3              3             4.0        NaN        2.0         3   \n",
       "4              4             4.0        NaN        NaN         3   \n",
       "\n",
       "   Claim Mode Id  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(dim_claim_mode, how='left', left_on=['claimmode_txt'], right_on=['Calim Mode'])\n",
    "\n",
    "df.drop(['claimmode_txt', 'Calim Mode'], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6e950eba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 74) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , ishostkid , nhostkid , nhostkidus , nhours , ndays , divert , kidhijcountry , ransom , ransomamt , ransomamtus , ransompaid , ransompaidus , hostkidoutcome , hostkidoutcome_txt , nreleased , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id , Claim Mode Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adf63f57",
   "metadata": {},
   "source": [
    "___\n",
    "## 8- FactKidnapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3fa35263",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>Number Of Hostages Killed</th>\n",
       "      <th>Number Of Hostages Killed (US)</th>\n",
       "      <th>Hours of Kidnapping</th>\n",
       "      <th>Days of Kidnapping</th>\n",
       "      <th>Country That Kidnappers Diverted To</th>\n",
       "      <th>Country of Kidnapping Resolution</th>\n",
       "      <th>Ransom Demanded?</th>\n",
       "      <th>Ransom Amount</th>\n",
       "      <th>Ransom Amount (US)</th>\n",
       "      <th>Ransom Paid</th>\n",
       "      <th>Ransom Paid (US)</th>\n",
       "      <th>Kidnapping Outcome</th>\n",
       "      <th>Number Released/Escaped/Rescued</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1.0</td>\n",
       "      <td>800000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>197001220001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Venezuela</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hostage(s) released by perpetrators</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>197003030001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Spain</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>197003060001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Guatemala</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hostage(s) released by perpetrators</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>197003110001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hostage(s) released by perpetrators</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181620</th>\n",
       "      <td>201712280040</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Successful Rescue</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181630</th>\n",
       "      <td>201712290008</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>-99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181636</th>\n",
       "      <td>201712290017</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>-99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181660</th>\n",
       "      <td>201712300018</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Successful Rescue</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181684</th>\n",
       "      <td>201712310019</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Successful Rescue</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13572 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             eventid  Number Of Hostages Killed  \\\n",
       "1       197000000002                        1.0   \n",
       "27      197001220001                        1.0   \n",
       "103     197003030001                        1.0   \n",
       "112     197003060001                        1.0   \n",
       "123     197003110001                        1.0   \n",
       "...              ...                        ...   \n",
       "181620  201712280040                        3.0   \n",
       "181630  201712290008                        1.0   \n",
       "181636  201712290017                       12.0   \n",
       "181660  201712300018                       11.0   \n",
       "181684  201712310019                        1.0   \n",
       "\n",
       "        Number Of Hostages Killed (US)  Hours of Kidnapping  \\\n",
       "1                                  0.0                  NaN   \n",
       "27                                 0.0                  NaN   \n",
       "103                                0.0                  NaN   \n",
       "112                                1.0                  0.0   \n",
       "123                                0.0                  0.0   \n",
       "...                                ...                  ...   \n",
       "181620                             0.0                  NaN   \n",
       "181630                             0.0                  NaN   \n",
       "181636                             0.0                  NaN   \n",
       "181660                             0.0                -99.0   \n",
       "181684                             0.0                 14.0   \n",
       "\n",
       "        Days of Kidnapping Country That Kidnappers Diverted To  \\\n",
       "1                      NaN                             Unknown   \n",
       "27                     NaN                             Unknown   \n",
       "103                    NaN                             Unknown   \n",
       "112                    2.0                             Unknown   \n",
       "123                    4.0                             Unknown   \n",
       "...                    ...                                 ...   \n",
       "181620                 2.0                             Unknown   \n",
       "181630               -99.0                             Unknown   \n",
       "181636               -99.0                             Unknown   \n",
       "181660                 NaN                             Unknown   \n",
       "181684                 NaN                             Unknown   \n",
       "\n",
       "       Country of Kidnapping Resolution  Ransom Demanded?  Ransom Amount  \\\n",
       "1                                Mexico               1.0       800000.0   \n",
       "27                            Venezuela               1.0       110000.0   \n",
       "103                               Spain               1.0            0.0   \n",
       "112                           Guatemala               1.0            0.0   \n",
       "123                              Brazil               1.0            0.0   \n",
       "...                                 ...               ...            ...   \n",
       "181620                          Unknown               0.0            NaN   \n",
       "181630                          Unknown               0.0            NaN   \n",
       "181636                          Unknown               0.0            NaN   \n",
       "181660                          Unknown               0.0            NaN   \n",
       "181684                          Unknown               0.0            NaN   \n",
       "\n",
       "        Ransom Amount (US)  Ransom Paid  Ransom Paid (US)  \\\n",
       "1                      NaN          NaN               NaN   \n",
       "27                     NaN     110000.0               NaN   \n",
       "103                    NaN          NaN               NaN   \n",
       "112                    NaN          NaN               NaN   \n",
       "123                    NaN          NaN               NaN   \n",
       "...                    ...          ...               ...   \n",
       "181620                 NaN          NaN               NaN   \n",
       "181630                 NaN          NaN               NaN   \n",
       "181636                 NaN          NaN               NaN   \n",
       "181660                 NaN          NaN               NaN   \n",
       "181684                 NaN          NaN               NaN   \n",
       "\n",
       "                         Kidnapping Outcome  Number Released/Escaped/Rescued  \n",
       "1                                   Unknown                              NaN  \n",
       "27      Hostage(s) released by perpetrators                              1.0  \n",
       "103                                 Unknown                              NaN  \n",
       "112     Hostage(s) released by perpetrators                              1.0  \n",
       "123     Hostage(s) released by perpetrators                              1.0  \n",
       "...                                     ...                              ...  \n",
       "181620                    Successful Rescue                              3.0  \n",
       "181630                              Unknown                            -99.0  \n",
       "181636                              Unknown                            -99.0  \n",
       "181660                    Successful Rescue                             11.0  \n",
       "181684                    Successful Rescue                              1.0  \n",
       "\n",
       "[13572 rows x 14 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fact_kidnapping = df.loc[(df['ishostkid'] == 1), [\n",
    "    'eventid',\n",
    "    'nhostkid',\n",
    "    'nhostkidus',\n",
    "    'nhours',\n",
    "    'ndays',\n",
    "    'divert',\n",
    "    'kidhijcountry',\n",
    "    'ransom', \n",
    "    'ransomamt', \n",
    "    'ransomamtus', \n",
    "    'ransompaid', \n",
    "    'ransompaidus', \n",
    "    'hostkidoutcome_txt', \n",
    "    'nreleased'\n",
    "]]\n",
    "\n",
    "fact_kidnapping = fact_kidnapping.apply(lambda x: x.fillna(np.nan) if x.dtype.kind in 'biufc' else x.fillna('Unknown'))\n",
    "\n",
    "fact_kidnapping.drop_duplicates(inplace=True)\n",
    "\n",
    "fact_kidnapping.rename(columns={\n",
    "    'nhostkid': 'Number Of Hostages Killed',\n",
    "    'nhostkidus': 'Number Of Hostages Killed (US)',\n",
    "    'nhours': 'Hours of Kidnapping',\n",
    "    'ndays': 'Days of Kidnapping',\n",
    "    'divert': 'Country That Kidnappers Diverted To',\n",
    "    'kidhijcountry': 'Country of Kidnapping Resolution',\n",
    "    'ransom': 'Ransom Demanded?', \n",
    "    'ransomamt': 'Ransom Amount', \n",
    "    'ransomamtus': 'Ransom Amount (US)', \n",
    "    'ransompaid': 'Ransom Paid', \n",
    "    'ransompaidus': 'Ransom Paid (US)', \n",
    "    'hostkidoutcome_txt': 'Kidnapping Outcome', \n",
    "    'nreleased': 'Number Released/Escaped/Rescued'\n",
    "}, inplace=True)\n",
    "\n",
    "fact_kidnapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "fddc03fa",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>iyear</th>\n",
       "      <th>imonth</th>\n",
       "      <th>iday</th>\n",
       "      <th>approxdate</th>\n",
       "      <th>extended</th>\n",
       "      <th>resolution</th>\n",
       "      <th>specificity</th>\n",
       "      <th>vicinity</th>\n",
       "      <th>crit1</th>\n",
       "      <th>...</th>\n",
       "      <th>INT_MISC</th>\n",
       "      <th>INT_ANY</th>\n",
       "      <th>related</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Group Id</th>\n",
       "      <th>Claim Mode Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1970</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  iyear  imonth  iday approxdate  extended resolution  \\\n",
       "0  197000000001   1970       7     2        NaN         0        NaN   \n",
       "1  197000000002   1970       0     0        NaN         0        NaN   \n",
       "2  197001000001   1970       1     0        NaN         0        NaN   \n",
       "3  197001000002   1970       1     0        NaN         0        NaN   \n",
       "4  197001000003   1970       1     0        NaN         0        NaN   \n",
       "\n",
       "   specificity  vicinity  crit1  ...  INT_MISC  INT_ANY  related  Location Id  \\\n",
       "0          1.0         0      1  ...         0        0      NaN          NaN   \n",
       "1          1.0         0      1  ...         1        1      NaN          NaN   \n",
       "2          4.0         0      1  ...         1        1      NaN          3.0   \n",
       "3          1.0         0      1  ...         1        1      NaN          4.0   \n",
       "4          1.0         0      1  ...         1        1      NaN          5.0   \n",
       "\n",
       "  Attack Type Id  Target Type Id  Target Id  Weapon Id  Group Id  \\\n",
       "0              1             1.0        NaN        NaN         1   \n",
       "1              2             2.0        2.0        NaN         2   \n",
       "2              1             3.0        3.0        NaN         3   \n",
       "3              3             4.0        NaN        2.0         3   \n",
       "4              4             4.0        NaN        NaN         3   \n",
       "\n",
       "   Claim Mode Id  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop([\n",
    "    'ishostkid' ,\n",
    "    'nhostkid',\n",
    "    'nhostkidus',\n",
    "    'nhours',\n",
    "    'ndays',\n",
    "    'divert',\n",
    "    'kidhijcountry',\n",
    "    'ransom',\n",
    "    'ransomamt',\n",
    "    'ransomamtus',\n",
    "    'ransompaid',\n",
    "    'ransompaidus',\n",
    "    'hostkidoutcome',\n",
    "    'hostkidoutcome_txt',\n",
    "    'nreleased'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e3a63830",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 59) \n",
      "\n",
      "eventid , iyear , imonth , iday , approxdate , extended , resolution , specificity , vicinity , crit1 , crit2 , crit3 , doubtterr , alternative , alternative_txt , multiple , success , suicide , guncertain1 , guncertain2 , guncertain3 , individual , nperps , nperpcap , claimed , claimmode , claim2 , claimmode2 , claimmode2_txt , claim3 , claimmode3 , claimmode3_txt , compclaim , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent , propextent_txt , propvalue , scite1 , scite2 , scite3 , dbsource , INT_LOG , INT_IDEO , INT_MISC , INT_ANY , related , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id , Claim Mode Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f30c719",
   "metadata": {},
   "source": [
    "---\n",
    "## 9- FactAttackEvent Clean-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "218464da",
   "metadata": {},
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>success</th>\n",
       "      <th>suicide</th>\n",
       "      <th>individual</th>\n",
       "      <th>nperps</th>\n",
       "      <th>nperpcap</th>\n",
       "      <th>claimed</th>\n",
       "      <th>nkill</th>\n",
       "      <th>nkillus</th>\n",
       "      <th>nkillter</th>\n",
       "      <th>...</th>\n",
       "      <th>property</th>\n",
       "      <th>propextent_txt</th>\n",
       "      <th>propvalue</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Group Id</th>\n",
       "      <th>Claim Mode Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventid  success  suicide  individual  nperps  nperpcap  claimed  \\\n",
       "0  197000000001        1        0           0     NaN       NaN      NaN   \n",
       "1  197000000002        1        0           0     7.0       NaN      NaN   \n",
       "2  197001000001        1        0           0     NaN       NaN      NaN   \n",
       "3  197001000002        1        0           0     NaN       NaN      NaN   \n",
       "4  197001000003        1        0           0     NaN       NaN      NaN   \n",
       "\n",
       "   nkill  nkillus  nkillter  ...  property  propextent_txt  propvalue  \\\n",
       "0    1.0      NaN       NaN  ...         0             NaN        NaN   \n",
       "1    0.0      NaN       NaN  ...         0             NaN        NaN   \n",
       "2    1.0      NaN       NaN  ...         0             NaN        NaN   \n",
       "3    NaN      NaN       NaN  ...         1             NaN        NaN   \n",
       "4    NaN      NaN       NaN  ...         1             NaN        NaN   \n",
       "\n",
       "   Location Id Attack Type Id  Target Type Id  Target Id  Weapon Id  Group Id  \\\n",
       "0          NaN              1             1.0        NaN        NaN         1   \n",
       "1          NaN              2             2.0        2.0        NaN         2   \n",
       "2          3.0              1             3.0        3.0        NaN         3   \n",
       "3          4.0              3             4.0        NaN        2.0         3   \n",
       "4          5.0              4             4.0        NaN        NaN         3   \n",
       "\n",
       "   Claim Mode Id  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop([\n",
    "    'iyear', 'imonth', 'iday', 'approxdate',\n",
    "    'extended', 'resolution', 'specificity', 'vicinity',\n",
    "    'crit1', 'crit2', 'crit3', 'doubtterr', 'alternative', 'alternative_txt', 'multiple',\n",
    "    'guncertain1', 'guncertain2', 'guncertain3', 'compclaim', 'propextent',\n",
    "    'claimmode', 'claim2' , 'claimmode2' , 'claimmode2_txt' , 'claim3' , 'claimmode3' , 'claimmode3_txt',\n",
    "    'scite1' , 'scite2' , 'scite3' , 'dbsource' , 'INT_LOG' , 'INT_IDEO' , 'INT_MISC' , 'INT_ANY' , 'related'\n",
    "], axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ea0a2098",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 23) \n",
      "\n",
      "eventid , success , suicide , individual , nperps , nperpcap , claimed , nkill , nkillus , nkillter , nwound , nwoundus , nwoundte , property , propextent_txt , propvalue , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id , Claim Mode Id\n"
     ]
    }
   ],
   "source": [
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "182f5167",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(181691, 23) \n",
      "\n",
      "eventid , Successful Attack? , Suicide Attack? , Unaffiliated Individual(s) , Number of Perpetrators , Number of Perpetrators Captured , Claim of Responsibility? , Number of Fatalities , Number of Fatalities (US) , Number of Perpetrator Fatalities , Number of Injured , Number of Injured (US) , Number of Perpetrators Injured , Property Damage? , Extent of Property Damage , Value of Property Damage (USD) , Location Id , Attack Type Id , Target Type Id , Target Id , Weapon Id , Group Id , Claim Mode Id\n"
     ]
    }
   ],
   "source": [
    "df.rename(columns={\n",
    "    'success': 'Successful Attack?',\n",
    "    'suicide': 'Suicide Attack?',\n",
    "    'individual': 'Unaffiliated Individual(s)',\n",
    "    'nperps': 'Number of Perpetrators',\n",
    "    'nperpcap': 'Number of Perpetrators Captured',\n",
    "    'claimed': 'Claim of Responsibility?',\n",
    "    'nkill': 'Number of Fatalities',\n",
    "    'nkillus': 'Number of Fatalities (US)',\n",
    "    'nkillter': 'Number of Perpetrator Fatalities',\n",
    "    'nwound': 'Number of Injured',\n",
    "    'nwoundus': 'Number of Injured (US)',\n",
    "    'nwoundte': 'Number of Perpetrators Injured',\n",
    "    'property': 'Property Damage?',\n",
    "    'propextent_txt': 'Extent of Property Damage',\n",
    "    'propvalue': 'Value of Property Damage (USD)'\n",
    "}, inplace=True)\n",
    "\n",
    "print(df.shape, '\\n')\n",
    "print(*df.columns, sep=' , ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "74d9f055",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reorder columns\n",
    "\n",
    "df = df.loc[:,[\n",
    "    'eventid',\n",
    "    'Location Id',\n",
    "    'Attack Type Id',\n",
    "    'Group Id',\n",
    "    'Target Type Id',\n",
    "    'Target Id',\n",
    "    'Weapon Id',\n",
    "    'Claim Mode Id',\n",
    "    'Successful Attack?',\n",
    "    'Suicide Attack?',\n",
    "    'Unaffiliated Individual(s)',\n",
    "    'Number of Perpetrators',\n",
    "    'Number of Perpetrators Captured',\n",
    "    'Claim of Responsibility?',\n",
    "    'Number of Fatalities',\n",
    "    'Number of Fatalities (US)',\n",
    "    'Number of Perpetrator Fatalities',\n",
    "    'Number of Injured',\n",
    "    'Number of Injured (US)',\n",
    "    'Number of Perpetrators Injured',\n",
    "    'Property Damage?',\n",
    "    'Extent of Property Damage',\n",
    "    'Value of Property Damage (USD)'\n",
    "]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "81b49da3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "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>eventid</th>\n",
       "      <th>Location Id</th>\n",
       "      <th>Attack Type Id</th>\n",
       "      <th>Group Id</th>\n",
       "      <th>Target Type Id</th>\n",
       "      <th>Target Id</th>\n",
       "      <th>Weapon Id</th>\n",
       "      <th>Claim Mode Id</th>\n",
       "      <th>Successful Attack?</th>\n",
       "      <th>Suicide Attack?</th>\n",
       "      <th>...</th>\n",
       "      <th>Claim of Responsibility?</th>\n",
       "      <th>Number of Fatalities</th>\n",
       "      <th>Number of Fatalities (US)</th>\n",
       "      <th>Number of Perpetrator Fatalities</th>\n",
       "      <th>Number of Injured</th>\n",
       "      <th>Number of Injured (US)</th>\n",
       "      <th>Number of Perpetrators Injured</th>\n",
       "      <th>Property Damage?</th>\n",
       "      <th>Extent of Property Damage</th>\n",
       "      <th>Value of Property Damage (USD)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>197000000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197000000002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197001000001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>197001000002</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197001000003</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181686</th>\n",
       "      <td>201712310022</td>\n",
       "      <td>40675.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2700</td>\n",
       "      <td>123.0</td>\n",
       "      <td>87673.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181687</th>\n",
       "      <td>201712310029</td>\n",
       "      <td>28343.0</td>\n",
       "      <td>3</td>\n",
       "      <td>599</td>\n",
       "      <td>10.0</td>\n",
       "      <td>114865.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181688</th>\n",
       "      <td>201712310030</td>\n",
       "      <td>40676.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2977</td>\n",
       "      <td>18.0</td>\n",
       "      <td>103106.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181689</th>\n",
       "      <td>201712310031</td>\n",
       "      <td>1592.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>9.0</td>\n",
       "      <td>114866.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181690</th>\n",
       "      <td>201712310032</td>\n",
       "      <td>1795.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52139.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>181691 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             eventid  Location Id  Attack Type Id  Group Id  Target Type Id  \\\n",
       "0       197000000001          NaN               1         1             1.0   \n",
       "1       197000000002          NaN               2         2             2.0   \n",
       "2       197001000001          3.0               1         3             3.0   \n",
       "3       197001000002          4.0               3         3             4.0   \n",
       "4       197001000003          5.0               4         3             4.0   \n",
       "...              ...          ...             ...       ...             ...   \n",
       "181686  201712310022      40675.0               5      2700           123.0   \n",
       "181687  201712310029      28343.0               3       599            10.0   \n",
       "181688  201712310030      40676.0               4      2977            18.0   \n",
       "181689  201712310031       1592.0               3         3             9.0   \n",
       "181690  201712310032       1795.0               3         3             NaN   \n",
       "\n",
       "        Target Id  Weapon Id  Claim Mode Id  Successful Attack?  \\\n",
       "0             NaN        NaN            NaN                   1   \n",
       "1             2.0        NaN            NaN                   1   \n",
       "2             3.0        NaN            NaN                   1   \n",
       "3             NaN        2.0            NaN                   1   \n",
       "4             NaN        NaN            NaN                   1   \n",
       "...           ...        ...            ...                 ...   \n",
       "181686    87673.0        4.0            1.0                   1   \n",
       "181687   114865.0       17.0            NaN                   1   \n",
       "181688   103106.0        9.0            NaN                   1   \n",
       "181689   114866.0       24.0            NaN                   0   \n",
       "181690    52139.0        2.0            NaN                   0   \n",
       "\n",
       "        Suicide Attack?  ...  Claim of Responsibility?  Number of Fatalities  \\\n",
       "0                     0  ...                   Unknown                   1.0   \n",
       "1                     0  ...                   Unknown                   0.0   \n",
       "2                     0  ...                   Unknown                   1.0   \n",
       "3                     0  ...                   Unknown                   NaN   \n",
       "4                     0  ...                   Unknown                   NaN   \n",
       "...                 ...  ...                       ...                   ...   \n",
       "181686                0  ...                       Yes                   1.0   \n",
       "181687                0  ...                        No                   2.0   \n",
       "181688                0  ...                        No                   0.0   \n",
       "181689                0  ...                        No                   0.0   \n",
       "181690                0  ...                        No                   0.0   \n",
       "\n",
       "        Number of Fatalities (US) Number of Perpetrator Fatalities  \\\n",
       "0                             NaN                              NaN   \n",
       "1                             NaN                              NaN   \n",
       "2                             NaN                              NaN   \n",
       "3                             NaN                              NaN   \n",
       "4                             NaN                              NaN   \n",
       "...                           ...                              ...   \n",
       "181686                        0.0                              0.0   \n",
       "181687                        0.0                              0.0   \n",
       "181688                        0.0                              0.0   \n",
       "181689                        0.0                              0.0   \n",
       "181690                        0.0                              0.0   \n",
       "\n",
       "        Number of Injured  Number of Injured (US)  \\\n",
       "0                     0.0                     NaN   \n",
       "1                     0.0                     NaN   \n",
       "2                     0.0                     NaN   \n",
       "3                     NaN                     NaN   \n",
       "4                     NaN                     NaN   \n",
       "...                   ...                     ...   \n",
       "181686                2.0                     0.0   \n",
       "181687                7.0                     0.0   \n",
       "181688                0.0                     0.0   \n",
       "181689                0.0                     0.0   \n",
       "181690                0.0                     0.0   \n",
       "\n",
       "        Number of Perpetrators Injured  Property Damage?  \\\n",
       "0                                  NaN                No   \n",
       "1                                  NaN                No   \n",
       "2                                  NaN                No   \n",
       "3                                  NaN               Yes   \n",
       "4                                  NaN               Yes   \n",
       "...                                ...               ...   \n",
       "181686                             0.0           Unknown   \n",
       "181687                             0.0               Yes   \n",
       "181688                             0.0               Yes   \n",
       "181689                             0.0           Unknown   \n",
       "181690                             0.0                No   \n",
       "\n",
       "        Extent of Property Damage  Value of Property Damage (USD)  \n",
       "0                         Unknown                             NaN  \n",
       "1                         Unknown                             NaN  \n",
       "2                         Unknown                             NaN  \n",
       "3                         Unknown                             NaN  \n",
       "4                         Unknown                             NaN  \n",
       "...                           ...                             ...  \n",
       "181686                    Unknown                             NaN  \n",
       "181687                    Unknown                             NaN  \n",
       "181688                    Unknown                             NaN  \n",
       "181689                    Unknown                             NaN  \n",
       "181690                    Unknown                             NaN  \n",
       "\n",
       "[181691 rows x 23 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.apply(lambda x: x.fillna(np.nan) if x.dtype.kind in 'biufc' else x.fillna('Unknown'))\n",
    "\n",
    "df.loc[\n",
    "    (df['Number of Perpetrators'] == -99.0) | (df['Number of Perpetrators'] == -9.0),\n",
    "    'Number of Perpetrators'] = np.nan\n",
    "df.loc[\n",
    "    (df['Number of Perpetrators Captured'] == -99.0) | (df['Number of Perpetrators Captured'] == -9.0),\n",
    "    'Number of Perpetrators Captured'] = np.nan\n",
    "df.loc[(df['Value of Property Damage (USD)'] == -99.0),'Value of Property Damage (USD)'] = np.nan\n",
    "\n",
    "df['Claim of Responsibility?'].replace({-9.0: 'Unknown', np.nan: 'Unknown', 0: 'No', 1: 'Yes'}, inplace=True)\n",
    "df['Property Damage?'].replace({-9.0: 'Unknown', np.nan: 'Unknown', 0: 'No', 1: 'Yes'}, inplace=True)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "75ef456c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_location.to_csv('dim_location.csv', index=False)\n",
    "dim_attack_type.to_csv('dim_attack_type.csv', index=False)\n",
    "dim_target_type.to_csv('dim_target_type.csv', index=False)\n",
    "dim_target.to_csv('dim_target.csv', index=False)\n",
    "dim_weapon.to_csv('dim_weapon.csv', index=False)\n",
    "dim_group.to_csv('dim_group.csv', index=False)\n",
    "dim_event_desc.to_csv('dim_event_desc.csv', index=False)\n",
    "dim_claim_mode.to_csv('dim_claim_mode.csv', index=False)\n",
    "fact_kidnapping.to_csv('fact_kidnapping.csv', index=False)\n",
    "df.to_csv('fact_attack_event.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}