notebooks/2022-04-03 potential_bot (Kelly Testskript).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9a7810a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                              description\n",
      "id                                                       \n",
      "1        As MSc Computer Science student I enjoy forwa...\n",
      "34540    Arbeit bei Serlo n nIch mich im weitesten Sin...\n",
      "35408    multimedia explanation Hallo allerseits mein ...\n",
      "68640    Shop online for prom dresses bridesmaid dress...\n",
      "70444    Wer bin ich n nHi ich bin Benni Jahre und kom...\n",
      "...                                                   ...\n",
      "239027   Over the years SMPL has established itself as...\n",
      "239039   a https www digitaldynamicswsi com Digital Dy...\n",
      "239073   An excellent option for deciding which and ho...\n",
      "239090   ialah situs slot online terpopuler dengan kon...\n",
      "239104   blockquote strong true a https newkhane ir we...\n",
      "\n",
      "[99 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector as mysql\n",
    "import re\n",
    "import numpy as np\n",
    "\n",
    "db = mysql.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    password=\"password\",\n",
    "    port=\"3306\",\n",
    "    database=\"serlo\"\n",
    ")\n",
    "\n",
    "def is_flagged_word(word):\n",
    "    flagged_words = [\n",
    "                    'online','official','call','fantasy','contact','installation', \n",
    "                    'pay', 'manufacturer', 'delivery', 'established', 'registration',\n",
    "                    'seo', 'service', 'customers', 'trading', 'company', 'buy', 'sell', \n",
    "                    'escort', 'provider', 'product', 'patients', 'website', 'sites', \n",
    "                    'free', 'affordable', 'website', 'buisness', 'installed', 'install',\n",
    "                    'management', 'manufacture', 'install', 'download', 'provides',\n",
    "                    'downloaded', 'services', 'development', 'drug', 'top', 'reviews',\n",
    "                    'review', 'treatment', 'deliver', 'hire', 'hiring', 'supply', 'supplier', \n",
    "                    'commercial', 'discount', 'account', 'websites', 'app', 'products', \n",
    "                    'guests', 'deals'\n",
    "                    ]\n",
    "     \n",
    "    return word.lower() in flagged_words\n",
    "\n",
    "\n",
    "def is_unhelpful_word(word):\n",
    "    unhelpful_words = [\n",
    "                      'col','plugin','rows','state','children','content','text',\n",
    "                      'type','level','children','p','h','attachment','file','layout',\n",
    "                      'child','image','src','width','alt','href'\n",
    "                     ]\n",
    "    \n",
    "    return word.lower() in unhelpful_words\n",
    "        \n",
    "        \n",
    "def readable(words):\n",
    "    result_data = ''\n",
    "    for word in words:\n",
    "        if word.isalpha():\n",
    "            if is_unhelpful_word(word):\n",
    "                pass\n",
    "            else:\n",
    "                result_data = result_data + ' ' + word\n",
    "    return(result_data)\n",
    "\n",
    "def query(sql):\n",
    "    c = db.cursor()\n",
    "    c.execute(sql)\n",
    "    return c.fetchall()\n",
    "\n",
    "MAX_DESCRIPTION_SIZE = 1500\n",
    "\n",
    "potential_bots = np.empty((0,2))\n",
    "\n",
    "database = query('''SELECT id, description FROM user WHERE description != \"NULL\"''' )\n",
    "\n",
    "for user in database:\n",
    "    flagged_word_count = 0\n",
    "    user_description = user[1]\n",
    "    words = re.split('\\W+', user_description)\n",
    "    \n",
    "    if len(words) > MAX_DESCRIPTION_SIZE:\n",
    "        potential_bots = np.vstack([potential_bots, user])\n",
    "        continue\n",
    "    \n",
    "    for word in words:\n",
    "        if(is_flagged_word(word)):\n",
    "            potential_bots = np.vstack([potential_bots, [user[0], readable(words)]])\n",
    "            break\n",
    "    \n",
    "df = pd.DataFrame(potential_bots, columns = ['id', 'description'])\n",
    "df.set_index('id', inplace = True)\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db04b5ba",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}