4. New Customer Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1317a864",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import scipy.stats as stats\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2eab8263",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\manis\\AppData\\Local\\Temp\\ipykernel_13964\\1994424616.py:1: FutureWarning: Inferring datetime64[ns] from data containing strings is deprecated and will be removed in a future version. To retain the old behavior explicitly pass Series(data, dtype=datetime64[ns])\n",
      "  new_customer = pd.read_excel('Client Data.xlsx', sheet_name=4, header=1)\n"
     ]
    }
   ],
   "source": [
    "new_customer = pd.read_excel('Client Data.xlsx', sheet_name=4, header=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2ec4b185",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 23 columns):\n",
      " #   Column                               Non-Null Count  Dtype         \n",
      "---  ------                               --------------  -----         \n",
      " 0   first_name                           1000 non-null   object        \n",
      " 1   last_name                            971 non-null    object        \n",
      " 2   gender                               1000 non-null   object        \n",
      " 3   past_3_years_bike_related_purchases  1000 non-null   int64         \n",
      " 4   DOB                                  983 non-null    datetime64[ns]\n",
      " 5   job_title                            894 non-null    object        \n",
      " 6   job_industry_category                835 non-null    object        \n",
      " 7   wealth_segment                       1000 non-null   object        \n",
      " 8   deceased_indicator                   1000 non-null   object        \n",
      " 9   owns_car                             1000 non-null   object        \n",
      " 10  tenure                               1000 non-null   int64         \n",
      " 11  address                              1000 non-null   object        \n",
      " 12  postcode                             1000 non-null   int64         \n",
      " 13  state                                1000 non-null   object        \n",
      " 14  country                              1000 non-null   object        \n",
      " 15  property_valuation                   1000 non-null   int64         \n",
      " 16  Unnamed: 16                          1000 non-null   float64       \n",
      " 17  Unnamed: 17                          1000 non-null   float64       \n",
      " 18  Unnamed: 18                          1000 non-null   float64       \n",
      " 19  Unnamed: 19                          1000 non-null   float64       \n",
      " 20  Unnamed: 20                          1000 non-null   int64         \n",
      " 21  Rank                                 1000 non-null   int64         \n",
      " 22  Value                                1000 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(5), int64(6), object(11)\n",
      "memory usage: 179.8+ KB\n"
     ]
    }
   ],
   "source": [
    "new_customer.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "45ad9d22",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>DOB</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "      <th>Unnamed: 18</th>\n",
       "      <th>Unnamed: 19</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chickie</td>\n",
       "      <td>Brister</td>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>1957-07-12</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "      <td>0.55</td>\n",
       "      <td>0.6875</td>\n",
       "      <td>0.859375</td>\n",
       "      <td>0.730469</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.718750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morly</td>\n",
       "      <td>Genery</td>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>1970-03-22</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>11</td>\n",
       "      <td>0.77</td>\n",
       "      <td>0.7700</td>\n",
       "      <td>0.962500</td>\n",
       "      <td>0.818125</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.718750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ardelis</td>\n",
       "      <td>Forrester</td>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>1974-08-28</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5</td>\n",
       "      <td>0.74</td>\n",
       "      <td>0.7400</td>\n",
       "      <td>0.740000</td>\n",
       "      <td>0.740000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.718750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lucine</td>\n",
       "      <td>Stutt</td>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>1979-01-28</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>1</td>\n",
       "      <td>0.79</td>\n",
       "      <td>0.9875</td>\n",
       "      <td>0.987500</td>\n",
       "      <td>0.987500</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.703125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melinda</td>\n",
       "      <td>Hadlee</td>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>1965-09-21</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.5700</td>\n",
       "      <td>0.712500</td>\n",
       "      <td>0.712500</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.703125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name  last_name  gender  past_3_years_bike_related_purchases  \\\n",
       "0    Chickie    Brister    Male                                   86   \n",
       "1      Morly     Genery    Male                                   69   \n",
       "2    Ardelis  Forrester  Female                                   10   \n",
       "3     Lucine      Stutt  Female                                   64   \n",
       "4    Melinda     Hadlee  Female                                   34   \n",
       "\n",
       "         DOB                   job_title job_industry_category  \\\n",
       "0 1957-07-12             General Manager         Manufacturing   \n",
       "1 1970-03-22         Structural Engineer              Property   \n",
       "2 1974-08-28      Senior Cost Accountant    Financial Services   \n",
       "3 1979-01-28  Account Representative III         Manufacturing   \n",
       "4 1965-09-21           Financial Analyst    Financial Services   \n",
       "\n",
       "      wealth_segment deceased_indicator owns_car  ...  state    country  \\\n",
       "0      Mass Customer                  N      Yes  ...    QLD  Australia   \n",
       "1      Mass Customer                  N       No  ...    NSW  Australia   \n",
       "2  Affluent Customer                  N       No  ...    VIC  Australia   \n",
       "3  Affluent Customer                  N      Yes  ...    QLD  Australia   \n",
       "4  Affluent Customer                  N       No  ...    NSW  Australia   \n",
       "\n",
       "   property_valuation Unnamed: 16 Unnamed: 17  Unnamed: 18  Unnamed: 19  \\\n",
       "0                   6        0.55      0.6875     0.859375     0.730469   \n",
       "1                  11        0.77      0.7700     0.962500     0.818125   \n",
       "2                   5        0.74      0.7400     0.740000     0.740000   \n",
       "3                   1        0.79      0.9875     0.987500     0.987500   \n",
       "4                   9        0.57      0.5700     0.712500     0.712500   \n",
       "\n",
       "   Unnamed: 20  Rank     Value  \n",
       "0            1     1  1.718750  \n",
       "1            1     1  1.718750  \n",
       "2            1     1  1.718750  \n",
       "3            4     4  1.703125  \n",
       "4            4     4  1.703125  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d6709792",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>DOB</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "      <th>Unnamed: 18</th>\n",
       "      <th>Unnamed: 19</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Ferdinand</td>\n",
       "      <td>Romanetti</td>\n",
       "      <td>Male</td>\n",
       "      <td>60</td>\n",
       "      <td>1959-10-07</td>\n",
       "      <td>Paralegal</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.6200</td>\n",
       "      <td>0.620000</td>\n",
       "      <td>0.620000</td>\n",
       "      <td>996</td>\n",
       "      <td>996</td>\n",
       "      <td>0.374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Burk</td>\n",
       "      <td>Wortley</td>\n",
       "      <td>Male</td>\n",
       "      <td>22</td>\n",
       "      <td>2001-10-17</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "      <td>0.89</td>\n",
       "      <td>0.8900</td>\n",
       "      <td>1.112500</td>\n",
       "      <td>0.945625</td>\n",
       "      <td>997</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Melloney</td>\n",
       "      <td>Temby</td>\n",
       "      <td>Female</td>\n",
       "      <td>17</td>\n",
       "      <td>1954-10-05</td>\n",
       "      <td>Budget/Accounting Analyst IV</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.8375</td>\n",
       "      <td>0.837500</td>\n",
       "      <td>0.837500</td>\n",
       "      <td>997</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>Dickie</td>\n",
       "      <td>Cubbini</td>\n",
       "      <td>Male</td>\n",
       "      <td>30</td>\n",
       "      <td>1952-12-17</td>\n",
       "      <td>Financial Advisor</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.8750</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>0.743750</td>\n",
       "      <td>997</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>Sylas</td>\n",
       "      <td>Duffill</td>\n",
       "      <td>Male</td>\n",
       "      <td>56</td>\n",
       "      <td>1955-10-02</td>\n",
       "      <td>Staff Accountant IV</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.5125</td>\n",
       "      <td>0.640625</td>\n",
       "      <td>0.544531</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.340</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    first_name  last_name  gender  past_3_years_bike_related_purchases  \\\n",
       "995  Ferdinand  Romanetti    Male                                   60   \n",
       "996       Burk    Wortley    Male                                   22   \n",
       "997   Melloney      Temby  Female                                   17   \n",
       "998     Dickie    Cubbini    Male                                   30   \n",
       "999      Sylas    Duffill    Male                                   56   \n",
       "\n",
       "           DOB                     job_title job_industry_category  \\\n",
       "995 1959-10-07                     Paralegal    Financial Services   \n",
       "996 2001-10-17        Senior Sales Associate                Health   \n",
       "997 1954-10-05  Budget/Accounting Analyst IV    Financial Services   \n",
       "998 1952-12-17             Financial Advisor    Financial Services   \n",
       "999 1955-10-02           Staff Accountant IV              Property   \n",
       "\n",
       "        wealth_segment deceased_indicator owns_car  ...  state    country  \\\n",
       "995  Affluent Customer                  N       No  ...    NSW  Australia   \n",
       "996      Mass Customer                  N       No  ...    NSW  Australia   \n",
       "997  Affluent Customer                  N      Yes  ...    QLD  Australia   \n",
       "998      Mass Customer                  N      Yes  ...    QLD  Australia   \n",
       "999      Mass Customer                  N      Yes  ...    NSW  Australia   \n",
       "\n",
       "     property_valuation Unnamed: 16 Unnamed: 17  Unnamed: 18  Unnamed: 19  \\\n",
       "995                   7        0.62      0.6200     0.620000     0.620000   \n",
       "996                  10        0.89      0.8900     1.112500     0.945625   \n",
       "997                   2        0.67      0.8375     0.837500     0.837500   \n",
       "998                   2        0.70      0.8750     0.875000     0.743750   \n",
       "999                   9        0.41      0.5125     0.640625     0.544531   \n",
       "\n",
       "     Unnamed: 20  Rank  Value  \n",
       "995          996   996  0.374  \n",
       "996          997   997  0.357  \n",
       "997          997   997  0.357  \n",
       "998          997   997  0.357  \n",
       "999         1000  1000  0.340  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "73eda94b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "first_name                               0\n",
       "last_name                               29\n",
       "gender                                   0\n",
       "past_3_years_bike_related_purchases      0\n",
       "DOB                                     17\n",
       "job_title                              106\n",
       "job_industry_category                  165\n",
       "wealth_segment                           0\n",
       "deceased_indicator                       0\n",
       "owns_car                                 0\n",
       "tenure                                   0\n",
       "address                                  0\n",
       "postcode                                 0\n",
       "state                                    0\n",
       "country                                  0\n",
       "property_valuation                       0\n",
       "Unnamed: 16                              0\n",
       "Unnamed: 17                              0\n",
       "Unnamed: 18                              0\n",
       "Unnamed: 19                              0\n",
       "Unnamed: 20                              0\n",
       "Rank                                     0\n",
       "Value                                    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b75c9c63",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the unnamed columns with unknown datapoints\n",
    "del new_customer['Unnamed: 16']\n",
    "del new_customer['Unnamed: 17']\n",
    "del new_customer['Unnamed: 18']\n",
    "del new_customer['Unnamed: 19']\n",
    "del new_customer['Unnamed: 20']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "88e991c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 18 columns):\n",
      " #   Column                               Non-Null Count  Dtype         \n",
      "---  ------                               --------------  -----         \n",
      " 0   first_name                           1000 non-null   object        \n",
      " 1   last_name                            971 non-null    object        \n",
      " 2   gender                               1000 non-null   object        \n",
      " 3   past_3_years_bike_related_purchases  1000 non-null   int64         \n",
      " 4   DOB                                  983 non-null    datetime64[ns]\n",
      " 5   job_title                            894 non-null    object        \n",
      " 6   job_industry_category                835 non-null    object        \n",
      " 7   wealth_segment                       1000 non-null   object        \n",
      " 8   deceased_indicator                   1000 non-null   object        \n",
      " 9   owns_car                             1000 non-null   object        \n",
      " 10  tenure                               1000 non-null   int64         \n",
      " 11  address                              1000 non-null   object        \n",
      " 12  postcode                             1000 non-null   int64         \n",
      " 13  state                                1000 non-null   object        \n",
      " 14  country                              1000 non-null   object        \n",
      " 15  property_valuation                   1000 non-null   int64         \n",
      " 16  Rank                                 1000 non-null   int64         \n",
      " 17  Value                                1000 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(1), int64(5), object(11)\n",
      "memory usage: 140.8+ KB\n"
     ]
    }
   ],
   "source": [
    "new_customer.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "72b4290e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filling in the missing value for last_name\n",
    "new_customer['last_name'].fillna(value = 'Missing', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e15f47bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Female    513\n",
       "Male      470\n",
       "U          17\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the entered data\n",
    "new_customer['gender'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2e4502a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NSW    506\n",
       "VIC    266\n",
       "QLD    228\n",
       "Name: state, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the entered data\n",
    "new_customer['state'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7bdbc6ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Australia    1000\n",
       "Name: country, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the entered data\n",
    "new_customer['country'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9ce7f063",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the entered data\n",
    "new_customer['address'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d4e1f6ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1957-07-12\n",
       "1      1970-03-22\n",
       "2      1974-08-28\n",
       "3      1979-01-28\n",
       "4      1965-09-21\n",
       "          ...    \n",
       "995    1959-10-07\n",
       "996    2001-10-17\n",
       "997    1954-10-05\n",
       "998    1952-12-17\n",
       "999    1955-10-02\n",
       "Name: DOB, Length: 1000, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make sure all dates are in the correct format\n",
    "new_customer['DOB'] = new_customer['DOB'].dt.date\n",
    "new_customer['DOB']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "309c7cdd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Associate Professor         15\n",
       "Environmental Tech          14\n",
       "Software Consultant         14\n",
       "Chief Design Engineer       13\n",
       "Assistant Manager           12\n",
       "                            ..\n",
       "Accountant II                1\n",
       "Programmer IV                1\n",
       "Administrative Officer       1\n",
       "Accounting Assistant III     1\n",
       "Web Developer I              1\n",
       "Name: job_title, Length: 184, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the frequency of different job titles\n",
    "new_customer['job_title'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "aa9f150d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Associate Professor'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer['job_title'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "98b5cc9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_customer['job_title'].fillna(new_customer['job_title'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c38abccf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Financial Services    203\n",
       "Manufacturing         199\n",
       "Health                152\n",
       "Retail                 78\n",
       "Property               64\n",
       "IT                     51\n",
       "Entertainment          37\n",
       "Argiculture            26\n",
       "Telecommunications     25\n",
       "Name: job_industry_category, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the frequency of job industries\n",
    "new_customer['job_industry_category'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "dd7b0936",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Financial Services'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer['job_industry_category'].mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0b1a2ed2",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_customer['job_industry_category'].fillna(new_customer['job_industry_category'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "414662af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make sure that the column names are in the correct format\n",
    "new_customer.columns = map(str.lower, new_customer.columns)\n",
    "new_customer.columns = map(str.strip, new_customer.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ba148dca",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>dob</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>rank</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chickie</td>\n",
       "      <td>Brister</td>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>1957-07-12</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>45 Shopko Center</td>\n",
       "      <td>4500</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morly</td>\n",
       "      <td>Genery</td>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>1970-03-22</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>14 Mccormick Park</td>\n",
       "      <td>2113</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>1.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ardelis</td>\n",
       "      <td>Forrester</td>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>1974-08-28</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>10</td>\n",
       "      <td>5 Colorado Crossing</td>\n",
       "      <td>3505</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lucine</td>\n",
       "      <td>Stutt</td>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>1979-01-28</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "      <td>207 Annamark Plaza</td>\n",
       "      <td>4814</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melinda</td>\n",
       "      <td>Hadlee</td>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>1965-09-21</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>19</td>\n",
       "      <td>115 Montana Place</td>\n",
       "      <td>2093</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>1.703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Ferdinand</td>\n",
       "      <td>Romanetti</td>\n",
       "      <td>Male</td>\n",
       "      <td>60</td>\n",
       "      <td>1959-10-07</td>\n",
       "      <td>Paralegal</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>9</td>\n",
       "      <td>2 Sloan Way</td>\n",
       "      <td>2200</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7</td>\n",
       "      <td>996</td>\n",
       "      <td>0.374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Burk</td>\n",
       "      <td>Wortley</td>\n",
       "      <td>Male</td>\n",
       "      <td>22</td>\n",
       "      <td>2001-10-17</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>6</td>\n",
       "      <td>04 Union Crossing</td>\n",
       "      <td>2196</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Melloney</td>\n",
       "      <td>Temby</td>\n",
       "      <td>Female</td>\n",
       "      <td>17</td>\n",
       "      <td>1954-10-05</td>\n",
       "      <td>Budget/Accounting Analyst IV</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>15</td>\n",
       "      <td>33475 Fair Oaks Junction</td>\n",
       "      <td>4702</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>Dickie</td>\n",
       "      <td>Cubbini</td>\n",
       "      <td>Male</td>\n",
       "      <td>30</td>\n",
       "      <td>1952-12-17</td>\n",
       "      <td>Financial Advisor</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>19</td>\n",
       "      <td>57666 Victoria Way</td>\n",
       "      <td>4215</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>997</td>\n",
       "      <td>0.357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>Sylas</td>\n",
       "      <td>Duffill</td>\n",
       "      <td>Male</td>\n",
       "      <td>56</td>\n",
       "      <td>1955-10-02</td>\n",
       "      <td>Staff Accountant IV</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>21875 Grover Drive</td>\n",
       "      <td>2010</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.340</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    first_name  last_name  gender  past_3_years_bike_related_purchases  \\\n",
       "0      Chickie    Brister    Male                                   86   \n",
       "1        Morly     Genery    Male                                   69   \n",
       "2      Ardelis  Forrester  Female                                   10   \n",
       "3       Lucine      Stutt  Female                                   64   \n",
       "4      Melinda     Hadlee  Female                                   34   \n",
       "..         ...        ...     ...                                  ...   \n",
       "995  Ferdinand  Romanetti    Male                                   60   \n",
       "996       Burk    Wortley    Male                                   22   \n",
       "997   Melloney      Temby  Female                                   17   \n",
       "998     Dickie    Cubbini    Male                                   30   \n",
       "999      Sylas    Duffill    Male                                   56   \n",
       "\n",
       "            dob                     job_title job_industry_category  \\\n",
       "0    1957-07-12               General Manager         Manufacturing   \n",
       "1    1970-03-22           Structural Engineer              Property   \n",
       "2    1974-08-28        Senior Cost Accountant    Financial Services   \n",
       "3    1979-01-28    Account Representative III         Manufacturing   \n",
       "4    1965-09-21             Financial Analyst    Financial Services   \n",
       "..          ...                           ...                   ...   \n",
       "995  1959-10-07                     Paralegal    Financial Services   \n",
       "996  2001-10-17        Senior Sales Associate                Health   \n",
       "997  1954-10-05  Budget/Accounting Analyst IV    Financial Services   \n",
       "998  1952-12-17             Financial Advisor    Financial Services   \n",
       "999  1955-10-02           Staff Accountant IV              Property   \n",
       "\n",
       "        wealth_segment deceased_indicator owns_car  tenure  \\\n",
       "0        Mass Customer                  N      Yes      14   \n",
       "1        Mass Customer                  N       No      16   \n",
       "2    Affluent Customer                  N       No      10   \n",
       "3    Affluent Customer                  N      Yes       5   \n",
       "4    Affluent Customer                  N       No      19   \n",
       "..                 ...                ...      ...     ...   \n",
       "995  Affluent Customer                  N       No       9   \n",
       "996      Mass Customer                  N       No       6   \n",
       "997  Affluent Customer                  N      Yes      15   \n",
       "998      Mass Customer                  N      Yes      19   \n",
       "999      Mass Customer                  N      Yes      14   \n",
       "\n",
       "                      address  postcode state    country  property_valuation  \\\n",
       "0            45 Shopko Center      4500   QLD  Australia                   6   \n",
       "1           14 Mccormick Park      2113   NSW  Australia                  11   \n",
       "2         5 Colorado Crossing      3505   VIC  Australia                   5   \n",
       "3          207 Annamark Plaza      4814   QLD  Australia                   1   \n",
       "4           115 Montana Place      2093   NSW  Australia                   9   \n",
       "..                        ...       ...   ...        ...                 ...   \n",
       "995               2 Sloan Way      2200   NSW  Australia                   7   \n",
       "996         04 Union Crossing      2196   NSW  Australia                  10   \n",
       "997  33475 Fair Oaks Junction      4702   QLD  Australia                   2   \n",
       "998        57666 Victoria Way      4215   QLD  Australia                   2   \n",
       "999        21875 Grover Drive      2010   NSW  Australia                   9   \n",
       "\n",
       "     rank  value  \n",
       "0       1  1.719  \n",
       "1       1  1.719  \n",
       "2       1  1.719  \n",
       "3       4  1.703  \n",
       "4       4  1.703  \n",
       "..    ...    ...  \n",
       "995   996  0.374  \n",
       "996   997  0.357  \n",
       "997   997  0.357  \n",
       "998   997  0.357  \n",
       "999  1000  0.340  \n",
       "\n",
       "[1000 rows x 18 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# round the values to the correct format\n",
    "new_customer['value'] = new_customer['value'].round(3)\n",
    "new_customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b95a8820",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 18 columns):\n",
      " #   Column                               Non-Null Count  Dtype  \n",
      "---  ------                               --------------  -----  \n",
      " 0   first_name                           1000 non-null   object \n",
      " 1   last_name                            1000 non-null   object \n",
      " 2   gender                               1000 non-null   object \n",
      " 3   past_3_years_bike_related_purchases  1000 non-null   int64  \n",
      " 4   dob                                  983 non-null    object \n",
      " 5   job_title                            1000 non-null   object \n",
      " 6   job_industry_category                1000 non-null   object \n",
      " 7   wealth_segment                       1000 non-null   object \n",
      " 8   deceased_indicator                   1000 non-null   object \n",
      " 9   owns_car                             1000 non-null   object \n",
      " 10  tenure                               1000 non-null   int64  \n",
      " 11  address                              1000 non-null   object \n",
      " 12  postcode                             1000 non-null   int64  \n",
      " 13  state                                1000 non-null   object \n",
      " 14  country                              1000 non-null   object \n",
      " 15  property_valuation                   1000 non-null   int64  \n",
      " 16  rank                                 1000 non-null   int64  \n",
      " 17  value                                1000 non-null   float64\n",
      "dtypes: float64(1), int64(5), object(12)\n",
      "memory usage: 140.8+ KB\n"
     ]
    }
   ],
   "source": [
    "new_customer.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6f2e2acf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "first_name                              0\n",
       "last_name                               0\n",
       "gender                                  0\n",
       "past_3_years_bike_related_purchases     0\n",
       "dob                                    17\n",
       "job_title                               0\n",
       "job_industry_category                   0\n",
       "wealth_segment                          0\n",
       "deceased_indicator                      0\n",
       "owns_car                                0\n",
       "tenure                                  0\n",
       "address                                 0\n",
       "postcode                                0\n",
       "state                                   0\n",
       "country                                 0\n",
       "property_valuation                      0\n",
       "rank                                    0\n",
       "value                                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_customer.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6c595f60",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print to csv file after the cleaning\n",
    "new_customer.to_csv('./new_customer_cleaned.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}