KPMGtask2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trans = pd.read_excel(\"KPMG_VI_New_raw_data_update_final.xlsx\", sheet_name = 'Transactions', header = 1, skiprows = 0, parse_dates = [12])\n",
    "df_new_customer_list = pd.read_excel(\"KPMG_VI_New_raw_data_update_final.xlsx\",sheet_name='NewCustomerList', header = 1, skiprows = 0)\n",
    "df_customer_demographics = pd.read_excel(\"KPMG_VI_New_raw_data_update_final.xlsx\",sheet_name='CustomerDemographic', header = 1, skiprows = 0)\n",
    "df_customer_address = pd.read_excel(\"KPMG_VI_New_raw_data_update_final.xlsx\", sheet_name= 'CustomerAddress', header = 1, skiprows = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20000, 13)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "now = pd.Timestamp('now')\n",
    "df_customer_demographics['DOB'] = pd.to_datetime(df_customer_demographics['DOB'], format='%m%d%y')    # 1\n",
    "df_customer_demographics['DOB'] = df_customer_demographics['DOB'].where(df_customer_demographics['DOB'] < now, df_customer_demographics['DOB'] -  np.timedelta64(100, 'Y'))   # 2\n",
    "df_customer_demographics['age'] = (now - df_customer_demographics['DOB']).astype('<m8[Y]')    # 3\n",
    "df_customer_demographics['age'] -= 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-02-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.62</td>\n",
       "      <td>41245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-05-21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Trek Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>2091.47</td>\n",
       "      <td>388.92</td>\n",
       "      <td>41701.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>402</td>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>OHM Cycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>low</td>\n",
       "      <td>medium</td>\n",
       "      <td>1793.43</td>\n",
       "      <td>248.82</td>\n",
       "      <td>36361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>88</td>\n",
       "      <td>3135</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>1198.46</td>\n",
       "      <td>381.10</td>\n",
       "      <td>36145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>78</td>\n",
       "      <td>787</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>1765.30</td>\n",
       "      <td>709.48</td>\n",
       "      <td>42226.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "0               1           2         2950       2017-02-25           0.0   \n",
       "1               2           3         3120       2017-05-21           1.0   \n",
       "2               3          37          402       2017-10-16           0.0   \n",
       "3               4          88         3135       2017-08-31           0.0   \n",
       "4               5          78          787       2017-10-01           1.0   \n",
       "\n",
       "  order_status           brand product_line product_class product_size  \\\n",
       "0     Approved           Solex     Standard        medium       medium   \n",
       "1     Approved   Trek Bicycles     Standard        medium        large   \n",
       "2     Approved      OHM Cycles     Standard           low       medium   \n",
       "3     Approved  Norco Bicycles     Standard        medium       medium   \n",
       "4     Approved  Giant Bicycles     Standard        medium        large   \n",
       "\n",
       "   list_price  standard_cost  product_first_sold_date  \n",
       "0       71.49          53.62                  41245.0  \n",
       "1     2091.47         388.92                  41701.0  \n",
       "2     1793.43         248.82                  36361.0  \n",
       "3     1198.46         381.10                  36145.0  \n",
       "4     1765.30         709.48                  42226.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20000 entries, 0 to 19999\n",
      "Data columns (total 13 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   transaction_id           20000 non-null  int64         \n",
      " 1   product_id               20000 non-null  int64         \n",
      " 2   customer_id              20000 non-null  int64         \n",
      " 3   transaction_date         20000 non-null  datetime64[ns]\n",
      " 4   online_order             19640 non-null  float64       \n",
      " 5   order_status             20000 non-null  object        \n",
      " 6   brand                    19803 non-null  object        \n",
      " 7   product_line             19803 non-null  object        \n",
      " 8   product_class            19803 non-null  object        \n",
      " 9   product_size             19803 non-null  object        \n",
      " 10  list_price               20000 non-null  float64       \n",
      " 11  standard_cost            19803 non-null  float64       \n",
      " 12  product_first_sold_date  19803 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(4), int64(3), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_trans.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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>online_order</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>20000.000000</td>\n",
       "      <td>20000.00000</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>19640.000000</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>19803.000000</td>\n",
       "      <td>19803.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>10000.500000</td>\n",
       "      <td>45.36465</td>\n",
       "      <td>1738.246050</td>\n",
       "      <td>0.500458</td>\n",
       "      <td>1107.829449</td>\n",
       "      <td>556.046951</td>\n",
       "      <td>38199.776549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5773.647028</td>\n",
       "      <td>30.75359</td>\n",
       "      <td>1011.951046</td>\n",
       "      <td>0.500013</td>\n",
       "      <td>582.825242</td>\n",
       "      <td>405.955660</td>\n",
       "      <td>2875.201110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.010000</td>\n",
       "      <td>7.210000</td>\n",
       "      <td>33259.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5000.750000</td>\n",
       "      <td>18.00000</td>\n",
       "      <td>857.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>575.270000</td>\n",
       "      <td>215.140000</td>\n",
       "      <td>35667.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>10000.500000</td>\n",
       "      <td>44.00000</td>\n",
       "      <td>1736.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1163.890000</td>\n",
       "      <td>507.580000</td>\n",
       "      <td>38216.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>15000.250000</td>\n",
       "      <td>72.00000</td>\n",
       "      <td>2613.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1635.300000</td>\n",
       "      <td>795.100000</td>\n",
       "      <td>40672.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>20000.000000</td>\n",
       "      <td>100.00000</td>\n",
       "      <td>5034.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2091.470000</td>\n",
       "      <td>1759.850000</td>\n",
       "      <td>42710.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       transaction_id   product_id   customer_id  online_order    list_price  \\\n",
       "count    20000.000000  20000.00000  20000.000000  19640.000000  20000.000000   \n",
       "mean     10000.500000     45.36465   1738.246050      0.500458   1107.829449   \n",
       "std       5773.647028     30.75359   1011.951046      0.500013    582.825242   \n",
       "min          1.000000      0.00000      1.000000      0.000000     12.010000   \n",
       "25%       5000.750000     18.00000    857.750000      0.000000    575.270000   \n",
       "50%      10000.500000     44.00000   1736.000000      1.000000   1163.890000   \n",
       "75%      15000.250000     72.00000   2613.000000      1.000000   1635.300000   \n",
       "max      20000.000000    100.00000   5034.000000      1.000000   2091.470000   \n",
       "\n",
       "       standard_cost  product_first_sold_date  \n",
       "count   19803.000000             19803.000000  \n",
       "mean      556.046951             38199.776549  \n",
       "std       405.955660              2875.201110  \n",
       "min         7.210000             33259.000000  \n",
       "25%       215.140000             35667.000000  \n",
       "50%       507.580000             38216.000000  \n",
       "75%       795.100000             40672.000000  \n",
       "max      1759.850000             42710.000000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "transaction_id               0\n",
       "product_id                   0\n",
       "customer_id                  0\n",
       "transaction_date             0\n",
       "online_order               360\n",
       "order_status                 0\n",
       "brand                      197\n",
       "product_line               197\n",
       "product_class              197\n",
       "product_size               197\n",
       "list_price                   0\n",
       "standard_cost              197\n",
       "product_first_sold_date    197\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2017-12-30 00:00:00')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['transaction_date'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13882    33259.0\n",
       "13411    33259.0\n",
       "1548     33259.0\n",
       "11494    33259.0\n",
       "10036    33259.0\n",
       "          ...   \n",
       "19340        NaN\n",
       "19383        NaN\n",
       "19793        NaN\n",
       "19859        NaN\n",
       "19871        NaN\n",
       "Name: product_first_sold_date, Length: 20000, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['product_first_sold_date'].sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34586.0    162\n",
       "37659.0    163\n",
       "41922.0    166\n",
       "42404.0    168\n",
       "41848.0    169\n",
       "          ... \n",
       "38216.0    220\n",
       "39880.0    222\n",
       "37823.0    227\n",
       "41064.0    229\n",
       "33879.0    234\n",
       "Name: product_first_sold_date, Length: 100, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['product_first_sold_date'].value_counts(ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_trans['diff_time'] =  df_trans['transaction_date'] - df_trans['product_first_sold_date'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_trans.drop(df_trans['diff_time'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Standard    14176\n",
       "Road         3970\n",
       "Touring      1234\n",
       "Mountain      423\n",
       "Name: product_line, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['product_line'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Approved     19821\n",
       "Cancelled      179\n",
       "Name: order_status, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['order_status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medium    13826\n",
       "high       3013\n",
       "low        2964\n",
       "NaN         197\n",
       "Name: product_class, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['product_class'].value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medium    12990\n",
       "large      3976\n",
       "small      2837\n",
       "Name: product_size, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans['product_size'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trans.drop_duplicates(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20000, 13)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trans['profit'] = (df_trans['list_price'] - df_trans['standard_cost'])*100/df_trans['standard_cost']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>list_price</th>\n",
       "      <th>standard_cost</th>\n",
       "      <th>product_first_sold_date</th>\n",
       "      <th>profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-02-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>71.49</td>\n",
       "      <td>53.62</td>\n",
       "      <td>41245.0</td>\n",
       "      <td>33.327117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-05-21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Trek Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>2091.47</td>\n",
       "      <td>388.92</td>\n",
       "      <td>41701.0</td>\n",
       "      <td>437.763550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>402</td>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>OHM Cycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>low</td>\n",
       "      <td>medium</td>\n",
       "      <td>1793.43</td>\n",
       "      <td>248.82</td>\n",
       "      <td>36361.0</td>\n",
       "      <td>620.774054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>88</td>\n",
       "      <td>3135</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Norco Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>1198.46</td>\n",
       "      <td>381.10</td>\n",
       "      <td>36145.0</td>\n",
       "      <td>214.473891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>78</td>\n",
       "      <td>787</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>1765.30</td>\n",
       "      <td>709.48</td>\n",
       "      <td>42226.0</td>\n",
       "      <td>148.816034</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "0               1           2         2950       2017-02-25           0.0   \n",
       "1               2           3         3120       2017-05-21           1.0   \n",
       "2               3          37          402       2017-10-16           0.0   \n",
       "3               4          88         3135       2017-08-31           0.0   \n",
       "4               5          78          787       2017-10-01           1.0   \n",
       "\n",
       "  order_status           brand product_line product_class product_size  \\\n",
       "0     Approved           Solex     Standard        medium       medium   \n",
       "1     Approved   Trek Bicycles     Standard        medium        large   \n",
       "2     Approved      OHM Cycles     Standard           low       medium   \n",
       "3     Approved  Norco Bicycles     Standard        medium       medium   \n",
       "4     Approved  Giant Bicycles     Standard        medium        large   \n",
       "\n",
       "   list_price  standard_cost  product_first_sold_date      profit  \n",
       "0       71.49          53.62                  41245.0   33.327117  \n",
       "1     2091.47         388.92                  41701.0  437.763550  \n",
       "2     1793.43         248.82                  36361.0  620.774054  \n",
       "3     1198.46         381.10                  36145.0  214.473891  \n",
       "4     1765.30         709.48                  42226.0  148.816034  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "transaction_id               0\n",
       "product_id                   0\n",
       "customer_id                  0\n",
       "transaction_date             0\n",
       "online_order               360\n",
       "order_status                 0\n",
       "brand                      197\n",
       "product_line               197\n",
       "product_class              197\n",
       "product_size               197\n",
       "list_price                   0\n",
       "standard_cost              197\n",
       "product_first_sold_date    197\n",
       "profit                     197\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>past_3_years_bike_related_purchases</th>\n",
       "      <th>tenure</th>\n",
       "      <th>postcode</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>count</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>49.836000</td>\n",
       "      <td>11.388000</td>\n",
       "      <td>3019.227000</td>\n",
       "      <td>7.397000</td>\n",
       "      <td>0.741750</td>\n",
       "      <td>0.832570</td>\n",
       "      <td>0.935724</td>\n",
       "      <td>0.864437</td>\n",
       "      <td>498.819000</td>\n",
       "      <td>498.819000</td>\n",
       "      <td>0.881714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>27.796686</td>\n",
       "      <td>5.037145</td>\n",
       "      <td>848.895767</td>\n",
       "      <td>2.758804</td>\n",
       "      <td>0.203532</td>\n",
       "      <td>0.245927</td>\n",
       "      <td>0.292172</td>\n",
       "      <td>0.278420</td>\n",
       "      <td>288.810997</td>\n",
       "      <td>288.810997</td>\n",
       "      <td>0.293525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.340000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.340000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>26.750000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>2209.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.570000</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.690000</td>\n",
       "      <td>0.648125</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>0.649531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>51.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>2800.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>0.730000</td>\n",
       "      <td>0.812500</td>\n",
       "      <td>0.910000</td>\n",
       "      <td>0.846875</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>0.860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3845.500000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.920000</td>\n",
       "      <td>1.020000</td>\n",
       "      <td>1.150000</td>\n",
       "      <td>1.062500</td>\n",
       "      <td>750.250000</td>\n",
       "      <td>750.250000</td>\n",
       "      <td>1.075000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>99.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>4879.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>1.100000</td>\n",
       "      <td>1.375000</td>\n",
       "      <td>1.718750</td>\n",
       "      <td>1.718750</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1.718750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       past_3_years_bike_related_purchases       tenure     postcode  \\\n",
       "count                          1000.000000  1000.000000  1000.000000   \n",
       "mean                             49.836000    11.388000  3019.227000   \n",
       "std                              27.796686     5.037145   848.895767   \n",
       "min                               0.000000     0.000000  2000.000000   \n",
       "25%                              26.750000     7.000000  2209.000000   \n",
       "50%                              51.000000    11.000000  2800.000000   \n",
       "75%                              72.000000    15.000000  3845.500000   \n",
       "max                              99.000000    22.000000  4879.000000   \n",
       "\n",
       "       property_valuation  Unnamed: 16  Unnamed: 17  Unnamed: 18  Unnamed: 19  \\\n",
       "count         1000.000000  1000.000000  1000.000000  1000.000000  1000.000000   \n",
       "mean             7.397000     0.741750     0.832570     0.935724     0.864437   \n",
       "std              2.758804     0.203532     0.245927     0.292172     0.278420   \n",
       "min              1.000000     0.400000     0.400000     0.400000     0.340000   \n",
       "25%              6.000000     0.570000     0.625000     0.690000     0.648125   \n",
       "50%              8.000000     0.730000     0.812500     0.910000     0.846875   \n",
       "75%              9.000000     0.920000     1.020000     1.150000     1.062500   \n",
       "max             12.000000     1.100000     1.375000     1.718750     1.718750   \n",
       "\n",
       "       Unnamed: 20         Rank        Value  \n",
       "count  1000.000000  1000.000000  1000.000000  \n",
       "mean    498.819000   498.819000     0.881714  \n",
       "std     288.810997   288.810997     0.293525  \n",
       "min       1.000000     1.000000     0.340000  \n",
       "25%     250.000000   250.000000     0.649531  \n",
       "50%     500.000000   500.000000     0.860000  \n",
       "75%     750.250000   750.250000     1.075000  \n",
       "max    1000.000000  1000.000000     1.718750  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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": [
    "df_new_customer_list.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new_customer_list.drop(['Unnamed: 16','Unnamed: 17','Unnamed: 18','Unnamed: 19','Unnamed: 20'], axis = 1, inplace = True)"
   ]
  },
  {
   "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>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.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>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.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>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.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>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.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>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.703125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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  tenure              address  \\\n",
       "0      Mass Customer                  N      Yes      14     45 Shopko Center   \n",
       "1      Mass Customer                  N       No      16    14 Mccormick Park   \n",
       "2  Affluent Customer                  N       No      10  5 Colorado Crossing   \n",
       "3  Affluent Customer                  N      Yes       5   207 Annamark Plaza   \n",
       "4  Affluent Customer                  N       No      19    115 Montana Place   \n",
       "\n",
       "   postcode state    country  property_valuation  Rank     Value  \n",
       "0      4500   QLD  Australia                   6     1  1.718750  \n",
       "1      2113   NSW  Australia                  11     1  1.718750  \n",
       "2      3505   VIC  Australia                   5     1  1.718750  \n",
       "3      4814   QLD  Australia                   1     4  1.703125  \n",
       "4      2093   NSW  Australia                   9     4  1.703125  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list.head()"
   ]
  },
  {
   "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>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.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>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.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>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.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>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.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>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.703125</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.374000</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.357000</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.357000</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.357000</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.340000</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.718750  \n",
       "1       1  1.718750  \n",
       "2       1  1.718750  \n",
       "3       4  1.703125  \n",
       "4       4  1.703125  \n",
       "..    ...       ...  \n",
       "995   996  0.374000  \n",
       "996   997  0.357000  \n",
       "997   997  0.357000  \n",
       "998   997  0.357000  \n",
       "999  1000  0.340000  \n",
       "\n",
       "[1000 rows x 18 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Female    513\n",
       "Male      470\n",
       "U          17\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list['gender'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new_customer_list['gender'].replace(to_replace= 'U', value = np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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                               983 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": [
    "df_new_customer_list.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>past_3_years_bike_related_purchases</th>\n",
       "      <th>tenure</th>\n",
       "      <th>postcode</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>49.836000</td>\n",
       "      <td>11.388000</td>\n",
       "      <td>3019.227000</td>\n",
       "      <td>7.397000</td>\n",
       "      <td>498.819000</td>\n",
       "      <td>0.881714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>27.796686</td>\n",
       "      <td>5.037145</td>\n",
       "      <td>848.895767</td>\n",
       "      <td>2.758804</td>\n",
       "      <td>288.810997</td>\n",
       "      <td>0.293525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.340000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>26.750000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>2209.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>0.649531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>51.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>2800.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>0.860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>3845.500000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>750.250000</td>\n",
       "      <td>1.075000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>99.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>4879.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1.718750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       past_3_years_bike_related_purchases       tenure     postcode  \\\n",
       "count                          1000.000000  1000.000000  1000.000000   \n",
       "mean                             49.836000    11.388000  3019.227000   \n",
       "std                              27.796686     5.037145   848.895767   \n",
       "min                               0.000000     0.000000  2000.000000   \n",
       "25%                              26.750000     7.000000  2209.000000   \n",
       "50%                              51.000000    11.000000  2800.000000   \n",
       "75%                              72.000000    15.000000  3845.500000   \n",
       "max                              99.000000    22.000000  4879.000000   \n",
       "\n",
       "       property_valuation         Rank        Value  \n",
       "count         1000.000000  1000.000000  1000.000000  \n",
       "mean             7.397000   498.819000     0.881714  \n",
       "std              2.758804   288.810997     0.293525  \n",
       "min              1.000000     1.000000     0.340000  \n",
       "25%              6.000000   250.000000     0.649531  \n",
       "50%              8.000000   500.000000     0.860000  \n",
       "75%              9.000000   750.250000     1.075000  \n",
       "max             12.000000  1000.000000     1.718750  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Australia'], dtype=object)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list['country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['QLD', 'NSW', 'VIC'], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list['state'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list['job_industry_category'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N    3998\n",
       "Y       2\n",
       "Name: deceased_indicator, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['deceased_indicator'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4000, 13)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>tenure</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4000.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>3913.000000</td>\n",
       "      <td>3913.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2000.500000</td>\n",
       "      <td>48.890000</td>\n",
       "      <td>10.657041</td>\n",
       "      <td>40.218502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1154.844867</td>\n",
       "      <td>28.715005</td>\n",
       "      <td>5.660146</td>\n",
       "      <td>12.799741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1000.750000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2000.500000</td>\n",
       "      <td>48.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3000.250000</td>\n",
       "      <td>73.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>50.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4000.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>174.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       customer_id  past_3_years_bike_related_purchases       tenure  \\\n",
       "count  4000.000000                          4000.000000  3913.000000   \n",
       "mean   2000.500000                            48.890000    10.657041   \n",
       "std    1154.844867                            28.715005     5.660146   \n",
       "min       1.000000                             0.000000     1.000000   \n",
       "25%    1000.750000                            24.000000     6.000000   \n",
       "50%    2000.500000                            48.000000    11.000000   \n",
       "75%    3000.250000                            73.000000    15.000000   \n",
       "max    4000.000000                            99.000000    22.000000   \n",
       "\n",
       "               age  \n",
       "count  3913.000000  \n",
       "mean     40.218502  \n",
       "std      12.799741  \n",
       "min      16.000000  \n",
       "25%      31.000000  \n",
       "50%      40.000000  \n",
       "75%      50.000000  \n",
       "max     174.000000  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4000 entries, 0 to 3999\n",
      "Data columns (total 13 columns):\n",
      " #   Column                               Non-Null Count  Dtype         \n",
      "---  ------                               --------------  -----         \n",
      " 0   customer_id                          4000 non-null   int64         \n",
      " 1   first_name                           4000 non-null   object        \n",
      " 2   last_name                            3875 non-null   object        \n",
      " 3   gender                               4000 non-null   object        \n",
      " 4   past_3_years_bike_related_purchases  4000 non-null   int64         \n",
      " 5   DOB                                  3913 non-null   datetime64[ns]\n",
      " 6   job_title                            3494 non-null   object        \n",
      " 7   job_industry_category                3344 non-null   object        \n",
      " 8   wealth_segment                       4000 non-null   object        \n",
      " 9   deceased_indicator                   4000 non-null   object        \n",
      " 10  owns_car                             4000 non-null   object        \n",
      " 11  tenure                               3913 non-null   float64       \n",
      " 12  age                                  3913 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(2), object(8)\n",
      "memory usage: 406.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_customer_demographics.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 18)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['F', 'Male', 'Female', 'U', 'Femal', 'M'], dtype=object)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['DOB'].min()\n",
    "df_customer_demographics['gender'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer_demographics['gender'].replace({'Male':'M','Female':'F','Femal':'F','U':np.nan},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>customer_id</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>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Laraine</td>\n",
       "      <td>Medendorp</td>\n",
       "      <td>F</td>\n",
       "      <td>93</td>\n",
       "      <td>1953-10-12</td>\n",
       "      <td>Executive Secretary</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Eli</td>\n",
       "      <td>Bockman</td>\n",
       "      <td>M</td>\n",
       "      <td>81</td>\n",
       "      <td>1980-12-16</td>\n",
       "      <td>Administrative Officer</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>16.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Arlin</td>\n",
       "      <td>Dearle</td>\n",
       "      <td>M</td>\n",
       "      <td>61</td>\n",
       "      <td>1954-01-20</td>\n",
       "      <td>Recruiting Manager</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>15.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Talbot</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>33</td>\n",
       "      <td>1961-10-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IT</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>7.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Sheila-kathryn</td>\n",
       "      <td>Calton</td>\n",
       "      <td>F</td>\n",
       "      <td>56</td>\n",
       "      <td>1977-05-13</td>\n",
       "      <td>Senior Editor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>8.0</td>\n",
       "      <td>40.0</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>3995</th>\n",
       "      <td>3996</td>\n",
       "      <td>Rosalia</td>\n",
       "      <td>Halgarth</td>\n",
       "      <td>F</td>\n",
       "      <td>8</td>\n",
       "      <td>1975-08-09</td>\n",
       "      <td>VP Product Management</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>19.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3996</th>\n",
       "      <td>3997</td>\n",
       "      <td>Blanch</td>\n",
       "      <td>Nisuis</td>\n",
       "      <td>F</td>\n",
       "      <td>87</td>\n",
       "      <td>2001-07-13</td>\n",
       "      <td>Statistician II</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>High Net Worth</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3997</th>\n",
       "      <td>3998</td>\n",
       "      <td>Sarene</td>\n",
       "      <td>Woolley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Assistant Manager</td>\n",
       "      <td>IT</td>\n",
       "      <td>High Net Worth</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3998</th>\n",
       "      <td>3999</td>\n",
       "      <td>Patrizius</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>11</td>\n",
       "      <td>1973-10-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999</th>\n",
       "      <td>4000</td>\n",
       "      <td>Kippy</td>\n",
       "      <td>Oldland</td>\n",
       "      <td>M</td>\n",
       "      <td>76</td>\n",
       "      <td>1991-11-05</td>\n",
       "      <td>Software Engineer IV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>11.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id      first_name  last_name gender  \\\n",
       "0               1         Laraine  Medendorp      F   \n",
       "1               2             Eli    Bockman      M   \n",
       "2               3           Arlin     Dearle      M   \n",
       "3               4          Talbot        NaN      M   \n",
       "4               5  Sheila-kathryn     Calton      F   \n",
       "...           ...             ...        ...    ...   \n",
       "3995         3996         Rosalia   Halgarth      F   \n",
       "3996         3997          Blanch     Nisuis      F   \n",
       "3997         3998          Sarene    Woolley    NaN   \n",
       "3998         3999       Patrizius        NaN      M   \n",
       "3999         4000           Kippy    Oldland      M   \n",
       "\n",
       "      past_3_years_bike_related_purchases        DOB               job_title  \\\n",
       "0                                      93 1953-10-12     Executive Secretary   \n",
       "1                                      81 1980-12-16  Administrative Officer   \n",
       "2                                      61 1954-01-20      Recruiting Manager   \n",
       "3                                      33 1961-10-03                     NaN   \n",
       "4                                      56 1977-05-13           Senior Editor   \n",
       "...                                   ...        ...                     ...   \n",
       "3995                                    8 1975-08-09   VP Product Management   \n",
       "3996                                   87 2001-07-13         Statistician II   \n",
       "3997                                   60        NaT       Assistant Manager   \n",
       "3998                                   11 1973-10-24                     NaN   \n",
       "3999                                   76 1991-11-05    Software Engineer IV   \n",
       "\n",
       "     job_industry_category     wealth_segment deceased_indicator owns_car  \\\n",
       "0                   Health      Mass Customer                  N      Yes   \n",
       "1       Financial Services      Mass Customer                  N      Yes   \n",
       "2                 Property      Mass Customer                  N      Yes   \n",
       "3                       IT      Mass Customer                  N       No   \n",
       "4                      NaN  Affluent Customer                  N      Yes   \n",
       "...                    ...                ...                ...      ...   \n",
       "3995                Health      Mass Customer                  N       No   \n",
       "3996         Manufacturing     High Net Worth                  N      Yes   \n",
       "3997                    IT     High Net Worth                  N       No   \n",
       "3998         Manufacturing  Affluent Customer                  N      Yes   \n",
       "3999                   NaN  Affluent Customer                  N       No   \n",
       "\n",
       "      tenure   age  \n",
       "0       11.0  64.0  \n",
       "1       16.0  37.0  \n",
       "2       15.0  64.0  \n",
       "3        7.0  56.0  \n",
       "4        8.0  40.0  \n",
       "...      ...   ...  \n",
       "3995    19.0  42.0  \n",
       "3996     1.0  16.0  \n",
       "3997     NaN   NaN  \n",
       "3998    10.0  44.0  \n",
       "3999    11.0  26.0  \n",
       "\n",
       "[4000 rows x 13 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Health', 'Financial Services', 'Property', 'IT', nan, 'Retail',\n",
       "       'Argiculture', 'Manufacturing', 'Telecommunications',\n",
       "       'Entertainment'], dtype=object)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['job_industry_category'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Manufacturing         799\n",
       "Financial Services    774\n",
       "Health                602\n",
       "Retail                358\n",
       "Property              267\n",
       "IT                    223\n",
       "Entertainment         136\n",
       "Argiculture           113\n",
       "Telecommunications     72\n",
       "Name: job_industry_category, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['job_industry_category'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['F', 'M', nan], dtype=object)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['gender'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Male', 'Female', nan], dtype=object)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_customer_list['gender'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Executive Secretary', 'Administrative Officer',\n",
       "       'Recruiting Manager', nan, 'Senior Editor', 'Media Manager I',\n",
       "       'Business Systems Development Analyst', 'Senior Quality Engineer',\n",
       "       'Nuclear Power Engineer', 'Developer I', 'Account Executive',\n",
       "       'Junior Executive', 'Media Manager IV', 'Sales Associate',\n",
       "       'Professor', 'Geological Engineer', 'Project Manager',\n",
       "       'Safety Technician I', 'Research Assistant I',\n",
       "       'Accounting Assistant III', 'Editor', 'Research Nurse',\n",
       "       'Safety Technician III', 'Staff Accountant III', 'Legal Assistant',\n",
       "       'Product Engineer', 'Information Systems Manager',\n",
       "       'VP Quality Control', 'Social Worker', 'Senior Cost Accountant',\n",
       "       'Assistant Media Planner', 'Payment Adjustment Coordinator',\n",
       "       'Food Chemist', 'Accountant III', 'Director of Sales',\n",
       "       'Senior Financial Analyst', 'Registered Nurse',\n",
       "       'Biostatistician II', 'Computer Systems Analyst II',\n",
       "       'Software Test Engineer II', 'Paralegal', 'VP Sales',\n",
       "       'Chief Design Engineer', 'Office Assistant III',\n",
       "       'Physical Therapy Assistant', 'Help Desk Operator',\n",
       "       'Web Developer II', 'Research Associate', 'Teacher',\n",
       "       'VP Product Management', 'Statistician II',\n",
       "       'Automation Specialist IV', 'Data Coordiator',\n",
       "       'Software Test Engineer III', 'Internal Auditor',\n",
       "       'Analyst Programmer', 'Occupational Therapist',\n",
       "       'Speech Pathologist', 'Quality Control Specialist',\n",
       "       'Civil Engineer', 'Software Engineer III',\n",
       "       'Community Outreach Specialist', 'Safety Technician IV',\n",
       "       'VP Accounting', 'General Manager', 'Nurse Practicioner',\n",
       "       'Automation Specialist II', 'Marketing Assistant',\n",
       "       'Marketing Manager', 'Staff Scientist', 'Assistant Professor',\n",
       "       'Budget/Accounting Analyst IV', 'Associate Professor',\n",
       "       'Graphic Designer', 'Administrative Assistant II',\n",
       "       'Compensation Analyst', 'Systems Administrator III',\n",
       "       'Financial Advisor', 'Chemical Engineer', 'Web Designer I',\n",
       "       'Senior Developer', 'Office Assistant II', 'Recruiter', 'Operator',\n",
       "       'Programmer Analyst III', 'Quality Engineer', 'Environmental Tech',\n",
       "       'Analog Circuit Design manager', 'Cost Accountant', 'Librarian',\n",
       "       'Structural Analysis Engineer', 'Pharmacist', 'Assistant Manager',\n",
       "       'Accountant I', 'Web Designer III', 'Geologist III',\n",
       "       'Software Test Engineer I', 'Structural Engineer',\n",
       "       'Safety Technician II', 'Web Developer III',\n",
       "       'Programmer Analyst II', 'Design Engineer', 'Statistician I',\n",
       "       'VP Marketing', 'Desktop Support Technician', 'Actuary',\n",
       "       'Database Administrator III', 'Electrical Engineer',\n",
       "       'Tax Accountant', 'Clinical Specialist',\n",
       "       'Database Administrator IV', 'Systems Administrator II',\n",
       "       'Account Coordinator', 'Programmer III',\n",
       "       'Administrative Assistant III', 'Nurse', 'Technical Writer',\n",
       "       'Staff Accountant II', 'Dental Hygienist', 'Sales Representative',\n",
       "       'Budget/Accounting Analyst III', 'Computer Systems Analyst IV',\n",
       "       'Geologist I', 'Financial Analyst', 'Accounting Assistant II',\n",
       "       'Senior Sales Associate', 'Database Administrator II',\n",
       "       'Engineer I', 'Budget/Accounting Analyst I', 'Developer IV',\n",
       "       'Database Administrator I', 'Environmental Specialist',\n",
       "       'Computer Systems Analyst I', 'Account Representative IV',\n",
       "       'Statistician IV', 'Human Resources Manager',\n",
       "       'GIS Technical Architect', 'Programmer IV',\n",
       "       'Accounting Assistant IV', 'Software Engineer IV', 'Programmer II',\n",
       "       'Engineer III', 'Software Consultant', 'Biostatistician IV',\n",
       "       'Help Desk Technician', 'Automation Specialist I', 'Developer III',\n",
       "       'Human Resources Assistant I', 'Geologist IV', 'Media Manager II',\n",
       "       'Statistician III', 'Engineer II', 'Health Coach II',\n",
       "       'Developer II', 'Systems Administrator I', 'Web Developer I',\n",
       "       'Software Engineer II', 'Accounting Assistant I',\n",
       "       'Research Assistant II', 'Programmer Analyst IV', 'Health Coach I',\n",
       "       'Accountant II', 'Automation Specialist III',\n",
       "       'Administrative Assistant I', 'Health Coach IV',\n",
       "       'Media Manager III', 'Account Representative III',\n",
       "       'Web Designer IV', 'Budget/Accounting Analyst II',\n",
       "       'Web Developer IV', 'Programmer I', 'Biostatistician III',\n",
       "       'Software Test Engineer IV', 'Research Assistant IV',\n",
       "       'Account Representative I', 'Accountant IV', 'Biostatistician I',\n",
       "       'Human Resources Assistant IV', 'Administrative Assistant IV',\n",
       "       'Office Assistant I', 'Human Resources Assistant II',\n",
       "       'Mechanical Systems Engineer', 'Engineer IV', 'Health Coach III',\n",
       "       'Office Assistant IV', 'Software Engineer I',\n",
       "       'Human Resources Assistant III', 'Staff Accountant I',\n",
       "       'Computer Systems Analyst III', 'Geologist II', 'Web Designer II',\n",
       "       'Staff Accountant IV', 'Account Representative II',\n",
       "       'Programmer Analyst I', 'Systems Administrator IV',\n",
       "       'Research Assistant III'], dtype=object)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_demographics['job_title'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3999, 6)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_address.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False])"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_address.duplicated().unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>customer_id</th>\n",
       "      <th>postcode</th>\n",
       "      <th>property_valuation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3999.000000</td>\n",
       "      <td>3999.000000</td>\n",
       "      <td>3999.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2003.987997</td>\n",
       "      <td>2985.755939</td>\n",
       "      <td>7.514379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1154.576912</td>\n",
       "      <td>844.878364</td>\n",
       "      <td>2.824663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1004.500000</td>\n",
       "      <td>2200.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2004.000000</td>\n",
       "      <td>2768.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3003.500000</td>\n",
       "      <td>3750.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4003.000000</td>\n",
       "      <td>4883.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       customer_id     postcode  property_valuation\n",
       "count  3999.000000  3999.000000         3999.000000\n",
       "mean   2003.987997  2985.755939            7.514379\n",
       "std    1154.576912   844.878364            2.824663\n",
       "min       1.000000  2000.000000            1.000000\n",
       "25%    1004.500000  2200.000000            6.000000\n",
       "50%    2004.000000  2768.000000            8.000000\n",
       "75%    3003.500000  3750.000000           10.000000\n",
       "max    4003.000000  4883.000000           12.000000"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_address.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3999 entries, 0 to 3998\n",
      "Data columns (total 6 columns):\n",
      " #   Column              Non-Null Count  Dtype \n",
      "---  ------              --------------  ----- \n",
      " 0   customer_id         3999 non-null   int64 \n",
      " 1   address             3999 non-null   object\n",
      " 2   postcode            3999 non-null   int64 \n",
      " 3   state               3999 non-null   object\n",
      " 4   country             3999 non-null   object\n",
      " 5   property_valuation  3999 non-null   int64 \n",
      "dtypes: int64(3), object(3)\n",
      "memory usage: 187.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df_customer_address.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Australia'], dtype=object)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_address['country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer_address['state'].unique()\n",
    "df_customer_address['state'].replace({'New South Wales':'NSW','Victoria':'VIC'},inplace=True\n",
    "                                     )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9     647\n",
       "8     646\n",
       "10    577\n",
       "7     493\n",
       "11    281\n",
       "6     238\n",
       "5     225\n",
       "4     214\n",
       "12    195\n",
       "3     186\n",
       "1     154\n",
       "2     143\n",
       "Name: property_valuation, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_address['property_valuation'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer = df_customer_demographics.merge(df_customer_address, on = 'customer_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3996, 18)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_customer_demographics.merge(df_customer_address, how = 'outer', on = 'customer_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4003, 18)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "customer_id                              0\n",
       "first_name                               3\n",
       "last_name                              128\n",
       "gender                                  91\n",
       "past_3_years_bike_related_purchases      3\n",
       "DOB                                     90\n",
       "job_title                              509\n",
       "job_industry_category                  659\n",
       "wealth_segment                           3\n",
       "deceased_indicator                       3\n",
       "owns_car                                 3\n",
       "tenure                                  90\n",
       "age                                     90\n",
       "address                                  4\n",
       "postcode                                 4\n",
       "state                                    4\n",
       "country                                  4\n",
       "property_valuation                       4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = df_trans.merge(df, on='customer_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>...</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>age</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-02-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11065</td>\n",
       "      <td>1</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18923</td>\n",
       "      <td>62</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-04-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-05-21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Trek Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>...</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>4 Shopko Circle</td>\n",
       "      <td>2196.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6862</td>\n",
       "      <td>4</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-10-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>high</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>4 Shopko Circle</td>\n",
       "      <td>2196.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "0               1           2         2950       2017-02-25           0.0   \n",
       "1           11065           1         2950       2017-10-16           0.0   \n",
       "2           18923          62         2950       2017-04-26           0.0   \n",
       "3               2           3         3120       2017-05-21           1.0   \n",
       "4            6862           4         3120       2017-10-05           0.0   \n",
       "\n",
       "  order_status           brand product_line product_class product_size  ...  \\\n",
       "0     Approved           Solex     Standard        medium       medium  ...   \n",
       "1     Approved  Giant Bicycles     Standard        medium       medium  ...   \n",
       "2     Approved           Solex     Standard        medium       medium  ...   \n",
       "3     Approved   Trek Bicycles     Standard        medium        large  ...   \n",
       "4     Approved  Giant Bicycles     Standard          high       medium  ...   \n",
       "\n",
       "   wealth_segment  deceased_indicator  owns_car  tenure   age  \\\n",
       "0   Mass Customer                   N       Yes    10.0  63.0   \n",
       "1   Mass Customer                   N       Yes    10.0  63.0   \n",
       "2   Mass Customer                   N       Yes    10.0  63.0   \n",
       "3   Mass Customer                   N       Yes    10.0  39.0   \n",
       "4   Mass Customer                   N       Yes    10.0  39.0   \n",
       "\n",
       "             address postcode  state    country property_valuation  \n",
       "0  984 Hoepker Court   3064.0    VIC  Australia                6.0  \n",
       "1  984 Hoepker Court   3064.0    VIC  Australia                6.0  \n",
       "2  984 Hoepker Court   3064.0    VIC  Australia                6.0  \n",
       "3    4 Shopko Circle   2196.0    NSW  Australia                5.0  \n",
       "4    4 Shopko Circle   2196.0    NSW  Australia                5.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>age</th>\n",
       "      <th>tenure</th>\n",
       "      <th>postcode</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.439024</td>\n",
       "      <td>-0.012064</td>\n",
       "      <td>-0.037855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tenure</th>\n",
       "      <td>0.439024</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.011839</td>\n",
       "      <td>-0.020642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>postcode</th>\n",
       "      <td>-0.012064</td>\n",
       "      <td>0.011839</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.017359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <td>-0.037855</td>\n",
       "      <td>-0.020642</td>\n",
       "      <td>-0.017359</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          age    tenure  postcode  \\\n",
       "age                                  1.000000  0.439024 -0.012064   \n",
       "tenure                               0.439024  1.000000  0.011839   \n",
       "postcode                            -0.012064  0.011839  1.000000   \n",
       "past_3_years_bike_related_purchases -0.037855 -0.020642 -0.017359   \n",
       "\n",
       "                                     past_3_years_bike_related_purchases  \n",
       "age                                                            -0.037855  \n",
       "tenure                                                         -0.020642  \n",
       "postcode                                                       -0.017359  \n",
       "past_3_years_bike_related_purchases                             1.000000  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df[['age','tenure','postcode','wealth_segment','past_3_years_bike_related_purchases']].corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1e1701b8640>"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYYAAAEHCAYAAACqbOGYAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAgAElEQVR4nO3debwcVZn/8c+XEELIBjEhExASRAYERYgXZHWICCMqggpifjKyKbiDjgvMOCAqgoC4L8SFRRGGxZFFETDsyJaEkLCIoCSIRoisAQIh8Pz+OOeavp3OvV2drq57b77v1+u+blV19amnu7rr6Tqn6hxFBGZmZt3WqDoAMzPrX5wYzMysBycGMzPrwYnBzMx6cGIwM7Me1qw6gFU1bty4mDx5ctVhmJkNKLNmzfpHRIxv9NiATwyTJ09m5syZVYdhZjagSFqwssdclWRmZj04MZiZWQ9ODGZm1kOpiUHSTyU9KumummVjJV0l6f78f72ax46R9ICk+yT9e5mxmZlZY2WfMZwJvLVu2dHAjIjYDJiR55G0JfA+YKv8nO9LGlJyfGZmVqfUxBAR1wOP1y3eBzgrT58F7Fuz/LyIeCEiHgQeALYvMz4zM1tRFW0MEyJiIUD+v35eviHwl5r1Hs7LViDpcEkzJc1ctGhRqcGama1u+lPjsxosa9gneERMj4iuiOgaP77h/RlmZtaiKm5we0TSxIhYKGki8Ghe/jCwUc16rwT+VmYgu37w5DKLtxY8/tp3Vh2CNbDGqFdUHYI1MO+wcn4YV3HGcAlwUJ4+CLi4Zvn7JA2TtAmwGXBbBfGZma3WSj1jkHQusBswTtLDwHHAScD5kg4DHgL2B4iIuyWdD9wDLAM+FhEvlRmfmZmtqNTEEBHTVvLQ7itZ/wTghPIiMjOzvvSnxmczM+sHnBjMzKwHJwYzM+vBicHMzHoY8AP1rIoXxm1WdQhWZ9i4V1YdgjUwduhqfahY7TR9xiDpZEmjJQ2VNEPSPyQdWGZwZmbWeUWqkvaMiKeBd5DuUv5X4LOlRGVmZpUpkhiG5v9vA86NiPpeU83MbBAoUnF4qaQ/AEuAj0oaDzxfTlhmZlaVps8YIuJoYEegKyJeBJ4jjaFgZmaDSJHG53WAjwE/yIs2ALrKCMrMzKpTpCrpDGAWsFOefxi4ALis3UF1ypKxk6oOwepM8GWR/dIID7K7WinS+LxpRJwMvAgQEUtoPLiOmZkNYEUSw1JJw8mjqknaFHihlKjMzKwyRc7bjwN+C2wk6RxgZ+DgMoIyM7PqNJ0YIuIqSbOBHUhVSEdGxD9Ki8zMzCpR5KqknYHnI+LXwLrAf0ly662Z2SBTpI3hB8Bzkl5P6gpjAXB2KVGZmVllirQxLIuIkLQP8O2I+Imkg8oKrBP08rKqQ7A6Y4ZE1SFYA2N8FfFqpcjuXizpGOBA4E2ShrC8/yQzMxskilQlHUC6PPWwiPg7sCFwSilRmZlZZYpclfR34LSa+YdwG4OZ2aBT5KqkHSTdLukZSUslvSTpqTKDMzOzzitSlfRdYBpwPzAc+CDwvTKCMjOz6hS61iAiHpA0JCJeAs6Q9PuS4jIzs4oUSQzPSVoLmCPpZGAhMKKcsDpj2OJHqw7B6oxd05er9kePLitSuWADXZG9/R95/Y8DzwIbAe8pIygzM6tOkauSFuTJ54HjywnHzMyq1nRiyH0lfRGYVPu8iHhV+8MyM7OqFGlj+AnwKdIobi+VE46ZmVWtSGJ4KiIuLy0SMzPrF/pMDJKm5MlrJJ0C/JKakdsiYnYrG5b0KdK9EAHMAw4B1gH+F5gMzAfeGxFPtFK+mZm1ppkzhq/XzXfVTAfw5qIblbQh8Elgy4hYIul84H3AlsCMiDhJ0tHA0cDni5bfrGGP/bmsoq1Ftz6+Y9UhWAMRL1cdgjW0diml9pkYImJqKVtO2x4u6UXSmcLfgGOA3fLjZwHXUmJiMDOzFRXpK+mrktatmV9P0lda2WhE/BU4FXiIdKPcUxFxJTAhIhbmdRYC668klsMlzZQ0c9GiRa2EYGZmK1HkBre9IuLJ7plc9/+2VjYqaT1gH2ATYANghKQDm31+REyPiK6I6Bo/fnwrIZiZ2UoUSQxDJA3rnpE0HBjWy/q9eQvwYEQsiogXSQ3aOwGPSJqYy58IuM8KM7MOK3K56s+BGZLOIDU6H0pqB2jFQ8AOktYBlgC7AzNJXW0cBJyU/1/cYvlmZtaiphKDJAHnAnNJv/YFfDkirmhloxFxq6QLgdnAMuAOYDowEjhf0mGk5LF/K+WbmVnrmkoMERGSfhURbwB+244NR8RxwHF1i18gnT10xOJJ23dqU9akocPKufzOVs2bxlQdgXVSkTaGWyRtV1okZmbWLxRpY5gKHCFpAaktQKSTia1LiczMzCpRJDHsVVoUZmbWbxRJDB5ay8xsNVAkMfyalBxE6qBjE+A+YKsS4jIzs4oUGcHtdbXzudfVI9oekZmZVarIGUMPETF7oF+lNOaPv6s6BKvzuq18LUN/dNUf3RNx//TaUkotMrTnp2tm1wCmAO7BzsxskClyxjCqZnoZqc3hovaGY2ZmVSvSxnA8gKTRaTYWlxaVmZlVpsh4DF2S5pH6S5on6U5JbygvNDMzq0KRqqSfAh+NiBsAJO0CnAG4tdDMbBAp0lfS4u6kABARNwKuTjIzG2SKnDHcJul0UvfbARwAXJvvZyAiZpcQX6me2GrvqkOwOg+9UOS3inXK0PGTqg7BOqhIYtgm/6/vKnsnUqJ4c1siMjOzShW5Kmlqb49LOigiWh3RzczM+ol2nrcf2cayzMysIu1MDGpjWWZmVpF2JgZ3y21mNgj4jMHMzHpouXfVBm5qY1kdMeaBa6sOweo8OOZfqg7BGlh7xKi+V7JBo8/EUNer6goi4rT8/+PtCsrMzKrTzBlD90+FzYHtgEvy/N7A9WUEZWZm1ekzMdT0qnolMKW7V1VJXwQuKDU6MzPruCKNzxsDS2vmlwKT2xqNmZlVrkjj889I/SX9H+nS1HcBZ5cSlZmZVaZIlxgnSLoc2DUvOiQi7ignLDMzq0rRy1XXAZ6OiDMkjZe0SUQ8WEZgnfDUpm+qOgSrM2ndMVWHYA1MGOr7V1cnRUZwOw74PHBMXjQU+HkZQZmZWXWKND6/C3gn8CxARPyN5ZeympnZIFEkMSyNiCD3iSRpRDkhmZlZlYokhvPzCG7rSvoQ8Dvgx61uWNK6ki6U9AdJ90raUdJYSVdJuj//X6/V8s3MrDVNJ4aIOBW4ELiIdBf0sRHx7VXY9reA30bEFsDrgXuBo4EZEbEZMCPPm5lZBzV9VZKkr0XE54GrGiwrRNJo4E3AwQARsRRYKmkfYLe82lnAtaQGbzMz65Ail6vuwYoH6b0aLGvGq4BFwBmSXg/MIo0ANyEiFgJExEJJ6zd6sqTDgcMBNt544xY2n6yx7IWWn2vlWPDkk1WHYA3MX7a075WsAq0f/3rTZ1WSpI9ImgdsLmluzd+DwNwWt7smMAX4QURsS7rSqelqo4iYHhFdEdE1fvz4FkMwM7NGmjlj+AVwOXAiPQ/eiyPi8Ra3+zDwcETcmucvzGU/ImliPluYCDzaYvlmZtaiPs8YIuKpiJgfEdMiYgGwhHTJ6khJLZ3HRMTfgb9I2jwv2h24h9Sl90F52UHAxa2Ub2ZmrSvS+Lw3cBqwAemX/CTSlURbtbjtTwDnSFoL+DNwCClRnS/pMOAhYP8WyzYzsxYVaXz+CrAD8LuI2FbSVGBaqxuOiDlAV4OHdm+1TDMzW3VFbnB7MSIeA9aQtEZEXANsU1JcZmZWkSJnDE9KGkkazvMcSY8Cy8oJy8zMqlIkMewDPA98Cng/MAb4UhlBdcpLw0ZWHYLVGTd63apDsAYmr+Vut1cnRQbqebZm9qwSYjEzs36gz8QgaTG5R9XuRXleQETE6JJiMzOzCvSZGCLCYy6Yma1GilyVhKRdJB2Sp8dJ2qScsMzMrCqrMrTnWnhoTzOzQcdDe5qZWQ9FLlddGhEhadAM7Tl6wW1Vh2B1nhy3UdUhWAO3L3qs6hCsoVeVUuqqDu35o1KiMjOzyjR1xiBJwP8CWwBPs3xoz6t6faKZmQ04TSWGXIX0q4h4AzVDe5qZ2eBTpCrpFknblRaJmZn1C0Uan6cCR0haQLoyqfvO561LiczMzCpRJDHs1duDktaLiCdWMR4zM6tYkU70FvSxygxgyqqF01lrLpxbdQhW56UX9646BGtAaw2vOgTroEJdYvRBbSzLzMwq0s7E4A7bzcwGgXYmBjMzGwRclWRmZj2sSrfb4+u63d69rZGZmVklVqXb7aHUdLsdEY+3NzQzM6tCkfsY3gVsC8yG1O22pAHd7fZju3y06hCszitHDeiP1KC1aOlLVYdgHVSkKmlpRAT56qPB0O22mZmtaFW73f5xOWGZmVlVitz5fKqkPajpdhu4vqzAzMysGk0nBkk/jYhDyd1uSxoJ/AZfjWRmNqgUqUr6q6QfQOowD7iSmquSzMxscGg6MUTE/wBPS/ohKSl8PSLOKC0yMzOrRJ9VSZLeXTN7G/A/+X9IendE/LKs4Mq27v3XVR2C1fnbOvtUHYI1sOYw9666OmmmjaG+H+Q7SDe37U26dLXlxCBpCDAT+GtEvEPSWNLY0pOB+cB7PcaDmVln9ZkYIuKQErd/JHAvMDrPHw3MiIiTJB2d5z9f4vbNzKxOM1VJn4uIkyV9hwZda0fEJ1vZsKRXAm8HTgA+nRfvA+yWp88CrsWJwcyso5qpSro3/5/Z5m1/E/gcUNsHwoSIWAgQEQslrd/oiZIOBw4H2HjjjdsclpnZ6q2ZqqRL8/+zACSNTrOxuNWNSnoH8GhEzJK0W9HnR8R0YDpAV1eXBwgyM2ujIje4dQFnkH7hS9KTwKERMauF7e4MvFPS24C1gdGSfg48ImliPluYCDzaQtlmZrYKivSu+lPgoxFxA6SxGUiJYuuiG42IY8jdd+czhs9ExIGSTgEOAk7K/y8uWnYRz4+dXGbx1oJRI0f3vZJ13HMvvFB1CNZBRe58XtydFAAi4kag5eqklTgJ2EPS/cAeed7MzDqomauSpuTJ23LvqueSrk46gHTV0CqJiGu7y4mIx3DfS2ZmlWqmKunrdfPH1Uy74dfMbJBp5qqkqc0UJOmg7iuXzMxs4CrSxtCXI9tYlpmZVaSdiUFtLMvMzCpS5HLVvgy49oYR91xWdQhW58nJU/peyTpvwH27bVX4jMHMzHpoZ2K4qY1lmZlZRZpODJKOlDRayU8kzZa0Z/fjEfHxckI0M7NOKnLGcGhEPA3sCYwHDsF3JpuZDTpFEkN3G8LbgDMi4k7crmBmNugUSQyzJF1JSgxXSBoFvFxOWGZmVpWmLleVJOBYUhXSnyPiOUmvIFUnDVhLNntL1SFYna3HrFN1CNbAWu28TMX6vaYSQ0SEpF9FxBtqlj0GPFZaZGZmVokivwNukbRdaZGYmVm/UOTO56nAEZIWAM+SGp4jIgoP1GNmZv1XkcSwV2lRmJlZv9F0YoiIBQCS1ieN02xmZoNQkTuf35mH3HwQuA6YD1xeUlxmZlaRIlVJXwZ2AH4XEdtKmgpMKyeszhiy9JmqQ7A6855+vuoQrIGXli2tOgRraHgppRa5KunFfInqGpLWiIhrgG1KicrMzCpT5IzhSUkjgRuAcyQ9CiwrJywzM6tKkTOGfYDngKOA3wJ/AvYuIygzM6tOkauSnpU0CdgsIs6StA4wpLzQzMysCkWuSvoQcCFwel60IfCrMoIyM7PqFKlK+hiwM/A0QETcD6xfRlBmZladIo3PL0TE0tTRKkhakwE+RPiSsZOrDsHqbDna9072R/c9t1bVIVgHFTljuE7SfwHDJe0BXABcWk5YZmZWlSKJ4WhgETAPOAL4DfCFMoIyM7PqFKlKehvwk4j4UVnBmJlZ9YqcMbwPuF/SyZJeU1ZAZmZWraYTQ0QcCGxLurHtDEk3Szo8j/1sZmaDRKGRXCPiaeAi4DxgIvAuYLakTxQpR9JGkq6RdK+kuyUdmZePlXSVpPvz//WKlGtmZquu6TYGSXsDhwKbAj8Dto+IR/Md0PcC3ymw3WXAf0bE7HzGMUvSVcDBwIyIOEnS0aQG788XKLeQEY/8oayirUVzR0+oOgRrJF6uOgJr6NWllFqk8Xl/4BsRcX3twoh4TtKhRTYaEQuBhXl6saR7SXdS7wPsllc7C7iWEhODmZmtqEgbwwfqk0LNYzMk3dxKAJImk9oubgUm5KTRnTwa3lmd2zZmSpq5aNGiVjZrZmYrUaiNoQ+Fb1nN3XhfBByV2y+aEhHTI6IrIrrGjx9fdLNmZtaLdiaGQt1jSBpKSgrnRMQv8+JHJE3Mj08EHm1jfGZm1oR2JoamKXW49BPg3og4reahS4CD8vRBwMWdjs3MbHVXpPG5Lyqw7s7AfwDzJM3Jy/4LOAk4X9JhwEOkBm8zM+ugdiaG/2h2xYi4kZUnkt3bE07fntpkh05typo1pJ0fSWuXoSPGVB2CdVCfVUmStpB0uaRfS9pU0pmSnpR0W23XGBFxV7mhmplZJzTTxjAd+D7wc+Bq0njP6wFfBr5bXmhmZlaFZhLDqIi4NCLOBV6MiPMiuZSUIMzMbBBpJjEMqZk+re4xD+tkZjbINJMYvpdvRCMivt+9UNKrgd+VFZiZmVWjz8QQEadHxDMNlj8QEUd1z0s6pt3BmZlZ57Xz2sD9gRPbWF7pXnHnRVWHYHWe2f2ovleyjnt5wdyqQ7CGdiql1Hbe+VzkBjczM+unKusryczM+iefMZiZWQ9NJwZJO/ex7IK2RGRmZpUqcsbQaOjOfy6LiK+uejhmZla1Pq9KkrQjqel7vKRP1zw0mp43v5mZ2SDQzOWqawEj87qjapY/DexXRlCd8sRWe1cdgtXZdXQlQ4RYHxZuPqXqEKyD+kwMEXEdcJ2kMyNiAYCkNYCRRYbjNDOzgaHIz7MTJY2WNAK4B7hP0mdLisvMzCpSJDFsmc8Q9gV+A2xMgcF5zMxsYCiSGIZKGkpKDBdHxIv4pjYzs0GnSGI4HZgPjACulzSJ1ABtZmaDSNOd6EXEt4Fv1yxaIGlq+0MyM7MqFepdVdLbga2AtWsWf6mtEXXQyL+6x8j+5uYJr6o6BGtAernqEKyDinSJ8UPgAOATpH6R9gcmlRSXmZlVpEgbw04R8QHgiYg4HtgR2KicsMzMrCpFEsOS/P85SRsALwKbtD8kMzOrUpE2hsskrQucDMzKy37c/pDMzKxKRRLDqcBHgF2Bm4EbgB+UEZSZmVWnSGI4C1jM8ktWpwFnA+9td1BmZladIolh84h4fc38NZLubHdAnTRkyRNVh2B11hwytOoQrBGPz7haKdL4fIekHbpnJL0RuKn9IZmZWZWKnDG8EfiApIfy/MbAvZLmARERW7c9OjMz67giieGtpUVhZmb9RpG+khaUGUg3SW8FvkUaNvTHEXFSJ7ZrZmZJvxpHUdIQ4HvAXsCWwDRJW1YblZnZ6qVfJQZge+CBiPhzRCwFzgP2qTgmM7PVSn9LDBsCf6mZfzgv60HS4ZJmSpq5aNGijgVnZrY6KNTtdgc0ulp6hVHiImI6MB2gq6ur5VHkbvzhJ1t9qpnZoNXfzhgepmePra8E/lZRLGZmq6X+lhhuBzaTtImktYD3AZdUHJOZ2WqlX1UlRcQySR8HriBdrvrTiLi74rDMzFYr/SoxAETEb4DfVB2Hmdnqqr9VJZmZWcWcGMzMrAcnBjMz68GJwczMelBEy/eH9QuSFgEd6eCvnxsH/KPqIGwF3i/9j/dJMikixjd6YMAnBkskzYyIrqrjsJ68X/of75O+uSrJzMx6cGIwM7MenBgGj+lVB2ANeb/0P94nfXAbg5mZ9eAzBjMz68GJwczMenBiaIGkkPSzmvk1JS2SdFmJ2/yMpD9IukvSnZI+0EIZkyX9vzLi6xRJ78rv/xY1y8ZLulXSHZJ2lbS/pHslXSNptzL2i6R9exuPXNIH8r66W9I9kj7TwjbWlfTRVYu0WpKeqZs/WNJ38/SH+/oc167fx3rXSppZM98l6do+nrPS74Ok/5O0b838fZK+UDN/kaR39xVXzfpHSVqnZv6Z3tavmhNDa54FXitpeJ7fA/hrWRuT9OG8je0j4rXAm2g82l1fJgMdTQyS2t2D7zTgRtJYHd12B/4QEdtGxA3AYcBHI2Jqm7dda1+gYWKQtBdwFLBnRGwFTAGeamEb6wIdTQwl7K+ViogfRsTZbSxy/fzeN2syK/8+/B7YCUDSK4BngB1rHt8xr9MnSUNIn4d1+lq334gI/xX8I31Ivgrsl+fPBj4PXJbntyd9aO7I/zfPy7cCbgPmAHOBzYARwK+BO4G7gAMabO8hYNOVxDIfGJenu4Br8/S/5e3MyXGMAm4hHaDmAJ8C1gbOAObldabm5x4M/Aq4FHgQ+Djw6bzOLcDYvN6mwG+BWcANwBZ5+ZnAacA1wNfb+L6PJCXgfyUlAoBt8vuzKL+u4/L+uQ84BditZr98EfhMTXl3AZPz9IE1++Z0YEjNvj4h759bgAmkA8bj+b2ZU79vgOuBN6/kNVwLdOXpccD8Xj4b5wFL8rJTSD8GTslxz+v+rOTXeB1wPvBH4CTg/bm8ed3xAeOBi0gDYt0O7FzzvkwHrgR+0e7vSt38wcB36/cHsF1+3Td3v8aa9X+ZP2f3Ayf38r5+AripwXdhSC7z9ryNI/LyHt+HuvJ2Am7M03sDx+f3U8AmwJ/yY9Pye3wX8LW6Y8SXgFuBY4Gleb1rVva5qvq41uP1Vx3AQPzLO3Vr4ELSwXUOPQ9Ao4E18/RbgIvy9HeA9+fptYDhwHuAH9WUPaZuW6OAJ3qJZT6NE8OlNV/8kaSxN/4ZY17+n8AZeXoL0gF27fxlfCBve3z+8nw4r/cN4Kg8PQPYLE+/Ebg6T58JXEY+uLbxfT8Q+Eme/j0wJU8fTD7Y5PlrWX7wrd0vX6RBYgBek9+voXn594EP5OkA9s7TJwNfqHmN+60kzsfr9+NKYqtNDI0+G5PJB8i8/D3AVaQD3YS8vybm1/hknh5GSp7H5+ccCXwzT/8C2CVPbwzcW/O+zAKGl/BdeYnlP1Dm5JgbJYa7gJ3y9En0TAx/BsaQPpsLgI1W9r4CVwNT6fldOLxmvw0DZpIO7v/8bDQob1h+T9cCTgTeCvyMdJb4ftKPwQ3y6xlP+n5dDexb87l5b6PvaW+fq/7y1+8G6hkoImKupMmkXwz1AwuNAc6StBnpAzA0L78Z+G9JrwR+GRH3S5oHnCrpa6QP6Q11ZSmXUdRNwGmSzsnbelhaofZpF9IBiYj4g6QFpF/jkH7ZLAYWS3qKdOCE9Ktna0kjSb+qLqgpd1hN2RdExEstxN2bacA38/R5eX52G8rdHXgDcHt+LcOBR/NjS0lJDtLBc482bK+RRp+N+nV2Ac7N7+sjkq4j/dJ+Grg9IhYCSPoT6dc/pP3VXaX2FmDLmnJHSxqVpy+JiCUlvK4lEbFN94ykg0kHbWqWrQuMiojuqplfAO+oWWVGRDyV170HmAT8ZSXb+wrwBdIZfLc9SZ/Z/fL8GNIZ2dKVBR0RL0i6m1QNuAPp4P0q0md+W9IPk+1IyWdRju0cUjXvr0gJ8aKVlU/nPlctcRvDqrkEOBU4t275l0kH1teSTkPXBoiIXwDvJFUPXCHpzRHxR9JBaR5woqRjawuKiKeBZyW9aiUxLGP5fly75nknAR8kHeRuqW2srdFbO8ULNdMv18y/TPp1tAbwZERsU/P3mprnPNtL2YXlet43Az+WNB/4LHCAGhw9e1H7XsHy90vAWTWvY/OI+GJ+7MXIP+tIX/ZmfkzdTdqnfcVQu79W+Gw0eO6q7C/ydneseZ0b5uQPbd5fBfW1D2tfW6/7ICKuJr2vO9SV/4ma171JRFzZuIQefk860I+KiCdIVT475b+b+oj7+T5+GLXyueoYJ4ZV81PgSxExr275GJY3Rh/cvTAf3P8cEd8mJZWtJW0APBcRPyclmSkNtnMi8D1Jo3M5oyUdnh+bz/KD0HtqtrVpRMyLiK+RTp23ABaTqoe6XU86LUbSv5KqF+5r5oXnhPWgpP3z8yXp9c08t0X7AWdHxKSImBwRG5Hq+HcpUMZ88vsraQqpOgFSldh+ktbPj42VNKmPsurfy1onAidL+pdc3jBJn6yJoXt/df+CbfjZaLCN60nJcIik8aSD1m19xFnrSlJ7Ufc2t+ll3Y7JB93FkroP5u/rbf0mnAB8rmb+CuAjkoZC+qxLGkHv+xDSwf8IUjsApPaJHUjfk7tJ7Qf/JmlcbmCeRmrraaSvbfUrTgyrICIejohvNXjoZNKv/5tI9cHdDgDukjSHdKA+G3gdcFte9t+kU+F6PyA15N4u6S7Sh++5/NjxwLck3UD65dHtqO5LW0m/Qi8nfbCX5ctdP0WqSx+Sq7P+Fzg4Imp/nfXl/cBheRt3A/sUeG5R04D/q1t2EcWusroIGJvf64+QGmqJiHtI1Q9XSppLqsef2EdZ5wGfzZfIblr7QKRxy78H/C5XR8xi+S/CU0kHqd+T2hi6rfDZiIjHgJvyfjyF9Prnkg5UVwOfi4i/F3j9nwS6JM3NVTIfLvDcsh0GTJd0M+mXeCtXcQH/fP8X1Sz6MXAPMDt/f04n7Y/670O935Oqj27O5S4jVTHOjIiXc9XdMaTv5p3A7Ii4eCVhTQcul3RNq6+rk9wlhplVTtLIiHgmTx8NTIyIIysOa7XVr+q1zGy19XZJx5COSQuoqYK1zvMZg5mZ9eA2BjMz68GJwczMenBiMDOzHpwYzMysBycGs0zSmd3dJgy0bpKLyF1Zb1B1HNZ/OTGYNTawukku5mBSB3BmDTkx2IAl6XPdXU1I+oakq/P07pJ+LmlPSTdLmi3pgtzxH5KOlXR7vqN4en1/S7nMDYBrau9UlXRCvkv2FkkTeolrfy0fUOn6vGyIpFPydudKOiIvX0PS95UG9LlM0m9qzlrmS/pqfg0zJU2RdIWkPymN0dG9vc/WlHt8XjZZabCiH+Wyr5Q0PJfdBZwjaY6Wjyli9k9ODDaQXQ/smqe7gMvdT1cAAAKQSURBVJG5P5xdSJ0SfgF4S0RMIfUX9em87ncjYrvcyeFwevbkSe6v6G+k8Sm6eyYdAdwSEa/P2/1QL3EdC/x7XvededlhwFMRsR2pV84PSdoEeDepe+3XkTo93LGurL9ExI6k8S7OJPWvtAOpr38k7UnqKXR70tgUb5D0pvzczYDvRRos6EngPRFxYX4v3p87lCujR1Ub4Hznsw1ks0gHwlGkHjhnkxLErqSO6LYk9TUEqV/9m/Pzpkr6HKmqaCypn6dL6V2RbpJvAs6UdD5pkBlYedfPu5C6KH8Z+HuDvnQuyf/nASNrukJ/Xqm76j3z3x15vZG53IeAByNiTk3Mk/t4jWaAE4MNYBHxolIX3IeQOjybSxp7YFNSz6tXRcS02udIWpvUeWBXRPxF0hep6f66F013kxwRH5b0RuDtwJzci2l3189X1MXz9j62W9t9dn3X2mvmck+MiNPryp3Mit1Vu9rImuKqJBvorgc+k//fQOoxdA6p7/ydJb0aQNI6Sl2LdyeBf+Q2h/1WLBJYhW6Slbo8vzUijgX+AWzEyrt+vhF4T25rmEAaVayIK4BDa9pPNlTuPrwXA6oLaOs8nzHYQHcDqbvymyPiWUnPAzdExCKl0cLOldQ9stwXIuKPkn5EqpqZTxoHuJHubpIX1rQzNOsUpdH7RBrr4U7S2cxkUtfPInULvS+pK/DdSUNb/pHUx3/TXU5HxJWSXgPcnKvMniENgdrbIDFnAj+UtIQ0cI/bGawHd6JnVrHuLqeVRqm7jTRWd5FxFszaymcMZtW7LDckrwV82UnBquYzBrMWSfpvYP+6xRdExAlVxGPWLk4MZmbWg69KMjOzHpwYzMysBycGMzPrwYnBzMx6+P8he8RJfu8OuwAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "sns.histplot(merged_df,x='wealth_segment', y= 'past_3_years_bike_related_purchases')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1e16d246d00>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZIAAAEHCAYAAACEKcAKAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAboklEQVR4nO3debwcZZ3v8c+XREJYgmQ45IYkeCJmZBsXOGBYdMQ4kLnOSEZAwsUhzM0YQFzQES5ceaHO3AwoXEVGQSJqEkUwLA6BEYEJqxAIB4iEgEiUJcdECC4YUMHAb/54nmMqnT5L8pzuzpnzfb9e/eqqp7anurrr21XV/ZQiAjMzs821VasrYGZmg5uDxMzMijhIzMysiIPEzMyKOEjMzKzI8FZXoNl23nnnaG9vb3U1zMwGlfvvv/+5iGirN2zIBUl7ezudnZ2troaZ2aAi6amehvnUlpmZFXGQmJlZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFWlYkEj6hqRnJT1cKRst6WZJj+fnnSrDzpS0QtJjkg6vlO8naVkedqEk5fIRkr6by++V1N6odTEzs5418ohkLjC1puwMYFFETAIW5X4k7QVMB/bO01wkaVie5mJgFjApP7rnORP4dUS8Afgi8LmGrYmZmfWoYUESEXcAv6opPgKYl7vnAdMq5VdExEsR8QSwAjhA0lhgVEQsjnQHrvk103TP6ypgSvfRSqOMm7AbkvzYwh7jJuzWyM1uZn1odhMpYyJiNUBErJa0Sy4fB9xTGa8rl/0xd9eWd0+zMs9rnaTngT8DnqtdqKRZpKMadttt83c6q7pWcswld2/29NYY3z3xoFZXwWxI21Iuttc7koheynubZuPCiDkR0RERHW1tddscMzOzzdTsIHkmn64iPz+by7uACZXxxgOrcvn4OuUbTCNpOLAjG59KMzOzBmt2kCwEZuTuGcC1lfLp+ZdYE0kX1Zfk02BrJU3O1z+Or5mme15HAbfk6yhmZtZEDbtGIuly4J3AzpK6gE8D5wILJM0EngaOBoiI5ZIWAI8A64BTIuKVPKuTSb8AGwnckB8AXwe+JWkF6UhkeqPWxczMetawIImIY3sYNKWH8WcDs+uUdwL71Cn/AzmIzMysdbaUi+1mZjZIOUjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKxIS4JE0sclLZf0sKTLJW0jabSkmyU9np93qox/pqQVkh6TdHilfD9Jy/KwCyWpFetjZjaUNT1IJI0DPgp0RMQ+wDBgOnAGsCgiJgGLcj+S9srD9wamAhdJGpZndzEwC5iUH1ObuCpmZkbrTm0NB0ZKGg5sC6wCjgDm5eHzgGm5+wjgioh4KSKeAFYAB0gaC4yKiMUREcD8yjRmZtYkTQ+SiPg5cD7wNLAaeD4ibgLGRMTqPM5qYJc8yThgZWUWXblsXO6uLd+IpFmSOiV1rlmzZiBXx8xsyGvFqa2dSEcZE4Fdge0kfaC3SeqURS/lGxdGzImIjojoaGtr29Qqm5lZL1pxauvdwBMRsSYi/ghcAxwEPJNPV5Gfn83jdwETKtOPJ50K68rdteVmZtZErQiSp4HJkrbNv7KaAjwKLARm5HFmANfm7oXAdEkjJE0kXVRfkk9/rZU0Oc/n+Mo0ZmbWJMObvcCIuFfSVcADwDrgQWAOsD2wQNJMUtgcncdfLmkB8Ege/5SIeCXP7mRgLjASuCE/zMysiZoeJAAR8Wng0zXFL5GOTuqNPxuYXae8E9hnwCtoZmb95n+2m5lZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFXGQmJlZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFXGQmJlZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFXGQmJlZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFXGQmJlZEQeJmZkVcZCYmVkRB4mZmRVxkJiZWREHiZmZFXGQmJlZEQeJmZkVaUmQSHqtpKsk/VjSo5IOlDRa0s2SHs/PO1XGP1PSCkmPSTq8Ur6fpGV52IWS1Ir1MTMbylp1RPIl4AcRsQfwZuBR4AxgUURMAhblfiTtBUwH9gamAhdJGpbnczEwC5iUH1ObuRJmZtaCIJE0CngH8HWAiHg5In4DHAHMy6PNA6bl7iOAKyLipYh4AlgBHCBpLDAqIhZHRADzK9OYmVmTtOKI5PXAGuCbkh6UdKmk7YAxEbEaID/vkscfB6ysTN+Vy8bl7tryjUiaJalTUueaNWsGdm3MzIa4VgTJcGBf4OKIeCvwIvk0Vg/qXfeIXso3LoyYExEdEdHR1ta2qfU1M7NetCJIuoCuiLg3919FCpZn8ukq8vOzlfEnVKYfD6zK5ePrlJuZWRP1K0gkHdyfsv6IiF8AKyW9MRdNAR4BFgIzctkM4NrcvRCYLmmEpImki+pL8umvtZIm519rHV+ZxszMmmR4P8f7N9JRQ19l/fUR4DJJWwM/A/6BFGoLJM0EngaOBoiI5ZIWkMJmHXBKRLyS53MyMBcYCdyQH2Zm1kS9BomkA4GDgDZJn6gMGgUMqz9V3yJiKdBRZ9CUHsafDcyuU94J7LO59TAzs3J9HZFsDWyfx9uhUv5b4KhGVcrMzAaPXoMkIm4Hbpc0NyKealKdzMxsEOnvNZIRkuYA7dVpIuJdjaiUmZkNHv0NkiuBrwKXAq/0Ma6ZmQ0h/Q2SdRFxcUNrYmZmg1J//5B4naQPSRqbW+kdLWl0Q2tmZmaDQn+PSLr/KHhapSxI7WaZmdkQ1q8giYiJja6ImZkNTv0KEknH1yuPiPkDWx0zMxts+ntqa/9K9zakf6A/QLoHiJmZDWH9PbX1kWq/pB2BbzWkRmZmNqhsbjPyvyO1wmtmZkNcf6+RXMf6m0YNA/YEFjSqUmZmNnj09xrJ+ZXudcBTEdHV08hmNrSNm7Abq7pW9j2iNdWu4yfw85VPD/h8+3uN5HZJY1h/0f3xAa+Jmf23saprJcdccnerq2E1vnviQQ2Zb3/vkPh+YAnpZlPvB+6V5Gbkzcys36e2PgXsHxHPAkhqA/6TdL91MzMbwvr7q62tukMk++UmTGtmZv+N9feI5AeSbgQuz/3HAN9vTJXMNtFWw5HU6lqYDVl93bP9DcCYiDhN0vuAQwABi4HLmlA/s769us4Xdrcwjbqoa1umvk5PXQCsBYiIayLiExHxcdLRyAWNrpyZmW35+gqS9oh4qLYwIjpJt901M7Mhrq8g2aaXYSMHsiJmZjY49RUk90n6YG2hpJnA/Y2pkpmZDSZ9/WrrVOB7ko5jfXB0AFsDf9fIipmZ2eDQa5BExDPAQZIOBfbJxf8REbc0vGZmZjYo9LetrVuBWxtcFzMzG4T873QzMyviIDEzsyIOEjMzK+IgMTOzIg4SMzMr0rIgkTRM0oOSrs/9oyXdLOnx/LxTZdwzJa2Q9Jikwyvl+0laloddKDcBa2bWdK08IvkY8Gil/wxgUURMAhblfiTtBUwH9gamAhdJGpanuRiYBUzKj6nNqbqZmXVrSZBIGg+8B7i0UnwEMC93zwOmVcqviIiXIuIJYAVwgKSxwKiIWBwRAcyvTGNmZk3SqiOSC4DTgVcrZWMiYjVAft4ll48DVlbG68pl43J3bflGJM2S1Cmpc82aNQOzBmZmBrQgSCT9DfBsRPS30cd61z2il/KNCyPmRERHRHS0tbX1c7FmZtYf/b3V7kA6GHivpP9JaqZ+lKRvA89IGhsRq/Npq+57xHcBEyrTjwdW5fLxdcrNzKyJmn5EEhFnRsT4iGgnXUS/JSI+ACwEZuTRZgDX5u6FwHRJIyRNJF1UX5JPf62VNDn/Wuv4yjRmZtYkrTgi6cm5wIJ8r5OngaMBImK5pAXAI8A64JSIeCVPczIwl3STrRvyw8zMmqilQRIRtwG35e5fAlN6GG82MLtOeSfrm7c3M7MW8D/bzcysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKND1IJE2QdKukRyUtl/SxXD5a0s2SHs/PO1WmOVPSCkmPSTq8Ur6fpGV52IWS1Oz1MTMb6lpxRLIO+KeI2BOYDJwiaS/gDGBRREwCFuV+8rDpwN7AVOAiScPyvC4GZgGT8mNqM1fEzMxaECQRsToiHsjda4FHgXHAEcC8PNo8YFruPgK4IiJeiogngBXAAZLGAqMiYnFEBDC/Mo2ZmTVJS6+RSGoH3grcC4yJiNWQwgbYJY82DlhZmawrl43L3bXlZmbWRC0LEknbA1cDp0bEb3sbtU5Z9FJeb1mzJHVK6lyzZs2mV9bMzHrUkiCR9BpSiFwWEdfk4mfy6Sry87O5vAuYUJl8PLAql4+vU76RiJgTER0R0dHW1jZwK2JmZi351ZaArwOPRsQXKoMWAjNy9wzg2kr5dEkjJE0kXVRfkk9/rZU0Oc/z+Mo0ZmbWJMNbsMyDgb8Hlklamsv+L3AusEDSTOBp4GiAiFguaQHwCOkXX6dExCt5upOBucBI4Ib8MDOzJmp6kETED6l/fQNgSg/TzAZm1ynvBPYZuNqZmdmm8j/bzcysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKyIg8TMzIo4SMzMrIiDxMzMijhIzMysiIPEzMyKOEjMzKzIoA8SSVMlPSZphaQzWl0fM7OhZlAHiaRhwFeAvwb2Ao6VtFdra2VmNrQM6iABDgBWRMTPIuJl4ArgiBbXycxsSFFEtLoOm03SUcDUiPjH3P/3wNsi4sM1480CZuXeNwKPNbWiW6adgedaXQnbgLfJlsnbJXldRLTVGzC82TUZYKpTtlEyRsQcYE7jqzN4SOqMiI5W18PW8zbZMnm79G2wn9rqAiZU+scDq1pUFzOzIWmwB8l9wCRJEyVtDUwHFra4TmZmQ8qgPrUVEeskfRi4ERgGfCMilre4WoOFT/VtebxNtkzeLn0Y1Bfbzcys9Qb7qS0zM2sxB4mZmRVxkDSBpJD0rUr/cElrJF3fwGV+UtKPJT0s6UeSjt+MebRL+l+NqF+zSPq7/PrvUSlrk3SvpAclvV3S0ZIelXSrpHc2YrtImtZbqwuSjs/barmkRyR9cjOW8VpJHyqraWtJeqGm/wRJX87dJ/X1Pq6O38d4t0nqrPR3SLqtj2l6/DxI+p6kaZX+xySdVem/WtL7+qpXZfxTJW1b6X+ht/FbzUHSHC8C+0gamfv/Cvh5oxYm6aS8jAMiYh/gHdT/z01f2oGmBomkgf4ByLHAD0m/6Os2BfhxRLw1Iu4EZgIfiohDB3jZVdNIzfhsRNJfA6cCh0XE3sC+wPObsYzXAk0NkgZsrx5FxFcjYv4AznKX/Nr3Vzs9fx7uBg4CkPRnwAvAgZXhB+Zx+pSbfjoV2LavcbcYEeFHgx+kN9W/Akfl/vnA/wGuz/0HkN5kD+bnN+byvYElwFLgIWASsB3wH8CPgIeBY+os72lg9x7q8iSwc+7uAG7L3X+Zl7M012MH4B7SDm0p8HFgG+CbwLI8zqF52hOAfweuA54APgx8Io9zDzA6j7c78APgfuBOYI9cPhf4AnAr8P8H8HXfnhTYf04KDoC35NdnTV6vT+ft8xhwHvDOynb5DPDJyvweBtpz9wcq2+YSYFhlW8/O2+ceYAxpB/Or/Nosrd02wB3Au3pYh9uAjty9M/BkL++NK4Df57LzSF8ezsv1Xtb9XsnreDuwAPgJcC5wXJ7fsu76AW3A1aSf2d8HHFx5XeYANwHfGejPSk3/CcCXa7cHsH9e78Xd61gZ/5r8Pnsc+Hwvr+tHgLvqfBaG5Xnel5dxYi7f4PNQM7+DgB/m7r8FPptfTwETgZ/mYcfm1/hh4HM1+4h/Bu4FzgZezuPd2tP7qtX7tQ3Wv9UVGAqP/CZ4E3AVaWe8lA13WKOA4bn73cDVufvfgONy99bASOBI4GuVee9Ys6wdgF/3UpcnqR8k11V2FNuTfhr+pzrm8n8Cvpm79yDtkLfJH94Vedlt+cN2Uh7vi8CpuXsRMCl3vw24JXfPBa4n74wH8HX/APD13H03sG/uPoG8c8r9t7F+Z13dLp+hTpAAe+bX6zW5/CLg+NwdwN/m7s8DZ1XW8age6vmr2u3YQ92qQVLvvdFO3qHm8iOBm0k7xjF5e43N6/ib3D2CFLafzdN8DLggd38HOCR37wY8Wnld7gdGNuCz8grrv9AszXWuFyQPAwfl7nPZMEh+BuxIem8+BUzo6XUFbgEOZcPPwqzKdhsBdJLC4E/vjTrzG5Ff062Bc4CpwLdIR6HHkb487prXp430+boFmFZ537y/3ue0t/fVlvIY1P8jGUwi4iFJ7aRvJN+vGbwjME/SJNIb5jW5fDHwKUnjgWsi4nFJy4DzJX2O9Ka+s2Zeok4zMf1wF/AFSZflZXVJG50NO4S0AyMifizpKdK3fUjfnNYCayU9T9rRQvpW9SZJ25O+tV1Zme+IyryvjIhXNqPevTkWuCB3X5H7HxiA+U4B9gPuy+syEng2D3uZFIqQdrZ/NQDLq6fee6N2nEOAy/Pr+oyk20nf5H8L3BcRqwEk/ZR0dAFpe3Wf4ns3sFdlvqMk7ZC7F0bE7xuwXr+PiLd090g6gbSTp1L2WmCHiOg+VfQd4G8qoyyKiOfzuI8ArwNW9rC8/wecRTpD0O0w0nv2qNy/I+mI7+WeKh0RL0laTjotOZm0s3896T3/VtIXmf1JYbUm1+0y0mnnfycF6NU9zZ/mva82i6+RNNdC4Hzg8pryfyHtiPchHRZvAxAR3wHeSzpdcaOkd0XET0g7sWXAOZLOrs4oIn4LvCjp9T3UYR3rt/s2lenOBf6RtFO8p3pxuqK36ywvVbpfrfS/Svr2tRXwm4h4S+WxZ2WaF3uZ9ybL56nfBVwq6UngNOAY1dnb9qL6WsH610vAvMp6vDEiPpOH/THy10bSzqE/X9aWk7ZpX3Wobq+N3ht1pi3ZXuTlHlhZz3H5ywIM8PbaRH1tw+q69boNIuIW0us6uWb+H6ms98SIuKn+HDZwNykYdoiIX5NOQR2UH3f1Ue8/9PFFanPeV03jIGmubwD/HBHLasp3ZP3F9xO6C3MY/CwiLiSF0Jsk7Qr8LiK+TQqlfess5xzgK5JG5fmMyi0gQzpk7t5pHVlZ1u4RsSwiPkc6lN8DWEs6XdXtDtJhOpL+nHS6o18tKeeAe0LS0Xl6SXpzf6bdTEcB8yPidRHRHhETSNcoDtmEeTxJfn0l7Us6vQHpFN1RknbJw0ZLel0f86p9LavOAT4v6X/k+Y2Q9NFKHbq3V/c35LrvjTrLuIMUnsMktZF2ckv6qGfVTaTrXd3LfEsv4zZN3kmvldS985/e2/j9MBs4vdJ/I3CypNdAeq9L2o7etyGksDiRdB0D0vWVyaTPyXLS9Y+/lLRzvqB+LOlaVT19LWuL4iBpoojoiogv1Rn0edLRxV2k89ndjgEelrSUtGOfD/wFsCSXfYp0aF7rYtKF6/skPUx6s/4uD/ss8CVJd5K+2XQ7tfunwqRvuTeQPgjr8s+HP066FjAsn177LnBCRFS//fXlOGBmXsZyGnvvmGOB79WUXc2m/QrtamB0fq1PJl2YJiIeIZ0OuUnSQ6TrEGP7mNcVwGn5J8e7VwdExPdJN2j7z3x65H7Wf+M8n7RTu5t0jaTbRu+NiPglcFfejueR1v8h0o7tFuD0iPjFJqz/R4EOSQ/lU0QnbcK0jTYTmCNpMemb/ub8yg340+u/plJ0KfAI8ED+/FxC2h61n4dad5NOZy3O811HOuXZGRGv5lOJZ5I+mz8CHoiIa3uo1hzgBkm3bu56NZObSDGzQUfS9hHxQu4+AxgbER9rcbWGrC3qPJuZWT+9R9KZpH3YU1ROCVvz+YjEzMyK+BqJmZkVcZCYmVkRB4mZmRVxkJiZWREHidlmkjS3uxmNwdbs96bITbPv2up62JbLQWI2MAZXs9+b5gRSg4NmdTlIbMiQdHp30yOSvijpltw9RdK3JR0mabGkByRdmRuaRNLZku7L/xifU9teV57nrsCt1X8iS5qd/wV9j6QxvdTraK2/AdkduWyYpPPych+SdGIu30rSRUo3wLpe0vcrR0VPSvrXvA6dkvaVdKOknyrdo6Z7eadV5vvZXNaudHOvr+V53yRpZJ53B3CZpKVaf08dsz9xkNhQcgfw9tzdAWyf21M6hNQI5lnAuyNiX1J7Y5/I4345IvbPjWqOZMOWZsntXa0i3Z+lu+Xc7YB7IuLNebkf7KVeZwOH53Hfm8tmAs9HxP6kVmM/KGki8D5Sc/F/QWpk88Caea2MiANJ93uZS2qfazLpXhdIOozUku0BpHuz7CfpHXnaScBXIt1c6zfAkRFxVX4tjssNGDaixV8b5PzPdhtK7iftOHcgtRD7AClQ3k5q+HAvUltVkO4rsThPd6ik00mnrkaT2gm7jt5tSrPfdwFzJS0g3ZQJem7K/BBSk/uvAr+o0xbTwvy8DNi+0rT/H5SaXz8sPx7M422f5/s08ERELK3Uub2PdTQDHCQ2hETEH5WalP8HUgN7D5HuvbE7qWXgmyPi2Oo0krYhNVbZERErJX2GSnPuveh3s98RcZKktwHvAZbmVna7mzK/saY+7+ljudXm4Gubih+e53tORFxSM992Nm5+3aexrF98asuGmjuAT+bnO0kt2i4l3TviYElvAJC0rVJT+d2h8Vy+ZnLUxrMECpr9VmrC/96IOBt4DphAz02Z/xA4Ml8rGUO6a9+muBH435XrP+OUm8PvxaBq0tyaz0ckNtTcSWp+f3FEvCjpD8CdEbFG6W58l0vqvnPjWRHxE0lfI50qepJ0H+96upv9Xl25TtJf5yndHVOke538iHS01E5qylykZs6nkZq2n0K61exPSPe46HcT6hFxk6Q9gcX5FN4LpFsS93ZTpbnAVyX9nnSjK18nsQ240UazQaa7CXWlu0AuAQ7exPuMmA0oH5GYDT7X5wvnWwP/4hCxVvMRiVmTSPoUcHRN8ZURMbsV9TEbKA4SMzMr4l9tmZlZEQeJmZkVcZCYmVkRB4mZmRX5L6EmD6io9qbPAAAAAElFTkSuQmCC",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.histplot(merged_df,x='wealth_segment')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "wealth_segment_profit  = merged_df.groupby('wealth_segment')['profit'].sum()\n",
    "state_profit = merged_df.groupby('state')['profit'].sum()\n",
    "car_profit = merged_df.groupby('owns_car')['profit'].sum()\n",
    "gender_profit = merged_df.groupby('gender')['profit'].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getAgeGroup(age):\n",
    "    if(age is np.nan):\n",
    "        return 'unknown'\n",
    "    if age < 18:\n",
    "        return \"below18\"\n",
    "    elif 18 <= age < 30:\n",
    "        return \"18-30\"\n",
    "    elif 30 <= age < 45:\n",
    "        return \"30-45\"\n",
    "    elif 45 <= age < 60:\n",
    "        return \"45-60\"\n",
    "    else:\n",
    "        return \"60+\"\n",
    "merged_df['age_group'] = merged_df['age'].apply(getAgeGroup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_group_profit = merged_df.groupby('age_group')['profit'].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "owns_car\n",
       "No     3.333940e+06\n",
       "Yes    2.928841e+06\n",
       "Name: profit, dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "car_profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gender\n",
       "F    3.176468e+06\n",
       "M    2.928692e+06\n",
       "Name: profit, dtype: float64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gender_profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age_group\n",
       "18-30      1.311646e+06\n",
       "30-45      2.586005e+06\n",
       "45-60      1.611723e+06\n",
       "60+        5.984080e+05\n",
       "below18    1.549982e+05\n",
       "Name: profit, dtype: float64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_group_profit"
   ]
  },
  {
   "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>age</th>\n",
       "      <th>tenure</th>\n",
       "      <th>profit</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>property_valuation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.439024</td>\n",
       "      <td>-0.007129</td>\n",
       "      <td>-0.037855</td>\n",
       "      <td>0.014142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tenure</th>\n",
       "      <td>0.439024</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.004782</td>\n",
       "      <td>-0.020642</td>\n",
       "      <td>-0.019417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>profit</th>\n",
       "      <td>-0.007129</td>\n",
       "      <td>0.004782</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.004286</td>\n",
       "      <td>-0.010312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <td>-0.037855</td>\n",
       "      <td>-0.020642</td>\n",
       "      <td>0.004286</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.012140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>property_valuation</th>\n",
       "      <td>0.014142</td>\n",
       "      <td>-0.019417</td>\n",
       "      <td>-0.010312</td>\n",
       "      <td>0.012140</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          age    tenure    profit  \\\n",
       "age                                  1.000000  0.439024 -0.007129   \n",
       "tenure                               0.439024  1.000000  0.004782   \n",
       "profit                              -0.007129  0.004782  1.000000   \n",
       "past_3_years_bike_related_purchases -0.037855 -0.020642  0.004286   \n",
       "property_valuation                   0.014142 -0.019417 -0.010312   \n",
       "\n",
       "                                     past_3_years_bike_related_purchases  \\\n",
       "age                                                            -0.037855   \n",
       "tenure                                                         -0.020642   \n",
       "profit                                                          0.004286   \n",
       "past_3_years_bike_related_purchases                             1.000000   \n",
       "property_valuation                                              0.012140   \n",
       "\n",
       "                                     property_valuation  \n",
       "age                                            0.014142  \n",
       "tenure                                        -0.019417  \n",
       "profit                                        -0.010312  \n",
       "past_3_years_bike_related_purchases            0.012140  \n",
       "property_valuation                             1.000000  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df[['age','tenure','profit','past_3_years_bike_related_purchases','property_valuation']].corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 19997 entries, 0 to 19996\n",
      "Data columns (total 32 columns):\n",
      " #   Column                               Non-Null Count  Dtype         \n",
      "---  ------                               --------------  -----         \n",
      " 0   transaction_id                       19997 non-null  int64         \n",
      " 1   product_id                           19997 non-null  int64         \n",
      " 2   customer_id                          19997 non-null  int64         \n",
      " 3   transaction_date                     19997 non-null  datetime64[ns]\n",
      " 4   online_order                         19637 non-null  float64       \n",
      " 5   order_status                         19997 non-null  object        \n",
      " 6   brand                                19800 non-null  object        \n",
      " 7   product_line                         19800 non-null  object        \n",
      " 8   product_class                        19800 non-null  object        \n",
      " 9   product_size                         19800 non-null  object        \n",
      " 10  list_price                           19997 non-null  float64       \n",
      " 11  standard_cost                        19800 non-null  float64       \n",
      " 12  product_first_sold_date              19800 non-null  float64       \n",
      " 13  profit                               19800 non-null  float64       \n",
      " 14  first_name                           19997 non-null  object        \n",
      " 15  last_name                            19355 non-null  object        \n",
      " 16  gender                               19542 non-null  object        \n",
      " 17  past_3_years_bike_related_purchases  19997 non-null  float64       \n",
      " 18  DOB                                  19551 non-null  datetime64[ns]\n",
      " 19  job_title                            17603 non-null  object        \n",
      " 20  job_industry_category                16768 non-null  object        \n",
      " 21  wealth_segment                       19997 non-null  object        \n",
      " 22  deceased_indicator                   19997 non-null  object        \n",
      " 23  owns_car                             19997 non-null  object        \n",
      " 24  tenure                               19551 non-null  float64       \n",
      " 25  age                                  19551 non-null  float64       \n",
      " 26  address                              19968 non-null  object        \n",
      " 27  postcode                             19968 non-null  float64       \n",
      " 28  state                                19968 non-null  object        \n",
      " 29  country                              19968 non-null  object        \n",
      " 30  property_valuation                   19968 non-null  float64       \n",
      " 31  age_group                            19997 non-null  object        \n",
      "dtypes: datetime64[ns](2), float64(10), int64(3), object(17)\n",
      "memory usage: 5.7+ MB\n"
     ]
    }
   ],
   "source": [
    "merged_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "countplot() takes from 0 to 1 positional arguments but 2 were given",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-73-3de8884680e3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0msns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcountplot\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmerged_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'brand'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'unknown'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmerged_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'wealth_segment'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'unknown'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m: countplot() takes from 0 to 1 positional arguments but 2 were given"
     ]
    }
   ],
   "source": [
    "sns.countplot(merged_df['brand'].fillna('unknown'), merged_df['wealth_segment'].fillna('unknown'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "wealth_segment     brand         \n",
       "Affluent Customer  Giant Bicycles     827\n",
       "                   Norco Bicycles     652\n",
       "                   OHM Cycles         750\n",
       "                   Solex             1040\n",
       "                   Trek Bicycles      764\n",
       "                   WeareA2B           777\n",
       "High Net Worth     Giant Bicycles     873\n",
       "                   Norco Bicycles     777\n",
       "                   OHM Cycles         751\n",
       "                   Solex             1071\n",
       "                   Trek Bicycles      733\n",
       "                   WeareA2B           841\n",
       "Mass Customer      Giant Bicycles    1612\n",
       "                   Norco Bicycles    1480\n",
       "                   OHM Cycles        1541\n",
       "                   Solex             2141\n",
       "                   Trek Bicycles     1493\n",
       "                   WeareA2B          1677\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = merged_df.groupby(['wealth_segment','brand'])\n",
    "t1['brand'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>brand</th>\n",
       "      <th>Giant Bicycles</th>\n",
       "      <th>Norco Bicycles</th>\n",
       "      <th>OHM Cycles</th>\n",
       "      <th>Solex</th>\n",
       "      <th>Trek Bicycles</th>\n",
       "      <th>WeareA2B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_class</th>\n",
       "      <th>age_group</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">high</th>\n",
       "      <th>18-30</th>\n",
       "      <td>135.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-45</th>\n",
       "      <td>348.0</td>\n",
       "      <td>223.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-60</th>\n",
       "      <td>203.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>136.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60+</th>\n",
       "      <td>88.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>below18</th>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">low</th>\n",
       "      <th>18-30</th>\n",
       "      <td>52.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-45</th>\n",
       "      <td>86.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>347.0</td>\n",
       "      <td>143.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-60</th>\n",
       "      <td>40.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60+</th>\n",
       "      <td>17.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>below18</th>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">medium</th>\n",
       "      <th>18-30</th>\n",
       "      <td>475.0</td>\n",
       "      <td>347.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>715.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>618.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-45</th>\n",
       "      <td>945.0</td>\n",
       "      <td>751.0</td>\n",
       "      <td>719.0</td>\n",
       "      <td>1352.0</td>\n",
       "      <td>793.0</td>\n",
       "      <td>1220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-60</th>\n",
       "      <td>633.0</td>\n",
       "      <td>411.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>504.0</td>\n",
       "      <td>798.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60+</th>\n",
       "      <td>232.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>284.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>below18</th>\n",
       "      <td>46.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "brand                    Giant Bicycles  Norco Bicycles  OHM Cycles   Solex  \\\n",
       "product_class age_group                                                       \n",
       "high          18-30               135.0           137.0       147.0   127.0   \n",
       "              30-45               348.0           223.0       343.0   242.0   \n",
       "              45-60               203.0           129.0       213.0   136.0   \n",
       "              60+                  88.0            66.0        53.0    53.0   \n",
       "              below18              11.0            11.0        15.0     8.0   \n",
       "low           18-30                52.0           127.0       142.0    72.0   \n",
       "              30-45                86.0           270.0       241.0   187.0   \n",
       "              45-60                40.0           158.0       168.0    97.0   \n",
       "              60+                  17.0            63.0        50.0    45.0   \n",
       "              below18               1.0            14.0         8.0     9.0   \n",
       "medium        18-30               475.0           347.0       341.0   715.0   \n",
       "              30-45               945.0           751.0       719.0  1352.0   \n",
       "              45-60               633.0           411.0       425.0   871.0   \n",
       "              60+                 232.0           171.0       143.0   280.0   \n",
       "              below18              46.0            31.0        34.0    58.0   \n",
       "\n",
       "brand                    Trek Bicycles  WeareA2B  \n",
       "product_class age_group                           \n",
       "high          18-30               63.0       NaN  \n",
       "              30-45              137.0       NaN  \n",
       "              45-60               84.0       NaN  \n",
       "              60+                 37.0       NaN  \n",
       "              below18              4.0       NaN  \n",
       "low           18-30              175.0      69.0  \n",
       "              30-45              347.0     143.0  \n",
       "              45-60              186.0      84.0  \n",
       "              60+                 63.0      25.0  \n",
       "              below18             15.0      10.0  \n",
       "medium        18-30              392.0     618.0  \n",
       "              30-45              793.0    1220.0  \n",
       "              45-60              504.0     798.0  \n",
       "              60+                167.0     284.0  \n",
       "              below18             23.0      44.0  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t2 = merged_df.groupby(['product_class','age_group','brand'])\n",
    "t2['brand'].count().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-45      8427\n",
       "45-60      5184\n",
       "18-30      4178\n",
       "60+        1864\n",
       "below18     344\n",
       "Name: age_group, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df['age_group'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>transaction_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>online_order</th>\n",
       "      <th>order_status</th>\n",
       "      <th>brand</th>\n",
       "      <th>product_line</th>\n",
       "      <th>product_class</th>\n",
       "      <th>product_size</th>\n",
       "      <th>...</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>age</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>age_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-02-25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11065</td>\n",
       "      <td>1</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-10-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18923</td>\n",
       "      <td>62</td>\n",
       "      <td>2950</td>\n",
       "      <td>2017-04-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Solex</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>984 Hoepker Court</td>\n",
       "      <td>3064.0</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-05-21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Trek Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>4 Shopko Circle</td>\n",
       "      <td>2196.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6862</td>\n",
       "      <td>4</td>\n",
       "      <td>3120</td>\n",
       "      <td>2017-10-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Standard</td>\n",
       "      <td>high</td>\n",
       "      <td>medium</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>4 Shopko Circle</td>\n",
       "      <td>2196.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>4900</td>\n",
       "      <td>66</td>\n",
       "      <td>50</td>\n",
       "      <td>2017-07-24</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Road</td>\n",
       "      <td>low</td>\n",
       "      <td>small</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>16.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>957 Veith Junction</td>\n",
       "      <td>2047.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>431</td>\n",
       "      <td>2017-09-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>18.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>6920 Dwight Circle</td>\n",
       "      <td>4173.0</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>923</th>\n",
       "      <td>17432</td>\n",
       "      <td>0</td>\n",
       "      <td>2135</td>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>15.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0 Moose Court</td>\n",
       "      <td>2063.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>12.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>4274</td>\n",
       "      <td>66</td>\n",
       "      <td>3261</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Road</td>\n",
       "      <td>low</td>\n",
       "      <td>small</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>4591 Del Sol Court</td>\n",
       "      <td>2745.0</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>8.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1109</th>\n",
       "      <td>14234</td>\n",
       "      <td>75</td>\n",
       "      <td>2528</td>\n",
       "      <td>2017-12-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Approved</td>\n",
       "      <td>Giant Bicycles</td>\n",
       "      <td>Touring</td>\n",
       "      <td>medium</td>\n",
       "      <td>large</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>15.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>3 Myrtle Terrace</td>\n",
       "      <td>4161.0</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7.0</td>\n",
       "      <td>30-45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>165 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      transaction_id  product_id  customer_id transaction_date  online_order  \\\n",
       "0                  1           2         2950       2017-02-25           0.0   \n",
       "1              11065           1         2950       2017-10-16           0.0   \n",
       "2              18923          62         2950       2017-04-26           0.0   \n",
       "3                  2           3         3120       2017-05-21           1.0   \n",
       "4               6862           4         3120       2017-10-05           0.0   \n",
       "...              ...         ...          ...              ...           ...   \n",
       "792             4900          66           50       2017-07-24           0.0   \n",
       "888              137           0          431       2017-09-23           0.0   \n",
       "923            17432           0         2135       2017-08-15           1.0   \n",
       "940             4274          66         3261       2017-10-15           1.0   \n",
       "1109           14234          75         2528       2017-12-05           0.0   \n",
       "\n",
       "     order_status           brand product_line product_class product_size  \\\n",
       "0        Approved           Solex     Standard        medium       medium   \n",
       "1        Approved  Giant Bicycles     Standard        medium       medium   \n",
       "2        Approved           Solex     Standard        medium       medium   \n",
       "3        Approved   Trek Bicycles     Standard        medium        large   \n",
       "4        Approved  Giant Bicycles     Standard          high       medium   \n",
       "...           ...             ...          ...           ...          ...   \n",
       "792      Approved  Giant Bicycles         Road           low        small   \n",
       "888      Approved             NaN          NaN           NaN          NaN   \n",
       "923      Approved             NaN          NaN           NaN          NaN   \n",
       "940      Approved  Giant Bicycles         Road           low        small   \n",
       "1109     Approved  Giant Bicycles      Touring        medium        large   \n",
       "\n",
       "      ...  deceased_indicator  owns_car  tenure   age             address  \\\n",
       "0     ...                   N       Yes    10.0  63.0   984 Hoepker Court   \n",
       "1     ...                   N       Yes    10.0  63.0   984 Hoepker Court   \n",
       "2     ...                   N       Yes    10.0  63.0   984 Hoepker Court   \n",
       "3     ...                   N       Yes    10.0  39.0     4 Shopko Circle   \n",
       "4     ...                   N       Yes    10.0  39.0     4 Shopko Circle   \n",
       "...   ...                 ...       ...     ...   ...                 ...   \n",
       "792   ...                   N       Yes    16.0  37.0  957 Veith Junction   \n",
       "888   ...                   N       Yes    18.0  62.0  6920 Dwight Circle   \n",
       "923   ...                   N       Yes    15.0  37.0       0 Moose Court   \n",
       "940   ...                   N       Yes     5.0  43.0  4591 Del Sol Court   \n",
       "1109  ...                   N        No    15.0  39.0    3 Myrtle Terrace   \n",
       "\n",
       "     postcode state    country property_valuation age_group  \n",
       "0      3064.0   VIC  Australia                6.0       60+  \n",
       "1      3064.0   VIC  Australia                6.0       60+  \n",
       "2      3064.0   VIC  Australia                6.0       60+  \n",
       "3      2196.0   NSW  Australia                5.0     30-45  \n",
       "4      2196.0   NSW  Australia                5.0     30-45  \n",
       "...       ...   ...        ...                ...       ...  \n",
       "792    2047.0   NSW  Australia               10.0     30-45  \n",
       "888    4173.0   QLD  Australia                7.0       60+  \n",
       "923    2063.0   NSW  Australia               12.0     30-45  \n",
       "940    2745.0   NSW  Australia                8.0     30-45  \n",
       "1109   4161.0   QLD  Australia                7.0     30-45  \n",
       "\n",
       "[165 rows x 32 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.groupby(['brand','product_class','product_line']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['transaction_id', 'product_id', 'customer_id', 'transaction_date',\n",
       "       'online_order', 'order_status', 'brand', 'product_line',\n",
       "       'product_class', 'product_size', 'list_price', 'standard_cost',\n",
       "       'product_first_sold_date', 'profit', 'first_name', 'last_name',\n",
       "       'gender', 'past_3_years_bike_related_purchases', 'DOB', 'job_title',\n",
       "       'job_industry_category', 'wealth_segment', 'deceased_indicator',\n",
       "       'owns_car', 'tenure', 'age', 'address', 'postcode', 'state', 'country',\n",
       "       'property_valuation', 'age_group'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "features = ['gender','owns_car','age_group','state','wealth_segment','past_3_years_bike_related_purchases','tenure','property_valuation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_df_x = merged_df[features]\n",
    "model_df_y = merged_df['profit']\n",
    "\n",
    "# eval_data = df_new_customer_list[features]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 19997 entries, 0 to 19996\n",
      "Data columns (total 8 columns):\n",
      " #   Column                               Non-Null Count  Dtype  \n",
      "---  ------                               --------------  -----  \n",
      " 0   gender                               19542 non-null  object \n",
      " 1   owns_car                             19997 non-null  object \n",
      " 2   age_group                            19997 non-null  object \n",
      " 3   state                                19968 non-null  object \n",
      " 4   wealth_segment                       19997 non-null  object \n",
      " 5   past_3_years_bike_related_purchases  19997 non-null  float64\n",
      " 6   tenure                               19551 non-null  float64\n",
      " 7   property_valuation                   19968 non-null  float64\n",
      "dtypes: float64(3), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "labelencoder = LabelEncoder()\n",
    "model_df_x.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\SINCHANA\\anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:2963: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self[k1] = value[k2]\n"
     ]
    }
   ],
   "source": [
    "model_df_x[['gender','owns_car','age_group','state','wealth_segment']] = model_df_x[['gender','owns_car','age_group','state','wealth_segment']].fillna('N')\n",
    "model_df_x[['gender','owns_car','age_group','state','wealth_segment']] = model_df_x[['gender','owns_car','age_group','state','wealth_segment']].apply(labelencoder.fit_transform)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_df_x = model_df_x.fillna(0)\n",
    "model_df_y = model_df_y.fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_df_y.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LinearRegression()"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "model = LinearRegression()\n",
    "train_x, test_x, train_y, test_y = train_test_split(model_df_x,model_df_y,test_size=0.2,random_state=2)\n",
    "model.fit(train_x, train_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "prof_predict = model.predict(test_x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "385.6982530536087"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.metrics import mean_absolute_error, mean_squared_error\n",
    "mean_absolute_error(prof_predict, test_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    19997.000000\n",
       "mean       313.185992\n",
       "std       1017.209210\n",
       "min          0.000000\n",
       "25%         33.327117\n",
       "50%         66.666113\n",
       "75%        206.320621\n",
       "max       9519.345238\n",
       "Name: profit, dtype: float64"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_df_y.describe()"
   ]
  },
  {
   "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "e2ad1ebe9f82f2304c3f06c7191729f955916537fd18be2e37e5c0adda29a584"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}