ETL Pipeline Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Pipeline Preparation\n",
    "Follow the instructions below to help you create your ETL pipeline.\n",
    "### 1. Import libraries and load datasets.\n",
    "- Import Python libraries\n",
    "- Load `messages.csv` into a dataframe and inspect the first few lines.\n",
    "- Load `categories.csv` into a dataframe and inspect the first few lines."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Weather update - a cold front from Cuba that c...</td>\n",
       "      <td>Un front froid se retrouve sur Cuba ce matin. ...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>Is the Hurricane over or is it not over</td>\n",
       "      <td>Cyclone nan fini osinon li pa fini</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>Looking for someone but no name</td>\n",
       "      <td>Patnm, di Maryani relem pou li banm nouvel li ...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>says: west side of Haiti, rest of the country ...</td>\n",
       "      <td>facade ouest d Haiti et le reste du pays aujou...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                            message  \\\n",
       "0   2  Weather update - a cold front from Cuba that c...   \n",
       "1   7            Is the Hurricane over or is it not over   \n",
       "2   8                    Looking for someone but no name   \n",
       "3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n",
       "4  12  says: west side of Haiti, rest of the country ...   \n",
       "\n",
       "                                            original   genre  \n",
       "0  Un front froid se retrouve sur Cuba ce matin. ...  direct  \n",
       "1                 Cyclone nan fini osinon li pa fini  direct  \n",
       "2  Patnm, di Maryani relem pou li banm nouvel li ...  direct  \n",
       "3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct  \n",
       "4  facade ouest d Haiti et le reste du pays aujou...  direct  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load messages dataset\n",
    "messages = pd.read_csv('./raw_data/messages.csv',sep=',',quotechar='\"')\n",
    "messages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['direct', 'social', 'news'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "messages.genre.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Messages notes\n",
    "ID will be used to merge messages with categories. Original is unnecessary: we'll be doing our analysis based on the English messages. Genre can be useful as an additional feature. We'll see when we train the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>0</th>\n",
       "      <td>2</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</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",
       "0   2  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "1   7  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "2   8  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "3   9  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "4  12  related-1;request-0;offer-0;aid_related-0;medi..."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load categories dataset\n",
    "categories = pd.read_csv('./raw_data/categories.csv',sep=',',quotechar='\"')\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categories notes\n",
    "ID will be used to merge messages with categories. The categories field will need to be split into multiple fields based on the separator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Merge datasets.\n",
    "- Merge the messages and categories datasets using the common id\n",
    "- Assign this combined dataset to `df`, which will be cleaned in the following steps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge notes\n",
    "Both datasets have an 'id' column. pd.merge() should work automatically."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking for duplicates should come before merges and joins!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in Messages is 26248\n",
      "Number of unique IDs in Messages is 26180\n",
      "Number of rows in Categories is 26248\n",
      "Number of unique IDs in Categories is 26180\n"
     ]
    }
   ],
   "source": [
    "print('Number of rows in Messages is {}'.format(len(messages)))\n",
    "print('Number of unique IDs in Messages is {}'.format(len(messages['id'].unique())))\n",
    "\n",
    "print('Number of rows in Categories is {}'.format(len(categories)))\n",
    "print('Number of unique IDs in Categories is {}'.format(len(categories['id'].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We have duplicates\n",
    "We don't know the nature of these duplicates. Are the rows completely the same? Maybe they have the same IDs but different data? It's unsafe nad outright bad practice to merge the datasets at this point. Let's try to clean the datasets up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "messages = messages.drop_duplicates()\n",
    "categories = categories.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After basic duplicates drop.\n",
      "Number of rows in Messages is 26180\n",
      "Number of unique IDs in Messages is 26180\n",
      "Number of rows in Categories is 26216\n",
      "Number of unique IDs in Categories is 26180\n"
     ]
    }
   ],
   "source": [
    "print('After basic duplicates drop.')\n",
    "print('Number of rows in Messages is {}'.format(len(messages)))\n",
    "print('Number of unique IDs in Messages is {}'.format(len(messages['id'].unique())))\n",
    "\n",
    "print('Number of rows in Categories is {}'.format(len(categories)))\n",
    "print('Number of unique IDs in Categories is {}'.format(len(categories['id'].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Messages are clean, Categories need more processing\n",
    "There are still duplicates in categories. We should get rid of them before merging.\n",
    "Let's take a look at the duplicate rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories.groupby?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>162</th>\n",
       "      <td>202</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>202</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>862</td>\n",
       "      <td>related-0;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>710</th>\n",
       "      <td>862</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1407</th>\n",
       "      <td>1652</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408</th>\n",
       "      <td>1652</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2824</th>\n",
       "      <td>3250</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2825</th>\n",
       "      <td>3250</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>3882</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>3882</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                         categories\n",
       "162    202  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "163    202  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "709    862  related-0;request-0;offer-0;aid_related-0;medi...\n",
       "710    862  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "1407  1652  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "1408  1652  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "2824  3250  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "2825  3250  related-1;request-1;offer-0;aid_related-0;medi...\n",
       "3374  3882  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "3373  3882  related-1;request-1;offer-0;aid_related-1;medi..."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts = categories.groupby(by=['id']).count()\n",
    "id_dupl = id_counts[id_counts.values > 1]\n",
    "categories[categories['id'].isin(id_dupl.index)].sort_values(by=['id']).iloc[:10,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>2824</th>\n",
       "      <td>3250</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2825</th>\n",
       "      <td>3250</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                         categories\n",
       "2824  3250  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "2825  3250  related-1;request-1;offer-0;aid_related-0;medi..."
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories[categories['id']==3250]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "36\n",
      "36\n"
     ]
    }
   ],
   "source": [
    "print(len(id_dupl))\n",
    "print(len(id_dupl[id_dupl.values==2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There are 36 different rows with the same id in categories\n",
    "We have 18 IDs that have 2 variants of category labelling each. This is not perfect, but the dataset has approximately 28000 observations, so it's not a big deal. Let's just keep an arbitrary variant and move on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = categories.drop_duplicates(subset=['id'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After final duplicates drop.\n",
      "Number of rows in Messages is 26180\n",
      "Number of unique IDs in Messages is 26180\n",
      "Number of rows in Categories is 26180\n",
      "Number of unique IDs in Categories is 26180\n"
     ]
    }
   ],
   "source": [
    "print('After final duplicates drop.')\n",
    "print('Number of rows in Messages is {}'.format(len(messages)))\n",
    "print('Number of unique IDs in Messages is {}'.format(len(messages['id'].unique())))\n",
    "\n",
    "print('Number of rows in Categories is {}'.format(len(categories)))\n",
    "print('Number of unique IDs in Categories is {}'.format(len(categories['id'].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we can merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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",
       "      <th>categories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Weather update - a cold front from Cuba that c...</td>\n",
       "      <td>Un front froid se retrouve sur Cuba ce matin. ...</td>\n",
       "      <td>direct</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>Is the Hurricane over or is it not over</td>\n",
       "      <td>Cyclone nan fini osinon li pa fini</td>\n",
       "      <td>direct</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>Looking for someone but no name</td>\n",
       "      <td>Patnm, di Maryani relem pou li banm nouvel li ...</td>\n",
       "      <td>direct</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>direct</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>says: west side of Haiti, rest of the country ...</td>\n",
       "      <td>facade ouest d Haiti et le reste du pays aujou...</td>\n",
       "      <td>direct</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                                            message  \\\n",
       "0   2  Weather update - a cold front from Cuba that c...   \n",
       "1   7            Is the Hurricane over or is it not over   \n",
       "2   8                    Looking for someone but no name   \n",
       "3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n",
       "4  12  says: west side of Haiti, rest of the country ...   \n",
       "\n",
       "                                            original   genre  \\\n",
       "0  Un front froid se retrouve sur Cuba ce matin. ...  direct   \n",
       "1                 Cyclone nan fini osinon li pa fini  direct   \n",
       "2  Patnm, di Maryani relem pou li banm nouvel li ...  direct   \n",
       "3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct   \n",
       "4  facade ouest d Haiti et le reste du pays aujou...  direct   \n",
       "\n",
       "                                          categories  \n",
       "0  related-1;request-0;offer-0;aid_related-0;medi...  \n",
       "1  related-1;request-0;offer-0;aid_related-1;medi...  \n",
       "2  related-1;request-0;offer-0;aid_related-0;medi...  \n",
       "3  related-1;request-1;offer-0;aid_related-1;medi...  \n",
       "4  related-1;request-0;offer-0;aid_related-0;medi...  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge datasets\n",
    "df = pd.merge(messages, categories)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of rows in the merged dataset is 26180\n"
     ]
    }
   ],
   "source": [
    "print('The number of rows in the merged dataset is {}'.format(len(df)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Split `categories` into separate category columns.\n",
    "- Split the values in the `categories` column on the `;` character so that each value becomes a separate column. You'll find [this method](https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.Series.str.split.html) very helpful! Make sure to set `expand=True`.\n",
    "- Use the first row of categories dataframe to create column names for the categories data.\n",
    "- Rename columns of `categories` with new column names."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notes on splitting into categories.\n",
    "The str.split() approach is lazy. We have no guarantee that the categories in the first row extend across all the 26180 rows of the dataset. We need to be sure, so let's test before we split."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get column names for testing\n",
    "test_cols = [x[:-2] for x in categories.iloc[0,1].split(';')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Build a testing function\n",
    "def test_categories(text, test_cols):\n",
    "    \"\"\"\n",
    "    Test if the encoded categories haven't changed\n",
    "    \n",
    "    Args:\n",
    "    text (str): string with categories and values encoded\n",
    "    test_cols (list): list of category names to check against\n",
    "    \n",
    "    Output:\n",
    "    (bool): True if the categories and their order haven't\n",
    "            changed, False otherwise\n",
    "    \"\"\"\n",
    "    t2 = test_cols\n",
    "    t1 = [x[:-2] for x in text.split(';')]\n",
    "    if t1==t2:\n",
    "        return True\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>0</th>\n",
       "      <td>2</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>related-1;request-1;offer-0;aid_related-1;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</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",
       "0   2  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "1   7  related-1;request-0;offer-0;aid_related-1;medi...\n",
       "2   8  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "3   9  related-1;request-1;offer-0;aid_related-1;medi...\n",
       "4  12  related-1;request-0;offer-0;aid_related-0;medi..."
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: categories, dtype: bool)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_test = categories['categories'].apply(lambda x: test_categories(x, test_cols))\n",
    "cat_test[~cat_test.values]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We've checked that the columns and their order are the same for every row\n",
    "Such tests should be done unless coding and running them is prhobitively expensive. This was simple to code and took almost no time to run.\n",
    "Now we are ready to do the split. We'll do it a bit differently from what's suggested in the Notebook for readability. The result is the same though."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['related', 'request', 'offer', 'aid_related', 'medical_help', 'medical_products', 'search_and_rescue', 'security', 'military', 'child_alone', 'water', 'food', 'shelter', 'clothing', 'money', 'missing_people', 'refugees', 'death', 'other_aid', 'infrastructure_related', 'transport', 'buildings', 'electricity', 'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure', 'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold', 'other_weather', 'direct_report']\n"
     ]
    }
   ],
   "source": [
    "#Get column names\n",
    "cols = categories.loc[0,'categories'].split(';')\n",
    "cols = [x[:-2] for x in cols]\n",
    "print(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get data\n",
    "def get_cat_values(text):\n",
    "    \"\"\"\n",
    "    This basic function takes an encoded category string\n",
    "    and returns a list of 0s and 1s indicating \n",
    "    categories. It could be written as a lambda,\n",
    "    but it would be less readable.\n",
    "    \n",
    "    Args:\n",
    "    text (str): text with values encoded in them\n",
    "    \n",
    "    Output:\n",
    "    val_list (list): a list of 0s and 1s flags\n",
    "    \"\"\"\n",
    "    \n",
    "    vals = [int(x[-1]) for x in text.split(';')]\n",
    "    return vals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26180, 36)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Turn a Series of str in to a Series of lists\n",
    "data = categories['categories'].apply(get_cat_values)\n",
    "#Turn the Series into a list of lists\n",
    "data=list(data)\n",
    "#Direct cast to np.array didn't work as expected (made an array of lists)\n",
    "data=np.array(data)\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Make the ID column\n",
    "ids = np.array(categories['id'])[np.newaxis].T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Add ID to the values and to the column names list\n",
    "data_full = np.hstack((data,ids))\n",
    "cols_full = cols + ['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>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",
       "      <th>id</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>2</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>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>7</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>8</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>9</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>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   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",
       "   search_and_rescue  security  military  child_alone  ...  \\\n",
       "0                  0         0         0            0  ...   \n",
       "1                  0         0         0            0  ...   \n",
       "2                  0         0         0            0  ...   \n",
       "3                  0         0         0            0  ...   \n",
       "4                  0         0         0            0  ...   \n",
       "\n",
       "   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",
       "   cold  other_weather  direct_report  id  \n",
       "0     0              0              0   2  \n",
       "1     0              0              0   7  \n",
       "2     0              0              0   8  \n",
       "3     0              0              0   9  \n",
       "4     0              0              0  12  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the columns of `categories`\n",
    "\n",
    "#We'll make a new categories DataFrame instead\n",
    "categories_new = pd.DataFrame(data=data_full,columns=cols_full)\n",
    "categories_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>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",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.0</td>\n",
       "      <td>...</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "      <td>26180.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.773568</td>\n",
       "      <td>0.170512</td>\n",
       "      <td>0.004507</td>\n",
       "      <td>0.414095</td>\n",
       "      <td>0.079488</td>\n",
       "      <td>0.050076</td>\n",
       "      <td>0.027655</td>\n",
       "      <td>0.017991</td>\n",
       "      <td>0.032811</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.043965</td>\n",
       "      <td>0.278304</td>\n",
       "      <td>0.082086</td>\n",
       "      <td>0.093201</td>\n",
       "      <td>0.010772</td>\n",
       "      <td>0.093659</td>\n",
       "      <td>0.020168</td>\n",
       "      <td>0.052559</td>\n",
       "      <td>0.193430</td>\n",
       "      <td>15227.563942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.435351</td>\n",
       "      <td>0.376089</td>\n",
       "      <td>0.066986</td>\n",
       "      <td>0.492574</td>\n",
       "      <td>0.270504</td>\n",
       "      <td>0.218107</td>\n",
       "      <td>0.163985</td>\n",
       "      <td>0.132920</td>\n",
       "      <td>0.178146</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.205021</td>\n",
       "      <td>0.448172</td>\n",
       "      <td>0.274500</td>\n",
       "      <td>0.290719</td>\n",
       "      <td>0.103228</td>\n",
       "      <td>0.291360</td>\n",
       "      <td>0.140578</td>\n",
       "      <td>0.223156</td>\n",
       "      <td>0.394995</td>\n",
       "      <td>8827.269301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7449.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15665.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22927.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>30265.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            related       request         offer   aid_related  medical_help  \\\n",
       "count  26180.000000  26180.000000  26180.000000  26180.000000  26180.000000   \n",
       "mean       0.773568      0.170512      0.004507      0.414095      0.079488   \n",
       "std        0.435351      0.376089      0.066986      0.492574      0.270504   \n",
       "min        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%        1.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "50%        1.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "75%        1.000000      0.000000      0.000000      1.000000      0.000000   \n",
       "max        2.000000      1.000000      1.000000      1.000000      1.000000   \n",
       "\n",
       "       medical_products  search_and_rescue      security      military  \\\n",
       "count      26180.000000       26180.000000  26180.000000  26180.000000   \n",
       "mean           0.050076           0.027655      0.017991      0.032811   \n",
       "std            0.218107           0.163985      0.132920      0.178146   \n",
       "min            0.000000           0.000000      0.000000      0.000000   \n",
       "25%            0.000000           0.000000      0.000000      0.000000   \n",
       "50%            0.000000           0.000000      0.000000      0.000000   \n",
       "75%            0.000000           0.000000      0.000000      0.000000   \n",
       "max            1.000000           1.000000      1.000000      1.000000   \n",
       "\n",
       "       child_alone  ...  other_infrastructure  weather_related        floods  \\\n",
       "count      26180.0  ...          26180.000000     26180.000000  26180.000000   \n",
       "mean           0.0  ...              0.043965         0.278304      0.082086   \n",
       "std            0.0  ...              0.205021         0.448172      0.274500   \n",
       "min            0.0  ...              0.000000         0.000000      0.000000   \n",
       "25%            0.0  ...              0.000000         0.000000      0.000000   \n",
       "50%            0.0  ...              0.000000         0.000000      0.000000   \n",
       "75%            0.0  ...              0.000000         1.000000      0.000000   \n",
       "max            0.0  ...              1.000000         1.000000      1.000000   \n",
       "\n",
       "              storm          fire    earthquake          cold  other_weather  \\\n",
       "count  26180.000000  26180.000000  26180.000000  26180.000000   26180.000000   \n",
       "mean       0.093201      0.010772      0.093659      0.020168       0.052559   \n",
       "std        0.290719      0.103228      0.291360      0.140578       0.223156   \n",
       "min        0.000000      0.000000      0.000000      0.000000       0.000000   \n",
       "25%        0.000000      0.000000      0.000000      0.000000       0.000000   \n",
       "50%        0.000000      0.000000      0.000000      0.000000       0.000000   \n",
       "75%        0.000000      0.000000      0.000000      0.000000       0.000000   \n",
       "max        1.000000      1.000000      1.000000      1.000000       1.000000   \n",
       "\n",
       "       direct_report            id  \n",
       "count   26180.000000  26180.000000  \n",
       "mean        0.193430  15227.563942  \n",
       "std         0.394995   8827.269301  \n",
       "min         0.000000      2.000000  \n",
       "25%         0.000000   7449.750000  \n",
       "50%         0.000000  15665.500000  \n",
       "75%         0.000000  22927.250000  \n",
       "max         1.000000  30265.000000  \n",
       "\n",
       "[8 rows x 37 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_new.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Convert category values to just numbers 0 or 1.\n",
    "- Iterate through the category columns in df to keep only the last character of each string (the 1 or 0). For example, `related-0` becomes `0`, `related-1` becomes `1`. Convert the string to a numeric value.\n",
    "- You can perform [normal string actions on Pandas Series](https://pandas.pydata.org/pandas-docs/stable/text.html#indexing-with-str), like indexing, by including `.str` after the Series. You may need to first convert the Series to be of type string, which you can do with `astype(str)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We took care of this during step 3, because there was no need to\n",
    "# make a weird DataFrame full of text, when we easily did this in one step.\n",
    "\n",
    "# for column in categories:\n",
    "#     # set each value to be the last character of the string\n",
    "#     categories[column] = \n",
    "#     \n",
    "#     # convert column from string to numeric\n",
    "#     categories[column] = \n",
    "# categories.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Replace `categories` column in `df` with new category columns.\n",
    "- Drop the categories column from the df dataframe since it is no longer needed.\n",
    "- Concatenate df and categories data frames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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",
       "      <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>...</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>Weather update - a cold front from Cuba that c...</td>\n",
       "      <td>Un front froid se retrouve sur Cuba ce matin. ...</td>\n",
       "      <td>direct</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>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>Is the Hurricane over or is it not over</td>\n",
       "      <td>Cyclone nan fini osinon li pa fini</td>\n",
       "      <td>direct</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>...</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>Looking for someone but no name</td>\n",
       "      <td>Patnm, di Maryani relem pou li banm nouvel li ...</td>\n",
       "      <td>direct</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>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>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>UN reports Leogane 80-90 destroyed. Only Hospi...</td>\n",
       "      <td>direct</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>...</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>says: west side of Haiti, rest of the country ...</td>\n",
       "      <td>facade ouest d Haiti et le reste du pays aujou...</td>\n",
       "      <td>direct</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>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>5 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                            message  \\\n",
       "0   2  Weather update - a cold front from Cuba that c...   \n",
       "1   7            Is the Hurricane over or is it not over   \n",
       "2   8                    Looking for someone but no name   \n",
       "3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n",
       "4  12  says: west side of Haiti, rest of the country ...   \n",
       "\n",
       "                                            original   genre  related  \\\n",
       "0  Un front froid se retrouve sur Cuba ce matin. ...  direct        1   \n",
       "1                 Cyclone nan fini osinon li pa fini  direct        1   \n",
       "2  Patnm, di Maryani relem pou li banm nouvel li ...  direct        1   \n",
       "3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct        1   \n",
       "4  facade ouest d Haiti et le reste du pays aujou...  direct        1   \n",
       "\n",
       "   request  offer  aid_related  medical_help  medical_products  ...  \\\n",
       "0        0      0            0             0                 0  ...   \n",
       "1        0      0            1             0                 0  ...   \n",
       "2        0      0            0             0                 0  ...   \n",
       "3        1      0            1             0                 1  ...   \n",
       "4        0      0            0             0                 0  ...   \n",
       "\n",
       "   aid_centers  other_infrastructure  weather_related  floods  storm  fire  \\\n",
       "0            0                     0                0       0      0     0   \n",
       "1            0                     0                1       0      1     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",
       "   earthquake  cold  other_weather  direct_report  \n",
       "0           0     0              0              0  \n",
       "1           0     0              0              0  \n",
       "2           0     0              0              0  \n",
       "3           0     0              0              0  \n",
       "4           0     0              0              0  \n",
       "\n",
       "[5 rows x 40 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This is a weird way to do this. Let's just re-merge with the new DataFrame we made.\n",
    "df = pd.merge(messages, categories_new)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Remove duplicates.\n",
    "- Check how many duplicates are in this dataset.\n",
    "- Drop the duplicates.\n",
    "- Confirm duplicates were removed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We already did this. Duplicates should be cleaned before any joins or merges.\n",
    "It's dangerous to merge or join before removing duplicates, because duplicates can and will multiply (the program infers a many-to-many operation when running into duplicates). Things can get out of hand fast.\n",
    "This is why I've cleaned everything already."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Already done during step 2\n",
    "# check number of duplicates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Already done during step 2\n",
    "# drop duplicates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Already done during step 2\n",
    "# check number of duplicates\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Save the clean dataset into an sqlite database.\n",
    "You can do this with pandas [`to_sql` method](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html) combined with the SQLAlchemy library. Remember to import SQLAlchemy's `create_engine` in the first cell of this notebook to use it below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///database/InsertDatabaseName.db')\n",
    "df.to_sql('categorized_messages', engine, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Use this notebook to complete `etl_pipeline.py`\n",
    "Use the template file attached in the Resources folder to write a script that runs the steps above to create a database based on new datasets specified by the user. Alternatively, you can complete `etl_pipeline.py` in the classroom on the `Project Workspace IDE` coming later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "def load_data(messages_filepath, categories_filepath):\n",
    "    \"\"\"\n",
    "    Load the messages and categories files\n",
    "    returns DataFrames\n",
    "    \n",
    "    CAUTION: Data should really be cleaned before merging.\n",
    "    Merging dirty data and then cleaning, in my experience,\n",
    "    can lead to unnecessary problems.\n",
    "    \n",
    "    This is why I don't merge here and do it in clean_data instead\n",
    "    \n",
    "    Args:\n",
    "    messages_filepath(str): path to the messages file\n",
    "    categories_filepath(str): path to the categories file\n",
    "    \n",
    "    Out:\n",
    "    messages (Series) - a Series with the messages\n",
    "    categories (Series) - a Series with the categories\n",
    "    \"\"\"\n",
    "    messages = pd.read_csv(messages_filepath,sep=',',quotechar='\"')\n",
    "    categories = pd.read_csv(categories_filepath,sep=',',quotechar='\"')\n",
    "    return messages, categories\n",
    "\n",
    "\n",
    "def clean_data(messages, categories):\n",
    "    \"\"\"\n",
    "    Cleans the messages and categories\n",
    "    \n",
    "    Args:\n",
    "    messages (Series) - a Series with the messages\n",
    "    categories (Series) - a Series with the categories\n",
    "    \n",
    "    Out:\n",
    "    messages (Series) - a Series with the messages\n",
    "    categories (Series) - a Series with the categories\n",
    "    \"\"\"\n",
    "    messages = messages.drop_duplicates()\n",
    "    categories = categories.drop_duplicates()\n",
    "    \n",
    "    categories = categories.drop_duplicates(subset=['id'],keep='first')\n",
    "    \n",
    "    return messages, categories\n",
    "\n",
    "\n",
    "def preprocess_data(messages, categories):\n",
    "    \"\"\"\n",
    "    Does the rest of preprocessing: splits the categories into\n",
    "    separate classes and merges\n",
    "    \n",
    "    \n",
    "    Args:\n",
    "    messages (Series) - a Series with the messages\n",
    "    categories (Series) - a Series with the categories\n",
    "    \n",
    "    Out:\n",
    "    df (DataFrame): cleaned, preprocessed, and merged DataFrame\n",
    "    \n",
    "    \"\"\"\n",
    "    def get_cat_values(text):\n",
    "        \"\"\"\n",
    "        This basic function takes an encoded category string\n",
    "        and returns a list of 0s and 1s indicating \n",
    "        categories. It could be written as a lambda,\n",
    "        but it would be less readable.\n",
    "\n",
    "        Args:\n",
    "        text (str): text with values encoded in them\n",
    "\n",
    "        Output:\n",
    "        val_list (list): a list of 0s and 1s flags\n",
    "        \"\"\"\n",
    "\n",
    "        vals = [int(x[-1]) for x in text.split(';')]\n",
    "        return vals\n",
    "    \n",
    "    \n",
    "    # Get column names\n",
    "    cols = categories.loc[0,'categories'].split(';')\n",
    "    cols = [x[:-2] for x in cols]\n",
    "    \n",
    "    # Get data\n",
    "    \n",
    "    #Turn a Series of str in to a Series of lists\n",
    "    data = categories['categories'].apply(get_cat_values)\n",
    "    #Turn the Series into a list of lists\n",
    "    data=list(data)\n",
    "    #Direct cast to np.array didn't work as expected (made an array of lists)\n",
    "    data=np.array(data)\n",
    "    #Make the ID column\n",
    "    ids = np.array(categories['id'])[np.newaxis].T\n",
    "    #Add ID to the values and to the column names list\n",
    "    data_full = np.hstack((data,ids))\n",
    "    cols_full = cols + ['id']\n",
    "    #We'll make a new categories DataFrame instead\n",
    "    categories_new = pd.DataFrame(data=data_full,columns=cols_full)\n",
    "    \n",
    "    df = pd.merge(messages, categories_new)\n",
    "    \n",
    "    return df\n",
    "\n",
    "def save_data(df, database_filename):\n",
    "    \"\"\"\n",
    "    Saves the df DataFrame in a database\n",
    "    \"\"\"\n",
    "    engine = create_engine('sqlite:///' + database_filename)\n",
    "    df.to_sql('categorized_messages', engine, index=False)\n",
    "\n",
    "\n",
    "    \n",
    "def main():\n",
    "    if len(sys.argv) == 4:\n",
    "\n",
    "        messages_filepath, categories_filepath, database_filepath = sys.argv[1:]\n",
    "\n",
    "        print('Loading data...\\n    MESSAGES: {}\\n    CATEGORIES: {}'\n",
    "              .format(messages_filepath, categories_filepath))\n",
    "        messages, categories = load_data(messages_filepath, categories_filepath)\n",
    "\n",
    "        print('Cleaning data...')\n",
    "        messages, categories = clean_data(messages, categories)\n",
    "        \n",
    "        print('Finishing preprocessing...')\n",
    "        df = preprocess_data(messages, categories)\n",
    "        \n",
    "        print('Saving data...\\n    DATABASE: {}'.format(database_filepath))\n",
    "        save_data(df, database_filepath)\n",
    "        \n",
    "        print('Cleaned data saved to database!')\n",
    "    \n",
    "    else:\n",
    "        print('Please provide the filepaths of the messages and categories '\\\n",
    "              'datasets as the first and second argument respectively, as '\\\n",
    "              'well as the filepath of the database to save the cleaned data '\\\n",
    "              'to as the third argument. \\n\\nExample: python process_data.py '\\\n",
    "              'disaster_messages.csv disaster_categories.csv '\\\n",
    "              'DisasterResponse.db')\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}