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": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                            message  \\\n0   2  Weather update - a cold front from Cuba that c...   \n1   7            Is the Hurricane over or is it not over   \n2   8                    Looking for someone but no name   \n3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4  12  says: west side of Haiti, rest of the country ...   \n\n                                            original   genre  \n0  Un front froid se retrouve sur Cuba ce matin. ...  direct  \n1                 Cyclone nan fini osinon li pa fini  direct  \n2  Patnm, di Maryani relem pou li banm nouvel li ...  direct  \n3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct  \n4  facade ouest d Haiti et le reste du pays aujou...  direct  ",
      "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>"
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "source": [
    "# load messages dataset\n",
    "messages = pd.read_csv(\"data/disaster_messages.csv\")\n",
    "messages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "id              0\nmessage         0\noriginal    16064\ngenre           0\ndtype: int64"
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "messages.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "[    2     7     8 ... 30263 30264 30265]\n['Weather update - a cold front from Cuba that could pass over Haiti'\n 'Is the Hurricane over or is it not over'\n 'Looking for someone but no name' ...\n \"Proshika, operating in Cox's Bazar municipality and 5 other unions, Ramu and Chokoria, assessment, 5 kg rice, 1,5 kg lentils to 700 families.\"\n 'Some 2,000 women protesting against the conduct of the elections were teargassed as they tried to converge on the local electoral commission offices in the southern oil city of Port Harcourt.'\n 'A radical shift in thinking came about as a result of this meeting, recognizing that HIV/AIDS is at the core of the humanitarian crisis and identifying the crisis itself as a function of the HIV/AIDS pandemic.']\n['Un front froid se retrouve sur Cuba ce matin. Il pourrait traverser Haiti demain. Des averses de pluie isolee sont encore prevues sur notre region ce soi'\n 'Cyclone nan fini osinon li pa fini'\n 'Patnm, di Maryani relem pou li banm nouvel li ak timoun yo. Mesi se john jean depi Monben kwochi.'\n ...\n 'shikarpur mein swanyasevak ki avyashaktha he.khana,dhavainyon ki zakth zaroorath hein.'\n 'JO KO KHANA, PAANI, DAWAYIAN, NAOV CHAHIYE.'\n 'Umer kot: Sindh: logon ko rehne ke liye ghar,sharan,dera avyashak he.yahan log kinare par kade hein.']\n['direct' 'social' 'news']\n"
    }
   ],
   "source": [
    "for col in messages.columns:\n",
    "    print(messages[col].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(26248, 4)"
     },
     "metadata": {},
     "execution_count": 37
    }
   ],
   "source": [
    "messages.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(26180, 4)"
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "source": [
    "messages.drop_duplicates().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "          id                                            message  \\\n162      202  ?? port au prince ?? and food. they need gover...   \n163      202  ?? port au prince ?? and food. they need gover...   \n655      804  elle est vraiment malade et a besoin d'aide. u...   \n656      804  elle est vraiment malade et a besoin d'aide. u...   \n709      862  What is the address of the radio station? I as...   \n...      ...                                                ...   \n24856  28687  At present, UNHCR has 2,500 additional tents a...   \n25155  29022  In a field in Jallouzai, just inside Pakistan,...   \n25156  29022  In a field in Jallouzai, just inside Pakistan,...   \n25241  29119  Most victims (90 per cent) show little or no s...   \n25242  29119  Most victims (90 per cent) show little or no s...   \n\n                                                original   genre  \n162    p bay pap la syen ak manje. Yo bezwen ed gouve...  direct  \n163    p bay pap la syen ak manje. Yo bezwen ed gouve...  direct  \n655    she is really sick she need your help. please ...  direct  \n656    she is really sick she need your help. please ...  direct  \n709    Ki adres radyo a? Paske m bezwen al depoze dos...  direct  \n...                                                  ...     ...  \n24856                                                NaN    news  \n25155                                                NaN    news  \n25156                                                NaN    news  \n25241                                                NaN    news  \n25242                                                NaN    news  \n\n[135 rows x 4 columns]",
      "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    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>24856</th>\n      <td>28687</td>\n      <td>At present, UNHCR has 2,500 additional tents a...</td>\n      <td>NaN</td>\n      <td>news</td>\n    </tr>\n    <tr>\n      <th>25155</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    </tr>\n    <tr>\n      <th>25156</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    </tr>\n    <tr>\n      <th>25241</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    </tr>\n    <tr>\n      <th>25242</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    </tr>\n  </tbody>\n</table>\n<p>135 rows × 4 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "source": [
    "#messages[messages[\"id\"].duplicated(keep=False)]\n",
    "messages[messages.duplicated(keep=False)]\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "id 68\nmessage 71\noriginal 16617\ngenre 26245\n"
    }
   ],
   "source": [
    "for col in messages.columns:\n",
    "    print(col, messages[col].duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                            message  \\\n0   2  Weather update - a cold front from Cuba that c...   \n1   7            Is the Hurricane over or is it not over   \n2   8                    Looking for someone but no name   \n3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4  12  says: west side of Haiti, rest of the country ...   \n\n                                            original   genre  \n0  Un front froid se retrouve sur Cuba ce matin. ...  direct  \n1                 Cyclone nan fini osinon li pa fini  direct  \n2  Patnm, di Maryani relem pou li banm nouvel li ...  direct  \n3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct  \n4  facade ouest d Haiti et le reste du pays aujou...  direct  ",
      "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>"
     },
     "metadata": {},
     "execution_count": 41
    }
   ],
   "source": [
    "messages = messages.drop_duplicates()\n",
    "messages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                         categories\n0   2  related-1;request-0;offer-0;aid_related-0;medi...\n1   7  related-1;request-0;offer-0;aid_related-1;medi...\n2   8  related-1;request-0;offer-0;aid_related-0;medi...\n3   9  related-1;request-1;offer-0;aid_related-1;medi...\n4  12  related-1;request-0;offer-0;aid_related-0;medi...",
      "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>"
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "source": [
    "# load categories dataset\n",
    "categories = pd.read_csv(\"data/disaster_categories.csv\")\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "id            0\ncategories    0\ndtype: int64"
     },
     "metadata": {},
     "execution_count": 43
    }
   ],
   "source": [
    "categories.isnull().sum()\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "id 68\ncategories 22245\n"
    }
   ],
   "source": [
    "for col in categories.columns:\n",
    "    print(col, categories[col].duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(26248, 2)"
     },
     "metadata": {},
     "execution_count": 45
    }
   ],
   "source": [
    "categories.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(26216, 2)"
     },
     "metadata": {},
     "execution_count": 46
    }
   ],
   "source": [
    "categories.drop_duplicates().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = categories.drop_duplicates()"
   ]
  },
  {
   "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": 48,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                            message  \\\n0   2  Weather update - a cold front from Cuba that c...   \n1   7            Is the Hurricane over or is it not over   \n2   8                    Looking for someone but no name   \n3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4  12  says: west side of Haiti, rest of the country ...   \n\n                                            original   genre  \\\n0  Un front froid se retrouve sur Cuba ce matin. ...  direct   \n1                 Cyclone nan fini osinon li pa fini  direct   \n2  Patnm, di Maryani relem pou li banm nouvel li ...  direct   \n3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct   \n4  facade ouest d Haiti et le reste du pays aujou...  direct   \n\n                                          categories  \n0  related-1;request-0;offer-0;aid_related-0;medi...  \n1  related-1;request-0;offer-0;aid_related-1;medi...  \n2  related-1;request-0;offer-0;aid_related-0;medi...  \n3  related-1;request-1;offer-0;aid_related-1;medi...  \n4  related-1;request-0;offer-0;aid_related-0;medi...  ",
      "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>"
     },
     "metadata": {},
     "execution_count": 48
    }
   ],
   "source": [
    "# merge datasets\n",
    "df = pd.merge(messages, categories, how = \"left\")\n",
    "df.head()"
   ]
  },
  {
   "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": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a dataframe of the 36 individual category columns\n",
    "#df[\"categories\"].str.split(\";\")\n",
    "#categories = df[\"categories\"].apply(lambda x:pd.Series(x.split(\";\")))\n",
    "categories  = df[\"categories\"].str.split(\";\", expand=True)\n",
    "#categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "              0          1        2              3               4   \\\n0      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n1      related-1  request-0  offer-0  aid_related-1  medical_help-0   \n2      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n3      related-1  request-1  offer-0  aid_related-1  medical_help-0   \n4      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n...          ...        ...      ...            ...             ...   \n26211  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n26212  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n26213  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n26214  related-1  request-0  offer-0  aid_related-1  medical_help-0   \n26215  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n\n                       5                    6           7           8   \\\n0      medical_products-0  search_and_rescue-0  security-0  military-0   \n1      medical_products-0  search_and_rescue-0  security-0  military-0   \n2      medical_products-0  search_and_rescue-0  security-0  military-0   \n3      medical_products-1  search_and_rescue-0  security-0  military-0   \n4      medical_products-0  search_and_rescue-0  security-0  military-0   \n...                   ...                  ...         ...         ...   \n26211  medical_products-0  search_and_rescue-0  security-0  military-0   \n26212  medical_products-0  search_and_rescue-0  security-0  military-0   \n26213  medical_products-0  search_and_rescue-0  security-0  military-0   \n26214  medical_products-0  search_and_rescue-0  security-0  military-1   \n26215  medical_products-0  search_and_rescue-0  security-0  military-0   \n\n                  9   ...             26                      27  \\\n0      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n1      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n2      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n3      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n4      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n...              ...  ...            ...                     ...   \n26211  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26212  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26213  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26214  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26215  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n\n                      28        29       30      31            32      33  \\\n0      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n1      weather_related-1  floods-0  storm-1  fire-0  earthquake-0  cold-0   \n2      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n3      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n4      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n...                  ...       ...      ...     ...           ...     ...   \n26211  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26212  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26213  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26214  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26215  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n\n                    34               35  \n0      other_weather-0  direct_report-0  \n1      other_weather-0  direct_report-0  \n2      other_weather-0  direct_report-0  \n3      other_weather-0  direct_report-0  \n4      other_weather-0  direct_report-0  \n...                ...              ...  \n26211  other_weather-0  direct_report-0  \n26212  other_weather-0  direct_report-0  \n26213  other_weather-0  direct_report-0  \n26214  other_weather-0  direct_report-0  \n26215  other_weather-0  direct_report-0  \n\n[26216 rows x 36 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n      <th>...</th>\n      <th>26</th>\n      <th>27</th>\n      <th>28</th>\n      <th>29</th>\n      <th>30</th>\n      <th>31</th>\n      <th>32</th>\n      <th>33</th>\n      <th>34</th>\n      <th>35</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-1</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-1</td>\n      <td>floods-0</td>\n      <td>storm-1</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>related-1</td>\n      <td>request-1</td>\n      <td>offer-0</td>\n      <td>aid_related-1</td>\n      <td>medical_help-0</td>\n      <td>medical_products-1</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>26211</th>\n      <td>related-0</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26212</th>\n      <td>related-0</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26213</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>26214</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-1</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-1</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26215</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>26216 rows × 36 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 92
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['related',\n 'request',\n 'offer',\n 'aid_related',\n 'medical_help',\n 'medical_products',\n 'search_and_rescue',\n 'security',\n 'military',\n 'child_alone',\n 'water',\n 'food',\n 'shelter',\n 'clothing',\n 'money',\n 'missing_people',\n 'refugees',\n 'death',\n 'other_aid',\n 'infrastructure_related',\n 'transport',\n 'buildings',\n 'electricity',\n 'tools',\n 'hospitals',\n 'shops',\n 'aid_centers',\n 'other_infrastructure',\n 'weather_related',\n 'floods',\n 'storm',\n 'fire',\n 'earthquake',\n 'cold',\n 'other_weather',\n 'direct_report']"
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "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.str.extract(r\"([a-zA-Z _]*)\")[0].tolist()\n",
    "category_colnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     related    request    offer    aid_related    medical_help  \\\n0  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n1  related-1  request-0  offer-0  aid_related-1  medical_help-0   \n2  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n3  related-1  request-1  offer-0  aid_related-1  medical_help-0   \n4  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n\n     medical_products    search_and_rescue    security    military  \\\n0  medical_products-0  search_and_rescue-0  security-0  military-0   \n1  medical_products-0  search_and_rescue-0  security-0  military-0   \n2  medical_products-0  search_and_rescue-0  security-0  military-0   \n3  medical_products-1  search_and_rescue-0  security-0  military-0   \n4  medical_products-0  search_and_rescue-0  security-0  military-0   \n\n     child_alone  ...    aid_centers    other_infrastructure  \\\n0  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n1  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n2  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n3  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n4  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n\n     weather_related    floods    storm    fire    earthquake    cold  \\\n0  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n1  weather_related-1  floods-0  storm-1  fire-0  earthquake-0  cold-0   \n2  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n3  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n4  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n\n     other_weather    direct_report  \n0  other_weather-0  direct_report-0  \n1  other_weather-0  direct_report-0  \n2  other_weather-0  direct_report-0  \n3  other_weather-0  direct_report-0  \n4  other_weather-0  direct_report-0  \n\n[5 rows x 36 columns]",
      "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>"
     },
     "metadata": {},
     "execution_count": 51
    }
   ],
   "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": 52,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "         related    request    offer    aid_related    medical_help  \\\n0      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n1      related-1  request-0  offer-0  aid_related-1  medical_help-0   \n2      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n3      related-1  request-1  offer-0  aid_related-1  medical_help-0   \n4      related-1  request-0  offer-0  aid_related-0  medical_help-0   \n...          ...        ...      ...            ...             ...   \n26211  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n26212  related-0  request-0  offer-0  aid_related-0  medical_help-0   \n26213  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n26214  related-1  request-0  offer-0  aid_related-1  medical_help-0   \n26215  related-1  request-0  offer-0  aid_related-0  medical_help-0   \n\n         medical_products    search_and_rescue    security    military  \\\n0      medical_products-0  search_and_rescue-0  security-0  military-0   \n1      medical_products-0  search_and_rescue-0  security-0  military-0   \n2      medical_products-0  search_and_rescue-0  security-0  military-0   \n3      medical_products-1  search_and_rescue-0  security-0  military-0   \n4      medical_products-0  search_and_rescue-0  security-0  military-0   \n...                   ...                  ...         ...         ...   \n26211  medical_products-0  search_and_rescue-0  security-0  military-0   \n26212  medical_products-0  search_and_rescue-0  security-0  military-0   \n26213  medical_products-0  search_and_rescue-0  security-0  military-0   \n26214  medical_products-0  search_and_rescue-0  security-0  military-1   \n26215  medical_products-0  search_and_rescue-0  security-0  military-0   \n\n         child_alone  ...    aid_centers    other_infrastructure  \\\n0      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n1      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n2      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n3      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n4      child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n...              ...  ...            ...                     ...   \n26211  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26212  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26213  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26214  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n26215  child_alone-0  ...  aid_centers-0  other_infrastructure-0   \n\n         weather_related    floods    storm    fire    earthquake    cold  \\\n0      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n1      weather_related-1  floods-0  storm-1  fire-0  earthquake-0  cold-0   \n2      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n3      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n4      weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n...                  ...       ...      ...     ...           ...     ...   \n26211  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26212  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26213  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26214  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n26215  weather_related-0  floods-0  storm-0  fire-0  earthquake-0  cold-0   \n\n         other_weather    direct_report  \n0      other_weather-0  direct_report-0  \n1      other_weather-0  direct_report-0  \n2      other_weather-0  direct_report-0  \n3      other_weather-0  direct_report-0  \n4      other_weather-0  direct_report-0  \n...                ...              ...  \n26211  other_weather-0  direct_report-0  \n26212  other_weather-0  direct_report-0  \n26213  other_weather-0  direct_report-0  \n26214  other_weather-0  direct_report-0  \n26215  other_weather-0  direct_report-0  \n\n[26216 rows x 36 columns]",
      "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    <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>26211</th>\n      <td>related-0</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26212</th>\n      <td>related-0</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-0</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-0</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26213</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>26214</th>\n      <td>related-1</td>\n      <td>request-0</td>\n      <td>offer-0</td>\n      <td>aid_related-1</td>\n      <td>medical_help-0</td>\n      <td>medical_products-0</td>\n      <td>search_and_rescue-0</td>\n      <td>security-0</td>\n      <td>military-1</td>\n      <td>child_alone-0</td>\n      <td>...</td>\n      <td>aid_centers-0</td>\n      <td>other_infrastructure-0</td>\n      <td>weather_related-0</td>\n      <td>floods-0</td>\n      <td>storm-0</td>\n      <td>fire-0</td>\n      <td>earthquake-0</td>\n      <td>cold-0</td>\n      <td>other_weather-0</td>\n      <td>direct_report-0</td>\n    </tr>\n    <tr>\n      <th>26215</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>26216 rows × 36 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 52
    }
   ],
   "source": [
    "categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       0\n0      0\n1      0\n2      0\n3      0\n4      0\n...   ..\n26211  0\n26212  0\n26213  0\n26214  0\n26215  0\n\n[26216 rows x 1 columns]",
      "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    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>26211</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>26212</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>26213</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>26214</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>26215</th>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>26216 rows × 1 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 53
    }
   ],
   "source": [
    "categories.iloc[:,4].str.extract(r\"([0-9].*)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   related  request  offer  aid_related  medical_help  medical_products  \\\n0        1        0      0            0             0                 0   \n1        1        0      0            1             0                 0   \n2        1        0      0            0             0                 0   \n3        1        1      0            1             0                 1   \n4        1        0      0            0             0                 0   \n\n   search_and_rescue  security  military  child_alone  ...  aid_centers  \\\n0                  0         0         0            0  ...            0   \n1                  0         0         0            0  ...            0   \n2                  0         0         0            0  ...            0   \n3                  0         0         0            0  ...            0   \n4                  0         0         0            0  ...            0   \n\n   other_infrastructure  weather_related  floods  storm  fire  earthquake  \\\n0                     0                0       0      0     0           0   \n1                     0                1       0      1     0           0   \n2                     0                0       0      0     0           0   \n3                     0                0       0      0     0           0   \n4                     0                0       0      0     0           0   \n\n   cold  other_weather  direct_report  \n0     0              0              0  \n1     0              0              0  \n2     0              0              0  \n3     0              0              0  \n4     0              0              0  \n\n[5 rows x 36 columns]",
      "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>"
     },
     "metadata": {},
     "execution_count": 54
    }
   ],
   "source": [
    "for col in categories.columns:\n",
    "    categories[col] = categories[col].str.extract(r\"([0-9].*)\")\n",
    "    categories[col] = categories[col].astype(\"int\")\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "<class 'pandas.core.frame.DataFrame'>\nInt64Index: 26216 entries, 0 to 26215\nData columns (total 36 columns):\n #   Column                  Non-Null Count  Dtype\n---  ------                  --------------  -----\n 0   related                 26216 non-null  int32\n 1   request                 26216 non-null  int32\n 2   offer                   26216 non-null  int32\n 3   aid_related             26216 non-null  int32\n 4   medical_help            26216 non-null  int32\n 5   medical_products        26216 non-null  int32\n 6   search_and_rescue       26216 non-null  int32\n 7   security                26216 non-null  int32\n 8   military                26216 non-null  int32\n 9   child_alone             26216 non-null  int32\n 10  water                   26216 non-null  int32\n 11  food                    26216 non-null  int32\n 12  shelter                 26216 non-null  int32\n 13  clothing                26216 non-null  int32\n 14  money                   26216 non-null  int32\n 15  missing_people          26216 non-null  int32\n 16  refugees                26216 non-null  int32\n 17  death                   26216 non-null  int32\n 18  other_aid               26216 non-null  int32\n 19  infrastructure_related  26216 non-null  int32\n 20  transport               26216 non-null  int32\n 21  buildings               26216 non-null  int32\n 22  electricity             26216 non-null  int32\n 23  tools                   26216 non-null  int32\n 24  hospitals               26216 non-null  int32\n 25  shops                   26216 non-null  int32\n 26  aid_centers             26216 non-null  int32\n 27  other_infrastructure    26216 non-null  int32\n 28  weather_related         26216 non-null  int32\n 29  floods                  26216 non-null  int32\n 30  storm                   26216 non-null  int32\n 31  fire                    26216 non-null  int32\n 32  earthquake              26216 non-null  int32\n 33  cold                    26216 non-null  int32\n 34  other_weather           26216 non-null  int32\n 35  direct_report           26216 non-null  int32\ndtypes: int32(36)\nmemory usage: 5.1 MB\n"
    }
   ],
   "source": [
    "categories.info()"
   ]
  },
  {
   "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": 56,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "          id                                            message  \\\n0          2  Weather update - a cold front from Cuba that c...   \n1          7            Is the Hurricane over or is it not over   \n2          8                    Looking for someone but no name   \n3          9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4         12  says: west side of Haiti, rest of the country ...   \n...      ...                                                ...   \n26211  30261  The training demonstrated how to enhance micro...   \n26212  30262  A suitable candidate has been selected and OCH...   \n26213  30263  Proshika, operating in Cox's Bazar municipalit...   \n26214  30264  Some 2,000 women protesting against the conduc...   \n26215  30265  A radical shift in thinking came about as a re...   \n\n                                                original   genre  \\\n0      Un front froid se retrouve sur Cuba ce matin. ...  direct   \n1                     Cyclone nan fini osinon li pa fini  direct   \n2      Patnm, di Maryani relem pou li banm nouvel li ...  direct   \n3      UN reports Leogane 80-90 destroyed. Only Hospi...  direct   \n4      facade ouest d Haiti et le reste du pays aujou...  direct   \n...                                                  ...     ...   \n26211                                                NaN    news   \n26212                                                NaN    news   \n26213                                                NaN    news   \n26214                                                NaN    news   \n26215                                                NaN    news   \n\n                                              categories  \n0      related-1;request-0;offer-0;aid_related-0;medi...  \n1      related-1;request-0;offer-0;aid_related-1;medi...  \n2      related-1;request-0;offer-0;aid_related-0;medi...  \n3      related-1;request-1;offer-0;aid_related-1;medi...  \n4      related-1;request-0;offer-0;aid_related-0;medi...  \n...                                                  ...  \n26211  related-0;request-0;offer-0;aid_related-0;medi...  \n26212  related-0;request-0;offer-0;aid_related-0;medi...  \n26213  related-1;request-0;offer-0;aid_related-0;medi...  \n26214  related-1;request-0;offer-0;aid_related-1;medi...  \n26215  related-1;request-0;offer-0;aid_related-0;medi...  \n\n[26216 rows x 5 columns]",
      "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    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>26211</th>\n      <td>30261</td>\n      <td>The training demonstrated how to enhance micro...</td>\n      <td>NaN</td>\n      <td>news</td>\n      <td>related-0;request-0;offer-0;aid_related-0;medi...</td>\n    </tr>\n    <tr>\n      <th>26212</th>\n      <td>30262</td>\n      <td>A suitable candidate has been selected and OCH...</td>\n      <td>NaN</td>\n      <td>news</td>\n      <td>related-0;request-0;offer-0;aid_related-0;medi...</td>\n    </tr>\n    <tr>\n      <th>26213</th>\n      <td>30263</td>\n      <td>Proshika, operating in Cox's Bazar municipalit...</td>\n      <td>NaN</td>\n      <td>news</td>\n      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n    </tr>\n    <tr>\n      <th>26214</th>\n      <td>30264</td>\n      <td>Some 2,000 women protesting against the conduc...</td>\n      <td>NaN</td>\n      <td>news</td>\n      <td>related-1;request-0;offer-0;aid_related-1;medi...</td>\n    </tr>\n    <tr>\n      <th>26215</th>\n      <td>30265</td>\n      <td>A radical shift in thinking came about as a re...</td>\n      <td>NaN</td>\n      <td>news</td>\n      <td>related-1;request-0;offer-0;aid_related-0;medi...</td>\n    </tr>\n  </tbody>\n</table>\n<p>26216 rows × 5 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 56
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                            message  \\\n0   2  Weather update - a cold front from Cuba that c...   \n1   7            Is the Hurricane over or is it not over   \n2   8                    Looking for someone but no name   \n3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4  12  says: west side of Haiti, rest of the country ...   \n\n                                            original   genre  \n0  Un front froid se retrouve sur Cuba ce matin. ...  direct  \n1                 Cyclone nan fini osinon li pa fini  direct  \n2  Patnm, di Maryani relem pou li banm nouvel li ...  direct  \n3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct  \n4  facade ouest d Haiti et le reste du pays aujou...  direct  ",
      "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>"
     },
     "metadata": {},
     "execution_count": 57
    }
   ],
   "source": [
    "# drop the original categories column from `df`\n",
    "df = df.drop([\"categories\"], axis=\"columns\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   id                                            message  \\\n0   2  Weather update - a cold front from Cuba that c...   \n1   7            Is the Hurricane over or is it not over   \n2   8                    Looking for someone but no name   \n3   9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4  12  says: west side of Haiti, rest of the country ...   \n\n                                            original   genre  related  \\\n0  Un front froid se retrouve sur Cuba ce matin. ...  direct        1   \n1                 Cyclone nan fini osinon li pa fini  direct        1   \n2  Patnm, di Maryani relem pou li banm nouvel li ...  direct        1   \n3  UN reports Leogane 80-90 destroyed. Only Hospi...  direct        1   \n4  facade ouest d Haiti et le reste du pays aujou...  direct        1   \n\n   request  offer  aid_related  medical_help  medical_products  ...  \\\n0        0      0            0             0                 0  ...   \n1        0      0            1             0                 0  ...   \n2        0      0            0             0                 0  ...   \n3        1      0            1             0                 1  ...   \n4        0      0            0             0                 0  ...   \n\n   aid_centers  other_infrastructure  weather_related  floods  storm  fire  \\\n0            0                     0                0       0      0     0   \n1            0                     0                1       0      1     0   \n2            0                     0                0       0      0     0   \n3            0                     0                0       0      0     0   \n4            0                     0                0       0      0     0   \n\n   earthquake  cold  other_weather  direct_report  \n0           0     0              0              0  \n1           0     0              0              0  \n2           0     0              0              0  \n3           0     0              0              0  \n4           0     0              0              0  \n\n[5 rows x 40 columns]",
      "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>"
     },
     "metadata": {},
     "execution_count": 58
    }
   ],
   "source": [
    "# concatenate the original dataframe with the new `categories` dataframe\n",
    "df = pd.concat([df, categories], axis = \"columns\")\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": 59,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "0"
     },
     "metadata": {},
     "execution_count": 59
    }
   ],
   "source": [
    "# check number of duplicates\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates\n",
    "# DONE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "0"
     },
     "metadata": {},
     "execution_count": 61
    }
   ],
   "source": [
    "# check number of duplicates\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create dummy variables from genre column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df, pd.get_dummies(df.genre)], axis=1)\n",
    "df = df.drop(['genre', 'social'], axis=1)\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Index(['id', 'message', 'original', 'related', 'request', 'offer',\n       'aid_related', 'medical_help', 'medical_products', 'search_and_rescue',\n       'security', 'military', 'child_alone', 'water', 'food', 'shelter',\n       'clothing', 'money', 'missing_people', 'refugees', 'death', 'other_aid',\n       'infrastructure_related', 'transport', 'buildings', 'electricity',\n       'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure',\n       'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold',\n       'other_weather', 'direct_report', 'direct', 'news'],\n      dtype='object')"
     },
     "metadata": {},
     "execution_count": 63
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "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": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///data/disaster_response.db')\n",
    "df.to_sql('messages_categories', engine, index=False, if_exists=\"replace\")"
   ]
  },
  {
   "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": [
    "#ttps://www.pythonsheets.com/notes/python-sqlalchemy.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as db\n",
    "connection = engine.connect()\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = connection.execute(\"SELECT * FROM messages_categories\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "          id                                            message  \\\n0          2  Weather update - a cold front from Cuba that c...   \n1          7            Is the Hurricane over or is it not over   \n2          8                    Looking for someone but no name   \n3          9  UN reports Leogane 80-90 destroyed. Only Hospi...   \n4         12  says: west side of Haiti, rest of the country ...   \n...      ...                                                ...   \n26211  30261  The training demonstrated how to enhance micro...   \n26212  30262  A suitable candidate has been selected and OCH...   \n26213  30263  Proshika, operating in Cox's Bazar municipalit...   \n26214  30264  Some 2,000 women protesting against the conduc...   \n26215  30265  A radical shift in thinking came about as a re...   \n\n                                                original  related  request  \\\n0      Un front froid se retrouve sur Cuba ce matin. ...        1        0   \n1                     Cyclone nan fini osinon li pa fini        1        0   \n2      Patnm, di Maryani relem pou li banm nouvel li ...        1        0   \n3      UN reports Leogane 80-90 destroyed. Only Hospi...        1        1   \n4      facade ouest d Haiti et le reste du pays aujou...        1        0   \n...                                                  ...      ...      ...   \n26211                                               None        0        0   \n26212                                               None        0        0   \n26213                                               None        1        0   \n26214                                               None        1        0   \n26215                                               None        1        0   \n\n       offer  aid_related  medical_help  medical_products  search_and_rescue  \\\n0          0            0             0                 0                  0   \n1          0            1             0                 0                  0   \n2          0            0             0                 0                  0   \n3          0            1             0                 1                  0   \n4          0            0             0                 0                  0   \n...      ...          ...           ...               ...                ...   \n26211      0            0             0                 0                  0   \n26212      0            0             0                 0                  0   \n26213      0            0             0                 0                  0   \n26214      0            1             0                 0                  0   \n26215      0            0             0                 0                  0   \n\n       ...  weather_related  floods  storm  fire  earthquake  cold  \\\n0      ...                0       0      0     0           0     0   \n1      ...                1       0      1     0           0     0   \n2      ...                0       0      0     0           0     0   \n3      ...                0       0      0     0           0     0   \n4      ...                0       0      0     0           0     0   \n...    ...              ...     ...    ...   ...         ...   ...   \n26211  ...                0       0      0     0           0     0   \n26212  ...                0       0      0     0           0     0   \n26213  ...                0       0      0     0           0     0   \n26214  ...                0       0      0     0           0     0   \n26215  ...                0       0      0     0           0     0   \n\n       other_weather  direct_report  direct  news  \n0                  0              0       1     0  \n1                  0              0       1     0  \n2                  0              0       1     0  \n3                  0              0       1     0  \n4                  0              0       1     0  \n...              ...            ...     ...   ...  \n26211              0              0       0     1  \n26212              0              0       0     1  \n26213              0              0       0     1  \n26214              0              0       0     1  \n26215              0              0       0     1  \n\n[26216 rows x 41 columns]",
      "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>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>...</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>direct</th>\n      <th>news</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>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</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>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>...</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>1</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>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</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>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>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <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>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</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>26211</th>\n      <td>30261</td>\n      <td>The training demonstrated how to enhance micro...</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>26212</th>\n      <td>30262</td>\n      <td>A suitable candidate has been selected and OCH...</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>26213</th>\n      <td>30263</td>\n      <td>Proshika, operating in Cox's Bazar municipalit...</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>26214</th>\n      <td>30264</td>\n      <td>Some 2,000 women protesting against the conduc...</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>26215</th>\n      <td>30265</td>\n      <td>A radical shift in thinking came about as a re...</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n<p>26216 rows × 41 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 68
    }
   ],
   "source": [
    "pd.read_sql(\"SELECT * FROM messages_categories\", con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}