data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in csv file\n",
    "df = pd.read_csv('resources/historical_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the unnamed: 0 column as the placement for contestants\n",
    "df = df.rename(columns={'Unnamed: 0': 'result'})\n",
    "df['result'] = 1 + df['result']"
   ]
  },
  {
   "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>result</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Hometown</th>\n",
       "      <th>Job</th>\n",
       "      <th>Eliminated</th>\n",
       "      <th>season</th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Outcome</th>\n",
       "      <th>Place</th>\n",
       "      <th>Ref</th>\n",
       "      <th>Arrived</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Amanda Marsh</td>\n",
       "      <td>23.0</td>\n",
       "      <td>Chanute, Kansas</td>\n",
       "      <td>Event Planner</td>\n",
       "      <td>Winner</td>\n",
       "      <td>1</td>\n",
       "      <td>Event Planner</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Trista Rehn</td>\n",
       "      <td>29.0</td>\n",
       "      <td>St. Louis, Missouri</td>\n",
       "      <td>Miami Heat Dancer</td>\n",
       "      <td>Runner-up</td>\n",
       "      <td>1</td>\n",
       "      <td>Miami Heat Dancer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Shannon Oliver</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Dallas, Texas</td>\n",
       "      <td>Financial Management Consultant</td>\n",
       "      <td>Week 5</td>\n",
       "      <td>1</td>\n",
       "      <td>Financial Management Consultant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Kimberly Karels</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Tempe, Arizona</td>\n",
       "      <td>Nanny</td>\n",
       "      <td>Week 4</td>\n",
       "      <td>1</td>\n",
       "      <td>Nanny</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Cathy Grimes</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Terre Haute, Indiana</td>\n",
       "      <td>Graduate Student</td>\n",
       "      <td>Week 3</td>\n",
       "      <td>1</td>\n",
       "      <td>Graduate Student</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result             Name   Age              Hometown  \\\n",
       "0       1     Amanda Marsh  23.0       Chanute, Kansas   \n",
       "1       2      Trista Rehn  29.0   St. Louis, Missouri   \n",
       "2       3   Shannon Oliver  24.0         Dallas, Texas   \n",
       "3       4  Kimberly Karels  24.0        Tempe, Arizona   \n",
       "4       5     Cathy Grimes  22.0  Terre Haute, Indiana   \n",
       "\n",
       "                               Job Eliminated  season  \\\n",
       "0                    Event Planner     Winner       1   \n",
       "1                Miami Heat Dancer  Runner-up       1   \n",
       "2  Financial Management Consultant     Week 5       1   \n",
       "3                            Nanny     Week 4       1   \n",
       "4                 Graduate Student     Week 3       1   \n",
       "\n",
       "                        Occupation Outcome Place  Ref Arrived  \n",
       "0                    Event Planner     NaN   NaN  NaN     NaN  \n",
       "1                Miami Heat Dancer     NaN   NaN  NaN     NaN  \n",
       "2  Financial Management Consultant     NaN   NaN  NaN     NaN  \n",
       "3                            Nanny     NaN   NaN  NaN     NaN  \n",
       "4                 Graduate Student     NaN   NaN  NaN     NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the data in the Job and Occupation columns\n",
    "df['Occupation'] = df['Occupation'].fillna(df['Job'])\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 612 entries, 0 to 611\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   name        612 non-null    object\n",
      " 1   age         611 non-null    object\n",
      " 2   season      612 non-null    int64 \n",
      " 3   hometown    612 non-null    object\n",
      " 4   occupation  612 non-null    object\n",
      " 5   result      612 non-null    int64 \n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 28.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# Drop unnecessary columns\n",
    "df = df[['Name', 'Age', 'season', 'Hometown', 'Occupation', 'result']]\n",
    "df = df.rename(columns={'Name': 'name',\n",
    "                        'Age': 'age',\n",
    "                        'Hometown': 'hometown',\n",
    "                        'Occupation': 'occupation'})\n",
    "\n",
    "# Investigate the dataset to see what dtypes need to be converted\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 612 entries, 0 to 611\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   name        612 non-null    object \n",
      " 1   age         608 non-null    float64\n",
      " 2   season      612 non-null    object \n",
      " 3   hometown    612 non-null    object \n",
      " 4   occupation  612 non-null    object \n",
      " 5   result      612 non-null    int64  \n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 28.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# Convert the datatypes\n",
    "df['age'] = pd.to_numeric(df['age'], errors='coerce')\n",
    "df['season'] = df['season'].astype(str)\n",
    "\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>name</th>\n",
       "      <th>age</th>\n",
       "      <th>season</th>\n",
       "      <th>hometown</th>\n",
       "      <th>occupation</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Cosetta Blanca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>Italy</td>\n",
       "      <td>Dancer</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>Maquel Cooper</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>Alayah Benavidez</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>Susie Evans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>(Returned to competition)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name  age season                   hometown  \\\n",
       "42     Cosetta Blanca  NaN      9                      Italy   \n",
       "408     Maquel Cooper  NaN     22  (Returned to competition)   \n",
       "439  Alayah Benavidez  NaN     24  (Returned to competition)   \n",
       "494       Susie Evans  NaN     26  (Returned to competition)   \n",
       "\n",
       "                    occupation  result  \n",
       "42                      Dancer      18  \n",
       "408  (Returned to competition)      16  \n",
       "439  (Returned to competition)      17  \n",
       "494  (Returned to competition)       4  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Investigate the increase in NULL values for Age\n",
    "null_df = df[df['age'].isna()]\n",
    "\n",
    "null_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 608 entries, 0 to 611\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   name        608 non-null    object \n",
      " 1   age         608 non-null    float64\n",
      " 2   season      608 non-null    object \n",
      " 3   hometown    608 non-null    object \n",
      " 4   occupation  608 non-null    object \n",
      " 5   result      608 non-null    int64  \n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 33.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# These are duplicates and can be dropped\n",
    "# Minus Cosetta (sorry)\n",
    "df = df.dropna()\n",
    "\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\grant\\AppData\\Local\\Temp\\ipykernel_5592\\3063554193.py:3: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  df['name'] = df['name'].str.replace(pattern, '')\n"
     ]
    },
    {
     "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>name</th>\n",
       "      <th>age</th>\n",
       "      <th>season</th>\n",
       "      <th>hometown</th>\n",
       "      <th>occupation</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amanda Marsh</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chanute, Kansas</td>\n",
       "      <td>Event Planner</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Trista Rehn</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1</td>\n",
       "      <td>St. Louis, Missouri</td>\n",
       "      <td>Miami Heat Dancer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon Oliver</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Dallas, Texas</td>\n",
       "      <td>Financial Management Consultant</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kimberly Karels</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tempe, Arizona</td>\n",
       "      <td>Nanny</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cathy Grimes</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Terre Haute, Indiana</td>\n",
       "      <td>Graduate Student</td>\n",
       "      <td>5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607</th>\n",
       "      <td>Erin Landry</td>\n",
       "      <td>28.0</td>\n",
       "      <td>23</td>\n",
       "      <td>Plano, Texas</td>\n",
       "      <td>Cinderella</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>Adrianne \"Jane\" Averbukh</td>\n",
       "      <td>26.0</td>\n",
       "      <td>23</td>\n",
       "      <td>West Hollywood, California</td>\n",
       "      <td>Social Worker</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>609</th>\n",
       "      <td>Laura Pellerito</td>\n",
       "      <td>26.0</td>\n",
       "      <td>23</td>\n",
       "      <td>Dallas, Texas</td>\n",
       "      <td>Accountant</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610</th>\n",
       "      <td>Revian Chang</td>\n",
       "      <td>24.0</td>\n",
       "      <td>23</td>\n",
       "      <td>Plano, Texas</td>\n",
       "      <td>Nurse</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Tahzjuan Hawkins</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23</td>\n",
       "      <td>Castle Pines, Colorado</td>\n",
       "      <td>Business Development Associate</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>608 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name   age season                    hometown  \\\n",
       "0                Amanda Marsh  23.0      1             Chanute, Kansas   \n",
       "1                 Trista Rehn  29.0      1         St. Louis, Missouri   \n",
       "2              Shannon Oliver  24.0      1               Dallas, Texas   \n",
       "3             Kimberly Karels  24.0      1              Tempe, Arizona   \n",
       "4                Cathy Grimes  22.0      1        Terre Haute, Indiana   \n",
       "..                        ...   ...    ...                         ...   \n",
       "607               Erin Landry  28.0     23                Plano, Texas   \n",
       "608  Adrianne \"Jane\" Averbukh  26.0     23  West Hollywood, California   \n",
       "609           Laura Pellerito  26.0     23               Dallas, Texas   \n",
       "610              Revian Chang  24.0     23                Plano, Texas   \n",
       "611          Tahzjuan Hawkins  25.0     23      Castle Pines, Colorado   \n",
       "\n",
       "                          occupation  result  \n",
       "0                      Event Planner       1  \n",
       "1                  Miami Heat Dancer       2  \n",
       "2    Financial Management Consultant       3  \n",
       "3                              Nanny       4  \n",
       "4                   Graduate Student       5  \n",
       "..                               ...     ...  \n",
       "607                       Cinderella      26  \n",
       "608                    Social Worker      27  \n",
       "609                       Accountant      28  \n",
       "610                            Nurse      29  \n",
       "611   Business Development Associate      30  \n",
       "\n",
       "[608 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove the number link references\n",
    "pattern = r'\\[\\d+\\]'\n",
    "df['name'] = df['name'].str.replace(pattern, '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>name</th>\n",
       "      <th>age</th>\n",
       "      <th>season</th>\n",
       "      <th>hometown</th>\n",
       "      <th>occupation</th>\n",
       "      <th>result</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amanda Marsh</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chanute, Kansas</td>\n",
       "      <td>Event Planner</td>\n",
       "      <td>1</td>\n",
       "      <td>Chanute</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Trista Rehn</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1</td>\n",
       "      <td>St. Louis, Missouri</td>\n",
       "      <td>Miami Heat Dancer</td>\n",
       "      <td>2</td>\n",
       "      <td>St. Louis</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon Oliver</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Dallas, Texas</td>\n",
       "      <td>Financial Management Consultant</td>\n",
       "      <td>3</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kimberly Karels</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tempe, Arizona</td>\n",
       "      <td>Nanny</td>\n",
       "      <td>4</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cathy Grimes</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Terre Haute, Indiana</td>\n",
       "      <td>Graduate Student</td>\n",
       "      <td>5</td>\n",
       "      <td>Terre Haute</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name   age season              hometown  \\\n",
       "0     Amanda Marsh  23.0      1       Chanute, Kansas   \n",
       "1      Trista Rehn  29.0      1   St. Louis, Missouri   \n",
       "2   Shannon Oliver  24.0      1         Dallas, Texas   \n",
       "3  Kimberly Karels  24.0      1        Tempe, Arizona   \n",
       "4     Cathy Grimes  22.0      1  Terre Haute, Indiana   \n",
       "\n",
       "                        occupation  result         city      state other  \n",
       "0                    Event Planner       1      Chanute     Kansas  None  \n",
       "1                Miami Heat Dancer       2    St. Louis   Missouri  None  \n",
       "2  Financial Management Consultant       3       Dallas      Texas  None  \n",
       "3                            Nanny       4        Tempe    Arizona  None  \n",
       "4                 Graduate Student       5  Terre Haute    Indiana  None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Split the hometown into two additional columns for city and state\n",
    "df[['city', 'state', 'other']] = df['hometown'].str.split(',', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace values in city with Chicago and state with Illinois\n",
    "df.at[37, 'hometown'] = 'Chicago, Illinois'\n",
    "df.at[37, 'city'] = 'Chicago'\n",
    "df.at[37, 'state'] = 'Illinois'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the other column\n",
    "df.drop(columns='other', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the name column similar to the hometown\n",
    "df[['first', 'x', 'last']] = df['name'].str.split(' ', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find all the nicknames\n",
    "df['last'] = df['last'].fillna(df['x'])\n",
    "df.drop(columns='x', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out list of occupations for later grouping\n",
    "occupations = df['occupation']\n",
    "\n",
    "occupations.to_csv('x.csv')\n",
    "\n",
    "# Used Excel to analyze the professions and create categories\n",
    "\n",
    "# Read in csv file\n",
    "profession_groups_df = pd.read_csv('resources/professions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the dataframes for professional groups\n",
    "final_df = pd.merge(df, profession_groups_df, on='occupation', how='inner')\n",
    "\n",
    "# Drop duplicates based on all columns\n",
    "final_df = final_df.drop_duplicates().reset_index()\n",
    "\n",
    "# Format Final columns\n",
    "final_df = final_df[['name', 'age', 'season', 'hometown', 'occupation', 'result',\n",
    "       'city', 'state', 'first', 'last', 'job_category']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export to csv in the resources folder\n",
    "final_df.to_csv('resources/dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}