Keyword_Clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "74f6a865",
   "metadata": {},
   "source": [
    "# Download and import librairies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3fd4351a",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 106,
     "resources": {
      "http://localhost:8080/nbextensions/google.colab/files.js": {
       "data": "Ly8gQ29weXJpZ2h0IDIwMTcgR29vZ2xlIExMQwovLwovLyBMaWNlbnNlZCB1bmRlciB0aGUgQXBhY2hlIExpY2Vuc2UsIFZlcnNpb24gMi4wICh0aGUgIkxpY2Vuc2UiKTsKLy8geW91IG1heSBub3QgdXNlIHRoaXMgZmlsZSBleGNlcHQgaW4gY29tcGxpYW5jZSB3aXRoIHRoZSBMaWNlbnNlLgovLyBZb3UgbWF5IG9idGFpbiBhIGNvcHkgb2YgdGhlIExpY2Vuc2UgYXQKLy8KLy8gICAgICBodHRwOi8vd3d3LmFwYWNoZS5vcmcvbGljZW5zZXMvTElDRU5TRS0yLjAKLy8KLy8gVW5sZXNzIHJlcXVpcmVkIGJ5IGFwcGxpY2FibGUgbGF3IG9yIGFncmVlZCB0byBpbiB3cml0aW5nLCBzb2Z0d2FyZQovLyBkaXN0cmlidXRlZCB1bmRlciB0aGUgTGljZW5zZSBpcyBkaXN0cmlidXRlZCBvbiBhbiAiQVMgSVMiIEJBU0lTLAovLyBXSVRIT1VUIFdBUlJBTlRJRVMgT1IgQ09ORElUSU9OUyBPRiBBTlkgS0lORCwgZWl0aGVyIGV4cHJlc3Mgb3IgaW1wbGllZC4KLy8gU2VlIHRoZSBMaWNlbnNlIGZvciB0aGUgc3BlY2lmaWMgbGFuZ3VhZ2UgZ292ZXJuaW5nIHBlcm1pc3Npb25zIGFuZAovLyBsaW1pdGF0aW9ucyB1bmRlciB0aGUgTGljZW5zZS4KCi8qKgogKiBAZmlsZW92ZXJ2aWV3IEhlbHBlcnMgZm9yIGdvb2dsZS5jb2xhYiBQeXRob24gbW9kdWxlLgogKi8KKGZ1bmN0aW9uKHNjb3BlKSB7CmZ1bmN0aW9uIHNwYW4odGV4dCwgc3R5bGVBdHRyaWJ1dGVzID0ge30pIHsKICBjb25zdCBlbGVtZW50ID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnc3BhbicpOwogIGVsZW1lbnQudGV4dENvbnRlbnQgPSB0ZXh0OwogIGZvciAoY29uc3Qga2V5IG9mIE9iamVjdC5rZXlzKHN0eWxlQXR0cmlidXRlcykpIHsKICAgIGVsZW1lbnQuc3R5bGVba2V5XSA9IHN0eWxlQXR0cmlidXRlc1trZXldOwogIH0KICByZXR1cm4gZWxlbWVudDsKfQoKLy8gTWF4IG51bWJlciBvZiBieXRlcyB3aGljaCB3aWxsIGJlIHVwbG9hZGVkIGF0IGEgdGltZS4KY29uc3QgTUFYX1BBWUxPQURfU0laRSA9IDEwMCAqIDEwMjQ7CgpmdW5jdGlvbiBfdXBsb2FkRmlsZXMoaW5wdXRJZCwgb3V0cHV0SWQpIHsKICBjb25zdCBzdGVwcyA9IHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCk7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICAvLyBDYWNoZSBzdGVwcyBvbiB0aGUgb3V0cHV0RWxlbWVudCB0byBtYWtlIGl0IGF2YWlsYWJsZSBmb3IgdGhlIG5leHQgY2FsbAogIC8vIHRvIHVwbG9hZEZpbGVzQ29udGludWUgZnJvbSBQeXRob24uCiAgb3V0cHV0RWxlbWVudC5zdGVwcyA9IHN0ZXBzOwoKICByZXR1cm4gX3VwbG9hZEZpbGVzQ29udGludWUob3V0cHV0SWQpOwp9CgovLyBUaGlzIGlzIHJvdWdobHkgYW4gYXN5bmMgZ2VuZXJhdG9yIChub3Qgc3VwcG9ydGVkIGluIHRoZSBicm93c2VyIHlldCksCi8vIHdoZXJlIHRoZXJlIGFyZSBtdWx0aXBsZSBhc3luY2hyb25vdXMgc3RlcHMgYW5kIHRoZSBQeXRob24gc2lkZSBpcyBnb2luZwovLyB0byBwb2xsIGZvciBjb21wbGV0aW9uIG9mIGVhY2ggc3RlcC4KLy8gVGhpcyB1c2VzIGEgUHJvbWlzZSB0byBibG9jayB0aGUgcHl0aG9uIHNpZGUgb24gY29tcGxldGlvbiBvZiBlYWNoIHN0ZXAsCi8vIHRoZW4gcGFzc2VzIHRoZSByZXN1bHQgb2YgdGhlIHByZXZpb3VzIHN0ZXAgYXMgdGhlIGlucHV0IHRvIHRoZSBuZXh0IHN0ZXAuCmZ1bmN0aW9uIF91cGxvYWRGaWxlc0NvbnRpbnVlKG91dHB1dElkKSB7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICBjb25zdCBzdGVwcyA9IG91dHB1dEVsZW1lbnQuc3RlcHM7CgogIGNvbnN0IG5leHQgPSBzdGVwcy5uZXh0KG91dHB1dEVsZW1lbnQubGFzdFByb21pc2VWYWx1ZSk7CiAgcmV0dXJuIFByb21pc2UucmVzb2x2ZShuZXh0LnZhbHVlLnByb21pc2UpLnRoZW4oKHZhbHVlKSA9PiB7CiAgICAvLyBDYWNoZSB0aGUgbGFzdCBwcm9taXNlIHZhbHVlIHRvIG1ha2UgaXQgYXZhaWxhYmxlIHRvIHRoZSBuZXh0CiAgICAvLyBzdGVwIG9mIHRoZSBnZW5lcmF0b3IuCiAgICBvdXRwdXRFbGVtZW50Lmxhc3RQcm9taXNlVmFsdWUgPSB2YWx1ZTsKICAgIHJldHVybiBuZXh0LnZhbHVlLnJlc3BvbnNlOwogIH0pOwp9CgovKioKICogR2VuZXJhdG9yIGZ1bmN0aW9uIHdoaWNoIGlzIGNhbGxlZCBiZXR3ZWVuIGVhY2ggYXN5bmMgc3RlcCBvZiB0aGUgdXBsb2FkCiAqIHByb2Nlc3MuCiAqIEBwYXJhbSB7c3RyaW5nfSBpbnB1dElkIEVsZW1lbnQgSUQgb2YgdGhlIGlucHV0IGZpbGUgcGlja2VyIGVsZW1lbnQuCiAqIEBwYXJhbSB7c3RyaW5nfSBvdXRwdXRJZCBFbGVtZW50IElEIG9mIHRoZSBvdXRwdXQgZGlzcGxheS4KICogQHJldHVybiB7IUl0ZXJhYmxlPCFPYmplY3Q+fSBJdGVyYWJsZSBvZiBuZXh0IHN0ZXBzLgogKi8KZnVuY3Rpb24qIHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCkgewogIGNvbnN0IGlucHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKGlucHV0SWQpOwogIGlucHV0RWxlbWVudC5kaXNhYmxlZCA9IGZhbHNlOwoKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIG91dHB1dEVsZW1lbnQuaW5uZXJIVE1MID0gJyc7CgogIGNvbnN0IHBpY2tlZFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgaW5wdXRFbGVtZW50LmFkZEV2ZW50TGlzdGVuZXIoJ2NoYW5nZScsIChlKSA9PiB7CiAgICAgIHJlc29sdmUoZS50YXJnZXQuZmlsZXMpOwogICAgfSk7CiAgfSk7CgogIGNvbnN0IGNhbmNlbCA9IGRvY3VtZW50LmNyZWF0ZUVsZW1lbnQoJ2J1dHRvbicpOwogIGlucHV0RWxlbWVudC5wYXJlbnRFbGVtZW50LmFwcGVuZENoaWxkKGNhbmNlbCk7CiAgY2FuY2VsLnRleHRDb250ZW50ID0gJ0NhbmNlbCB1cGxvYWQnOwogIGNvbnN0IGNhbmNlbFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgY2FuY2VsLm9uY2xpY2sgPSAoKSA9PiB7CiAgICAgIHJlc29sdmUobnVsbCk7CiAgICB9OwogIH0pOwoKICAvLyBXYWl0IGZvciB0aGUgdXNlciB0byBwaWNrIHRoZSBmaWxlcy4KICBjb25zdCBmaWxlcyA9IHlpZWxkIHsKICAgIHByb21pc2U6IFByb21pc2UucmFjZShbcGlja2VkUHJvbWlzZSwgY2FuY2VsUHJvbWlzZV0pLAogICAgcmVzcG9uc2U6IHsKICAgICAgYWN0aW9uOiAnc3RhcnRpbmcnLAogICAgfQogIH07CgogIGNhbmNlbC5yZW1vdmUoKTsKCiAgLy8gRGlzYWJsZSB0aGUgaW5wdXQgZWxlbWVudCBzaW5jZSBmdXJ0aGVyIHBpY2tzIGFyZSBub3QgYWxsb3dlZC4KICBpbnB1dEVsZW1lbnQuZGlzYWJsZWQgPSB0cnVlOwoKICBpZiAoIWZpbGVzKSB7CiAgICByZXR1cm4gewogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbXBsZXRlJywKICAgICAgfQogICAgfTsKICB9CgogIGZvciAoY29uc3QgZmlsZSBvZiBmaWxlcykgewogICAgY29uc3QgbGkgPSBkb2N1bWVudC5jcmVhdGVFbGVtZW50KCdsaScpOwogICAgbGkuYXBwZW5kKHNwYW4oZmlsZS5uYW1lLCB7Zm9udFdlaWdodDogJ2JvbGQnfSkpOwogICAgbGkuYXBwZW5kKHNwYW4oCiAgICAgICAgYCgke2ZpbGUudHlwZSB8fCAnbi9hJ30pIC0gJHtmaWxlLnNpemV9IGJ5dGVzLCBgICsKICAgICAgICBgbGFzdCBtb2RpZmllZDogJHsKICAgICAgICAgICAgZmlsZS5sYXN0TW9kaWZpZWREYXRlID8gZmlsZS5sYXN0TW9kaWZpZWREYXRlLnRvTG9jYWxlRGF0ZVN0cmluZygpIDoKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgJ24vYSd9IC0gYCkpOwogICAgY29uc3QgcGVyY2VudCA9IHNwYW4oJzAlIGRvbmUnKTsKICAgIGxpLmFwcGVuZENoaWxkKHBlcmNlbnQpOwoKICAgIG91dHB1dEVsZW1lbnQuYXBwZW5kQ2hpbGQobGkpOwoKICAgIGNvbnN0IGZpbGVEYXRhUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICAgIGNvbnN0IHJlYWRlciA9IG5ldyBGaWxlUmVhZGVyKCk7CiAgICAgIHJlYWRlci5vbmxvYWQgPSAoZSkgPT4gewogICAgICAgIHJlc29sdmUoZS50YXJnZXQucmVzdWx0KTsKICAgICAgfTsKICAgICAgcmVhZGVyLnJlYWRBc0FycmF5QnVmZmVyKGZpbGUpOwogICAgfSk7CiAgICAvLyBXYWl0IGZvciB0aGUgZGF0YSB0byBiZSByZWFkeS4KICAgIGxldCBmaWxlRGF0YSA9IHlpZWxkIHsKICAgICAgcHJvbWlzZTogZmlsZURhdGFQcm9taXNlLAogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbnRpbnVlJywKICAgICAgfQogICAgfTsKCiAgICAvLyBVc2UgYSBjaHVua2VkIHNlbmRpbmcgdG8gYXZvaWQgbWVzc2FnZSBzaXplIGxpbWl0cy4gU2VlIGIvNjIxMTU2NjAuCiAgICBsZXQgcG9zaXRpb24gPSAwOwogICAgZG8gewogICAgICBjb25zdCBsZW5ndGggPSBNYXRoLm1pbihmaWxlRGF0YS5ieXRlTGVuZ3RoIC0gcG9zaXRpb24sIE1BWF9QQVlMT0FEX1NJWkUpOwogICAgICBjb25zdCBjaHVuayA9IG5ldyBVaW50OEFycmF5KGZpbGVEYXRhLCBwb3NpdGlvbiwgbGVuZ3RoKTsKICAgICAgcG9zaXRpb24gKz0gbGVuZ3RoOwoKICAgICAgY29uc3QgYmFzZTY0ID0gYnRvYShTdHJpbmcuZnJvbUNoYXJDb2RlLmFwcGx5KG51bGwsIGNodW5rKSk7CiAgICAgIHlpZWxkIHsKICAgICAgICByZXNwb25zZTogewogICAgICAgICAgYWN0aW9uOiAnYXBwZW5kJywKICAgICAgICAgIGZpbGU6IGZpbGUubmFtZSwKICAgICAgICAgIGRhdGE6IGJhc2U2NCwKICAgICAgICB9LAogICAgICB9OwoKICAgICAgbGV0IHBlcmNlbnREb25lID0gZmlsZURhdGEuYnl0ZUxlbmd0aCA9PT0gMCA/CiAgICAgICAgICAxMDAgOgogICAgICAgICAgTWF0aC5yb3VuZCgocG9zaXRpb24gLyBmaWxlRGF0YS5ieXRlTGVuZ3RoKSAqIDEwMCk7CiAgICAgIHBlcmNlbnQudGV4dENvbnRlbnQgPSBgJHtwZXJjZW50RG9uZX0lIGRvbmVgOwoKICAgIH0gd2hpbGUgKHBvc2l0aW9uIDwgZmlsZURhdGEuYnl0ZUxlbmd0aCk7CiAgfQoKICAvLyBBbGwgZG9uZS4KICB5aWVsZCB7CiAgICByZXNwb25zZTogewogICAgICBhY3Rpb246ICdjb21wbGV0ZScsCiAgICB9CiAgfTsKfQoKc2NvcGUuZ29vZ2xlID0gc2NvcGUuZ29vZ2xlIHx8IHt9OwpzY29wZS5nb29nbGUuY29sYWIgPSBzY29wZS5nb29nbGUuY29sYWIgfHwge307CnNjb3BlLmdvb2dsZS5jb2xhYi5fZmlsZXMgPSB7CiAgX3VwbG9hZEZpbGVzLAogIF91cGxvYWRGaWxlc0NvbnRpbnVlLAp9Owp9KShzZWxmKTsK",
       "headers": [
        [
         "content-type",
         "application/javascript"
        ]
       ],
       "ok": true,
       "status": 200,
       "status_text": ""
      }
     }
    },
    "id": "3fd4351a",
    "outputId": "dc6925eb-c879-4e73-a90b-e3c81d150ad5"
   },
   "outputs": [],
   "source": [
    "# Import Librairies\n",
    "# !pip install gspread_dataframe gspread oauth2client\n",
    "# !pip install gspread-pandas\n",
    "# !{sys.executable} -m pip install pygsheets\n",
    "from gspread_pandas import Spread, Client\n",
    "from google.oauth2.service_account import Credentials\n",
    "import pandas as pd\n",
    "import re\n",
    "import sys\n",
    "import json\n",
    "import gspread\n",
    "import pygsheets\n",
    "import csv\n",
    "from gspread_dataframe import get_as_dataframe, set_with_dataframe\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "from collections import defaultdict\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.feature_extraction import text\n",
    "from sklearn.cluster import DBSCAN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05f5dbb3",
   "metadata": {},
   "source": [
    "# Import our Google Search Console data from a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dce66826",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 665
    },
    "id": "dce66826",
    "outputId": "bcefb4ab-04a1-4af2-de0c-0778d383cfc1"
   },
   "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>Top queries</th>\n",
       "      <th>Clicks</th>\n",
       "      <th>Impressions</th>\n",
       "      <th>CTR</th>\n",
       "      <th>Position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>adelphi living</td>\n",
       "      <td>255</td>\n",
       "      <td>765</td>\n",
       "      <td>33.33%</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi</td>\n",
       "      <td>15</td>\n",
       "      <td>314</td>\n",
       "      <td>4.78%</td>\n",
       "      <td>10.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sil providers perth</td>\n",
       "      <td>11</td>\n",
       "      <td>98</td>\n",
       "      <td>11.22%</td>\n",
       "      <td>2.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>supported independent living perth</td>\n",
       "      <td>8</td>\n",
       "      <td>327</td>\n",
       "      <td>2.45%</td>\n",
       "      <td>8.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>disability accommodation perth</td>\n",
       "      <td>4</td>\n",
       "      <td>645</td>\n",
       "      <td>0.62%</td>\n",
       "      <td>21.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sda providers perth</td>\n",
       "      <td>4</td>\n",
       "      <td>39</td>\n",
       "      <td>10.26%</td>\n",
       "      <td>7.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ndis providers near me</td>\n",
       "      <td>3</td>\n",
       "      <td>54</td>\n",
       "      <td>5.56%</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>adelphi living mandurah</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>16.67%</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>sil ndis</td>\n",
       "      <td>2</td>\n",
       "      <td>906</td>\n",
       "      <td>0.22%</td>\n",
       "      <td>37.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ndis housing perth</td>\n",
       "      <td>2</td>\n",
       "      <td>454</td>\n",
       "      <td>0.44%</td>\n",
       "      <td>10.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ndis providers perth</td>\n",
       "      <td>2</td>\n",
       "      <td>357</td>\n",
       "      <td>0.56%</td>\n",
       "      <td>48.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>disability supported accommodation perth</td>\n",
       "      <td>2</td>\n",
       "      <td>222</td>\n",
       "      <td>0.9%</td>\n",
       "      <td>13.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>sil accommodation perth</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>15.38%</td>\n",
       "      <td>4.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ndis</td>\n",
       "      <td>1</td>\n",
       "      <td>974</td>\n",
       "      <td>0.1%</td>\n",
       "      <td>3.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>disability housing perth</td>\n",
       "      <td>1</td>\n",
       "      <td>498</td>\n",
       "      <td>0.2%</td>\n",
       "      <td>24.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>specialist disability accommodation perth</td>\n",
       "      <td>1</td>\n",
       "      <td>434</td>\n",
       "      <td>0.23%</td>\n",
       "      <td>8.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>sil</td>\n",
       "      <td>1</td>\n",
       "      <td>342</td>\n",
       "      <td>0.29%</td>\n",
       "      <td>5.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ndis events perth</td>\n",
       "      <td>1</td>\n",
       "      <td>264</td>\n",
       "      <td>0.38%</td>\n",
       "      <td>15.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ndis short term accommodation</td>\n",
       "      <td>1</td>\n",
       "      <td>261</td>\n",
       "      <td>0.38%</td>\n",
       "      <td>29.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>disability service providers perth</td>\n",
       "      <td>1</td>\n",
       "      <td>223</td>\n",
       "      <td>0.45%</td>\n",
       "      <td>66.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Top queries  Clicks  Impressions     CTR  \\\n",
       "0                              adelphi living     255          765  33.33%   \n",
       "1                                     adelphi      15          314   4.78%   \n",
       "2                         sil providers perth      11           98  11.22%   \n",
       "3          supported independent living perth       8          327   2.45%   \n",
       "4              disability accommodation perth       4          645   0.62%   \n",
       "5                         sda providers perth       4           39  10.26%   \n",
       "6                      ndis providers near me       3           54   5.56%   \n",
       "7                     adelphi living mandurah       3           18  16.67%   \n",
       "8                                    sil ndis       2          906   0.22%   \n",
       "9                          ndis housing perth       2          454   0.44%   \n",
       "10                       ndis providers perth       2          357   0.56%   \n",
       "11   disability supported accommodation perth       2          222    0.9%   \n",
       "12                    sil accommodation perth       2           13  15.38%   \n",
       "13                                       ndis       1          974    0.1%   \n",
       "14                   disability housing perth       1          498    0.2%   \n",
       "15  specialist disability accommodation perth       1          434   0.23%   \n",
       "16                                        sil       1          342   0.29%   \n",
       "17                          ndis events perth       1          264   0.38%   \n",
       "18              ndis short term accommodation       1          261   0.38%   \n",
       "19         disability service providers perth       1          223   0.45%   \n",
       "\n",
       "    Position  \n",
       "0       1.06  \n",
       "1      10.04  \n",
       "2       2.20  \n",
       "3       8.10  \n",
       "4      21.36  \n",
       "5       7.21  \n",
       "6       1.00  \n",
       "7       1.06  \n",
       "8      37.77  \n",
       "9      10.86  \n",
       "10     48.13  \n",
       "11     13.06  \n",
       "12      4.38  \n",
       "13      3.13  \n",
       "14     24.32  \n",
       "15      8.61  \n",
       "16      5.02  \n",
       "17     15.02  \n",
       "18     29.24  \n",
       "19     66.67  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import our Google Search Console CSV Data into a Dataframe\n",
    "df = pd.read_csv('Queries.csv', delimiter=',')\n",
    "df.head(20)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe2a29cf",
   "metadata": {},
   "source": [
    "## Create a list of keywords from our GSC data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4gVzU-z2abqK",
   "metadata": {
    "id": "4gVzU-z2abqK"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['adelphi living',\n",
       " 'adelphi',\n",
       " 'sil providers perth',\n",
       " 'supported independent living perth',\n",
       " 'disability accommodation perth',\n",
       " 'sda providers perth',\n",
       " 'ndis providers near me',\n",
       " 'adelphi living mandurah',\n",
       " 'sil ndis',\n",
       " 'ndis housing perth',\n",
       " 'ndis providers perth',\n",
       " 'disability supported accommodation perth',\n",
       " 'sil accommodation perth',\n",
       " 'ndis',\n",
       " 'disability housing perth',\n",
       " 'specialist disability accommodation perth',\n",
       " 'sil',\n",
       " 'ndis events perth',\n",
       " 'ndis short term accommodation',\n",
       " 'disability service providers perth',\n",
       " 'disability accommodation wa',\n",
       " 'medium term accommodation ndis',\n",
       " 'medium term accommodation',\n",
       " 'disability housing',\n",
       " 'ndis respite accommodation',\n",
       " 'medium term accommodation perth',\n",
       " 'ndis sda housing',\n",
       " 'disability homes',\n",
       " 'sil housing',\n",
       " 'adelphi apartments perth',\n",
       " 'adelphi living jobs',\n",
       " 'ndis robust housing',\n",
       " 'ndis wa providers',\n",
       " 'sda living',\n",
       " 'disability services near me',\n",
       " 'adelphi care',\n",
       " 'support coordinators',\n",
       " 'sda payment',\n",
       " 'assisted living for schizophrenia near me',\n",
       " 'ndia wa',\n",
       " 'ndis sda application form',\n",
       " 'adelphi housing',\n",
       " 'disability support organisation',\n",
       " 'disability support services near me',\n",
       " 'independent living supplies',\n",
       " 'ndis support provider',\n",
       " 'ndis perth',\n",
       " 'sda ndis',\n",
       " 'supported independent living ndis',\n",
       " 'ndis supported independent living',\n",
       " 'disability accommodation',\n",
       " 'ndis sil',\n",
       " 'ndis provider perth',\n",
       " 'sil provider',\n",
       " 'ndis accommodation',\n",
       " 'ndis events',\n",
       " 'ndis sda',\n",
       " 'supported independent living',\n",
       " 'sda provider',\n",
       " 'sil providers',\n",
       " 'supported accommodation perth',\n",
       " 'respite accommodation',\n",
       " 'disability group homes perth',\n",
       " 'ndis support coordination perth',\n",
       " 'assisted living perth',\n",
       " 'sil accommodation',\n",
       " 'sda accommodation',\n",
       " 'sda house',\n",
       " 'short term accommodation perth',\n",
       " 'supported independent living providers',\n",
       " 'sda housing perth',\n",
       " 'ndis perth wa',\n",
       " 'disability services perth',\n",
       " 'supported living accommodation',\n",
       " 'independent living wa',\n",
       " 'independent living specialists',\n",
       " 'independent living housing',\n",
       " 'supported living',\n",
       " 'ndis provider wa',\n",
       " 'independent living',\n",
       " 'ndis service provider perth',\n",
       " 'disability respite accommodation',\n",
       " 'ndis events wa',\n",
       " 'sda house available',\n",
       " 'ndis events western australia',\n",
       " 'ndis services perth',\n",
       " 'what is sda',\n",
       " 'disability accommodation services',\n",
       " 'residential disability access perth',\n",
       " 'ndis service providers perth',\n",
       " 'ndis respite',\n",
       " 'disability short term accommodation',\n",
       " 'independent living perth',\n",
       " 'independent living services',\n",
       " 'respite care perth',\n",
       " 'sda living western australia',\n",
       " 'supported accommodation ndis',\n",
       " 'disability care services perth',\n",
       " 'sda homes',\n",
       " 'ndis providers wa',\n",
       " 'ndis independent living',\n",
       " 'short term accommodation ndis',\n",
       " 'independent living apartments for disabled',\n",
       " 'sda housing ndis',\n",
       " 'supported disability accommodation',\n",
       " 'ndis medium term accommodation',\n",
       " 'support coordinator perth',\n",
       " 'disability and independent living',\n",
       " 'assisted living',\n",
       " 'sda houses',\n",
       " 'ndis living',\n",
       " 'ndis housing',\n",
       " 'disability rental housing perth',\n",
       " 'independent living solutions',\n",
       " 'disability support services perth',\n",
       " 'independent living perth wa',\n",
       " 'ndis accommodation vacancies',\n",
       " 'sda apartments',\n",
       " 'ndis mental health perth',\n",
       " 'independent living western australia',\n",
       " 'independent living homes',\n",
       " 'ndis assisted living',\n",
       " 'independent living facilities near me',\n",
       " 'sil accomodation',\n",
       " 'short term accommodation disability',\n",
       " 'independent living near me',\n",
       " 'long stay accommodation perth',\n",
       " 'independent living disabled',\n",
       " 'freedom adelphi',\n",
       " 'atsa independent living expo',\n",
       " 'sda housing availability',\n",
       " 'able living',\n",
       " 'sda properties perth',\n",
       " 'assisted living apartments for disabled',\n",
       " 'living in perth',\n",
       " 'disability service provider perth',\n",
       " 'sda houding ndis',\n",
       " 'supported accommodation for people with disabilities',\n",
       " 'disability apartments',\n",
       " 'mental health housing wa',\n",
       " 'housing disability perth',\n",
       " 'specialist disability accommodation providers',\n",
       " 'sda perth',\n",
       " 'ndis sil rules',\n",
       " 'sil and sda',\n",
       " 'disability home care perth',\n",
       " 'disability home care',\n",
       " 'specialist support coordination perth',\n",
       " 'ndis properties perth',\n",
       " 'housing for people with disabilities perth',\n",
       " 'specialist disability housing perth',\n",
       " 'disability housing options perth',\n",
       " 'ndis sda properties perth',\n",
       " 'respite short term accommodation',\n",
       " 'sda participants',\n",
       " 'sil funding ndis',\n",
       " 'long term accommodation perth',\n",
       " 'disability residential homes',\n",
       " 'physical disability housing perth',\n",
       " 'disability housing solutions perth',\n",
       " 'independent living expo',\n",
       " 'alchera living',\n",
       " 'respite care perth wa',\n",
       " 'disability apartments perth',\n",
       " 'ndis sil providers',\n",
       " 'sil ndis funding',\n",
       " 'supported independent living disability',\n",
       " 'supported accommodation',\n",
       " 'ndis accommodation provider',\n",
       " 'independent living center perth',\n",
       " 'it provider perth',\n",
       " 'ndis provider',\n",
       " 'perth disability renovation',\n",
       " 'short term accommodation',\n",
       " 'erskine accommodation',\n",
       " 'housing for the disabled',\n",
       " 'perth disability services',\n",
       " 'sil disability housing',\n",
       " 'sda providers',\n",
       " 'sda wa',\n",
       " 'atsa',\n",
       " 'sda',\n",
       " 'supported living ndis',\n",
       " 'disability solutions for independent living',\n",
       " 'sda providers victoria',\n",
       " 'disabled accommodation perth',\n",
       " 'howard accommodation',\n",
       " 'disabled persons housing service',\n",
       " 'sil services',\n",
       " 'sda and sil',\n",
       " 'supported independant living facility',\n",
       " 'disability home care services',\n",
       " 'ndis supported accommodation',\n",
       " 'sda services',\n",
       " 'maddington accommodation',\n",
       " 'long term disability accommodation services',\n",
       " 'furnished accommodation perth',\n",
       " 'perth disability service provider and participant connection expo 2021',\n",
       " 'ndis joondalup',\n",
       " 'sil eligibility',\n",
       " 'independent living disability housing',\n",
       " 'disabled living accommodation',\n",
       " 'disability services',\n",
       " 'atsa expo',\n",
       " 'sil sda',\n",
       " 'medium-term accommodation',\n",
       " 'ndis homes',\n",
       " 'retirement living perth',\n",
       " 'home for disabled',\n",
       " 'adelphi apartments',\n",
       " 'national disability insurance scheme perth',\n",
       " 'content living homes',\n",
       " 'disability activities perth',\n",
       " 'sda categories',\n",
       " 'respite accommodation ndis',\n",
       " 'perth living',\n",
       " 'short term accommodation for disabled',\n",
       " 'independent living products perth wa',\n",
       " 'ndis mandurah',\n",
       " 'disability care wa',\n",
       " 'sda disability',\n",
       " 'ndis short term accommodation providers',\n",
       " 'sda payments',\n",
       " 'short term apartments perth wa',\n",
       " 'ndis rockingham',\n",
       " 'ndis sil eligibility',\n",
       " 'short stay accommodation perth',\n",
       " 'perth short term accommodation',\n",
       " 'ndis sda rules',\n",
       " 'specialist disability accommodation',\n",
       " 'ndis support perth',\n",
       " 'respite',\n",
       " 'howlong accommodation',\n",
       " 'adelphi apartments echuca',\n",
       " 'disability perth',\n",
       " 'sil perth',\n",
       " 'adelphi perth',\n",
       " 'share accommodation perth',\n",
       " 'specialised disability accommodation',\n",
       " 'sil supports',\n",
       " 'serviced apartments perth',\n",
       " 'the adelphi',\n",
       " 'what are the ndis supported indepented living',\n",
       " 'adelphi hotel perth',\n",
       " 'approved ndis providers in perth',\n",
       " 'sda home',\n",
       " 'perth apartments short stay',\n",
       " 'aussie living homes',\n",
       " 'altum living',\n",
       " 'what is sda ndis',\n",
       " 'ndis short term accommodation providers melbourne',\n",
       " 'sda contact',\n",
       " 'respite ndis',\n",
       " 'perth convention centre accommodation',\n",
       " 'what is sil',\n",
       " 'disability housing wa',\n",
       " 'perth expo',\n",
       " 'ndis sil provider',\n",
       " 'disabled living apartments',\n",
       " 'ndis bunbury',\n",
       " 'disability living support',\n",
       " 'ndis contact number',\n",
       " 'mental health supported accommodation perth',\n",
       " 'sda housing providers',\n",
       " 'sil supported independent living',\n",
       " 'ndis specialist disability accommodation',\n",
       " 'worker accommodation',\n",
       " 'short term accomodation ndis',\n",
       " 'short stay disability accommodation',\n",
       " 'sil funding',\n",
       " 'ndis number',\n",
       " 'high physical support sda',\n",
       " 'robust housing',\n",
       " 'disability group homes',\n",
       " 'supported independent living provider',\n",
       " 'ndis housing provider',\n",
       " 'ndis service providers wa',\n",
       " 'supported independent living accommodation',\n",
       " 'adventist senior living',\n",
       " 'ndis air conditioning',\n",
       " 'independent living services for the disabled',\n",
       " 'ndis short term accommodation clarmore',\n",
       " 'live your potential expo',\n",
       " 'disability service provider and participant connection expo 2021',\n",
       " 'disability care perth',\n",
       " 'ndis housing providers',\n",
       " 'it providers perth',\n",
       " 'ndis home and living',\n",
       " 'ndis wa',\n",
       " 'what is sil ndis',\n",
       " 'support coordinators perth',\n",
       " 'surgical house perth',\n",
       " 'perth share accommodation',\n",
       " 'wheelchair accessible accommodation perth',\n",
       " 'share accommodation in perth',\n",
       " 'supported disability accommodation ndis',\n",
       " 'adelphi beauty',\n",
       " 'perth disability service provider and participant connection expo 2021, \\nperth convention and exhibition centre, 11 june',\n",
       " 'disability service provider and participant connection expo',\n",
       " 'ndis sil funding',\n",
       " 'short stay rentals perth',\n",
       " 'short stay apartments perth',\n",
       " 'disability',\n",
       " 'adelphi house',\n",
       " 'ndis apartments',\n",
       " 'ndis sil and sda',\n",
       " 'sil logo',\n",
       " 'robust sda',\n",
       " 'ndis tablet',\n",
       " 'accessible accommodation perth',\n",
       " 'community living perth',\n",
       " 'respite care in perth',\n",
       " 'workers accommodation',\n",
       " 'abilities expo perth 2021',\n",
       " 'sda funding',\n",
       " 'howard emery',\n",
       " 'ndis short term accomodation',\n",
       " 'ndis locations',\n",
       " 'sda funding ndis',\n",
       " 'get building sda',\n",
       " 'specialist disability accommodation providers victoria',\n",
       " 'share accomodation perth',\n",
       " 'ndis short term accommodation mental health',\n",
       " 'independent builders perth',\n",
       " 'disability organisations perth',\n",
       " 'ndis service providers',\n",
       " 'home and living ndis',\n",
       " 'ndis accommodation support',\n",
       " 'ndis short term accommodation providers victoria',\n",
       " 'ndis short term accommodation rules',\n",
       " 'contact sda',\n",
       " 'sil house',\n",
       " 'sil ltd',\n",
       " 'south perth short term accommodation',\n",
       " 'long term serviced apartments perth',\n",
       " 'medium term accommodation providers',\n",
       " 'home and living expo',\n",
       " 'short term apartments perth',\n",
       " 'living in perth wa',\n",
       " 'it provider',\n",
       " 'medium perth',\n",
       " 'ndis housing solutions',\n",
       " 'disability support perth',\n",
       " 'supported independent living wa',\n",
       " 'ndis service providers near me',\n",
       " 'sda solutions',\n",
       " 'sda and sil ndis',\n",
       " 'disability respite houses',\n",
       " 'fully accessible sda',\n",
       " 'sda access',\n",
       " 'specialist disability accommodation ndis',\n",
       " 'retirement living wa',\n",
       " 'adelphi insurance',\n",
       " 'independent living disability',\n",
       " 'independent living centre mandurah',\n",
       " 'service apartment perth',\n",
       " 'ndis provider registration wa',\n",
       " 'living independently',\n",
       " 'sdas',\n",
       " 'independant living',\n",
       " 'sda approved builders perth',\n",
       " 'short term disability accommodation',\n",
       " 'specialist disability accommodation services',\n",
       " 'ndis provider near me',\n",
       " 'assisted living mandurah',\n",
       " 'ndia sil',\n",
       " 'short term accomodation perth',\n",
       " 'sil support',\n",
       " 'alioth independents',\n",
       " 'handicap assisted living',\n",
       " 'short term stay perth',\n",
       " 'serviced apartments perth long term',\n",
       " 'short term rental perth',\n",
       " 'open homes perth',\n",
       " 'disability housing providers',\n",
       " 'sda approved housing',\n",
       " 'short term respite care',\n",
       " 'perth short stay apartments',\n",
       " 'sil living',\n",
       " 'disability living',\n",
       " 'ndis phone number',\n",
       " 'aldelphi',\n",
       " 'supported independent living homes',\n",
       " 'adelphi menu',\n",
       " 'sil disability',\n",
       " 'medium stay accommodation',\n",
       " 'sil houses',\n",
       " 'ndis short term accommodation rates',\n",
       " 'disability accomodation',\n",
       " 'ndis short term accommodation and assistance',\n",
       " 'avendelle assisted living',\n",
       " 'independent living centre perth',\n",
       " 'mental health accommodation perth',\n",
       " 'sda ndis rules',\n",
       " 'independent living accommodation',\n",
       " 'long stay accommodation perth cbd',\n",
       " 'living wa',\n",
       " 'sil homes',\n",
       " 'disability services mandurah',\n",
       " 'sda aged care',\n",
       " 'logo adelphi',\n",
       " 'shared accommodation',\n",
       " 'long term accommodation rockingham',\n",
       " 'disability housing solutions brisbane',\n",
       " 'clairvoyant perth',\n",
       " 'cheap serviced apartments perth',\n",
       " 'disabled living near me',\n",
       " 'aldephi',\n",
       " 'sda approved builders',\n",
       " 'ndis disability support',\n",
       " 'sda housing qld',\n",
       " 'housing ndis',\n",
       " 'ndis renovations perth',\n",
       " 'ndis support',\n",
       " 'abilities expo',\n",
       " 'sda sil',\n",
       " 'disability homes near me',\n",
       " 'improved liveability sda',\n",
       " '27 adelphi road claremont',\n",
       " 'living expo',\n",
       " 'sda robust',\n",
       " 'living in wa',\n",
       " 'ndis expo',\n",
       " 'sda levels ndis',\n",
       " 'ndis supported living',\n",
       " 'short term accommodation perth wa',\n",
       " 'sil assessment ndis',\n",
       " 'short stay perth',\n",
       " 'difference between sil and sda',\n",
       " 'what does sil include',\n",
       " 'sda virtual room',\n",
       " 'improved living solutions',\n",
       " 'independant living solutions',\n",
       " 'home expo perth',\n",
       " 'disability respite',\n",
       " 'air adelphi',\n",
       " 'possibility disability services',\n",
       " 'adelphi group',\n",
       " 'home assist wa',\n",
       " 'supports sil',\n",
       " 's&a living',\n",
       " 'ndis wa contact',\n",
       " 'disability centre',\n",
       " 'disability facility near me',\n",
       " 'disability service',\n",
       " 'disability service provider',\n",
       " 'sil accommodation ndis',\n",
       " 'ndis houses',\n",
       " 'wa ndis',\n",
       " 'ndis accommodation perth',\n",
       " 'developing australian communities expo',\n",
       " 'sda high physical support',\n",
       " 'what is ndis sil',\n",
       " 'improved livability',\n",
       " 'ndis medium term accommodation providers',\n",
       " 'temporary accommodation perth',\n",
       " 'howard livings',\n",
       " 'sda care',\n",
       " 'ndis sda eligibility',\n",
       " 'short stays perth',\n",
       " 'short term accommodation in perth',\n",
       " 'independence living',\n",
       " 'adelphi care services',\n",
       " 'independent living centres australia',\n",
       " 'short term rentals perth wa',\n",
       " 'perth short stay accommodation',\n",
       " 'live in treeby',\n",
       " 'disability housing solutions',\n",
       " 'independent living centre wa',\n",
       " 'ndis accomodation',\n",
       " 'shared supported accommodation disability services',\n",
       " 'independant living specialists',\n",
       " 'accommodation perth region',\n",
       " 'adelphi logo',\n",
       " 'connection perth',\n",
       " 'short term accommodation mandurah',\n",
       " 'supported independent living ndis, supported independent living vacancies, \\nsupported independent living sil',\n",
       " 'perth beauty expo',\n",
       " 'independent living equipment perth',\n",
       " 'ndis living assistance',\n",
       " 'homes west joondalup',\n",
       " 'perth serviced accommodation',\n",
       " 'sda logo',\n",
       " 'perth exhibitions',\n",
       " 'perth serviced apartment deal',\n",
       " 'shared accommodation perth',\n",
       " 'exhibitions perth',\n",
       " 'intellectual disability accommodation',\n",
       " 'diability services',\n",
       " 'disability facility',\n",
       " 'housing support',\n",
       " 'independent support services',\n",
       " 'living',\n",
       " 'ndis support workers near me',\n",
       " 'support independent living',\n",
       " 'disability home',\n",
       " 'ndis disability services',\n",
       " 'independent supported living',\n",
       " 'ndis home care',\n",
       " 'sil insurance',\n",
       " 'call ndis',\n",
       " 'disability support work',\n",
       " 'disability support worker',\n",
       " 'independent living facilities for disabled',\n",
       " 'ndis home',\n",
       " 'sda accomodation',\n",
       " 'supported independant living',\n",
       " 'ndis providers',\n",
       " 'ndis supplier',\n",
       " 'organisation',\n",
       " 'in home support ndis',\n",
       " 'medium term accomodation ndis',\n",
       " 'sil payments',\n",
       " 'disability expo perth 2021',\n",
       " 'adelphi freedom',\n",
       " 'sil ndia',\n",
       " 'disability expo perth',\n",
       " 'sda robust housing',\n",
       " 'connect expo',\n",
       " 'sda assessments',\n",
       " 'adelphi employment',\n",
       " 'apartments for people with disabilities',\n",
       " 'building ndis housing',\n",
       " 'now living',\n",
       " 'perth home expo',\n",
       " 'short term accommodation south perth',\n",
       " 'sda western australia',\n",
       " 'independent living ndis',\n",
       " 'nitropack scam',\n",
       " 'adelphi springs',\n",
       " 'independent living centre joondalup',\n",
       " 'disability services wa',\n",
       " 'disability independent living',\n",
       " 'ailfe perth',\n",
       " 'living and working in perth',\n",
       " 'short term lease perth',\n",
       " 'living perth',\n",
       " 'independent events perth',\n",
       " 'short term apartment rental perth',\n",
       " 'independent living centre',\n",
       " 'short term rentals perth',\n",
       " 'what is a sil',\n",
       " 'individualised living options',\n",
       " 'short term accomodation',\n",
       " 'freedom adelphi table',\n",
       " 'independent senior living',\n",
       " 'ndis jobs perth wa',\n",
       " 'independent assisted living',\n",
       " 'independent and assisted living communities',\n",
       " 'home modifications perth',\n",
       " 'disability care',\n",
       " 'in home respite care',\n",
       " 'sda design categories',\n",
       " 'access housing mandurah',\n",
       " 'living houses',\n",
       " 'access housing perth',\n",
       " 'what is sda funding',\n",
       " 'participant',\n",
       " 'short stay apartments',\n",
       " 'independent living facilities',\n",
       " 'simple approach perth',\n",
       " 'disability services midland',\n",
       " 'perth disability',\n",
       " 'independent living home',\n",
       " 'ndis provider logo',\n",
       " 'serviced accommodation perth',\n",
       " 'ndis accommodation providers',\n",
       " 'sda eligibility',\n",
       " 'respite care services perth',\n",
       " 'homes for disabled',\n",
       " 'independent care solution',\n",
       " 'adelphi house assisted living',\n",
       " 'adlephi',\n",
       " 'disability care center',\n",
       " 'disability care facility',\n",
       " 'disability care homes',\n",
       " 'disability holiday accommodation',\n",
       " 'disability houses',\n",
       " 'disability support worker agencies',\n",
       " 'disability supported accommodation',\n",
       " 'disability supported living',\n",
       " 'flexible living',\n",
       " 'indepedent living',\n",
       " 'independent disability',\n",
       " 'independent housing',\n",
       " 'independent living support',\n",
       " 'independent living victoria',\n",
       " 'indpendent living',\n",
       " 'living support',\n",
       " 'mason homes',\n",
       " 'ndis aged care providers',\n",
       " 'ndis disability housing',\n",
       " 'ndis near me',\n",
       " 'ndis provider in perth',\n",
       " 'ndis service',\n",
       " 'residential disability care facility',\n",
       " 'support living accommodation',\n",
       " 'support workers near me',\n",
       " 'supported living accomodation',\n",
       " 'supported living homes',\n",
       " 'supported living provider',\n",
       " 'supported living services',\n",
       " 'wa disability services',\n",
       " 'independent living program',\n",
       " 'independent living program for disabled',\n",
       " 'living living',\n",
       " 'ndis home care providers',\n",
       " 'ndis provider\"',\n",
       " 'ndis telephone number',\n",
       " 'sda housing sydney',\n",
       " 'sda loan',\n",
       " 'sda loans',\n",
       " 'wheelchair accommodation',\n",
       " '360 ndis',\n",
       " 'access living',\n",
       " 'afelphi',\n",
       " 'disablity support worker',\n",
       " 'disbaility',\n",
       " 'independent living for disabled',\n",
       " 'n.d.i.s',\n",
       " 'national disability services',\n",
       " 'ndia provider',\n",
       " 'ndis approved housing',\n",
       " 'ndis call',\n",
       " 'ndis provider number',\n",
       " 'ndis provider services',\n",
       " 'ndis specialist disability accommodation design standard',\n",
       " 'ndis.',\n",
       " 'ndis?',\n",
       " 'sda ndia',\n",
       " 'sil?',\n",
       " 'supported indepedent living',\n",
       " '/sil',\n",
       " 'ndis aged care',\n",
       " 'ndis australia',\n",
       " 'ndis service provider',\n",
       " 'sda approval',\n",
       " 'sda housing',\n",
       " 'sda housing adelaide',\n",
       " 'sil solutions',\n",
       " 'accommodation for disabled adults',\n",
       " 'accommodation for people with disabilities',\n",
       " 'aged care supported living',\n",
       " 'contact ndis',\n",
       " 'ndis home and living supports',\n",
       " 'sda in ndis',\n",
       " '\"ndis aged care',\n",
       " 'adelphi 2021',\n",
       " 'nursing specialty',\n",
       " 'sda disability housing',\n",
       " 'sda housing brisbane',\n",
       " 'sda smart homes reviews',\n",
       " 'sil housing ndis',\n",
       " 'abilities expo 2021',\n",
       " 'adelphi management',\n",
       " 'robust housing meaning',\n",
       " 'rockingham disability expo',\n",
       " 'sil.',\n",
       " 'accommodation for disability',\n",
       " 'adelp',\n",
       " 'adelphi ceo',\n",
       " 'sda call',\n",
       " 'disability specialists',\n",
       " 'indepent living',\n",
       " 'coordinators',\n",
       " 'home for the disabled near me',\n",
       " 'atsa 2021',\n",
       " 'sda application ndis',\n",
       " 'ndis expo perth',\n",
       " 'ndis short term accommodation guidelines',\n",
       " 'perth disability expo',\n",
       " 'atsa perth',\n",
       " 'sda category',\n",
       " 'sil sda ndis',\n",
       " 'senior housing',\n",
       " 'what is an sil ndis',\n",
       " 'sda assessment',\n",
       " 'independent living bunbury',\n",
       " '+independent +retirement +living',\n",
       " 'clicks adelphi',\n",
       " 'intellectual disability support services',\n",
       " 'who is sil',\n",
       " 'homes for independent living',\n",
       " 'independent retirement living',\n",
       " 'ndia perth',\n",
       " 'does ndis pay for accommodation',\n",
       " 'sda ceo',\n",
       " 'disability sil',\n",
       " 'independent home care',\n",
       " 'individual living options',\n",
       " 'perth long term accommodation',\n",
       " 'what is sil funding',\n",
       " 'sda apartment',\n",
       " 'ndis sda fact sheet',\n",
       " 'house sharing perth',\n",
       " 'disability providers perth',\n",
       " 'perth short term rental',\n",
       " 'treeby nursing home',\n",
       " '\"houses\"',\n",
       " 'share housing perth',\n",
       " 'apartments perth short stay',\n",
       " 'flintwood disability services',\n",
       " 'what is ndis wa',\n",
       " 'sda virtual',\n",
       " 'weekly accommodation perth',\n",
       " 'assisted living disability',\n",
       " 'erskine wa',\n",
       " 'sil home',\n",
       " 'sda options',\n",
       " 'short term living',\n",
       " 'short term rentals in perth',\n",
       " 'home living expo',\n",
       " 'sda location',\n",
       " 'ndis application wa',\n",
       " 'what is ndis',\n",
       " 'best independent living communities',\n",
       " 'disability agencies perth',\n",
       " 'disability expo',\n",
       " 'shared supported accommodation',\n",
       " 'disability houses for rent near me',\n",
       " 'independent living houses',\n",
       " 'ndis services wa',\n",
       " 'short term accommodations',\n",
       " 'expo perth',\n",
       " 'independent community living',\n",
       " 'ndis eligibility wa',\n",
       " 'personal care assistance',\n",
       " 'share house perth',\n",
       " 'accommodation peel region wa',\n",
       " 'long term stay perth',\n",
       " 'sda structures',\n",
       " 'short term work perth',\n",
       " 'www.sil',\n",
       " 'independent living australia',\n",
       " 'living in mandurah',\n",
       " 'perth expo centre',\n",
       " 'nulsen disability services',\n",
       " 'ndis support wa',\n",
       " 'independent living communities',\n",
       " 'long term accommodation bunbury',\n",
       " 'ndis sil team',\n",
       " 'sda resources',\n",
       " 'self contained apartments perth',\n",
       " 'what is specialist disability accommodation',\n",
       " 'long term accommodation services',\n",
       " 'service apartments perth',\n",
       " 'short stay apartment perth',\n",
       " 'beauty expo perth',\n",
       " 'perthexpo',\n",
       " 'short term accommodation subiaco',\n",
       " 'adelphi laudale',\n",
       " 'ndis sil quote',\n",
       " 'old age care homes near me',\n",
       " 'serviced appartments perth',\n",
       " 'best it service provider perth',\n",
       " 'exhibitions in perth',\n",
       " 'home care wa',\n",
       " 'home living exhibition 2021',\n",
       " 'living in perth australia',\n",
       " 'perth home care services',\n",
       " 'short term accommodation joondalup',\n",
       " 'sda vacancies',\n",
       " 'adelphi health services',\n",
       " 'ndis help with housing',\n",
       " 'aged care residences',\n",
       " 'independent living options',\n",
       " 'outdoor living mandurah',\n",
       " 'short term accommodation ndis providers',\n",
       " 'abode living',\n",
       " 'disabled houses',\n",
       " 'independent living units adelaide',\n",
       " 'respite care homes in perth',\n",
       " 'disability support accommodation',\n",
       " 'fifo short term accommodation perth',\n",
       " 'independent living centres',\n",
       " 'personal services perth wa',\n",
       " 'short term furnished rentals perth',\n",
       " 'independent living solution',\n",
       " 'dali accommodation',\n",
       " 'independent retirement living adelaide',\n",
       " 'residential respite high care',\n",
       " 'disabled independent living housing',\n",
       " 'respite care ndis',\n",
       " 'adelphi suites',\n",
       " 'disability employment services providers perth',\n",
       " 'disability house',\n",
       " 'home care perth wa',\n",
       " 'long term accommodation near me',\n",
       " 'independent living hobart',\n",
       " 'bold living',\n",
       " 'disabled living manchester',\n",
       " 'disability insurance perth',\n",
       " 'housing for people with disabilities',\n",
       " 'disability support providers',\n",
       " 'live in specialist care']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a variable to hold our 'Top queries' column only\n",
    "top_queries = df['Top queries']\n",
    "\n",
    "# Create a list of keywords and populate it from our dataframe\n",
    "keywords = []\n",
    "\n",
    "for x in top_queries:\n",
    "  keywords.append(x)\n",
    "\n",
    "# Display keywords list\n",
    "keywords"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b3b14d6",
   "metadata": {},
   "source": [
    "# Start clustering our keywords using Machine Learning sklearn library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "q4lV5VScU_Jd",
   "metadata": {
    "id": "q4lV5VScU_Jd"
   },
   "outputs": [],
   "source": [
    "# Define our keyword clustering model with sklearn and TfidfVectorizer (using DBSCAN and not Kmean)\n",
    "tfidf_vectorizer = TfidfVectorizer(max_df=0.2, max_features=10000,min_df=0.01,use_idf=True, ngram_range=(1,2))\n",
    "\n",
    "# Import our keyword list for Keyword Clustering\n",
    "tfidf_matrix = tfidf_vectorizer.fit_transform(keywords)\n",
    "\n",
    "# Define settings for cluster adjustment (number and size of clusters)\n",
    "ds = DBSCAN(eps=0.7, min_samples=5).fit(tfidf_matrix)\n",
    "clusters = ds.labels_"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e802626",
   "metadata": {},
   "source": [
    "## Create a dataframe to hold our keyword clusters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "65aeee4e",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "65aeee4e",
    "outputId": "e04d1269-f118-4ac8-a869-34f11b431e3d"
   },
   "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>Cluster Number</th>\n",
       "      <th>Top queries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1</td>\n",
       "      <td>adelphi living mandurah, sil housing, ndis wa providers, adelphi care, assisted living for schizophrenia near me, adelphi housing, ndis support provider, sil providers, sda accommodation, ndis services perth, ndis providers wa, disability and independent living, independent living solutions, sda apartments, independent living homes, long stay accommodation perth, independent living disabled, a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi living, adelphi, adelphi living jobs, freedom adelphi, adelphi perth, the adelphi, adelphi hotel perth, adelphi beauty, adelphi insurance, adelphi menu, logo adelphi, 27 adelphi road claremont, air adelphi, adelphi group, adelphi logo, adelphi freedom, adelphi employment, adelphi springs, freedom adelphi table, adelphi management, adelphi ceo, clicks adelphi, adelphi laudale, adelphi s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>sil providers perth, sda providers perth, ndis providers perth, disability service providers perth, ndis service providers perth, it providers perth, disability providers perth, disability employment services providers perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>supported independent living perth, supported independent living ndis, ndis supported independent living, supported independent living, supported independent living providers, supported independent living disability, sil supported independent living, supported independent living provider, supported independent living accommodation, supported independent living wa, supported independent living ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>ndis providers near me, disability services near me, disability support services near me, independent living facilities near me, independent living near me, ndis service providers near me, ndis provider near me, disabled living near me, disability homes near me, disability facility near me, ndis support workers near me, ndis near me, support workers near me, home for the disabled near me, disa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>sil ndis, sil, sil accommodation, sil eligibility, sil perth, sil supports, sil logo, sil ltd, ndia sil, sil living, sil disability, sil assessment ndis, supports sil, sil insurance, sil payments, sil ndia, sil?, /sil, sil., disability sil, www.sil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>ndis housing perth, disability housing perth, disability housing, disability rental housing perth, specialist disability housing perth, disability housing options perth, physical disability housing perth, disability housing solutions perth, sil disability housing, independent living disability housing, disability housing wa, disability housing providers, disability housing solutions brisbane, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>disability supported accommodation perth, supported accommodation perth, supported accommodation ndis, supported accommodation for people with disabilities, supported accommodation, ndis supported accommodation, mental health supported accommodation perth, shared supported accommodation disability services, disability supported accommodation, shared supported accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>sil accommodation perth, furnished accommodation perth, share accommodation perth, wheelchair accessible accommodation perth, accessible accommodation perth, mental health accommodation perth, ndis accommodation perth, temporary accommodation perth, accommodation perth region, shared accommodation perth, weekly accommodation perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>ndis, ndis events perth, ndis perth, ndis events, ndis living, ndis mental health perth, able living, ndis properties perth, alchera living, atsa, ndis joondalup, retirement living perth, perth living, ndis rockingham, altum living, ndis bunbury, ndis contact number, ndis number, adventist senior living, ndis air conditioning, ndis tablet, community living perth, howard emery, ndis locations, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>9</td>\n",
       "      <td>specialist disability accommodation perth, specialist disability accommodation providers, specialist disability accommodation, ndis specialist disability accommodation, specialist disability accommodation providers victoria, specialist disability accommodation ndis, specialist disability accommodation services, ndis specialist disability accommodation design standard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10</td>\n",
       "      <td>ndis short term accommodation, short term accommodation perth, disability short term accommodation, short term accommodation ndis, short term accommodation disability, respite short term accommodation, short term accommodation, ndis short term accommodation providers, perth short term accommodation, ndis short term accommodation providers melbourne, short term accomodation ndis, ndis short ter...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>11</td>\n",
       "      <td>medium term accommodation ndis, medium term accommodation, medium term accommodation perth, ndis medium term accommodation, medium-term accommodation, medium term accommodation providers, ndis medium term accommodation providers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>12</td>\n",
       "      <td>ndis respite accommodation, respite accommodation, disability respite accommodation, ndis respite, respite, respite ndis, disability respite, residential respite high care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>13</td>\n",
       "      <td>ndis sda housing, sda housing perth, sda housing ndis, sda housing availability, sda housing providers, sda housing qld, sda housing sydney, sda housing, sda housing adelaide, sda housing brisbane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>14</td>\n",
       "      <td>disability homes, disability group homes perth, sda homes, disability residential homes, ndis homes, content living homes, aussie living homes, disability group homes, open homes perth, sil homes, homes west joondalup, mason homes, sda smart homes reviews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>15</td>\n",
       "      <td>adelphi apartments perth, disability apartments perth, adelphi apartments, adelphi apartments echuca, serviced apartments perth, cheap serviced apartments perth, self contained apartments perth, service apartments perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>16</td>\n",
       "      <td>ndis robust housing, ndis housing, housing disability perth, robust housing, sda approved housing, housing ndis, sda robust housing, building ndis housing, ndis approved housing, robust housing meaning, senior housing, ndis help with housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>17</td>\n",
       "      <td>sda living, sda payment, ndis sda application form, sda ndis, ndis sda, sda properties perth, sda houding ndis, sda perth, ndis sda properties perth, sda participants, sda, sda categories, sda payments, ndis sda rules, sda contact, robust sda, get building sda, contact sda, fully accessible sda, sda access, sda approved builders perth, sda ndis rules, sda approved builders, improved liveabilit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>18</td>\n",
       "      <td>support coordinators, ndis support coordination perth, support coordinator perth, ndis support perth, disability living support, high physical support sda, support coordinators perth, ndis accommodation support, sil support, ndis support, sda high physical support, living support, support living accommodation, disablity support worker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>19</td>\n",
       "      <td>ndia wa, ndis events wa, sda wa, ndis wa, retirement living wa, living wa, ndis wa contact, wa ndis, erskine wa, ndis application wa, ndis eligibility wa, accommodation peel region wa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20</td>\n",
       "      <td>disability support organisation, disability support services perth, disability support perth, ndis disability support, disability support work, disability support worker, disability support worker agencies, intellectual disability support services, disability support accommodation, disability support providers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>21</td>\n",
       "      <td>independent living supplies, ndis perth wa, independent living wa, independent living specialists, independent living housing, independent living, independent living perth, independent living services, ndis independent living, independent living perth wa, independent living center perth, independent living products perth wa, independent builders perth, independent living disability, independen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>22</td>\n",
       "      <td>disability accommodation perth, disability accommodation wa, disability accommodation, disability accommodation services, supported disability accommodation, specialised disability accommodation, supported disability accommodation ndis, intellectual disability accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>23</td>\n",
       "      <td>ndis sil, ndis sil rules, sil funding ndis, ndis sil providers, sil ndis funding, ndis sil eligibility, ndis sil provider, sil funding, ndis sil funding, ndis sil team, ndis sil quote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>24</td>\n",
       "      <td>ndis provider perth, ndis provider wa, ndis provider, ndis provider registration wa, ndis provider logo, ndis provider\", ndis provider number, ndis provider services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>25</td>\n",
       "      <td>ndis accommodation, ndis accommodation vacancies, erskine accommodation, howard accommodation, maddington accommodation, howlong accommodation, worker accommodation, perth share accommodation, workers accommodation, shared accommodation, wheelchair accommodation, dali accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>26</td>\n",
       "      <td>assisted living perth, assisted living, ndis assisted living, assisted living mandurah, handicap assisted living, avendelle assisted living, independent assisted living, independent and assisted living communities, assisted living disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>27</td>\n",
       "      <td>sda house, sda house available, surgical house perth, adelphi house, sil house, house sharing perth, share house perth, disability house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>28</td>\n",
       "      <td>disability services perth, perth disability services, disability services, disability services mandurah, possibility disability services, ndis disability services, disability services wa, disability services midland, wa disability services, national disability services, flintwood disability services, nulsen disability services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>29</td>\n",
       "      <td>supported living accommodation, supported living, supported living ndis, ndis supported living, independent supported living, disability supported living, supported living accomodation, supported living homes, supported living provider, supported living services, aged care supported living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>30</td>\n",
       "      <td>ndis service provider perth, disability service provider perth, perth disability service provider and participant connection expo 2021, disability service provider and participant connection expo 2021, perth disability service provider and participant connection expo 2021, \\nperth convention and exhibition centre, 11 june, disability service provider and participant connection expo, disability...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>31</td>\n",
       "      <td>ndis events western australia, sda living western australia, independent living western australia, independent living centres australia, sda western australia, ndis australia, independent living australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>32</td>\n",
       "      <td>what is sda, what is sda ndis, what is sil, what is sil ndis, what is ndis sil, what is a sil, what is sda funding, what is an sil ndis, what is sil funding, what is ndis wa, what is ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>33</td>\n",
       "      <td>residential disability access perth, perth disability renovation, national disability insurance scheme perth, disability activities perth, disability perth, disability, disability organisations perth, disability living, disability facility, perth disability, disability specialists, disability agencies perth, disability insurance perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>34</td>\n",
       "      <td>respite care perth, respite care perth wa, respite care in perth, short term respite care, in home respite care, respite care services perth, respite care homes in perth, respite care ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>35</td>\n",
       "      <td>disability care services perth, disability care wa, disability care perth, disability care, disability care center, disability care facility, disability care homes, residential disability care facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>36</td>\n",
       "      <td>sda houses, disability respite houses, sil houses, ndis houses, living houses, disability houses, \"houses\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>37</td>\n",
       "      <td>living in perth, approved ndis providers in perth, share accommodation in perth, living and working in perth, exhibitions in perth, living in perth australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>38</td>\n",
       "      <td>sil and sda, sda and sil, ndis sil and sda, sda and sil ndis, difference between sil and sda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>39</td>\n",
       "      <td>disability home care perth, disability home care, disability home care services, ndis home care, ndis home care providers, independent home care, home care wa, perth home care services, home care perth wa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>40</td>\n",
       "      <td>long term accommodation perth, long term disability accommodation services, long term accommodation rockingham, perth long term accommodation, long term accommodation bunbury, long term accommodation services, long term accommodation near me</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>41</td>\n",
       "      <td>sil provider, sda provider, ndis accommodation provider, it provider perth, ndis housing provider, it provider, ndia provider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>42</td>\n",
       "      <td>atsa expo, perth expo, live your potential expo, abilities expo, living expo, ndis expo, developing australian communities expo, perth beauty expo, disability expo perth, connect expo, rockingham disability expo, ndis expo perth, perth disability expo, disability expo, expo perth, beauty expo perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>43</td>\n",
       "      <td>independent living apartments for disabled, home for disabled, short term accommodation for disabled, independent living facilities for disabled, homes for disabled, independent living program for disabled, independent living for disabled, accommodation for disabled adults</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>44</td>\n",
       "      <td>short stay accommodation perth, perth apartments short stay, short stay disability accommodation, short stay rentals perth, short stay apartments perth, perth short stay apartments, short stay perth, perth short stay accommodation, short stay apartments, apartments perth short stay, short stay apartment perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>45</td>\n",
       "      <td>sil accomodation, share accomodation perth, disability accomodation, ndis accomodation, sda accomodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>46</td>\n",
       "      <td>ndis home and living, home and living ndis, home and living expo, home expo perth, perth home expo, ndis home and living supports, home living expo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>47</td>\n",
       "      <td>independent living centre mandurah, independent living centre perth, independent living centre wa, independent living centre joondalup, independent living centre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>48</td>\n",
       "      <td>sda aged care, sda care, independent care solution, ndis aged care, \"ndis aged care, personal care assistance, aged care residences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>49</td>\n",
       "      <td>ndis housing solutions, sda solutions, improved living solutions, independant living solutions, sil solutions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>50</td>\n",
       "      <td>sda home, disability home, ndis home, home modifications perth, treeby nursing home, sil home</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>51</td>\n",
       "      <td>abilities expo perth 2021, adelphi 2021, abilities expo 2021, atsa 2021, home living exhibition 2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Cluster Number  \\\n",
       "0               -1   \n",
       "1                0   \n",
       "2                1   \n",
       "3                2   \n",
       "4                3   \n",
       "5                4   \n",
       "6                5   \n",
       "7                6   \n",
       "8                7   \n",
       "9                8   \n",
       "10               9   \n",
       "11              10   \n",
       "12              11   \n",
       "13              12   \n",
       "14              13   \n",
       "15              14   \n",
       "16              15   \n",
       "17              16   \n",
       "18              17   \n",
       "19              18   \n",
       "20              19   \n",
       "21              20   \n",
       "22              21   \n",
       "23              22   \n",
       "24              23   \n",
       "25              24   \n",
       "26              25   \n",
       "27              26   \n",
       "28              27   \n",
       "29              28   \n",
       "30              29   \n",
       "31              30   \n",
       "32              31   \n",
       "33              32   \n",
       "34              33   \n",
       "35              34   \n",
       "36              35   \n",
       "37              36   \n",
       "38              37   \n",
       "39              38   \n",
       "40              39   \n",
       "41              40   \n",
       "42              41   \n",
       "43              42   \n",
       "44              43   \n",
       "45              44   \n",
       "46              45   \n",
       "47              46   \n",
       "48              47   \n",
       "49              48   \n",
       "50              49   \n",
       "51              50   \n",
       "52              51   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                        Top queries  \n",
       "0   adelphi living mandurah, sil housing, ndis wa providers, adelphi care, assisted living for schizophrenia near me, adelphi housing, ndis support provider, sil providers, sda accommodation, ndis services perth, ndis providers wa, disability and independent living, independent living solutions, sda apartments, independent living homes, long stay accommodation perth, independent living disabled, a...  \n",
       "1   adelphi living, adelphi, adelphi living jobs, freedom adelphi, adelphi perth, the adelphi, adelphi hotel perth, adelphi beauty, adelphi insurance, adelphi menu, logo adelphi, 27 adelphi road claremont, air adelphi, adelphi group, adelphi logo, adelphi freedom, adelphi employment, adelphi springs, freedom adelphi table, adelphi management, adelphi ceo, clicks adelphi, adelphi laudale, adelphi s...  \n",
       "2                                                                                                                                                                                  sil providers perth, sda providers perth, ndis providers perth, disability service providers perth, ndis service providers perth, it providers perth, disability providers perth, disability employment services providers perth  \n",
       "3   supported independent living perth, supported independent living ndis, ndis supported independent living, supported independent living, supported independent living providers, supported independent living disability, sil supported independent living, supported independent living provider, supported independent living accommodation, supported independent living wa, supported independent living ...  \n",
       "4   ndis providers near me, disability services near me, disability support services near me, independent living facilities near me, independent living near me, ndis service providers near me, ndis provider near me, disabled living near me, disability homes near me, disability facility near me, ndis support workers near me, ndis near me, support workers near me, home for the disabled near me, disa...  \n",
       "5                                                                                                                                                          sil ndis, sil, sil accommodation, sil eligibility, sil perth, sil supports, sil logo, sil ltd, ndia sil, sil living, sil disability, sil assessment ndis, supports sil, sil insurance, sil payments, sil ndia, sil?, /sil, sil., disability sil, www.sil  \n",
       "6   ndis housing perth, disability housing perth, disability housing, disability rental housing perth, specialist disability housing perth, disability housing options perth, physical disability housing perth, disability housing solutions perth, sil disability housing, independent living disability housing, disability housing wa, disability housing providers, disability housing solutions brisbane, ...  \n",
       "7                           disability supported accommodation perth, supported accommodation perth, supported accommodation ndis, supported accommodation for people with disabilities, supported accommodation, ndis supported accommodation, mental health supported accommodation perth, shared supported accommodation disability services, disability supported accommodation, shared supported accommodation  \n",
       "8                                                                      sil accommodation perth, furnished accommodation perth, share accommodation perth, wheelchair accessible accommodation perth, accessible accommodation perth, mental health accommodation perth, ndis accommodation perth, temporary accommodation perth, accommodation perth region, shared accommodation perth, weekly accommodation perth  \n",
       "9   ndis, ndis events perth, ndis perth, ndis events, ndis living, ndis mental health perth, able living, ndis properties perth, alchera living, atsa, ndis joondalup, retirement living perth, perth living, ndis rockingham, altum living, ndis bunbury, ndis contact number, ndis number, adventist senior living, ndis air conditioning, ndis tablet, community living perth, howard emery, ndis locations, ...  \n",
       "10                                specialist disability accommodation perth, specialist disability accommodation providers, specialist disability accommodation, ndis specialist disability accommodation, specialist disability accommodation providers victoria, specialist disability accommodation ndis, specialist disability accommodation services, ndis specialist disability accommodation design standard  \n",
       "11  ndis short term accommodation, short term accommodation perth, disability short term accommodation, short term accommodation ndis, short term accommodation disability, respite short term accommodation, short term accommodation, ndis short term accommodation providers, perth short term accommodation, ndis short term accommodation providers melbourne, short term accomodation ndis, ndis short ter...  \n",
       "12                                                                                                                                                                             medium term accommodation ndis, medium term accommodation, medium term accommodation perth, ndis medium term accommodation, medium-term accommodation, medium term accommodation providers, ndis medium term accommodation providers  \n",
       "13                                                                                                                                                                                                                                      ndis respite accommodation, respite accommodation, disability respite accommodation, ndis respite, respite, respite ndis, disability respite, residential respite high care  \n",
       "14                                                                                                                                                                                                             ndis sda housing, sda housing perth, sda housing ndis, sda housing availability, sda housing providers, sda housing qld, sda housing sydney, sda housing, sda housing adelaide, sda housing brisbane  \n",
       "15                                                                                                                                                  disability homes, disability group homes perth, sda homes, disability residential homes, ndis homes, content living homes, aussie living homes, disability group homes, open homes perth, sil homes, homes west joondalup, mason homes, sda smart homes reviews  \n",
       "16                                                                                                                                                                                      adelphi apartments perth, disability apartments perth, adelphi apartments, adelphi apartments echuca, serviced apartments perth, cheap serviced apartments perth, self contained apartments perth, service apartments perth  \n",
       "17                                                                                                                                                                ndis robust housing, ndis housing, housing disability perth, robust housing, sda approved housing, housing ndis, sda robust housing, building ndis housing, ndis approved housing, robust housing meaning, senior housing, ndis help with housing  \n",
       "18  sda living, sda payment, ndis sda application form, sda ndis, ndis sda, sda properties perth, sda houding ndis, sda perth, ndis sda properties perth, sda participants, sda, sda categories, sda payments, ndis sda rules, sda contact, robust sda, get building sda, contact sda, fully accessible sda, sda access, sda approved builders perth, sda ndis rules, sda approved builders, improved liveabilit...  \n",
       "19                                                                 support coordinators, ndis support coordination perth, support coordinator perth, ndis support perth, disability living support, high physical support sda, support coordinators perth, ndis accommodation support, sil support, ndis support, sda high physical support, living support, support living accommodation, disablity support worker  \n",
       "20                                                                                                                                                                                                                          ndia wa, ndis events wa, sda wa, ndis wa, retirement living wa, living wa, ndis wa contact, wa ndis, erskine wa, ndis application wa, ndis eligibility wa, accommodation peel region wa  \n",
       "21                                                                                          disability support organisation, disability support services perth, disability support perth, ndis disability support, disability support work, disability support worker, disability support worker agencies, intellectual disability support services, disability support accommodation, disability support providers  \n",
       "22  independent living supplies, ndis perth wa, independent living wa, independent living specialists, independent living housing, independent living, independent living perth, independent living services, ndis independent living, independent living perth wa, independent living center perth, independent living products perth wa, independent builders perth, independent living disability, independen...  \n",
       "23                                                                                                                               disability accommodation perth, disability accommodation wa, disability accommodation, disability accommodation services, supported disability accommodation, specialised disability accommodation, supported disability accommodation ndis, intellectual disability accommodation  \n",
       "24                                                                                                                                                                                                                          ndis sil, ndis sil rules, sil funding ndis, ndis sil providers, sil ndis funding, ndis sil eligibility, ndis sil provider, sil funding, ndis sil funding, ndis sil team, ndis sil quote  \n",
       "25                                                                                                                                                                                                                                            ndis provider perth, ndis provider wa, ndis provider, ndis provider registration wa, ndis provider logo, ndis provider\", ndis provider number, ndis provider services  \n",
       "26                                                                                                                       ndis accommodation, ndis accommodation vacancies, erskine accommodation, howard accommodation, maddington accommodation, howlong accommodation, worker accommodation, perth share accommodation, workers accommodation, shared accommodation, wheelchair accommodation, dali accommodation  \n",
       "27                                                                                                                                                                assisted living perth, assisted living, ndis assisted living, assisted living mandurah, handicap assisted living, avendelle assisted living, independent assisted living, independent and assisted living communities, assisted living disability  \n",
       "28                                                                                                                                                                                                                                                                         sda house, sda house available, surgical house perth, adelphi house, sil house, house sharing perth, share house perth, disability house  \n",
       "29                                                                         disability services perth, perth disability services, disability services, disability services mandurah, possibility disability services, ndis disability services, disability services wa, disability services midland, wa disability services, national disability services, flintwood disability services, nulsen disability services  \n",
       "30                                                                                                               supported living accommodation, supported living, supported living ndis, ndis supported living, independent supported living, disability supported living, supported living accomodation, supported living homes, supported living provider, supported living services, aged care supported living  \n",
       "31  ndis service provider perth, disability service provider perth, perth disability service provider and participant connection expo 2021, disability service provider and participant connection expo 2021, perth disability service provider and participant connection expo 2021, \\nperth convention and exhibition centre, 11 june, disability service provider and participant connection expo, disability...  \n",
       "32                                                                                                                                                                                                     ndis events western australia, sda living western australia, independent living western australia, independent living centres australia, sda western australia, ndis australia, independent living australia  \n",
       "33                                                                                                                                                                                                                      what is sda, what is sda ndis, what is sil, what is sil ndis, what is ndis sil, what is a sil, what is sda funding, what is an sil ndis, what is sil funding, what is ndis wa, what is ndis  \n",
       "34                                                                 residential disability access perth, perth disability renovation, national disability insurance scheme perth, disability activities perth, disability perth, disability, disability organisations perth, disability living, disability facility, perth disability, disability specialists, disability agencies perth, disability insurance perth  \n",
       "35                                                                                                                                                                                                                     respite care perth, respite care perth wa, respite care in perth, short term respite care, in home respite care, respite care services perth, respite care homes in perth, respite care ndis  \n",
       "36                                                                                                                                                                                                        disability care services perth, disability care wa, disability care perth, disability care, disability care center, disability care facility, disability care homes, residential disability care facility  \n",
       "37                                                                                                                                                                                                                                                                                                       sda houses, disability respite houses, sil houses, ndis houses, living houses, disability houses, \"houses\"  \n",
       "38                                                                                                                                                                                                                                                    living in perth, approved ndis providers in perth, share accommodation in perth, living and working in perth, exhibitions in perth, living in perth australia  \n",
       "39                                                                                                                                                                                                                                                                                                                     sil and sda, sda and sil, ndis sil and sda, sda and sil ndis, difference between sil and sda  \n",
       "40                                                                                                                                                                                                     disability home care perth, disability home care, disability home care services, ndis home care, ndis home care providers, independent home care, home care wa, perth home care services, home care perth wa  \n",
       "41                                                                                                                                                                long term accommodation perth, long term disability accommodation services, long term accommodation rockingham, perth long term accommodation, long term accommodation bunbury, long term accommodation services, long term accommodation near me  \n",
       "42                                                                                                                                                                                                                                                                                    sil provider, sda provider, ndis accommodation provider, it provider perth, ndis housing provider, it provider, ndia provider  \n",
       "43                                                                                                      atsa expo, perth expo, live your potential expo, abilities expo, living expo, ndis expo, developing australian communities expo, perth beauty expo, disability expo perth, connect expo, rockingham disability expo, ndis expo perth, perth disability expo, disability expo, expo perth, beauty expo perth  \n",
       "44                                                                                                                                independent living apartments for disabled, home for disabled, short term accommodation for disabled, independent living facilities for disabled, homes for disabled, independent living program for disabled, independent living for disabled, accommodation for disabled adults  \n",
       "45                                                                                           short stay accommodation perth, perth apartments short stay, short stay disability accommodation, short stay rentals perth, short stay apartments perth, perth short stay apartments, short stay perth, perth short stay accommodation, short stay apartments, apartments perth short stay, short stay apartment perth  \n",
       "46                                                                                                                                                                                                                                                                                                         sil accomodation, share accomodation perth, disability accomodation, ndis accomodation, sda accomodation  \n",
       "47                                                                                                                                                                                                                                                              ndis home and living, home and living ndis, home and living expo, home expo perth, perth home expo, ndis home and living supports, home living expo  \n",
       "48                                                                                                                                                                                                                                                independent living centre mandurah, independent living centre perth, independent living centre wa, independent living centre joondalup, independent living centre  \n",
       "49                                                                                                                                                                                                                                                                              sda aged care, sda care, independent care solution, ndis aged care, \"ndis aged care, personal care assistance, aged care residences  \n",
       "50                                                                                                                                                                                                                                                                                                    ndis housing solutions, sda solutions, improved living solutions, independant living solutions, sil solutions  \n",
       "51                                                                                                                                                                                                                                                                                                                    sda home, disability home, ndis home, home modifications perth, treeby nursing home, sil home  \n",
       "52                                                                                                                                                                                                                                                                                                             abilities expo perth 2021, adelphi 2021, abilities expo 2021, atsa 2021, home living exhibition 2021  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a dataframe for our clusters\n",
    "cluster_df = pd.DataFrame(clusters, columns=['Cluster Number'])\n",
    "\n",
    "# Merge our cluster df with our top queries df\n",
    "result = pd.merge(cluster_df, top_queries, left_index=True, right_index=True)\n",
    "\n",
    "# We group our keywords by cluster number and separate them with a comma\n",
    "clusters_queries = result.groupby(['Cluster Number'])['Top queries'].apply(', '.join).reset_index()\n",
    "\n",
    "# Extend Column and Row width for a better readability \n",
    "pd.set_option('max_colwidth', 400)\n",
    "pd.set_option('max_rows', 100)\n",
    "\n",
    "clusters_queries\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488cbc0f",
   "metadata": {},
   "source": [
    "## Use the same ML model on each cluster so we can name our clusters automatically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "adee7c9a",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 357
    },
    "id": "adee7c9a",
    "outputId": "3f8923cb-1174-4f8a-a29a-80fa8cc90019"
   },
   "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>Cluster Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>perth providers disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>independent living supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>near disability ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sil disability ndia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>housing disability perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>accommodation supported disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>accommodation perth accessible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>living ndis perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>accommodation disability specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>term short accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>accommodation medium term</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>respite accommodation ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>housing sda ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>homes disability group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>apartments perth adelphi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>housing ndis robust</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>sda ndis perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>support ndis perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>wa ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>disability support perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>independent living perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>accommodation disability supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>sil ndis funding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ndis provider wa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>accommodation ndis dali</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>assisted living independent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>house perth sda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>disability services perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>living supported ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>provider service disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>australia living western</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>ndis sil sda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>disability perth insurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>care respite perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>care disability facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>houses disability living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>perth living accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>sda sil ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>care home disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>accommodation long term</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>provider ndis accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>expo perth disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>disabled independent living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>short stay perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>accomodation disability ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>home living expo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>centre independent living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>care aged ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>solutions living housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>home disability modifications</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2021 abilities expo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Cluster Name\n",
       "0                     perth ndis living\n",
       "1                 adelphi freedom perth\n",
       "2            perth providers disability\n",
       "3          independent living supported\n",
       "4                  near disability ndis\n",
       "5                   sil disability ndia\n",
       "6              housing disability perth\n",
       "7    accommodation supported disability\n",
       "8        accommodation perth accessible\n",
       "9                     living ndis perth\n",
       "10  accommodation disability specialist\n",
       "11             term short accommodation\n",
       "12            accommodation medium term\n",
       "13           respite accommodation ndis\n",
       "14                     housing sda ndis\n",
       "15               homes disability group\n",
       "16             apartments perth adelphi\n",
       "17                  housing ndis robust\n",
       "18                       sda ndis perth\n",
       "19                   support ndis perth\n",
       "20                       wa ndis living\n",
       "21             disability support perth\n",
       "22             independent living perth\n",
       "23   accommodation disability supported\n",
       "24                     sil ndis funding\n",
       "25                     ndis provider wa\n",
       "26              accommodation ndis dali\n",
       "27          assisted living independent\n",
       "28                      house perth sda\n",
       "29            disability services perth\n",
       "30                living supported ndis\n",
       "31          provider service disability\n",
       "32             australia living western\n",
       "33                         ndis sil sda\n",
       "34           disability perth insurance\n",
       "35                   care respite perth\n",
       "36             care disability facility\n",
       "37             houses disability living\n",
       "38           perth living accommodation\n",
       "39                         sda sil ndis\n",
       "40                 care home disability\n",
       "41              accommodation long term\n",
       "42          provider ndis accommodation\n",
       "43                expo perth disability\n",
       "44          disabled independent living\n",
       "45                     short stay perth\n",
       "46         accomodation disability ndis\n",
       "47                     home living expo\n",
       "48            centre independent living\n",
       "49                       care aged ndis\n",
       "50             solutions living housing\n",
       "51        home disability modifications\n",
       "52                  2021 abilities expo"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create an empty list to store our cluster names\n",
    "cluster_name = []\n",
    "\n",
    "# Use Iterrows and tfIdVectorizer to get all words and their TF-IDF Score per cluster\n",
    "for index, row in clusters_queries.iterrows():\n",
    "    \n",
    "    queries = row['Top queries']\n",
    "    queries = [queries]\n",
    "\n",
    "    # Add a stop_words function to drop irrelevant words (ex: “a”, “the”, “is”, “are”, \"and\" etc...)\n",
    "    my_stop_words = text.ENGLISH_STOP_WORDS\n",
    "\n",
    "    # Run tfIdfVectorizer for each cluster\n",
    "    tfIdfVectorizer=TfidfVectorizer(use_idf=True, stop_words=my_stop_words)\n",
    "    tfIdf = tfIdfVectorizer.fit_transform(queries)\n",
    "    query_score_cluster = pd.DataFrame(tfIdf[0].T.todense(), index=tfIdfVectorizer.get_feature_names(), columns=[\"TF-IDF Score\"])\n",
    "    query_score_cluster = query_score_cluster.sort_values('TF-IDF Score', ascending=False)\n",
    "\n",
    "    # Get the top 3 queries (selected by highest scores) per cluster and get them into lists, clean words of commas and ''\n",
    "    top_three_queries = query_score_cluster.index[:3]\n",
    "    top_three_queries_list = top_three_queries.values.tolist()\n",
    "    top_three_queries_list = str(top_three_queries_list).replace(',', '')\n",
    "    top_three_queries_list = str(top_three_queries_list).replace('[','').replace(']','')\n",
    "    top_three_queries_list = str(top_three_queries_list).replace(\"'\",\"\")\n",
    "\n",
    "    # Append our top 3 queries to our cluster_name list\n",
    "    cluster_name.append(top_three_queries_list)\n",
    "    \n",
    "    # Create a Cluster Name dataframe    \n",
    "    cluster_name_df = pd.DataFrame({'Cluster Name': cluster_name})\n",
    "\n",
    "cluster_name_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5d28276",
   "metadata": {},
   "source": [
    "# Merge our dataframes so we can see all of our keywords grouped into different clusters and their names automatically generated. We can make sure it is matching properly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7iGuS8gJ8xuX",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "7iGuS8gJ8xuX",
    "outputId": "370f0921-4481-47fe-82e0-d3a932ac8f3a"
   },
   "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>Cluster Name</th>\n",
       "      <th>Cluster Number</th>\n",
       "      <th>Top queries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>perth ndis living</td>\n",
       "      <td>-1</td>\n",
       "      <td>adelphi living mandurah, sil housing, ndis wa providers, adelphi care, assisted living for schizophrenia near me, adelphi housing, ndis support provider, sil providers, sda accommodation, ndis services perth, ndis providers wa, disability and independent living, independent living solutions, sda apartments, independent living homes, long stay accommodation perth, independent living disabled, a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi freedom perth</td>\n",
       "      <td>0</td>\n",
       "      <td>adelphi living, adelphi, adelphi living jobs, freedom adelphi, adelphi perth, the adelphi, adelphi hotel perth, adelphi beauty, adelphi insurance, adelphi menu, logo adelphi, 27 adelphi road claremont, air adelphi, adelphi group, adelphi logo, adelphi freedom, adelphi employment, adelphi springs, freedom adelphi table, adelphi management, adelphi ceo, clicks adelphi, adelphi laudale, adelphi s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>1</td>\n",
       "      <td>sil providers perth, sda providers perth, ndis providers perth, disability service providers perth, ndis service providers perth, it providers perth, disability providers perth, disability employment services providers perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>independent living supported</td>\n",
       "      <td>2</td>\n",
       "      <td>supported independent living perth, supported independent living ndis, ndis supported independent living, supported independent living, supported independent living providers, supported independent living disability, sil supported independent living, supported independent living provider, supported independent living accommodation, supported independent living wa, supported independent living ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>near disability ndis</td>\n",
       "      <td>3</td>\n",
       "      <td>ndis providers near me, disability services near me, disability support services near me, independent living facilities near me, independent living near me, ndis service providers near me, ndis provider near me, disabled living near me, disability homes near me, disability facility near me, ndis support workers near me, ndis near me, support workers near me, home for the disabled near me, disa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sil disability ndia</td>\n",
       "      <td>4</td>\n",
       "      <td>sil ndis, sil, sil accommodation, sil eligibility, sil perth, sil supports, sil logo, sil ltd, ndia sil, sil living, sil disability, sil assessment ndis, supports sil, sil insurance, sil payments, sil ndia, sil?, /sil, sil., disability sil, www.sil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>housing disability perth</td>\n",
       "      <td>5</td>\n",
       "      <td>ndis housing perth, disability housing perth, disability housing, disability rental housing perth, specialist disability housing perth, disability housing options perth, physical disability housing perth, disability housing solutions perth, sil disability housing, independent living disability housing, disability housing wa, disability housing providers, disability housing solutions brisbane, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>accommodation supported disability</td>\n",
       "      <td>6</td>\n",
       "      <td>disability supported accommodation perth, supported accommodation perth, supported accommodation ndis, supported accommodation for people with disabilities, supported accommodation, ndis supported accommodation, mental health supported accommodation perth, shared supported accommodation disability services, disability supported accommodation, shared supported accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>accommodation perth accessible</td>\n",
       "      <td>7</td>\n",
       "      <td>sil accommodation perth, furnished accommodation perth, share accommodation perth, wheelchair accessible accommodation perth, accessible accommodation perth, mental health accommodation perth, ndis accommodation perth, temporary accommodation perth, accommodation perth region, shared accommodation perth, weekly accommodation perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>living ndis perth</td>\n",
       "      <td>8</td>\n",
       "      <td>ndis, ndis events perth, ndis perth, ndis events, ndis living, ndis mental health perth, able living, ndis properties perth, alchera living, atsa, ndis joondalup, retirement living perth, perth living, ndis rockingham, altum living, ndis bunbury, ndis contact number, ndis number, adventist senior living, ndis air conditioning, ndis tablet, community living perth, howard emery, ndis locations, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>accommodation disability specialist</td>\n",
       "      <td>9</td>\n",
       "      <td>specialist disability accommodation perth, specialist disability accommodation providers, specialist disability accommodation, ndis specialist disability accommodation, specialist disability accommodation providers victoria, specialist disability accommodation ndis, specialist disability accommodation services, ndis specialist disability accommodation design standard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>term short accommodation</td>\n",
       "      <td>10</td>\n",
       "      <td>ndis short term accommodation, short term accommodation perth, disability short term accommodation, short term accommodation ndis, short term accommodation disability, respite short term accommodation, short term accommodation, ndis short term accommodation providers, perth short term accommodation, ndis short term accommodation providers melbourne, short term accomodation ndis, ndis short ter...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>accommodation medium term</td>\n",
       "      <td>11</td>\n",
       "      <td>medium term accommodation ndis, medium term accommodation, medium term accommodation perth, ndis medium term accommodation, medium-term accommodation, medium term accommodation providers, ndis medium term accommodation providers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>respite accommodation ndis</td>\n",
       "      <td>12</td>\n",
       "      <td>ndis respite accommodation, respite accommodation, disability respite accommodation, ndis respite, respite, respite ndis, disability respite, residential respite high care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>housing sda ndis</td>\n",
       "      <td>13</td>\n",
       "      <td>ndis sda housing, sda housing perth, sda housing ndis, sda housing availability, sda housing providers, sda housing qld, sda housing sydney, sda housing, sda housing adelaide, sda housing brisbane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>homes disability group</td>\n",
       "      <td>14</td>\n",
       "      <td>disability homes, disability group homes perth, sda homes, disability residential homes, ndis homes, content living homes, aussie living homes, disability group homes, open homes perth, sil homes, homes west joondalup, mason homes, sda smart homes reviews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>apartments perth adelphi</td>\n",
       "      <td>15</td>\n",
       "      <td>adelphi apartments perth, disability apartments perth, adelphi apartments, adelphi apartments echuca, serviced apartments perth, cheap serviced apartments perth, self contained apartments perth, service apartments perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>housing ndis robust</td>\n",
       "      <td>16</td>\n",
       "      <td>ndis robust housing, ndis housing, housing disability perth, robust housing, sda approved housing, housing ndis, sda robust housing, building ndis housing, ndis approved housing, robust housing meaning, senior housing, ndis help with housing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>sda ndis perth</td>\n",
       "      <td>17</td>\n",
       "      <td>sda living, sda payment, ndis sda application form, sda ndis, ndis sda, sda properties perth, sda houding ndis, sda perth, ndis sda properties perth, sda participants, sda, sda categories, sda payments, ndis sda rules, sda contact, robust sda, get building sda, contact sda, fully accessible sda, sda access, sda approved builders perth, sda ndis rules, sda approved builders, improved liveabilit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>support ndis perth</td>\n",
       "      <td>18</td>\n",
       "      <td>support coordinators, ndis support coordination perth, support coordinator perth, ndis support perth, disability living support, high physical support sda, support coordinators perth, ndis accommodation support, sil support, ndis support, sda high physical support, living support, support living accommodation, disablity support worker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>wa ndis living</td>\n",
       "      <td>19</td>\n",
       "      <td>ndia wa, ndis events wa, sda wa, ndis wa, retirement living wa, living wa, ndis wa contact, wa ndis, erskine wa, ndis application wa, ndis eligibility wa, accommodation peel region wa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>disability support perth</td>\n",
       "      <td>20</td>\n",
       "      <td>disability support organisation, disability support services perth, disability support perth, ndis disability support, disability support work, disability support worker, disability support worker agencies, intellectual disability support services, disability support accommodation, disability support providers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>independent living perth</td>\n",
       "      <td>21</td>\n",
       "      <td>independent living supplies, ndis perth wa, independent living wa, independent living specialists, independent living housing, independent living, independent living perth, independent living services, ndis independent living, independent living perth wa, independent living center perth, independent living products perth wa, independent builders perth, independent living disability, independen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>accommodation disability supported</td>\n",
       "      <td>22</td>\n",
       "      <td>disability accommodation perth, disability accommodation wa, disability accommodation, disability accommodation services, supported disability accommodation, specialised disability accommodation, supported disability accommodation ndis, intellectual disability accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>sil ndis funding</td>\n",
       "      <td>23</td>\n",
       "      <td>ndis sil, ndis sil rules, sil funding ndis, ndis sil providers, sil ndis funding, ndis sil eligibility, ndis sil provider, sil funding, ndis sil funding, ndis sil team, ndis sil quote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ndis provider wa</td>\n",
       "      <td>24</td>\n",
       "      <td>ndis provider perth, ndis provider wa, ndis provider, ndis provider registration wa, ndis provider logo, ndis provider\", ndis provider number, ndis provider services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>accommodation ndis dali</td>\n",
       "      <td>25</td>\n",
       "      <td>ndis accommodation, ndis accommodation vacancies, erskine accommodation, howard accommodation, maddington accommodation, howlong accommodation, worker accommodation, perth share accommodation, workers accommodation, shared accommodation, wheelchair accommodation, dali accommodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>assisted living independent</td>\n",
       "      <td>26</td>\n",
       "      <td>assisted living perth, assisted living, ndis assisted living, assisted living mandurah, handicap assisted living, avendelle assisted living, independent assisted living, independent and assisted living communities, assisted living disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>house perth sda</td>\n",
       "      <td>27</td>\n",
       "      <td>sda house, sda house available, surgical house perth, adelphi house, sil house, house sharing perth, share house perth, disability house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>disability services perth</td>\n",
       "      <td>28</td>\n",
       "      <td>disability services perth, perth disability services, disability services, disability services mandurah, possibility disability services, ndis disability services, disability services wa, disability services midland, wa disability services, national disability services, flintwood disability services, nulsen disability services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>living supported ndis</td>\n",
       "      <td>29</td>\n",
       "      <td>supported living accommodation, supported living, supported living ndis, ndis supported living, independent supported living, disability supported living, supported living accomodation, supported living homes, supported living provider, supported living services, aged care supported living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>provider service disability</td>\n",
       "      <td>30</td>\n",
       "      <td>ndis service provider perth, disability service provider perth, perth disability service provider and participant connection expo 2021, disability service provider and participant connection expo 2021, perth disability service provider and participant connection expo 2021, \\nperth convention and exhibition centre, 11 june, disability service provider and participant connection expo, disability...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>australia living western</td>\n",
       "      <td>31</td>\n",
       "      <td>ndis events western australia, sda living western australia, independent living western australia, independent living centres australia, sda western australia, ndis australia, independent living australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>ndis sil sda</td>\n",
       "      <td>32</td>\n",
       "      <td>what is sda, what is sda ndis, what is sil, what is sil ndis, what is ndis sil, what is a sil, what is sda funding, what is an sil ndis, what is sil funding, what is ndis wa, what is ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>disability perth insurance</td>\n",
       "      <td>33</td>\n",
       "      <td>residential disability access perth, perth disability renovation, national disability insurance scheme perth, disability activities perth, disability perth, disability, disability organisations perth, disability living, disability facility, perth disability, disability specialists, disability agencies perth, disability insurance perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>care respite perth</td>\n",
       "      <td>34</td>\n",
       "      <td>respite care perth, respite care perth wa, respite care in perth, short term respite care, in home respite care, respite care services perth, respite care homes in perth, respite care ndis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>care disability facility</td>\n",
       "      <td>35</td>\n",
       "      <td>disability care services perth, disability care wa, disability care perth, disability care, disability care center, disability care facility, disability care homes, residential disability care facility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>houses disability living</td>\n",
       "      <td>36</td>\n",
       "      <td>sda houses, disability respite houses, sil houses, ndis houses, living houses, disability houses, \"houses\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>perth living accommodation</td>\n",
       "      <td>37</td>\n",
       "      <td>living in perth, approved ndis providers in perth, share accommodation in perth, living and working in perth, exhibitions in perth, living in perth australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>sda sil ndis</td>\n",
       "      <td>38</td>\n",
       "      <td>sil and sda, sda and sil, ndis sil and sda, sda and sil ndis, difference between sil and sda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>care home disability</td>\n",
       "      <td>39</td>\n",
       "      <td>disability home care perth, disability home care, disability home care services, ndis home care, ndis home care providers, independent home care, home care wa, perth home care services, home care perth wa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>accommodation long term</td>\n",
       "      <td>40</td>\n",
       "      <td>long term accommodation perth, long term disability accommodation services, long term accommodation rockingham, perth long term accommodation, long term accommodation bunbury, long term accommodation services, long term accommodation near me</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>provider ndis accommodation</td>\n",
       "      <td>41</td>\n",
       "      <td>sil provider, sda provider, ndis accommodation provider, it provider perth, ndis housing provider, it provider, ndia provider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>expo perth disability</td>\n",
       "      <td>42</td>\n",
       "      <td>atsa expo, perth expo, live your potential expo, abilities expo, living expo, ndis expo, developing australian communities expo, perth beauty expo, disability expo perth, connect expo, rockingham disability expo, ndis expo perth, perth disability expo, disability expo, expo perth, beauty expo perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>disabled independent living</td>\n",
       "      <td>43</td>\n",
       "      <td>independent living apartments for disabled, home for disabled, short term accommodation for disabled, independent living facilities for disabled, homes for disabled, independent living program for disabled, independent living for disabled, accommodation for disabled adults</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>short stay perth</td>\n",
       "      <td>44</td>\n",
       "      <td>short stay accommodation perth, perth apartments short stay, short stay disability accommodation, short stay rentals perth, short stay apartments perth, perth short stay apartments, short stay perth, perth short stay accommodation, short stay apartments, apartments perth short stay, short stay apartment perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>accomodation disability ndis</td>\n",
       "      <td>45</td>\n",
       "      <td>sil accomodation, share accomodation perth, disability accomodation, ndis accomodation, sda accomodation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>home living expo</td>\n",
       "      <td>46</td>\n",
       "      <td>ndis home and living, home and living ndis, home and living expo, home expo perth, perth home expo, ndis home and living supports, home living expo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>centre independent living</td>\n",
       "      <td>47</td>\n",
       "      <td>independent living centre mandurah, independent living centre perth, independent living centre wa, independent living centre joondalup, independent living centre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>care aged ndis</td>\n",
       "      <td>48</td>\n",
       "      <td>sda aged care, sda care, independent care solution, ndis aged care, \"ndis aged care, personal care assistance, aged care residences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>solutions living housing</td>\n",
       "      <td>49</td>\n",
       "      <td>ndis housing solutions, sda solutions, improved living solutions, independant living solutions, sil solutions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>home disability modifications</td>\n",
       "      <td>50</td>\n",
       "      <td>sda home, disability home, ndis home, home modifications perth, treeby nursing home, sil home</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2021 abilities expo</td>\n",
       "      <td>51</td>\n",
       "      <td>abilities expo perth 2021, adelphi 2021, abilities expo 2021, atsa 2021, home living exhibition 2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Cluster Name  Cluster Number  \\\n",
       "0                     perth ndis living              -1   \n",
       "1                 adelphi freedom perth               0   \n",
       "2            perth providers disability               1   \n",
       "3          independent living supported               2   \n",
       "4                  near disability ndis               3   \n",
       "5                   sil disability ndia               4   \n",
       "6              housing disability perth               5   \n",
       "7    accommodation supported disability               6   \n",
       "8        accommodation perth accessible               7   \n",
       "9                     living ndis perth               8   \n",
       "10  accommodation disability specialist               9   \n",
       "11             term short accommodation              10   \n",
       "12            accommodation medium term              11   \n",
       "13           respite accommodation ndis              12   \n",
       "14                     housing sda ndis              13   \n",
       "15               homes disability group              14   \n",
       "16             apartments perth adelphi              15   \n",
       "17                  housing ndis robust              16   \n",
       "18                       sda ndis perth              17   \n",
       "19                   support ndis perth              18   \n",
       "20                       wa ndis living              19   \n",
       "21             disability support perth              20   \n",
       "22             independent living perth              21   \n",
       "23   accommodation disability supported              22   \n",
       "24                     sil ndis funding              23   \n",
       "25                     ndis provider wa              24   \n",
       "26              accommodation ndis dali              25   \n",
       "27          assisted living independent              26   \n",
       "28                      house perth sda              27   \n",
       "29            disability services perth              28   \n",
       "30                living supported ndis              29   \n",
       "31          provider service disability              30   \n",
       "32             australia living western              31   \n",
       "33                         ndis sil sda              32   \n",
       "34           disability perth insurance              33   \n",
       "35                   care respite perth              34   \n",
       "36             care disability facility              35   \n",
       "37             houses disability living              36   \n",
       "38           perth living accommodation              37   \n",
       "39                         sda sil ndis              38   \n",
       "40                 care home disability              39   \n",
       "41              accommodation long term              40   \n",
       "42          provider ndis accommodation              41   \n",
       "43                expo perth disability              42   \n",
       "44          disabled independent living              43   \n",
       "45                     short stay perth              44   \n",
       "46         accomodation disability ndis              45   \n",
       "47                     home living expo              46   \n",
       "48            centre independent living              47   \n",
       "49                       care aged ndis              48   \n",
       "50             solutions living housing              49   \n",
       "51        home disability modifications              50   \n",
       "52                  2021 abilities expo              51   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                        Top queries  \n",
       "0   adelphi living mandurah, sil housing, ndis wa providers, adelphi care, assisted living for schizophrenia near me, adelphi housing, ndis support provider, sil providers, sda accommodation, ndis services perth, ndis providers wa, disability and independent living, independent living solutions, sda apartments, independent living homes, long stay accommodation perth, independent living disabled, a...  \n",
       "1   adelphi living, adelphi, adelphi living jobs, freedom adelphi, adelphi perth, the adelphi, adelphi hotel perth, adelphi beauty, adelphi insurance, adelphi menu, logo adelphi, 27 adelphi road claremont, air adelphi, adelphi group, adelphi logo, adelphi freedom, adelphi employment, adelphi springs, freedom adelphi table, adelphi management, adelphi ceo, clicks adelphi, adelphi laudale, adelphi s...  \n",
       "2                                                                                                                                                                                  sil providers perth, sda providers perth, ndis providers perth, disability service providers perth, ndis service providers perth, it providers perth, disability providers perth, disability employment services providers perth  \n",
       "3   supported independent living perth, supported independent living ndis, ndis supported independent living, supported independent living, supported independent living providers, supported independent living disability, sil supported independent living, supported independent living provider, supported independent living accommodation, supported independent living wa, supported independent living ...  \n",
       "4   ndis providers near me, disability services near me, disability support services near me, independent living facilities near me, independent living near me, ndis service providers near me, ndis provider near me, disabled living near me, disability homes near me, disability facility near me, ndis support workers near me, ndis near me, support workers near me, home for the disabled near me, disa...  \n",
       "5                                                                                                                                                          sil ndis, sil, sil accommodation, sil eligibility, sil perth, sil supports, sil logo, sil ltd, ndia sil, sil living, sil disability, sil assessment ndis, supports sil, sil insurance, sil payments, sil ndia, sil?, /sil, sil., disability sil, www.sil  \n",
       "6   ndis housing perth, disability housing perth, disability housing, disability rental housing perth, specialist disability housing perth, disability housing options perth, physical disability housing perth, disability housing solutions perth, sil disability housing, independent living disability housing, disability housing wa, disability housing providers, disability housing solutions brisbane, ...  \n",
       "7                           disability supported accommodation perth, supported accommodation perth, supported accommodation ndis, supported accommodation for people with disabilities, supported accommodation, ndis supported accommodation, mental health supported accommodation perth, shared supported accommodation disability services, disability supported accommodation, shared supported accommodation  \n",
       "8                                                                      sil accommodation perth, furnished accommodation perth, share accommodation perth, wheelchair accessible accommodation perth, accessible accommodation perth, mental health accommodation perth, ndis accommodation perth, temporary accommodation perth, accommodation perth region, shared accommodation perth, weekly accommodation perth  \n",
       "9   ndis, ndis events perth, ndis perth, ndis events, ndis living, ndis mental health perth, able living, ndis properties perth, alchera living, atsa, ndis joondalup, retirement living perth, perth living, ndis rockingham, altum living, ndis bunbury, ndis contact number, ndis number, adventist senior living, ndis air conditioning, ndis tablet, community living perth, howard emery, ndis locations, ...  \n",
       "10                                specialist disability accommodation perth, specialist disability accommodation providers, specialist disability accommodation, ndis specialist disability accommodation, specialist disability accommodation providers victoria, specialist disability accommodation ndis, specialist disability accommodation services, ndis specialist disability accommodation design standard  \n",
       "11  ndis short term accommodation, short term accommodation perth, disability short term accommodation, short term accommodation ndis, short term accommodation disability, respite short term accommodation, short term accommodation, ndis short term accommodation providers, perth short term accommodation, ndis short term accommodation providers melbourne, short term accomodation ndis, ndis short ter...  \n",
       "12                                                                                                                                                                             medium term accommodation ndis, medium term accommodation, medium term accommodation perth, ndis medium term accommodation, medium-term accommodation, medium term accommodation providers, ndis medium term accommodation providers  \n",
       "13                                                                                                                                                                                                                                      ndis respite accommodation, respite accommodation, disability respite accommodation, ndis respite, respite, respite ndis, disability respite, residential respite high care  \n",
       "14                                                                                                                                                                                                             ndis sda housing, sda housing perth, sda housing ndis, sda housing availability, sda housing providers, sda housing qld, sda housing sydney, sda housing, sda housing adelaide, sda housing brisbane  \n",
       "15                                                                                                                                                  disability homes, disability group homes perth, sda homes, disability residential homes, ndis homes, content living homes, aussie living homes, disability group homes, open homes perth, sil homes, homes west joondalup, mason homes, sda smart homes reviews  \n",
       "16                                                                                                                                                                                      adelphi apartments perth, disability apartments perth, adelphi apartments, adelphi apartments echuca, serviced apartments perth, cheap serviced apartments perth, self contained apartments perth, service apartments perth  \n",
       "17                                                                                                                                                                ndis robust housing, ndis housing, housing disability perth, robust housing, sda approved housing, housing ndis, sda robust housing, building ndis housing, ndis approved housing, robust housing meaning, senior housing, ndis help with housing  \n",
       "18  sda living, sda payment, ndis sda application form, sda ndis, ndis sda, sda properties perth, sda houding ndis, sda perth, ndis sda properties perth, sda participants, sda, sda categories, sda payments, ndis sda rules, sda contact, robust sda, get building sda, contact sda, fully accessible sda, sda access, sda approved builders perth, sda ndis rules, sda approved builders, improved liveabilit...  \n",
       "19                                                                 support coordinators, ndis support coordination perth, support coordinator perth, ndis support perth, disability living support, high physical support sda, support coordinators perth, ndis accommodation support, sil support, ndis support, sda high physical support, living support, support living accommodation, disablity support worker  \n",
       "20                                                                                                                                                                                                                          ndia wa, ndis events wa, sda wa, ndis wa, retirement living wa, living wa, ndis wa contact, wa ndis, erskine wa, ndis application wa, ndis eligibility wa, accommodation peel region wa  \n",
       "21                                                                                          disability support organisation, disability support services perth, disability support perth, ndis disability support, disability support work, disability support worker, disability support worker agencies, intellectual disability support services, disability support accommodation, disability support providers  \n",
       "22  independent living supplies, ndis perth wa, independent living wa, independent living specialists, independent living housing, independent living, independent living perth, independent living services, ndis independent living, independent living perth wa, independent living center perth, independent living products perth wa, independent builders perth, independent living disability, independen...  \n",
       "23                                                                                                                               disability accommodation perth, disability accommodation wa, disability accommodation, disability accommodation services, supported disability accommodation, specialised disability accommodation, supported disability accommodation ndis, intellectual disability accommodation  \n",
       "24                                                                                                                                                                                                                          ndis sil, ndis sil rules, sil funding ndis, ndis sil providers, sil ndis funding, ndis sil eligibility, ndis sil provider, sil funding, ndis sil funding, ndis sil team, ndis sil quote  \n",
       "25                                                                                                                                                                                                                                            ndis provider perth, ndis provider wa, ndis provider, ndis provider registration wa, ndis provider logo, ndis provider\", ndis provider number, ndis provider services  \n",
       "26                                                                                                                       ndis accommodation, ndis accommodation vacancies, erskine accommodation, howard accommodation, maddington accommodation, howlong accommodation, worker accommodation, perth share accommodation, workers accommodation, shared accommodation, wheelchair accommodation, dali accommodation  \n",
       "27                                                                                                                                                                assisted living perth, assisted living, ndis assisted living, assisted living mandurah, handicap assisted living, avendelle assisted living, independent assisted living, independent and assisted living communities, assisted living disability  \n",
       "28                                                                                                                                                                                                                                                                         sda house, sda house available, surgical house perth, adelphi house, sil house, house sharing perth, share house perth, disability house  \n",
       "29                                                                         disability services perth, perth disability services, disability services, disability services mandurah, possibility disability services, ndis disability services, disability services wa, disability services midland, wa disability services, national disability services, flintwood disability services, nulsen disability services  \n",
       "30                                                                                                               supported living accommodation, supported living, supported living ndis, ndis supported living, independent supported living, disability supported living, supported living accomodation, supported living homes, supported living provider, supported living services, aged care supported living  \n",
       "31  ndis service provider perth, disability service provider perth, perth disability service provider and participant connection expo 2021, disability service provider and participant connection expo 2021, perth disability service provider and participant connection expo 2021, \\nperth convention and exhibition centre, 11 june, disability service provider and participant connection expo, disability...  \n",
       "32                                                                                                                                                                                                     ndis events western australia, sda living western australia, independent living western australia, independent living centres australia, sda western australia, ndis australia, independent living australia  \n",
       "33                                                                                                                                                                                                                      what is sda, what is sda ndis, what is sil, what is sil ndis, what is ndis sil, what is a sil, what is sda funding, what is an sil ndis, what is sil funding, what is ndis wa, what is ndis  \n",
       "34                                                                 residential disability access perth, perth disability renovation, national disability insurance scheme perth, disability activities perth, disability perth, disability, disability organisations perth, disability living, disability facility, perth disability, disability specialists, disability agencies perth, disability insurance perth  \n",
       "35                                                                                                                                                                                                                     respite care perth, respite care perth wa, respite care in perth, short term respite care, in home respite care, respite care services perth, respite care homes in perth, respite care ndis  \n",
       "36                                                                                                                                                                                                        disability care services perth, disability care wa, disability care perth, disability care, disability care center, disability care facility, disability care homes, residential disability care facility  \n",
       "37                                                                                                                                                                                                                                                                                                       sda houses, disability respite houses, sil houses, ndis houses, living houses, disability houses, \"houses\"  \n",
       "38                                                                                                                                                                                                                                                    living in perth, approved ndis providers in perth, share accommodation in perth, living and working in perth, exhibitions in perth, living in perth australia  \n",
       "39                                                                                                                                                                                                                                                                                                                     sil and sda, sda and sil, ndis sil and sda, sda and sil ndis, difference between sil and sda  \n",
       "40                                                                                                                                                                                                     disability home care perth, disability home care, disability home care services, ndis home care, ndis home care providers, independent home care, home care wa, perth home care services, home care perth wa  \n",
       "41                                                                                                                                                                long term accommodation perth, long term disability accommodation services, long term accommodation rockingham, perth long term accommodation, long term accommodation bunbury, long term accommodation services, long term accommodation near me  \n",
       "42                                                                                                                                                                                                                                                                                    sil provider, sda provider, ndis accommodation provider, it provider perth, ndis housing provider, it provider, ndia provider  \n",
       "43                                                                                                      atsa expo, perth expo, live your potential expo, abilities expo, living expo, ndis expo, developing australian communities expo, perth beauty expo, disability expo perth, connect expo, rockingham disability expo, ndis expo perth, perth disability expo, disability expo, expo perth, beauty expo perth  \n",
       "44                                                                                                                                independent living apartments for disabled, home for disabled, short term accommodation for disabled, independent living facilities for disabled, homes for disabled, independent living program for disabled, independent living for disabled, accommodation for disabled adults  \n",
       "45                                                                                           short stay accommodation perth, perth apartments short stay, short stay disability accommodation, short stay rentals perth, short stay apartments perth, perth short stay apartments, short stay perth, perth short stay accommodation, short stay apartments, apartments perth short stay, short stay apartment perth  \n",
       "46                                                                                                                                                                                                                                                                                                         sil accomodation, share accomodation perth, disability accomodation, ndis accomodation, sda accomodation  \n",
       "47                                                                                                                                                                                                                                                              ndis home and living, home and living ndis, home and living expo, home expo perth, perth home expo, ndis home and living supports, home living expo  \n",
       "48                                                                                                                                                                                                                                                independent living centre mandurah, independent living centre perth, independent living centre wa, independent living centre joondalup, independent living centre  \n",
       "49                                                                                                                                                                                                                                                                              sda aged care, sda care, independent care solution, ndis aged care, \"ndis aged care, personal care assistance, aged care residences  \n",
       "50                                                                                                                                                                                                                                                                                                    ndis housing solutions, sda solutions, improved living solutions, independant living solutions, sil solutions  \n",
       "51                                                                                                                                                                                                                                                                                                                    sda home, disability home, ndis home, home modifications perth, treeby nursing home, sil home  \n",
       "52                                                                                                                                                                                                                                                                                                             abilities expo perth 2021, adelphi 2021, abilities expo 2021, atsa 2021, home living exhibition 2021  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge cluster_name_df with clusters_queries so we can have them matching and double check the cluster names match with our queries\n",
    "clusters = pd.merge(cluster_name_df, clusters_queries, left_index=True, right_index=True)\n",
    "clusters"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11c752e1",
   "metadata": {},
   "source": [
    "## We only retain our Cluster names and numbers together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "060c7f54",
   "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>Cluster Name</th>\n",
       "      <th>Cluster Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>perth ndis living</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi freedom perth</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>independent living supported</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>near disability ndis</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Cluster Name  Cluster Number\n",
       "0             perth ndis living              -1\n",
       "1         adelphi freedom perth               0\n",
       "2    perth providers disability               1\n",
       "3  independent living supported               2\n",
       "4          near disability ndis               3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# splited_clusters = pd.concat([pd.Series(row['Cluster Name'], row['Top queries'].split(','))              \n",
    "#                     for _, row in clusters.iterrows()]).reset_index()\n",
    "cluster_name_nb = clusters[[\"Cluster Name\", \"Cluster Number\"]]\n",
    "cluster_name_nb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2520f853",
   "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>Cluster Number</th>\n",
       "      <th>Top queries</th>\n",
       "      <th>Cluster Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi living</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi living jobs</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>freedom adelphi</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi perth</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Cluster Number          Top queries           Cluster Name\n",
       "0               0       adelphi living  adelphi freedom perth\n",
       "1               0              adelphi  adelphi freedom perth\n",
       "2               0  adelphi living jobs  adelphi freedom perth\n",
       "3               0      freedom adelphi  adelphi freedom perth\n",
       "4               0        adelphi perth  adelphi freedom perth"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge it with our machine learning results\n",
    "clusters_queries = pd.merge(result, cluster_name_nb, how=\"inner\", on=\"Cluster Number\")\n",
    "clusters_queries.head() \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ffbbca52",
   "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>Top queries</th>\n",
       "      <th>Clicks</th>\n",
       "      <th>Impressions</th>\n",
       "      <th>CTR</th>\n",
       "      <th>Position</th>\n",
       "      <th>Cluster Number</th>\n",
       "      <th>Cluster Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>adelphi living</td>\n",
       "      <td>255</td>\n",
       "      <td>765</td>\n",
       "      <td>33.33%</td>\n",
       "      <td>1.06</td>\n",
       "      <td>0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi</td>\n",
       "      <td>15</td>\n",
       "      <td>314</td>\n",
       "      <td>4.78%</td>\n",
       "      <td>10.04</td>\n",
       "      <td>0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sil providers perth</td>\n",
       "      <td>11</td>\n",
       "      <td>98</td>\n",
       "      <td>11.22%</td>\n",
       "      <td>2.20</td>\n",
       "      <td>1</td>\n",
       "      <td>perth providers disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>supported independent living perth</td>\n",
       "      <td>8</td>\n",
       "      <td>327</td>\n",
       "      <td>2.45%</td>\n",
       "      <td>8.10</td>\n",
       "      <td>2</td>\n",
       "      <td>independent living supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>disability accommodation perth</td>\n",
       "      <td>4</td>\n",
       "      <td>645</td>\n",
       "      <td>0.62%</td>\n",
       "      <td>21.36</td>\n",
       "      <td>22</td>\n",
       "      <td>accommodation disability supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>790</th>\n",
       "      <td>disabled living manchester</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>134.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>disability insurance perth</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>141.00</td>\n",
       "      <td>33</td>\n",
       "      <td>disability perth insurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>housing for people with disabilities</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>196.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>disability support providers</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>199.00</td>\n",
       "      <td>20</td>\n",
       "      <td>disability support perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794</th>\n",
       "      <td>live in specialist care</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>276.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>795 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Top queries  Clicks  Impressions     CTR  \\\n",
       "0                          adelphi living     255          765  33.33%   \n",
       "1                                 adelphi      15          314   4.78%   \n",
       "2                     sil providers perth      11           98  11.22%   \n",
       "3      supported independent living perth       8          327   2.45%   \n",
       "4          disability accommodation perth       4          645   0.62%   \n",
       "..                                    ...     ...          ...     ...   \n",
       "790            disabled living manchester       0            1      0%   \n",
       "791            disability insurance perth       0            1      0%   \n",
       "792  housing for people with disabilities       0            1      0%   \n",
       "793          disability support providers       0            1      0%   \n",
       "794               live in specialist care       0            1      0%   \n",
       "\n",
       "     Position  Cluster Number                        Cluster Name  \n",
       "0        1.06               0               adelphi freedom perth  \n",
       "1       10.04               0               adelphi freedom perth  \n",
       "2        2.20               1          perth providers disability  \n",
       "3        8.10               2        independent living supported  \n",
       "4       21.36              22  accommodation disability supported  \n",
       "..        ...             ...                                 ...  \n",
       "790    134.00              -1                   perth ndis living  \n",
       "791    141.00              33          disability perth insurance  \n",
       "792    196.00              -1                   perth ndis living  \n",
       "793    199.00              20            disability support perth  \n",
       "794    276.00              -1                   perth ndis living  \n",
       "\n",
       "[795 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge it with the original dataset to get the matching query performances\n",
    "final_df = pd.merge(df, clusters_queries, how=\"inner\", on=\"Top queries\")\n",
    "final_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2b1696a",
   "metadata": {},
   "source": [
    "### We now have a complete dataframe with our queries, performances and corresponding cluster names. \n",
    "\n",
    "### It means we can now perform data analysis per cluster, which can be useful especially if you have a lot of queries\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8e3de50",
   "metadata": {},
   "source": [
    "## Export our dataframe to a Google Spreadsheet (previously created) so it can be read dynamically with Tableau and Google Data Studio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e1ab6b8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use of gspread to connect our Google Spreadsheet to our final dataframe\n",
    "scope = ['https://www.googleapis.com/auth/spreadsheets',\n",
    "        'https://www.googleapis.com/auth/drive']\n",
    "\n",
    "# Load credentials (not on the github / heroku folder, has to be replaced manually for safety purposes)\n",
    "creds = Credentials.from_service_account_file(\"default.json\", scopes=scope)\n",
    "client = gspread.authorize(creds)\n",
    "\n",
    "# Loading a Google Spreadsheet created before\n",
    "google_sh = client.open(\"keyword_clustering\")\n",
    "\n",
    "# Selecting the sheet\n",
    "sheet1 = google_sh.get_worksheet(0)\n",
    "\n",
    "# Clearing output\n",
    "sheet1.clear()\n",
    "\n",
    "# Send our pandas dataframe to the google spreadsheet (you will be able to load it from Tableau and Google Data Studio)\n",
    "df_google = set_with_dataframe(sheet1, final_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9e432bdf",
   "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>Top queries</th>\n",
       "      <th>Clicks</th>\n",
       "      <th>Impressions</th>\n",
       "      <th>CTR</th>\n",
       "      <th>Position</th>\n",
       "      <th>Cluster Number</th>\n",
       "      <th>Cluster Name</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "      <th>Unnamed: 18</th>\n",
       "      <th>Unnamed: 19</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Unnamed: 21</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>adelphi living</td>\n",
       "      <td>255.0</td>\n",
       "      <td>765.0</td>\n",
       "      <td>0.3333</td>\n",
       "      <td>1.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adelphi</td>\n",
       "      <td>15.0</td>\n",
       "      <td>314.0</td>\n",
       "      <td>0.0478</td>\n",
       "      <td>10.04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sil providers perth</td>\n",
       "      <td>11.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0.1122</td>\n",
       "      <td>2.20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>supported independent living perth</td>\n",
       "      <td>8.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>0.0245</td>\n",
       "      <td>8.10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>independent living supported</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>disability accommodation perth</td>\n",
       "      <td>4.0</td>\n",
       "      <td>645.0</td>\n",
       "      <td>0.0062</td>\n",
       "      <td>21.36</td>\n",
       "      <td>22.0</td>\n",
       "      <td>accommodation disability supported</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sda providers perth</td>\n",
       "      <td>4.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.1026</td>\n",
       "      <td>7.21</td>\n",
       "      <td>1.0</td>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ndis providers near me</td>\n",
       "      <td>3.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.0556</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>near disability ndis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>adelphi living mandurah</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.1667</td>\n",
       "      <td>1.06</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>perth ndis living</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>sil ndis</td>\n",
       "      <td>2.0</td>\n",
       "      <td>906.0</td>\n",
       "      <td>0.0022</td>\n",
       "      <td>37.77</td>\n",
       "      <td>4.0</td>\n",
       "      <td>sil disability ndia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ndis housing perth</td>\n",
       "      <td>2.0</td>\n",
       "      <td>454.0</td>\n",
       "      <td>0.0044</td>\n",
       "      <td>10.86</td>\n",
       "      <td>5.0</td>\n",
       "      <td>housing disability perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ndis providers perth</td>\n",
       "      <td>2.0</td>\n",
       "      <td>357.0</td>\n",
       "      <td>0.0056</td>\n",
       "      <td>48.13</td>\n",
       "      <td>1.0</td>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>disability supported accommodation perth</td>\n",
       "      <td>2.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0090</td>\n",
       "      <td>13.06</td>\n",
       "      <td>6.0</td>\n",
       "      <td>accommodation supported disability</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>sil accommodation perth</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.1538</td>\n",
       "      <td>4.38</td>\n",
       "      <td>7.0</td>\n",
       "      <td>accommodation perth accessible</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ndis</td>\n",
       "      <td>1.0</td>\n",
       "      <td>974.0</td>\n",
       "      <td>0.0010</td>\n",
       "      <td>3.13</td>\n",
       "      <td>8.0</td>\n",
       "      <td>living ndis perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>disability housing perth</td>\n",
       "      <td>1.0</td>\n",
       "      <td>498.0</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>24.32</td>\n",
       "      <td>5.0</td>\n",
       "      <td>housing disability perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>specialist disability accommodation perth</td>\n",
       "      <td>1.0</td>\n",
       "      <td>434.0</td>\n",
       "      <td>0.0023</td>\n",
       "      <td>8.61</td>\n",
       "      <td>9.0</td>\n",
       "      <td>accommodation disability specialist</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>sil</td>\n",
       "      <td>1.0</td>\n",
       "      <td>342.0</td>\n",
       "      <td>0.0029</td>\n",
       "      <td>5.02</td>\n",
       "      <td>4.0</td>\n",
       "      <td>sil disability ndia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ndis events perth</td>\n",
       "      <td>1.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>0.0038</td>\n",
       "      <td>15.02</td>\n",
       "      <td>8.0</td>\n",
       "      <td>living ndis perth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ndis short term accommodation</td>\n",
       "      <td>1.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>0.0038</td>\n",
       "      <td>29.24</td>\n",
       "      <td>10.0</td>\n",
       "      <td>term short accommodation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>disability service providers perth</td>\n",
       "      <td>1.0</td>\n",
       "      <td>223.0</td>\n",
       "      <td>0.0045</td>\n",
       "      <td>66.67</td>\n",
       "      <td>1.0</td>\n",
       "      <td>perth providers disability</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Top queries  Clicks  Impressions     CTR  \\\n",
       "0                              adelphi living   255.0        765.0  0.3333   \n",
       "1                                     adelphi    15.0        314.0  0.0478   \n",
       "2                         sil providers perth    11.0         98.0  0.1122   \n",
       "3          supported independent living perth     8.0        327.0  0.0245   \n",
       "4              disability accommodation perth     4.0        645.0  0.0062   \n",
       "5                         sda providers perth     4.0         39.0  0.1026   \n",
       "6                      ndis providers near me     3.0         54.0  0.0556   \n",
       "7                     adelphi living mandurah     3.0         18.0  0.1667   \n",
       "8                                    sil ndis     2.0        906.0  0.0022   \n",
       "9                          ndis housing perth     2.0        454.0  0.0044   \n",
       "10                       ndis providers perth     2.0        357.0  0.0056   \n",
       "11   disability supported accommodation perth     2.0        222.0  0.0090   \n",
       "12                    sil accommodation perth     2.0         13.0  0.1538   \n",
       "13                                       ndis     1.0        974.0  0.0010   \n",
       "14                   disability housing perth     1.0        498.0  0.0020   \n",
       "15  specialist disability accommodation perth     1.0        434.0  0.0023   \n",
       "16                                        sil     1.0        342.0  0.0029   \n",
       "17                          ndis events perth     1.0        264.0  0.0038   \n",
       "18              ndis short term accommodation     1.0        261.0  0.0038   \n",
       "19         disability service providers perth     1.0        223.0  0.0045   \n",
       "\n",
       "    Position  Cluster Number                         Cluster Name  Unnamed: 7  \\\n",
       "0       1.06             0.0                adelphi freedom perth         NaN   \n",
       "1      10.04             0.0                adelphi freedom perth         NaN   \n",
       "2       2.20             1.0           perth providers disability         NaN   \n",
       "3       8.10             2.0         independent living supported         NaN   \n",
       "4      21.36            22.0   accommodation disability supported         NaN   \n",
       "5       7.21             1.0           perth providers disability         NaN   \n",
       "6       1.00             3.0                 near disability ndis         NaN   \n",
       "7       1.06            -1.0                    perth ndis living         NaN   \n",
       "8      37.77             4.0                  sil disability ndia         NaN   \n",
       "9      10.86             5.0             housing disability perth         NaN   \n",
       "10     48.13             1.0           perth providers disability         NaN   \n",
       "11     13.06             6.0   accommodation supported disability         NaN   \n",
       "12      4.38             7.0       accommodation perth accessible         NaN   \n",
       "13      3.13             8.0                    living ndis perth         NaN   \n",
       "14     24.32             5.0             housing disability perth         NaN   \n",
       "15      8.61             9.0  accommodation disability specialist         NaN   \n",
       "16      5.02             4.0                  sil disability ndia         NaN   \n",
       "17     15.02             8.0                    living ndis perth         NaN   \n",
       "18     29.24            10.0             term short accommodation         NaN   \n",
       "19     66.67             1.0           perth providers disability         NaN   \n",
       "\n",
       "    Unnamed: 8  Unnamed: 9  ...  Unnamed: 16  Unnamed: 17  Unnamed: 18  \\\n",
       "0          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "1          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "2          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "3          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "4          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "5          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "6          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "7          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "8          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "9          NaN         NaN  ...          NaN          NaN          NaN   \n",
       "10         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "11         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "12         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "13         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "14         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "15         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "16         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "17         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "18         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "19         NaN         NaN  ...          NaN          NaN          NaN   \n",
       "\n",
       "    Unnamed: 19  Unnamed: 20  Unnamed: 21  Unnamed: 22  Unnamed: 23  \\\n",
       "0           NaN          NaN          NaN          NaN          NaN   \n",
       "1           NaN          NaN          NaN          NaN          NaN   \n",
       "2           NaN          NaN          NaN          NaN          NaN   \n",
       "3           NaN          NaN          NaN          NaN          NaN   \n",
       "4           NaN          NaN          NaN          NaN          NaN   \n",
       "5           NaN          NaN          NaN          NaN          NaN   \n",
       "6           NaN          NaN          NaN          NaN          NaN   \n",
       "7           NaN          NaN          NaN          NaN          NaN   \n",
       "8           NaN          NaN          NaN          NaN          NaN   \n",
       "9           NaN          NaN          NaN          NaN          NaN   \n",
       "10          NaN          NaN          NaN          NaN          NaN   \n",
       "11          NaN          NaN          NaN          NaN          NaN   \n",
       "12          NaN          NaN          NaN          NaN          NaN   \n",
       "13          NaN          NaN          NaN          NaN          NaN   \n",
       "14          NaN          NaN          NaN          NaN          NaN   \n",
       "15          NaN          NaN          NaN          NaN          NaN   \n",
       "16          NaN          NaN          NaN          NaN          NaN   \n",
       "17          NaN          NaN          NaN          NaN          NaN   \n",
       "18          NaN          NaN          NaN          NaN          NaN   \n",
       "19          NaN          NaN          NaN          NaN          NaN   \n",
       "\n",
       "    Unnamed: 24  Unnamed: 25  \n",
       "0           NaN          NaN  \n",
       "1           NaN          NaN  \n",
       "2           NaN          NaN  \n",
       "3           NaN          NaN  \n",
       "4           NaN          NaN  \n",
       "5           NaN          NaN  \n",
       "6           NaN          NaN  \n",
       "7           NaN          NaN  \n",
       "8           NaN          NaN  \n",
       "9           NaN          NaN  \n",
       "10          NaN          NaN  \n",
       "11          NaN          NaN  \n",
       "12          NaN          NaN  \n",
       "13          NaN          NaN  \n",
       "14          NaN          NaN  \n",
       "15          NaN          NaN  \n",
       "16          NaN          NaN  \n",
       "17          NaN          NaN  \n",
       "18          NaN          NaN  \n",
       "19          NaN          NaN  \n",
       "\n",
       "[20 rows x 26 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read our Google Spreadsheet to confirm the data has been correctly exported ('Unnamed' are the empty cells)\n",
    "df_google = get_as_dataframe(sheet1)\n",
    "df_google.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a89e78c3",
   "metadata": {},
   "source": [
    "## Export our final_df Dataframe to sqlite for further purposes (ex: load JSON URL data with Plotly or D3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "23e0d562",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL Alchemy\n",
    "import sqlalchemy\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy import create_engine, func"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e7229f46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:40,785 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-09-07 10:37:40,792 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,795 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-09-07 10:37:40,797 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "# Create connection\n",
    "engine = create_engine('sqlite:///keyword_clustering.sqlite', echo=True)\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a050dbba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:40,819 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\n",
      "2021-09-07 10:37:40,822 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,827 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,830 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,837 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,839 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,844 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,847 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,850 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,852 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,854 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,856 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,863 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,864 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,868 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:40,870 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,872 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,877 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,881 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:40,883 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,887 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,890 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "# Reflect an existing database into a new model\n",
    "Base = automap_base()\n",
    "Base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3ccdc5fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:40,921 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,923 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,928 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,931 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,934 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\n",
      "2021-09-07 10:37:40,936 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,939 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,941 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,947 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,949 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,952 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,953 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,955 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,957 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,959 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,961 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,964 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,967 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,970 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:40,972 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,977 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:40,978 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,981 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:40,983 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,985 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:40,986 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:40,988 INFO sqlalchemy.engine.base.Engine \n",
      "DROP TABLE keyword_clustering\n",
      "2021-09-07 10:37:40,989 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,000 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-09-07 10:37:41,004 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE keyword_clustering (\n",
      "\t\"index\" BIGINT, \n",
      "\t\"Top queries\" TEXT, \n",
      "\t\"Clicks\" BIGINT, \n",
      "\t\"Impressions\" BIGINT, \n",
      "\t\"CTR\" TEXT, \n",
      "\t\"Position\" FLOAT, \n",
      "\t\"Cluster Number\" BIGINT, \n",
      "\t\"Cluster Name\" TEXT\n",
      ")\n",
      "\n",
      "\n",
      "2021-09-07 10:37:41,005 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,014 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-09-07 10:37:41,016 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_keyword_clustering_index ON keyword_clustering (\"index\")\n",
      "2021-09-07 10:37:41,017 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,026 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-09-07 10:37:41,030 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-09-07 10:37:41,044 INFO sqlalchemy.engine.base.Engine INSERT INTO keyword_clustering (\"index\", \"Top queries\", \"Clicks\", \"Impressions\", \"CTR\", \"Position\", \"Cluster Number\", \"Cluster Name\") VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n",
      "2021-09-07 10:37:41,046 INFO sqlalchemy.engine.base.Engine ((0, 'adelphi living', 255, 765, '33.33%', 1.06, 0, 'adelphi freedom perth'), (1, 'adelphi', 15, 314, '4.78%', 10.04, 0, 'adelphi freedom perth'), (2, 'sil providers perth', 11, 98, '11.22%', 2.2, 1, 'perth providers disability'), (3, 'supported independent living perth', 8, 327, '2.45%', 8.1, 2, 'independent living supported'), (4, 'disability accommodation perth', 4, 645, '0.62%', 21.36, 22, 'accommodation disability supported'), (5, 'sda providers perth', 4, 39, '10.26%', 7.21, 1, 'perth providers disability'), (6, 'ndis providers near me', 3, 54, '5.56%', 1.0, 3, 'near disability ndis'), (7, 'adelphi living mandurah', 3, 18, '16.67%', 1.06, -1, 'perth ndis living')  ... displaying 10 of 795 total bound parameter sets ...  (793, 'disability support providers', 0, 1, '0%', 199.0, 20, 'disability support perth'), (794, 'live in specialist care', 0, 1, '0%', 276.0, -1, 'perth ndis living'))\n",
      "2021-09-07 10:37:41,058 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# Export our dataframe to SQL and replace the data if the table is already there\n",
    "final_df.to_sql(\"keyword_clustering\", conn, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "579da707",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:41,098 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\n",
      "2021-09-07 10:37:41,100 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,104 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"keyword_clustering\")\n",
      "2021-09-07 10:37:41,105 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,110 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:41,113 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,117 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:41,119 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,120 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:41,122 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,123 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:41,125 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,128 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:41,130 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,133 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:41,134 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,139 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"keyword_clustering\")\n",
      "2021-09-07 10:37:41,140 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,144 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"ix_keyword_clustering_index\")\n",
      "2021-09-07 10:37:41,145 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,149 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'keyword_clustering' AND type = 'table'\n",
      "2021-09-07 10:37:41,150 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reflect Database into ORM classes\n",
    "Base = automap_base()\n",
    "Base.prepare(engine, reflect=True)\n",
    "Base.classes.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c4c05a4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our session (link) from Python to the DB\n",
    "session = Session(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1adbbe00",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:41,179 INFO sqlalchemy.engine.base.Engine SELECT * FROM keyword_clustering LIMIT 5\n",
      "2021-09-07 10:37:41,180 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(0, 'adelphi living', 255, 765, '33.33%', 1.06, 0, 'adelphi freedom perth'),\n",
       " (1, 'adelphi', 15, 314, '4.78%', 10.04, 0, 'adelphi freedom perth'),\n",
       " (2, 'sil providers perth', 11, 98, '11.22%', 2.2, 1, 'perth providers disability'),\n",
       " (3, 'supported independent living perth', 8, 327, '2.45%', 8.1, 2, 'independent living supported'),\n",
       " (4, 'disability accommodation perth', 4, 645, '0.62%', 21.36, 22, 'accommodation disability supported')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use `engine.execute` to select and display the first 10 rows from the station table\n",
    "engine.execute('SELECT * FROM keyword_clustering LIMIT 5').fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1dedc598",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-09-07 10:37:41,196 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"SELECT * FROM keyword_clustering\")\n",
      "2021-09-07 10:37:41,197 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,199 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"SELECT * FROM keyword_clustering\")\n",
      "2021-09-07 10:37:41,201 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-09-07 10:37:41,202 INFO sqlalchemy.engine.base.Engine SELECT * FROM keyword_clustering\n",
      "2021-09-07 10:37:41,203 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "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>index</th>\n",
       "      <th>Top queries</th>\n",
       "      <th>Clicks</th>\n",
       "      <th>Impressions</th>\n",
       "      <th>CTR</th>\n",
       "      <th>Position</th>\n",
       "      <th>Cluster Number</th>\n",
       "      <th>Cluster Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>adelphi living</td>\n",
       "      <td>255</td>\n",
       "      <td>765</td>\n",
       "      <td>33.33%</td>\n",
       "      <td>1.06</td>\n",
       "      <td>0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>adelphi</td>\n",
       "      <td>15</td>\n",
       "      <td>314</td>\n",
       "      <td>4.78%</td>\n",
       "      <td>10.04</td>\n",
       "      <td>0</td>\n",
       "      <td>adelphi freedom perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>sil providers perth</td>\n",
       "      <td>11</td>\n",
       "      <td>98</td>\n",
       "      <td>11.22%</td>\n",
       "      <td>2.20</td>\n",
       "      <td>1</td>\n",
       "      <td>perth providers disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>supported independent living perth</td>\n",
       "      <td>8</td>\n",
       "      <td>327</td>\n",
       "      <td>2.45%</td>\n",
       "      <td>8.10</td>\n",
       "      <td>2</td>\n",
       "      <td>independent living supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>disability accommodation perth</td>\n",
       "      <td>4</td>\n",
       "      <td>645</td>\n",
       "      <td>0.62%</td>\n",
       "      <td>21.36</td>\n",
       "      <td>22</td>\n",
       "      <td>accommodation disability supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>790</th>\n",
       "      <td>790</td>\n",
       "      <td>disabled living manchester</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>134.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>791</th>\n",
       "      <td>791</td>\n",
       "      <td>disability insurance perth</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>141.00</td>\n",
       "      <td>33</td>\n",
       "      <td>disability perth insurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>792</td>\n",
       "      <td>housing for people with disabilities</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>196.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>793</td>\n",
       "      <td>disability support providers</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>199.00</td>\n",
       "      <td>20</td>\n",
       "      <td>disability support perth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794</th>\n",
       "      <td>794</td>\n",
       "      <td>live in specialist care</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0%</td>\n",
       "      <td>276.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>perth ndis living</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>795 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index                           Top queries  Clicks  Impressions     CTR  \\\n",
       "0        0                        adelphi living     255          765  33.33%   \n",
       "1        1                               adelphi      15          314   4.78%   \n",
       "2        2                   sil providers perth      11           98  11.22%   \n",
       "3        3    supported independent living perth       8          327   2.45%   \n",
       "4        4        disability accommodation perth       4          645   0.62%   \n",
       "..     ...                                   ...     ...          ...     ...   \n",
       "790    790            disabled living manchester       0            1      0%   \n",
       "791    791            disability insurance perth       0            1      0%   \n",
       "792    792  housing for people with disabilities       0            1      0%   \n",
       "793    793          disability support providers       0            1      0%   \n",
       "794    794               live in specialist care       0            1      0%   \n",
       "\n",
       "     Position  Cluster Number                        Cluster Name  \n",
       "0        1.06               0               adelphi freedom perth  \n",
       "1       10.04               0               adelphi freedom perth  \n",
       "2        2.20               1          perth providers disability  \n",
       "3        8.10               2        independent living supported  \n",
       "4       21.36              22  accommodation disability supported  \n",
       "..        ...             ...                                 ...  \n",
       "790    134.00              -1                   perth ndis living  \n",
       "791    141.00              33          disability perth insurance  \n",
       "792    196.00              -1                   perth ndis living  \n",
       "793    199.00              20            disability support perth  \n",
       "794    276.00              -1                   perth ndis living  \n",
       "\n",
       "[795 rows x 8 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query keyword_clustering in the the Database\n",
    "data = pd.read_sql(\"SELECT * FROM keyword_clustering\", conn)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e68c61c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Keyword Clustering.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}