ETL_Mini_Project_APino_DMcnulty_CRichards.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('max_colwidth', 400)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the crowdfunding.xlsx Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>blurb</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_at</th>\n",
       "      <th>deadline</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>1581573600</td>\n",
       "      <td>1614578400</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400</td>\n",
       "      <td>14560</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1611554400</td>\n",
       "      <td>1621918800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400</td>\n",
       "      <td>142523</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>1608184800</td>\n",
       "      <td>1640844000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200</td>\n",
       "      <td>2477</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634792400</td>\n",
       "      <td>1642399200</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600</td>\n",
       "      <td>5265</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1608530400</td>\n",
       "      <td>1629694800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cf_id  contact_id                 company_name  \\\n",
       "0    147        4661   Baldwin, Riley and Jackson   \n",
       "1   1621        3765                     Odom Inc   \n",
       "2   1812        4187   Melton, Robinson and Fritz   \n",
       "3   2156        4941  Mcdonald, Gonzalez and Ross   \n",
       "4   1365        2199                Larson-Little   \n",
       "\n",
       "                                           blurb    goal  pledged     outcome  \\\n",
       "0           Pre-emptive tertiary standardization     100        0      failed   \n",
       "1               Managed bottom-line architecture    1400    14560  successful   \n",
       "2   Function-based leadingedge pricing structure  108400   142523  successful   \n",
       "3  Vision-oriented fresh-thinking conglomeration    4200     2477      failed   \n",
       "4                      Proactive foreground core    7600     5265      failed   \n",
       "\n",
       "   backers_count country currency  launched_at    deadline  staff_pick  \\\n",
       "0              0      CA      CAD   1581573600  1614578400       False   \n",
       "1            158      US      USD   1611554400  1621918800       False   \n",
       "2           1425      AU      AUD   1608184800  1640844000       False   \n",
       "3             24      US      USD   1634792400  1642399200       False   \n",
       "4             53      US      USD   1608530400  1629694800       False   \n",
       "\n",
       "   spotlight category & sub-category  \n",
       "0      False        food/food trucks  \n",
       "1       True              music/rock  \n",
       "2      False          technology/web  \n",
       "3      False              music/rock  \n",
       "4      False           theater/plays  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the data into a Pandas DataFrame\n",
    "crowdfunding_info_df = pd.read_excel('Resources/crowdfunding.xlsx')\n",
    "crowdfunding_info_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 15 columns):\n",
      " #   Column                   Non-Null Count  Dtype \n",
      "---  ------                   --------------  ----- \n",
      " 0   cf_id                    1000 non-null   int64 \n",
      " 1   contact_id               1000 non-null   int64 \n",
      " 2   company_name             1000 non-null   object\n",
      " 3   blurb                    1000 non-null   object\n",
      " 4   goal                     1000 non-null   int64 \n",
      " 5   pledged                  1000 non-null   int64 \n",
      " 6   outcome                  1000 non-null   object\n",
      " 7   backers_count            1000 non-null   int64 \n",
      " 8   country                  1000 non-null   object\n",
      " 9   currency                 1000 non-null   object\n",
      " 10  launched_at              1000 non-null   int64 \n",
      " 11  deadline                 1000 non-null   int64 \n",
      " 12  staff_pick               1000 non-null   bool  \n",
      " 13  spotlight                1000 non-null   bool  \n",
      " 14  category & sub-category  1000 non-null   object\n",
      "dtypes: bool(2), int64(7), object(6)\n",
      "memory usage: 103.6+ KB\n"
     ]
    }
   ],
   "source": [
    "# Get a brief summary of the crowdfunding_info DataFrame.\n",
    "crowdfunding_info_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cf_id', 'contact_id', 'company_name', 'blurb', 'goal', 'pledged',\n",
       "       'outcome', 'backers_count', 'country', 'currency', 'launched_at',\n",
       "       'deadline', 'staff_pick', 'spotlight', 'category & sub-category'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the crowdfunding_info_df columns.\n",
    "crowdfunding_info_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>blurb</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_at</th>\n",
       "      <th>deadline</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>1581573600</td>\n",
       "      <td>1614578400</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400</td>\n",
       "      <td>14560</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1611554400</td>\n",
       "      <td>1621918800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400</td>\n",
       "      <td>142523</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>1608184800</td>\n",
       "      <td>1640844000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200</td>\n",
       "      <td>2477</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634792400</td>\n",
       "      <td>1642399200</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600</td>\n",
       "      <td>5265</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1608530400</td>\n",
       "      <td>1629694800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>2986</td>\n",
       "      <td>3684</td>\n",
       "      <td>Manning-Hamilton</td>\n",
       "      <td>Vision-oriented scalable definition</td>\n",
       "      <td>97300</td>\n",
       "      <td>153216</td>\n",
       "      <td>successful</td>\n",
       "      <td>2043</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1609221600</td>\n",
       "      <td>1622350800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>2031</td>\n",
       "      <td>5784</td>\n",
       "      <td>Butler LLC</td>\n",
       "      <td>Future-proofed upward-trending migration</td>\n",
       "      <td>6600</td>\n",
       "      <td>4814</td>\n",
       "      <td>failed</td>\n",
       "      <td>112</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634274000</td>\n",
       "      <td>1638252000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>1627</td>\n",
       "      <td>1498</td>\n",
       "      <td>Ball LLC</td>\n",
       "      <td>Right-sized full-range throughput</td>\n",
       "      <td>7600</td>\n",
       "      <td>4603</td>\n",
       "      <td>canceled</td>\n",
       "      <td>139</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1636174800</td>\n",
       "      <td>1639116000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>2175</td>\n",
       "      <td>6073</td>\n",
       "      <td>Taylor, Santiago and Flores</td>\n",
       "      <td>Polarized composite customer loyalty</td>\n",
       "      <td>66600</td>\n",
       "      <td>37823</td>\n",
       "      <td>failed</td>\n",
       "      <td>374</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1602133200</td>\n",
       "      <td>1618117200</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/indie rock</td>\n",
       "      <td>music</td>\n",
       "      <td>indie rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1788</td>\n",
       "      <td>4939</td>\n",
       "      <td>Hernandez, Norton and Kelley</td>\n",
       "      <td>Expanded eco-centric policy</td>\n",
       "      <td>111100</td>\n",
       "      <td>62819</td>\n",
       "      <td>canceled</td>\n",
       "      <td>1122</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1609308000</td>\n",
       "      <td>1629262800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     cf_id  contact_id                  company_name  \\\n",
       "0      147        4661    Baldwin, Riley and Jackson   \n",
       "1     1621        3765                      Odom Inc   \n",
       "2     1812        4187    Melton, Robinson and Fritz   \n",
       "3     2156        4941   Mcdonald, Gonzalez and Ross   \n",
       "4     1365        2199                 Larson-Little   \n",
       "..     ...         ...                           ...   \n",
       "995   2986        3684              Manning-Hamilton   \n",
       "996   2031        5784                    Butler LLC   \n",
       "997   1627        1498                      Ball LLC   \n",
       "998   2175        6073   Taylor, Santiago and Flores   \n",
       "999   1788        4939  Hernandez, Norton and Kelley   \n",
       "\n",
       "                                             blurb    goal  pledged  \\\n",
       "0             Pre-emptive tertiary standardization     100        0   \n",
       "1                 Managed bottom-line architecture    1400    14560   \n",
       "2     Function-based leadingedge pricing structure  108400   142523   \n",
       "3    Vision-oriented fresh-thinking conglomeration    4200     2477   \n",
       "4                        Proactive foreground core    7600     5265   \n",
       "..                                             ...     ...      ...   \n",
       "995            Vision-oriented scalable definition   97300   153216   \n",
       "996       Future-proofed upward-trending migration    6600     4814   \n",
       "997              Right-sized full-range throughput    7600     4603   \n",
       "998           Polarized composite customer loyalty   66600    37823   \n",
       "999                    Expanded eco-centric policy  111100    62819   \n",
       "\n",
       "        outcome  backers_count country currency  launched_at    deadline  \\\n",
       "0        failed              0      CA      CAD   1581573600  1614578400   \n",
       "1    successful            158      US      USD   1611554400  1621918800   \n",
       "2    successful           1425      AU      AUD   1608184800  1640844000   \n",
       "3        failed             24      US      USD   1634792400  1642399200   \n",
       "4        failed             53      US      USD   1608530400  1629694800   \n",
       "..          ...            ...     ...      ...          ...         ...   \n",
       "995  successful           2043      US      USD   1609221600  1622350800   \n",
       "996      failed            112      US      USD   1634274000  1638252000   \n",
       "997    canceled            139      IT      EUR   1636174800  1639116000   \n",
       "998      failed            374      US      USD   1602133200  1618117200   \n",
       "999    canceled           1122      US      USD   1609308000  1629262800   \n",
       "\n",
       "     staff_pick  spotlight category & sub-category    category sub-category  \n",
       "0         False      False        food/food trucks        food  food trucks  \n",
       "1         False       True              music/rock       music         rock  \n",
       "2         False      False          technology/web  technology          web  \n",
       "3         False      False              music/rock       music         rock  \n",
       "4         False      False           theater/plays     theater        plays  \n",
       "..          ...        ...                     ...         ...          ...  \n",
       "995       False       True        food/food trucks        food  food trucks  \n",
       "996       False      False           theater/plays     theater        plays  \n",
       "997       False      False           theater/plays     theater        plays  \n",
       "998       False       True        music/indie rock       music   indie rock  \n",
       "999       False      False        food/food trucks        food  food trucks  \n",
       "\n",
       "[1000 rows x 17 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assign the category and subcategory values to category and subcategory columns.\n",
    "crowdfunding_info_df[['category', 'sub-category']] = crowdfunding_info_df['category & sub-category'].str.split(\"/\", expand=True)\n",
    "crowdfunding_info_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['food', 'music', 'technology', 'theater', 'film & video', 'publishing', 'games', 'photography', 'journalism']\n",
      "['food trucks', 'rock', 'web', 'plays', 'documentary', 'electric music', 'drama', 'indie rock', 'wearables', 'nonfiction', 'animation', 'video games', 'shorts', 'fiction', 'photography books', 'radio & podcasts', 'metal', 'jazz', 'translations', 'television', 'mobile games', 'world music', 'science fiction', 'audio']\n"
     ]
    }
   ],
   "source": [
    "# Get the unique categories and subcategories in separate lists.\n",
    "categories = [x for x in crowdfunding_info_df['category'].unique()]\n",
    "subcategories = [x for x in crowdfunding_info_df['sub-category'].unique()]\n",
    "print(categories)\n",
    "print(subcategories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n",
      "24\n"
     ]
    }
   ],
   "source": [
    "# Get the number of distinct values in the categories and subcategories lists.\n",
    "print(len(categories))\n",
    "print(len(subcategories))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1 2 3 4 5 6 7 8 9]\n",
      "[ 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24]\n"
     ]
    }
   ],
   "source": [
    "# Create numpy arrays from 1-9 for the categories and 1-24 for the subcategories.\n",
    "category_ids = np.arange(1, 10)\n",
    "subcategory_ids = np.arange(1, 25)\n",
    "\n",
    "print(category_ids)\n",
    "print(subcategory_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['cat1', 'cat2', 'cat3', 'cat4', 'cat5', 'cat6', 'cat7', 'cat8', 'cat9']\n",
      "['subcat1', 'subcat2', 'subcat3', 'subcat4', 'subcat5', 'subcat6', 'subcat7', 'subcat8', 'subcat9', 'subcat10', 'subcat11', 'subcat12', 'subcat13', 'subcat14', 'subcat15', 'subcat16', 'subcat17', 'subcat18', 'subcat19', 'subcat20', 'subcat21', 'subcat22', 'subcat23', 'subcat24']\n"
     ]
    }
   ],
   "source": [
    "# Use a list comprehension to add \"cat\" to each category_id. \n",
    "cat_ids = [f\"cat{x}\" for x in category_ids]\n",
    "\n",
    "# Use a list comprehension to add \"subcat\" to each subcategory_id.    \n",
    "scat_ids = [f\"subcat{y}\" for y in subcategory_ids]\n",
    "    \n",
    "print(cat_ids)\n",
    "print(scat_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a category DataFrame with the category_id array as the category_id and categories list as the category name.\n",
    "category_df = pd.DataFrame({'category_id': cat_ids,'category': categories})\n",
    "\n",
    "# Create a category DataFrame with the subcategory_id array as the subcategory_id and subcategories list as the subcategory name. \n",
    "subcategory_df = pd.DataFrame({'subcategory_id': scat_ids,\\\n",
    "                               'subcategory': subcategories})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cat1</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cat2</td>\n",
       "      <td>music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cat3</td>\n",
       "      <td>technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cat4</td>\n",
       "      <td>theater</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cat5</td>\n",
       "      <td>film &amp; video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>cat6</td>\n",
       "      <td>publishing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>cat7</td>\n",
       "      <td>games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>cat8</td>\n",
       "      <td>photography</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>cat9</td>\n",
       "      <td>journalism</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category_id      category\n",
       "0        cat1          food\n",
       "1        cat2         music\n",
       "2        cat3    technology\n",
       "3        cat4       theater\n",
       "4        cat5  film & video\n",
       "5        cat6    publishing\n",
       "6        cat7         games\n",
       "7        cat8   photography\n",
       "8        cat9    journalism"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show new category DataFrame\n",
    "category_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>subcategory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>subcat1</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>subcat2</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>subcat3</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>subcat4</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>subcat5</td>\n",
       "      <td>documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>subcat6</td>\n",
       "      <td>electric music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>subcat7</td>\n",
       "      <td>drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>subcat8</td>\n",
       "      <td>indie rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>subcat9</td>\n",
       "      <td>wearables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>subcat10</td>\n",
       "      <td>nonfiction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>subcat11</td>\n",
       "      <td>animation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>subcat12</td>\n",
       "      <td>video games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>subcat13</td>\n",
       "      <td>shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>subcat14</td>\n",
       "      <td>fiction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>subcat15</td>\n",
       "      <td>photography books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>subcat16</td>\n",
       "      <td>radio &amp; podcasts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>subcat17</td>\n",
       "      <td>metal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>subcat18</td>\n",
       "      <td>jazz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>subcat19</td>\n",
       "      <td>translations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>subcat20</td>\n",
       "      <td>television</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>subcat21</td>\n",
       "      <td>mobile games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>subcat22</td>\n",
       "      <td>world music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>subcat23</td>\n",
       "      <td>science fiction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>subcat24</td>\n",
       "      <td>audio</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subcategory_id        subcategory\n",
       "0         subcat1        food trucks\n",
       "1         subcat2               rock\n",
       "2         subcat3                web\n",
       "3         subcat4              plays\n",
       "4         subcat5        documentary\n",
       "5         subcat6     electric music\n",
       "6         subcat7              drama\n",
       "7         subcat8         indie rock\n",
       "8         subcat9          wearables\n",
       "9        subcat10         nonfiction\n",
       "10       subcat11          animation\n",
       "11       subcat12        video games\n",
       "12       subcat13             shorts\n",
       "13       subcat14            fiction\n",
       "14       subcat15  photography books\n",
       "15       subcat16   radio & podcasts\n",
       "16       subcat17              metal\n",
       "17       subcat18               jazz\n",
       "18       subcat19       translations\n",
       "19       subcat20         television\n",
       "20       subcat21       mobile games\n",
       "21       subcat22        world music\n",
       "22       subcat23    science fiction\n",
       "23       subcat24              audio"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show new subcategory DataFrame\n",
    "subcategory_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export categories_df and subcategories_df as CSV files.\n",
    "category_df.to_csv(\"Resources/category.csv\", index=False)\n",
    "\n",
    "subcategory_df.to_csv(\"Resources/subcategory.csv\", index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Campaign DataFrame\n",
    "----\n",
    "**Create a Campaign DataFrame that has the following columns:**\n",
    "- The \"cf_id\" column.\n",
    "- The \"contact_id\" column.\n",
    "- The “company_name” column.\n",
    "- The \"blurb\" column is renamed as \"description.\"\n",
    "- The \"goal\" column.\n",
    "- The \"goal\" column is converted to a `float` datatype.\n",
    "- The \"pledged\" column is converted to a `float` datatype. \n",
    "- The \"backers_count\" column. \n",
    "- The \"country\" column.\n",
    "- The \"currency\" column.\n",
    "- The \"launched_at\" column is renamed as \"launch_date\" and converted to a datetime format. \n",
    "- The \"deadline\" column is renamed as \"end_date\" and converted to a datetime format.\n",
    "- The \"category_id\" with the unique number matching the “category_id” from the category DataFrame. \n",
    "- The \"subcategory_id\" with the unique number matching the “subcategory_id” from the subcategory DataFrame.\n",
    "- And, create a column that contains the unique four-digit contact ID number from the `contact.xlsx` file.\n",
    " \n",
    "\n",
    "Then export the DataFrame as a `campaign.csv` CSV file.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>blurb</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_at</th>\n",
       "      <th>deadline</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>1581573600</td>\n",
       "      <td>1614578400</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400</td>\n",
       "      <td>14560</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1611554400</td>\n",
       "      <td>1621918800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400</td>\n",
       "      <td>142523</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>1608184800</td>\n",
       "      <td>1640844000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200</td>\n",
       "      <td>2477</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634792400</td>\n",
       "      <td>1642399200</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600</td>\n",
       "      <td>5265</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1608530400</td>\n",
       "      <td>1629694800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cf_id  contact_id                 company_name  \\\n",
       "0    147        4661   Baldwin, Riley and Jackson   \n",
       "1   1621        3765                     Odom Inc   \n",
       "2   1812        4187   Melton, Robinson and Fritz   \n",
       "3   2156        4941  Mcdonald, Gonzalez and Ross   \n",
       "4   1365        2199                Larson-Little   \n",
       "\n",
       "                                           blurb    goal  pledged     outcome  \\\n",
       "0           Pre-emptive tertiary standardization     100        0      failed   \n",
       "1               Managed bottom-line architecture    1400    14560  successful   \n",
       "2   Function-based leadingedge pricing structure  108400   142523  successful   \n",
       "3  Vision-oriented fresh-thinking conglomeration    4200     2477      failed   \n",
       "4                      Proactive foreground core    7600     5265      failed   \n",
       "\n",
       "   backers_count country currency  launched_at    deadline  staff_pick  \\\n",
       "0              0      CA      CAD   1581573600  1614578400       False   \n",
       "1            158      US      USD   1611554400  1621918800       False   \n",
       "2           1425      AU      AUD   1608184800  1640844000       False   \n",
       "3             24      US      USD   1634792400  1642399200       False   \n",
       "4             53      US      USD   1608530400  1629694800       False   \n",
       "\n",
       "   spotlight category & sub-category    category sub-category  \n",
       "0      False        food/food trucks        food  food trucks  \n",
       "1       True              music/rock       music         rock  \n",
       "2      False          technology/web  technology          web  \n",
       "3      False              music/rock       music         rock  \n",
       "4      False           theater/plays     theater        plays  "
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a copy of the crowdfunding_info_df DataFrame name campaign_df. \n",
    "campaign_df = crowdfunding_info_df.copy()\n",
    "campaign_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>description</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>1581573600</td>\n",
       "      <td>1614578400</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400</td>\n",
       "      <td>14560</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1611554400</td>\n",
       "      <td>1621918800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400</td>\n",
       "      <td>142523</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>1608184800</td>\n",
       "      <td>1640844000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200</td>\n",
       "      <td>2477</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634792400</td>\n",
       "      <td>1642399200</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600</td>\n",
       "      <td>5265</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1608530400</td>\n",
       "      <td>1629694800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cf_id  contact_id                 company_name  \\\n",
       "0    147        4661   Baldwin, Riley and Jackson   \n",
       "1   1621        3765                     Odom Inc   \n",
       "2   1812        4187   Melton, Robinson and Fritz   \n",
       "3   2156        4941  Mcdonald, Gonzalez and Ross   \n",
       "4   1365        2199                Larson-Little   \n",
       "\n",
       "                                     description    goal  pledged     outcome  \\\n",
       "0           Pre-emptive tertiary standardization     100        0      failed   \n",
       "1               Managed bottom-line architecture    1400    14560  successful   \n",
       "2   Function-based leadingedge pricing structure  108400   142523  successful   \n",
       "3  Vision-oriented fresh-thinking conglomeration    4200     2477      failed   \n",
       "4                      Proactive foreground core    7600     5265      failed   \n",
       "\n",
       "   backers_count country currency  launched_date    end_date  staff_pick  \\\n",
       "0              0      CA      CAD     1581573600  1614578400       False   \n",
       "1            158      US      USD     1611554400  1621918800       False   \n",
       "2           1425      AU      AUD     1608184800  1640844000       False   \n",
       "3             24      US      USD     1634792400  1642399200       False   \n",
       "4             53      US      USD     1608530400  1629694800       False   \n",
       "\n",
       "   spotlight category & sub-category    category sub-category  \n",
       "0      False        food/food trucks        food  food trucks  \n",
       "1       True              music/rock       music         rock  \n",
       "2      False          technology/web  technology          web  \n",
       "3      False              music/rock       music         rock  \n",
       "4      False           theater/plays     theater        plays  "
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the blurb, launched_at, and deadline columns.\n",
    "campaign_df.rename(columns={\"blurb\":\"description\",\\\n",
    "            \"launched_at\":\"launched_date\", \"deadline\":\"end_date\"},inplace=True)\n",
    "campaign_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>description</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>1581573600</td>\n",
       "      <td>1614578400</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>14560.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1611554400</td>\n",
       "      <td>1621918800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>142523.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>1608184800</td>\n",
       "      <td>1640844000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>2477.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634792400</td>\n",
       "      <td>1642399200</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>5265.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1608530400</td>\n",
       "      <td>1629694800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>2986</td>\n",
       "      <td>3684</td>\n",
       "      <td>Manning-Hamilton</td>\n",
       "      <td>Vision-oriented scalable definition</td>\n",
       "      <td>97300.0</td>\n",
       "      <td>153216.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>2043</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1609221600</td>\n",
       "      <td>1622350800</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>2031</td>\n",
       "      <td>5784</td>\n",
       "      <td>Butler LLC</td>\n",
       "      <td>Future-proofed upward-trending migration</td>\n",
       "      <td>6600.0</td>\n",
       "      <td>4814.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>112</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1634274000</td>\n",
       "      <td>1638252000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>1627</td>\n",
       "      <td>1498</td>\n",
       "      <td>Ball LLC</td>\n",
       "      <td>Right-sized full-range throughput</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>4603.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>139</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1636174800</td>\n",
       "      <td>1639116000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>2175</td>\n",
       "      <td>6073</td>\n",
       "      <td>Taylor, Santiago and Flores</td>\n",
       "      <td>Polarized composite customer loyalty</td>\n",
       "      <td>66600.0</td>\n",
       "      <td>37823.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>374</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1602133200</td>\n",
       "      <td>1618117200</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/indie rock</td>\n",
       "      <td>music</td>\n",
       "      <td>indie rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1788</td>\n",
       "      <td>4939</td>\n",
       "      <td>Hernandez, Norton and Kelley</td>\n",
       "      <td>Expanded eco-centric policy</td>\n",
       "      <td>111100.0</td>\n",
       "      <td>62819.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>1122</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>1609308000</td>\n",
       "      <td>1629262800</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     cf_id  contact_id                  company_name  \\\n",
       "0      147        4661    Baldwin, Riley and Jackson   \n",
       "1     1621        3765                      Odom Inc   \n",
       "2     1812        4187    Melton, Robinson and Fritz   \n",
       "3     2156        4941   Mcdonald, Gonzalez and Ross   \n",
       "4     1365        2199                 Larson-Little   \n",
       "..     ...         ...                           ...   \n",
       "995   2986        3684              Manning-Hamilton   \n",
       "996   2031        5784                    Butler LLC   \n",
       "997   1627        1498                      Ball LLC   \n",
       "998   2175        6073   Taylor, Santiago and Flores   \n",
       "999   1788        4939  Hernandez, Norton and Kelley   \n",
       "\n",
       "                                       description      goal   pledged  \\\n",
       "0             Pre-emptive tertiary standardization     100.0       0.0   \n",
       "1                 Managed bottom-line architecture    1400.0   14560.0   \n",
       "2     Function-based leadingedge pricing structure  108400.0  142523.0   \n",
       "3    Vision-oriented fresh-thinking conglomeration    4200.0    2477.0   \n",
       "4                        Proactive foreground core    7600.0    5265.0   \n",
       "..                                             ...       ...       ...   \n",
       "995            Vision-oriented scalable definition   97300.0  153216.0   \n",
       "996       Future-proofed upward-trending migration    6600.0    4814.0   \n",
       "997              Right-sized full-range throughput    7600.0    4603.0   \n",
       "998           Polarized composite customer loyalty   66600.0   37823.0   \n",
       "999                    Expanded eco-centric policy  111100.0   62819.0   \n",
       "\n",
       "        outcome  backers_count country currency  launched_date    end_date  \\\n",
       "0        failed              0      CA      CAD     1581573600  1614578400   \n",
       "1    successful            158      US      USD     1611554400  1621918800   \n",
       "2    successful           1425      AU      AUD     1608184800  1640844000   \n",
       "3        failed             24      US      USD     1634792400  1642399200   \n",
       "4        failed             53      US      USD     1608530400  1629694800   \n",
       "..          ...            ...     ...      ...            ...         ...   \n",
       "995  successful           2043      US      USD     1609221600  1622350800   \n",
       "996      failed            112      US      USD     1634274000  1638252000   \n",
       "997    canceled            139      IT      EUR     1636174800  1639116000   \n",
       "998      failed            374      US      USD     1602133200  1618117200   \n",
       "999    canceled           1122      US      USD     1609308000  1629262800   \n",
       "\n",
       "     staff_pick  spotlight category & sub-category    category sub-category  \n",
       "0         False      False        food/food trucks        food  food trucks  \n",
       "1         False       True              music/rock       music         rock  \n",
       "2         False      False          technology/web  technology          web  \n",
       "3         False      False              music/rock       music         rock  \n",
       "4         False      False           theater/plays     theater        plays  \n",
       "..          ...        ...                     ...         ...          ...  \n",
       "995       False       True        food/food trucks        food  food trucks  \n",
       "996       False      False           theater/plays     theater        plays  \n",
       "997       False      False           theater/plays     theater        plays  \n",
       "998       False       True        music/indie rock       music   indie rock  \n",
       "999       False      False        food/food trucks        food  food trucks  \n",
       "\n",
       "[1000 rows x 17 columns]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the goal and pledged columns to a `float` data type.\n",
    "campaign_df[['goal','pledged']] = campaign_df[['goal','pledged']]\\\n",
    "                                 .astype('float')\n",
    "campaign_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cf_id                        int64\n",
       "contact_id                   int64\n",
       "company_name                object\n",
       "description                 object\n",
       "goal                       float64\n",
       "pledged                    float64\n",
       "outcome                     object\n",
       "backers_count                int64\n",
       "country                     object\n",
       "currency                    object\n",
       "launched_date                int64\n",
       "end_date                     int64\n",
       "staff_pick                    bool\n",
       "spotlight                     bool\n",
       "category & sub-category     object\n",
       "category                    object\n",
       "sub-category                object\n",
       "dtype: object"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the datatypes\n",
    "campaign_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>description</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>sub-category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>14560.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-01-25</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>142523.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>2020-12-17</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>2477.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>2022-01-17</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>5265.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>2021-08-23</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cf_id  contact_id                 company_name  \\\n",
       "0    147        4661   Baldwin, Riley and Jackson   \n",
       "1   1621        3765                     Odom Inc   \n",
       "2   1812        4187   Melton, Robinson and Fritz   \n",
       "3   2156        4941  Mcdonald, Gonzalez and Ross   \n",
       "4   1365        2199                Larson-Little   \n",
       "\n",
       "                                     description      goal   pledged  \\\n",
       "0           Pre-emptive tertiary standardization     100.0       0.0   \n",
       "1               Managed bottom-line architecture    1400.0   14560.0   \n",
       "2   Function-based leadingedge pricing structure  108400.0  142523.0   \n",
       "3  Vision-oriented fresh-thinking conglomeration    4200.0    2477.0   \n",
       "4                      Proactive foreground core    7600.0    5265.0   \n",
       "\n",
       "      outcome  backers_count country currency launched_date    end_date  \\\n",
       "0      failed              0      CA      CAD    2020-02-13  2021-03-01   \n",
       "1  successful            158      US      USD    2021-01-25  2021-05-25   \n",
       "2  successful           1425      AU      AUD    2020-12-17  2021-12-30   \n",
       "3      failed             24      US      USD    2021-10-21  2022-01-17   \n",
       "4      failed             53      US      USD    2020-12-21  2021-08-23   \n",
       "\n",
       "   staff_pick  spotlight category & sub-category    category sub-category  \n",
       "0       False      False        food/food trucks        food  food trucks  \n",
       "1       False       True              music/rock       music         rock  \n",
       "2       False      False          technology/web  technology          web  \n",
       "3       False      False              music/rock       music         rock  \n",
       "4       False      False           theater/plays     theater        plays  "
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format the launched_date and end_date columns to datetime format\n",
    "from datetime import datetime as dt\n",
    "campaign_df[\"launched_date\"] = pd.to_datetime(campaign_df[\"launched_date\"],\\\n",
    "                                              unit='s').dt.strftime('%Y-%m-%d') \n",
    "campaign_df[\"end_date\"] = pd.to_datetime(campaign_df[\"end_date\"],\\\n",
    "                                         unit='s').dt.strftime('%Y-%m-%d')\n",
    "campaign_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename campaign_df column to match the subcategory column name in category_df\n",
    "campaign_df.rename(columns={\"sub-category\":\"subcategory\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>description</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>990</th>\n",
       "      <td>1561</td>\n",
       "      <td>5500</td>\n",
       "      <td>Ortiz-Roberts</td>\n",
       "      <td>Devolved foreground customer loyalty</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>6839.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>64</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-06-09</td>\n",
       "      <td>2021-06-18</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>film &amp; video/drama</td>\n",
       "      <td>film &amp; video</td>\n",
       "      <td>drama</td>\n",
       "      <td>cat5</td>\n",
       "      <td>subcat7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991</th>\n",
       "      <td>2632</td>\n",
       "      <td>3494</td>\n",
       "      <td>Ramirez LLC</td>\n",
       "      <td>Reduced reciprocal focus group</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>11091.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>241</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-09</td>\n",
       "      <td>2021-05-26</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>439</td>\n",
       "      <td>3924</td>\n",
       "      <td>Morrow Inc</td>\n",
       "      <td>Networked global migration</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>13223.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>132</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>film &amp; video/drama</td>\n",
       "      <td>film &amp; video</td>\n",
       "      <td>drama</td>\n",
       "      <td>cat5</td>\n",
       "      <td>subcat7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>461</td>\n",
       "      <td>3521</td>\n",
       "      <td>Erickson-Rogers</td>\n",
       "      <td>De-engineered even-keeled definition</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>7608.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>75</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2021-07-03</td>\n",
       "      <td>2021-07-08</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>photography/photography books</td>\n",
       "      <td>photography</td>\n",
       "      <td>photography books</td>\n",
       "      <td>cat8</td>\n",
       "      <td>subcat15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>1419</td>\n",
       "      <td>5262</td>\n",
       "      <td>Leach, Rich and Price</td>\n",
       "      <td>Implemented bi-directional flexibility</td>\n",
       "      <td>141100.0</td>\n",
       "      <td>74073.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>842</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-11-15</td>\n",
       "      <td>2021-12-07</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>publishing/translations</td>\n",
       "      <td>publishing</td>\n",
       "      <td>translations</td>\n",
       "      <td>cat6</td>\n",
       "      <td>subcat19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>2986</td>\n",
       "      <td>3684</td>\n",
       "      <td>Manning-Hamilton</td>\n",
       "      <td>Vision-oriented scalable definition</td>\n",
       "      <td>97300.0</td>\n",
       "      <td>153216.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>2043</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>2021-05-30</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>2031</td>\n",
       "      <td>5784</td>\n",
       "      <td>Butler LLC</td>\n",
       "      <td>Future-proofed upward-trending migration</td>\n",
       "      <td>6600.0</td>\n",
       "      <td>4814.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>112</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-15</td>\n",
       "      <td>2021-11-30</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>1627</td>\n",
       "      <td>1498</td>\n",
       "      <td>Ball LLC</td>\n",
       "      <td>Right-sized full-range throughput</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>4603.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>139</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2021-11-06</td>\n",
       "      <td>2021-12-10</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>2175</td>\n",
       "      <td>6073</td>\n",
       "      <td>Taylor, Santiago and Flores</td>\n",
       "      <td>Polarized composite customer loyalty</td>\n",
       "      <td>66600.0</td>\n",
       "      <td>37823.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>374</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>2021-04-11</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/indie rock</td>\n",
       "      <td>music</td>\n",
       "      <td>indie rock</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1788</td>\n",
       "      <td>4939</td>\n",
       "      <td>Hernandez, Norton and Kelley</td>\n",
       "      <td>Expanded eco-centric policy</td>\n",
       "      <td>111100.0</td>\n",
       "      <td>62819.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>1122</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2021-08-18</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cf_id  contact_id                  company_name  \\\n",
       "990   1561        5500                 Ortiz-Roberts   \n",
       "991   2632        3494                   Ramirez LLC   \n",
       "992    439        3924                    Morrow Inc   \n",
       "993    461        3521               Erickson-Rogers   \n",
       "994   1419        5262         Leach, Rich and Price   \n",
       "995   2986        3684              Manning-Hamilton   \n",
       "996   2031        5784                    Butler LLC   \n",
       "997   1627        1498                      Ball LLC   \n",
       "998   2175        6073   Taylor, Santiago and Flores   \n",
       "999   1788        4939  Hernandez, Norton and Kelley   \n",
       "\n",
       "                                  description      goal   pledged     outcome  \\\n",
       "990      Devolved foreground customer loyalty    7800.0    6839.0      failed   \n",
       "991            Reduced reciprocal focus group    9800.0   11091.0  successful   \n",
       "992                Networked global migration    3100.0   13223.0  successful   \n",
       "993      De-engineered even-keeled definition    9800.0    7608.0    canceled   \n",
       "994    Implemented bi-directional flexibility  141100.0   74073.0      failed   \n",
       "995       Vision-oriented scalable definition   97300.0  153216.0  successful   \n",
       "996  Future-proofed upward-trending migration    6600.0    4814.0      failed   \n",
       "997         Right-sized full-range throughput    7600.0    4603.0    canceled   \n",
       "998      Polarized composite customer loyalty   66600.0   37823.0      failed   \n",
       "999               Expanded eco-centric policy  111100.0   62819.0    canceled   \n",
       "\n",
       "     backers_count country currency launched_date    end_date  staff_pick  \\\n",
       "990             64      US      USD    2021-06-09  2021-06-18       False   \n",
       "991            241      US      USD    2020-12-09  2021-05-26       False   \n",
       "992            132      US      USD    2020-06-14  2021-02-09       False   \n",
       "993             75      IT      EUR    2021-07-03  2021-07-08       False   \n",
       "994            842      US      USD    2021-11-15  2021-12-07       False   \n",
       "995           2043      US      USD    2020-12-29  2021-05-30       False   \n",
       "996            112      US      USD    2021-10-15  2021-11-30       False   \n",
       "997            139      IT      EUR    2021-11-06  2021-12-10       False   \n",
       "998            374      US      USD    2020-10-08  2021-04-11       False   \n",
       "999           1122      US      USD    2020-12-30  2021-08-18       False   \n",
       "\n",
       "     spotlight        category & sub-category      category  \\\n",
       "990       True             film & video/drama  film & video   \n",
       "991       True                     music/rock         music   \n",
       "992       True             film & video/drama  film & video   \n",
       "993       True  photography/photography books   photography   \n",
       "994       True        publishing/translations    publishing   \n",
       "995       True               food/food trucks          food   \n",
       "996      False                  theater/plays       theater   \n",
       "997      False                  theater/plays       theater   \n",
       "998       True               music/indie rock         music   \n",
       "999      False               food/food trucks          food   \n",
       "\n",
       "           subcategory category_id subcategory_id  \n",
       "990              drama        cat5        subcat7  \n",
       "991               rock        cat2        subcat2  \n",
       "992              drama        cat5        subcat7  \n",
       "993  photography books        cat8       subcat15  \n",
       "994       translations        cat6       subcat19  \n",
       "995        food trucks        cat1        subcat1  \n",
       "996              plays        cat4        subcat4  \n",
       "997              plays        cat4        subcat4  \n",
       "998         indie rock        cat2        subcat8  \n",
       "999        food trucks        cat1        subcat1  "
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the campaign_df with the category_df on the \"category\" column and \n",
    "# the subcategory_df on the \"subcategory\" column.\n",
    "campaign_merged_df = campaign_df.merge(category_df, on='category', how='left').merge(subcategory_df, on='subcategory', how='left')\n",
    "campaign_merged_df.tail(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cf_id</th>\n",
       "      <th>contact_id</th>\n",
       "      <th>company_name</th>\n",
       "      <th>description</th>\n",
       "      <th>goal</th>\n",
       "      <th>pledged</th>\n",
       "      <th>outcome</th>\n",
       "      <th>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launched_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>14560.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-01-25</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>142523.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>2020-12-17</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>cat3</td>\n",
       "      <td>subcat3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>2477.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>2022-01-17</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>5265.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>2021-08-23</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cf_id  contact_id                 company_name  \\\n",
       "0    147        4661   Baldwin, Riley and Jackson   \n",
       "1   1621        3765                     Odom Inc   \n",
       "2   1812        4187   Melton, Robinson and Fritz   \n",
       "3   2156        4941  Mcdonald, Gonzalez and Ross   \n",
       "4   1365        2199                Larson-Little   \n",
       "\n",
       "                                     description      goal   pledged  \\\n",
       "0           Pre-emptive tertiary standardization     100.0       0.0   \n",
       "1               Managed bottom-line architecture    1400.0   14560.0   \n",
       "2   Function-based leadingedge pricing structure  108400.0  142523.0   \n",
       "3  Vision-oriented fresh-thinking conglomeration    4200.0    2477.0   \n",
       "4                      Proactive foreground core    7600.0    5265.0   \n",
       "\n",
       "      outcome  backers_count country currency launched_date    end_date  \\\n",
       "0      failed              0      CA      CAD    2020-02-13  2021-03-01   \n",
       "1  successful            158      US      USD    2021-01-25  2021-05-25   \n",
       "2  successful           1425      AU      AUD    2020-12-17  2021-12-30   \n",
       "3      failed             24      US      USD    2021-10-21  2022-01-17   \n",
       "4      failed             53      US      USD    2020-12-21  2021-08-23   \n",
       "\n",
       "  category_id subcategory_id  \n",
       "0        cat1        subcat1  \n",
       "1        cat2        subcat2  \n",
       "2        cat3        subcat3  \n",
       "3        cat2        subcat2  \n",
       "4        cat4        subcat4  "
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unwanted columns\n",
    "campaign_cleaned=campaign_merged_df.drop(['staff_pick','spotlight',\\\n",
    "                'category & sub-category','category','subcategory'],axis =1)\n",
    "campaign_cleaned.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "campaign_cleaned.to_csv(\"Resources/campaign.csv\", index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the contacts.xlsx Data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                contact_info\n",
       "0   {\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}\n",
       "1          {\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}\n",
       "2            {\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}\n",
       "3  {\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}\n",
       "4        {\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the data into a Pandas DataFrame. Use the `header=3` parameter when reading in the data.\n",
    "contact_info_df = pd.read_excel('Resources/contacts.xlsx', header=3)\n",
    "contact_info_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the Contacts DataFrame \n",
    "---\n",
    "**Create a Contacts DataFrame that has the following columns:**\n",
    "- A column named \"contact_id\"  that contains the unique number of the contact person.\n",
    "- A column named \"first_name\" that contains the first name of the contact person.\n",
    "- A column named \"last_name\" that contains the first name of the contact person.\n",
    "- A column named \"email\" that contains the email address of the contact person\n",
    "\n",
    "Then export the DataFrame as a `contacts.csv` CSV file."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 1: Use Pandas to create the contacts DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[4661, 'Cecilia Velasco', 'cecilia.velasco@rodrigues.fr'], [3765, 'Mariana Ellis', 'mariana.ellis@rossi.org'], [4187, 'Sofie Woods', 'sofie.woods@riviere.com'], [4941, 'Jeanette Iannotti', 'jeanette.iannotti@yahoo.com'], [2199, 'Samuel Sorgatz', 'samuel.sorgatz@gmail.com'], [5650, 'Socorro Luna', 'socorro.luna@hotmail.com'], [5889, 'Carolina Murray', 'carolina.murray@knight.com'], [4842, 'Kayla Moon', 'kayla.moon@yahoo.de'], [3280, 'Ariadna Geisel', 'ariadna.geisel@rangel.com'], [5468, 'Danielle Ladeck', 'danielle.ladeck@scalfaro.net']]\n"
     ]
    }
   ],
   "source": [
    "# Iterate through the contact_info_df and convert each row to a dictionary.\n",
    "import json\n",
    "dict_values = []\n",
    "\n",
    "for i, row in contact_info_df.iterrows():\n",
    "    contact_info_dict = json.loads(row['contact_info'])\n",
    "    row_values = [v for k, v in contact_info_dict.items()]\n",
    "    dict_values.append(row_values)\n",
    "\n",
    "# Print out the list of values for each row.\n",
    "print(dict_values[0:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_id</th>\n",
       "      <th>name</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4661</td>\n",
       "      <td>Cecilia Velasco</td>\n",
       "      <td>cecilia.velasco@rodrigues.fr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3765</td>\n",
       "      <td>Mariana Ellis</td>\n",
       "      <td>mariana.ellis@rossi.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4187</td>\n",
       "      <td>Sofie Woods</td>\n",
       "      <td>sofie.woods@riviere.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4941</td>\n",
       "      <td>Jeanette Iannotti</td>\n",
       "      <td>jeanette.iannotti@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2199</td>\n",
       "      <td>Samuel Sorgatz</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id               name                         email\n",
       "0        4661    Cecilia Velasco  cecilia.velasco@rodrigues.fr\n",
       "1        3765      Mariana Ellis       mariana.ellis@rossi.org\n",
       "2        4187        Sofie Woods       sofie.woods@riviere.com\n",
       "3        4941  Jeanette Iannotti   jeanette.iannotti@yahoo.com\n",
       "4        2199     Samuel Sorgatz      samuel.sorgatz@gmail.com"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a contact_info DataFrame and add each list of values, i.e., each row \n",
    "# to the 'contact_id', 'name', 'email' columns.\n",
    "dictionary_col_names = []\n",
    "\n",
    "for i, row in contact_info_df.iterrows():\n",
    "    contact_info_dict = json.loads(row['contact_info'])\n",
    "    columns = [k for k,v in contact_info_dict.items()]\n",
    "    dictionary_col_names.append(columns)\n",
    "    \n",
    "# Print out the list of values for each row.\n",
    "\n",
    "contacts_df_clean = pd.DataFrame(dict_values, columns=dictionary_col_names[0])\n",
    "contacts_df_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 3 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   contact_id  1000 non-null   int64 \n",
      " 1   name        1000 non-null   object\n",
      " 2   email       1000 non-null   object\n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 23.6+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the datatypes.\n",
    "contacts_df_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_id</th>\n",
       "      <th>email</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4661</td>\n",
       "      <td>cecilia.velasco@rodrigues.fr</td>\n",
       "      <td>Cecilia</td>\n",
       "      <td>Velasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3765</td>\n",
       "      <td>mariana.ellis@rossi.org</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4187</td>\n",
       "      <td>sofie.woods@riviere.com</td>\n",
       "      <td>Sofie</td>\n",
       "      <td>Woods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4941</td>\n",
       "      <td>jeanette.iannotti@yahoo.com</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>Iannotti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2199</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "      <td>Samuel</td>\n",
       "      <td>Sorgatz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5650</td>\n",
       "      <td>socorro.luna@hotmail.com</td>\n",
       "      <td>Socorro</td>\n",
       "      <td>Luna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5889</td>\n",
       "      <td>carolina.murray@knight.com</td>\n",
       "      <td>Carolina</td>\n",
       "      <td>Murray</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4842</td>\n",
       "      <td>kayla.moon@yahoo.de</td>\n",
       "      <td>Kayla</td>\n",
       "      <td>Moon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3280</td>\n",
       "      <td>ariadna.geisel@rangel.com</td>\n",
       "      <td>Ariadna</td>\n",
       "      <td>Geisel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5468</td>\n",
       "      <td>danielle.ladeck@scalfaro.net</td>\n",
       "      <td>Danielle</td>\n",
       "      <td>Ladeck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id                         email first_name last_name\n",
       "0        4661  cecilia.velasco@rodrigues.fr    Cecilia   Velasco\n",
       "1        3765       mariana.ellis@rossi.org    Mariana     Ellis\n",
       "2        4187       sofie.woods@riviere.com      Sofie     Woods\n",
       "3        4941   jeanette.iannotti@yahoo.com   Jeanette  Iannotti\n",
       "4        2199      samuel.sorgatz@gmail.com     Samuel   Sorgatz\n",
       "5        5650      socorro.luna@hotmail.com    Socorro      Luna\n",
       "6        5889    carolina.murray@knight.com   Carolina    Murray\n",
       "7        4842           kayla.moon@yahoo.de      Kayla      Moon\n",
       "8        3280     ariadna.geisel@rangel.com    Ariadna    Geisel\n",
       "9        5468  danielle.ladeck@scalfaro.net   Danielle    Ladeck"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a \"first\"name\" and \"last_name\" column with the first and last names from the \"name\" column. \n",
    "contacts_df_clean['first_name'] = contacts_df_clean['name'].str.split().str[0]\n",
    "contacts_df_clean['last_name'] = contacts_df_clean['name'].str.split().str[-1]\n",
    "\n",
    "# Drop the contact_name column\n",
    "contacts_df_clean.drop('name', axis=1, inplace=True)\n",
    "\n",
    "contacts_df_clean.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4661</td>\n",
       "      <td>Cecilia</td>\n",
       "      <td>Velasco</td>\n",
       "      <td>cecilia.velasco@rodrigues.fr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3765</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>mariana.ellis@rossi.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4187</td>\n",
       "      <td>Sofie</td>\n",
       "      <td>Woods</td>\n",
       "      <td>sofie.woods@riviere.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4941</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>Iannotti</td>\n",
       "      <td>jeanette.iannotti@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2199</td>\n",
       "      <td>Samuel</td>\n",
       "      <td>Sorgatz</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5650</td>\n",
       "      <td>Socorro</td>\n",
       "      <td>Luna</td>\n",
       "      <td>socorro.luna@hotmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5889</td>\n",
       "      <td>Carolina</td>\n",
       "      <td>Murray</td>\n",
       "      <td>carolina.murray@knight.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4842</td>\n",
       "      <td>Kayla</td>\n",
       "      <td>Moon</td>\n",
       "      <td>kayla.moon@yahoo.de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3280</td>\n",
       "      <td>Ariadna</td>\n",
       "      <td>Geisel</td>\n",
       "      <td>ariadna.geisel@rangel.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5468</td>\n",
       "      <td>Danielle</td>\n",
       "      <td>Ladeck</td>\n",
       "      <td>danielle.ladeck@scalfaro.net</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id first_name last_name                         email\n",
       "0        4661    Cecilia   Velasco  cecilia.velasco@rodrigues.fr\n",
       "1        3765    Mariana     Ellis       mariana.ellis@rossi.org\n",
       "2        4187      Sofie     Woods       sofie.woods@riviere.com\n",
       "3        4941   Jeanette  Iannotti   jeanette.iannotti@yahoo.com\n",
       "4        2199     Samuel   Sorgatz      samuel.sorgatz@gmail.com\n",
       "5        5650    Socorro      Luna      socorro.luna@hotmail.com\n",
       "6        5889   Carolina    Murray    carolina.murray@knight.com\n",
       "7        4842      Kayla      Moon           kayla.moon@yahoo.de\n",
       "8        3280    Ariadna    Geisel     ariadna.geisel@rangel.com\n",
       "9        5468   Danielle    Ladeck  danielle.ladeck@scalfaro.net"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reorder the columns\n",
    "contacts_df_clean = contacts_df_clean [['contact_id','first_name','last_name','email']]\n",
    "contacts_df_clean.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 4 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   contact_id  1000 non-null   int64 \n",
      " 1   first_name  1000 non-null   object\n",
      " 2   last_name   1000 non-null   object\n",
      " 3   email       1000 non-null   object\n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 31.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the datatypes one more time before exporting as CSV file.\n",
    "contacts_df_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "contacts_df_clean.to_csv(\"Resources/contacts.csv\", encoding='utf8', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}