Capstone AAA - Graphics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EDA- Graphics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cargar librerias\n",
    "from __future__ import absolute_import, division, print_function, unicode_literals\n",
    "import os\n",
    "from datetime import datetime\n",
    "import datetime as dt  # Used to manage dates\n",
    "import warnings                   # To ignore the warnings warnings.filterwarnings(\"ignore\")\n",
    "import requests\n",
    "import itertools # Create combinations of fields\n",
    "import pymysql # Import MySql \n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pylab import rcParams \n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.metrics import r2_score, mean_absolute_error, mean_squared_error, mean_squared_log_error\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.ensemble import IsolationForest\n",
    "from sklearn.svm import OneClassSVM\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.model_selection import cross_val_score\n",
    "\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from sklearn.preprocessing import minmax_scale\n",
    "from sklearn.preprocessing import MaxAbsScaler\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.preprocessing import RobustScaler\n",
    "from sklearn.preprocessing import Normalizer\n",
    "from sklearn.preprocessing import QuantileTransformer\n",
    "from sklearn.preprocessing import PowerTransformer\n",
    "\n",
    "import plotly.offline as py\n",
    "from plotly import graph_objs as go\n",
    "from plotly import figure_factory as ff\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots\n",
    "\n",
    "# Definir el ambiente de SQL\n",
    "pymysql.install_as_MySQLdb()\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "warnings.simplefilter(\"ignore\", DeprecationWarning)\n",
    "warnings.simplefilter(\"ignore\", FutureWarning, )\n",
    "\n",
    "%matplotlib inline\n",
    "pd.plotting.register_matplotlib_converters()\n",
    "pd.options.display.float_format = '{:.4f}'.format\n",
    "rcParams.update({'figure.autolayout': True, 'figure.figsize':(12,10),'axes.titlesize':14})\n",
    "\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('max_info_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Function Definitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_data(df, file_name):\n",
    "    # Write data to CSV File\n",
    "    df.to_csv(path_or_buf=file_name, sep=',',index=False, encoding='utf-8')\n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* CREATE \n",
    "    ALGORITHM = UNDEFINED \n",
    "    DEFINER = `root`@`localhost` \n",
    "    SQL SECURITY DEFINER\n",
    "VIEW `capstone_ai`.`household_view` AS\n",
    "    SELECT \n",
    "        `a`.`membership_id` AS `membership_id`,\n",
    "        `a`.`household_key` AS `household_key`,\n",
    "        `a`.`income` AS `income`,\n",
    "        `a`.`member_status` AS `member_status`,\n",
    "        `a`.`state_grouped` AS `state_grouped`,\n",
    "        `a`.`credit_ranges` AS `credit_ranges`,\n",
    "        `a`.`cancel_reason` AS `cancel_reason`,\n",
    "        `a`.`renew_method` AS `renew_method`,\n",
    "        `a`.`plus_indicator_description` AS `plus_indicator_description`,\n",
    "        `a`.`zip` AS `zip`,\n",
    "        `a`.`sc_vehicle_manufacturer_name` AS `sc_vehicle_manufacturer_name`,\n",
    "        `a`.`sc_vehicle_model_name` AS `sc_vehicle_model_name`,\n",
    "        `a`.`race` AS `race`,\n",
    "        `a`.`home_owner` AS `home_owner`,\n",
    "        `a`.`education` AS `education`,\n",
    "        `a`.`dwelling_type` AS `dwelling_type`,\n",
    "        `a`.`gender` AS `gender`,\n",
    "        `a`.`occupation_code` AS `occupation_code`,\n",
    "        `a`.`occupation_group` AS `occupation_group`,\n",
    "        `a`.`occupant_type` AS `occupant_type`,\n",
    "        `a`.`mosaic_household` AS `mosaic_household`,\n",
    "        `a`.`mosaic_global_household` AS `mosaic_global_household`,\n",
    "        `a`.`kcl_b_ind_mosaicsgrouping` AS `kcl_b_ind_mosaicsgrouping`,\n",
    "        `a`.`generation` AS `generation`,\n",
    "        `a`.`tenure` AS `tenure`,\n",
    "        `a`.`no_tenure` AS `aaa_no_tenure`,\n",
    "        MAX(`a`.`member_tenure_years`) AS `aaa_member_tenure_years`,\n",
    "        AVG(`a`.`age`) AS `aaa_mean_age`,\n",
    "        AVG(`a`.`length_of_residence`) AS `length_of_residence`,\n",
    "        MAX(`a`.`fsv_cmsi_flag`) AS `aaa_mortgage`,\n",
    "        MAX(`a`.`fsv_credit_card_flag`) AS `aaa_credit_card`,\n",
    "        MAX(`a`.`fsv_deposit_program_flag`) AS `aaa_deposit`,\n",
    "        MAX(`a`.`fsv_home_equity_flag`) AS `aaa_home_equity`,\n",
    "        MAX(`a`.`fsv_cmsi_flag`) AS `aaa_financial_service`,\n",
    "        MAX(`a`.`ins_client_flag`) AS `aaa_auto_insurance`,\n",
    "        MAX(`a`.`fsv_id_theft_flag`) AS `aaa_id_theft`,\n",
    "        MAX(`a`.`motorcycle_indicator`) AS `aaa_motorcycle_indicator`,\n",
    "        MAX(`a`.`trv_globalware_flag`) AS `aaa_travel`,\n",
    "        AVG(`a`.`no_child`) AS `aaa_mean_child`,\n",
    "        AVG(`a`.`total_cost`) AS `aaa_mean_total_cost`,\n",
    "        MAX(`a`.`no_race`) AS `aaa_no_race`,\n",
    "        MAX(`a`.`no_home_owner`) AS `aaa_no_home_owner`,\n",
    "        MAX(`a`.`no_education`) AS `aaa_no_education`,\n",
    "        MAX(`a`.`no_income`) AS `aaa_no_income`,\n",
    "        MAX(`a`.`no_dwelling_type`) AS `aaa_no_dwelling_type`,\n",
    "        MAX(`a`.`no_credit_ranges`) AS `aaa_no_credit_ranges`,\n",
    "        MAX(`a`.`no_gender`) AS `aaa_no_gender`,\n",
    "        MAX(`a`.`no_language`) AS `aaa_no_language`,\n",
    "        MAX(`a`.`no_reason_joined`) AS `aaa_no_reason_joined`,\n",
    "        MAX(`a`.`no_cancel_reason`) AS `aaa_cancel_reason`,\n",
    "        MAX(`a`.`no_mosaic_household`) AS `aaa_no_mosaic_household`,\n",
    "        MAX(`a`.`no_mosaic_global_household`) AS `aaa_no_mosaic_global_household`,\n",
    "        MAX(`a`.`no_kcl_b_ind_mosaicsgrouping`) AS `aaa_no_kcl_b_ind_mosaicsgrouping`,\n",
    "        MAX(`a`.`no_occupation_code`) AS `aaa_no_occupation_code`,\n",
    "        MAX(`a`.`no_occupation_group`) AS `aaa_no_occupation_group`,\n",
    "        MAX(`a`.`no_occupant_type`) AS `aaa_no_occupant_type`,\n",
    "        MAX(`a`.`no_plus_indicator_description`) AS `aaa_no_plus_indicator_description`,\n",
    "        MAX(`a`.`no_generation`) AS `aaa_no_generation`,\n",
    "        SUM(`a`.`ers_ent_count_year_1`) AS `total_calls_year_1`,\n",
    "        SUM(`a`.`ers_ent_count_year_2`) AS `total_calls_year_2`,\n",
    "        SUM(`a`.`ers_ent_count_year_3`) AS `total_calls_year_3`,\n",
    "        SUM(`a`.`ers_member_cost_year_1`) AS `total_member_cost_1`,\n",
    "        SUM(`a`.`ers_member_cost_year_2`) AS `total_member_cost_2`,\n",
    "        SUM(`a`.`ers_member_cost_year_3`) AS `total_member_cost_3`,\n",
    "        SUM(`a`.`basic_cost`) AS `basic_cost`,\n",
    "        SUM(`a`.`plus_cost`) AS `plus_cost`,\n",
    "        SUM(`a`.`premier_cost`) AS `premier_cost`,\n",
    "        AVG(`a`.`basic_cost`) AS `mean_basic_cost`,\n",
    "        AVG(`a`.`plus_cost`) AS `mean_plus_cost`,\n",
    "        AVG(`a`.`premier_cost`) AS `mean_premier_cost`,\n",
    "        SUM(((`a`.`ers_ent_count_year_1` + `a`.`ers_ent_count_year_2`) + `a`.`ers_ent_count_year_3`)) AS `total_calls`,\n",
    "        SUM(((`a`.`ers_member_cost_year_1` + `a`.`ers_member_cost_year_2`) + `a`.`ers_member_cost_year_3`)) AS `total_member_cost`,\n",
    "        AVG(((`a`.`ers_member_cost_year_1` + `a`.`ers_member_cost_year_2`) + `a`.`ers_member_cost_year_3`)) AS `mean_total_member_cost`,\n",
    "        CONCAT(`a`.`sc_vehicle_manufacturer_name`,\n",
    "                ' ',\n",
    "                `a`.`sc_vehicle_model_name`) AS `vehicle`,\n",
    "        SUM(`a`.`total_cost`) AS `total_cost`,\n",
    "        AVG(`a`.`total_cost`) AS `mean_total_cost`,\n",
    "        COUNT(DISTINCT `a`.`member_number_associate_id`) AS `total_members_in_household`\n",
    "    FROM\n",
    "        `capstone_ai`.`capstone_aaa_featured` `a`\n",
    "    GROUP BY `a`.`membership_id`\n",
    "    ORDER BY `a`.`membership_id`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load data from the View"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data from the View create using Capstone AAA - EDA .ipynb\n",
    "df_feature = pd.read_csv('household_view.csv', sep=',')\n",
    "df_capstone_aaa_f = pd.read_csv('capstone_aaa_featured.csv', sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_capstone_aaa_f.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot number of members to calls to AAA for services - all member status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Columns to plot \n",
    "# Not used : 'cancel_reason', 'state_grouped', 'renew_method'\n",
    "# Not used :'occupation_code', 'occupant_type' - too many unknowns, \n",
    "\n",
    "cols = [ 'income', 'credit_ranges', 'member_status', 'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_group', 'mosaic_household','cancel_reason',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'total_calls', 'aaa_mortgage', 'aaa_credit_card', \n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type',\n",
    "             'tenure':'Tenure Range', 'generation':'Generation', 'total_calls':'Total Breakdown Calls', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle','total_member_cost':'Total Member Avg. Costs',\n",
    "             \n",
    "            }\n",
    "\n",
    "\n",
    "# number of members per state\n",
    "\n",
    "for col in cols:\n",
    "    counts = df_feature[col].value_counts().nlargest(15)\n",
    "    sns.set(style=\"whitegrid\")\n",
    "    plt.axhline(0, color=\"k\", clip_on=False)\n",
    "    plt.ylabel(\"# of Members\",fontsize=14, fontweight='bold')\n",
    "    plt.xlabel(dict_cols[col], fontsize=14, fontweight='bold')\n",
    "    plt.title('# of Members by '+dict_cols[col] + ' - 15th Largest',loc='center', fontdict={'fontsize':14, 'fontweight':'bold'})\n",
    "    sns.barplot(counts.index, counts.values)\n",
    "    plt.xticks(\n",
    "        rotation=45, \n",
    "        horizontalalignment='right',\n",
    "        fontweight='light',\n",
    "        fontsize='large')\n",
    "    i = 0\n",
    "    for v in list(counts.values):\n",
    "        plt.text(i-.25, v, str(v), color='blue', fontweight='bold')\n",
    "        i +=1\n",
    "    image_name='./pics/Members by '+dict_cols[col]+  ' - 15th Largest - Bar Chart.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()\n",
    "    counts = pd.DataFrame(df_feature[col].value_counts().nlargest(15), index=None)\n",
    "    counts.reset_index(inplace=True)\n",
    "    counts.rename(columns={\"index\": dict_cols[col], col: \"# of Members\"}, inplace=True)\n",
    "    fig = ff.create_table(counts, height_constant=30, index=False)\n",
    "    py.iplot(fig)\n",
    "    image_name='./pics/Members by '+dict_cols[col]+  ' - 15th Largest - Table.png'\n",
    "    fig.write_image(image_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot costs of calls to AAA for services - ONLY ACTIVE OR PENDING in member status."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Columns to plot Total costs and total calls\n",
    "cols = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs',\n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Avg. Costs'\n",
    "            }\n",
    "\n",
    "cols2 = 'total_cost'\n",
    "cols3 = 'total_calls'\n",
    "\n",
    "for col in cols:\n",
    "    \n",
    "    df_cols = [col, cols2, cols3]\n",
    "    df = pd.DataFrame(data=df_feature,columns=df_cols)\n",
    "    df[cols2] = df[cols2].apply(lambda x: int(x))\n",
    "    df[cols3] = df[cols3].apply(lambda x: int(x))\n",
    "    df = df.groupby(by=col, axis=0, level=None, as_index=False, \n",
    "                    sort=True, group_keys=True, \n",
    "                    squeeze=True,observed=False,).sum().nlargest(15,cols2)\n",
    "    df.sort_values(by=cols2, ascending=False,inplace=True)\n",
    "    \n",
    "    fig, ax1 = plt.subplots()\n",
    "    sns.set(style=\"whitegrid\")\n",
    "    ax2 = ax1.twinx()\n",
    "    sns.barplot(df[col], df[cols2], ax=ax1)\n",
    "    # Show values at the bottom of each bar graph\n",
    "    i = 0\n",
    "    for v in list(df[cols2]):\n",
    "        ax1.text(i-.25, v/2, str(int(v)), color='blue', fontweight='bold')\n",
    "        i +=1\n",
    "    # Draw a line plot using a second axis\n",
    "    sns.lineplot(x=df[col],y=df[cols3], color=\"r\", marker=\"o\", ax=ax2, sort=False)\n",
    "    i = 0\n",
    "    for v in list(df[cols3]):\n",
    "        ax2.text(i, v/0.98, str(int(v)), color='red', fontweight='bold')\n",
    "        i +=1\n",
    "    \n",
    "    ax1.set_ylabel(dict_cols[cols2],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xlabel(dict_cols[col],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xticklabels(df[col],rotation=90, horizontalalignment='right', fontweight='light', fontsize='large')\n",
    "    \n",
    "    ax2.set_ylabel(dict_cols[cols3],fontsize=14, fontweight='bold')\n",
    "    ax2.legend(labels=[dict_cols[cols3]], loc='upper right')\n",
    "              \n",
    "    plt.axhline(0, color=\"k\", clip_on=False)\n",
    "    plt.title('Total Costs and Calls by '+dict_cols[col] + ' - 15th Largest',loc='center', fontdict={'fontsize':14, 'fontweight':'bold'})\n",
    "    # Save image\n",
    "    image_name='./pics/Total Costs and Calls by '+ dict_cols[col]+ ' - 15th Largest - Bar Chart.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()\n",
    "    df['Avg. Costs'] = np.round(df[cols2]/df[cols3],2)\n",
    "    df.rename(columns={col: dict_cols[col], 'total_cost': \"Total Costs\", 'total_calls':'Total Breakdown Calls'}, inplace=True)\n",
    "    fig = ff.create_table(df, height_constant=30, index=False)\n",
    "    py.iplot(fig)\n",
    "    image_name='./pics/Total Costs and Calls by '+dict_cols[col]+ ' - 15th Largest - Table.png'\n",
    "    fig.write_image(image_name)\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Columns to plot Total costs and member costs \n",
    "cols = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs',\n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Costs'\n",
    "            }\n",
    "\n",
    "cols2 = 'total_cost'\n",
    "cols3 = 'total_member_cost'\n",
    "\n",
    "for col in cols:\n",
    "    \n",
    "    df_cols = [col, cols2, cols3]\n",
    "    df = pd.DataFrame(data=df_feature,columns=df_cols)\n",
    "    df[cols2] = df[cols2].apply(lambda x: int(x))\n",
    "    df[cols3] = df[cols3].apply(lambda x: int(x))\n",
    "    df = df.groupby(by=col, axis=0, level=None, as_index=False, \n",
    "                    sort=True, group_keys=True, \n",
    "                    squeeze=True,observed=False,).sum().nlargest(15,cols2)\n",
    "    df.sort_values(by=cols2, ascending=False,inplace=True)\n",
    "    \n",
    "    fig, ax1 = plt.subplots()\n",
    "    sns.set(style=\"whitegrid\")\n",
    "    ax2 = ax1.twinx()\n",
    "    sns.barplot(df[col], df[cols2], ax=ax1)\n",
    "    # Show values at the bottom of each bar graph\n",
    "    i = 0\n",
    "    for v in list(df[cols2]):\n",
    "        ax1.text(i-.25, v/2, str(int(v)), color='blue', fontweight='bold')\n",
    "        i +=1\n",
    "    # Draw a line plot using a second axis\n",
    "    sns.lineplot(x=df[col],y=df[cols3], color=\"r\", marker=\"o\", ax=ax2, sort=False)\n",
    "    i = 0\n",
    "    for v in list(df[cols3]):\n",
    "        ax2.text(i, v/0.98, str(int(v)), color='red', fontweight='bold')\n",
    "        i +=1\n",
    "    \n",
    "    ax1.set_ylabel(dict_cols[cols2],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xlabel(dict_cols[col],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xticklabels(df[col],rotation=90, horizontalalignment='right', fontweight='light', fontsize='large')\n",
    "    \n",
    "    ax2.set_ylabel(dict_cols[cols3],fontsize=14, fontweight='bold')\n",
    "    ax2.legend(labels=[dict_cols[cols3]], loc='upper right')\n",
    "              \n",
    "    plt.axhline(0, color=\"k\", clip_on=False)\n",
    "    plt.title('Total Costs and Members Costs by '+ dict_cols[col] + ' - 15th Largest',loc='center', fontdict={'fontsize':14, 'fontweight':'bold'})\n",
    "    # Save image\n",
    "    image_name='./pics/Total Costs and Members Costs by '+ dict_cols[col]+ ' - 15th Largest - Bar Chart.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()\n",
    "    df['Total Profit (3 yrs)'] = np.round(df['total_member_cost'] - df['total_cost'],2)\n",
    "    df.rename(columns={col: dict_cols[col], 'total_cost': \"Total Costs\", 'total_member_cost':'Total Member Costs'}, inplace=True)\n",
    "    fig = ff.create_table(df, height_constant=30, index=False)\n",
    "    py.iplot(fig)\n",
    "    image_name='./pics/Total Costs and Members Costs by '+dict_cols[col]+ ' - 15th Largest - Table.png'\n",
    "    fig.write_image(image_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Heatmap with Seaborn - Total Costs\n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description', \n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card', \n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols2 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description', \n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card', \n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', \n",
    "             'generation':'Generation', 'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service',\n",
    "             'aaa_deposit':'Use Deposit Service','aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs',\n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Costs'             \n",
    "            }\n",
    "\n",
    "cols3 = 'total_cost'\n",
    "cols1_cols2_cols3 = [[x[0], x[1], cols3] for x in list(itertools.product(cols1,cols2))]\n",
    "# Load columns \n",
    "# Draw a heatmap with the numeric values in each cell\n",
    "for i in cols1_cols2_cols3:\n",
    "    if i[0]==i[1]: continue        \n",
    "    df = pd.DataFrame(df_feature, columns=i)\n",
    "    df = df.groupby(by=[i[0], i[1]]).sum()\n",
    "    df.reset_index(inplace=True)\n",
    "    df = df.pivot(i[0], i[1], i[2])\n",
    "\n",
    "    # Draw a heatmap with the numeric values in each cell\n",
    "    f, ax = plt.subplots()\n",
    "    ax = sns.heatmap(df, annot=True, fmt=\".0f\", linewidths=0.1, ax=ax, xticklabels=True, \n",
    "                cbar_kws={'label': 'Total Costs'}, yticklabels=True)\n",
    "    ax.figure.axes[-1].yaxis.label.set_size(14)\n",
    "    plt.ylabel(dict_cols[i[0]],fontsize=14, fontweight='bold')\n",
    "    plt.xlabel(dict_cols[i[1]], fontsize=14, fontweight='bold')\n",
    "    plt.title('HeatMap - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] ,loc='center', fontdict={'fontsize':14, 'fontweight':'bold'})\n",
    "    bottom, top = ax.get_ylim()\n",
    "    ax.set_ylim(bottom + 0.5, top - 0.5)\n",
    "    image_name='./pics/Total Costs - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] +  '- HeatMap.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by='aaa_auto_insurance',axis=0,\n",
    "    ascending=False,\n",
    "    inplace=False,\n",
    "    kind='quicksort',\n",
    "    na_position='last',)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Heatmap with Seaborn - Total Breakdown calls\n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card', \n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols2 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs',\n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Costs'\n",
    "             \n",
    "            }\n",
    "\n",
    "cols3 = 'total_calls'\n",
    "cols1_cols2_cols3 = [[x[0], x[1], cols3] for x in list(itertools.product(cols1,cols2))]\n",
    "# Load columns \n",
    "# Draw a heatmap with the numeric values in each cell\n",
    "for i in cols1_cols2_cols3:\n",
    "    if i[0]==i[1]: continue        \n",
    "    df = pd.DataFrame(df_feature, columns=i)\n",
    "    df = df.groupby(by=[i[0], i[1]]).sum()\n",
    "    df.reset_index(inplace=True)\n",
    "    df = df.pivot(i[0], i[1], i[2])\n",
    "\n",
    "    # Draw a heatmap with the numeric values in each cell\n",
    "    f, ax = plt.subplots()\n",
    "    ax = sns.heatmap(df, annot=True, fmt=\".0f\", linewidths=0.1, ax=ax, \n",
    "                cbar_kws={'label': 'Total Breakdown Calls'}, xticklabels=True, yticklabels=True)\n",
    "    ax.figure.axes[-1].yaxis.label.set_size(14)\n",
    "    plt.ylabel(dict_cols[i[0]],fontsize=14, fontweight='bold')\n",
    "    plt.xlabel(dict_cols[i[1]], fontsize=14, fontweight='bold')\n",
    "    plt.title('HeatMap - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] ,loc='center', \n",
    "              fontdict={'fontsize':14, 'fontweight':'bold'}\n",
    "             )\n",
    "    bottom, top = ax.get_ylim()\n",
    "    ax.set_ylim(bottom + 0.5, top - 0.5)\n",
    "    image_name='./pics/Total Breakdown Calls - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] +  '- HeatMap.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Heatmap with Seaborn - Total Member costs\n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card', \n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols2 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs',\n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Costs'\n",
    "             \n",
    "            }\n",
    "\n",
    "cols3 = 'total_member_cost'\n",
    "cols1_cols2_cols3 = [[x[0], x[1], cols3] for x in list(itertools.product(cols1,cols2))]\n",
    "# Load columns \n",
    "# Draw a heatmap with the numeric values in each cell\n",
    "for i in cols1_cols2_cols3:\n",
    "    if i[0]==i[1]: continue        \n",
    "    df = pd.DataFrame(df_feature, columns=i)\n",
    "    df = df.groupby(by=[i[0], i[1]]).sum()\n",
    "    df.reset_index(inplace=True)\n",
    "    df = df.pivot(i[0], i[1], i[2])\n",
    "\n",
    "    # Draw a heatmap with the numeric values in each cell\n",
    "    f, ax = plt.subplots()\n",
    "    ax = sns.heatmap(df, annot=True, fmt=\".0f\", linewidths=0.1, ax=ax, \n",
    "                cbar_kws={'label': 'Total Member Costs'}, xticklabels=True, yticklabels=True)\n",
    "    ax.figure.axes[-1].yaxis.label.set_size(14)\n",
    "    plt.ylabel(dict_cols[i[0]],fontsize=14, fontweight='bold')\n",
    "    plt.xlabel(dict_cols[i[1]], fontsize=14, fontweight='bold')\n",
    "    plt.title('HeatMap - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] ,loc='center', \n",
    "              fontdict={'fontsize':14, 'fontweight':'bold'}\n",
    "             )\n",
    "    bottom, top = ax.get_ylim()\n",
    "    ax.set_ylim(bottom + 0.5, top - 0.5)\n",
    "    image_name='./pics/Total Member Costs - '+ dict_cols[i[0]] + ' by '+ dict_cols[i[1]] +  '- HeatMap.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Line Graph with total costs and Total Breakdown  \n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs', \n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Avg. Costs'             \n",
    "            }\n",
    "\n",
    "cols2 = 'total_cost'\n",
    "cols3 = 'total_calls'\n",
    "\n",
    "# Draw a line plot with the numeric values in each cell\n",
    "for i in cols1:\n",
    "    df_cols =  [i, cols2, cols3]\n",
    "    df = pd.DataFrame(data=df_feature,columns=df_cols)\n",
    "    df[cols2] = df[cols2].apply(lambda x: int(x))\n",
    "    df = df.groupby(by=i, axis=0, level=None, as_index=False, sort=True, group_keys=True, squeeze=True,observed=False,).sum()\n",
    "    df.sort_values(by=cols2, ascending=False,inplace=True)\n",
    "    # Just to show the Top 30\n",
    "    df = df.sort_values(by=cols2, ascending=False)[:30].reset_index(drop=True)\n",
    "\n",
    "    fig, ax1 = plt.subplots()\n",
    "    ax2 = ax1.twinx()\n",
    "    sns.lineplot(x=df[i], y=df[cols3], color=\"r\", legend='full', marker=\"x\", ax=ax1)\n",
    "    sns.lineplot(data=df[cols2], color=\"b\", marker=\"o\", ax=ax2)\n",
    "    ax1.set_ylabel(dict_cols[cols3],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xlabel(dict_cols[i],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xticklabels(df[i],rotation=90)\n",
    "    ax1.legend(labels=['Total Breakdown Calls'], loc='upper left')\n",
    "    ax2.set_ylabel(dict_cols[cols2],fontsize=14, fontweight='bold')\n",
    "    ax2.legend(labels=['Total Costs'], loc='upper right')\n",
    "    \n",
    "    \n",
    "    plt.title('Line Plot - Total Costs & Total Breakdown Calls by '+ dict_cols[i],loc='center', \n",
    "              fontdict={'fontsize':14, 'fontweight':'bold'}\n",
    "             )\n",
    "    image_name='./pics/Total Costs and Breakdown Calls - by '+ dict_cols[i] +  '- Lineplot.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Line Graph with total costs and Total member costs  \n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle', 'total_cost':'Total Costs', \n",
    "             'total_calls':'Total Breakdown Calls', 'total_member_cost':'Total Member Costs'             \n",
    "            }\n",
    "\n",
    "cols2 = 'total_cost'\n",
    "cols3 = 'total_member_cost'\n",
    "\n",
    "# Draw a line plot with the numeric values in each cell\n",
    "for i in cols1:\n",
    "    df_cols =  [i, cols2, cols3]\n",
    "    df = pd.DataFrame(data=df_feature,columns=df_cols)\n",
    "    df[cols2] = df[cols2].apply(lambda x: int(x))\n",
    "    df = df.groupby(by=i, axis=0, level=None, as_index=False, sort=True, group_keys=True, squeeze=True,observed=False,).sum()\n",
    "    df.sort_values(by=cols2, ascending=False,inplace=True)\n",
    "    # Just to show the Top 30\n",
    "    df = df.sort_values(by=cols2, ascending=False)[:30].reset_index(drop=True)\n",
    "\n",
    "    fig, ax1 = plt.subplots()\n",
    "    ax2 = ax1.twinx()\n",
    "    sns.lineplot(x=df[i], y=df[cols3], color=\"r\", legend='full', marker=\"x\", ax=ax1)\n",
    "    sns.lineplot(data=df[cols2], color=\"b\", marker=\"o\", ax=ax2, sort=False)\n",
    "    ax1.set_ylabel(dict_cols[cols3],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xlabel(dict_cols[i],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xticklabels(df[i],rotation=90)\n",
    "    ax1.legend(labels=[dict_cols[cols3]], loc='upper left')\n",
    "    ax2.set_ylabel(dict_cols[cols2],fontsize=14, fontweight='bold')\n",
    "    ax2.legend(labels=[dict_cols[cols2]], loc='upper right')\n",
    "        \n",
    "    plt.title('Line Plot - Total Costs & Total Member Costs by '+ dict_cols[i],loc='center', \n",
    "              fontdict={'fontsize':14, 'fontweight':'bold'}\n",
    "             )\n",
    "    image_name='./pics/Total Costs and Breakdown Calls - by '+ dict_cols[i] +  '- Lineplot.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Line Graph with total costs and Total Breakdown  - Problem Codes\n",
    "cols1 = ['clearing_code_last_description', 'dispatch_code1_description', 'prob1_code_description', \n",
    "         'sc_sts_rsn_code_description'\n",
    "       ]\n",
    "\n",
    "# Dictionary for X Axis Labels\n",
    "dict_cols = { 'clearing_code_last_description':'Clearing Code Description', 'dispatch_code1_description':'Dispatch Code Description',\n",
    "             'prob1_code_description':'Problem Description','total_cost':'Total Costs', 'total_calls':'Total Breakdown Calls',\n",
    "             'sc_sts_rsn_code_description':'Service Type'\n",
    "            }\n",
    "\n",
    "cols2 = 'total_cost'\n",
    "cols3 = 'total_calls'\n",
    "\n",
    "# Draw a line plot with the numeric values in each cell\n",
    "for i in cols1:\n",
    "    df_cols =  [i, cols2, cols3]\n",
    "    df = pd.DataFrame(data=df_capstone_aaa_f,columns=df_cols)\n",
    "    df[cols2] = df[cols2].apply(lambda x: int(x))\n",
    "    df = df.groupby(by=i, axis=0, level=None, as_index=False, sort=True, group_keys=True, squeeze=True,observed=False,).sum()\n",
    "    df.sort_values(by=cols2, ascending=False,inplace=True)\n",
    "    # Just to show the Top 30\n",
    "    df = df = df.sort_values(by=cols2, ascending=False)[:30].reset_index(drop=True)\n",
    "\n",
    "    fig, ax1 = plt.subplots()\n",
    "    ax2 = ax1.twinx()\n",
    "    sns.lineplot(x=df[i], y=df[cols3], color=\"r\", legend='full', marker=\"x\", ax=ax1)\n",
    "    sns.lineplot(data=df[cols2], color=\"b\", marker=\"o\", ax=ax2)\n",
    "    ax1.set_ylabel(dict_cols[cols3],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xlabel(dict_cols[i],fontsize=14, fontweight='bold')\n",
    "    ax1.set_xticklabels(df[i],rotation=90)\n",
    "    ax1.legend(labels=['Total Breakdown Calls'], loc='upper left')\n",
    "    ax2.set_ylabel(dict_cols[cols2],fontsize=14, fontweight='bold')\n",
    "    ax2.legend(labels=['Total Costs'], loc='upper right')\n",
    "    \n",
    "    \n",
    "    plt.title('Line Plot - Total Costs & Total Breakdown Calls by '+ dict_cols[i],loc='center', \n",
    "              fontdict={'fontsize':14, 'fontweight':'bold'}\n",
    "             )\n",
    "    image_name='./pics/Total Costs and Breakdown Calls - by '+ dict_cols[i] +  '- Lineplot.png'\n",
    "    plt.savefig(image_name)\n",
    "    plt.show()\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Heatmaps with Plotly\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle'             \n",
    "            }\n",
    "\n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols2 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols3 = 'total_cost'\n",
    "\n",
    "cols1_cols2_cols3 = [[x[0], x[1], cols3] for x in list(itertools.product(cols1,cols2))]\n",
    "for i in cols1_cols2_cols3:\n",
    "    if i[0]==i[1]: continue        \n",
    "    fig = go.Figure(data=go.Heatmap(z=df_feature[i[2]].dropna(), x=df_feature[i[0]].dropna(), y=df_feature[i[1]].dropna(),\n",
    "                                    autocolorscale = True, colorscale='Viridis'\n",
    "                                   )\n",
    "                   )    \n",
    "    fig.update_layout(title='Total Costs HeatMap - AAA - '+ dict_cols[i[0]] + ' vs. ' + dict_cols[i[1]],  xaxis_nticks=36,\n",
    "                      xaxis_title=dict_cols[i[0]],\n",
    "                      yaxis_title=dict_cols[i[1]]\n",
    "                     )\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Heatmaps with Plotly\n",
    "dict_cols = { 'income':'Income', 'member_status':'Member Status','state_grouped': 'State', 'credit_ranges':'Credit Range',\n",
    "             'cancel_reason':'Cancel Reason','renew_method':'Renewal Method','sc_vehicle_manufacturer_name':'Vehicle Manufacturer',\n",
    "             'sc_vehicle_model_name':'Vehicle Model','race': 'Ethnic Group','home_owner': 'Home Owner','education':'Education Level', \n",
    "             'dwelling_type': 'Dwelling Type', 'gender':'Gender','occupation_code':'Occupation', 'occupation_group':'Occupation Group',\n",
    "             'occupant_type': 'Occupant Type','mosaic_household':'Mosaic household','mosaic_global_household':'Global Mosaic',\n",
    "             'kcl_b_ind_mosaicsgrouping':'Mosaic Grouping', 'plus_indicator_description':'Membership Type','tenure':'tenure Range', 'generation':'Generation', \n",
    "             'aaa_mortgage':'Used Mortgage Service', 'aaa_credit_card':'Use Credit Card Service','aaa_deposit':'Use Deposit Service',\n",
    "             'aaa_home_equity':'Use Home Equity Service', 'aaa_financial_service':'Use Financial Service', \n",
    "             'aaa_auto_insurance':'Use Insurance Service', 'vehicle':'Vehicle'             \n",
    "            }\n",
    "\n",
    "cols1 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols2 = [ 'income', 'member_status','state_grouped', 'credit_ranges', 'cancel_reason','renew_method',\n",
    "        'sc_vehicle_manufacturer_name','sc_vehicle_model_name','race','home_owner','education', \n",
    "        'dwelling_type', 'gender','occupation_code', 'occupation_group', 'occupant_type','mosaic_household',\n",
    "        'mosaic_global_household','kcl_b_ind_mosaicsgrouping', 'plus_indicator_description',\n",
    "        'tenure', 'generation', 'aaa_mortgage', 'aaa_credit_card',\n",
    "        'aaa_deposit', 'aaa_home_equity', 'aaa_financial_service', 'aaa_auto_insurance', 'vehicle'\n",
    "       ]\n",
    "\n",
    "cols3 = 'total_calls'\n",
    "\n",
    "cols1_cols2_cols3 = [[x[0], x[1], cols3] for x in list(itertools.product(cols1,cols2))]\n",
    "for i in cols1_cols2_cols3:\n",
    "    if i[0]==i[1]: continue        \n",
    "    fig = go.Figure(data=go.Heatmap(z=df_feature[i[2]].dropna(), x=df_feature[i[0]].dropna(), y=df_feature[i[1]].dropna(),\n",
    "                                    autocolorscale = True, colorscale='Viridis'\n",
    "                                   )\n",
    "                   )    \n",
    "    fig.update_layout(title='Total BreakDown Calls HeatMap - AAA - '+ dict_cols[i[0]] + ' vs. ' + dict_cols[i[1]],  xaxis_nticks=36,\n",
    "                      xaxis_title=dict_cols[i[0]],\n",
    "                      yaxis_title=dict_cols[i[1]]\n",
    "                     )\n",
    "    fig.show()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}