5/read_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "from config import db_config\n",
    "from collections import defaultdict\n",
    "import pyodbc\n",
    "from beem.account import Account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pyodbc.connect(driver=db_config['driver'],\n",
    "                            server=db_config['server'],\n",
    "                            database=db_config['database'],\n",
    "                            uid=db_config['uid'],\n",
    "                            pwd=db_config['pwd'])\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_sql_list(x):\n",
    "    return \"('\" + \"', '\".join(x) + \"')\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query1 = \"\"\"\\\n",
    "SELECT account\n",
    "FROM (\n",
    "       SELECT SUBSTRING(body, CHARINDEX('@', body) + 1, CHARINDEX(' leads', body) - CHARINDEX('@', body) - 1) as account, *\n",
    "       FROM Comments (NOLOCK)\n",
    "       WHERE depth > 0 AND\n",
    "             author = 'guard' AND CONTAINS(body, 'phishing')) C\n",
    "GROUP BY account\n",
    "ORDER BY COUNT(*) DESC\"\"\"\n",
    "\n",
    "query2 = \"\"\"\\\n",
    " SELECT DISTINCT SUBSTRING(body, CHARINDEX('@', body) + 1, CHARINDEX(' is a', body) - CHARINDEX('@', body) - 1) as account\n",
    " FROM Comments (NOLOCK)\n",
    " WHERE depth > 0 AND\n",
    "       author = 'arcange' AND \n",
    "       CONTAINS(body, 'CONFIRMED AND SCAM') AND \n",
    "       body LIKE '%The message you received from%'\"\"\"\n",
    "\n",
    "scammers1 = [row[0] for row in cursor.execute(query1)]\n",
    "scammers2 = [row[0] for row in cursor.execute(query2)]\n",
    "scammers = list(set(scammers1 + scammers2))\n",
    "with open('scammers.txt', 'w') as f:\n",
    "    f.write('\\n'.join(scammers))\n",
    "    \n",
    "n = len(scammers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\\\n",
    "SELECT body, COUNT(*) as cnt\n",
    "FROM Comments (NOLOCK)\n",
    "WHERE depth = 1 AND LEN(body) < 15 AND created BETWEEN GETUTCDATE() - 60 AND GETUTCDATE()\n",
    "GROUP BY body\n",
    "ORDER BY cnt DESC\"\"\"\n",
    "\n",
    "spam_comments = [row[0] for row in cursor.execute(query)]\n",
    "with open('spam_comments.txt', 'w', encoding='utf-8') as f:\n",
    "    f.write('\\n'.join(spam_comments))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spam_phrases = [\n",
    "    'nice', 'nice post', 'good', 'beautiful', 'good post',\n",
    "    'thanks', 'upvoted', 'very nice', 'great', 'nice blog',\n",
    "    'thank you', 'wow', 'amazing', 'nice one', 'awesome',\n",
    "    'great post', 'lol', 'like', 'cool', 'hi',\n",
    "    'nice, upvoted', 'good job', 'nice article', 'nice pic', 'nice photo',\n",
    "    'welcome', 'hello', 'good article', 'nice picture', 'nice info',\n",
    "    'promote me', 'fantastic', 'super', 'nice work', 'nice video',\n",
    "    'good project', 'wonderful', 'nice bro', 'lovely', 'nice shot'\n",
    "]\n",
    "\n",
    "query = \"\"\"\\\n",
    "SELECT TOP {0} author\n",
    "FROM Comments (NOLOCK)\n",
    "WHERE depth = 1 AND\n",
    "      created BETWEEN GETUTCDATE() - 60 AND GETUTCDATE() AND\n",
    "      body in {1}\n",
    "GROUP BY author\n",
    "ORDER BY COUNT(*) DESC\"\"\".format(n, to_sql_list(spam_phrases))\n",
    "\n",
    "comment_spammers = [row[0] for row in cursor.execute(query)]\n",
    "with open('comment_spammers.txt', 'w') as f:\n",
    "    f.write('\\n'.join(comment_spammers))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\\\n",
    "SELECT TOP {0} author\n",
    "FROM Comments (NOLOCK)\n",
    "WHERE depth = 0 AND\n",
    "      category in ('utopian-io', 'dtube', 'dlive', 'steemhunt', 'polish')\n",
    "ORDER BY NEWID()\"\"\".format(n)\n",
    "\n",
    "content_creators = [row[0] for row in cursor.execute(query)]\n",
    "with open('content_creators.txt', 'w') as f:\n",
    "    f.write('\\n'.join(content_creators))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bid_bots = list(map(str.strip, open('bid_bots.txt', 'r').readlines()))\n",
    "full_list = content_creators + scammers + comment_spammers + bid_bots\n",
    "d = defaultdict(lambda: defaultdict(int))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, name in enumerate(full_list):\n",
    "    print(i, end=' ')\n",
    "    account = Account(name)\n",
    "    foll = account.get_follow_count()\n",
    "    d[name]['name'] = name\n",
    "    d[name]['followers'] = foll['follower_count']\n",
    "    d[name]['followings'] = foll['following_count']\n",
    "    d[name]['follow ratio'] = foll['following_count'] / foll['follower_count'] if foll['follower_count'] > 0 else 0\n",
    "    d[name]['muters'] = len(account.get_muters())\n",
    "    d[name]['reputation'] = account.get_reputation()\n",
    "    d[name]['effective sp'] = account.get_steem_power()\n",
    "    own_sp = account.get_steem_power(onlyOwnSP=True)\n",
    "    d[name]['own sp'] = own_sp\n",
    "    d[name]['sp ratio'] = account.get_steem_power() / own_sp if own_sp > 0 else 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\\\n",
    "SELECT\n",
    "  name,\n",
    "  curation_rewards,\n",
    "  posting_rewards,\n",
    "  witnesses_voted_for\n",
    "FROM Accounts (NOLOCK) a\n",
    "WHERE name in {0}\"\"\".format(to_sql_list(full_list))\n",
    "\n",
    "for row in cursor.execute(query):\n",
    "    name = row[0]\n",
    "    curation_rewards = row[1] / 1000.0\n",
    "    posting_rewards = row[2] / 1000.0\n",
    "    witnesses_voted_for = row[3]   \n",
    "    d[name]['curation_rewards'] = curation_rewards\n",
    "    d[name]['posting_rewards'] = posting_rewards\n",
    "    d[name]['witnesses_voted_for'] = witnesses_voted_for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\\\n",
    "SELECT\n",
    "  author,\n",
    "  COUNT(*),\n",
    "  AVG(LEN(body))\n",
    "FROM Comments (NOLOCK) a\n",
    "WHERE depth = 0 AND\n",
    "      created BETWEEN GETUTCDATE() - 90 AND GETUTCDATE()\n",
    "      AND author in {0}\n",
    "GROUP BY author\n",
    "\"\"\".format(to_sql_list(full_list))\n",
    "\n",
    "for row in cursor.execute(query):\n",
    "    name = row[0]\n",
    "    posts = row[1]\n",
    "    average_post_len = row[2]\n",
    "    d[name]['posts'] = posts\n",
    "    d[name]['average_post_len'] = average_post_len"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query=\"\"\"\\\n",
    "SELECT\n",
    "  author,\n",
    "  COUNT(*),\n",
    "  AVG(LEN(body)),\n",
    "  CAST(SUM(CASE WHEN body LIKE '%http%' THEN 1 ELSE 0 END) as DECIMAL(10, 3)) / COUNT(*)\n",
    "FROM Comments (NOLOCK) a\n",
    "WHERE depth > 0 AND\n",
    "      created BETWEEN GETUTCDATE() - 90 AND GETUTCDATE()\n",
    "      AND author in {0}\n",
    "GROUP BY author\n",
    "\"\"\".format(to_sql_list(full_list))\n",
    "\n",
    "for row in cursor.execute(query):\n",
    "    name = row[0]\n",
    "    comments = row[1]\n",
    "    average_comment_len = row[2]\n",
    "    comments_with_link_ratio = row[3]\n",
    "    d[name]['comments'] = comments\n",
    "    d[name]['average_comment_len'] = average_comment_len\n",
    "    d[name]['comments_with_link_ratio'] = comments_with_link_ratio\n",
    "    d[name]['posts_to_comments_ratio'] = d[name]['posts'] / comments if comments > 0 else 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_class(name):\n",
    "    if name in content_creators:\n",
    "        return 0\n",
    "    elif name in scammers:\n",
    "        return 1\n",
    "    elif name in comment_spammers:\n",
    "        return 2\n",
    "    else:\n",
    "        return 3\n",
    "    \n",
    "for name in full_list:\n",
    "    d[name]['class'] = to_class(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['name', 'followers', 'followings', 'follow ratio', 'muters',\n",
    "           'reputation', 'effective sp', 'own sp', 'sp ratio', 'curation_rewards',\n",
    "          'posting_rewards', 'witnesses_voted_for', 'posts', 'average_post_len', 'comments',\n",
    "          'average_comment_len', 'comments_with_link_ratio', 'posts_to_comments_ratio', 'class']\n",
    "\n",
    "with open ('data.csv' , 'w') as f:\n",
    "    f.write(','.join(columns) + '\\n')\n",
    "    for name in full_list:\n",
    "        row = [d[name][column] for column in columns]\n",
    "        f.write(','.join(map(str, row)) + '\\n')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}