VZ cluster analysis - Skynet - clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vodafone Ziggo: cluster analysis using the Skynet case\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data\n",
    "\n",
    "   Import data from a local csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn import preprocessing\n",
    "from sklearn.metrics import silhouette_score\n",
    "\n",
    "# make sure all graphical output from mathplotlib is shown\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'D:\\\\Users\\\\welsendoorn\\\\OneDrive - VodafoneZiggo\\\\Training'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Are we in the right place?\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('Skynet_input_clustering_sample.csv' ,sep = ';', decimal = '.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data is a different directory: specify where\n",
    "in_path = os.path.join('D:\\\\Users\\\\welsendoorn\\\\OneDrive - VodafoneZiggo\\\\Training', 'Skynet_input_clustering_full.csv')\n",
    "\n",
    "df_2 = pd.read_csv(in_path ,sep = ';', decimal = '.')"
   ]
  },
  {
   "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>customers_id</th>\n",
       "      <th>urbanisation</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>NPS</th>\n",
       "      <th>subscription</th>\n",
       "      <th>subscription_add_on</th>\n",
       "      <th>customer_lifetime</th>\n",
       "      <th>GB_usage_month</th>\n",
       "      <th>service_calls_year</th>\n",
       "      <th>perc_payment_on_time</th>\n",
       "      <th>marketing_optin</th>\n",
       "      <th>account_login_3_months</th>\n",
       "      <th>mechanic_year</th>\n",
       "      <th>survey_changes</th>\n",
       "      <th>survey_news</th>\n",
       "      <th>survey_usage</th>\n",
       "      <th>survey_support</th>\n",
       "      <th>survey_payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>146113</td>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>M</td>\n",
       "      <td>8</td>\n",
       "      <td>Complete</td>\n",
       "      <td>no add-ons</td>\n",
       "      <td>less then 12 months</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>2.315</td>\n",
       "      <td>3.677</td>\n",
       "      <td>2.540</td>\n",
       "      <td>4.485</td>\n",
       "      <td>3.910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>414632</td>\n",
       "      <td>1</td>\n",
       "      <td>36.0</td>\n",
       "      <td>F</td>\n",
       "      <td>10</td>\n",
       "      <td>Internet only Compact</td>\n",
       "      <td>no add-ons</td>\n",
       "      <td>&gt; 5 years</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>4.843</td>\n",
       "      <td>2.583</td>\n",
       "      <td>4.783</td>\n",
       "      <td>1.800</td>\n",
       "      <td>2.835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>377220</td>\n",
       "      <td>2</td>\n",
       "      <td>17.0</td>\n",
       "      <td>M</td>\n",
       "      <td>8</td>\n",
       "      <td>Complete</td>\n",
       "      <td>no add-ons</td>\n",
       "      <td>less then 12 months</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>1.775</td>\n",
       "      <td>4.859</td>\n",
       "      <td>1.991</td>\n",
       "      <td>4.599</td>\n",
       "      <td>3.946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>244103</td>\n",
       "      <td>1</td>\n",
       "      <td>39.0</td>\n",
       "      <td>M</td>\n",
       "      <td>10</td>\n",
       "      <td>Internet only Compact</td>\n",
       "      <td>no add-ons</td>\n",
       "      <td>&gt; 5 years</td>\n",
       "      <td>93</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>1</td>\n",
       "      <td>2.676</td>\n",
       "      <td>3.355</td>\n",
       "      <td>4.299</td>\n",
       "      <td>3.473</td>\n",
       "      <td>3.072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>450183</td>\n",
       "      <td>2</td>\n",
       "      <td>20.0</td>\n",
       "      <td>M</td>\n",
       "      <td>9</td>\n",
       "      <td>Internet &amp; Television Plus</td>\n",
       "      <td>no add-ons</td>\n",
       "      <td>less then 12 months</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>3.363</td>\n",
       "      <td>4.200</td>\n",
       "      <td>4.104</td>\n",
       "      <td>4.523</td>\n",
       "      <td>3.280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customers_id  urbanisation   age gender  NPS                subscription  \\\n",
       "0        146113             1  16.0      M    8                    Complete   \n",
       "1        414632             1  36.0      F   10       Internet only Compact   \n",
       "2        377220             2  17.0      M    8                    Complete   \n",
       "3        244103             1  39.0      M   10       Internet only Compact   \n",
       "4        450183             2  20.0      M    9  Internet & Television Plus   \n",
       "\n",
       "  subscription_add_on    customer_lifetime  GB_usage_month  \\\n",
       "0          no add-ons  less then 12 months              53   \n",
       "1          no add-ons            > 5 years              92   \n",
       "2          no add-ons  less then 12 months              40   \n",
       "3          no add-ons            > 5 years              93   \n",
       "4          no add-ons  less then 12 months              43   \n",
       "\n",
       "   service_calls_year  perc_payment_on_time marketing_optin  \\\n",
       "0                   0                   1.0               Y   \n",
       "1                   0                   1.0               N   \n",
       "2                   0                   1.0               N   \n",
       "3                   0                   1.0               N   \n",
       "4                   0                   1.0               Y   \n",
       "\n",
       "  account_login_3_months  mechanic_year  survey_changes  survey_news  \\\n",
       "0                      N              0           2.315        3.677   \n",
       "1                      Y              0           4.843        2.583   \n",
       "2                      N              0           1.775        4.859   \n",
       "3                      Y              1           2.676        3.355   \n",
       "4                      Y              0           3.363        4.200   \n",
       "\n",
       "   survey_usage  survey_support  survey_payment  \n",
       "0         2.540           4.485           3.910  \n",
       "1         4.783           1.800           2.835  \n",
       "2         1.991           4.599           3.946  \n",
       "3         4.299           3.473           3.072  \n",
       "4         4.104           4.523           3.280  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check, do we have data ;)\n",
    "df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27637, 19)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# do we have all data?\n",
    "df_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 27637 entries, 0 to 27636\n",
      "Data columns (total 19 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   customers_id            27637 non-null  int64  \n",
      " 1   urbanisation            27637 non-null  int64  \n",
      " 2   age                     26787 non-null  float64\n",
      " 3   gender                  27637 non-null  object \n",
      " 4   NPS                     27637 non-null  int64  \n",
      " 5   subscription            27637 non-null  object \n",
      " 6   subscription_add_on     27637 non-null  object \n",
      " 7   customer_lifetime       27637 non-null  object \n",
      " 8   GB_usage_month          27637 non-null  int64  \n",
      " 9   service_calls_year      27637 non-null  int64  \n",
      " 10  perc_payment_on_time    27637 non-null  float64\n",
      " 11  marketing_optin         27637 non-null  object \n",
      " 12  account_login_3_months  27637 non-null  object \n",
      " 13  mechanic_year           27637 non-null  int64  \n",
      " 14  survey_changes          26809 non-null  float64\n",
      " 15  survey_news             27085 non-null  float64\n",
      " 16  survey_usage            26810 non-null  float64\n",
      " 17  survey_support          26809 non-null  float64\n",
      " 18  survey_payment          27085 non-null  float64\n",
      "dtypes: float64(7), int64(6), object(6)\n",
      "memory usage: 4.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data preparation\n",
    "\n",
    "- missing values\n",
    "- outliers\n",
    "- compute dummies for categories (can also be done in source)\n",
    "- selection (correlation plot: do not select variables that correlate too high)\n",
    "- normalization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing values\n",
    "K-means cannot handle missing values, so find out if your dataset has missing values and solve any NAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "customers_id                0\n",
       "mechanic_year               0\n",
       "account_login_3_months      0\n",
       "marketing_optin             0\n",
       "perc_payment_on_time        0\n",
       "GB_usage_month              0\n",
       "customer_lifetime           0\n",
       "service_calls_year          0\n",
       "subscription                0\n",
       "NPS                         0\n",
       "gender                      0\n",
       "urbanisation                0\n",
       "subscription_add_on         0\n",
       "survey_news               277\n",
       "survey_payment            294\n",
       "survey_changes            394\n",
       "age                       426\n",
       "survey_support            436\n",
       "survey_usage              459\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Do we have missing values?\n",
    "df.isna().sum().sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you do encounter missing values you can either remove the record or substitute it \n",
    "# with another value, like the mean or median. inplace=True > replace original, else a copy will be made.\n",
    "df['survey_news'].fillna((df['survey_news'].mean()), inplace=True)\n",
    "\n",
    "# Replace missing values on age with the mean\n",
    "df['age'].fillna((df['age'].mean()), inplace=True)\n",
    "\n",
    "# Make sure age is an integer and not a float\n",
    "df['age'] = df['age'].astype('int64')\n",
    "\n",
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspect your data and deal with outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use a boxplot to inspect your data\n",
    "sns.boxplot(x=df['age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Or use the histogram function in pandas\n",
    "df['age'].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Alternative: use a scatterplot against another variable\n",
    "sns.scatterplot(x=df['age'], y=df['survey_news'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You can use a function to remove all values above or below 1.5 times the IQR, or replace values you see fit\n",
    "# Here we substitute people with age 0 with the mean\n",
    "df['age'] = np.where(df['age'] == 0 , df['age'].mean(), df['age']).astype('int64')\n",
    "\n",
    "# And what about people older than 80 years?\n",
    "df['age'] = np.where(df['age'] >=80 , 80, df['age']).astype('int64')\n",
    "\n",
    "# Check results\n",
    "sns.boxplot(x=df['age'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create numeric variables as input\n",
    "K-mean can handle only numeric input, so we need to dummify categories (if you have them in your data)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Which variabels are not numeric?\n",
    "df.describe(exclude=[np.number])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new dataframe with all the input for the cluster analysis, starting with all numeric columns\n",
    "df_input = df.select_dtypes(include = 'number')\n",
    "\n",
    "# Show\n",
    "df_input.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify which variables you want to dummify, example\n",
    "pd.get_dummies(df['gender'], prefix='gender', drop_first=False, dummy_na= False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add dummified data for gender to your dataframe\n",
    "df_input = pd.concat([df_input, pd.get_dummies(df['gender'], prefix='gender', drop_first=False, dummy_na= False)],axis=1)\n",
    "\n",
    "# Add dummified data for online login to your dataframe\n",
    "df_input = pd.concat([df_input, pd.get_dummies(df['marketing_optin'], prefix='optin', drop_first=False, dummy_na= False)],axis=1)\n",
    "\n",
    "# Add dummified data for online login to your dataframe\n",
    "df_input = pd.concat([df_input, pd.get_dummies(df['account_login_3_months'], prefix='login', drop_first=False, dummy_na= False)],axis=1)\n",
    "\n",
    "df_input.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## K-means clustering\n",
    "\n",
    "- choose variables that are input for clustering > not to high corrrelations\n",
    "- determine # clusters using elbow measure or silhouette score\n",
    "- difference between normalized data and raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Can we add all data, first check for high correlations\n",
    "corrMatrix = df_input.select_dtypes(include = 'number').corr()\n",
    "\n",
    "# Clearly number of koffie and number of orders correlate too high\n",
    "plt.figure(figsize=(16,10))\n",
    "corrMatrixDisplay = sns.heatmap(corrMatrix, annot=True, linewidths=2, cmap='coolwarm', fmt='.2f')\n",
    "corrMatrixDisplay.set_ylim(sorted(corrMatrixDisplay.get_xlim(), reverse=True)) # fit top & bottom limits for mathplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the variables we do not use because of to high correlation\n",
    "df_input.drop(columns = ['gender_M', 'login_N', 'optin_N'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Standardize the input, clusters will tend to be separated along variables with greater variance\n",
    "\n",
    "# define standard scaler\n",
    "scaler = StandardScaler()\n",
    "\n",
    "# save the ID field, we don't want to scale it\n",
    "ID = df_input['customers_id']\n",
    "\n",
    "# fit scaler on all remaining fields\n",
    "scaler.fit(df_input.drop('customers_id', axis = 1))\n",
    "\n",
    "# Calculate scaled values and store them in a separate object\n",
    "scaled_values = scaler.transform(df_input.drop('customers_id', axis = 1))\n",
    "\n",
    "# Create a dataframe, add column names and put ID field back\n",
    "df_scaled = pd.DataFrame(scaled_values, index = df_input.index, columns = df_input.drop('customers_id', axis = 1).columns)\n",
    "df_scaled['customers_id'] = ID\n",
    "df_scaled.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you know for cluster analysis, an unsupervised method, there is no 'best' outcome. You have to evaluate cluster solutions based on a few metrics but foremost your business knowledge. **Guidelines**:\n",
    "\n",
    "- Limited number of clusters \n",
    "- That differ significantly\n",
    "- Are large enough to handle for our case\n",
    "- Are stable: repeated attempts (even on samples) lead to the same result\n",
    "- Cluster membership is easy to profile: expressive features that enable labelling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select initial set of variables for kmeans input\n",
    "kmeans_input = df_scaled[['GB_usage_month', 'perc_payment_on_time', 'survey_support', 'survey_changes', 'survey_usage', 'age', 'login_Y']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the elbow visual as guide to decide how many clusters we want to use; WCSS within cluster sum of squares\n",
    "wcss = []\n",
    "\n",
    "for i in range(1, 12):\n",
    "    kmeans = KMeans(n_clusters=i, init='k-means++', max_iter=300, n_init=10, random_state=4343, algorithm='auto')\n",
    "    kmeans.fit(kmeans_input)\n",
    "    wcss.append(kmeans.inertia_)\n",
    "plt.plot(range(1, 12), wcss)\n",
    "plt.title('Elbow Method')\n",
    "plt.xlabel('Number of clusters')\n",
    "plt.ylabel('WCSS')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the silhouette measure as guide to decide how many clusters we want to use\n",
    "silhouette_coefficients = []\n",
    "\n",
    "for k in range(2, 12):\n",
    "    kmeans = KMeans(n_clusters=k, init='k-means++', max_iter=300, n_init=10, random_state=4343, algorithm='auto'))\n",
    "    kmeans.fit(kmeans_input)\n",
    "    score = silhouette_score(kmeans_input, kmeans.labels_, metric='euclidean')\n",
    "    silhouette_coefficients.append(score)\n",
    "plt.plot(range(2, 12), silhouette_coefficients)\n",
    "plt.xticks(range(2, 12))\n",
    "plt.xlabel(\"Number of Clusters\")\n",
    "plt.ylabel(\"Silhouette Coefficient\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set k-means to 3 clusters\n",
    "kmeans = KMeans(n_clusters=3, init='k-means++', max_iter=300, n_init=10, random_state=4343, algorithm='auto')) \n",
    "kmeans.fit(kmeans_input)\n",
    "\n",
    "# Save clusters\n",
    "clusters = kmeans.fit_predict(kmeans_input)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save cluster solution in your dataframe, you can alsways enter multiple solutions\n",
    "df_input['cluster_solution'] = pd.Series(clusters, index=df_input.index)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evaluate \n",
    "\n",
    "- size of clusters\n",
    "- visualize results of clusters > dimension reduction\n",
    "- plot cluster solution against various different characteristics\n",
    "- interprete meaning: is this solution usefull for our task?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# How big are our clusters?\n",
    "df_input['cluster_solution'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualise cluster solution along two dimensions (PCA)\n",
    "from sklearn.decomposition import PCA\n",
    "pca = PCA(n_components=2)\n",
    "pca_result = pca.fit_transform(kmeans_input.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add dimensions to our input dataframe\n",
    "df_input['pca-1'] = pca_result[:,0]\n",
    "df_input['pca-2'] = pca_result[:,1]\n",
    "plt.figure(figsize=(16,10))\n",
    "sns.scatterplot(\n",
    "    x=\"pca-1\", y=\"pca-2\",\n",
    "    hue=\"cluster_solution\",\n",
    "    palette=sns.color_palette(\"hls\", 3),\n",
    "    data=df_input,\n",
    "    legend=\"full\",\n",
    "    alpha=0.4\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look at mean differences between clusters\n",
    "df_input.groupby(\n",
    "   ['cluster_solution']\n",
    ").agg(\n",
    "    {\n",
    "         'customers_id': 'count',\n",
    "         'age': 'mean',                \n",
    "         'urbanisation' : 'mean',\n",
    "         'GB_usage_month': 'mean',\n",
    "         'service_calls_year': 'mean',\n",
    "         'perc_payment_on_time': 'mean',\n",
    "         'mechanic_year': 'mean',\n",
    "         'NPS': 'mean',\n",
    "         'optin_Y': 'mean',\n",
    "         'login_Y': 'mean',\n",
    "         'gender_F': 'mean',\n",
    "         'survey_news': 'mean',\n",
    "         'survey_usage': 'mean', \n",
    "         'survey_support': 'mean',\n",
    "         'survey_payment': 'mean', \n",
    "         'survey_changes': 'mean' \n",
    "    }\n",
    ").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Looking at the mean value is sometimes not that informative, display the distribution of age for example\n",
    "sns.FacetGrid(df_input\n",
    "              ,hue=\"cluster_solution\"\n",
    "              ,height=5, aspect=2).map(sns.distplot, \"age\", label=\"cluster_solution\", hist=False, kde_kws={\"shade\": True})\n",
    "plt.legend(title=\"Cluster number\")\n",
    "plt.title('Distribution of customer age')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display the distribution of GB usage per month\n",
    "sns.FacetGrid(df_input\n",
    "              ,hue=\"cluster_solution\"\n",
    "              ,height=5, aspect=2).map(sns.distplot, \"survey_support\", label=\"cluster_solution\", hist=False, kde_kws={\"shade\": True})\n",
    "plt.legend(title=\"Cluster number\")\n",
    "plt.title('Distribution of website usage for online support')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display the distribution of GB usage per month\n",
    "sns.FacetGrid(df_input\n",
    "              ,hue=\"cluster_solution\"\n",
    "              ,height=5, aspect=2).map(sns.distplot, \"GB_usage_month\", label=\"cluster_solution\", hist=False, kde_kws={\"shade\": True})\n",
    "plt.legend(title=\"Cluster number\")\n",
    "plt.title('Distribution of GB usage per month')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save results\n",
    "\n",
    "- Merge other features for exploratory analysis\n",
    "- Save file to CSV\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create ouput dataframe: non-numeric fields to keep and Kmeans input\n",
    "df_save = pd.merge(df[['customers_id', 'subscription', 'subscription_add_on', 'customer_lifetime']], df_input, on='customers_id')\n",
    "\n",
    "# Subset final dataframe and upload to SQL-server\n",
    "df_save.to_csv('wvg_cluster_solution.csv', index = False, sep = ';', decimal = ',')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}