ETL_Mini_Project_JShulyak.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('max_colwidth', 400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Create the Category and Subcategory DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the crowdfunding.xlsx Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 4,
     "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": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 15 columns):\n",
      " #   Column                   Non-Null Count  Dtype \n",
      "---  ------                   --------------  ----- \n",
      " 0   cf_id                    1000 non-null   int64 \n",
      " 1   contact_id               1000 non-null   int64 \n",
      " 2   company_name             1000 non-null   object\n",
      " 3   blurb                    1000 non-null   object\n",
      " 4   goal                     1000 non-null   int64 \n",
      " 5   pledged                  1000 non-null   int64 \n",
      " 6   outcome                  1000 non-null   object\n",
      " 7   backers_count            1000 non-null   int64 \n",
      " 8   country                  1000 non-null   object\n",
      " 9   currency                 1000 non-null   object\n",
      " 10  launched_at              1000 non-null   int64 \n",
      " 11  deadline                 1000 non-null   int64 \n",
      " 12  staff_pick               1000 non-null   bool  \n",
      " 13  spotlight                1000 non-null   bool  \n",
      " 14  category & sub-category  1000 non-null   object\n",
      "dtypes: bool(2), int64(7), object(6)\n",
      "memory usage: 103.6+ KB\n"
     ]
    }
   ],
   "source": [
    "# Get a brief summary of the crowdfunding_info DataFrame.\n",
    "crowdfunding_info_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['cf_id',\n",
       " 'contact_id',\n",
       " 'company_name',\n",
       " 'blurb',\n",
       " 'goal',\n",
       " 'pledged',\n",
       " 'outcome',\n",
       " 'backers_count',\n",
       " 'country',\n",
       " 'currency',\n",
       " 'launched_at',\n",
       " 'deadline',\n",
       " 'staff_pick',\n",
       " 'spotlight',\n",
       " 'category & sub-category']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the crowdfunding_info_df columns.\n",
    "print(crowdfunding_info_df.columns.to_list())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create columns for categories and subcategories. Columns should include:\n",
    "\n",
    "A \"category_id\" (\"subcategory_id\") column that has entries going sequentially from \"cat1\" to \"catn\", where n is the number of unique categories\n",
    "\n",
    "A \"category\" (\"subcategorY) column that contains only the category titles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the category and subcategory values to category and subcategory columns.\n",
    "crowdfunding_info_df['category'] = [item.split('/')[0] for item in crowdfunding_info_df['category & sub-category']]\n",
    "crowdfunding_info_df['subcategory'] = [item.split('/')[1] for item in crowdfunding_info_df['category & sub-category']]"
   ]
  },
  {
   "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>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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crowdfunding_info_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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 = list(crowdfunding_info_df['category'].unique())\n",
    "subcategories = list(crowdfunding_info_df['subcategory'].unique())\n",
    "print(categories)\n",
    "print(subcategories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": 15,
   "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": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['cat1', 'cat2', 'cat3', 'cat4', 'cat5', 'cat6', 'cat7', 'cat8', 'cat9']\n",
      "['subcat1', 'subcat2', 'subcat3', 'subcat4', 'subcat5', 'subcat6', 'subcat7', 'subcat8', 'subcat9', 'subcat10', 'subcat11', 'subcat12', 'subcat13', 'subcat14', 'subcat15', 'subcat16', 'subcat17', 'subcat18', 'subcat19', 'subcat20', 'subcat21', 'subcat22', 'subcat23', 'subcat24']\n"
     ]
    }
   ],
   "source": [
    "# Use a list comprehension to add \"cat\" to each category_id. \n",
    "cat_ids = [\"cat\" + str(x) for x in category_ids]\n",
    "# Use a list comprehension to add \"subcat\" to each subcategory_id.    \n",
    "scat_ids = [\"subcat\" + str(x) for x in subcategory_ids]\n",
    "    \n",
    "print(cat_ids)\n",
    "print(scat_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create category dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cat1</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cat2</td>\n",
       "      <td>music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cat3</td>\n",
       "      <td>technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cat4</td>\n",
       "      <td>theater</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cat5</td>\n",
       "      <td>film &amp; video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>cat6</td>\n",
       "      <td>publishing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>cat7</td>\n",
       "      <td>games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>cat8</td>\n",
       "      <td>photography</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>cat9</td>\n",
       "      <td>journalism</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category_id      category\n",
       "0        cat1          food\n",
       "1        cat2         music\n",
       "2        cat3    technology\n",
       "3        cat4       theater\n",
       "4        cat5  film & video\n",
       "5        cat6    publishing\n",
       "6        cat7         games\n",
       "7        cat8   photography\n",
       "8        cat9    journalism"
      ]
     },
     "execution_count": 17,
     "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, 'category': categories})\n",
    "\n",
    "category_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create subcategory dataframe"
   ]
  },
  {
   "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>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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a category DataFrame with the subcategory_id array as the subcategory_id and subcategories list as the subcategory name. \n",
    "subcategory_df = pd.DataFrame({'subcategory_id': scat_ids, 'subcategory': subcategories})\n",
    "\n",
    "subcategory_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export dfs as CSV files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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": [
    "# Part 2: Create the Campaign DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make a copy of the crowdfunding_info_df to work with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 20,
     "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": "markdown",
   "metadata": {},
   "source": [
    "### Make changes to columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>launch_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  launch_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": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the blurb, launched_at, and deadline columns.\n",
    "campaign_renamed_df = campaign_df.rename(columns={'blurb': 'description', 'launched_at': 'launch_date', 'deadline': 'end_date'})\n",
    "campaign_renamed_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 17 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   description              1000 non-null   object \n",
      " 4   goal                     1000 non-null   float64\n",
      " 5   pledged                  1000 non-null   float64\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  launch_date              1000 non-null   int64  \n",
      " 11  end_date                 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",
      " 15  category                 1000 non-null   object \n",
      " 16  subcategory              1000 non-null   object \n",
      "dtypes: bool(2), float64(2), int64(5), object(8)\n",
      "memory usage: 119.3+ KB\n"
     ]
    }
   ],
   "source": [
    "# Convert the goal and pledged columns to a `float` data type.\n",
    "campaign_renamed_df = campaign_renamed_df.astype({'goal': 'float',\n",
    "                                                'pledged': 'float'})\n",
    "# Check the datatypes\n",
    "campaign_renamed_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>launch_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>2020-02-13 06:00:00</td>\n",
       "      <td>2021-03-01 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1621</td>\n",
       "      <td>3765</td>\n",
       "      <td>Odom Inc</td>\n",
       "      <td>Managed bottom-line architecture</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>14560.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-01-25 06:00:00</td>\n",
       "      <td>2021-05-25 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1812</td>\n",
       "      <td>4187</td>\n",
       "      <td>Melton, Robinson and Fritz</td>\n",
       "      <td>Function-based leadingedge pricing structure</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>142523.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>2020-12-17 06:00:00</td>\n",
       "      <td>2021-12-30 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>technology/web</td>\n",
       "      <td>technology</td>\n",
       "      <td>web</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2156</td>\n",
       "      <td>4941</td>\n",
       "      <td>Mcdonald, Gonzalez and Ross</td>\n",
       "      <td>Vision-oriented fresh-thinking conglomeration</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>2477.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-21 05:00:00</td>\n",
       "      <td>2022-01-17 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1365</td>\n",
       "      <td>2199</td>\n",
       "      <td>Larson-Little</td>\n",
       "      <td>Proactive foreground core</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>5265.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-21 06:00:00</td>\n",
       "      <td>2021-08-23 05:00:00</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         launch_date  \\\n",
       "0      failed              0      CA      CAD 2020-02-13 06:00:00   \n",
       "1  successful            158      US      USD 2021-01-25 06:00:00   \n",
       "2  successful           1425      AU      AUD 2020-12-17 06:00:00   \n",
       "3      failed             24      US      USD 2021-10-21 05:00:00   \n",
       "4      failed             53      US      USD 2020-12-21 06:00:00   \n",
       "\n",
       "             end_date  staff_pick  spotlight category & sub-category  \\\n",
       "0 2021-03-01 06:00:00       False      False        food/food trucks   \n",
       "1 2021-05-25 05:00:00       False       True              music/rock   \n",
       "2 2021-12-30 06:00:00       False      False          technology/web   \n",
       "3 2022-01-17 06:00:00       False      False              music/rock   \n",
       "4 2021-08-23 05:00:00       False      False           theater/plays   \n",
       "\n",
       "     category  subcategory  \n",
       "0        food  food trucks  \n",
       "1       music         rock  \n",
       "2  technology          web  \n",
       "3       music         rock  \n",
       "4     theater        plays  "
      ]
     },
     "execution_count": 24,
     "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",
    "campaign_renamed_df['launch_date'] = pd.to_datetime(campaign_renamed_df['launch_date'], unit='s')\n",
    "campaign_renamed_df['end_date'] = pd.to_datetime(campaign_renamed_df['end_date'], unit='s')\n",
    "\n",
    "campaign_renamed_df.head(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge the campaign df with the category and subcategory dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>launch_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>staff_pick</th>\n",
       "      <th>spotlight</th>\n",
       "      <th>category &amp; sub-category</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>990</th>\n",
       "      <td>1561</td>\n",
       "      <td>5500</td>\n",
       "      <td>Ortiz-Roberts</td>\n",
       "      <td>Devolved foreground customer loyalty</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>6839.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>64</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-06-09 05:00:00</td>\n",
       "      <td>2021-06-18 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>film &amp; video/drama</td>\n",
       "      <td>film &amp; video</td>\n",
       "      <td>drama</td>\n",
       "      <td>cat5</td>\n",
       "      <td>subcat7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991</th>\n",
       "      <td>2632</td>\n",
       "      <td>3494</td>\n",
       "      <td>Ramirez LLC</td>\n",
       "      <td>Reduced reciprocal focus group</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>11091.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>241</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-09 06:00:00</td>\n",
       "      <td>2021-05-26 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/rock</td>\n",
       "      <td>music</td>\n",
       "      <td>rock</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>439</td>\n",
       "      <td>3924</td>\n",
       "      <td>Morrow Inc</td>\n",
       "      <td>Networked global migration</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>13223.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>132</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-06-14 05:00:00</td>\n",
       "      <td>2021-02-09 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>film &amp; video/drama</td>\n",
       "      <td>film &amp; video</td>\n",
       "      <td>drama</td>\n",
       "      <td>cat5</td>\n",
       "      <td>subcat7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>461</td>\n",
       "      <td>3521</td>\n",
       "      <td>Erickson-Rogers</td>\n",
       "      <td>De-engineered even-keeled definition</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>7608.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>75</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2021-07-03 05:00:00</td>\n",
       "      <td>2021-07-08 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>photography/photography books</td>\n",
       "      <td>photography</td>\n",
       "      <td>photography books</td>\n",
       "      <td>cat8</td>\n",
       "      <td>subcat15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>1419</td>\n",
       "      <td>5262</td>\n",
       "      <td>Leach, Rich and Price</td>\n",
       "      <td>Implemented bi-directional flexibility</td>\n",
       "      <td>141100.0</td>\n",
       "      <td>74073.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>842</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-11-15 06:00:00</td>\n",
       "      <td>2021-12-07 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>publishing/translations</td>\n",
       "      <td>publishing</td>\n",
       "      <td>translations</td>\n",
       "      <td>cat6</td>\n",
       "      <td>subcat19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>2986</td>\n",
       "      <td>3684</td>\n",
       "      <td>Manning-Hamilton</td>\n",
       "      <td>Vision-oriented scalable definition</td>\n",
       "      <td>97300.0</td>\n",
       "      <td>153216.0</td>\n",
       "      <td>successful</td>\n",
       "      <td>2043</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-29 06:00:00</td>\n",
       "      <td>2021-05-30 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>2031</td>\n",
       "      <td>5784</td>\n",
       "      <td>Butler LLC</td>\n",
       "      <td>Future-proofed upward-trending migration</td>\n",
       "      <td>6600.0</td>\n",
       "      <td>4814.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>112</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-15 05:00:00</td>\n",
       "      <td>2021-11-30 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>1627</td>\n",
       "      <td>1498</td>\n",
       "      <td>Ball LLC</td>\n",
       "      <td>Right-sized full-range throughput</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>4603.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>139</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2021-11-06 05:00:00</td>\n",
       "      <td>2021-12-10 06:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>theater/plays</td>\n",
       "      <td>theater</td>\n",
       "      <td>plays</td>\n",
       "      <td>cat4</td>\n",
       "      <td>subcat4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>2175</td>\n",
       "      <td>6073</td>\n",
       "      <td>Taylor, Santiago and Flores</td>\n",
       "      <td>Polarized composite customer loyalty</td>\n",
       "      <td>66600.0</td>\n",
       "      <td>37823.0</td>\n",
       "      <td>failed</td>\n",
       "      <td>374</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-10-08 05:00:00</td>\n",
       "      <td>2021-04-11 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>music/indie rock</td>\n",
       "      <td>music</td>\n",
       "      <td>indie rock</td>\n",
       "      <td>cat2</td>\n",
       "      <td>subcat8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1788</td>\n",
       "      <td>4939</td>\n",
       "      <td>Hernandez, Norton and Kelley</td>\n",
       "      <td>Expanded eco-centric policy</td>\n",
       "      <td>111100.0</td>\n",
       "      <td>62819.0</td>\n",
       "      <td>canceled</td>\n",
       "      <td>1122</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-30 06:00:00</td>\n",
       "      <td>2021-08-18 05:00:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>food/food trucks</td>\n",
       "      <td>food</td>\n",
       "      <td>food trucks</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cf_id  contact_id                  company_name  \\\n",
       "990   1561        5500                 Ortiz-Roberts   \n",
       "991   2632        3494                   Ramirez LLC   \n",
       "992    439        3924                    Morrow Inc   \n",
       "993    461        3521               Erickson-Rogers   \n",
       "994   1419        5262         Leach, Rich and Price   \n",
       "995   2986        3684              Manning-Hamilton   \n",
       "996   2031        5784                    Butler LLC   \n",
       "997   1627        1498                      Ball LLC   \n",
       "998   2175        6073   Taylor, Santiago and Flores   \n",
       "999   1788        4939  Hernandez, Norton and Kelley   \n",
       "\n",
       "                                  description      goal   pledged     outcome  \\\n",
       "990      Devolved foreground customer loyalty    7800.0    6839.0      failed   \n",
       "991            Reduced reciprocal focus group    9800.0   11091.0  successful   \n",
       "992                Networked global migration    3100.0   13223.0  successful   \n",
       "993      De-engineered even-keeled definition    9800.0    7608.0    canceled   \n",
       "994    Implemented bi-directional flexibility  141100.0   74073.0      failed   \n",
       "995       Vision-oriented scalable definition   97300.0  153216.0  successful   \n",
       "996  Future-proofed upward-trending migration    6600.0    4814.0      failed   \n",
       "997         Right-sized full-range throughput    7600.0    4603.0    canceled   \n",
       "998      Polarized composite customer loyalty   66600.0   37823.0      failed   \n",
       "999               Expanded eco-centric policy  111100.0   62819.0    canceled   \n",
       "\n",
       "     backers_count country currency         launch_date            end_date  \\\n",
       "990             64      US      USD 2021-06-09 05:00:00 2021-06-18 05:00:00   \n",
       "991            241      US      USD 2020-12-09 06:00:00 2021-05-26 05:00:00   \n",
       "992            132      US      USD 2020-06-14 05:00:00 2021-02-09 06:00:00   \n",
       "993             75      IT      EUR 2021-07-03 05:00:00 2021-07-08 05:00:00   \n",
       "994            842      US      USD 2021-11-15 06:00:00 2021-12-07 06:00:00   \n",
       "995           2043      US      USD 2020-12-29 06:00:00 2021-05-30 05:00:00   \n",
       "996            112      US      USD 2021-10-15 05:00:00 2021-11-30 06:00:00   \n",
       "997            139      IT      EUR 2021-11-06 05:00:00 2021-12-10 06:00:00   \n",
       "998            374      US      USD 2020-10-08 05:00:00 2021-04-11 05:00:00   \n",
       "999           1122      US      USD 2020-12-30 06:00:00 2021-08-18 05:00:00   \n",
       "\n",
       "     staff_pick  spotlight        category & sub-category      category  \\\n",
       "990       False       True             film & video/drama  film & video   \n",
       "991       False       True                     music/rock         music   \n",
       "992       False       True             film & video/drama  film & video   \n",
       "993       False       True  photography/photography books   photography   \n",
       "994       False       True        publishing/translations    publishing   \n",
       "995       False       True               food/food trucks          food   \n",
       "996       False      False                  theater/plays       theater   \n",
       "997       False      False                  theater/plays       theater   \n",
       "998       False       True               music/indie rock         music   \n",
       "999       False      False               food/food trucks          food   \n",
       "\n",
       "           subcategory category_id subcategory_id  \n",
       "990              drama        cat5        subcat7  \n",
       "991               rock        cat2        subcat2  \n",
       "992              drama        cat5        subcat7  \n",
       "993  photography books        cat8       subcat15  \n",
       "994       translations        cat6       subcat19  \n",
       "995        food trucks        cat1        subcat1  \n",
       "996              plays        cat4        subcat4  \n",
       "997              plays        cat4        subcat4  \n",
       "998         indie rock        cat2        subcat8  \n",
       "999        food trucks        cat1        subcat1  "
      ]
     },
     "execution_count": 26,
     "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",
    "\n",
    "merge1 = pd.merge(campaign_renamed_df, category_df, on =\"category\", how = 'left')\n",
    "campaign_merged_df = pd.merge(merge1, subcategory_df, on = 'subcategory', how = 'left')  \n",
    "\n",
    "campaign_merged_df.tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop unwanted columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>backers_count</th>\n",
       "      <th>country</th>\n",
       "      <th>currency</th>\n",
       "      <th>launch_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>4661</td>\n",
       "      <td>Baldwin, Riley and Jackson</td>\n",
       "      <td>Pre-emptive tertiary standardization</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>CA</td>\n",
       "      <td>CAD</td>\n",
       "      <td>2020-02-13 06:00:00</td>\n",
       "      <td>2021-03-01 06:00:00</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>158</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-01-25 06:00:00</td>\n",
       "      <td>2021-05-25 05:00:00</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>1425</td>\n",
       "      <td>AU</td>\n",
       "      <td>AUD</td>\n",
       "      <td>2020-12-17 06:00:00</td>\n",
       "      <td>2021-12-30 06:00:00</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>24</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-21 05:00:00</td>\n",
       "      <td>2022-01-17 06:00:00</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>53</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-21 06:00:00</td>\n",
       "      <td>2021-08-23 05:00:00</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",
       "    </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>2043</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-29 06:00:00</td>\n",
       "      <td>2021-05-30 05:00:00</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>112</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2021-10-15 05:00:00</td>\n",
       "      <td>2021-11-30 06:00:00</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>139</td>\n",
       "      <td>IT</td>\n",
       "      <td>EUR</td>\n",
       "      <td>2021-11-06 05:00:00</td>\n",
       "      <td>2021-12-10 06:00:00</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>374</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-10-08 05:00:00</td>\n",
       "      <td>2021-04-11 05:00:00</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>1122</td>\n",
       "      <td>US</td>\n",
       "      <td>USD</td>\n",
       "      <td>2020-12-30 06:00:00</td>\n",
       "      <td>2021-08-18 05:00:00</td>\n",
       "      <td>cat1</td>\n",
       "      <td>subcat1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 13 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",
       "     backers_count country currency         launch_date            end_date  \\\n",
       "0                0      CA      CAD 2020-02-13 06:00:00 2021-03-01 06:00:00   \n",
       "1              158      US      USD 2021-01-25 06:00:00 2021-05-25 05:00:00   \n",
       "2             1425      AU      AUD 2020-12-17 06:00:00 2021-12-30 06:00:00   \n",
       "3               24      US      USD 2021-10-21 05:00:00 2022-01-17 06:00:00   \n",
       "4               53      US      USD 2020-12-21 06:00:00 2021-08-23 05:00:00   \n",
       "..             ...     ...      ...                 ...                 ...   \n",
       "995           2043      US      USD 2020-12-29 06:00:00 2021-05-30 05:00:00   \n",
       "996            112      US      USD 2021-10-15 05:00:00 2021-11-30 06:00:00   \n",
       "997            139      IT      EUR 2021-11-06 05:00:00 2021-12-10 06:00:00   \n",
       "998            374      US      USD 2020-10-08 05:00:00 2021-04-11 05:00:00   \n",
       "999           1122      US      USD 2020-12-30 06:00:00 2021-08-18 05:00:00   \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 13 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "campaign_cleaned = campaign_merged_df.drop(columns=['outcome', 'staff_pick', 'spotlight', 'category & sub-category', 'category', 'subcategory'])\n",
    "campaign_cleaned"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export the DataFrame as a `campaign.csv` CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "campaign_cleaned.to_csv(\"Resources/campaign.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 3: Create the Contacts DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import contact ifo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>This list of contacts was updated on 11/10/2020.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Note: The contact information needs to be separated into the following columns: contact_id,  first name, last name,  and email.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>contact_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                  This list of contacts was updated on 11/10/2020.\n",
       "0  Note: The contact information needs to be separated into the following columns: contact_id,  first name, last name,  and email.\n",
       "1                                                                                                                                 \n",
       "2                                                                                                                     contact_info\n",
       "3                                         {\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}\n",
       "4                                                {\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_df = pd.read_excel('Resources/contacts.xlsx')\n",
    "contact_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transform excel data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"contact_id\": 4661, \"name\": \"Cecilia Velasco\", \"email\": \"cecilia.velasco@rodrigues.fr\"}\n",
      "{\"contact_id\": 3765, \"name\": \"Mariana Ellis\", \"email\": \"mariana.ellis@rossi.org\"}\n",
      "{\"contact_id\": 4187, \"name\": \"Sofie Woods\", \"email\": \"sofie.woods@riviere.com\"}\n",
      "{\"contact_id\": 4941, \"name\": \"Jeanette Iannotti\", \"email\": \"jeanette.iannotti@yahoo.com\"}\n",
      "{\"contact_id\": 2199, \"name\": \"Samuel Sorgatz\", \"email\": \"samuel.sorgatz@gmail.com\"}\n"
     ]
    }
   ],
   "source": [
    "# Columns for the info\n",
    "columns = ['contact_id', 'first_name', 'last_name', 'email']\n",
    "\n",
    "# Need to make a list of dictionaries \n",
    "# Need to chop of first 3 lists because they do not hold the data\n",
    "contact_info = (contact_df.values.tolist())[3:]\n",
    "\n",
    "# print first 5 lists to check \n",
    "for x in range(5):\n",
    "    print(contact_info[x][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'contact_id': 4661,\n",
       " 'name': 'Cecilia Velasco',\n",
       " 'email': 'cecilia.velasco@rodrigues.fr'}"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loop through each row and convert each to a dictionary using json.loads\n",
    "dict = [json.loads(x[0]) for x in contact_info]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_id</th>\n",
       "      <th>name</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4661</td>\n",
       "      <td>Cecilia Velasco</td>\n",
       "      <td>cecilia.velasco@rodrigues.fr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3765</td>\n",
       "      <td>Mariana Ellis</td>\n",
       "      <td>mariana.ellis@rossi.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4187</td>\n",
       "      <td>Sofie Woods</td>\n",
       "      <td>sofie.woods@riviere.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4941</td>\n",
       "      <td>Jeanette Iannotti</td>\n",
       "      <td>jeanette.iannotti@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2199</td>\n",
       "      <td>Samuel Sorgatz</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id               name                         email\n",
       "0        4661    Cecilia Velasco  cecilia.velasco@rodrigues.fr\n",
       "1        3765      Mariana Ellis       mariana.ellis@rossi.org\n",
       "2        4187        Sofie Woods       sofie.woods@riviere.com\n",
       "3        4941  Jeanette Iannotti   jeanette.iannotti@yahoo.com\n",
       "4        2199     Samuel Sorgatz      samuel.sorgatz@gmail.com"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Create contact_info dataframe\n",
    "contact_info_df = pd.DataFrame(dict)\n",
    "contact_info_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Cecilia', 'Mariana', 'Sofie', 'Jeanette', 'Samuel']"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Split the name column and make list of first names and list of last names\n",
    "first_name = [row.split(\" \")[0] for row in contact_info_df[\"name\"]]\n",
    "\n",
    "last_name = [row.split(\" \")[1] for row in contact_info_df[\"name\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add first and last name columns to df\n",
    "contact_info_df['first_name'] = first_name\n",
    "\n",
    "contact_info_df['last_name'] = last_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contact_id</th>\n",
       "      <th>email</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4661</td>\n",
       "      <td>cecilia.velasco@rodrigues.fr</td>\n",
       "      <td>Cecilia</td>\n",
       "      <td>Velasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3765</td>\n",
       "      <td>mariana.ellis@rossi.org</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4187</td>\n",
       "      <td>sofie.woods@riviere.com</td>\n",
       "      <td>Sofie</td>\n",
       "      <td>Woods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4941</td>\n",
       "      <td>jeanette.iannotti@yahoo.com</td>\n",
       "      <td>Jeanette</td>\n",
       "      <td>Iannotti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2199</td>\n",
       "      <td>samuel.sorgatz@gmail.com</td>\n",
       "      <td>Samuel</td>\n",
       "      <td>Sorgatz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id                         email first_name last_name\n",
       "0        4661  cecilia.velasco@rodrigues.fr    Cecilia   Velasco\n",
       "1        3765       mariana.ellis@rossi.org    Mariana     Ellis\n",
       "2        4187       sofie.woods@riviere.com      Sofie     Woods\n",
       "3        4941   jeanette.iannotti@yahoo.com   Jeanette  Iannotti\n",
       "4        2199      samuel.sorgatz@gmail.com     Samuel   Sorgatz"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contacts_df = contact_info_df.drop(columns='name')\n",
    "contacts_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contact_id first_name last_name                         email\n",
       "0        4661    Cecilia   Velasco  cecilia.velasco@rodrigues.fr\n",
       "1        3765    Mariana     Ellis       mariana.ellis@rossi.org\n",
       "2        4187      Sofie     Woods       sofie.woods@riviere.com\n",
       "3        4941   Jeanette  Iannotti   jeanette.iannotti@yahoo.com\n",
       "4        2199     Samuel   Sorgatz      samuel.sorgatz@gmail.com"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reorder the columns \n",
    "contacts_df = contacts_df[['contact_id', 'first_name', 'last_name', 'email']]\n",
    "\n",
    "contacts_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 4 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   contact_id  1000 non-null   int64 \n",
      " 1   first_name  1000 non-null   object\n",
      " 2   last_name   1000 non-null   object\n",
      " 3   email       1000 non-null   object\n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 31.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check datatype\n",
    "contacts_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export the DataFrame as a `contacts.csv` CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the DataFrame as a CSV file. \n",
    "contacts_df.to_csv(\"Resources/contacts.csv\", encoding='utf8', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('base')",
   "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.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "8ca3c2b93fec3a68989a8a35ad4ad37f554c7436255229aa8dbd97e50d16e03f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}