data/ETL Pipeline Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Pipeline Preparation\n",
    "\n",
    "\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": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import pandas as pd\n",
    "import numpy as  np\n",
    "import sqlite3"
   ]
  },
  {
   "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>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": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load messages dataset\n",
    "messages = pd.read_csv('disaster_messages.csv')\n",
    "messages.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load categories dataset\n",
    "categories = pd.read_csv('disaster_categories.csv')\n",
    "categories.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows and columns in messages: (26248, 4)\n",
      "Rows and columns in categories: (26248, 2)\n"
     ]
    }
   ],
   "source": [
    "# Looking at the shapes of the DataFrames:\n",
    "print('Rows and columns in messages:', messages.shape)\n",
    "print('Rows and columns in categories:', 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": 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",
       "      <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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge datasets\n",
    "df = messages.merge(categories, on='id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows and columns in the merged dataset: (26386, 5)\n"
     ]
    }
   ],
   "source": [
    "# Looking at the shapes of the DataFrame:\n",
    "print('Rows and columns in the merged dataset:', 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": 30,
   "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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe of the 36 individual category columns\n",
    "#pd.DataFrame([''.join(c).split(';') for c in categories.categories])\n",
    "categories =  df.categories.str.split(pat=';', expand=True)\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0                    related\n",
      "1                    request\n",
      "2                      offer\n",
      "3                aid_related\n",
      "4               medical_help\n",
      "5           medical_products\n",
      "6          search_and_rescue\n",
      "7                   security\n",
      "8                   military\n",
      "9                child_alone\n",
      "10                     water\n",
      "11                      food\n",
      "12                   shelter\n",
      "13                  clothing\n",
      "14                     money\n",
      "15            missing_people\n",
      "16                  refugees\n",
      "17                     death\n",
      "18                 other_aid\n",
      "19    infrastructure_related\n",
      "20                 transport\n",
      "21                 buildings\n",
      "22               electricity\n",
      "23                     tools\n",
      "24                 hospitals\n",
      "25                     shops\n",
      "26               aid_centers\n",
      "27      other_infrastructure\n",
      "28           weather_related\n",
      "29                    floods\n",
      "30                     storm\n",
      "31                      fire\n",
      "32                earthquake\n",
      "33                      cold\n",
      "34             other_weather\n",
      "35             direct_report\n",
      "Name: 0, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# select the first row of the categories dataframe\n",
    "row = categories.iloc[0]\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.split('-')[0])\n",
    "print(category_colnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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": [
       "0    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",
       "0    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",
       "0    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",
       "0    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",
       "0    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": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the columns of `categories`\n",
    "categories.columns = category_colnames\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": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>related</th>\n",
       "      <th>request</th>\n",
       "      <th>offer</th>\n",
       "      <th>aid_related</th>\n",
       "      <th>medical_help</th>\n",
       "      <th>medical_products</th>\n",
       "      <th>search_and_rescue</th>\n",
       "      <th>security</th>\n",
       "      <th>military</th>\n",
       "      <th>child_alone</th>\n",
       "      <th>...</th>\n",
       "      <th>aid_centers</th>\n",
       "      <th>other_infrastructure</th>\n",
       "      <th>weather_related</th>\n",
       "      <th>floods</th>\n",
       "      <th>storm</th>\n",
       "      <th>fire</th>\n",
       "      <th>earthquake</th>\n",
       "      <th>cold</th>\n",
       "      <th>other_weather</th>\n",
       "      <th>direct_report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0  related  request  offer  aid_related  medical_help  medical_products  \\\n",
       "0        1        0      0            0             0                 0   \n",
       "1        1        0      0            1             0                 0   \n",
       "2        1        0      0            0             0                 0   \n",
       "3        1        1      0            1             0                 1   \n",
       "4        1        0      0            0             0                 0   \n",
       "\n",
       "0  search_and_rescue  security  military  child_alone  ...  aid_centers  \\\n",
       "0                  0         0         0            0  ...            0   \n",
       "1                  0         0         0            0  ...            0   \n",
       "2                  0         0         0            0  ...            0   \n",
       "3                  0         0         0            0  ...            0   \n",
       "4                  0         0         0            0  ...            0   \n",
       "\n",
       "0  other_infrastructure  weather_related  floods  storm  fire  earthquake  \\\n",
       "0                     0                0       0      0     0           0   \n",
       "1                     0                1       0      1     0           0   \n",
       "2                     0                0       0      0     0           0   \n",
       "3                     0                0       0      0     0           0   \n",
       "4                     0                0       0      0     0           0   \n",
       "\n",
       "0  cold  other_weather  direct_report  \n",
       "0     0              0              0  \n",
       "1     0              0              0  \n",
       "2     0              0              0  \n",
       "3     0              0              0  \n",
       "4     0              0              0  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for column in categories.columns:\n",
    "    # set each value to be the last character of the string\n",
    "    categories[column] = categories[column].apply(lambda x: x.split('-')[1] if int(x.split('-')[1]) < 2 else 1)   \n",
    "    \n",
    "    # convert column from string to numeric\n",
    "    categories[column] = pd.to_numeric(categories[column])\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26386, 36)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.shape"
   ]
  },
  {
   "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": 36,
   "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": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the original categories column from `df`\n",
    "df.drop('categories', axis=1, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate the original dataframe with the new `categories` dataframe\n",
    "df = pd.concat([df, categories], join='inner', 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": 38,
   "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>164</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",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>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>165</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",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>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>658</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",
       "      <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>659</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",
       "      <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>660</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",
       "      <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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25291</th>\n",
       "      <td>29022</td>\n",
       "      <td>In a field in Jallouzai, just inside Pakistan,...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</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>25292</th>\n",
       "      <td>29022</td>\n",
       "      <td>In a field in Jallouzai, just inside Pakistan,...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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>25378</th>\n",
       "      <td>29119</td>\n",
       "      <td>Most victims (90 per cent) show little or no s...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25379</th>\n",
       "      <td>29119</td>\n",
       "      <td>Most victims (90 per cent) show little or no s...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25380</th>\n",
       "      <td>29119</td>\n",
       "      <td>Most victims (90 per cent) show little or no s...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>news</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>171 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                            message  \\\n",
       "164      202  ?? port au prince ?? and food. they need gover...   \n",
       "165      202  ?? port au prince ?? and food. they need gover...   \n",
       "658      804  elle est vraiment malade et a besoin d'aide. u...   \n",
       "659      804  elle est vraiment malade et a besoin d'aide. u...   \n",
       "660      804  elle est vraiment malade et a besoin d'aide. u...   \n",
       "...      ...                                                ...   \n",
       "25291  29022  In a field in Jallouzai, just inside Pakistan,...   \n",
       "25292  29022  In a field in Jallouzai, just inside Pakistan,...   \n",
       "25378  29119  Most victims (90 per cent) show little or no s...   \n",
       "25379  29119  Most victims (90 per cent) show little or no s...   \n",
       "25380  29119  Most victims (90 per cent) show little or no s...   \n",
       "\n",
       "                                                original   genre  related  \\\n",
       "164    p bay pap la syen ak manje. Yo bezwen ed gouve...  direct        1   \n",
       "165    p bay pap la syen ak manje. Yo bezwen ed gouve...  direct        1   \n",
       "658    she is really sick she need your help. please ...  direct        1   \n",
       "659    she is really sick she need your help. please ...  direct        1   \n",
       "660    she is really sick she need your help. please ...  direct        1   \n",
       "...                                                  ...     ...      ...   \n",
       "25291                                                NaN    news        1   \n",
       "25292                                                NaN    news        1   \n",
       "25378                                                NaN    news        1   \n",
       "25379                                                NaN    news        1   \n",
       "25380                                                NaN    news        1   \n",
       "\n",
       "       request  offer  aid_related  medical_help  medical_products  ...  \\\n",
       "164          1      0            1             0                 0  ...   \n",
       "165          1      0            1             0                 0  ...   \n",
       "658          0      0            0             0                 0  ...   \n",
       "659          0      0            0             0                 0  ...   \n",
       "660          0      0            0             0                 0  ...   \n",
       "...        ...    ...          ...           ...               ...  ...   \n",
       "25291        1      0            1             0                 1  ...   \n",
       "25292        0      0            1             0                 1  ...   \n",
       "25378        0      0            1             1                 0  ...   \n",
       "25379        0      0            1             1                 0  ...   \n",
       "25380        0      0            1             1                 0  ...   \n",
       "\n",
       "       aid_centers  other_infrastructure  weather_related  floods  storm  \\\n",
       "164              0                     0                0       0      0   \n",
       "165              0                     0                0       0      0   \n",
       "658              0                     0                0       0      0   \n",
       "659              0                     0                0       0      0   \n",
       "660              0                     0                0       0      0   \n",
       "...            ...                   ...              ...     ...    ...   \n",
       "25291            0                     0                0       0      0   \n",
       "25292            0                     0                0       0      0   \n",
       "25378            0                     0                0       0      0   \n",
       "25379            0                     0                0       0      0   \n",
       "25380            0                     0                0       0      0   \n",
       "\n",
       "       fire  earthquake  cold  other_weather  direct_report  \n",
       "164       0           0     0              0              0  \n",
       "165       0           0     0              0              0  \n",
       "658       0           0     0              0              0  \n",
       "659       0           0     0              0              0  \n",
       "660       0           0     0              0              0  \n",
       "...     ...         ...   ...            ...            ...  \n",
       "25291     0           0     0              0              0  \n",
       "25292     0           0     0              0              0  \n",
       "25378     0           0     0              0              0  \n",
       "25379     0           0     0              0              0  \n",
       "25380     0           0     0              0              0  \n",
       "\n",
       "[171 rows x 40 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# duplicate data\n",
    "df.loc[df.duplicated()]#.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of duplicated is:  171\n"
     ]
    }
   ],
   "source": [
    "# check number of duplicates\n",
    "print('The number of duplicated is: ',sum(df.duplicated()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates\n",
    "df.drop_duplicates( inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of duplicated is:  0\n"
     ]
    }
   ],
   "source": [
    "# check number of duplicates\n",
    "print('The number of duplicated is: ',sum(df.duplicated()))"
   ]
  },
  {
   "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": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlite3.connect('Database.db')\n",
    "df.to_sql('disaster_response', engine, index=False, if_exists='replace')"
   ]
  },
  {
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}