notebooks/2022-03-18-potential_bots_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "fb417d9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/kellyshor/.asdf/installs/python/3.9.1/lib/python3.9/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy\n",
      "  warnings.warn(\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",
    "\n",
    "def is_flagged_word(description):\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 description.lower() in flagged_words\n",
    "\n",
    "\n",
    "def contains_flagged_word(description):\n",
    "    for user in df:\n",
    "        words = re.split('\\W+', description)\n",
    "        for word in words:\n",
    "            if(is_flagged_word(word)):\n",
    "                return True\n",
    "            \n",
    "    \n",
    "         \n",
    "df = pd.read_sql(\"\"\"\n",
    "    Select\n",
    "        user.id,\n",
    "        user.username,\n",
    "        user.description,\n",
    "        user.date,\n",
    "        sum(events.edits) as edits,\n",
    "        sum(events.reviews) as reviews,\n",
    "        sum(events.comments) as comments,\n",
    "        sum(events.taxonomy) as taxonomy\n",
    "    From (\n",
    "        SELECT\n",
    "            IF(event_log.event_id = 5, 1, 0) as edits,\n",
    "            IF(event_log.event_id in (6,11), 1, 0) as reviews,\n",
    "            IF(event_log.event_id in (8,9,14,16), 1, 0) as comments,\n",
    "            IF(event_log.event_id in (1,2,12,15,17), 1, 0) as taxonomy,\n",
    "            event_log.actor_id\n",
    "        FROM event_log\n",
    "        WHERE event_log.event_id IN (5,6,11,8,9,14,16,1,2,12,15,17)\n",
    "    ) events\n",
    "    right join user on user.id = events.actor_id\n",
    "    group by user.id\n",
    "\"\"\", db)\n",
    "df.fillna(0, inplace=True)\n",
    "df.head()\n",
    "df[\"contains_flagged_word\"] = df[\"description\"].map(contains_flagged_word)  \n",
    "df_potential_bot = df[\n",
    "    ((df[\"edits\"] + df[\"comments\"] + df[\"reviews\"] + df[\"taxonomy\"] == 0) & df[\"contains_flagged_word\"])\n",
    "].copy()\n",
    "df_potential_bot.sort_values([\"id\"], inplace=True)\n",
    "df_potential_bot\n",
    "df_potential_bot.to_csv('all_potential_bots.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b3159cf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d286211",
   "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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}