notebooks/2022-03-21-users_with_comments_on_own_page.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3d768ff0",
   "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>id</th>\n",
       "      <th>username</th>\n",
       "      <th>comment_on_own_page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2057</th>\n",
       "      <td>41334</td>\n",
       "      <td>anjanajain727</td>\n",
       "      <td>\\r\\nNon commercial relocation is definitely an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2892</th>\n",
       "      <td>59143</td>\n",
       "      <td>n_dersim62</td>\n",
       "      <td>Ich finde es total doof, dass es keine Themen ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2990</th>\n",
       "      <td>61630</td>\n",
       "      <td>amitsulu</td>\n",
       "      <td>Relocation and boring job seems synonyms when ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5592</th>\n",
       "      <td>90995</td>\n",
       "      <td>ShikhaVerma</td>\n",
       "      <td>Really impressive post. I read it whole and go...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7894</th>\n",
       "      <td>115228</td>\n",
       "      <td>prekhshasharma</td>\n",
       "      <td>Get Packers and Movers Kolkata List of Top Rel...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8893</th>\n",
       "      <td>121905</td>\n",
       "      <td>truewickedsick</td>\n",
       "      <td>https://en.serlo.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11025</th>\n",
       "      <td>137246</td>\n",
       "      <td>Rolex2014</td>\n",
       "      <td>Gib es irgendwann vielleicht auch eine Serlo-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11151</th>\n",
       "      <td>138941</td>\n",
       "      <td>69Madde69Pladde69</td>\n",
       "      <td>ok</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13034</th>\n",
       "      <td>164603</td>\n",
       "      <td>1234567</td>\n",
       "      <td>Ich komme mit dem neuen Editor  noch nicht so ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13716</th>\n",
       "      <td>176558</td>\n",
       "      <td>solutionss</td>\n",
       "      <td>&lt;a href=\"\\r\\nhttps://shieldsecuritysolutions.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13736</th>\n",
       "      <td>177397</td>\n",
       "      <td>nikitarhode143</td>\n",
       "      <td>If you are facing HP printer offline issue and...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13838</th>\n",
       "      <td>179131</td>\n",
       "      <td>nortonsetup1</td>\n",
       "      <td>Best Technical Advisor for Technical Errors \\r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14161</th>\n",
       "      <td>183969</td>\n",
       "      <td>wisea</td>\n",
       "      <td>\\r\\nI am a web developer and software engineer...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14283</th>\n",
       "      <td>184872</td>\n",
       "      <td>365crack</td>\n",
       "      <td>https://365crack.com/apoweredit-crack-serial-key/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14324</th>\n",
       "      <td>185650</td>\n",
       "      <td>Ilaria</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14366</th>\n",
       "      <td>186793</td>\n",
       "      <td>studyclass</td>\n",
       "      <td>mba colleges in delhi ncr with fee structure\\r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14415</th>\n",
       "      <td>187703</td>\n",
       "      <td>poojarai</td>\n",
       "      <td>contactfor-guide.com is a leading Hardware &amp; S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14565</th>\n",
       "      <td>189648</td>\n",
       "      <td>mumbaiescorts23</td>\n",
       "      <td>Mumbai escort service is one of the best servi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14828</th>\n",
       "      <td>195091</td>\n",
       "      <td>juanitaharter</td>\n",
       "      <td>AOL com Homepage\\r\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14881</th>\n",
       "      <td>195857</td>\n",
       "      <td>reisedokument67</td>\n",
       "      <td>#Kaufen Sie gefÃ¤lschten FÃ¼hrerschein Ungaris...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14896</th>\n",
       "      <td>196007</td>\n",
       "      <td>cathleenjone2</td>\n",
       "      <td>A youthful accomplice can be dated. In any cas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14949</th>\n",
       "      <td>196921</td>\n",
       "      <td>travomojo</td>\n",
       "      <td>Travomojo.com makes booking super-easyâ€”with ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14965</th>\n",
       "      <td>197021</td>\n",
       "      <td>Backlinksmaker</td>\n",
       "      <td>http://backlinksmaker.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15550</th>\n",
       "      <td>212449</td>\n",
       "      <td>claramax600</td>\n",
       "      <td>hi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id           username  \\\n",
       "2057    41334      anjanajain727   \n",
       "2892    59143         n_dersim62   \n",
       "2990    61630           amitsulu   \n",
       "5592    90995        ShikhaVerma   \n",
       "7894   115228     prekhshasharma   \n",
       "8893   121905     truewickedsick   \n",
       "11025  137246          Rolex2014   \n",
       "11151  138941  69Madde69Pladde69   \n",
       "13034  164603            1234567   \n",
       "13716  176558         solutionss   \n",
       "13736  177397     nikitarhode143   \n",
       "13838  179131       nortonsetup1   \n",
       "14161  183969              wisea   \n",
       "14283  184872           365crack   \n",
       "14324  185650             Ilaria   \n",
       "14366  186793         studyclass   \n",
       "14415  187703           poojarai   \n",
       "14565  189648    mumbaiescorts23   \n",
       "14828  195091      juanitaharter   \n",
       "14881  195857    reisedokument67   \n",
       "14896  196007      cathleenjone2   \n",
       "14949  196921          travomojo   \n",
       "14965  197021     Backlinksmaker   \n",
       "15550  212449        claramax600   \n",
       "\n",
       "                                     comment_on_own_page  \n",
       "2057   \\r\\nNon commercial relocation is definitely an...  \n",
       "2892   Ich finde es total doof, dass es keine Themen ...  \n",
       "2990   Relocation and boring job seems synonyms when ...  \n",
       "5592   Really impressive post. I read it whole and go...  \n",
       "7894   Get Packers and Movers Kolkata List of Top Rel...  \n",
       "8893                                https://en.serlo.org  \n",
       "11025  Gib es irgendwann vielleicht auch eine Serlo-A...  \n",
       "11151                                                 ok  \n",
       "13034  Ich komme mit dem neuen Editor  noch nicht so ...  \n",
       "13716  <a href=\"\\r\\nhttps://shieldsecuritysolutions.c...  \n",
       "13736  If you are facing HP printer offline issue and...  \n",
       "13838  Best Technical Advisor for Technical Errors \\r...  \n",
       "14161  \\r\\nI am a web developer and software engineer...  \n",
       "14283  https://365crack.com/apoweredit-crack-serial-key/  \n",
       "14324                                               test  \n",
       "14366  mba colleges in delhi ncr with fee structure\\r...  \n",
       "14415  contactfor-guide.com is a leading Hardware & S...  \n",
       "14565  Mumbai escort service is one of the best servi...  \n",
       "14828                               AOL com Homepage\\r\\n  \n",
       "14881  #Kaufen Sie gefÃ¤lschten FÃ¼hrerschein Ungaris...  \n",
       "14896  A youthful accomplice can be dated. In any cas...  \n",
       "14949  Travomojo.com makes booking super-easyâ€”with ...  \n",
       "14965                         http://backlinksmaker.com/  \n",
       "15550                                                 hi  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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 query(sql):\n",
    "    c = db.cursor()\n",
    "    c.execute(sql)\n",
    "    return c.fetchall()\n",
    "\n",
    "\n",
    "def get_comment(user_id):\n",
    "    q = query(f\"\"\"select comment.content\n",
    "    from comment\n",
    "    join user\n",
    "    on user.id = comment.author_id\n",
    "    where comment.uuid_id = user.id and user.id = {user_id}\"\"\")\n",
    "    \n",
    "    return q[0][0] if len(q) > 0 else \"\"\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[\"comment_on_own_page\"] = df[\"id\"].map(get_comment)  \n",
    "df_potential_bot = df[(df[\"edits\"] + df[\"reviews\"] + df[\"taxonomy\"] == 0) & (df[\"comment_on_own_page\"] != \"\")][[\"id\", \"username\", \"comment_on_own_page\"]].copy()\n",
    "df_potential_bot.sort_values([\"id\"], inplace=True)\n",
    "df_potential_bot.to_csv(\"/tmp/2022-21-03users_with_comments_on_own_page.csv\")\n",
    "df_potential_bot\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49e78957",
   "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
}