Steam Insider Pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9d1c2602",
   "metadata": {},
   "source": [
    "# Importing libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "38323474",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-08-19 22:00:11,585 loading file C:\\Users\\HuyTran\\.flair\\models\\sentiment-en-mix-distillbert_4.pt\n",
      "2021-08-19 22:00:14,959 loading file C:\\Users\\HuyTran\\.flair\\models\\sentiment-en-mix-ft-rnn.pt\n"
     ]
    }
   ],
   "source": [
    "import requests \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import download_steam_reviews\n",
    "\n",
    "import sqlite3\n",
    "\n",
    "import re\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "import spacy\n",
    "\n",
    "from nltk.corpus import stopwords\n",
    "\n",
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer, TfidfTransformer\n",
    "\n",
    "from sentence_transformers import SentenceTransformer\n",
    "embedder = SentenceTransformer('paraphrase-MiniLM-L6-v2')\n",
    "\n",
    "from sklearn.cluster import AgglomerativeClustering\n",
    "\n",
    "from fuzzysearch import find_near_matches\n",
    "\n",
    "from textblob import TextBlob\n",
    "from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer\n",
    "import flair\n",
    "\n",
    "sentiment_model = flair.models.TextClassifier.load('sentiment')\n",
    "sentiment_model_fast = flair.models.TextClassifier.load('sentiment-fast')\n",
    "senti_analyzer = SentimentIntensityAnalyzer()\n",
    "\n",
    "import pickle\n",
    "\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "from sklearn.cluster import KMeans\n",
    "import hdbscan\n",
    "from summa.summarizer import summarize\n",
    "\n",
    "from scipy.cluster.vq import vq\n",
    "\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.manifold import TSNE\n",
    "# import umap.umap_ as umap\n",
    "\n",
    "from tqdm import tqdm\n",
    "\n",
    "from collections import Counter\n",
    "\n",
    "from fuzzywuzzy import fuzz, process\n",
    "\n",
    "from spacy import displacy\n",
    "\n",
    "from pprint import pprint\n",
    "\n",
    "from sumy.parsers.plaintext import PlaintextParser\n",
    "from sumy.nlp.tokenizers import Tokenizer\n",
    "from sumy.summarizers.lex_rank import LexRankSummarizer\n",
    "from sumy.summarizers.text_rank import TextRankSummarizer\n",
    "from sumy.nlp.stemmers import Stemmer\n",
    "from sumy.utils import get_stop_words\n",
    "from sumy.summarizers.lsa import LsaSummarizer\n",
    "from sumy.summarizers.luhn import LuhnSummarizer\n",
    "from sumy.summarizers.sum_basic import SumBasicSummarizer\n",
    "\n",
    "import plotly.graph_objects as go\n",
    "from math import ceil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b83bfa7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "376ee976",
   "metadata": {},
   "source": [
    "# Reviews scraping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f11a1a99",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('./data/steam_reviews_new.db') \n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "b80fb766",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 428550 - Momodora: Reverie under the Moonlight\n",
    "# 367520 - Hollow Knight \n",
    "# 736260 - Baba Is You \n",
    "# 501300 - What Remains of Edith Finch\n",
    "# 504230 - Celeste\n",
    "# 22000 - World of Goo\n",
    "# 40700 - Machinarium\n",
    "# 26800 - Braid\n",
    "# 1222700 - A Way Out\n",
    "# 1225570 - Unravel Two\n",
    "app_ids = [428550, 367520, 736260, 501300, 504230, 22000, 40700, 26800, 1222700, 1225570]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0f35a569",
   "metadata": {},
   "outputs": [],
   "source": [
    "### get game_name ###\n",
    "\n",
    "def get_app_list(): \n",
    "    app_list_url = 'https://api.steampowered.com/ISteamApps/GetAppList/v2/'\n",
    "    resp_data = requests.get(app_list_url)\n",
    "    return resp_data.json()\n",
    "\n",
    "def get_name(app_id, app_list): \n",
    "    for app in app_list['applist']['apps']: \n",
    "        if app['appid'] == app_id: \n",
    "            return app['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "78465edf",
   "metadata": {},
   "outputs": [],
   "source": [
    "### get header_img_url  \n",
    "\n",
    "def get_header_img_url(app_id): \n",
    "    return f'https://cdn.cloudflare.steamstatic.com/steam/apps/{app_id}/header.jpg'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0442bd36",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_text_length(text): \n",
    "    return len(set(text.split(' ')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "f6d7fbf3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[appID = 1145360] expected #reviews = 85063\n",
      "502 Bad Gateway for appID = 1145360 and cursor = AoJ4i+rv0/oCeuSj7QI=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 1145360 and cursor = AoJwhPXv/e4Cfpvt4wE=. Cooldown: 10 seconds\n",
      "[appID = 275850] expected #reviews = 126533\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4zYu8+voCca/C8AI=. Cooldown: 10 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4k+2IsvYCdfuougI=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJw38ibgfYCe4bAsQI=. Cooldown: 10 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ41pXYo/MCe+z9lAI=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJwmrjRr/ACdcut8wE=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4oIC1yOwCdfer0AE=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4uobn/OQCfK61pwE=. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4+Ziv4dYCdsXxXw==. Cooldown: 10 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJwvuqCy9YCfOudXw==. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJw543mxdYCdeeJXw==. Cooldown: 10 seconds\n",
      "Number of queries 150 reached. Cooldown: 310 seconds\n",
      "502 Bad Gateway for appID = 275850 and cursor = AoJ4zLORwNYCfLPnXg==. Cooldown: 10 seconds\n"
     ]
    }
   ],
   "source": [
    "### get total_positive, total_negative, total_reviews in Crawler for table games \n",
    "### get game_id, review, recommended, time in Crawler for table games \n",
    "\n",
    "app_list = get_app_list()\n",
    "\n",
    "request_params = {\n",
    "    'language': 'english'\n",
    "}\n",
    "\n",
    "# load or download new (maximum ~5000 newest reviews)\n",
    "load_mode = False\n",
    "\n",
    "for app_id in app_ids: \n",
    "    game_tuples = [] \n",
    "\n",
    "    game_name = get_name(app_id, app_list)\n",
    "    \n",
    "    header_img_url = get_header_img_url(app_id)\n",
    "\n",
    "    \n",
    "    if load_mode: \n",
    "        review_dict = download_steam_reviews.load_review_dict(app_id)['reviews'].values()\n",
    "    else: \n",
    "        review_dict = download_steam_reviews.download_reviews_for_app_id(app_id, \n",
    "                                                                     chosen_request_params=request_params)[0]['reviews'].values()\n",
    "    \n",
    "    with conn:\n",
    "        cursor.execute(\"\"\"INSERT INTO games (app_id, game_name, header_img_url) VALUES (?, ?, ?);\"\"\", \n",
    "                       (app_id, game_name, header_img_url))\n",
    "    \n",
    "    # get game_id (fk) for table reviews\n",
    "    game_id = cursor.execute(\"\"\"SELECT game_id FROM games WHERE app_id=?;\"\"\", (app_id,)).fetchone()[0] \n",
    "    \n",
    "    review_tuples = []\n",
    "    \n",
    "    for review_dict_value in review_dict:     \n",
    "        review = review_dict_value['review']\n",
    "        recommended = 1 if review_dict_value['voted_up'] else 0\n",
    "        time = review_dict_value['timestamp_updated']\n",
    "        \n",
    "        review_tuples.append((review, recommended, time, game_id))\n",
    "        \n",
    "        \n",
    "#         if get_text_length(review) < 3:\n",
    "#             print(f'> {review}')\n",
    "            \n",
    "    \n",
    "    with conn:\n",
    "        cursor.executemany(\"\"\"INSERT INTO reviews (review, recommended, time, game_id) VALUES \n",
    "                                (?, ?, ?, ?);\"\"\", review_tuples)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fe98b10",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e4f39413",
   "metadata": {},
   "source": [
    "# Reviews preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "ada075ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews = pd.read_sql_query(\"\"\"SELECT review_id, review, game_id \n",
    "                    FROM reviews JOIN games USING(game_id);\"\"\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "b2548b65",
   "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>review_id</th>\n",
       "      <th>review</th>\n",
       "      <th>game_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Metroidvania with some influences from Dark So...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>It's ok. Frustrating mechanics turned me off o...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Cat</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Has problems but overall pretty good. I'd sugg...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>This is a very good and very short game. If yo...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277499</th>\n",
       "      <td>277500</td>\n",
       "      <td>Quite nice, actually.</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277500</th>\n",
       "      <td>277501</td>\n",
       "      <td>Rest in peace Harambe. Your name will always b...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277501</th>\n",
       "      <td>277502</td>\n",
       "      <td>1ST PUBLIC REVIEW!\\n\\nEDIT: Quick tips, left c...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277502</th>\n",
       "      <td>277503</td>\n",
       "      <td>Its pretty good tbh\\nEDIT: Its pretty good but...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277503</th>\n",
       "      <td>277504</td>\n",
       "      <td>yeah m8, this is gr8, no d-b8 i r8 it an 8 i h...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>277504 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        review_id                                             review  game_id\n",
       "0               1  Metroidvania with some influences from Dark So...        1\n",
       "1               2  It's ok. Frustrating mechanics turned me off o...        1\n",
       "2               3                                                Cat        1\n",
       "3               4  Has problems but overall pretty good. I'd sugg...        1\n",
       "4               5  This is a very good and very short game. If yo...        1\n",
       "...           ...                                                ...      ...\n",
       "277499     277500                              Quite nice, actually.       12\n",
       "277500     277501  Rest in peace Harambe. Your name will always b...       12\n",
       "277501     277502  1ST PUBLIC REVIEW!\\n\\nEDIT: Quick tips, left c...       12\n",
       "277502     277503  Its pretty good tbh\\nEDIT: Its pretty good but...       12\n",
       "277503     277504  yeah m8, this is gr8, no d-b8 i r8 it an 8 i h...       12\n",
       "\n",
       "[277504 rows x 3 columns]"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "de03b913",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_missing_punct(text): \n",
    "    return re.sub('([A-Za-z0-9])\\s*(\\n+|$)', '\\g<1>. ', text)\n",
    "\n",
    "\n",
    "def replace_bullets(text): \n",
    "    text = re.sub('([A-Za-z0-9])\\s*\\n+\\s*[+-]?\\s*', '\\g<1>. ', text)\n",
    "    text = re.sub('\\s*([:+-]+)\\s*\\n+\\s*[+-]?\\s*', '. ', text) \n",
    "    return text\n",
    "    \n",
    "def replace_colons(text): \n",
    "    return re.sub('\\s*:\\s*(\\n+)\\s*', '.\\g<1>', text) \n",
    "    \n",
    "# remove url from text\n",
    "def remove_url(text):\n",
    "    return re.sub(r\"http\\S+\", ' ', text)\n",
    "\n",
    "\n",
    "def remove_square_brackets(text): \n",
    "    return re.sub('\\[(.*?)\\]', ' ', text)\n",
    "\n",
    "# remove HTML tags\n",
    "def remove_html_tags(text):\n",
    "    soup = BeautifulSoup(text, \"lxml\")\n",
    "    text = soup.get_text()  \n",
    "    return text\n",
    "\n",
    "\n",
    "# replace ’ with ' \n",
    "def normalize_single_quote(text):\n",
    "    return re.sub('[’‘]', '\\'', text)\n",
    "\n",
    "\n",
    "# remove non english characters effectively\n",
    "def remove_non_ascii(text): \n",
    "    return text.encode(\"ascii\", errors=\"ignore\").decode()\n",
    "    \n",
    "    \n",
    "# remove ANSI escape sequences\n",
    "def remove_ansi_escape_sequences(text):\n",
    "    ansi_escape = re.compile(r'(?:\\x1B[@-_]|[\\x80-\\x9F])[0-?]*[ -/]*[@-~]')\n",
    "    return ansi_escape.sub('', text)\n",
    "    \n",
    "    \n",
    "# remove multiple whitespaces with single whitespace\n",
    "def remove_multi_whitespaces(text): \n",
    "    return re.sub('\\s+', ' ', text.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "0510427c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_bullet_nums(sent): \n",
    "    return re.sub('^\\s*\\d+\\s*[.\\)]+\\s*([^\\d])', '\\g<1>', sent)\n",
    "\n",
    "def remove_leading_symbols(sent):\n",
    "    return re.sub('^[^A-Za-z\\\"\\'\\d]+', '', sent)\n",
    "\n",
    "def uppercase_first(sent): \n",
    "    return sent[0].upper() + sent[1:] if len(sent) != 0 else sent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "ddb6cfec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ['transformer', 'parser']\n",
    "# ['tok2vec', 'parser']\n",
    "# ['sentencizer']\n",
    "\n",
    "nlp = spacy.load(\"en_core_web_lg\")\n",
    "nlp.add_pipe(\"sentencizer\")\n",
    "\n",
    "def tokenize_sent(text, pipes=['tok2vec', 'parser']):\n",
    "    with nlp.select_pipes(enable=pipes):\n",
    "        doc = nlp(text)\n",
    "        sents = [str(sent).strip() for sent in doc.sents]\n",
    "    return sents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "4fb5fad0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lowercase(text):\n",
    "    return text.lower()\n",
    "\n",
    "def expand_contractions(text):\n",
    "    for key in contractions:\n",
    "        value = contractions[key]\n",
    "        text = text.replace(key, value)\n",
    "    return text\n",
    "\n",
    "# remove digits \n",
    "def remove_digits(text): \n",
    "    return re.sub('\\d+', ' ', text)\n",
    "\n",
    "# remove symbols \n",
    "def remove_symbols(text):\n",
    "    return re.sub('[^A-Za-z,.\\s\\d]+', ' ', text)\n",
    "\n",
    "# lemmatization with spacy \n",
    "def lemmatize_text(text): \n",
    "    doc = nlp(text, disable=['parser','ner'])\n",
    "    lemma = [token.lemma_ for token in doc if token.pos_ != 'PUNCT']\n",
    "    return ' '.join(lemma)\n",
    "\n",
    "# remove stop words \n",
    "def remove_stopwords(text, word_list=[]):\n",
    "    stop_words = stopwords.words(\"english\")\n",
    "    stop_words.extend(word_list)\n",
    "    stop_words = set(stop_words)\n",
    "    return ' '.join(e.lower() for e in text.split() if e.lower() not in stop_words)\n",
    "\n",
    "def get_extra_stopwords(game_name): \n",
    "    stopwords = set(['game', 'lot', 'bit', 'way '])\n",
    "    doc = nlp(game_name, disable=['parser', 'ner'])\n",
    "    for token in doc: \n",
    "        if token.pos_ not in {'PUNCT', 'NUM'}:\n",
    "            stopwords.add(token.text.lower())\n",
    "    return stopwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "7cbd13ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\envs\\steam_insider\\lib\\site-packages\\bs4\\__init__.py:336: MarkupResemblesLocatorWarning:\n",
      "\n",
      "\"...  \" looks like a filename, not markup. You should probably open this file and pass the filehandle into Beautiful Soup.\n",
      "\n",
      "D:\\Anaconda3\\envs\\steam_insider\\lib\\site-packages\\ipykernel_launcher.py:20: DeprecationWarning:\n",
      "\n",
      "The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sent_tuples = set()\n",
    "\n",
    "# filter out short reviews (len < 3)\n",
    "df_reviews = df_reviews[df_reviews.apply(lambda x: get_text_length(x['review']) > 2, axis=1)]\n",
    "\n",
    "for game_id in df_reviews['game_id'].unique():\n",
    "    df_reviews_game = df_reviews[df_reviews['game_id'] == game_id]\n",
    "    \n",
    "    reviews_game_cleaned = df_reviews_game['review'].map(remove_square_brackets)\\\n",
    "                        .map(remove_html_tags)\\\n",
    "                        .map(remove_url)\\\n",
    "                        .map(normalize_single_quote)\\\n",
    "                        .map(remove_non_ascii)\\\n",
    "                        .map(remove_ansi_escape_sequences)\\\n",
    "                        .map(add_missing_punct)\\\n",
    "                        .map(replace_colons)\n",
    "    \n",
    "    for review_id, review in zip(df_reviews_game['review_id'], reviews_game_cleaned):\n",
    "        sents = pd.Series(tokenize_sent(review)).map(remove_bullet_nums)\\\n",
    "                                                .map(remove_leading_symbols)\\\n",
    "                                                .map(uppercase_first)\\\n",
    "                                                .map(add_missing_punct)\\\n",
    "                                                .map(remove_multi_whitespaces)\\\n",
    "        \n",
    "        # filter out short sentences (len < 3) and sentences with uneven number of \", ), ( \n",
    "        for sent in sents:\n",
    "            if get_text_length(sent) > 2 and sent.count('\"') % 2 == 0 and sent.count('(') == sent.count(')'):\n",
    "                sent_tuples.add((review_id, sent))\n",
    "                \n",
    "sent_tuples = list(sent_tuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "32e14c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "with conn:\n",
    "    cursor.executemany(\"\"\"INSERT INTO sents (review_id, sent) VALUES \n",
    "                        (?, ?);\"\"\", sent_tuples)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7220a92a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "62ec89bc",
   "metadata": {},
   "source": [
    "# Aspect extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "16dee0ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sents_ = pd.read_sql_query(\"\"\"SELECT sent, sent_id, game_id\n",
    "                    FROM sents JOIN reviews USING(review_id) JOIN games USING(game_id);\"\"\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "id": "d44adb68",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_tokens_index_dict(string):\n",
    "    index_dict = {}\n",
    "    index = 0 \n",
    "    for i, char in enumerate(string): \n",
    "        if char == ' ':\n",
    "            index += 1\n",
    "        else: \n",
    "            index_dict[i] = index\n",
    "    \n",
    "    return index_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "id": "880d4c3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stopwords(word_list=[]):\n",
    "    stopwords = nlp.Defaults.stop_words.copy()  \n",
    "    stopwords |= set(word_list)\n",
    "    return stopwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "48e21007",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_pos_tags(tokens): \n",
    "    pos_tags = []\n",
    "    \n",
    "    for token in tokens: \n",
    "        if token.text == ',':\n",
    "            pos_tags.append('COMMA')\n",
    "        elif token.text == '-': \n",
    "            pos_tags.append('HYPHEN')\n",
    "        elif token.text.lower() in stop_words: \n",
    "            pos_tags.append('STOP')\n",
    "        elif token.pos_ in ('NOUN', 'PROPN'):\n",
    "            pos_tags.append('NOUN')\n",
    "        else: \n",
    "            pos_tags.append(token.pos_)\n",
    "    \n",
    "    return pos_tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "cf0398cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "nlp = spacy.load(\"en_core_web_trf\")\n",
    "nlp.add_pipe(\"sentencizer\")\n",
    "\n",
    "def get_kws_sent_ids(df_sents):\n",
    "    kws_sent_ids = set()\n",
    "\n",
    "    noun_types = set(['NOUN', 'PROPN'])\n",
    "    adj_types = set(['ADJ'])\n",
    "    pattern = re.compile('(ADJ (COMMA ADJ )*)*(NOUN (HYPHEN NOUN )*(HYPHEN )?)*NOUN')\n",
    "\n",
    "    for sent, sent_id in zip(df_sents['sent'], df_sents['sent_id']): \n",
    "        with nlp.select_pipes(enable=['transformer', 'tagger', 'attribute_ruler']):\n",
    "            doc = nlp(sent)\n",
    "\n",
    "        tokens = [token.text for token in doc]\n",
    "        tags_str = ' '.join(get_pos_tags([token for token in doc]))\n",
    "        tokens_index_dict = get_tokens_index_dict(tags_str)\n",
    "        matches = pattern.finditer(tags_str)\n",
    "        \n",
    "        for match in matches: \n",
    "            tags = [elem for elem in match.group(0).split()]\n",
    "            \n",
    "            index = match.start()\n",
    "            \n",
    "            kw = []\n",
    "            \n",
    "            for i, tag in enumerate(tags): \n",
    "                kw_token = tokens[tokens_index_dict[index]]\n",
    "                \n",
    "                index += len(tag) + 1\n",
    "                \n",
    "                if tag in ('NOUN', 'ADJ'):\n",
    "                    kw.append(kw_token.lower())\n",
    "                \n",
    "            kw = ' '.join(kw)\n",
    "            \n",
    "            if len(kw) > 1: \n",
    "                kws_sent_ids.add((kw, sent_id))\n",
    "            \n",
    "        if len(kws_sent_ids) % 1000 == 0:\n",
    "            print(f'Processed {len(kws_sent_ids)}')\n",
    "    \n",
    "    return kws_sent_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "56d23685",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_kws_sent_ids_group(kws_sent_ids):\n",
    "    res={}\n",
    "    \n",
    "    for kw_sent_id in kws_sent_ids:\n",
    "        if kw_sent_id[0] not in res:\n",
    "            res[kw_sent_id[0]] = [kw_sent_id[1]]\n",
    "        else:\n",
    "            res[kw_sent_id[0]].append(kw_sent_id[1])\n",
    "            \n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "8c35995d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the list of biggest clusters \n",
    "def get_biggest_clusters(kw_clusters, top_n=50):\n",
    "    cluster_nums_sizes = []\n",
    "\n",
    "    for cluster_num, kws_sent_ids in kw_clusters.items():\n",
    "        if cluster_num != -1:\n",
    "            cluster_size = sum([len(kw_sent_ids[1]) for kw_sent_ids in kws_sent_ids])\n",
    "            cluster_nums_sizes.append((cluster_num, cluster_size))\n",
    "\n",
    "    cluster_nums_sizes_sorted = sorted(cluster_nums_sizes, key=lambda x: x[1], reverse=True)\n",
    "    return set([cluster_num_size[0] for cluster_num_size in cluster_nums_sizes_sorted[:top_n]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "c14c1779",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the current auto-increment id of a primary key of a table\n",
    "def get_current_auto_pk(table_name):\n",
    "    with conn:\n",
    "        cursor.execute(f\"SELECT seq FROM sqlite_sequence WHERE name='{table_name}'\")\n",
    "    pk_id = cursor.fetchone() \n",
    "    return pk_id[0] + 1 if pk_id else 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "id": "024ed15a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get tuples for 4 tables clusters, kws, kws_sents and clusters_sents\n",
    "def get_cluster_kw_tuples(game_id, kw_clusters, biggest_clusters):\n",
    "    cluster_tuples, kw_tuples, cluster_sent_tuples, kw_sent_tuples = [], [], [], []\n",
    "    \n",
    "    # get cluster_id and kw_id\n",
    "    cluster_id = get_current_auto_pk('clusters')\n",
    "    kw_id = get_current_auto_pk('kws')\n",
    "    \n",
    "    for cluster_num, kws_sent_ids in kw_clusters.items():         \n",
    "        if cluster_num in biggest_clusters:\n",
    "            kws_sent_ids_sorted = sorted(kws_sent_ids, key=lambda x: len(x[1]), reverse=True)\n",
    "            cluster_name = kws_sent_ids_sorted[0][0]\n",
    "            \n",
    "            # clusters\n",
    "            cluster_tuples.append((cluster_name,))            \n",
    "\n",
    "            for kw_sent_ids in kws_sent_ids: \n",
    "                kw = kw_sent_ids[0]\n",
    "                sent_ids = kw_sent_ids[1]\n",
    "                \n",
    "                # kws\n",
    "                kw_tuples.append((kw, cluster_id))\n",
    "                \n",
    "                for sent_id in sent_ids: \n",
    "                    kw_sent_tuples.append((sent_id, kw_id))\n",
    "                    \n",
    "                kw_id += 1\n",
    "                \n",
    "            cluster_id += 1\n",
    "    \n",
    "    return cluster_tuples, kw_tuples, kw_sent_tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "dbf13525",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cluster_kws(kws): \n",
    "    kw_embeddings = embedder.encode(kws)\n",
    "\n",
    "    # Normalize the embeddings to unit length\n",
    "    kw_embeddings = kw_embeddings / np.linalg.norm(kw_embeddings, axis=1, keepdims=True)\n",
    "\n",
    "    # perform agglomerative clustering\n",
    "    clustering_model = AgglomerativeClustering(n_clusters=None, affinity='cosine', linkage='average', distance_threshold=0.6)\n",
    "    clustering_model.fit(kw_embeddings)\n",
    "    cluster_assignment = clustering_model.labels_\n",
    "\n",
    "    kw_clusters = {}\n",
    "    for kw_id, cluster_id in enumerate(cluster_assignment):\n",
    "        if cluster_id not in kw_clusters:\n",
    "            kw_clusters[cluster_id] = []\n",
    "\n",
    "        kw = kws[kw_id]\n",
    "        kw_clusters[cluster_id].append((kw, kws_sent_ids_group[kw]))\n",
    "    \n",
    "    return kw_clusters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "88e3541f",
   "metadata": {},
   "outputs": [],
   "source": [
    "game_ids = df_sents_['game_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "84bd6e68",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processed 2000\n",
      "Processed 2000\n"
     ]
    }
   ],
   "source": [
    "for game_id in game_ids: \n",
    "    df_sents = df_sents_[df_sents_['game_id'] == game_id][['sent', 'sent_id']][:1000]\n",
    "\n",
    "    # remove stopwords before grouping\n",
    "    stop_words = get_stopwords(['game', 'games', 'lot', 'lots', 'ton', 'tons', 'bit', 'bits', 'fun', \n",
    "                                'way', 'ways', 'thing', 'things', 'time', 'times', 'type', 'types', \n",
    "                                   'opinion', 'opinions', 'sense', 'terms', 'lack', 'fact'])\n",
    "\n",
    "    kws_sent_ids = get_kws_sent_ids(df_sents)\n",
    "    kws_sent_ids_group = get_kws_sent_ids_group(kws_sent_ids)\n",
    "    kws_sent_ids_group = {key: kws_sent_ids_group[key] for key, val in kws_sent_ids_group.items() if len(val) > 1}\n",
    "\n",
    "    kws = list(kws_sent_ids_group.keys())\n",
    "\n",
    "    kw_clusters = cluster_kws(kws)\n",
    "\n",
    "    biggest_clusters = get_biggest_clusters(kw_clusters, 100)\n",
    "    cluster_tuples, kw_tuples, kw_sent_tuples = get_cluster_kw_tuples(game_id, kw_clusters, biggest_clusters)\n",
    "    \n",
    "    with conn:\n",
    "        cursor.executemany(\"\"\"INSERT INTO clusters (cluster_name) VALUES \n",
    "                            (?);\"\"\", cluster_tuples)    \n",
    "    \n",
    "    with conn:\n",
    "        cursor.executemany(\"\"\"INSERT INTO kws (kw, cluster_id) VALUES \n",
    "                            (?, ?);\"\"\", kw_tuples)\n",
    "\n",
    "    with conn:\n",
    "        cursor.executemany(\"\"\"INSERT INTO kws_sents (sent_id, kw_id) VALUES \n",
    "                            (?, ?);\"\"\", kw_sent_tuples)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97c5831a",
   "metadata": {},
   "source": [
    "# Sentiment analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "c1eb4385",
   "metadata": {},
   "outputs": [],
   "source": [
    "with conn: \n",
    "    cursor.execute(\"\"\"\n",
    "                        DELETE FROM sents \n",
    "                        WHERE sent_id NOT IN (\n",
    "                        SELECT DISTINCT sent_id\n",
    "                        FROM kws_sents);\n",
    "                   \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "0bc9151c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sents = pd.read_sql_query(\"\"\"SELECT game_id, sent, sent_id \n",
    "                                FROM reviews JOIN sents USING(review_id);\"\"\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "eda69415",
   "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>game_id</th>\n",
       "      <th>sent</th>\n",
       "      <th>sent_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Music is brilliant.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Don't buy it on sale because the developer des...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Calvin Finch died from falling off a cliff fro...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>It's simple and puzzle solutions don't make yo...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Short version: Great story, great difficulty, ...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136105</th>\n",
       "      <td>7</td>\n",
       "      <td>Short (4 - 8 hours)</td>\n",
       "      <td>197219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136106</th>\n",
       "      <td>9</td>\n",
       "      <td>Fun to figure out puzzles with a friend.</td>\n",
       "      <td>197220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136107</th>\n",
       "      <td>9</td>\n",
       "      <td>The story was overall alright, at times predic...</td>\n",
       "      <td>197221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136108</th>\n",
       "      <td>4</td>\n",
       "      <td>Each room was preserved after the death of the...</td>\n",
       "      <td>197223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136109</th>\n",
       "      <td>7</td>\n",
       "      <td>A gorgeously hand-drawn point&amp;click adventure ...</td>\n",
       "      <td>197224</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>136110 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        game_id                                               sent  sent_id\n",
       "0             5                                Music is brilliant.        1\n",
       "1             5  Don't buy it on sale because the developer des...        5\n",
       "2             4  Calvin Finch died from falling off a cliff fro...        6\n",
       "3             7  It's simple and puzzle solutions don't make yo...        7\n",
       "4             5  Short version: Great story, great difficulty, ...        9\n",
       "...         ...                                                ...      ...\n",
       "136105        7                                Short (4 - 8 hours)   197219\n",
       "136106        9           Fun to figure out puzzles with a friend.   197220\n",
       "136107        9  The story was overall alright, at times predic...   197221\n",
       "136108        4  Each room was preserved after the death of the...   197223\n",
       "136109        7  A gorgeously hand-drawn point&click adventure ...   197224\n",
       "\n",
       "[136110 rows x 3 columns]"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "id": "88f18359",
   "metadata": {},
   "outputs": [],
   "source": [
    "def decode_sent_embeddings(sent_embeddings):\n",
    "    return np.array([pickle.loads(sent_embedding) for sent_embedding in sent_embeddings])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "8dc36d5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_score_flair(sent, threshold=0.9): \n",
    "    sent_flair = flair.data.Sentence(sent)\n",
    "    sentiment_model_fast.predict(sent_flair)\n",
    "    \n",
    "    value_flair = sent_flair.labels[0].value\n",
    "    score_flair = sent_flair.labels[0].score\n",
    "    \n",
    "    value_flair = 1 if value_flair == 'POSITIVE' else -1\n",
    "    \n",
    "    score_flair = value_flair*score_flair\n",
    "    \n",
    "    return 1 if score_flair > threshold else -1 if score_flair < -threshold else 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "e72f9ed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "sentis = df_sents['sent'].map(get_score_flair) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "8df834bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sent_embeddings = embedder.encode(df_sents['sent'])\n",
    "\n",
    "sent_embeddings = sent_embeddings / np.linalg.norm(sent_embeddings, axis=1, keepdims=True)\n",
    "\n",
    "sent_embeddings_str = [pickle.dumps(sent_embedding) for sent_embedding in sent_embeddings]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "09f17bec",
   "metadata": {},
   "outputs": [],
   "source": [
    "sent_tuples = []\n",
    "\n",
    "for senti, sent_embedding_str, sent_id in zip(sentis, sent_embeddings_str, df_sents['sent_id']): \n",
    "    sent_tuples.append((senti, sent_embedding_str, sent_id))     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "id": "8d34a4f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "with conn:\n",
    "    cursor.executemany(\"\"\"UPDATE sents SET (senti, sent_embedding) = (?, ?)\n",
    "                        WHERE sent_id=?;\"\"\", sent_tuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15adddef",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "39eef2d5",
   "metadata": {},
   "source": [
    "# Text summarization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "id": "ae4d967b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sents = pd.read_sql_query(\"\"\"SELECT game_id, cluster_id, cluster_name, kw_id, kw, sent_id, sent, sent_embedding, senti\n",
    "                                FROM reviews\n",
    "                                JOIN sents USING(review_id) \n",
    "                                JOIN kws_sents USING(sent_id)\n",
    "                                JOIN kws USING(kw_id)\n",
    "                                JOIN clusters USING(cluster_id);\"\"\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b30fb29",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_centroid(arr):\n",
    "    length, dim = arr.shape\n",
    "    return np.array([np.sum(arr[:, i])/length for i in range(dim)])\n",
    "\n",
    "\n",
    "# return index of the vectors in corpus_embeddings nearest to the centroid\n",
    "def get_nearest_indexes(centroids, corpus_embeddings):\n",
    "    return vq(centroids, corpus_embeddings)[0]\n",
    "\n",
    "\n",
    "def agglo_cluster(sents, sent_embeddings, n_sents, threshold=0.5): \n",
    "    clustering_model = AgglomerativeClustering(n_clusters=None, affinity='cosine', linkage='average', distance_threshold=threshold)\n",
    "    clustering_model.fit(sent_embeddings)\n",
    "    cluster_assignment = clustering_model.labels_\n",
    "\n",
    "    clustered_sents = {}\n",
    "    clustered_sent_embeddings = {}\n",
    "\n",
    "    for sent_id, cluster_id in enumerate(cluster_assignment):\n",
    "        if cluster_id not in clustered_sents:\n",
    "            clustered_sents[cluster_id] = []\n",
    "            clustered_sent_embeddings[cluster_id] = []\n",
    "\n",
    "        clustered_sents[cluster_id].append(sents[sent_id])\n",
    "        clustered_sent_embeddings[cluster_id].append(sent_embeddings[sent_id])\n",
    "    \n",
    "#     pprint(clustered_sents)\n",
    "    \n",
    "    return (cluster_assignment, clustered_sents, clustered_sent_embeddings) if len(clustered_sents) >= n_sents else agglo_cluster(sents, sent_embeddings, n_sents, threshold=threshold - 0.1)\n",
    "\n",
    "\n",
    "def hdbscan_cluster(sents, sent_embeddings, n_sents):\n",
    "    clustering_model = hdbscan.HDBSCAN(min_cluster_size=2)\n",
    "    clustering_model.fit(sent_embeddings)\n",
    "    cluster_assignment = clustering_model.labels_\n",
    "\n",
    "    clustered_sents = {}\n",
    "    clustered_sent_embeddings = {}\n",
    "\n",
    "    for sent_id, cluster_id in enumerate(cluster_assignment):\n",
    "        if cluster_id not in clustered_sents:\n",
    "            clustered_sents[cluster_id] = []\n",
    "            clustered_sent_embeddings[cluster_id] = []\n",
    "\n",
    "        clustered_sents[cluster_id].append(sents[sent_id])\n",
    "        clustered_sent_embeddings[cluster_id].append(sent_embeddings[sent_id])\n",
    "        \n",
    "#     pprint(clustered_sents)\n",
    "        \n",
    "    return (cluster_assignment, clustered_sents, clustered_sent_embeddings) \n",
    "\n",
    "\n",
    "def hybrid_cluster(sents, sent_embeddings, n_sents): \n",
    "    clustering_model = hdbscan.HDBSCAN(min_cluster_size=2)\n",
    "    clustering_model.fit(sent_embeddings)\n",
    "    cluster_assignment = clustering_model.labels_\n",
    "\n",
    "    clustered_sents = {}\n",
    "    clustered_sent_embeddings = {}\n",
    "\n",
    "    for sent_id, cluster_id in enumerate(cluster_assignment):\n",
    "        if cluster_id not in clustered_sents:\n",
    "            clustered_sents[cluster_id] = []\n",
    "            clustered_sent_embeddings[cluster_id] = []\n",
    "\n",
    "        clustered_sents[cluster_id].append(sents[sent_id])\n",
    "        clustered_sent_embeddings[cluster_id].append(sent_embeddings[sent_id])\n",
    "    \n",
    "#     pprint(clustered_sents)\n",
    "    \n",
    "    # remove unidentified cluster -1\n",
    "    clustered_sents = {k: v for k, v in clustered_sents.items() if k != -1}\n",
    "    \n",
    "    if len(clustered_sents) >= n_sents:\n",
    "        cluster_assignment = [e for e in cluster_assignment if e != -1]\n",
    "        clustered_sent_embeddings = {k: v for k, v in clustered_sent_embeddings.items() if k != -1}\n",
    "        return cluster_assignment, clustered_sents, clustered_sent_embeddings\n",
    "    else: \n",
    "        return agglo_cluster(sents, sent_embeddings, n_sents)\n",
    "    \n",
    "\n",
    "def sort_clusters(cluster_assignment):\n",
    "    cluster_nums, counts = np.unique(cluster_assignment, return_counts=True)\n",
    "    cluster_nums_counts = list(zip(cluster_nums, counts))\n",
    "    \n",
    "    return [cluster_num for cluster_num, _ in sorted(cluster_nums_counts, key=lambda x: x[1], reverse=True)]\n",
    "\n",
    "\n",
    "# generate summary\n",
    "def gen_sum(cluster_assignment, clustered_sents, clustered_sent_embeddings, n_sents):\n",
    "    cluster_nums = sort_clusters(cluster_assignment)\n",
    "    \n",
    "    sents_count = 0 \n",
    "\n",
    "    sents_sum = []\n",
    "\n",
    "    for cluster_num in cluster_nums:\n",
    "        centroid = get_centroid(np.array(clustered_sent_embeddings[cluster_num]))\n",
    "        centroid = np.array([centroid])\n",
    "\n",
    "        index = get_nearest_indexes(centroid, clustered_sent_embeddings[cluster_num])[0]\n",
    "        sents_sum.append(clustered_sents[cluster_num][index])\n",
    "\n",
    "        sents_count += 1\n",
    "        if sents_count == n_sents: \n",
    "            break\n",
    "    \n",
    "    return sents_sum\n",
    "\n",
    "\n",
    "def agglo_sum(sents, sent_embeddings, n_sents=5, threshold=0.5):\n",
    "    if len(sents) < 2 or n_sents > len(sents): \n",
    "        return sents\n",
    "    \n",
    "    cluster_assignment, clustered_sents, clustered_sent_embeddings = agglo_cluster(sents, sent_embeddings, n_sents, threshold)\n",
    "    return gen_sum(cluster_assignment, clustered_sents, clustered_sent_embeddings, n_sents)   \n",
    "\n",
    "\n",
    "def hybrid_sum(sents, sent_embeddings, n_sents=5): \n",
    "    if len(sents) < 2 or n_sents >= len(sents): \n",
    "        return sents\n",
    "    \n",
    "    cluster_assignment, clustered_sents, clustered_sent_embeddings = hybrid_cluster(sents, sent_embeddings, n_sents)\n",
    "    return gen_sum(cluster_assignment, clustered_sents, clustered_sent_embeddings, n_sents)\n",
    "\n",
    "\n",
    "def hdbscan_sum(sents, sent_embeddings, n_sents=5): \n",
    "    if len(sents) < 2 or n_sents >= len(sents): \n",
    "        return sents\n",
    "    \n",
    "    cluster_assignment, clustered_sents, clustered_sent_embeddings = hdbscan_cluster(sents, sent_embeddings, n_sents)\n",
    "    return gen_sum(cluster_assignment, clustered_sents, clustered_sent_embeddings, n_sents)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "id": "6f5cc6b5",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "cluster_sent_tuples = []\n",
    "\n",
    "for game_id in range(0, 11):\n",
    "    df_sent = df_sents[(df_sents['game_id'] == game_id)]\n",
    "    n_sents = 5\n",
    "    cluster_names = list(df_sent['cluster_name'].value_counts().index)\n",
    "\n",
    "    for cluster_name in cluster_names:     \n",
    "    #     print(f'Summary for cluster: {cluster_name}')\n",
    "        df_sent_cluster_name = df_sent[df_sent['cluster_name'] == cluster_name]\n",
    "\n",
    "        sents = df_sent_cluster_name['sent'].reset_index(drop = True)\n",
    "        sent_embeddings = decode_sent_embeddings(df_sent_cluster_name['sent_embedding'])\n",
    "        n_sents = n_sents\n",
    "\n",
    "        # summarize with hybrid clustering\n",
    "        sents_sum = hybrid_sum(sents, sent_embeddings, n_sents)\n",
    "\n",
    "    #     pprint(sents_sum)\n",
    "\n",
    "        for i, sent in enumerate(sents_sum): \n",
    "            df_sent_rows = df_sent_cluster_name[df_sent_cluster_name['sent'] == sent]\n",
    "\n",
    "            kw_id = df_sent_rows['kw_id'].iloc[0]\n",
    "            sent_id = df_sent_rows['sent_id'].iloc[0]\n",
    "\n",
    "            cluster_sent_tuples.append([int(i + 1), int(kw_id), int(sent_id)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "927232c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5000"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 500 sents per game\n",
    "len(cluster_sent_tuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "8d70bcf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "with conn:\n",
    "    cursor.executemany(\"\"\"UPDATE kws_sents SET rank = ?\n",
    "                        WHERE kw_id=? AND sent_id=?;\"\"\",\n",
    "                   (cluster_sent_tuples))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43d91c41",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Steam Insider",
   "language": "python",
   "name": "steam_insider"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}