Charts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "from steemdata import SteemData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = SteemData().db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load_ext autoreload\n",
    "# %autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go\n",
    "import cufflinks as cf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# go offline for faster workflow\n",
    "# cf.go_offline()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# go back online to update charts on website\n",
    "# cf.go_online()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Posts Count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=90),\n",
    "}\n",
    "conditions = {\n",
    "    'created': time_constraints,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posts = list(db['Posts'].find(conditions, projection={'_id': 0, 'created': 1}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "209976"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(posts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# count number of daily posts\n",
    "from collections import Counter\n",
    "c = Counter([x['created'].date() for x in posts])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# create pandas dataframe\n",
    "df = pd.DataFrame.from_dict(c, orient='index').reset_index()\n",
    "df.columns = ['date', 'count']\n",
    "df = df.sort_values('date')\n",
    "df.set_index('date', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-05-24</th>\n",
       "      <td>5856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-25</th>\n",
       "      <td>6142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-26</th>\n",
       "      <td>5577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-27</th>\n",
       "      <td>5129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-28</th>\n",
       "      <td>1618</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count\n",
       "date             \n",
       "2017-05-24   5856\n",
       "2017-05-25   6142\n",
       "2017-05-26   5577\n",
       "2017-05-27   5129\n",
       "2017-05-28   1618"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/user/anaconda3/lib/python3.6/site-packages/cufflinks/plotlytools.py:156: FutureWarning:\n",
      "\n",
      "pandas.tslib is deprecated and will be removed in a future version.\n",
      "You can access Timestamp as pandas.Timestamp\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "High five! You successfuly sent some data to your account on plotly. View your plot in your browser at https://plot.ly/~plotlyfurion/0 or inside your plot.ly account where it is named 'steemdata-30d-post-count'\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/0.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# plot everything but last day\n",
    "df.iloc[1:-1].iplot(\n",
    "    title='Daily Post Count',\n",
    "    colors=['blue'],\n",
    "    theme='white',\n",
    "    legend=False,\n",
    "    filename='steemdata-30d-post-count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Type Frequency Breakdown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['account_create',\n",
       " 'account_create_with_delegation',\n",
       " 'account_update',\n",
       " 'account_witness_proxy',\n",
       " 'account_witness_vote',\n",
       " 'author_reward',\n",
       " 'cancel_transfer_from_savings',\n",
       " 'change_recovery_account',\n",
       " 'claim_reward_balance',\n",
       " 'comment',\n",
       " 'comment_benefactor_reward',\n",
       " 'comment_options',\n",
       " 'convert',\n",
       " 'curation_reward',\n",
       " 'custom',\n",
       " 'custom_json',\n",
       " 'delegate_vesting_shares',\n",
       " 'delete_comment',\n",
       " 'escrow_approve',\n",
       " 'escrow_dispute',\n",
       " 'escrow_release',\n",
       " 'escrow_transfer',\n",
       " 'feed_publish',\n",
       " 'fill_convert_request',\n",
       " 'fill_order',\n",
       " 'fill_transfer_from_savings',\n",
       " 'fill_vesting_withdraw',\n",
       " 'interest',\n",
       " 'limit_order_cancel',\n",
       " 'limit_order_create',\n",
       " 'limit_order_create2',\n",
       " 'liquidity_reward',\n",
       " 'pow',\n",
       " 'pow2',\n",
       " 'recover_account',\n",
       " 'request_account_recovery',\n",
       " 'return_vesting_delegation',\n",
       " 'set_withdraw_vesting_route',\n",
       " 'shutdown_witness',\n",
       " 'transfer',\n",
       " 'transfer_from_savings',\n",
       " 'transfer_to_savings',\n",
       " 'transfer_to_vesting',\n",
       " 'vote',\n",
       " 'withdraw_vesting',\n",
       " 'witness_update']"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list op_types\n",
    "db['AccountOperations'].distinct('type', {})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=7),\n",
    "}\n",
    "conditions = {\n",
    "    'timestamp': time_constraints,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4040194"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count all operations\n",
    "db['AccountOperations'].find(conditions).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# count operations for each type\n",
    "query = [\n",
    "    {'$match': conditions},\n",
    "    {'$project': {'type':1}},\n",
    "    {'$group': {'_id': '$type', 'total': {'$sum': 1}}}\n",
    "]\n",
    "results = list(db['AccountOperations'].aggregate(query))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# construct a Pandas dataframe\n",
    "df = pd.DataFrame(results)\n",
    "df.columns = ['type', 'total']\n",
    "total = df['total'].sum()\n",
    "df['pct'] = df['total'].apply(lambda x: (x/total*100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>total</th>\n",
       "      <th>pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>vote</td>\n",
       "      <td>2531745</td>\n",
       "      <td>62.663946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>curation_reward</td>\n",
       "      <td>516230</td>\n",
       "      <td>12.777357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>comment</td>\n",
       "      <td>443224</td>\n",
       "      <td>10.970364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>custom_json</td>\n",
       "      <td>260748</td>\n",
       "      <td>6.453849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>author_reward</td>\n",
       "      <td>55332</td>\n",
       "      <td>1.369538</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               type    total        pct\n",
       "12             vote  2531745  62.663946\n",
       "10  curation_reward   516230  12.777357\n",
       "18          comment   443224  10.970364\n",
       "17      custom_json   260748   6.453849\n",
       "24    author_reward    55332   1.369538"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check most common operations\n",
    "df.sort_values('pct', ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# filter out op_types that are less than 0.05%\n",
    "df = df[(df['pct'] > 0.05)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/2.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# render a nice pie chart\n",
    "pie = go.Pie(\n",
    "    labels=df['type'].values.tolist(),\n",
    "    values=df['pct'].values)\n",
    "\n",
    "layout = go.Layout(title='Blockchain Operations Distribution')\n",
    "py.iplot(go.Figure(data=[pie], layout=layout), filename='steemdata-7d-type-pct')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# py.iplot?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Power-Ups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from steem.converter import Converter\n",
    "\n",
    "def power_up_down_data(direction='up', days=30, exclude_steemit=False):\n",
    "    \"\"\"direction: `up` or `down`.\"\"\"\n",
    "    time_constraints = {\n",
    "        '$gte': dt.datetime.now() - dt.timedelta(days=days),\n",
    "    }\n",
    "    conditions = {\n",
    "        'type': 'fill_vesting_withdraw' if direction == 'down' else 'transfer_to_vesting',\n",
    "        'timestamp': time_constraints,\n",
    "    }\n",
    "    # count daily power up sums\n",
    "    power_ups = list(db['AccountOperations'].find(conditions))\n",
    "    \n",
    "    def power_down_amount(amount):\n",
    "        \"\"\" If power-down is in VESTS, convert it to STEEM first.\"\"\"\n",
    "        if amount['asset'] == 'VESTS':\n",
    "            return Converter().vests_to_sp(amount['amount'])\n",
    "        return amount['amount']\n",
    "    \n",
    "    if direction == 'down':\n",
    "        return [{\n",
    "            'account': x['account'],\n",
    "            'amount': power_down_amount(x['deposited']),\n",
    "            'date': x['timestamp'].date()\n",
    "        } for x in power_ups if not (exclude_steemit == True and x['account'] == 'steemit')]\n",
    "    else:\n",
    "        return [{\n",
    "            'account': x['account'],\n",
    "            'amount': x['amount']['amount'],\n",
    "            'date': x['timestamp'].date()\n",
    "        } for x in power_ups]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#downs = pd.DataFrame(power_up_down_data(direction='down'))\n",
    "#downs.sort_values(by='amount', ascending=False).head(30)\n",
    "\n",
    "# downs[downs.account == 'steemit'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ups = pd.DataFrame(power_up_down_data(direction='up', days=20))\n",
    "daily_powerups = ups[['amount', 'date']].groupby('date').sum()\n",
    "daily_powerups.columns = ['UP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "downs = pd.DataFrame(power_up_down_data(direction='down', days=20))\n",
    "daily_powerdowns = downs[['amount', 'date']].groupby('date').sum()\n",
    "daily_powerdowns.columns = ['DOWN']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined = pd.merge(daily_powerdowns.reset_index(), daily_powerups.reset_index(), on='date')\n",
    "combined.set_index('date', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/4.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined.iplot(kind='bar',\n",
    "                title='Daily STEEM Power-Up vs Power-Down for past 20 days',\n",
    "                colors=['blue', 'orange'],\n",
    "                theme='white',\n",
    "                filename='steemdata-30d-power-ups')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Post by App Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=30),\n",
    "}\n",
    "conditions = {\n",
    "    'json_metadata.app': {'$exists': True},\n",
    "    'created': time_constraints,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posts = list(db['Posts'].find(conditions, projection={'_id': 0, 'json_metadata.app': 1}))\n",
    "apps = [x['json_metadata']['app'] for x in posts]\n",
    "# remove version information\n",
    "apps = [x.split('/')[0] for x in apps]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# count most common apps\n",
    "c = Counter(apps)\n",
    "top_apps = c.most_common(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>App</th>\n",
       "      <th>Post Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>steemit</td>\n",
       "      <td>116954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>esteem</td>\n",
       "      <td>2141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>streemian</td>\n",
       "      <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pysteem</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>steepshot</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         App  Post Count\n",
       "1    steemit      116954\n",
       "2     esteem        2141\n",
       "3  streemian         723\n",
       "4    pysteem         280\n",
       "5  steepshot         180"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(top_apps, index=list(range(1, len(top_apps)+1)))\n",
    "df.columns = ['App', 'Post Count']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/6.embed\" height=\"380px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# save table to plotly\n",
    "from plotly import figure_factory as FF\n",
    "\n",
    "table = FF.create_table(df, index=True, index_title='Position')\n",
    "py.iplot(table, filename='steemdata-30d-post-app-types')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## New Accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=90),\n",
    "}\n",
    "conditions = {\n",
    "    'type': {'$in': ['account_create', 'account_create_with_delegation']},\n",
    "    'timestamp': time_constraints,\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'timestamp': 1,\n",
    "    'creator': 1,\n",
    "    'new_account_name': 1,\n",
    "}\n",
    "accounts = list(db['Operations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "accounts = [{\n",
    "    'creator': x['creator'],\n",
    "    'name': x['new_account_name'],\n",
    "    'date': x['timestamp'].date(),\n",
    "    \n",
    "} for x in accounts]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'creator': 'steem', 'date': datetime.date(2017, 3, 30), 'name': 'invorfiha'}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "accounts[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>creator</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>steem</td>\n",
       "      <td>2017-03-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>steem</td>\n",
       "      <td>2017-03-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>steem</td>\n",
       "      <td>2017-03-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>steem</td>\n",
       "      <td>2017-03-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>steem</td>\n",
       "      <td>2017-03-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  creator        date\n",
       "0   steem  2017-03-30\n",
       "1   steem  2017-03-30\n",
       "2   steem  2017-03-30\n",
       "3   steem  2017-03-30\n",
       "4   steem  2017-03-30"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(accounts)\n",
    "# df.set_index('date', inplace=True)\n",
    "df.drop('name', axis=1, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/8.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_count = df.groupby('date').count()\n",
    "df_count.iloc[1:-1].iplot(\n",
    "    theme='white',\n",
    "    colors=['blue'],\n",
    "    legend=False,\n",
    "    title='Daily Account Creation',\n",
    "    filename='steemdata-account-creation',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# df_affiliate = df.groupby('creator').count().sort_values('date', ascending=False)\n",
    "# df_affiliate.iloc[:5].iplot(\n",
    "#     kind='bar',\n",
    "#     theme='white',\n",
    "#     colors=['blue'],\n",
    "#     title='Top Account Creators for past 90 days',\n",
    "#     filename='steemdata-account-creation-faucet',\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author Reward Distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=30),\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'author_reward',\n",
    "    'timestamp': time_constraints,\n",
    "    'vesting_payout.amount': {'$gt': 10000},\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'timestamp': 1,\n",
    "    'account': 1,\n",
    "    'vesting_payout.amount': 1,\n",
    "    'steem_payout.amount': 1,\n",
    "}\n",
    "payouts = list(db['AccountOperations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'account': 'a-condor',\n",
       " 'steem_payout': {'amount': 0.0},\n",
       " 'timestamp': datetime.datetime(2017, 5, 11, 8, 41, 57),\n",
       " 'vesting_payout': {'amount': 19997.612583}}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payouts[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_payouts(payouts):\n",
    "    \"\"\" Turn author_rewards into normalized VESTS payouts. \"\"\"\n",
    "    results = []\n",
    "    for payout in payouts:\n",
    "        # if this is a 100% power-up post, cut VESTS in half\n",
    "        vests = int(payout['vesting_payout']['amount'])\n",
    "        if int(payout['steem_payout']['amount']) == 0:\n",
    "            vests = int(vests/2)\n",
    "        \n",
    "        results.append({\n",
    "            'account': payout['account'],\n",
    "            'permlink': payout.get('permlink', ''),\n",
    "            'VESTS': vests,\n",
    "            'date': payout['timestamp'].date(),\n",
    "        })\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "payouts = process_payouts(payouts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'VESTS': 9998,\n",
       " 'account': 'a-condor',\n",
       " 'date': datetime.date(2017, 5, 11),\n",
       " 'permlink': ''}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payouts[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(payouts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "top_earners = df[['account', 'VESTS']].groupby('account').sum().sort_values('VESTS', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/12.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_earners.iplot(\n",
    "    kind='area',\n",
    "    fill=True,\n",
    "    title='Distribution of Author Rewards (in vests) for past 30 days',\n",
    "    colors=['blue', 'orange'],\n",
    "    theme='white',\n",
    "    legend=False,\n",
    "    yTitle='Vests Awarded',\n",
    "    filename='steemdata-30d-author-rewards'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How much rewards go to posts vs comments?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=7),\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'author_reward',\n",
    "    'timestamp': time_constraints,\n",
    "    'vesting_payout.amount': {'$gt': 10000},\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'timestamp': 1,\n",
    "    'account': 1,\n",
    "    'permlink': 1,\n",
    "    'vesting_payout.amount': 1,\n",
    "    'steem_payout.amount': 1,\n",
    "}\n",
    "payouts = list(db['AccountOperations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "payouts2 = process_payouts(payouts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posts_pool = 0\n",
    "comments_pool = 0\n",
    "for p in payouts2:\n",
    "    if p['permlink'][:3] == \"re-\":\n",
    "        comments_pool += p['VESTS']\n",
    "    else:\n",
    "        posts_pool += p['VESTS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.15287020670368953"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments_pool/posts_pool*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## how about rshares ratio?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=7),\n",
    "}\n",
    "conditions = {\n",
    "    'created': time_constraints,\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'identifier': 1,\n",
    "    'abs_rshares': 1,\n",
    "    'children_abs_rshares': 1,\n",
    "}\n",
    "posts = list(db['Posts'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from funcy.colls import pluck\n",
    "post_rshares = sum(map(int, pluck('abs_rshares', posts)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "root_posts = list(pluck('identifier', posts))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Reputable Users Comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=90),\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'comment',\n",
    "    'timestamp': time_constraints,\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'json_metadata': 0,\n",
    "    'body': 0,\n",
    "    'trx_id': 0,\n",
    "    'block_num': 0,\n",
    "    'type': 0,\n",
    "}\n",
    "all_comments = list(db['Operations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'author': 'nerdylab',\n",
       " 'parent_author': 'marcel1965',\n",
       " 'parent_permlink': 're-nerdylab-how-to-make-money-online-by-selling-tshirts-2017-guide-20170528t071037746z',\n",
       " 'permlink': 're-marcel1965-re-nerdylab-how-to-make-money-online-by-selling-tshirts-2017-guide-20170528t101719668z',\n",
       " 'timestamp': datetime.datetime(2017, 5, 28, 10, 17, 24),\n",
       " 'title': ''}"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_comments[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from steem.utils import is_comment\n",
    "from funcy import first, complement, pluck"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# split into comments and main posts\n",
    "comments = list(filter(is_comment, all_comments))\n",
    "posts = list(filter(complement(is_comment), all_comments))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# turn datetime into dates\n",
    "comments = [{**x, 'date': x['timestamp'].date()} for x in comments]\n",
    "posts = [{**x, 'date': x['timestamp'].date()} for x in posts]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def filter_accounts(accounts, min_rep=40, min_sp=100):\n",
    "    \"\"\" Return list of accounts that match minimum rep and SP requirements. \"\"\"\n",
    "    conditions = {\n",
    "        'name': {'$in': accounts},\n",
    "        'sp': {'$gt': min_sp},\n",
    "        'rep': {'$gt': min_rep},\n",
    "    }\n",
    "    projection = {\n",
    "        '_id': 0,\n",
    "        'name': 1,\n",
    "    }\n",
    "    return [x['name'] for x in db['Accounts'].find(conditions, projection)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3727"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a list of all accounts with adequate Rep/SP for both comments and posts\n",
    "accounts_to_filter = list(set(pluck('author', comments)) | set(pluck('author', posts)))\n",
    "qualifying_accounts = filter_accounts(accounts_to_filter)\n",
    "len(qualifying_accounts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def filter_comments(posts_or_comments, qualifying_accounts):\n",
    "    \"\"\" Filter out all posts/comments from disqualified accounts. \"\"\"\n",
    "    return [x for x in posts_or_comments if x['author'] in qualifying_accounts]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_df(posts_or_comments):\n",
    "    df = pd.DataFrame(posts_or_comments)\n",
    "    df.drop_duplicates(['author', 'permlink'], inplace=True)\n",
    "    return df[['author', 'date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# prepare all dataframes\n",
    "comments_df = create_df(comments)\n",
    "posts_df = create_df(posts)\n",
    "q_comments_df = create_df(filter_comments(comments, qualifying_accounts))\n",
    "q_posts_df = create_df(filter_comments(posts, qualifying_accounts))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nerdylab</td>\n",
       "      <td>2017-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>toptimist</td>\n",
       "      <td>2017-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>condra</td>\n",
       "      <td>2017-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>erikkartmen</td>\n",
       "      <td>2017-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tamaralovelace</td>\n",
       "      <td>2017-05-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           author        date\n",
       "0        nerdylab  2017-05-28\n",
       "1       toptimist  2017-05-28\n",
       "2          condra  2017-05-28\n",
       "3     erikkartmen  2017-05-28\n",
       "4  tamaralovelace  2017-05-28"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def merge_counts(qualified, unqualified):\n",
    "    \"\"\" Merge all comments and comments from reputable authors into single DataFrame. \"\"\"\n",
    "    left = qualified.groupby('date').count().iloc[:-1]\n",
    "    left.columns = ['reputable']\n",
    "    right = unqualified.groupby('date').count().iloc[:-1]\n",
    "    right.columns = ['all']\n",
    "    \n",
    "    return pd.merge(left, right, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/14.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_counts(q_comments_df, comments_df).iplot(\n",
    "    theme='white',\n",
    "    legend=False,\n",
    "    colors=['orange', 'blue'],\n",
    "    title='Daily Comments Count',\n",
    "    filename='steemdata-comments-count',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/28.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_counts(q_posts_df, posts_df).iplot(\n",
    "    theme='white',\n",
    "    legend=False,\n",
    "    colors=['orange', 'blue'],\n",
    "    title='Daily Posts Count',\n",
    "    filename='steemdata-posts-count-2',\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Withdrawal Prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# only bother with accounts powering down at least 1kV\n",
    "conditions = {\n",
    "    'vesting_withdraw_rate.amount': {'$gt': 1000},\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'vesting_withdraw_rate.amount': 1,\n",
    "    'next_vesting_withdrawal': 1,\n",
    "    'name': 1,\n",
    "}\n",
    "data = list(db['Accounts'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from steem.converter import Converter\n",
    "withdrawing_accounts = [{\n",
    "    'name': x['name'],\n",
    "    'vests': x['vesting_withdraw_rate']['amount'],\n",
    "    'date': x['next_vesting_withdrawal'].date()} for x in data]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.85"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how much SP is being powered down right now?\n",
    "all_vests = sum(pluck('vests', withdrawing_accounts))\n",
    "mvest_down = round(Converter().vests_to_sp(all_vests) / 1000000, 2)\n",
    "mvest_down"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(withdrawing_accounts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd_sum = df[['vests', 'date']].groupby('date').sum()\n",
    "pd_sum['sp'] = pd_sum['vests'].apply(lambda x: Converter().vests_to_sp(x))\n",
    "pd_sum.drop('vests', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~plotlyfurion/16.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_sum.iplot(\n",
    "    kind='bar',\n",
    "    theme='white',\n",
    "    colors=['blue'],\n",
    "    legend=False,\n",
    "    yTitle='STEEM to be powered down - %sm in total' % mvest_down,\n",
    "    title='Future Power-Downs',\n",
    "    filename='steemdata-future-powerdown',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transfer from exchanges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "exchanges = ['poloniex', 'bittrex', 'blocktrades']\n",
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=60),\n",
    "}\n",
    "incoming = {\n",
    "    'from': {'$in': exchanges},\n",
    "    'to': {'$nin': exchanges},\n",
    "}\n",
    "outgoing = {\n",
    "    'from': {'$nin': exchanges},\n",
    "    'to': {'$in': exchanges},\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'transfer',\n",
    "    'timestamp': time_constraints,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from steem.utils import keep_in_dict\n",
    "def simplify_transfers(transfers):\n",
    "    data = [keep_in_dict(x, ['amount', 'from', 'to', 'timestamp']) for x in transfers]\n",
    "    data = [{\n",
    "        x['amount']['asset']: x['amount']['amount'],\n",
    "        'date': x['timestamp'].date(),\n",
    "        **keep_in_dict(x, ['to', 'from']),\n",
    "    } for x in data]\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def exchange_flow(direction='incoming'):\n",
    "    if direction == 'incoming':\n",
    "        return simplify_transfers(db['Operations'].find({**conditions, **incoming}))\n",
    "    return simplify_transfers(db['Operations'].find({**conditions, **outgoing}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "incoming = exchange_flow('incoming')\n",
    "outgoing = exchange_flow('outgoing')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "incoming[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "incoming_df = pd.DataFrame(incoming).groupby('date')['STEEM'].sum()\n",
    "outgoing_df = pd.DataFrame(outgoing).groupby('date')['STEEM'].sum() * -1\n",
    "diff_df = incoming_df + outgoing_df # add together because we mult outgoing by -1 above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "diff_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# outgoing_df.iplot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "diff = go.Scatter(\n",
    "    name='Delta',\n",
    "    mode = 'lines+markers',\n",
    "    opacity=0.7,\n",
    "    x=diff_df.index,\n",
    "    y=diff_df.values\n",
    ")\n",
    "\n",
    "incoming = go.Bar(\n",
    "    name='Incoming STEEM',\n",
    "    opacity=0.9,\n",
    "    x=incoming_df.index,\n",
    "    y=incoming_df.values\n",
    ")\n",
    "\n",
    "outgoing = go.Bar(\n",
    "    name='Outgoing STEEM',\n",
    "    opacity=0.9,\n",
    "    x=outgoing_df.index,\n",
    "    y=outgoing_df.values\n",
    ")\n",
    "\n",
    "colors=['blue', 'orange', 'red']\n",
    "layout = go.Layout(title='STEEM Exchange Flows')\n",
    "fig = go.Figure(data=[incoming, outgoing, diff], layout=layout)\n",
    "py.iplot(fig, filename='steemdata-exchange-flows')\n",
    "\n",
    "# todo, make bar charts share X axis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Daily Transaction Rates\n",
    "- avg tx/s, transaction number, transaction volume (USD implied)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=30),\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'transfer',\n",
    "    'timestamp': time_constraints,\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'amount': 1,\n",
    "    'timestamp': 1,\n",
    "}\n",
    "transfers = list(db['Operations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transfers = [\n",
    "    omit({\n",
    "        **x,\n",
    "        'date': x['timestamp'].date(),\n",
    "         x['amount']['asset']: x['amount']['amount']}, ['timestamp', 'amount']) for x in transfers]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transfers[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(transfers).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# df.groupby('date').aggregate({\n",
    "#     'SBD': 'sum',\n",
    "#     'STEEM': 'sum',\n",
    "# })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_sum = df.groupby('date').sum()\n",
    "df_sum['count'] = df.groupby('date').count()['SBD']\n",
    "no_dust = df[(df.STEEM > 0.1) | (df.SBD > 0.1)]\n",
    "df_sum['no_dust'] = no_dust.groupby('date').count()['SBD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_sum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# df_sum.iplot(\n",
    "#     kind='bar',\n",
    "#     theme='white',\n",
    "#     colors=['blue', 'orange'],\n",
    "#     legend=False,\n",
    "#     title='Daily Transfer Volume',\n",
    "#     filename='steemdata-daily-transfer-volume',\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = [\n",
    "    go.Bar(\n",
    "        name='STEEM Volume',\n",
    "        opacity=0.9,\n",
    "        x=df_sum.index,\n",
    "        y=df_sum['STEEM'].values\n",
    "    ),\n",
    "    go.Bar(\n",
    "        name='SBD Volume',\n",
    "        opacity=0.9,\n",
    "        x=df_sum.index,\n",
    "        y=df_sum['SBD'].values\n",
    "    ),\n",
    "    go.Scatter(\n",
    "        name='Transactions',\n",
    "        mode = 'lines+markers',\n",
    "        opacity=0.7,\n",
    "        x=df_sum.index,\n",
    "        y=df_sum['no_dust'].values,\n",
    "        yaxis='y2'\n",
    "    ),\n",
    "]\n",
    "\n",
    "layout = go.Layout(\n",
    "    title='Daily Transactions',\n",
    "    yaxis=dict(\n",
    "        title='Transaction Volume'\n",
    "    ),\n",
    "    yaxis2=dict(\n",
    "        title='Count (Dust Removed)',\n",
    "        titlefont=dict(\n",
    "            color='rgb(148, 103, 189)'\n",
    "        ),\n",
    "        tickfont=dict(\n",
    "            color='rgb(148, 103, 189)'\n",
    "        ),\n",
    "        overlaying='y',\n",
    "        side='right'\n",
    "    )\n",
    ")\n",
    "\n",
    "colors=['blue', 'orange', 'yellow']\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "py.iplot(fig, filename='steemdata-tranfers-2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# daily transaction count\n",
    "df_count = pd.DataFrame()\n",
    "df_count['All Transfers'] = df.groupby('date').count()['SBD']\n",
    "no_dust = df[(df.STEEM > 0.1) | (df.SBD > 0.1)]\n",
    "df_count['/wo Dust'] = no_dust.groupby('date').count()['SBD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_count.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# df_count.iplot(\n",
    "#     kind='line',\n",
    "#     theme='white',\n",
    "#     colors=['blue', 'orange'],\n",
    "#     legend=False,\n",
    "#     title='Daily Transfers Count',\n",
    "#     filename='steemdata-daily-transfers-count',\n",
    "# )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conversion Requests Fill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# time constraints\n",
    "time_constraints = {\n",
    "    '$gte': dt.datetime.now() - dt.timedelta(days=30),\n",
    "}\n",
    "conditions = {\n",
    "    'type': 'fill_convert_request',\n",
    "    'timestamp': time_constraints,\n",
    "}\n",
    "projection = {\n",
    "    '_id': 0,\n",
    "    'amount_in': 1,\n",
    "    'timestamp': 1,\n",
    "}\n",
    "conversions = list(db['AccountOperations'].find(conditions, projection=projection))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conversions = [{\n",
    "    'date': x['timestamp'].date(),\n",
    "     x['amount_in']['asset']: x['amount_in']['amount']} for x in conversions]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(conversions)\n",
    "df_sum = df[['SBD', 'date']].groupby('date').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_sum.iplot(\n",
    "    kind='bar',\n",
    "    theme='white',\n",
    "    colors=['blue'],\n",
    "    legend=False,\n",
    "    yTitle='SBD Converted',\n",
    "    title='SBD to STEEM conversions',\n",
    "    filename='steemdata-sbd-steem-conversions',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}