KPMG_Address.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "10bee100",
   "metadata": {},
   "source": [
    "# Customer Addresses Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37ac21b4",
   "metadata": {},
   "source": [
    "The data that we have is with impurities and before we proceed to start analyzing it or predicting anything we will clean the data. The excel file is divided into 3 main tables:\n",
    "\n",
    "1. Transactions data\n",
    "2. Customer demographics data\n",
    "3. Customer Address data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "617870e6",
   "metadata": {},
   "source": [
    "In this notebook we will clean customer address table and do some preprocessing to make it reliable for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8666aace",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ded5fb07",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "\n",
    "cwd = os.getcwd()\n",
    "df_addr = pd.read_excel(cwd+\"/KPMG_VI_New_raw_data_update_final.xlsx\",'CustomerAddress', header=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2156b706",
   "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>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>060 Morning Avenue</td>\n",
       "      <td>2016</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>6 Meadow Vale Court</td>\n",
       "      <td>2153</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>0 Holy Cross Court</td>\n",
       "      <td>4211</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>17979 Del Mar Point</td>\n",
       "      <td>2448</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Australia</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>9 Oakridge Court</td>\n",
       "      <td>3216</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3994</th>\n",
       "      <td>3999</td>\n",
       "      <td>1482 Hauk Trail</td>\n",
       "      <td>3064</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3995</th>\n",
       "      <td>4000</td>\n",
       "      <td>57042 Village Green Point</td>\n",
       "      <td>4511</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3996</th>\n",
       "      <td>4001</td>\n",
       "      <td>87 Crescent Oaks Alley</td>\n",
       "      <td>2756</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3997</th>\n",
       "      <td>4002</td>\n",
       "      <td>8194 Lien Street</td>\n",
       "      <td>4032</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3998</th>\n",
       "      <td>4003</td>\n",
       "      <td>320 Acker Drive</td>\n",
       "      <td>2251</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3999 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id                    address  postcode            state  \\\n",
       "0               1         060 Morning Avenue      2016  New South Wales   \n",
       "1               2        6 Meadow Vale Court      2153  New South Wales   \n",
       "2               4         0 Holy Cross Court      4211              QLD   \n",
       "3               5        17979 Del Mar Point      2448  New South Wales   \n",
       "4               6           9 Oakridge Court      3216              VIC   \n",
       "...           ...                        ...       ...              ...   \n",
       "3994         3999            1482 Hauk Trail      3064              VIC   \n",
       "3995         4000  57042 Village Green Point      4511              QLD   \n",
       "3996         4001     87 Crescent Oaks Alley      2756              NSW   \n",
       "3997         4002           8194 Lien Street      4032              QLD   \n",
       "3998         4003            320 Acker Drive      2251              NSW   \n",
       "\n",
       "        country  property_valuation  \n",
       "0     Australia                  10  \n",
       "1     Australia                  10  \n",
       "2     Australia                   9  \n",
       "3     Australia                   4  \n",
       "4     Australia                   9  \n",
       "...         ...                 ...  \n",
       "3994  Australia                   3  \n",
       "3995  Australia                   6  \n",
       "3996  Australia                  10  \n",
       "3997  Australia                   7  \n",
       "3998  Australia                   7  \n",
       "\n",
       "[3999 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_addr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b1317dae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1       1\n",
       "2676    1\n",
       "2663    1\n",
       "2664    1\n",
       "2665    1\n",
       "       ..\n",
       "1343    1\n",
       "1344    1\n",
       "1345    1\n",
       "1346    1\n",
       "4003    1\n",
       "Name: customer_id, Length: 3999, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_addr.customer_id.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61a748d6",
   "metadata": {},
   "source": [
    "There are few customers ids that are new in this table and are not available in the customer demographics data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c92c6a70",
   "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_addr.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6d603a79",
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_addr.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6fb7a278",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NSW                2054\n",
       "VIC                 939\n",
       "QLD                 838\n",
       "New South Wales      86\n",
       "Victoria             82\n",
       "Name: state, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_addr.state.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cc365601",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_addr.state.replace({\"New South Wales\": \"NSW\", \"Victoria\":\"VIC\"}, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8d23ff0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1     154\n",
       "2     143\n",
       "3     186\n",
       "4     214\n",
       "5     225\n",
       "6     238\n",
       "7     493\n",
       "8     646\n",
       "9     647\n",
       "10    577\n",
       "11    281\n",
       "12    195\n",
       "Name: property_valuation, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_addr.property_valuation.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8bdd04d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_addr.to_excel(cwd + \"/Transcation.xlsx\", \"Customer Address\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89bea55e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}