Benchmarking_v1.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\n",
    "import pickle\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Shivalik\\Desktop\\CMU\\M19\\Capstone\n",
      "C:\\Users\\Shivalik\\Desktop\\CMU\\M19\\Capstone\\Benchmarking\\test_merged_sample.pickle\n",
      "Initial pickle contains 914372 records\n"
     ]
    }
   ],
   "source": [
    "raw_df = pd.DataFrame()\n",
    "print(os.getcwd())\n",
    "pickle_dir = os.getcwd()+ \"\\\\Benchmarking\\\\test_merged_sample.pickle\"\n",
    "print(pickle_dir)\n",
    "raw_df = pd.read_pickle(pickle_dir)#test_merged_sample.pickle\")\n",
    "print(\"Initial pickle contains\", len(raw_df.index), \"records\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preprocessing for Benchmarking"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count the number of unique business_sids in the input dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 2811 unique customers in the dataset\n",
      "Wall time: 131 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "business_sid_list = raw_df.iloc[:,1].unique()\n",
    "total_customers_in_data = len(business_sid_list)\n",
    "print(\"There are\" , total_customers_in_data , \"unique customers in the dataset\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract relevant columns for benchmarking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>business_sid</th>\n",
       "      <th>weight</th>\n",
       "      <th>zone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1232555</th>\n",
       "      <td>SOJW8FQCGI</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1423326</th>\n",
       "      <td>XKVL2MMJL2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1516794</th>\n",
       "      <td>BE922CE458</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1738984</th>\n",
       "      <td>BE922CE458</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164310</th>\n",
       "      <td>VAQ6Q6TLDY</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        business_sid  weight zone\n",
       "1232555   SOJW8FQCGI     2.0    6\n",
       "1423326   XKVL2MMJL2     4.0    6\n",
       "1516794   BE922CE458    10.0    8\n",
       "1738984   BE922CE458    20.0    2\n",
       "164310    VAQ6Q6TLDY    15.0    8"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "benchmarking_df = raw_df[['business_sid', 'weight', 'zone']]\n",
    "benchmarking_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create pivot tables for volume and weight, and their respective zone-wise proportions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_volumeCount_pivotTable(arg_df):\n",
    "    tempvoldf = arg_df.groupby(['business_sid', 'zone']).count()\n",
    "    tempvoldf.columns = ['Count']\n",
    "    volumeCount_pivotTable = pd.pivot_table(tempvoldf, values = 'Count', index = ['business_sid'], columns = 'zone', \n",
    "                     aggfunc = np.sum, fill_value = 0)\n",
    "    if len(volumeCount_pivotTable)==total_customers_in_data:\n",
    "        return volumeCount_pivotTable\n",
    "    else:\n",
    "        return \"There is an error in your code\"\n",
    "    \n",
    "def create_volumeProportion_pivotTable(arg_df):\n",
    "    volumeProportion_pivotTable = arg_df.apply(lambda x: x/x.sum(), axis = 1)\n",
    "    if len(volumeProportion_pivotTable)==total_customers_in_data:\n",
    "        return volumeProportion_pivotTable\n",
    "    else:\n",
    "        return \"There is an error in your code\"\n",
    "\n",
    "    \n",
    "def create_weightCount_pivotTable(arg_df):\n",
    "    tempwtdf = arg_df.groupby(['business_sid', 'zone']).sum()\n",
    "    weightSum_pivotTable = pd.pivot_table(tempwtdf, values = 'weight', index = ['business_sid'], columns = 'zone',\n",
    "                                         aggfunc = np.sum, fill_value = 0)\n",
    "    if len(weightSum_pivotTable)==total_customers_in_data:\n",
    "        return weightSum_pivotTable\n",
    "    else:\n",
    "        return \"There is an error in your code\"\n",
    "    \n",
    "\n",
    "def create_weightProportion_pivotTable(arg_df):\n",
    "    weightProportion_pivotTable= arg_df.apply(lambda x: x/x.sum(), axis = 1)\n",
    "    if len(weightProportion_pivotTable)==total_customers_in_data:\n",
    "        return weightProportion_pivotTable\n",
    "    else:\n",
    "        return \"There is an error in your code\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_PivotTables(arg_df):\n",
    "    a = create_volumeCount_pivotTable(arg_df)\n",
    "    #print(a.head())\n",
    "    b = create_volumeProportion_pivotTable(a)\n",
    "    #print(b.head())\n",
    "    c = create_weightCount_pivotTable(arg_df)\n",
    "    #print(c.head())\n",
    "    d = create_weightProportion_pivotTable(c)\n",
    "    #print(d.head())\n",
    "    return a,b,c,d\n",
    "    "
   ]
  },
  {
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2505</td>\n",
       "      <td>1556</td>\n",
       "      <td>1193</td>\n",
       "      <td>711</td>\n",
       "      <td>61</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>37</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone          0  1     2     3     4    5   6   7   8  9\n",
       "business_sid                                            \n",
       "004BB4E8C4    0  0     1     1     1    1   2   0   1  0\n",
       "0071F0E134    0  0  2505  1556  1193  711  61  20   3  0\n",
       "008936A589    0  0    28     9     8   14   9   1  18  0\n",
       "009F7F037A    0  0     7    18    12   37  19   6  31  0\n",
       "00B9362597    0  0    70    22     6   17  12   4   0  0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vc_table_test = create_volumeCount_pivotTable(benchmarking_df)\n",
    "vc_table_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.414118</td>\n",
       "      <td>0.257233</td>\n",
       "      <td>0.197223</td>\n",
       "      <td>0.117540</td>\n",
       "      <td>0.010084</td>\n",
       "      <td>0.003306</td>\n",
       "      <td>0.000496</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.321839</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.091954</td>\n",
       "      <td>0.160920</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.011494</td>\n",
       "      <td>0.206897</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.053846</td>\n",
       "      <td>0.138462</td>\n",
       "      <td>0.092308</td>\n",
       "      <td>0.284615</td>\n",
       "      <td>0.146154</td>\n",
       "      <td>0.046154</td>\n",
       "      <td>0.238462</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.534351</td>\n",
       "      <td>0.167939</td>\n",
       "      <td>0.045802</td>\n",
       "      <td>0.129771</td>\n",
       "      <td>0.091603</td>\n",
       "      <td>0.030534</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2         3         4         5         6  \\\n",
       "business_sid                                                               \n",
       "004BB4E8C4    0.0  0.0  0.142857  0.142857  0.142857  0.142857  0.285714   \n",
       "0071F0E134    0.0  0.0  0.414118  0.257233  0.197223  0.117540  0.010084   \n",
       "008936A589    0.0  0.0  0.321839  0.103448  0.091954  0.160920  0.103448   \n",
       "009F7F037A    0.0  0.0  0.053846  0.138462  0.092308  0.284615  0.146154   \n",
       "00B9362597    0.0  0.0  0.534351  0.167939  0.045802  0.129771  0.091603   \n",
       "\n",
       "zone                 7         8    9  \n",
       "business_sid                           \n",
       "004BB4E8C4    0.000000  0.142857  0.0  \n",
       "0071F0E134    0.003306  0.000496  0.0  \n",
       "008936A589    0.011494  0.206897  0.0  \n",
       "009F7F037A    0.046154  0.238462  0.0  \n",
       "00B9362597    0.030534  0.000000  0.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vp_table_test = create_volumeProportion_pivotTable(vc_table_test)\n",
    "vp_table_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59686.60</td>\n",
       "      <td>31323.3</td>\n",
       "      <td>26509.7</td>\n",
       "      <td>16345.8</td>\n",
       "      <td>1834.00</td>\n",
       "      <td>464.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>838.73</td>\n",
       "      <td>312.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>312.76</td>\n",
       "      <td>47.0</td>\n",
       "      <td>210.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>127.00</td>\n",
       "      <td>384.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>307.00</td>\n",
       "      <td>79.0</td>\n",
       "      <td>726.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1421.40</td>\n",
       "      <td>462.4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>407.6</td>\n",
       "      <td>156.00</td>\n",
       "      <td>125.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2        3        4        5        6      7  \\\n",
       "business_sid                                                                  \n",
       "004BB4E8C4    0.0  0.0      3.00      4.0      7.0     24.0    17.00    0.0   \n",
       "0071F0E134    0.0  0.0  59686.60  31323.3  26509.7  16345.8  1834.00  464.0   \n",
       "008936A589    0.0  0.0    838.73    312.0    193.0    400.0   312.76   47.0   \n",
       "009F7F037A    0.0  0.0    127.00    384.0    222.0    717.0   307.00   79.0   \n",
       "00B9362597    0.0  0.0   1421.40    462.4    113.0    407.6   156.00  125.0   \n",
       "\n",
       "zone              8    9  \n",
       "business_sid              \n",
       "004BB4E8C4      2.0  0.0  \n",
       "0071F0E134     24.0  0.0  \n",
       "008936A589    210.7  0.0  \n",
       "009F7F037A    726.0  0.0  \n",
       "00B9362597      0.0  0.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wc_table_test = create_weightCount_pivotTable(benchmarking_df)\n",
    "wc_table_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.052632</td>\n",
       "      <td>0.070175</td>\n",
       "      <td>0.122807</td>\n",
       "      <td>0.421053</td>\n",
       "      <td>0.298246</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.035088</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.438268</td>\n",
       "      <td>0.230001</td>\n",
       "      <td>0.194656</td>\n",
       "      <td>0.120024</td>\n",
       "      <td>0.013467</td>\n",
       "      <td>0.003407</td>\n",
       "      <td>0.000176</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.362429</td>\n",
       "      <td>0.134820</td>\n",
       "      <td>0.083399</td>\n",
       "      <td>0.172847</td>\n",
       "      <td>0.135149</td>\n",
       "      <td>0.020309</td>\n",
       "      <td>0.091047</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.049571</td>\n",
       "      <td>0.149883</td>\n",
       "      <td>0.086651</td>\n",
       "      <td>0.279859</td>\n",
       "      <td>0.119828</td>\n",
       "      <td>0.030835</td>\n",
       "      <td>0.283372</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.529307</td>\n",
       "      <td>0.172190</td>\n",
       "      <td>0.042079</td>\n",
       "      <td>0.151784</td>\n",
       "      <td>0.058092</td>\n",
       "      <td>0.046548</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2         3         4         5         6  \\\n",
       "business_sid                                                               \n",
       "004BB4E8C4    0.0  0.0  0.052632  0.070175  0.122807  0.421053  0.298246   \n",
       "0071F0E134    0.0  0.0  0.438268  0.230001  0.194656  0.120024  0.013467   \n",
       "008936A589    0.0  0.0  0.362429  0.134820  0.083399  0.172847  0.135149   \n",
       "009F7F037A    0.0  0.0  0.049571  0.149883  0.086651  0.279859  0.119828   \n",
       "00B9362597    0.0  0.0  0.529307  0.172190  0.042079  0.151784  0.058092   \n",
       "\n",
       "zone                 7         8    9  \n",
       "business_sid                           \n",
       "004BB4E8C4    0.000000  0.035088  0.0  \n",
       "0071F0E134    0.003407  0.000176  0.0  \n",
       "008936A589    0.020309  0.091047  0.0  \n",
       "009F7F037A    0.030835  0.283372  0.0  \n",
       "00B9362597    0.046548  0.000000  0.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wp_table_test = create_weightProportion_pivotTable(wc_table_test)\n",
    "wp_table_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#These table outputs are just a sample for testing\n",
    "\n",
    "vsitestingdf = vc_table_test.head()\n",
    "vditestingdf = vp_table_test.head()\n",
    "\n",
    "wsitestingdf = wc_table_test.head()\n",
    "wditestingdf = wp_table_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2505</td>\n",
       "      <td>1556</td>\n",
       "      <td>1193</td>\n",
       "      <td>711</td>\n",
       "      <td>61</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>37</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone          0  1     2     3     4    5   6   7   8  9\n",
       "business_sid                                            \n",
       "004BB4E8C4    0  0     1     1     1    1   2   0   1  0\n",
       "0071F0E134    0  0  2505  1556  1193  711  61  20   3  0\n",
       "008936A589    0  0    28     9     8   14   9   1  18  0\n",
       "009F7F037A    0  0     7    18    12   37  19   6  31  0\n",
       "00B9362597    0  0    70    22     6   17  12   4   0  0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vsitestingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.414118</td>\n",
       "      <td>0.257233</td>\n",
       "      <td>0.197223</td>\n",
       "      <td>0.117540</td>\n",
       "      <td>0.010084</td>\n",
       "      <td>0.003306</td>\n",
       "      <td>0.000496</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.321839</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.091954</td>\n",
       "      <td>0.160920</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.011494</td>\n",
       "      <td>0.206897</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.053846</td>\n",
       "      <td>0.138462</td>\n",
       "      <td>0.092308</td>\n",
       "      <td>0.284615</td>\n",
       "      <td>0.146154</td>\n",
       "      <td>0.046154</td>\n",
       "      <td>0.238462</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.534351</td>\n",
       "      <td>0.167939</td>\n",
       "      <td>0.045802</td>\n",
       "      <td>0.129771</td>\n",
       "      <td>0.091603</td>\n",
       "      <td>0.030534</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2         3         4         5         6  \\\n",
       "business_sid                                                               \n",
       "004BB4E8C4    0.0  0.0  0.142857  0.142857  0.142857  0.142857  0.285714   \n",
       "0071F0E134    0.0  0.0  0.414118  0.257233  0.197223  0.117540  0.010084   \n",
       "008936A589    0.0  0.0  0.321839  0.103448  0.091954  0.160920  0.103448   \n",
       "009F7F037A    0.0  0.0  0.053846  0.138462  0.092308  0.284615  0.146154   \n",
       "00B9362597    0.0  0.0  0.534351  0.167939  0.045802  0.129771  0.091603   \n",
       "\n",
       "zone                 7         8    9  \n",
       "business_sid                           \n",
       "004BB4E8C4    0.000000  0.142857  0.0  \n",
       "0071F0E134    0.003306  0.000496  0.0  \n",
       "008936A589    0.011494  0.206897  0.0  \n",
       "009F7F037A    0.046154  0.238462  0.0  \n",
       "00B9362597    0.030534  0.000000  0.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vditestingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59686.60</td>\n",
       "      <td>31323.3</td>\n",
       "      <td>26509.7</td>\n",
       "      <td>16345.8</td>\n",
       "      <td>1834.00</td>\n",
       "      <td>464.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>838.73</td>\n",
       "      <td>312.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>312.76</td>\n",
       "      <td>47.0</td>\n",
       "      <td>210.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>127.00</td>\n",
       "      <td>384.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>307.00</td>\n",
       "      <td>79.0</td>\n",
       "      <td>726.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1421.40</td>\n",
       "      <td>462.4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>407.6</td>\n",
       "      <td>156.00</td>\n",
       "      <td>125.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2        3        4        5        6      7  \\\n",
       "business_sid                                                                  \n",
       "004BB4E8C4    0.0  0.0      3.00      4.0      7.0     24.0    17.00    0.0   \n",
       "0071F0E134    0.0  0.0  59686.60  31323.3  26509.7  16345.8  1834.00  464.0   \n",
       "008936A589    0.0  0.0    838.73    312.0    193.0    400.0   312.76   47.0   \n",
       "009F7F037A    0.0  0.0    127.00    384.0    222.0    717.0   307.00   79.0   \n",
       "00B9362597    0.0  0.0   1421.40    462.4    113.0    407.6   156.00  125.0   \n",
       "\n",
       "zone              8    9  \n",
       "business_sid              \n",
       "004BB4E8C4      2.0  0.0  \n",
       "0071F0E134     24.0  0.0  \n",
       "008936A589    210.7  0.0  \n",
       "009F7F037A    726.0  0.0  \n",
       "00B9362597      0.0  0.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wsitestingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>zone</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>business_sid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>004BB4E8C4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.052632</td>\n",
       "      <td>0.070175</td>\n",
       "      <td>0.122807</td>\n",
       "      <td>0.421053</td>\n",
       "      <td>0.298246</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.035088</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.438268</td>\n",
       "      <td>0.230001</td>\n",
       "      <td>0.194656</td>\n",
       "      <td>0.120024</td>\n",
       "      <td>0.013467</td>\n",
       "      <td>0.003407</td>\n",
       "      <td>0.000176</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.362429</td>\n",
       "      <td>0.134820</td>\n",
       "      <td>0.083399</td>\n",
       "      <td>0.172847</td>\n",
       "      <td>0.135149</td>\n",
       "      <td>0.020309</td>\n",
       "      <td>0.091047</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.049571</td>\n",
       "      <td>0.149883</td>\n",
       "      <td>0.086651</td>\n",
       "      <td>0.279859</td>\n",
       "      <td>0.119828</td>\n",
       "      <td>0.030835</td>\n",
       "      <td>0.283372</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.529307</td>\n",
       "      <td>0.172190</td>\n",
       "      <td>0.042079</td>\n",
       "      <td>0.151784</td>\n",
       "      <td>0.058092</td>\n",
       "      <td>0.046548</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "zone            0    1         2         3         4         5         6  \\\n",
       "business_sid                                                               \n",
       "004BB4E8C4    0.0  0.0  0.052632  0.070175  0.122807  0.421053  0.298246   \n",
       "0071F0E134    0.0  0.0  0.438268  0.230001  0.194656  0.120024  0.013467   \n",
       "008936A589    0.0  0.0  0.362429  0.134820  0.083399  0.172847  0.135149   \n",
       "009F7F037A    0.0  0.0  0.049571  0.149883  0.086651  0.279859  0.119828   \n",
       "00B9362597    0.0  0.0  0.529307  0.172190  0.042079  0.151784  0.058092   \n",
       "\n",
       "zone                 7         8    9  \n",
       "business_sid                           \n",
       "004BB4E8C4    0.000000  0.035088  0.0  \n",
       "0071F0E134    0.003407  0.000176  0.0  \n",
       "008936A589    0.020309  0.091047  0.0  \n",
       "009F7F037A    0.030835  0.283372  0.0  \n",
       "00B9362597    0.046548  0.000000  0.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wditestingdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate individual indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_VSI(sid_in_focus, vsiPivotTable):\n",
    "    sid_list = vsiPivotTable.index.values\n",
    "    sid_count = len(sid_list)\n",
    "    vsi_list = np.empty(sid_count)\n",
    "    for x in range(sid_count):\n",
    "        if(sid_list[x]!=sid_in_focus):\n",
    "            vsi_numerator = vsiPivotTable.loc[sid_in_focus].combine(vsiPivotTable.iloc[x], min, 0)\n",
    "            vsi_denominator = vsiPivotTable.loc[sid_in_focus].combine(vsiPivotTable.iloc[x], max, 0)\n",
    "            vsi_list[x] = vsi_numerator.sum()/vsi_denominator.sum()\n",
    "        else:\n",
    "            vsi_list[x] = -1          \n",
    "    return vsi_list\n",
    "\n",
    "def calculate_VDI(sid_in_focus, vdiPivotTable):\n",
    "    sid_list = vdiPivotTable.index.values\n",
    "    sid_count = len(sid_list)\n",
    "    vdi_list = np.empty(sid_count)\n",
    "    for x in range(sid_count):\n",
    "        if(sid_list[x]!=sid_in_focus):\n",
    "            vdi_numerator = vdiPivotTable.loc[sid_in_focus].combine(vdiPivotTable.iloc[x], min, 0)\n",
    "            vdi_denominator = vdiPivotTable.loc[sid_in_focus].combine(vdiPivotTable.iloc[x], max, 0)\n",
    "            vdi_list[x] = vdi_numerator.sum()/vdi_denominator.sum()\n",
    "        else:\n",
    "            vdi_list[x] = -1          \n",
    "    return vdi_list\n",
    "\n",
    "def calculate_WSI(sid_in_focus, wsiPivotTable):\n",
    "    sid_list = wsiPivotTable.index.values\n",
    "    sid_count = len(sid_list)\n",
    "    wsi_list = np.empty(sid_count)\n",
    "    for x in range(sid_count):\n",
    "        if(sid_list[x]!=sid_in_focus):\n",
    "            wsi_numerator = wsiPivotTable.loc[sid_in_focus].combine(wsiPivotTable.iloc[x], min, 0)\n",
    "            wsi_denominator = wsiPivotTable.loc[sid_in_focus].combine(wsiPivotTable.iloc[x], max, 0)\n",
    "            wsi_list[x] = wsi_numerator.sum()/wsi_denominator.sum()\n",
    "        else:\n",
    "            wsi_list[x] = -1          \n",
    "    return wsi_list\n",
    "\n",
    "def calculate_WDI(sid_in_focus, wdiPivotTable):\n",
    "    sid_list = wdiPivotTable.index.values\n",
    "    sid_count = len(sid_list)\n",
    "    wdi_list = np.empty(sid_count)\n",
    "    for x in range(sid_count):\n",
    "        if(sid_list[x]!=sid_in_focus):\n",
    "            wdi_numerator = wdiPivotTable.loc[sid_in_focus].combine(wdiPivotTable.iloc[x], min, 0)\n",
    "            wdi_denominator = wdiPivotTable.loc[sid_in_focus].combine(wdiPivotTable.iloc[x], max, 0)\n",
    "            wdi_list[x] = wdi_numerator.sum()/wdi_denominator.sum()\n",
    "        else:\n",
    "            wdi_list[x] = -1          \n",
    "    return wdi_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.00115722, -1.        ,  0.01187335,  0.0167846 ,  0.02165647])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = calculate_VSI(sid_in_focus='0071F0E134', vsiPivotTable= vsitestingdf)\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.3857054 , -1.        ,  0.48002126,  0.2626598 ,  0.61132987])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = calculate_VDI(sid_in_focus='0071F0E134', vdiPivotTable= vditestingdf)\n",
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 4.18540922e-04, -1.00000000e+00,  1.56003963e-02,  1.35876116e-02,\n",
       "        1.97184174e-02])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = calculate_WSI(sid_in_focus='0071F0E134', wsiPivotTable= wsitestingdf)\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.23402045, -1.        ,  0.55972472,  0.26837476,  0.65212253])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = calculate_WDI(sid_in_focus='0071F0E134', wdiPivotTable= wditestingdf)\n",
    "d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Similarity Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#weight_array is the parameter for adjusting weights in the Similarity Score\n",
    "weight_array = np.array([0.25,0.25,0.25,0.25])\n",
    "\n",
    "#allPivots is the parameter for adjusting # of business_sids for which Similarity Score is calculated\n",
    "#To select more than 5 business_sids,modify the x.head() argument below\n",
    "allPivots = [x.head() for x in create_PivotTables(benchmarking_df)]\n",
    "    \n",
    "def calculate_similarity_score(sid_in_focus, pivotTables = allPivots, weights = weight_array,): \n",
    "    \n",
    "    vsiList = calculate_VSI(sid_in_focus, vsiPivotTable= pivotTables[0]) \n",
    "    vdiList = calculate_VDI(sid_in_focus, vdiPivotTable= pivotTables[1]) \n",
    "    wsiList = calculate_WSI(sid_in_focus, wsiPivotTable= pivotTables[2]) \n",
    "    wdiList = calculate_WDI(sid_in_focus, wdiPivotTable= pivotTables[3]) \n",
    "    \n",
    "    ss_array = (weight_array[0]*vsiList + \\\n",
    "                weight_array[1]*vdiList + \\\n",
    "                weight_array[2]*wsiList + \\\n",
    "                weight_array[3]*wdiList)*100\n",
    " \n",
    "    return np.round(ss_array,3)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Similarity Score Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function only takes the list of all relevant sids as an input and generates the complete similarity score table\n",
    "#Note: This table takes up a large amount of memory if all business_sids are provided in allPivots\n",
    "\n",
    "def create_similarity_score_table(sid_list):\n",
    "    similarity_score_table = pd.DataFrame(index = sid_list, columns = sid_list).fillna(-1)\n",
    "    for x in sid_list:\n",
    "        similarity_score_table.loc[x] = calculate_similarity_score(sid_in_focus = x, pivotTables = allPivots, \n",
    "                                                                   weights = weight_array)\n",
    "    return similarity_score_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 3.22 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "allPivots = [x.head(10) for x in create_PivotTables(benchmarking_df)]\n",
    "sid_list = allPivots[0].index.values\n",
    "create_similarity_score_table(sid_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>004BB4E8C4</th>\n",
       "      <th>0071F0E134</th>\n",
       "      <th>008936A589</th>\n",
       "      <th>009F7F037A</th>\n",
       "      <th>00B9362597</th>\n",
       "      <th>00BE684C10</th>\n",
       "      <th>00D34BA35B</th>\n",
       "      <th>00E31A2581</th>\n",
       "      <th>00EF728145</th>\n",
       "      <th>010010C13F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>004BB4E8C4</th>\n",
       "      <td>-100.000</td>\n",
       "      <td>15.533</td>\n",
       "      <td>26.383</td>\n",
       "      <td>27.654</td>\n",
       "      <td>16.964</td>\n",
       "      <td>7.486</td>\n",
       "      <td>20.284</td>\n",
       "      <td>22.660</td>\n",
       "      <td>27.683</td>\n",
       "      <td>40.621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0071F0E134</th>\n",
       "      <td>15.533</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>26.680</td>\n",
       "      <td>14.035</td>\n",
       "      <td>32.621</td>\n",
       "      <td>13.549</td>\n",
       "      <td>6.658</td>\n",
       "      <td>46.625</td>\n",
       "      <td>36.635</td>\n",
       "      <td>12.581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>008936A589</th>\n",
       "      <td>26.383</td>\n",
       "      <td>26.680</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>50.177</td>\n",
       "      <td>55.202</td>\n",
       "      <td>10.647</td>\n",
       "      <td>37.294</td>\n",
       "      <td>27.244</td>\n",
       "      <td>34.896</td>\n",
       "      <td>28.128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>009F7F037A</th>\n",
       "      <td>27.654</td>\n",
       "      <td>14.035</td>\n",
       "      <td>50.177</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>32.133</td>\n",
       "      <td>1.549</td>\n",
       "      <td>52.746</td>\n",
       "      <td>25.963</td>\n",
       "      <td>19.596</td>\n",
       "      <td>34.338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00B9362597</th>\n",
       "      <td>16.964</td>\n",
       "      <td>32.621</td>\n",
       "      <td>55.202</td>\n",
       "      <td>32.133</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>18.331</td>\n",
       "      <td>21.029</td>\n",
       "      <td>24.482</td>\n",
       "      <td>47.021</td>\n",
       "      <td>14.230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00BE684C10</th>\n",
       "      <td>7.486</td>\n",
       "      <td>13.549</td>\n",
       "      <td>10.647</td>\n",
       "      <td>1.549</td>\n",
       "      <td>18.331</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>2.480</td>\n",
       "      <td>5.843</td>\n",
       "      <td>19.875</td>\n",
       "      <td>3.359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00D34BA35B</th>\n",
       "      <td>20.284</td>\n",
       "      <td>6.658</td>\n",
       "      <td>37.294</td>\n",
       "      <td>52.746</td>\n",
       "      <td>21.029</td>\n",
       "      <td>2.480</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>15.804</td>\n",
       "      <td>13.671</td>\n",
       "      <td>30.902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00E31A2581</th>\n",
       "      <td>22.660</td>\n",
       "      <td>46.625</td>\n",
       "      <td>27.244</td>\n",
       "      <td>25.963</td>\n",
       "      <td>24.482</td>\n",
       "      <td>5.843</td>\n",
       "      <td>15.804</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>22.856</td>\n",
       "      <td>21.848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00EF728145</th>\n",
       "      <td>27.683</td>\n",
       "      <td>36.635</td>\n",
       "      <td>34.896</td>\n",
       "      <td>19.596</td>\n",
       "      <td>47.021</td>\n",
       "      <td>19.875</td>\n",
       "      <td>13.671</td>\n",
       "      <td>22.856</td>\n",
       "      <td>-100.000</td>\n",
       "      <td>23.885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010010C13F</th>\n",
       "      <td>40.621</td>\n",
       "      <td>12.581</td>\n",
       "      <td>28.128</td>\n",
       "      <td>34.338</td>\n",
       "      <td>14.230</td>\n",
       "      <td>3.359</td>\n",
       "      <td>30.902</td>\n",
       "      <td>21.848</td>\n",
       "      <td>23.885</td>\n",
       "      <td>-100.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            004BB4E8C4  0071F0E134  008936A589  009F7F037A  00B9362597  \\\n",
       "004BB4E8C4    -100.000      15.533      26.383      27.654      16.964   \n",
       "0071F0E134      15.533    -100.000      26.680      14.035      32.621   \n",
       "008936A589      26.383      26.680    -100.000      50.177      55.202   \n",
       "009F7F037A      27.654      14.035      50.177    -100.000      32.133   \n",
       "00B9362597      16.964      32.621      55.202      32.133    -100.000   \n",
       "00BE684C10       7.486      13.549      10.647       1.549      18.331   \n",
       "00D34BA35B      20.284       6.658      37.294      52.746      21.029   \n",
       "00E31A2581      22.660      46.625      27.244      25.963      24.482   \n",
       "00EF728145      27.683      36.635      34.896      19.596      47.021   \n",
       "010010C13F      40.621      12.581      28.128      34.338      14.230   \n",
       "\n",
       "            00BE684C10  00D34BA35B  00E31A2581  00EF728145  010010C13F  \n",
       "004BB4E8C4       7.486      20.284      22.660      27.683      40.621  \n",
       "0071F0E134      13.549       6.658      46.625      36.635      12.581  \n",
       "008936A589      10.647      37.294      27.244      34.896      28.128  \n",
       "009F7F037A       1.549      52.746      25.963      19.596      34.338  \n",
       "00B9362597      18.331      21.029      24.482      47.021      14.230  \n",
       "00BE684C10    -100.000       2.480       5.843      19.875       3.359  \n",
       "00D34BA35B       2.480    -100.000      15.804      13.671      30.902  \n",
       "00E31A2581       5.843      15.804    -100.000      22.856      21.848  \n",
       "00EF728145      19.875      13.671      22.856    -100.000      23.885  \n",
       "010010C13F       3.359      30.902      21.848      23.885    -100.000  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_similarity_score_table(sid_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sample IDs from Jose:\n",
    "'''\n",
    "7B8E9E45F5\n",
    "E0518749DD\n",
    "924052DDF5\n",
    "AACOENJW\n",
    "FHVD7BDTDG\n",
    "'''"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}