Telecom_project_task4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User Analytics in the Telecommunication Industry #"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 4 - Satisfaction Analysis ##"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this part, we wil assume that the satisfaction of a user is dependent on user engagement and experience, we will then analyze customer satisfaction in depth."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1. Engagement score ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: tabulate in c:\\programdata\\anaconda3\\lib\\site-packages (0.8.7)\n"
     ]
    }
   ],
   "source": [
    "#importing libraries\n",
    "!pip install tabulate\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "import plotly.express as px\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from statistics import *\n",
    "from tabulate import tabulate\n",
    "from sklearn import preprocessing\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.linear_model import Ridge\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>MSISDN/Number</th>\n",
       "      <th>no.of xDR sessions</th>\n",
       "      <th>Total Uls and DLs</th>\n",
       "      <th>session_freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>2</td>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  no.of xDR sessions  Total Uls and DLs  session_freq\n",
       "0   3.360100e+10                   1       5.173738e+08             1\n",
       "1   3.360100e+10                   1       1.234090e+09             1\n",
       "2   3.360100e+10                   1       7.183836e+08             1\n",
       "3   3.360101e+10                   1       1.638393e+09             1\n",
       "4   3.360101e+10                   2       1.158391e+09             1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading the engagement datamart\n",
    "data=pd.read_csv('engagement_datamart')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_MSISDN=data.copy()"
   ]
  },
  {
   "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>MSISDN/Number</th>\n",
       "      <th>no.of xDR sessions</th>\n",
       "      <th>Total Uls and DLs</th>\n",
       "      <th>session_freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>2</td>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  no.of xDR sessions  Total Uls and DLs  session_freq\n",
       "0   3.360100e+10                   1       5.173738e+08             1\n",
       "1   3.360100e+10                   1       1.234090e+09             1\n",
       "2   3.360100e+10                   1       7.183836e+08             1\n",
       "3   3.360101e+10                   1       1.638393e+09             1\n",
       "4   3.360101e+10                   2       1.158391e+09             1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_MSISDN.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first we will initialize the centroid of the least cluster in engagement score\n",
    "c0=(-0.00395958, -0.4885642 ,  0.52150644,)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A helper function to calculate the Euclidean diatance between the data \n",
    "# points and the least centroid in engagement score\n",
    "\n",
    "def calculate_distance(centroid, X, Y,Z):\n",
    "    distances = []\n",
    "        \n",
    "    # Unpack the x and y coordinates of the centroid\n",
    "    c_x, c_y,c_z = centroid\n",
    "        \n",
    "    # Iterate over the data points and calculate the distance using the           # given formula\n",
    "    for x, y,z in list(zip(X, Y,Z)):\n",
    "        root_diff_x = (x - c_x) ** 2\n",
    "        root_diff_y = (y - c_y) ** 2\n",
    "        root_diff_z = (z - c_z) ** 2\n",
    "        distance = np.sqrt(root_diff_x + root_diff_y + root_diff_z)\n",
    "        distances.append(distance)\n",
    "        \n",
    "    return distances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the distance and assign them to the DataFrame accordingly by calling the function\n",
    "data_MSISDN['Engagement_Euclidean_Distance'] = calculate_distance(c0,data_MSISDN['no.of xDR sessions'],data_MSISDN['Total Uls and DLs'],data_MSISDN['session_freq'])"
   ]
  },
  {
   "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></th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>no.of xDR sessions</th>\n",
       "      <th>Total Uls and DLs</th>\n",
       "      <th>session_freq</th>\n",
       "      <th>Engagement_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>1</td>\n",
       "      <td>5.173738e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1.234090e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>1</td>\n",
       "      <td>7.183836e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1.638393e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.360101e+10</td>\n",
       "      <td>2</td>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1.158391e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  no.of xDR sessions  Total Uls and DLs  session_freq  \\\n",
       "0   3.360100e+10                   1       5.173738e+08             1   \n",
       "1   3.360100e+10                   1       1.234090e+09             1   \n",
       "2   3.360100e+10                   1       7.183836e+08             1   \n",
       "3   3.360101e+10                   1       1.638393e+09             1   \n",
       "4   3.360101e+10                   2       1.158391e+09             1   \n",
       "\n",
       "   Engagement_Euclidean_Distance  \n",
       "0                   5.173738e+08  \n",
       "1                   1.234090e+09  \n",
       "2                   7.183836e+08  \n",
       "3                   1.638393e+09  \n",
       "4                   1.158391e+09  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_MSISDN.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1. Experience Score ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading Experience datamart\n",
    "exp_data=pd.read_csv('experience_datamart')"
   ]
  },
  {
   "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></th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Average TCP retransmission</th>\n",
       "      <th>Average RTT</th>\n",
       "      <th>Average throughput</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  Average TCP retransmission  Average RTT  Average throughput\n",
       "0   3.366496e+10                2.156957e+07    47.000000                46.0\n",
       "1   3.368185e+10                2.156957e+07    70.000000                32.0\n",
       "2   3.376063e+10                2.156957e+07   127.458589                12.0\n",
       "3   3.375034e+10                2.156957e+07   127.458589                88.0\n",
       "4   3.369980e+10                2.156957e+07   127.458589                12.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_data1=exp_data[['MSISDN/Number','Average TCP retransmission','Average RTT','Average throughput']]\n",
    "exp_data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first we will initialize the centroid of the least cluster in experience score\n",
    "c1=(3.13199983e+09, 1.49370370e+02, 1.26895321e+05)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A helper function to calculate the Euclidean diatance between the data \n",
    "# points and the least centroid in experience score\n",
    "\n",
    "def calculate_distance(centroid, X, Y,Z):\n",
    "    distances = []\n",
    "        \n",
    "    # Unpack the x and y coordinates of the centroid\n",
    "    c_x, c_y,c_z = centroid\n",
    "        \n",
    "    # Iterate over the data points and calculate the distance using the           # given formula\n",
    "    for x, y,z in list(zip(X, Y,Z)):\n",
    "        root_diff_x = (x - c_x) ** 2\n",
    "        root_diff_y = (y - c_y) ** 2\n",
    "        root_diff_z = (z - c_z) ** 2\n",
    "        distance = np.sqrt(root_diff_x + root_diff_y + root_diff_z)\n",
    "        distances.append(distance)\n",
    "        \n",
    "    return distances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning:\n",
      "\n",
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Calculate the distance and assign the values to the DataFrame accordingly by calling the function\n",
    "exp_data1['Experience_Euclidean_Distance'] = calculate_distance(c1,exp_data1['Average TCP retransmission'],exp_data1['Average RTT'],exp_data1['Average throughput'])"
   ]
  },
  {
   "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></th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Average TCP retransmission</th>\n",
       "      <th>Average RTT</th>\n",
       "      <th>Average throughput</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>32.0</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>88.0</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>2.156957e+07</td>\n",
       "      <td>127.458589</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  Average TCP retransmission  Average RTT  Average throughput  \\\n",
       "0   3.366496e+10                2.156957e+07    47.000000                46.0   \n",
       "1   3.368185e+10                2.156957e+07    70.000000                32.0   \n",
       "2   3.376063e+10                2.156957e+07   127.458589                12.0   \n",
       "3   3.375034e+10                2.156957e+07   127.458589                88.0   \n",
       "4   3.369980e+10                2.156957e+07   127.458589                12.0   \n",
       "\n",
       "   Experience_Euclidean_Distance  \n",
       "0                   3.110430e+09  \n",
       "1                   3.110430e+09  \n",
       "2                   3.110430e+09  \n",
       "3                   3.110430e+09  \n",
       "4                   3.110430e+09  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_data1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Determining the satisfaction level of the customers ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores_data=exp_data1[['MSISDN/Number','Experience_Euclidean_Distance']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  Experience_Euclidean_Distance\n",
       "0   3.366496e+10                   3.110430e+09\n",
       "1   3.368185e+10                   3.110430e+09\n",
       "2   3.376063e+10                   3.110430e+09\n",
       "3   3.375034e+10                   3.110430e+09\n",
       "4   3.369980e+10                   3.110430e+09"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "eng_score=data_MSISDN[['Engagement_Euclidean_Distance']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Engagement_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.439738e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.221409e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.371765e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1.076896e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.378444e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7.472439e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1.095797e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8.833491e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8.608276e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3.712218e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2.381956e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.581972e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.924682e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4.523731e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7.758777e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6.097900e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>7.320712e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1.073073e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1.607953e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1.070564e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>8.364596e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>4.460480e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7.822126e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>8.387594e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.001275e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106826</th>\n",
       "      <td>1.397927e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106827</th>\n",
       "      <td>5.362948e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106828</th>\n",
       "      <td>1.020036e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106829</th>\n",
       "      <td>8.580798e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106830</th>\n",
       "      <td>9.608852e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106831</th>\n",
       "      <td>1.059497e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106832</th>\n",
       "      <td>1.007289e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106833</th>\n",
       "      <td>5.908207e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106834</th>\n",
       "      <td>1.173060e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106835</th>\n",
       "      <td>9.785531e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106836</th>\n",
       "      <td>9.281506e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106837</th>\n",
       "      <td>9.521538e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106838</th>\n",
       "      <td>4.545137e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106839</th>\n",
       "      <td>8.482633e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106840</th>\n",
       "      <td>2.532309e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106841</th>\n",
       "      <td>1.236339e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106842</th>\n",
       "      <td>7.564859e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106843</th>\n",
       "      <td>1.278474e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106844</th>\n",
       "      <td>1.143569e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106845</th>\n",
       "      <td>1.158831e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106846</th>\n",
       "      <td>4.508421e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106847</th>\n",
       "      <td>9.645736e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106848</th>\n",
       "      <td>1.056376e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106849</th>\n",
       "      <td>5.251086e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106850</th>\n",
       "      <td>7.596144e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106851</th>\n",
       "      <td>9.215483e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106852</th>\n",
       "      <td>9.993291e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106853</th>\n",
       "      <td>6.968673e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106854</th>\n",
       "      <td>8.822031e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106855</th>\n",
       "      <td>1.309480e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106856 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Engagement_Euclidean_Distance\n",
       "0                        5.173738e+08\n",
       "1                        1.234090e+09\n",
       "2                        7.183836e+08\n",
       "3                        1.638393e+09\n",
       "4                        1.158391e+09\n",
       "5                        1.439738e+09\n",
       "6                        9.221409e+08\n",
       "7                        1.371765e+09\n",
       "8                        1.076896e+09\n",
       "9                        9.378444e+08\n",
       "10                       7.472439e+08\n",
       "11                       1.095797e+09\n",
       "12                       8.833491e+08\n",
       "13                       8.608276e+08\n",
       "14                       3.712218e+08\n",
       "15                       2.381956e+08\n",
       "16                       5.581972e+08\n",
       "17                       5.924682e+08\n",
       "18                       4.523731e+08\n",
       "19                       7.758777e+08\n",
       "20                       6.097900e+08\n",
       "21                       7.320712e+08\n",
       "22                       1.073073e+09\n",
       "23                       1.607953e+09\n",
       "24                       1.070564e+09\n",
       "25                       8.364596e+08\n",
       "26                       4.460480e+08\n",
       "27                       7.822126e+08\n",
       "28                       8.387594e+08\n",
       "29                       1.001275e+09\n",
       "...                               ...\n",
       "106826                   1.397927e+09\n",
       "106827                   5.362948e+08\n",
       "106828                   1.020036e+09\n",
       "106829                   8.580798e+08\n",
       "106830                   9.608852e+08\n",
       "106831                   1.059497e+09\n",
       "106832                   1.007289e+09\n",
       "106833                   5.908207e+08\n",
       "106834                   1.173060e+09\n",
       "106835                   9.785531e+08\n",
       "106836                   9.281506e+08\n",
       "106837                   9.521538e+08\n",
       "106838                   4.545137e+08\n",
       "106839                   8.482633e+08\n",
       "106840                   2.532309e+08\n",
       "106841                   1.236339e+09\n",
       "106842                   7.564859e+08\n",
       "106843                   1.278474e+09\n",
       "106844                   1.143569e+09\n",
       "106845                   1.158831e+09\n",
       "106846                   4.508421e+08\n",
       "106847                   9.645736e+08\n",
       "106848                   1.056376e+09\n",
       "106849                   5.251086e+08\n",
       "106850                   7.596144e+08\n",
       "106851                   9.215483e+08\n",
       "106852                   9.993291e+08\n",
       "106853                   6.968673e+08\n",
       "106854                   8.822031e+08\n",
       "106855                   1.309480e+09\n",
       "\n",
       "[106856 rows x 1 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eng_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores=pd.concat([eng_score,scores_data], axis=1, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0             1             2\n",
       "0  5.173738e+08  3.366496e+10  3.110430e+09\n",
       "1  1.234090e+09  3.368185e+10  3.110430e+09\n",
       "2  7.183836e+08  3.376063e+10  3.110430e+09\n",
       "3  1.638393e+09  3.375034e+10  3.110430e+09\n",
       "4  1.158391e+09  3.369980e+10  3.110430e+09"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores.columns=['Engagement_Euclidean_Distance','MSISDN/Number','Experience_Euclidean_Distance']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores['Engagement_Euclidean_Distance'].fillna(value=scores['Engagement_Euclidean_Distance'].mean(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>3.627804e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>4.344520e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>3.828814e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>4.748823e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>4.268821e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "0                   5.173738e+08   3.366496e+10   \n",
       "1                   1.234090e+09   3.368185e+10   \n",
       "2                   7.183836e+08   3.376063e+10   \n",
       "3                   1.638393e+09   3.375034e+10   \n",
       "4                   1.158391e+09   3.369980e+10   \n",
       "\n",
       "   Experience_Euclidean_Distance  satisfaction_score  \n",
       "0                   3.110430e+09        3.627804e+09  \n",
       "1                   3.110430e+09        4.344520e+09  \n",
       "2                   3.110430e+09        3.828814e+09  \n",
       "3                   3.110430e+09        4.748823e+09  \n",
       "4                   3.110430e+09        4.268821e+09  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''#getting the satisfaction score by adding the engagement and experience score\n",
    "scores['satisfaction_score']=scores['Engagement_Euclidean_Distance']+scores['Experience_Euclidean_Distance']'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores['satisfaction_score'] = scores[['Engagement_Euclidean_Distance', 'Experience_Euclidean_Distance']].mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.813902e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.172260e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.914407e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.374411e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.134411e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "0                   5.173738e+08   3.366496e+10   \n",
       "1                   1.234090e+09   3.368185e+10   \n",
       "2                   7.183836e+08   3.376063e+10   \n",
       "3                   1.638393e+09   3.375034e+10   \n",
       "4                   1.158391e+09   3.369980e+10   \n",
       "\n",
       "   Experience_Euclidean_Distance  satisfaction_score  \n",
       "0                   3.110430e+09        1.813902e+09  \n",
       "1                   3.110430e+09        2.172260e+09  \n",
       "2                   3.110430e+09        1.914407e+09  \n",
       "3                   3.110430e+09        2.374411e+09  \n",
       "4                   3.110430e+09        2.134411e+09  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sorting the satisfaction score per customer\n",
    "top10=scores.sort_values(by='satisfaction_score',ascending=False).head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38391</th>\n",
       "      <td>1.780674e+09</td>\n",
       "      <td>3.365094e+10</td>\n",
       "      <td>3.127786e+09</td>\n",
       "      <td>2.454230e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94379</th>\n",
       "      <td>1.772824e+09</td>\n",
       "      <td>3.366385e+10</td>\n",
       "      <td>3.131948e+09</td>\n",
       "      <td>2.452386e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70184</th>\n",
       "      <td>1.771882e+09</td>\n",
       "      <td>3.366556e+10</td>\n",
       "      <td>3.131238e+09</td>\n",
       "      <td>2.451560e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1643</th>\n",
       "      <td>1.762451e+09</td>\n",
       "      <td>3.366836e+10</td>\n",
       "      <td>3.131407e+09</td>\n",
       "      <td>2.446929e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97165</th>\n",
       "      <td>1.761169e+09</td>\n",
       "      <td>3.375200e+10</td>\n",
       "      <td>3.131958e+09</td>\n",
       "      <td>2.446564e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85224</th>\n",
       "      <td>1.760919e+09</td>\n",
       "      <td>3.366195e+10</td>\n",
       "      <td>3.131526e+09</td>\n",
       "      <td>2.446223e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45279</th>\n",
       "      <td>1.760477e+09</td>\n",
       "      <td>3.376363e+10</td>\n",
       "      <td>3.131799e+09</td>\n",
       "      <td>2.446138e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18393</th>\n",
       "      <td>1.760805e+09</td>\n",
       "      <td>3.366366e+10</td>\n",
       "      <td>3.131208e+09</td>\n",
       "      <td>2.446007e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5086</th>\n",
       "      <td>1.756118e+09</td>\n",
       "      <td>3.376082e+10</td>\n",
       "      <td>3.131620e+09</td>\n",
       "      <td>2.443869e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16024</th>\n",
       "      <td>1.757861e+09</td>\n",
       "      <td>3.366957e+10</td>\n",
       "      <td>3.129495e+09</td>\n",
       "      <td>2.443678e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "38391                   1.780674e+09   3.365094e+10   \n",
       "94379                   1.772824e+09   3.366385e+10   \n",
       "70184                   1.771882e+09   3.366556e+10   \n",
       "1643                    1.762451e+09   3.366836e+10   \n",
       "97165                   1.761169e+09   3.375200e+10   \n",
       "85224                   1.760919e+09   3.366195e+10   \n",
       "45279                   1.760477e+09   3.376363e+10   \n",
       "18393                   1.760805e+09   3.366366e+10   \n",
       "5086                    1.756118e+09   3.376082e+10   \n",
       "16024                   1.757861e+09   3.366957e+10   \n",
       "\n",
       "       Experience_Euclidean_Distance  satisfaction_score  \n",
       "38391                   3.127786e+09        2.454230e+09  \n",
       "94379                   3.131948e+09        2.452386e+09  \n",
       "70184                   3.131238e+09        2.451560e+09  \n",
       "1643                    3.131407e+09        2.446929e+09  \n",
       "97165                   3.131958e+09        2.446564e+09  \n",
       "85224                   3.131526e+09        2.446223e+09  \n",
       "45279                   3.131799e+09        2.446138e+09  \n",
       "18393                   3.131208e+09        2.446007e+09  \n",
       "5086                    3.131620e+09        2.443869e+09  \n",
       "16024                   3.129495e+09        2.443678e+09  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "Engagement_score=top10['Engagement_Euclidean_Distance']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "marker": {
          "color": "indianred"
         },
         "name": "Engagement_score",
         "type": "bar",
         "x": [
          "1",
          "2",
          "3",
          "4",
          "5",
          "6",
          "7",
          "8",
          "9",
          "10"
         ],
         "y": [
          1780674418.4885643,
          1772824453.4885643,
          1771882079.4885643,
          1762450886.4885643,
          1761168966.4885643,
          1760919169.4885643,
          1760476726.4885643,
          1760805258.4885643,
          1756118342.4885643,
          1757861115.4885643
         ]
        },
        {
         "marker": {
          "color": "lightsalmon"
         },
         "name": "Experience_score",
         "type": "bar",
         "x": [
          "1",
          "2",
          "3",
          "4",
          "5",
          "6",
          "7",
          "8",
          "9",
          "10"
         ],
         "y": [
          3127786496.5205564,
          3131948186.710268,
          3131237603.8734837,
          3131406783.2605,
          3131958149.1682754,
          3131525846.446025,
          3131798900.9354844,
          3131208453.876649,
          3131620398.04361,
          3129495293.657667
         ]
        }
       ],
       "layout": {
        "barmode": "group",
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "xaxis": {
         "tickangle": -45
        }
       }
      },
      "text/html": [
       "<div>\n",
       "        \n",
       "        \n",
       "            <div id=\"2a70d521-745f-4932-bc06-668f62529580\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>\n",
       "            <script type=\"text/javascript\">\n",
       "                require([\"plotly\"], function(Plotly) {\n",
       "                    window.PLOTLYENV=window.PLOTLYENV || {};\n",
       "                    \n",
       "                if (document.getElementById(\"2a70d521-745f-4932-bc06-668f62529580\")) {\n",
       "                    Plotly.newPlot(\n",
       "                        '2a70d521-745f-4932-bc06-668f62529580',\n",
       "                        [{\"marker\": {\"color\": \"indianred\"}, \"name\": \"Engagement_score\", \"type\": \"bar\", \"x\": [\"1\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\", \"8\", \"9\", \"10\"], \"y\": [1780674418.4885643, 1772824453.4885643, 1771882079.4885643, 1762450886.4885643, 1761168966.4885643, 1760919169.4885643, 1760476726.4885643, 1760805258.4885643, 1756118342.4885643, 1757861115.4885643]}, {\"marker\": {\"color\": \"lightsalmon\"}, \"name\": \"Experience_score\", \"type\": \"bar\", \"x\": [\"1\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\", \"8\", \"9\", \"10\"], \"y\": [3127786496.5205564, 3131948186.710268, 3131237603.8734837, 3131406783.2605, 3131958149.1682754, 3131525846.446025, 3131798900.9354844, 3131208453.876649, 3131620398.04361, 3129495293.657667]}],\n",
       "                        {\"barmode\": \"group\", \"template\": {\"data\": {\"bar\": [{\"error_x\": {\"color\": \"#2a3f5f\"}, \"error_y\": {\"color\": \"#2a3f5f\"}, \"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"bar\"}], \"barpolar\": [{\"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"barpolar\"}], \"carpet\": [{\"aaxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"baxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"type\": \"carpet\"}], \"choropleth\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"choropleth\"}], \"contour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"contour\"}], \"contourcarpet\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"contourcarpet\"}], \"heatmap\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmap\"}], \"heatmapgl\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmapgl\"}], \"histogram\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"histogram\"}], \"histogram2d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2d\"}], \"histogram2dcontour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2dcontour\"}], \"mesh3d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"mesh3d\"}], \"parcoords\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"parcoords\"}], \"pie\": [{\"automargin\": true, \"type\": \"pie\"}], \"scatter\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter\"}], \"scatter3d\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter3d\"}], \"scattercarpet\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattercarpet\"}], \"scattergeo\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergeo\"}], \"scattergl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergl\"}], \"scattermapbox\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattermapbox\"}], \"scatterpolar\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolar\"}], \"scatterpolargl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolargl\"}], \"scatterternary\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterternary\"}], \"surface\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"surface\"}], \"table\": [{\"cells\": {\"fill\": {\"color\": \"#EBF0F8\"}, \"line\": {\"color\": \"white\"}}, \"header\": {\"fill\": {\"color\": \"#C8D4E3\"}, \"line\": {\"color\": \"white\"}}, \"type\": \"table\"}]}, \"layout\": {\"annotationdefaults\": {\"arrowcolor\": \"#2a3f5f\", \"arrowhead\": 0, \"arrowwidth\": 1}, \"coloraxis\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"colorscale\": {\"diverging\": [[0, \"#8e0152\"], [0.1, \"#c51b7d\"], [0.2, \"#de77ae\"], [0.3, \"#f1b6da\"], [0.4, \"#fde0ef\"], [0.5, \"#f7f7f7\"], [0.6, \"#e6f5d0\"], [0.7, \"#b8e186\"], [0.8, \"#7fbc41\"], [0.9, \"#4d9221\"], [1, \"#276419\"]], \"sequential\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"sequentialminus\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]]}, \"colorway\": [\"#636efa\", \"#EF553B\", \"#00cc96\", \"#ab63fa\", \"#FFA15A\", \"#19d3f3\", \"#FF6692\", \"#B6E880\", \"#FF97FF\", \"#FECB52\"], \"font\": {\"color\": \"#2a3f5f\"}, \"geo\": {\"bgcolor\": \"white\", \"lakecolor\": \"white\", \"landcolor\": \"#E5ECF6\", \"showlakes\": true, \"showland\": true, \"subunitcolor\": \"white\"}, \"hoverlabel\": {\"align\": \"left\"}, \"hovermode\": \"closest\", \"mapbox\": {\"style\": \"light\"}, \"paper_bgcolor\": \"white\", \"plot_bgcolor\": \"#E5ECF6\", \"polar\": {\"angularaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"radialaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"scene\": {\"xaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"yaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"zaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}}, \"shapedefaults\": {\"line\": {\"color\": \"#2a3f5f\"}}, \"ternary\": {\"aaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"baxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"caxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"title\": {\"x\": 0.05}, \"xaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}, \"yaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}}}, \"xaxis\": {\"tickangle\": -45}},\n",
       "                        {\"responsive\": true}\n",
       "                    ).then(function(){\n",
       "                            \n",
       "var gd = document.getElementById('2a70d521-745f-4932-bc06-668f62529580');\n",
       "var x = new MutationObserver(function (mutations, observer) {{\n",
       "        var display = window.getComputedStyle(gd).display;\n",
       "        if (!display || display === 'none') {{\n",
       "            console.log([gd, 'removed!']);\n",
       "            Plotly.purge(gd);\n",
       "            observer.disconnect();\n",
       "        }}\n",
       "}});\n",
       "\n",
       "// Listen for the removal of the full notebook cells\n",
       "var notebookContainer = gd.closest('#notebook-container');\n",
       "if (notebookContainer) {{\n",
       "    x.observe(notebookContainer, {childList: true});\n",
       "}}\n",
       "\n",
       "// Listen for the clearing of the current output cell\n",
       "var outputEl = gd.closest('.output');\n",
       "if (outputEl) {{\n",
       "    x.observe(outputEl, {childList: true});\n",
       "}}\n",
       "\n",
       "                        })\n",
       "                };\n",
       "                });\n",
       "            </script>\n",
       "        </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "Engagement_score=top10['Engagement_Euclidean_Distance']\n",
    "levels=['1','2','3','4','5','6','7','8','9','10']\n",
    "Experience_score=top10['Experience_Euclidean_Distance']\n",
    "fig = go.Figure()\n",
    "fig.add_trace(go.Bar(\n",
    "    x=levels,\n",
    "    y=Engagement_score,\n",
    "    name='Engagement_score',\n",
    "    marker_color='indianred'\n",
    "))\n",
    "fig.add_trace(go.Bar(\n",
    "    x=levels,\n",
    "    y=Experience_score,\n",
    "    name='Experience_score',\n",
    "    marker_color='lightsalmon'\n",
    "))\n",
    "\n",
    "# Here we modify the tickangle of the xaxis, resulting in rotated labels.\n",
    "fig.update_layout(barmode='group', xaxis_tickangle=-45)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "top10.to_csv(\"Top10_satisfied_customers\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regression Modeling ###"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use Ridge Regression because of GridSearchCV which allows us to automatically perform 5-fold cross-validation with a range of different regularization parameters in order to find the optimal value of alpha"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'Xs' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-29-8e42cf8a09d9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[0mridge_regressor\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mGridSearchCV\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mridge\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mscoring\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'neg_mean_squared_error'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcv\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m5\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m \u001b[0mridge_regressor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mXs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0my\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'Xs' is not defined"
     ]
    }
   ],
   "source": [
    "'''alpha = [1e-15, 1e-10, 1e-8, 1e-4, 1e-3,1e-2, 1, 5, 10, 20]\n",
    "\n",
    "ridge = Ridge()\n",
    "\n",
    "parameters = {'alpha': [1e-15, 1e-10, 1e-8, 1e-4, 1e-3,1e-2, 1, 5, 10, 20]}\n",
    "\n",
    "ridge_regressor = GridSearchCV(ridge, parameters,scoring='neg_mean_squared_error', cv=5)\n",
    "\n",
    "ridge_regressor.fit(Xs, y)'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### K -means Clustering ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.813902e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.172260e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.914407e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.374411e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.134411e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "0                   5.173738e+08   3.366496e+10   \n",
       "1                   1.234090e+09   3.368185e+10   \n",
       "2                   7.183836e+08   3.376063e+10   \n",
       "3                   1.638393e+09   3.375034e+10   \n",
       "4                   1.158391e+09   3.369980e+10   \n",
       "\n",
       "   Experience_Euclidean_Distance  satisfaction_score  \n",
       "0                   3.110430e+09        1.813902e+09  \n",
       "1                   3.110430e+09        2.172260e+09  \n",
       "2                   3.110430e+09        1.914407e+09  \n",
       "3                   3.110430e+09        2.374411e+09  \n",
       "4                   3.110430e+09        2.134411e+09  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores1=scores[['MSISDN/Number','Engagement_Euclidean_Distance','Experience_Euclidean_Distance']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>MSISDN/Number</th>\n",
       "      <th>Engagement_Euclidean_Distance</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSISDN/Number  Engagement_Euclidean_Distance  Experience_Euclidean_Distance\n",
       "0   3.366496e+10                   5.173738e+08                   3.110430e+09\n",
       "1   3.368185e+10                   1.234090e+09                   3.110430e+09\n",
       "2   3.376063e+10                   7.183836e+08                   3.110430e+09\n",
       "3   3.375034e+10                   1.638393e+09                   3.110430e+09\n",
       "4   3.369980e+10                   1.158391e+09                   3.110430e+09"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>MSISDN/Number</th>\n",
       "      <th>Engagement_Euclidean_Distance</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.500010e+05</td>\n",
       "      <td>1.500010e+05</td>\n",
       "      <td>1.500010e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.182414e+10</td>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.111019e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.438731e+12</td>\n",
       "      <td>2.907772e+08</td>\n",
       "      <td>1.018117e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>3.360100e+10</td>\n",
       "      <td>5.836229e+07</td>\n",
       "      <td>5.353832e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.365094e+10</td>\n",
       "      <td>7.792049e+08</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.366361e+10</td>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.110430e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3.368307e+10</td>\n",
       "      <td>1.053934e+09</td>\n",
       "      <td>3.130507e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8.823971e+14</td>\n",
       "      <td>1.780674e+09</td>\n",
       "      <td>3.132000e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       MSISDN/Number  Engagement_Euclidean_Distance  \\\n",
       "count   1.500010e+05                   1.500010e+05   \n",
       "mean    4.182414e+10                   9.166177e+08   \n",
       "std     2.438731e+12                   2.907772e+08   \n",
       "min     3.360100e+10                   5.836229e+07   \n",
       "25%     3.365094e+10                   7.792049e+08   \n",
       "50%     3.366361e+10                   9.166177e+08   \n",
       "75%     3.368307e+10                   1.053934e+09   \n",
       "max     8.823971e+14                   1.780674e+09   \n",
       "\n",
       "       Experience_Euclidean_Distance  \n",
       "count                   1.500010e+05  \n",
       "mean                    3.111019e+09  \n",
       "std                     1.018117e+08  \n",
       "min                     5.353832e+06  \n",
       "25%                     3.110430e+09  \n",
       "50%                     3.110430e+09  \n",
       "75%                     3.130507e+09  \n",
       "max                     3.132000e+09  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores1.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Normalizing the data\n",
    "x = scores1.values #returns a numpy array\n",
    "min_max_scaler = preprocessing.MinMaxScaler()\n",
    "x_scaled = min_max_scaler.fit_transform(x)\n",
    "scores2 = pd.DataFrame(x_scaled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# k means using 2 clusters and k-means++ initialization\n",
    "kmeans = KMeans(n_jobs = -1, n_clusters = 2, init='k-means++')\n",
    "kmeans.fit(scores2)\n",
    "pred = kmeans.predict(scores2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores_clustered = pd.DataFrame(scores2)\n",
    "scores_clustered['clusters'] = pred"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>clusters</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.248772e-08</td>\n",
       "      <td>0.266509</td>\n",
       "      <td>0.993101</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.163195e-08</td>\n",
       "      <td>0.682645</td>\n",
       "      <td>0.993101</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.809066e-07</td>\n",
       "      <td>0.383218</td>\n",
       "      <td>0.993101</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.692516e-07</td>\n",
       "      <td>0.917389</td>\n",
       "      <td>0.993101</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.119654e-07</td>\n",
       "      <td>0.638693</td>\n",
       "      <td>0.993101</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0         1         2  clusters\n",
       "0  7.248772e-08  0.266509  0.993101         1\n",
       "1  9.163195e-08  0.682645  0.993101         0\n",
       "2  1.809066e-07  0.383218  0.993101         1\n",
       "3  1.692516e-07  0.917389  0.993101         0\n",
       "4  1.119654e-07  0.638693  0.993101         0"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_clustered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    109976\n",
       "1     40025\n",
       "Name: clusters, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_clustered['clusters'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregating the average of satisfaction & experience score per cluster ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_data=scores_clustered['clusters']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_scores=pd.concat([scores,cluster_data], axis=1, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_scores.columns=['Engagement_Euclidean_Distance','MSISDN/Number','Experience_Euclidean_Distance','satisfaction_score','clusters']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "      <th>clusters</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.813902e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.172260e+09</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.914407e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.374411e+09</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.134411e+09</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "0                   5.173738e+08   3.366496e+10   \n",
       "1                   1.234090e+09   3.368185e+10   \n",
       "2                   7.183836e+08   3.376063e+10   \n",
       "3                   1.638393e+09   3.375034e+10   \n",
       "4                   1.158391e+09   3.369980e+10   \n",
       "\n",
       "   Experience_Euclidean_Distance  satisfaction_score  clusters  \n",
       "0                   3.110430e+09        1.813902e+09         1  \n",
       "1                   3.110430e+09        2.172260e+09         0  \n",
       "2                   3.110430e+09        1.914407e+09         1  \n",
       "3                   3.110430e+09        2.374411e+09         0  \n",
       "4                   3.110430e+09        2.134411e+09         0  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cluster_scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    109976\n",
       "1     40025\n",
       "Name: clusters, dtype: int64"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cluster_scores['clusters'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exporting final scores Table to MySQL Database ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting mysql-connector-python\n",
      "  Downloading https://files.pythonhosted.org/packages/32/92/aa9f928c09302be5897c8707264cb56ae2fad10425e37db04f2c4310781b/mysql_connector_python-8.0.21-cp37-cp37m-win_amd64.whl (809kB)\n",
      "Requirement already satisfied: protobuf>=3.0.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from mysql-connector-python) (3.12.2)\n",
      "Requirement already satisfied: setuptools in c:\\programdata\\anaconda3\\lib\\site-packages (from protobuf>=3.0.0->mysql-connector-python) (40.8.0)\n",
      "Requirement already satisfied: six>=1.9 in c:\\programdata\\anaconda3\\lib\\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.12.0)\n",
      "Installing collected packages: mysql-connector-python\n",
      "Successfully installed mysql-connector-python-8.0.21\n"
     ]
    }
   ],
   "source": [
    "!pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>Engagement_Euclidean_Distance</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_Euclidean_Distance</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.813902e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.172260e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.914407e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.374411e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.134411e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Engagement_Euclidean_Distance  MSISDN/Number  \\\n",
       "0                   5.173738e+08   3.366496e+10   \n",
       "1                   1.234090e+09   3.368185e+10   \n",
       "2                   7.183836e+08   3.376063e+10   \n",
       "3                   1.638393e+09   3.375034e+10   \n",
       "4                   1.158391e+09   3.369980e+10   \n",
       "\n",
       "   Experience_Euclidean_Distance  satisfaction_score  \n",
       "0                   3.110430e+09        1.813902e+09  \n",
       "1                   3.110430e+09        2.172260e+09  \n",
       "2                   3.110430e+09        1.914407e+09  \n",
       "3                   3.110430e+09        2.374411e+09  \n",
       "4                   3.110430e+09        2.134411e+09  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Engagement_score</th>\n",
       "      <th>MSISDN/Number</th>\n",
       "      <th>Experience_score</th>\n",
       "      <th>satisfaction_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.173738e+08</td>\n",
       "      <td>3.366496e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.813902e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.234090e+09</td>\n",
       "      <td>3.368185e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.172260e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.183836e+08</td>\n",
       "      <td>3.376063e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.914407e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.638393e+09</td>\n",
       "      <td>3.375034e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.374411e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.158391e+09</td>\n",
       "      <td>3.369980e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.134411e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.439738e+09</td>\n",
       "      <td>3.366819e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.275084e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9.221409e+08</td>\n",
       "      <td>3.366537e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.016286e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.371765e+09</td>\n",
       "      <td>3.376349e+10</td>\n",
       "      <td>3.131221e+09</td>\n",
       "      <td>2.251493e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1.076896e+09</td>\n",
       "      <td>3.369874e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.093663e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.378444e+08</td>\n",
       "      <td>3.365922e+10</td>\n",
       "      <td>3.128761e+09</td>\n",
       "      <td>2.033303e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7.472439e+08</td>\n",
       "      <td>3.366565e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.928837e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1.095797e+09</td>\n",
       "      <td>3.366447e+10</td>\n",
       "      <td>3.125965e+09</td>\n",
       "      <td>2.110881e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8.833491e+08</td>\n",
       "      <td>3.360329e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.996890e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8.608276e+08</td>\n",
       "      <td>3.365922e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.985629e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3.712218e+08</td>\n",
       "      <td>3.365922e+10</td>\n",
       "      <td>3.099211e+09</td>\n",
       "      <td>1.735216e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2.381956e+08</td>\n",
       "      <td>3.375376e+10</td>\n",
       "      <td>3.118153e+09</td>\n",
       "      <td>1.678174e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.581972e+08</td>\n",
       "      <td>3.366901e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.834314e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.924682e+08</td>\n",
       "      <td>3.366878e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.851449e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4.523731e+08</td>\n",
       "      <td>3.369876e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.781402e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7.758777e+08</td>\n",
       "      <td>3.376060e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.943154e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6.097900e+08</td>\n",
       "      <td>3.365875e+10</td>\n",
       "      <td>3.122134e+09</td>\n",
       "      <td>1.865962e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>7.320712e+08</td>\n",
       "      <td>3.365922e+10</td>\n",
       "      <td>3.107251e+09</td>\n",
       "      <td>1.919661e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1.073073e+09</td>\n",
       "      <td>3.362124e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.091752e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1.607953e+09</td>\n",
       "      <td>3.368183e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.359192e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1.070564e+09</td>\n",
       "      <td>3.361154e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.090497e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>8.364596e+08</td>\n",
       "      <td>3.377042e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.973445e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>4.460480e+08</td>\n",
       "      <td>3.366284e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.778239e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7.822126e+08</td>\n",
       "      <td>3.366434e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.946321e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>8.387594e+08</td>\n",
       "      <td>3.366916e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>1.974595e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.001275e+09</td>\n",
       "      <td>3.369826e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.055853e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149971</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366394e+10</td>\n",
       "      <td>3.131984e+09</td>\n",
       "      <td>2.024301e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149972</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.376239e+10</td>\n",
       "      <td>3.131238e+09</td>\n",
       "      <td>2.023928e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149973</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366856e+10</td>\n",
       "      <td>3.131303e+09</td>\n",
       "      <td>2.023960e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149974</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.365093e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149975</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366078e+10</td>\n",
       "      <td>3.131369e+09</td>\n",
       "      <td>2.023994e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149976</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366286e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149977</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366394e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149978</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366905e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149979</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.376185e+10</td>\n",
       "      <td>3.128969e+09</td>\n",
       "      <td>2.022793e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149980</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366524e+10</td>\n",
       "      <td>3.123534e+09</td>\n",
       "      <td>2.020076e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149981</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.362060e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149982</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366658e+10</td>\n",
       "      <td>3.131583e+09</td>\n",
       "      <td>2.024101e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149983</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366004e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149984</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.363551e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149985</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.378663e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149986</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366436e+10</td>\n",
       "      <td>3.131221e+09</td>\n",
       "      <td>2.023919e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149987</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366436e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149988</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366083e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149989</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.368584e+10</td>\n",
       "      <td>3.131907e+09</td>\n",
       "      <td>2.024263e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149990</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.376127e+10</td>\n",
       "      <td>3.131995e+09</td>\n",
       "      <td>2.024306e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149991</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.376215e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149992</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.376127e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149993</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.362611e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149994</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.364566e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149995</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366865e+10</td>\n",
       "      <td>3.115284e+09</td>\n",
       "      <td>2.015951e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149996</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.365069e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149997</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.366345e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149998</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.362189e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149999</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.361962e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150000</th>\n",
       "      <td>9.166177e+08</td>\n",
       "      <td>3.362632e+10</td>\n",
       "      <td>3.110430e+09</td>\n",
       "      <td>2.013524e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150001 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Engagement_score  MSISDN/Number  Experience_score  satisfaction_score\n",
       "0           5.173738e+08   3.366496e+10      3.110430e+09        1.813902e+09\n",
       "1           1.234090e+09   3.368185e+10      3.110430e+09        2.172260e+09\n",
       "2           7.183836e+08   3.376063e+10      3.110430e+09        1.914407e+09\n",
       "3           1.638393e+09   3.375034e+10      3.110430e+09        2.374411e+09\n",
       "4           1.158391e+09   3.369980e+10      3.110430e+09        2.134411e+09\n",
       "5           1.439738e+09   3.366819e+10      3.110430e+09        2.275084e+09\n",
       "6           9.221409e+08   3.366537e+10      3.110430e+09        2.016286e+09\n",
       "7           1.371765e+09   3.376349e+10      3.131221e+09        2.251493e+09\n",
       "8           1.076896e+09   3.369874e+10      3.110430e+09        2.093663e+09\n",
       "9           9.378444e+08   3.365922e+10      3.128761e+09        2.033303e+09\n",
       "10          7.472439e+08   3.366565e+10      3.110430e+09        1.928837e+09\n",
       "11          1.095797e+09   3.366447e+10      3.125965e+09        2.110881e+09\n",
       "12          8.833491e+08   3.360329e+10      3.110430e+09        1.996890e+09\n",
       "13          8.608276e+08   3.365922e+10      3.110430e+09        1.985629e+09\n",
       "14          3.712218e+08   3.365922e+10      3.099211e+09        1.735216e+09\n",
       "15          2.381956e+08   3.375376e+10      3.118153e+09        1.678174e+09\n",
       "16          5.581972e+08   3.366901e+10      3.110430e+09        1.834314e+09\n",
       "17          5.924682e+08   3.366878e+10      3.110430e+09        1.851449e+09\n",
       "18          4.523731e+08   3.369876e+10      3.110430e+09        1.781402e+09\n",
       "19          7.758777e+08   3.376060e+10      3.110430e+09        1.943154e+09\n",
       "20          6.097900e+08   3.365875e+10      3.122134e+09        1.865962e+09\n",
       "21          7.320712e+08   3.365922e+10      3.107251e+09        1.919661e+09\n",
       "22          1.073073e+09   3.362124e+10      3.110430e+09        2.091752e+09\n",
       "23          1.607953e+09   3.368183e+10      3.110430e+09        2.359192e+09\n",
       "24          1.070564e+09   3.361154e+10      3.110430e+09        2.090497e+09\n",
       "25          8.364596e+08   3.377042e+10      3.110430e+09        1.973445e+09\n",
       "26          4.460480e+08   3.366284e+10      3.110430e+09        1.778239e+09\n",
       "27          7.822126e+08   3.366434e+10      3.110430e+09        1.946321e+09\n",
       "28          8.387594e+08   3.366916e+10      3.110430e+09        1.974595e+09\n",
       "29          1.001275e+09   3.369826e+10      3.110430e+09        2.055853e+09\n",
       "...                  ...            ...               ...                 ...\n",
       "149971      9.166177e+08   3.366394e+10      3.131984e+09        2.024301e+09\n",
       "149972      9.166177e+08   3.376239e+10      3.131238e+09        2.023928e+09\n",
       "149973      9.166177e+08   3.366856e+10      3.131303e+09        2.023960e+09\n",
       "149974      9.166177e+08   3.365093e+10      3.110430e+09        2.013524e+09\n",
       "149975      9.166177e+08   3.366078e+10      3.131369e+09        2.023994e+09\n",
       "149976      9.166177e+08   3.366286e+10      3.110430e+09        2.013524e+09\n",
       "149977      9.166177e+08   3.366394e+10      3.110430e+09        2.013524e+09\n",
       "149978      9.166177e+08   3.366905e+10      3.110430e+09        2.013524e+09\n",
       "149979      9.166177e+08   3.376185e+10      3.128969e+09        2.022793e+09\n",
       "149980      9.166177e+08   3.366524e+10      3.123534e+09        2.020076e+09\n",
       "149981      9.166177e+08   3.362060e+10      3.110430e+09        2.013524e+09\n",
       "149982      9.166177e+08   3.366658e+10      3.131583e+09        2.024101e+09\n",
       "149983      9.166177e+08   3.366004e+10      3.110430e+09        2.013524e+09\n",
       "149984      9.166177e+08   3.363551e+10      3.110430e+09        2.013524e+09\n",
       "149985      9.166177e+08   3.378663e+10      3.110430e+09        2.013524e+09\n",
       "149986      9.166177e+08   3.366436e+10      3.131221e+09        2.023919e+09\n",
       "149987      9.166177e+08   3.366436e+10      3.110430e+09        2.013524e+09\n",
       "149988      9.166177e+08   3.366083e+10      3.110430e+09        2.013524e+09\n",
       "149989      9.166177e+08   3.368584e+10      3.131907e+09        2.024263e+09\n",
       "149990      9.166177e+08   3.376127e+10      3.131995e+09        2.024306e+09\n",
       "149991      9.166177e+08   3.376215e+10      3.110430e+09        2.013524e+09\n",
       "149992      9.166177e+08   3.376127e+10      3.110430e+09        2.013524e+09\n",
       "149993      9.166177e+08   3.362611e+10      3.110430e+09        2.013524e+09\n",
       "149994      9.166177e+08   3.364566e+10      3.110430e+09        2.013524e+09\n",
       "149995      9.166177e+08   3.366865e+10      3.115284e+09        2.015951e+09\n",
       "149996      9.166177e+08   3.365069e+10      3.110430e+09        2.013524e+09\n",
       "149997      9.166177e+08   3.366345e+10      3.110430e+09        2.013524e+09\n",
       "149998      9.166177e+08   3.362189e+10      3.110430e+09        2.013524e+09\n",
       "149999      9.166177e+08   3.361962e+10      3.110430e+09        2.013524e+09\n",
       "150000      9.166177e+08   3.362632e+10      3.110430e+09        2.013524e+09\n",
       "\n",
       "[150001 rows x 4 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores.rename(columns={\"Engagement_Euclidean_Distance\": \"Engagement_score\", \"Experience_Euclidean_Distance\": \"Experience_score\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores.to_csv('final_tellco_table.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SELECT statement on the exported table using python ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#First we import MySQL connector\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "150001\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "from mysql.connector import Error\n",
    "\n",
    "try:\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='elvis_10acad',\n",
    "                                         user='root',\n",
    "                                         password='Oyogo0713613781#')\n",
    "\n",
    "    sql_select_Query = \"select * from final_tellco_table\" #counting how many rows we have in our table\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(sql_select_Query)\n",
    "    records = cursor.fetchall()\n",
    "    print(cursor.rowcount)\n",
    "except Error as e:\n",
    "    print(\"Error reading data from MySQL table\", e)"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}