notebooks/2022-03-16-max_desc_bot.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a37bd314",
   "metadata": {},
   "outputs": [],
   "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_max_length(description):\n",
    "    MAX_DESCRIPTION_SIZE = 1500\n",
    "    for user in df:\n",
    "        words = re.split('\\W+', description)\n",
    "        if len(words) > MAX_DESCRIPTION_SIZE:\n",
    "            return True\n",
    "        else:\n",
    "            return False\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[\"max_length\"] = df[\"description\"].map(is_max_length)  \n",
    "df_potential_bot = df[\n",
    "    ((df[\"edits\"] + df[\"comments\"] + df[\"reviews\"] + df[\"taxonomy\"] == 0) & df[\"max_length\"])\n",
    "].copy()\n",
    "df_potential_bot.sort_values([\"id\"], inplace=True)\n",
    "df_potential_bot.to_csv('potential_bots.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d31ac68",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee3160d5",
   "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
}