ETL_Pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Assess"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Messages Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>id</th>\n",
       "      <th>message</th>\n",
       "      <th>original</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23710</th>\n",
       "      <td>27408</td>\n",
       "      <td>Lower cotton production will also impact on th...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10628</th>\n",
       "      <td>12191</td>\n",
       "      <td>I have children 's clothing , adult clothing ,...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15959</th>\n",
       "      <td>18787</td>\n",
       "      <td>The local office of the Meteorology, Climatolo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19683</th>\n",
       "      <td>22921</td>\n",
       "      <td>Eveline Herfkens, Minister for Development Coo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13910</th>\n",
       "      <td>16537</td>\n",
       "      <td>Contrary to the arrival of a warmer season, th...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26011</th>\n",
       "      <td>29999</td>\n",
       "      <td>A Child Friendly Environment Drive is now unde...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9193</th>\n",
       "      <td>10323</td>\n",
       "      <td>if they have cyclone,what should do?</td>\n",
       "      <td>Si cyclone nan ta ki sa pou fe</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3466</th>\n",
       "      <td>3984</td>\n",
       "      <td>There are some people that I know in central P...</td>\n",
       "      <td>Gen kek moun mwen konnen ki sit potoprens blik...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15165</th>\n",
       "      <td>17916</td>\n",
       "      <td>Officials in the Disaster Management Ministry ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10837</th>\n",
       "      <td>12436</td>\n",
       "      <td>sooo I 'm sitting in my living room .. doing n...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>social</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                            message  \\\n",
       "23710  27408  Lower cotton production will also impact on th...   \n",
       "10628  12191  I have children 's clothing , adult clothing ,...   \n",
       "15959  18787  The local office of the Meteorology, Climatolo...   \n",
       "19683  22921  Eveline Herfkens, Minister for Development Coo...   \n",
       "13910  16537  Contrary to the arrival of a warmer season, th...   \n",
       "26011  29999  A Child Friendly Environment Drive is now unde...   \n",
       "9193   10323              if they have cyclone,what should do?    \n",
       "3466    3984  There are some people that I know in central P...   \n",
       "15165  17916  Officials in the Disaster Management Ministry ...   \n",
       "10837  12436  sooo I 'm sitting in my living room .. doing n...   \n",
       "\n",
       "                                                original   genre  \n",
       "23710                                                NaN    news  \n",
       "10628                                                NaN  direct  \n",
       "15959                                                NaN    news  \n",
       "19683                                                NaN    news  \n",
       "13910                                                NaN    news  \n",
       "26011                                                NaN    news  \n",
       "9193                      Si cyclone nan ta ki sa pou fe  direct  \n",
       "3466   Gen kek moun mwen konnen ki sit potoprens blik...  direct  \n",
       "15165                                                NaN    news  \n",
       "10837                                                NaN  social  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading data\n",
    "messages = pd.read_csv('data/disaster_messages.csv')\n",
    "messages.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['direct', 'social', 'news'], dtype=object)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Unique values\n",
    "messages.genre.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26248, 4)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Shape of the data\n",
    "messages.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id              0\n",
       "message         0\n",
       "original    16064\n",
       "genre           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking null values\n",
    "messages.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Oberservations:** \n",
    "\n",
    "- Original Column can be droped\n",
    "\n",
    "- If the `genre` column is to be used, it will need to be converted into dummy variables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categories Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>id</th>\n",
       "      <th>categories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23527</th>\n",
       "      <td>27197</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25782</th>\n",
       "      <td>29733</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25939</th>\n",
       "      <td>29911</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>159</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5103</th>\n",
       "      <td>5809</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17339</th>\n",
       "      <td>20328</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9356</th>\n",
       "      <td>10505</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1134</th>\n",
       "      <td>1358</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13149</th>\n",
       "      <td>15690</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7673</th>\n",
       "      <td>8624</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                         categories\n",
       "23527  27197  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "25782  29733  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "25939  29911  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "128      159  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "5103    5809  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "17339  20328  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "9356   10505  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "1134    1358  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "13149  15690  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "7673    8624  related-1;request-0;offer-0;aid_related-0;medi..."
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories = pd.read_csv('data/disaster_categories.csv')\n",
    "categories.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26248, 2)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "related-1;request-0;offer-0;aid_related-0;medical_help-0;medical_products-0;search_and_rescue-0;security-0;military-0;child_alone-0;water-0;food-0;shelter-0;clothing-0;money-0;missing_people-0;refugees-0;death-0;other_aid-0;infrastructure_related-0;transport-0;buildings-0;electricity-0;tools-0;hospitals-0;shops-0;aid_centers-0;other_infrastructure-0;weather_related-0;floods-0;storm-0;fire-0;earthquake-0;cold-0;other_weather-0;direct_report-0\n"
     ]
    }
   ],
   "source": [
    "# Reviewing contents of categories column\n",
    "print(categories.categories.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Total Categories**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(categories.categories.iloc[0].split(';'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observations:**\n",
    "\n",
    "- To prepare for machine learning  process the categories column needs to be split into 36 columns\n",
    "\n",
    "- Once the categories have been joined to the messages, it would be possible to drop the id column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning data\n",
    "messages_clean = messages.copy()\n",
    "categories_clean = categories.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Creating new dataframe of categories with category name for heading and 1s and 0s for values**\n",
    "\n",
    "- Use `.str.split(';', expand = True)` to split data\n",
    "- Use `.str[:-2]` to get headings\n",
    "- Use `.str[-1]` and `.astype(int)` to convert data to 1s and 0s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-1</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-1</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-1</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-1</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-1</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-1</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-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",
       "      <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>26243</th>\n",
       "      <td>related-0</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26244</th>\n",
       "      <td>related-0</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26245</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26246</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-1</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-1</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26247</th>\n",
       "      <td>related-1</td>\n",
       "      <td>request-0</td>\n",
       "      <td>offer-0</td>\n",
       "      <td>aid_related-0</td>\n",
       "      <td>medical_help-0</td>\n",
       "      <td>medical_products-0</td>\n",
       "      <td>search_and_rescue-0</td>\n",
       "      <td>security-0</td>\n",
       "      <td>military-0</td>\n",
       "      <td>child_alone-0</td>\n",
       "      <td>...</td>\n",
       "      <td>aid_centers-0</td>\n",
       "      <td>other_infrastructure-0</td>\n",
       "      <td>weather_related-0</td>\n",
       "      <td>floods-0</td>\n",
       "      <td>storm-0</td>\n",
       "      <td>fire-0</td>\n",
       "      <td>earthquake-0</td>\n",
       "      <td>cold-0</td>\n",
       "      <td>other_weather-0</td>\n",
       "      <td>direct_report-0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26248 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              0          1        2              3               4   \\\n",
       "0      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "1      related-1  request-0  offer-0  aid_related-1  medical_help-0   \n",
       "2      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "3      related-1  request-1  offer-0  aid_related-1  medical_help-0   \n",
       "4      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "...          ...        ...      ...            ...             ...   \n",
       "26243  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "26244  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "26245  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "26246  related-1  request-0  offer-0  aid_related-1  medical_help-0   \n",
       "26247  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n",
       "\n",
       "                       5                    6           7           8   \\\n",
       "0      medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "1      medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "2      medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "3      medical_products-1  search_and_rescue-0  security-0  military-0   \n",
       "4      medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "...                   ...                  ...         ...         ...   \n",
       "26243  medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "26244  medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "26245  medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "26246  medical_products-0  search_and_rescue-0  security-0  military-1   \n",
       "26247  medical_products-0  search_and_rescue-0  security-0  military-0   \n",
       "\n",
       "                  9   ...             26                      27  \\\n",
       "0      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "1      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "2      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "3      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "4      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "...              ...  ...            ...                     ...   \n",
       "26243  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "26244  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "26245  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "26246  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "26247  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n",
       "\n",
       "                      28        29       30      31            32      33  \\\n",
       "0      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "1      weather_related-1  floods-0  storm-1  fire-0  earthquake-0  cold-0   \n",
       "2      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "3      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "4      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "...                  ...       ...      ...     ...           ...     ...   \n",
       "26243  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "26244  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "26245  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "26246  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "26247  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n",
       "\n",
       "                    34               35  \n",
       "0      other_weather-0  direct_report-0  \n",
       "1      other_weather-0  direct_report-0  \n",
       "2      other_weather-0  direct_report-0  \n",
       "3      other_weather-0  direct_report-0  \n",
       "4      other_weather-0  direct_report-0  \n",
       "...                ...              ...  \n",
       "26243  other_weather-0  direct_report-0  \n",
       "26244  other_weather-0  direct_report-0  \n",
       "26245  other_weather-0  direct_report-0  \n",
       "26246  other_weather-0  direct_report-0  \n",
       "26247  other_weather-0  direct_report-0  \n",
       "\n",
       "[26248 rows x 36 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_clean.categories.str.split(';', expand = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating dataframe\n",
    "data = categories_clean.categories.str.split(';', expand = True)\n",
    "\n",
    "# Confirm 36 columns created\n",
    "assert data.shape[1] == 36, \"data columns: {}\".format(data.shape[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                    related\n",
       "1                    request\n",
       "2                      offer\n",
       "3                aid_related\n",
       "4               medical_help\n",
       "5           medical_products\n",
       "6          search_and_rescue\n",
       "7                   security\n",
       "8                   military\n",
       "9                child_alone\n",
       "10                     water\n",
       "11                      food\n",
       "12                   shelter\n",
       "13                  clothing\n",
       "14                     money\n",
       "15            missing_people\n",
       "16                  refugees\n",
       "17                     death\n",
       "18                 other_aid\n",
       "19    infrastructure_related\n",
       "20                 transport\n",
       "21                 buildings\n",
       "22               electricity\n",
       "23                     tools\n",
       "24                 hospitals\n",
       "25                     shops\n",
       "26               aid_centers\n",
       "27      other_infrastructure\n",
       "28           weather_related\n",
       "29                    floods\n",
       "30                     storm\n",
       "31                      fire\n",
       "32                earthquake\n",
       "33                      cold\n",
       "34             other_weather\n",
       "35             direct_report\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[0].str[:-2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['related', 'request', 'offer', 'aid_related', 'medical_help',\n",
       "       'medical_products', 'search_and_rescue', 'security', 'military',\n",
       "       'child_alone', 'water', 'food', 'shelter', 'clothing', 'money',\n",
       "       'missing_people', 'refugees', 'death', 'other_aid',\n",
       "       'infrastructure_related', 'transport', 'buildings', 'electricity',\n",
       "       'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure',\n",
       "       'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold',\n",
       "       'other_weather', 'direct_report'],\n",
       "      dtype='object', name=0)"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get headings\n",
    "col_names = data.iloc[0].str[:-2]\n",
    "data.columns = col_names\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert columns to int\n",
    "for col in col_names:\n",
    "    data[col] = data[col].str[-1].astype(int)\n",
    "\n",
    "# Confirm 36 int columns\n",
    "assert (data.dtypes == int).sum() == 36, df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>related</th>\n",
       "      <th>request</th>\n",
       "      <th>offer</th>\n",
       "      <th>aid_related</th>\n",
       "      <th>medical_help</th>\n",
       "      <th>medical_products</th>\n",
       "      <th>search_and_rescue</th>\n",
       "      <th>security</th>\n",
       "      <th>military</th>\n",
       "      <th>child_alone</th>\n",
       "      <th>...</th>\n",
       "      <th>aid_centers</th>\n",
       "      <th>other_infrastructure</th>\n",
       "      <th>weather_related</th>\n",
       "      <th>floods</th>\n",
       "      <th>storm</th>\n",
       "      <th>fire</th>\n",
       "      <th>earthquake</th>\n",
       "      <th>cold</th>\n",
       "      <th>other_weather</th>\n",
       "      <th>direct_report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>26243</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26244</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26245</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26246</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26247</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26248 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0      related  request  offer  aid_related  medical_help  medical_products  \\\n",
       "0            1        0      0            0             0                 0   \n",
       "1            1        0      0            1             0                 0   \n",
       "2            1        0      0            0             0                 0   \n",
       "3            1        1      0            1             0                 1   \n",
       "4            1        0      0            0             0                 0   \n",
       "...        ...      ...    ...          ...           ...               ...   \n",
       "26243        0        0      0            0             0                 0   \n",
       "26244        0        0      0            0             0                 0   \n",
       "26245        1        0      0            0             0                 0   \n",
       "26246        1        0      0            1             0                 0   \n",
       "26247        1        0      0            0             0                 0   \n",
       "\n",
       "0      search_and_rescue  security  military  child_alone  ...  aid_centers  \\\n",
       "0                      0         0         0            0  ...            0   \n",
       "1                      0         0         0            0  ...            0   \n",
       "2                      0         0         0            0  ...            0   \n",
       "3                      0         0         0            0  ...            0   \n",
       "4                      0         0         0            0  ...            0   \n",
       "...                  ...       ...       ...          ...  ...          ...   \n",
       "26243                  0         0         0            0  ...            0   \n",
       "26244                  0         0         0            0  ...            0   \n",
       "26245                  0         0         0            0  ...            0   \n",
       "26246                  0         0         1            0  ...            0   \n",
       "26247                  0         0         0            0  ...            0   \n",
       "\n",
       "0      other_infrastructure  weather_related  floods  storm  fire  earthquake  \\\n",
       "0                         0                0       0      0     0           0   \n",
       "1                         0                1       0      1     0           0   \n",
       "2                         0                0       0      0     0           0   \n",
       "3                         0                0       0      0     0           0   \n",
       "4                         0                0       0      0     0           0   \n",
       "...                     ...              ...     ...    ...   ...         ...   \n",
       "26243                     0                0       0      0     0           0   \n",
       "26244                     0                0       0      0     0           0   \n",
       "26245                     0                0       0      0     0           0   \n",
       "26246                     0                0       0      0     0           0   \n",
       "26247                     0                0       0      0     0           0   \n",
       "\n",
       "0      cold  other_weather  direct_report  \n",
       "0         0              0              0  \n",
       "1         0              0              0  \n",
       "2         0              0              0  \n",
       "3         0              0              0  \n",
       "4         0              0              0  \n",
       "...     ...            ...            ...  \n",
       "26243     0              0              0  \n",
       "26244     0              0              0  \n",
       "26245     0              0              0  \n",
       "26246     0              0              0  \n",
       "26247     0              0              0  \n",
       "\n",
       "[26248 rows x 36 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(83434, 26248)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sum(axis = 1).sum(), data.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "83434"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sum(axis = 1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['related', 'request', 'offer', 'aid_related', 'medical_help',\n",
       "       'medical_products', 'search_and_rescue', 'security', 'military',\n",
       "       'child_alone', 'water', 'food', 'shelter', 'clothing', 'money',\n",
       "       'missing_people', 'refugees', 'death', 'other_aid',\n",
       "       'infrastructure_related', 'transport', 'buildings', 'electricity',\n",
       "       'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure',\n",
       "       'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold',\n",
       "       'other_weather', 'direct_report'],\n",
       "      dtype='object', name=0)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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>related</th>\n",
       "      <th>request</th>\n",
       "      <th>offer</th>\n",
       "      <th>aid_related</th>\n",
       "      <th>medical_help</th>\n",
       "      <th>medical_products</th>\n",
       "      <th>search_and_rescue</th>\n",
       "      <th>security</th>\n",
       "      <th>military</th>\n",
       "      <th>child_alone</th>\n",
       "      <th>...</th>\n",
       "      <th>aid_centers</th>\n",
       "      <th>other_infrastructure</th>\n",
       "      <th>weather_related</th>\n",
       "      <th>floods</th>\n",
       "      <th>storm</th>\n",
       "      <th>fire</th>\n",
       "      <th>earthquake</th>\n",
       "      <th>cold</th>\n",
       "      <th>other_weather</th>\n",
       "      <th>direct_report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>26239</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26241</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26243</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26244</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26246</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21125 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0      related  request  offer  aid_related  medical_help  medical_products  \\\n",
       "1            1        0      0            1             0                 0   \n",
       "3            1        1      0            1             0                 1   \n",
       "5            0        0      0            0             0                 0   \n",
       "6            1        0      0            0             0                 0   \n",
       "7            1        1      0            1             0                 0   \n",
       "...        ...      ...    ...          ...           ...               ...   \n",
       "26239        1        1      0            0             0                 0   \n",
       "26241        0        0      0            0             0                 0   \n",
       "26243        0        0      0            0             0                 0   \n",
       "26244        0        0      0            0             0                 0   \n",
       "26246        1        0      0            1             0                 0   \n",
       "\n",
       "0      search_and_rescue  security  military  child_alone  ...  aid_centers  \\\n",
       "1                      0         0         0            0  ...            0   \n",
       "3                      0         0         0            0  ...            0   \n",
       "5                      0         0         0            0  ...            0   \n",
       "6                      0         0         0            0  ...            0   \n",
       "7                      0         0         0            0  ...            0   \n",
       "...                  ...       ...       ...          ...  ...          ...   \n",
       "26239                  0         0         0            0  ...            0   \n",
       "26241                  0         0         0            0  ...            0   \n",
       "26243                  0         0         0            0  ...            0   \n",
       "26244                  0         0         0            0  ...            0   \n",
       "26246                  0         0         1            0  ...            0   \n",
       "\n",
       "0      other_infrastructure  weather_related  floods  storm  fire  earthquake  \\\n",
       "1                         0                1       0      1     0           0   \n",
       "3                         0                0       0      0     0           0   \n",
       "5                         0                0       0      0     0           0   \n",
       "6                         0                1       0      1     0           0   \n",
       "7                         0                0       0      0     0           0   \n",
       "...                     ...              ...     ...    ...   ...         ...   \n",
       "26239                     0                0       0      0     0           0   \n",
       "26241                     0                0       0      0     0           0   \n",
       "26243                     0                0       0      0     0           0   \n",
       "26244                     0                0       0      0     0           0   \n",
       "26246                     0                0       0      0     0           0   \n",
       "\n",
       "0      cold  other_weather  direct_report  \n",
       "1         0              0              0  \n",
       "3         0              0              0  \n",
       "5         0              0              0  \n",
       "6         0              0              0  \n",
       "7         0              0              1  \n",
       "...     ...            ...            ...  \n",
       "26239     0              0              1  \n",
       "26241     0              0              0  \n",
       "26243     0              0              0  \n",
       "26244     0              0              0  \n",
       "26246     0              0              0  \n",
       "\n",
       "[21125 rows x 36 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data.sum(axis = 1) != 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Replace `categories` column with data**\n",
    "- Use `pd.concat([categories_clean, df], axis=1)` & `.drop('categories', axis=1)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26248, 2)"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_clean.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26248, 36)"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>id</th>\n",
       "      <th>related</th>\n",
       "      <th>request</th>\n",
       "      <th>offer</th>\n",
       "      <th>aid_related</th>\n",
       "      <th>medical_help</th>\n",
       "      <th>medical_products</th>\n",
       "      <th>search_and_rescue</th>\n",
       "      <th>security</th>\n",
       "      <th>military</th>\n",
       "      <th>...</th>\n",
       "      <th>aid_centers</th>\n",
       "      <th>other_infrastructure</th>\n",
       "      <th>weather_related</th>\n",
       "      <th>floods</th>\n",
       "      <th>storm</th>\n",
       "      <th>fire</th>\n",
       "      <th>earthquake</th>\n",
       "      <th>cold</th>\n",
       "      <th>other_weather</th>\n",
       "      <th>direct_report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>26243</th>\n",
       "      <td>30261</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26244</th>\n",
       "      <td>30262</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26245</th>\n",
       "      <td>30263</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26246</th>\n",
       "      <td>30264</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26247</th>\n",
       "      <td>30265</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26248 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  related  request  offer  aid_related  medical_help  \\\n",
       "0          2        1        0      0            0             0   \n",
       "1          7        1        0      0            1             0   \n",
       "2          8        1        0      0            0             0   \n",
       "3          9        1        1      0            1             0   \n",
       "4         12        1        0      0            0             0   \n",
       "...      ...      ...      ...    ...          ...           ...   \n",
       "26243  30261        0        0      0            0             0   \n",
       "26244  30262        0        0      0            0             0   \n",
       "26245  30263        1        0      0            0             0   \n",
       "26246  30264        1        0      0            1             0   \n",
       "26247  30265        1        0      0            0             0   \n",
       "\n",
       "       medical_products  search_and_rescue  security  military  ...  \\\n",
       "0                     0                  0         0         0  ...   \n",
       "1                     0                  0         0         0  ...   \n",
       "2                     0                  0         0         0  ...   \n",
       "3                     1                  0         0         0  ...   \n",
       "4                     0                  0         0         0  ...   \n",
       "...                 ...                ...       ...       ...  ...   \n",
       "26243                 0                  0         0         0  ...   \n",
       "26244                 0                  0         0         0  ...   \n",
       "26245                 0                  0         0         0  ...   \n",
       "26246                 0                  0         0         1  ...   \n",
       "26247                 0                  0         0         0  ...   \n",
       "\n",
       "       aid_centers  other_infrastructure  weather_related  floods  storm  \\\n",
       "0                0                     0                0       0      0   \n",
       "1                0                     0                1       0      1   \n",
       "2                0                     0                0       0      0   \n",
       "3                0                     0                0       0      0   \n",
       "4                0                     0                0       0      0   \n",
       "...            ...                   ...              ...     ...    ...   \n",
       "26243            0                     0                0       0      0   \n",
       "26244            0                     0                0       0      0   \n",
       "26245            0                     0                0       0      0   \n",
       "26246            0                     0                0       0      0   \n",
       "26247            0                     0                0       0      0   \n",
       "\n",
       "       fire  earthquake  cold  other_weather  direct_report  \n",
       "0         0           0     0              0              0  \n",
       "1         0           0     0              0              0  \n",
       "2         0           0     0              0              0  \n",
       "3         0           0     0              0              0  \n",
       "4         0           0     0              0              0  \n",
       "...     ...         ...   ...            ...            ...  \n",
       "26243     0           0     0              0              0  \n",
       "26244     0           0     0              0              0  \n",
       "26245     0           0     0              0              0  \n",
       "26246     0           0     0              0              0  \n",
       "26247     0           0     0              0              0  \n",
       "\n",
       "[26248 rows x 37 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([categories_clean.drop('categories', axis = 1), data], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories_clean = pd.concat([categories_clean.drop('categories', axis = 1), data], axis = 1)\n",
    "\n",
    "# Confirm total of 37 columns\n",
    "assert categories_clean.shape[1] == 37, \"Categories shape: {}\".format(categories_clean.shape[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create merged dataset**\n",
    "- Use `.merge(on='id')`\n",
    "\n",
    "The `original` column and `id` column were retained for storage in the database even if these will not be used in the machine learning process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "ename": "AssertionError",
     "evalue": "Merged df rows: 26386, Messages df rows: 26248",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAssertionError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-104-ae19153df96d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      6\u001b[0m                                                          messages_clean.shape[1] + categories_clean.shape[1] - 1)\n\u001b[0;32m      7\u001b[0m \u001b[1;32massert\u001b[0m \u001b[0mdata_merged\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mmessages_clean\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"Merged df rows: {}, Messages df rows: {}\"\u001b[0m\u001b[0;31m\\\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m     \u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata_merged\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmessages_clean\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mAssertionError\u001b[0m: Merged df rows: 26386, Messages df rows: 26248"
     ]
    }
   ],
   "source": [
    "data_merged = messages_clean.merge(categories_clean, on = 'id')\n",
    "\n",
    "# Confirm correct new shape\n",
    "assert data_merged.shape[1] == messages_clean.shape[1] + categories_clean.shape[1] - 1, \\\n",
    "    \"Merged df columns: {}, Combined columns: {}\".format(data_merged.shape[1], \n",
    "                                                         messages_clean.shape[1] + categories_clean.shape[1] - 1)\n",
    "assert data_merged.shape[0] == messages_clean.shape[0], \"Merged df rows: {}, Messages df rows: {}\"\\\n",
    "    .format(data_merged.shape[0], messages_clean.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**This might have created duplicates by joining them by id**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(170, 40)"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged[data_merged.duplicated()].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 170 duplicated rows that need to be dropped.\n",
    "\n",
    "Some values are not even 0 or 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Drop duplicated rows.**\n",
    "- Use `.drop_duplicates(inplace=True)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_merged.drop_duplicates(inplace = True)\n",
    "\n",
    "# Confirm no duplicates\n",
    "assert data_merged.duplicated().sum() == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Explore categories values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "related\n",
      "[1 0 2]\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for col in data_merged.columns[4:]:\n",
    "    if (data_merged[col] > 1).sum() > 0:\n",
    "        print(col)\n",
    "        print(data_merged[col].unique())\n",
    "        print('\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's just the `related` column that has these three different values. For the purposes of simply predicting related or not it is preferrable to only have \"on/off\" conditions. As a result, any value of 2 should be converted to 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_merged.loc[(data_merged.related == 2), 'related'] = 1\n",
    "# Confirm only two values\n",
    "assert len(data_merged.related.unique()) == 2, data.related.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('disaster_messages.db')\n",
    "data_merged.to_sql('messages', con = conn, if_exists = 'replace', index = False)\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Shape of Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('data/disaster_messages.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = pd.read_sql('SELECT COUNT(*) FROM messages', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "26216"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows.iloc[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26180, 4)"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "messages.drop_duplicates().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26216, 2)"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.drop_duplicates().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "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>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Weather update - a cold front from Cuba that c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Is the Hurricane over or is it not over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Looking for someone but no name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>says: west side of Haiti, rest of the country ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             message\n",
       "0  Weather update - a cold front from Cuba that c...\n",
       "1            Is the Hurricane over or is it not over\n",
       "2                    Looking for someone but no name\n",
       "3  UN reports Leogane 80-90 destroyed. Only Hospi...\n",
       "4  says: west side of Haiti, rest of the country ..."
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT message FROM messages LIMIT 5;', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}