SEJ_Semantic_Clustering_Tool_by_LeeFootSEO_n_fork_2.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "SEJ - Semantic Clustering Tool by @LeeFootSEO.ipynb",
      "private_outputs": true,
      "provenance": [],
      "collapsed_sections": [
        "VYacg07-m82j",
        "8yHMcc5FGZnx"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/noahlearner/autosuggest/blob/main/SEJ_Semantic_Clustering_Tool_by_LeeFootSEO_n_fork_2.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Semantic Keyword Cluster Tool V1\n",
        "by [LeeFootSEO](https://twitter.com/LeeFootSEO) February 2022\n",
        "\n",
        "**Website:** https://www.searchsolved.co.uk\n",
        "\n",
        "**Twitter:** https://twitter.com/LeeFootSEO"
      ],
      "metadata": {
        "id": "MXHRpn-EYrbv"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Welcome to Noah's fork of Lee Foot's Amazing Google Colab!!!\n",
        "\n",
        "Here's how to use it:\n",
        "\n",
        "\n",
        "1.   Edit the Config Details\n",
        "2.   Click on Runtime > Run All\n",
        "2.   The script will install required packages\n",
        "3.   As the script runs you'll watch each cell run from Top to Bottom.\n",
        "4.   When the script is done it will diwnload a csv onto your machine."
      ],
      "metadata": {
        "id": "ACAgbKwEFCPM"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Config"
      ],
      "metadata": {
        "id": "4lVyu0yg6lZa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@markdown ###CSV Output Name \n",
        "csv_name = \"plumber\" #@param {type:\"string\"} \n",
        "#@markdown ###Cluster accuracy  \n",
        "#@markdown 0-100 (100 = very tight clusters, but higher percentage of no_cluster groups)\n",
        "cluster_accuracy = 80  #@param {type:\"integer\"} \n",
        "#@markdown ###Minimum Cluster Size \n",
        "#@markdown set the minimum size of cluster groups. (Lower number = tighter groups)\n",
        "min_cluster_size = 2  #@param {type:\"integer\"} \n"
      ],
      "metadata": {
        "cellView": "form",
        "id": "BB5sNT0kyWR_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# UPLOAD your CSV\n",
        "\n",
        "*   Script expects a column called 'Keyword' and another called 'Volume'\n",
        "*   Recommend No More Than 50K Rows"
      ],
      "metadata": {
        "id": "GWR_3mfP2mNK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "from google.colab import files\n",
        "# upload the keyword export\n",
        "upload = files.upload()\n",
        "input_file = list(upload.keys())[0]  # get the name of the uploaded file"
      ],
      "metadata": {
        "id": "s9LF-cuyoPOy"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X4p40j7Xng5O"
      },
      "outputs": [],
      "source": [
        "#@title\n",
        "!pip install sentence_transformers\n",
        "!pip install pandas\n",
        "!pip install chardet\n",
        "!pip install detect_delimiter"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "import sys\n",
        "import time\n",
        "import sys\n",
        "import pandas as pd\n",
        "import chardet\n",
        "import codecs\n",
        "from detect_delimiter import detect\n",
        "from sentence_transformers import SentenceTransformer, util"
      ],
      "metadata": {
        "id": "uzalzYeXntsz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Choose a Sentence Transformer\n",
        "Download Pre-Trained Models: https://www.sbert.net/docs/pretrained_models.html"
      ],
      "metadata": {
        "id": "VYacg07-m82j"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "transformer = 'all-mpnet-base-v2'  # provides the best quality\n",
        "#transformer = 'all-MiniLM-L6-v2'  # 5 times faster and still offers good quality"
      ],
      "metadata": {
        "id": "tgnfzgnGnMWE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# automatically detect the character encoding type\n",
        "\n",
        "acceptable_confidence = .8\n",
        "\n",
        "contents = upload[input_file]\n",
        "\n",
        "codec_enc_mapping = {\n",
        "    codecs.BOM_UTF8: 'utf-8-sig',\n",
        "    codecs.BOM_UTF16: 'utf-16',\n",
        "    codecs.BOM_UTF16_BE: 'utf-16-be',\n",
        "    codecs.BOM_UTF16_LE: 'utf-16-le',\n",
        "    codecs.BOM_UTF32: 'utf-32',\n",
        "    codecs.BOM_UTF32_BE: 'utf-32-be',\n",
        "    codecs.BOM_UTF32_LE: 'utf-32-le',\n",
        "}\n",
        "\n",
        "encoding_type = 'utf-8'  # Default assumption\n",
        "is_unicode = False\n",
        "\n",
        "for bom, enc in codec_enc_mapping.items():\n",
        "    if contents.startswith(bom):\n",
        "        encoding_type = enc\n",
        "        is_unicode = True\n",
        "        break\n",
        "\n",
        "if not is_unicode:\n",
        "    # Didn't find BOM, so let's try to detect the encoding\n",
        "    guess = chardet.detect(contents)\n",
        "    if guess['confidence'] >= acceptable_confidence:\n",
        "        encoding_type = guess['encoding']\n",
        "\n",
        "print(\"Character Encoding Type Detected\", encoding_type)"
      ],
      "metadata": {
        "id": "irj1dFn42ayO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# automatically detect the delimiter\n",
        "with open(input_file,encoding=encoding_type) as myfile:\n",
        "    firstline = myfile.readline()\n",
        "myfile.close()\n",
        "delimiter_type = detect(firstline)"
      ],
      "metadata": {
        "id": "IoYTjbeCUfvs"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# create a dataframe using the detected delimiter and encoding type\n",
        "df = pd.read_csv((input_file), on_bad_lines='skip', encoding=encoding_type, delimiter=delimiter_type)\n",
        "count_rows = len(df)\n",
        "if count_rows > 50_000:\n",
        "  print(\"WARNING: You May Experience Crashes When Processing Over 50,000 Keywords at Once. Please consider smaller batches!\")\n",
        "print(\"Uploaded Keyword CSV File Successfully!\")\n",
        "df"
      ],
      "metadata": {
        "id": "5YPJV-9eoY5i"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# standardise the keyword columns\n",
        "df.rename(columns={\"Search term\": \"Keyword\", \"keyword\": \"Keyword\", \"query\": \"Keyword\", \"query\": \"Keyword\", \"Top queries\": \"Keyword\", \"queries\": \"Keyword\", \"Keywords\": \"Keyword\",\"keywords\": \"Keyword\", \"Search terms report\": \"Keyword\"}, inplace=True)\n",
        "\n",
        "if \"Keyword\" not in df.columns:\n",
        "  print(\"Error! Please make sure your csv file contains a column named 'Keyword!\")\n",
        "\n",
        "# store the data\n",
        "cluster_name_list = []\n",
        "corpus_sentences_list = []\n",
        "df_all = []\n",
        "\n",
        "corpus_set = set(df['Keyword'])\n",
        "corpus_set_all = corpus_set\n",
        "cluster = True\n"
      ],
      "metadata": {
        "id": "KhzhF3Rk_llo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Clustering Keywords - This can take a while!"
      ],
      "metadata": {
        "id": "8yHMcc5FGZnx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# keep looping through until no more clusters are created\n",
        "\n",
        "cluster_accuracy = cluster_accuracy / 100\n",
        "model = SentenceTransformer(transformer)\n",
        "\n",
        "while cluster:\n",
        "\n",
        "    corpus_sentences = list(corpus_set)\n",
        "    check_len = len(corpus_sentences)\n",
        "\n",
        "    corpus_embeddings = model.encode(corpus_sentences, batch_size=256, show_progress_bar=True, convert_to_tensor=True)\n",
        "    clusters = util.community_detection(corpus_embeddings, min_community_size=min_cluster_size, threshold=cluster_accuracy, init_max_size=len(corpus_embeddings))\n",
        "\n",
        "    for keyword, cluster in enumerate(clusters):\n",
        "        print(\"\\nCluster {}, #{} Elements \".format(keyword + 1, len(cluster)))\n",
        "\n",
        "        for sentence_id in cluster[0:]:\n",
        "            print(\"\\t\", corpus_sentences[sentence_id])\n",
        "            corpus_sentences_list.append(corpus_sentences[sentence_id])\n",
        "            cluster_name_list.append(\"Cluster {}, #{} Elements \".format(keyword + 1, len(cluster)))\n",
        "\n",
        "    df_new = pd.DataFrame(None)\n",
        "    df_new['Cluster Name'] = cluster_name_list\n",
        "    df_new[\"Keyword\"] = corpus_sentences_list\n",
        "\n",
        "\n",
        "    df_all.append(df_new)\n",
        "    have = set(df_new[\"Keyword\"])\n",
        "\n",
        "    corpus_set = corpus_set_all - have\n",
        "    remaining = len(corpus_set)\n",
        "    print(\"Total Unclustered Keywords: \", remaining)\n",
        "    if check_len == remaining:\n",
        "        break"
      ],
      "metadata": {
        "id": "ivDBFFPipZMk",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# make a new dataframe from the list of dataframe and merge back into the orginal df\n",
        "df_new = pd.concat(df_all)\n",
        "df = df.merge(df_new.drop_duplicates('Keyword'), how='left', on=\"Keyword\")"
      ],
      "metadata": {
        "id": "BbUja0QjqZok",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "\n",
        "# rename the clusters to the shortest keyword in the cluster\n",
        "df['Length'] = df['Keyword'].astype(str).map(len)\n",
        "df['Volume'] = df['Volume'].astype(int)\n",
        "df = df.sort_values(by=\"Volume\", ascending=False)\n",
        "\n",
        "df['Cluster Name'] = df.groupby('Cluster Name')['Keyword'].transform('first')\n",
        "df.sort_values(['Cluster Name', \"Keyword\"], ascending=[True, True], inplace=True)\n",
        "\n",
        "df['Cluster Name'] = df['Cluster Name'].fillna(\"zzz_no_cluster\")\n",
        "\n",
        "del df['Length']"
      ],
      "metadata": {
        "id": "NL2w0FH_qvEs",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "# move the cluster and keyword columns to the front\n",
        "col = df.pop(\"Keyword\")\n",
        "df.insert(0, col.name, col)\n",
        "\n",
        "col = df.pop('Cluster Name')\n",
        "df.insert(0, col.name, col)\n",
        "\n",
        "df.sort_values([\"Cluster Name\", \"Volume\"], ascending=[True, False], inplace=True)"
      ],
      "metadata": {
        "id": "VqIoJb-Nq2qL",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "uncluster_percent = (remaining / count_rows) * 100\n",
        "clustered_percent = 100 - uncluster_percent\n",
        "print(clustered_percent,\"% of rows clustered successfully!\")"
      ],
      "metadata": {
        "id": "6AluvS4eUuwG",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title\n",
        "df.to_csv(csv_name, index=False)\n",
        "files.download(csv_name + \".csv\")"
      ],
      "metadata": {
        "id": "dDZQNzMPonaa",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}