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",
    "\n",
    "# Setting up connection to sqlite\n",
    "import sqlite3"
   ]
  },
  {
   "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('./data/messages.csv')\n",
    "messages.head()"
   ]
  },
  {
   "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('./data/categories.csv')\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 Remove the duplicate rows\n",
    "First inspect how the duplicate rows look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>162</th>\n",
       "      <td>202</td>\n",
       "      <td>?? port au prince ?? and food. they need gover...</td>\n",
       "      <td>p bay pap la syen ak manje. Yo bezwen ed gouve...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>202</td>\n",
       "      <td>?? port au prince ?? and food. they need gover...</td>\n",
       "      <td>p bay pap la syen ak manje. Yo bezwen ed gouve...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>804</td>\n",
       "      <td>elle est vraiment malade et a besoin d'aide. u...</td>\n",
       "      <td>she is really sick she need your help. please ...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>804</td>\n",
       "      <td>elle est vraiment malade et a besoin d'aide. u...</td>\n",
       "      <td>she is really sick she need your help. please ...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>862</td>\n",
       "      <td>What is the address of the radio station? I as...</td>\n",
       "      <td>Ki adres radyo a? Paske m bezwen al depoze dos...</td>\n",
       "      <td>direct</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                                            message  \\\n",
       "162  202  ?? port au prince ?? and food. they need gover...   \n",
       "163  202  ?? port au prince ?? and food. they need gover...   \n",
       "655  804  elle est vraiment malade et a besoin d'aide. u...   \n",
       "656  804  elle est vraiment malade et a besoin d'aide. u...   \n",
       "709  862  What is the address of the radio station? I as...   \n",
       "\n",
       "                                              original   genre  \n",
       "162  p bay pap la syen ak manje. Yo bezwen ed gouve...  direct  \n",
       "163  p bay pap la syen ak manje. Yo bezwen ed gouve...  direct  \n",
       "655  she is really sick she need your help. please ...  direct  \n",
       "656  she is really sick she need your help. please ...  direct  \n",
       "709  Ki adres radyo a? Paske m bezwen al depoze dos...  direct  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Duplicate rows in messages.csv\n",
    "messages[messages.duplicated(keep=False)].head()"
   ]
  },
  {
   "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>655</th>\n",
       "      <td>804</td>\n",
       "      <td>related-2;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>804</td>\n",
       "      <td>related-2;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2130</th>\n",
       "      <td>2446</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2131</th>\n",
       "      <td>2446</td>\n",
       "      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2792</th>\n",
       "      <td>3217</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",
       "655    804  related-2;request-0;offer-0;aid_related-0;medi...\n",
       "656    804  related-2;request-0;offer-0;aid_related-0;medi...\n",
       "2130  2446  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "2131  2446  related-1;request-0;offer-0;aid_related-0;medi...\n",
       "2792  3217  related-1;request-1;offer-0;aid_related-1;medi..."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dupliacte rows in categories.csv\n",
    "categories[categories.duplicated(keep=False)].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like both datasets contains exact duplicates, including duplicate id columns within the same dataset. These need to be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove exact duplicate rows from the datasets\n",
    "messages.drop_duplicates(inplace=True)\n",
    "categories.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(26180, 4)\n",
      "(26216, 2)\n",
      "[]\n",
      "26180\n",
      "26180\n"
     ]
    }
   ],
   "source": [
    "# Looking at the shape they are still not exactly the same\n",
    "print(messages.shape)\n",
    "print(categories.shape)\n",
    "\n",
    "# After investigating this issue it turns out that the categories dataframe still contains duplicate id columns\n",
    "print(np.setdiff1d(list(messages['id']), list(categories['id'])))\n",
    "\n",
    "# Cause both columns have 26180 unique id values\n",
    "print(messages['id'].nunique())\n",
    "print(categories['id'].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normally I would like to talk to the people who delivered this dataset and find out what are the correct rows. Since this is not possible I decided that the last row within this dataset has the highest change of being the correct row. So only kept the highest row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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",
       "  </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..."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories[categories['id'].duplicated(keep=False)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories.drop_duplicates(subset='id', keep=\"last\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(26180, 4)\n",
      "(26180, 2)\n"
     ]
    }
   ],
   "source": [
    "# Looking at the shape they are finally exactly the same\n",
    "print(messages.shape)\n",
    "print(categories.shape)"
   ]
  },
  {
   "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": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge datasets\n",
    "df = pd.merge(messages, categories, on='id', how='inner')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26180, 5)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Making sure that the size is correct\n",
    "df.shape"
   ]
  },
  {
   "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": "code",
   "execution_count": 13,
   "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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "                   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",
       "              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",
       "                  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",
       "                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",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe of the 36 individual category columns\n",
    "categories = df['categories'].str.split(pat=';', expand=True)\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        0        1      2            3             4                 5   \\\n",
      "0  related  request  offer  aid_related  medical_help  medical_products   \n",
      "\n",
      "                  6         7         8            9       ...        \\\n",
      "0  search_and_rescue  security  military  child_alone      ...         \n",
      "\n",
      "            26                    27               28      29     30    31  \\\n",
      "0  aid_centers  other_infrastructure  weather_related  floods  storm  fire   \n",
      "\n",
      "           32    33             34             35  \n",
      "0  earthquake  cold  other_weather  direct_report  \n",
      "\n",
      "[1 rows x 36 columns]\n"
     ]
    }
   ],
   "source": [
    "# select the first row of the categories dataframe\n",
    "row = categories.head(1)\n",
    "\n",
    "# use this row to extract a list of new column names for categories.\n",
    "# one way is to apply a lambda function that takes everything \n",
    "# up to the second to last character of each string with slicing\n",
    "category_colnames = row.apply(lambda x: x.str[:-2])\n",
    "print(category_colnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     related    request    offer    aid_related    medical_help  \\\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",
       "     medical_products    search_and_rescue    security    military  \\\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",
       "     child_alone       ...           aid_centers    other_infrastructure  \\\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",
       "     weather_related    floods    storm    fire    earthquake    cold  \\\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",
       "     other_weather    direct_report  \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",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the columns of `categories`\n",
    "categories.rename(columns=category_colnames.iloc[0], inplace=True)\n",
    "categories.head()"
   ]
  },
  {
   "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": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in categories:\n",
    "    # set each value to be the last character of the string\n",
    "    # convert column from string to numeric\n",
    "    categories[column] = categories[column].apply(lambda x: int(x[-1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "related : [1 0 2]\n",
      "request : [0 1]\n",
      "offer : [0 1]\n",
      "aid_related : [0 1]\n",
      "medical_help : [0 1]\n",
      "medical_products : [0 1]\n",
      "search_and_rescue : [0 1]\n",
      "security : [0 1]\n",
      "military : [0 1]\n",
      "child_alone : [0]\n",
      "water : [0 1]\n",
      "food : [0 1]\n",
      "shelter : [0 1]\n",
      "clothing : [0 1]\n",
      "money : [0 1]\n",
      "missing_people : [0 1]\n",
      "refugees : [0 1]\n",
      "death : [0 1]\n",
      "other_aid : [0 1]\n",
      "infrastructure_related : [0 1]\n",
      "transport : [0 1]\n",
      "buildings : [0 1]\n",
      "electricity : [0 1]\n",
      "tools : [0 1]\n",
      "hospitals : [0 1]\n",
      "shops : [0 1]\n",
      "aid_centers : [0 1]\n",
      "other_infrastructure : [0 1]\n",
      "weather_related : [0 1]\n",
      "floods : [0 1]\n",
      "storm : [0 1]\n",
      "fire : [0 1]\n",
      "earthquake : [0 1]\n",
      "cold : [0 1]\n",
      "other_weather : [0 1]\n",
      "direct_report : [0 1]\n"
     ]
    }
   ],
   "source": [
    "for column in categories:\n",
    "    # Noted that related is not a binary variable right now\n",
    "    print(\"{} : {}\".format(column, categories[column].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean values two in the related column\n",
    "categories['related'].replace(2, 1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 0], dtype=int64)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories['related'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>2</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>NaN</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>NaN</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>NaN</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>NaN</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>NaN</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   2  \n",
       "0     0              0              0 NaN  \n",
       "1     0              0              0 NaN  \n",
       "2     0              0              0 NaN  \n",
       "3     0              0              0 NaN  \n",
       "4     0              0              0 NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "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": 28,
   "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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the original categories column from `df`\n",
    "df.drop(columns='categories', axis=0, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>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>2</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>NaN</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>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>NaN</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>NaN</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>NaN</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 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",
       "   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   2  \n",
       "0     0              0              0 NaN  \n",
       "1     0              0              0 NaN  \n",
       "2     0              0              0 NaN  \n",
       "3     0              0              0 NaN  \n",
       "4     0              0              0 NaN  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate the original dataframe with the new `categories` dataframe\n",
    "df = pd.concat([df, categories], axis=1)\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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check number of duplicates\n",
    "#Already performed these steps in step 1.1"
   ]
  },
  {
   "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": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('data/DisasterResponse.db')\n",
    "\n",
    "df.to_sql(\"messages\", conn, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "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>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>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>2</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>NaN</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>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>NaN</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>NaN</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>NaN</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 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",
       "   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   2  \n",
       "0     0              0              0 NaN  \n",
       "1     0              0              0 NaN  \n",
       "2     0              0              0 NaN  \n",
       "3     0              0              0 NaN  \n",
       "4     0              0              0 NaN  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure that we can query the results back\n",
    "test = pd.read_sql('SELECT * FROM messages', conn)\n",
    "test.head()"
   ]
  },
  {
   "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": []
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}