Crowdfunding-ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "pd.set_option('max_colwidth', 400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the crowdfunding.xlsx Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 2,
     "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": 3,
   "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",
      "             cf_id   contact_id           goal        pledged  backers_count  \\\n",
      "count  1000.000000  1000.000000    1000.000000    1000.000000    1000.000000   \n",
      "mean   1585.743000  3751.434000   43983.100000   42748.055000     727.005000   \n",
      "std     926.944534  1431.743284   58962.425595   57415.481551    1137.723135   \n",
      "min      23.000000  1235.000000     100.000000       0.000000       0.000000   \n",
      "25%     758.750000  2502.750000    4200.000000    5911.250000      86.000000   \n",
      "50%    1571.500000  3706.500000    8300.000000   11950.000000     184.500000   \n",
      "75%    2402.250000  4994.250000   80625.000000   66294.250000     923.750000   \n",
      "max    3209.000000  6234.000000  199200.000000  199110.000000    7295.000000   \n",
      "\n",
      "        launched_at      deadline  \n",
      "count  1.000000e+03  1.000000e+03  \n",
      "mean   1.610955e+09  1.627459e+09  \n",
      "std    1.330154e+07  9.072194e+06  \n",
      "min    1.579068e+09  1.609913e+09  \n",
      "25%    1.601507e+09  1.619672e+09  \n",
      "50%    1.611554e+09  1.627232e+09  \n",
      "75%    1.620018e+09  1.635224e+09  \n",
      "max    1.643695e+09  1.645596e+09  \n"
     ]
    }
   ],
   "source": [
    "# Get a brief summary of the crowdfunding_info DataFrame.\n",
    "\n",
    "# get summary statistics for numeric columns\n",
    "summary_stats = crowdfunding_info_df.describe()\n",
    "\n",
    "# get info on the DataFrame\n",
    "info = crowdfunding_info_df.info()\n",
    "\n",
    "# combine summary statistics and DataFrame info into a single DataFrame\n",
    "summary_df = pd.concat([summary_stats, info], axis=0)\n",
    "\n",
    "# display the summary DataFrame\n",
    "print(summary_df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the Category and Subcategory DataFrames\n",
    "---\n",
    "**Create a Category DataFrame that has the following columns:**\n",
    "- A \"category_id\" column that is numbered sequential form 1 to the length of the number of unique categories.\n",
    "- A \"category\" column that has only the categories.\n",
    "\n",
    "Export the DataFrame as a `category.csv` CSV file.\n",
    "\n",
    "**Create a SubCategory DataFrame that has the following columns:**\n",
    "- A \"subcategory_id\" column that is numbered sequential form 1 to the length of the number of unique subcategories.\n",
    "- A \"subcategory\" column that has only the subcategories. \n",
    "\n",
    "Export the DataFrame as a `subcategory.csv` CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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')\n"
     ]
    }
   ],
   "source": [
    "# Get the crowdfunding_info_df columns.\n",
    "print(crowdfunding_info_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              0            1\n",
      "0          food  food trucks\n",
      "1         music         rock\n",
      "2    technology          web\n",
      "3         music         rock\n",
      "4       theater        plays\n",
      "..          ...          ...\n",
      "995        food  food trucks\n",
      "996     theater        plays\n",
      "997     theater        plays\n",
      "998       music   indie rock\n",
      "999        food  food trucks\n",
      "\n",
      "[1000 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Assign the category and subcategory values to category and subcategory columns.\n",
    "categorize = crowdfunding_info_df[['category', 'subcategory']] = crowdfunding_info_df['category & sub-category'].str.split('/', expand=True)\n",
    "print(categorize)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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 = []\n",
    "subcategories = []\n",
    "\n",
    "for index, row in crowdfunding_info_df.iterrows():\n",
    "    # Split the category and subcategory string by '/'\n",
    "    category, subcategory = row['category & sub-category'].split('/')\n",
    "    # Append to the respective list if it doesn't already exist\n",
    "    if category not in categories:\n",
    "        categories.append(category)\n",
    "    if subcategory not in subcategories:\n",
    "        subcategories.append(subcategory)\n",
    "\n",
    "\n",
    "print(categories)\n",
    "print(subcategories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 8,
   "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": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    cat1\n",
      "1    cat2\n",
      "2    cat3\n",
      "3    cat4\n",
      "4    cat5\n",
      "5    cat6\n",
      "6    cat7\n",
      "7    cat8\n",
      "8    cat9\n",
      "Name: category_id, dtype: object\n",
      "0      subcat1\n",
      "1      subcat2\n",
      "2      subcat3\n",
      "3      subcat4\n",
      "4      subcat5\n",
      "5      subcat6\n",
      "6      subcat7\n",
      "7      subcat8\n",
      "8      subcat9\n",
      "9     subcat10\n",
      "10    subcat11\n",
      "11    subcat12\n",
      "12    subcat13\n",
      "13    subcat14\n",
      "14    subcat15\n",
      "15    subcat16\n",
      "16    subcat17\n",
      "17    subcat18\n",
      "18    subcat19\n",
      "19    subcat20\n",
      "20    subcat21\n",
      "21    subcat22\n",
      "22    subcat23\n",
      "23    subcat24\n",
      "Name: subcategory_id, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Use a list comprehension to add \"cat\" to each category_id. \n",
    "category_df = pd.DataFrame({'category_id': range(1, len(categories) + 1),\n",
    "                            'category': categories})\n",
    "\n",
    "\n",
    "cat_ids=category_df['category_id'] = 'cat' + category_df['category_id'].astype(str)\n",
    "\n",
    "\n",
    "# Use a list comprehension to add \"subcat\" to each subcategory_id.    \n",
    "\n",
    "subcategory_df = pd.DataFrame({'subcategory_id': range(1, len(subcategories) + 1),\n",
    "                            'subcategory': subcategories})\n",
    "subcat_ids = subcategory_df['subcategory_id']='subcat'+subcategory_df['subcategory_id'].astype(str)\n",
    "    \n",
    "print(cat_ids)\n",
    "print(subcat_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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, \n",
    "                           \"category\": categories})\n",
    "category_df\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\":subcat_ids, \n",
    "                           \"subcategory\": subcategories})\n",
    "subcategory_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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)"
   ]
  },
  {
   "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": 12,
   "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>subcategory</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  subcategory  \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": 12,
     "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": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "    </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  subcategory  \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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the blurb, launched_at, and deadline columns.\n",
    "\n",
    "crowdfunding_info_df = crowdfunding_info_df.rename(columns={\n",
    "    \"blurb\": \"description\",\n",
    "    \"launched_at\": \"launched_date\",\n",
    "    \"deadline\": \"end_date\"\n",
    "})\n",
    "\n",
    "crowdfunding_info_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the goal and pledged columns to a `float` data type.\n",
    "crowdfunding_info_df[\"goal\"] = crowdfunding_info_df[\"goal\"].astype(float)\n",
    "crowdfunding_info_df[\"pledged\"] = crowdfunding_info_df[\"pledged\"].astype(float)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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",
      "subcategory                 object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Check the datatypes\n",
    "print(crowdfunding_info_df.dtypes)\n",
    "\n",
    "#crowdfunding_info_df[\"pledged\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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    1970-01-02 1970-01-02   \n",
       "1  successful            158      US      USD    1970-01-02 1970-01-02   \n",
       "2  successful           1425      AU      AUD    1970-01-02 1970-01-02   \n",
       "3      failed             24      US      USD    1970-01-02 1970-01-02   \n",
       "4      failed             53      US      USD    1970-01-02 1970-01-02   \n",
       "\n",
       "   staff_pick  spotlight category & sub-category    category  subcategory  \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": 16,
     "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",
    "\n",
    "crowdfunding_info_df['launched_date'] = pd.to_datetime(crowdfunding_info_df['launched_date'], format='%Y-%m-%d')\n",
    "crowdfunding_info_df['end_date'] = pd.to_datetime(crowdfunding_info_df['end_date'], format='%Y-%m-%d')\n",
    "crowdfunding_info_df['launched_date'] = pd.to_datetime(crowdfunding_info_df['launched_date'].dt.day, unit='d')\n",
    "crowdfunding_info_df['end_date'] = pd.to_datetime(crowdfunding_info_df['end_date'].dt.day, unit='d')\n",
    "\n",
    "crowdfunding_info_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    </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    1970-01-02 1970-01-02       False   \n",
       "991            241      US      USD    1970-01-02 1970-01-02       False   \n",
       "992            132      US      USD    1970-01-02 1970-01-02       False   \n",
       "993             75      IT      EUR    1970-01-02 1970-01-02       False   \n",
       "994            842      US      USD    1970-01-02 1970-01-02       False   \n",
       "995           2043      US      USD    1970-01-02 1970-01-02       False   \n",
       "996            112      US      USD    1970-01-02 1970-01-02       False   \n",
       "997            139      IT      EUR    1970-01-02 1970-01-02       False   \n",
       "998            374      US      USD    1970-01-02 1970-01-02       False   \n",
       "999           1122      US      USD    1970-01-02 1970-01-02       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  \n",
       "990              drama        cat5  \n",
       "991               rock        cat2  \n",
       "992              drama        cat5  \n",
       "993  photography books        cat8  \n",
       "994       translations        cat6  \n",
       "995        food trucks        cat1  \n",
       "996              plays        cat4  \n",
       "997              plays        cat4  \n",
       "998         indie rock        cat2  \n",
       "999        food trucks        cat1  "
      ]
     },
     "execution_count": 17,
     "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_merge_df = crowdfunding_info_df.merge(category_df, how='left')\n",
    "\n",
    "campaign_merge_df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>subcategory</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "    <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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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>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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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>1970-01-02</td>\n",
       "      <td>1970-01-02</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",
       "<p>1000 rows × 19 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    1970-01-02 1970-01-02   \n",
       "1    successful            158      US      USD    1970-01-02 1970-01-02   \n",
       "2    successful           1425      AU      AUD    1970-01-02 1970-01-02   \n",
       "3        failed             24      US      USD    1970-01-02 1970-01-02   \n",
       "4        failed             53      US      USD    1970-01-02 1970-01-02   \n",
       "..          ...            ...     ...      ...           ...        ...   \n",
       "995  successful           2043      US      USD    1970-01-02 1970-01-02   \n",
       "996      failed            112      US      USD    1970-01-02 1970-01-02   \n",
       "997    canceled            139      IT      EUR    1970-01-02 1970-01-02   \n",
       "998      failed            374      US      USD    1970-01-02 1970-01-02   \n",
       "999    canceled           1122      US      USD    1970-01-02 1970-01-02   \n",
       "\n",
       "     staff_pick  spotlight category & sub-category    category  subcategory  \\\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",
       "    category_id subcategory_id  \n",
       "0          cat1        subcat1  \n",
       "1          cat2        subcat2  \n",
       "2          cat3        subcat3  \n",
       "3          cat2        subcat2  \n",
       "4          cat4        subcat4  \n",
       "..          ...            ...  \n",
       "995        cat1        subcat1  \n",
       "996        cat4        subcat4  \n",
       "997        cat4        subcat4  \n",
       "998        cat2        subcat8  \n",
       "999        cat1        subcat1  \n",
       "\n",
       "[1000 rows x 19 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "campaign_merged_df = campaign_merge_df.merge(subcategory_df, how='left')\n",
    "campaign_merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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</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>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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>True</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>True</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>1970-01-02</td>\n",
       "      <td>1970-01-02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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",
       "<p>1000 rows × 18 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    1970-01-02 1970-01-02   \n",
       "1    successful            158      US      USD    1970-01-02 1970-01-02   \n",
       "2    successful           1425      AU      AUD    1970-01-02 1970-01-02   \n",
       "3        failed             24      US      USD    1970-01-02 1970-01-02   \n",
       "4        failed             53      US      USD    1970-01-02 1970-01-02   \n",
       "..          ...            ...     ...      ...           ...        ...   \n",
       "995  successful           2043      US      USD    1970-01-02 1970-01-02   \n",
       "996      failed            112      US      USD    1970-01-02 1970-01-02   \n",
       "997    canceled            139      IT      EUR    1970-01-02 1970-01-02   \n",
       "998      failed            374      US      USD    1970-01-02 1970-01-02   \n",
       "999    canceled           1122      US      USD    1970-01-02 1970-01-02   \n",
       "\n",
       "     staff_pick  spotlight    category  subcategory category_id subcategory_id  \n",
       "0         False      False        food  food trucks        cat1        subcat1  \n",
       "1         False       True       music         rock        cat2        subcat2  \n",
       "2         False      False  technology          web        cat3        subcat3  \n",
       "3         False      False       music         rock        cat2        subcat2  \n",
       "4         False      False     theater        plays        cat4        subcat4  \n",
       "..          ...        ...         ...          ...         ...            ...  \n",
       "995       False       True        food  food trucks        cat1        subcat1  \n",
       "996       False      False     theater        plays        cat4        subcat4  \n",
       "997       False      False     theater        plays        cat4        subcat4  \n",
       "998       False       True       music   indie rock        cat2        subcat8  \n",
       "999       False      False        food  food trucks        cat1        subcat1  \n",
       "\n",
       "[1000 rows x 18 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unwanted columns?\n",
    "del campaign_merged_df['category & sub-category']\n",
    "\n",
    "campaign_merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cf_id', 'contact_id', 'company_name', 'description', 'goal', 'pledged',\n",
       "       'outcome', 'backers_count', 'country', 'currency', 'launched_date',\n",
       "       'end_date', 'staff_pick', 'spotlight', 'category', 'subcategory',\n",
       "       'category_id', 'subcategory_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "campaign_merged_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "del campaign_merged_df['category']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "del campaign_merged_df['subcategory']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "campaign_merged_df.to_csv(\"Resources/campaign.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the contacts.xlsx Data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the data into a Pandas DataFrame. Use the `header=2` parameter when reading in the data.\n",
    "contact_info_df = pd.read_excel('Resources/contacts.xlsx', header=3)\n",
    "contact_info_df.head()"
   ]
  },
  {
   "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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 1: Use Pandas to create the contacts DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "# Iterate through the contact_info_df and convert each row to a dictionary.\n",
    "import json\n",
    "dict_values = []\n",
    "\n",
    "\n",
    "# Print out the list of values for each row.\n",
    "print(dict_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "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",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check the datatypes.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a \"first\"name\" and \"last_name\" column with the first and last names from the \"name\" column. \n",
    "\n",
    "\n",
    "# Drop the contact_name column\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorder the columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check the datatypes one more time before exporting as CSV file.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "#contacts_df_clean.to_csv(\"Resources/contacts.csv\", encoding='utf8', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 2: Use regex to create the contacts DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_info_df_copy = contact_info_df.copy()\n",
    "contact_info_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['contact_info'], dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_info_df_copy.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the four-digit contact ID number.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the four-digit contact ID number.\n",
    "p = '(\\d{4})'\n",
    "contact_info_df_copy['contact_id']=contact_info_df_copy['contact_info'].str.extract(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_info</th>\n",
       "      <th>contact_id</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",
       "      <td>4661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\n",
       "      <td>3765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}</td>\n",
       "      <td>4187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}</td>\n",
       "      <td>4941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}</td>\n",
       "      <td>2199</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\"}   \n",
       "\n",
       "  contact_id  \n",
       "0       4661  \n",
       "1       3765  \n",
       "2       4187  \n",
       "3       4941  \n",
       "4       2199  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_info_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.astype of 0      4661\n",
       "1      3765\n",
       "2      4187\n",
       "3      4941\n",
       "4      2199\n",
       "       ... \n",
       "995    3684\n",
       "996    5784\n",
       "997    1498\n",
       "998    6073\n",
       "999    4939\n",
       "Name: contact_id, Length: 1000, dtype: object>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the datatypes.\n",
    "contact_info_df_copy['contact_id'].astype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      4661\n",
       "1      3765\n",
       "2      4187\n",
       "3      4941\n",
       "4      2199\n",
       "       ... \n",
       "995    3684\n",
       "996    5784\n",
       "997    1498\n",
       "998    6073\n",
       "999    4939\n",
       "Name: contact_id, Length: 1000, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the \"contact_id\" column to an int64 data type.\n",
    "contact_info_df_copy['contact_id'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the name of the contact and add it to a new column.\n",
    "p = '(?:\"name\": \")(.+\\s.+)(?:\",)'\n",
    "contact_info_df_copy['name']=contact_info_df_copy['contact_info'].str.extract(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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",
       "      <th>contact_id</th>\n",
       "      <th>name</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",
       "      <td>4661</td>\n",
       "      <td>Cecilia Velasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\n",
       "      <td>3765</td>\n",
       "      <td>Mariana Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}</td>\n",
       "      <td>4187</td>\n",
       "      <td>Sofie Woods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}</td>\n",
       "      <td>4941</td>\n",
       "      <td>Jeanette Iannotti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}</td>\n",
       "      <td>2199</td>\n",
       "      <td>Samuel Sorgatz</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\"}   \n",
       "\n",
       "  contact_id               name  \n",
       "0       4661    Cecilia Velasco  \n",
       "1       3765      Mariana Ellis  \n",
       "2       4187        Sofie Woods  \n",
       "3       4941  Jeanette Iannotti  \n",
       "4       2199     Samuel Sorgatz  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_info_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the email from the contacts and add the values to a new column.\n",
    "p = '(?:\")(\\w+\\.\\w+@\\w+\\.\\w+)(?:\")'\n",
    "contact_info_df_copy['email']=contact_info_df_copy['contact_info'].str.extract(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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",
       "      <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>{\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}</td>\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>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\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>{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}</td>\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>{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}</td>\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>{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}</td>\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_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\"}   \n",
       "\n",
       "  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": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_info_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy of the contact_info_df with the 'contact_id', 'name', 'email' columns.\n",
    "contacts_clean_df = contact_info_df_copy.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a \"first\"name\" and \"last_name\" column with the first and last names from the \"name\" column. \n",
    "\n",
    "p = '(\\w+)(?:\\s\\w+)'\n",
    "contacts_clean_df['first_name']=contacts_clean_df['name'].str.extract(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = '(?:\\w+\\s)(\\w+)'\n",
    "contacts_clean_df['last_name']=contacts_clean_df['name'].str.extract(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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",
       "      <th>contact_id</th>\n",
       "      <th>name</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>{\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}</td>\n",
       "      <td>4661</td>\n",
       "      <td>Cecilia Velasco</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>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\n",
       "      <td>3765</td>\n",
       "      <td>Mariana Ellis</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>{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}</td>\n",
       "      <td>4187</td>\n",
       "      <td>Sofie Woods</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>{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}</td>\n",
       "      <td>4941</td>\n",
       "      <td>Jeanette Iannotti</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>{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}</td>\n",
       "      <td>2199</td>\n",
       "      <td>Samuel Sorgatz</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "      <td>Samuel</td>\n",
       "      <td>Sorgatz</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\"}   \n",
       "\n",
       "  contact_id               name                         email first_name  \\\n",
       "0       4661    Cecilia Velasco  cecilia.velasco@rodrigues.fr    Cecilia   \n",
       "1       3765      Mariana Ellis       mariana.ellis@rossi.org    Mariana   \n",
       "2       4187        Sofie Woods       sofie.woods@riviere.com      Sofie   \n",
       "3       4941  Jeanette Iannotti   jeanette.iannotti@yahoo.com   Jeanette   \n",
       "4       2199     Samuel Sorgatz      samuel.sorgatz@gmail.com     Samuel   \n",
       "\n",
       "  last_name  \n",
       "0   Velasco  \n",
       "1     Ellis  \n",
       "2     Woods  \n",
       "3  Iannotti  \n",
       "4   Sorgatz  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contacts_clean_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the contact_name column\n",
    "contacts_clean_df=contacts_clean_df.drop(columns=['name', 'contact_info'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>3684</td>\n",
       "      <td>Whitney</td>\n",
       "      <td>Noack</td>\n",
       "      <td>whitney.noack@laboratorios.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>5784</td>\n",
       "      <td>Gelsomina</td>\n",
       "      <td>Migliaccio</td>\n",
       "      <td>gelsomina.migliaccio@junk.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>1498</td>\n",
       "      <td>Evangelista</td>\n",
       "      <td>Pereira</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>6073</td>\n",
       "      <td>Gareth</td>\n",
       "      <td>Comolli</td>\n",
       "      <td>gareth.comolli@tiscali.fr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>4939</td>\n",
       "      <td>Michelangelo</td>\n",
       "      <td>Hess</td>\n",
       "      <td>michelangelo.hess@bouygtel.fr</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 4 columns</p>\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",
       "..         ...           ...         ...                             ...\n",
       "995       3684       Whitney       Noack  whitney.noack@laboratorios.org\n",
       "996       5784     Gelsomina  Migliaccio   gelsomina.migliaccio@junk.com\n",
       "997       1498   Evangelista     Pereira                             NaN\n",
       "998       6073        Gareth     Comolli       gareth.comolli@tiscali.fr\n",
       "999       4939  Michelangelo        Hess   michelangelo.hess@bouygtel.fr\n",
       "\n",
       "[1000 rows x 4 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reorder the columns\n",
    "contacts_clean_df.iloc[:,[0,2,3,1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "contact_id    object\n",
       "email         object\n",
       "first_name    object\n",
       "last_name     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the datatypes one more time before exporting as CSV file.\n",
    "contacts_clean_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "contacts_clean_df['contact_id']=contacts_clean_df['contact_id'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "contact_id     int64\n",
       "email         object\n",
       "first_name    object\n",
       "last_name     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contacts_clean_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "contacts_clean_df.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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}