Snapshot Surfer 2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:09.483164Z",
     "end_time": "2023-04-05T19:25:09.718240Z"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import datetime as dt\n",
    "import time\n",
    "from datetime import date\n",
    "from subgrounds.subgraph import SyntheticField, FieldPath\n",
    "from subgrounds.subgrounds import Subgrounds\n",
    "import pandas as pd\n",
    "import  os as os\n",
    "import duckdb as db\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [],
   "source": [
    "sg = Subgrounds()\n",
    "snapshot = sg.load_api('https://hub.snapshot.org/graphql')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:09.503168Z",
     "end_time": "2023-04-05T19:25:09.816474Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [],
   "source": [
    "snapshot.Proposal.datetime = SyntheticField(\n",
    "  lambda timestamp: str(datetime.fromtimestamp(timestamp)),\n",
    "  SyntheticField.STRING,\n",
    "  snapshot.Proposal.end,\n",
    ")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:09.818469Z",
     "end_time": "2023-04-05T19:25:09.831469Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [],
   "source": [
    "spacename = input('Enter space name')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:09.834470Z",
     "end_time": "2023-04-05T19:25:13.135288Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [],
   "source": [
    "daysLimit = int(input('How many days in the past do you want to go?'))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:13.071234Z",
     "end_time": "2023-04-05T19:25:15.611644Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [],
   "source": [
    "proposals = snapshot.Query.proposals(\n",
    "  orderBy='created',\n",
    "  orderDirection='desc',\n",
    "  first=1000,\n",
    "  where=[\n",
    "    snapshot.Proposal.space == spacename, ##'fuse.eth',\n",
    "    snapshot.Proposal.state == 'closed'\n",
    "    ##snapshot.Proposal.title == 'OIP-18: Reward rate framework and reduction',\n",
    "  ]\n",
    ")\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:15.536633Z",
     "end_time": "2023-04-05T19:25:15.611644Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "outputs": [
    {
     "data": {
      "text/plain": "                                     proposals_title  proposals_created  \\\n0  OIP-136: Cross-chain native OHM as bonus proje...         1680108558   \n1                     TAP-22 - Treasury Segmentation         1680108259   \n2  OIP-135: Olympus Grants Program 1-Year Review ...         1679402793   \n3  AP-23 - Liquidity Incentives for Aura / Balanc...         1679325165   \n4  OIP 134: Hypernative as a security and risk de...         1679244244   \n5                     OIP-133: Staking Rate Strategy         1678428395   \n6       OIP-132: wstETH/OHM Boosted Liquidity Engine         1678427799   \n7  OIP 131: Boosted Liquidity Engine as a Key Pro...         1678126068   \n8  OIP-130: Lending AMO as a key DAO project for ...         1677691772   \n9   OOP-1 Funding request for 2023 Q1 and Q2 budget.         1677453933   \n\n                                        proposals_id  proposals_start  \\\n0  0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...       1680108526   \n1  0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...       1680108233   \n2  0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...       1679402748   \n3  0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...       1679325106   \n4  0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...       1679244167   \n5  0x0fbdc3e8adbda28970646743492623e92e3a7c2336d4...       1678428364   \n6  0x991058cca07d1e6f361217927a99951d200826e2ee57...       1678427767   \n7  0xe02d0e0d6cb2ad8e6a6512ab1d138d9ec6b64919715a...       1678125946   \n8  0x0e1173ac9585c4e46723d69ef1a4ef1995390d9d3f90...       1677691749   \n9  0xadd0850bb53fc31df236d61e2b53326748c80ef72f54...       1677453900   \n\n   proposals_end  proposals_votes         createdDate           startDate  \\\n0     1680367726               74 2023-03-29 16:49:18 2023-03-29 16:48:46   \n1     1680367433               63 2023-03-29 16:44:19 2023-03-29 16:43:53   \n2     1679686380               67 2023-03-21 12:46:33 2023-03-21 12:45:48   \n3     1679500800               50 2023-03-20 15:12:45 2023-03-20 15:11:46   \n4     1679503367               68 2023-03-19 16:44:04 2023-03-19 16:42:47   \n5     1678687564               76 2023-03-10 06:06:35 2023-03-10 06:06:04   \n6     1678686967               65 2023-03-10 05:56:39 2023-03-10 05:56:07   \n7     1678467600               80 2023-03-06 18:07:48 2023-03-06 18:05:46   \n8     1677950949               55 2023-03-01 17:29:32 2023-03-01 17:29:09   \n9     1677713100               60 2023-02-26 23:25:33 2023-02-26 23:25:00   \n\n              endDate  \n0 2023-04-01 16:48:46  \n1 2023-04-01 16:43:53  \n2 2023-03-24 19:33:00  \n3 2023-03-22 16:00:00  \n4 2023-03-22 16:42:47  \n5 2023-03-13 06:06:04  \n6 2023-03-13 05:56:07  \n7 2023-03-10 17:00:00  \n8 2023-03-04 17:29:09  \n9 2023-03-01 23:25:00  ",
      "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>proposals_title</th>\n      <th>proposals_created</th>\n      <th>proposals_id</th>\n      <th>proposals_start</th>\n      <th>proposals_end</th>\n      <th>proposals_votes</th>\n      <th>createdDate</th>\n      <th>startDate</th>\n      <th>endDate</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>OIP-136: Cross-chain native OHM as bonus proje...</td>\n      <td>1680108558</td>\n      <td>0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...</td>\n      <td>1680108526</td>\n      <td>1680367726</td>\n      <td>74</td>\n      <td>2023-03-29 16:49:18</td>\n      <td>2023-03-29 16:48:46</td>\n      <td>2023-04-01 16:48:46</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>TAP-22 - Treasury Segmentation</td>\n      <td>1680108259</td>\n      <td>0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...</td>\n      <td>1680108233</td>\n      <td>1680367433</td>\n      <td>63</td>\n      <td>2023-03-29 16:44:19</td>\n      <td>2023-03-29 16:43:53</td>\n      <td>2023-04-01 16:43:53</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>OIP-135: Olympus Grants Program 1-Year Review ...</td>\n      <td>1679402793</td>\n      <td>0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...</td>\n      <td>1679402748</td>\n      <td>1679686380</td>\n      <td>67</td>\n      <td>2023-03-21 12:46:33</td>\n      <td>2023-03-21 12:45:48</td>\n      <td>2023-03-24 19:33:00</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AP-23 - Liquidity Incentives for Aura / Balanc...</td>\n      <td>1679325165</td>\n      <td>0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...</td>\n      <td>1679325106</td>\n      <td>1679500800</td>\n      <td>50</td>\n      <td>2023-03-20 15:12:45</td>\n      <td>2023-03-20 15:11:46</td>\n      <td>2023-03-22 16:00:00</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>OIP 134: Hypernative as a security and risk de...</td>\n      <td>1679244244</td>\n      <td>0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...</td>\n      <td>1679244167</td>\n      <td>1679503367</td>\n      <td>68</td>\n      <td>2023-03-19 16:44:04</td>\n      <td>2023-03-19 16:42:47</td>\n      <td>2023-03-22 16:42:47</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>OIP-133: Staking Rate Strategy</td>\n      <td>1678428395</td>\n      <td>0x0fbdc3e8adbda28970646743492623e92e3a7c2336d4...</td>\n      <td>1678428364</td>\n      <td>1678687564</td>\n      <td>76</td>\n      <td>2023-03-10 06:06:35</td>\n      <td>2023-03-10 06:06:04</td>\n      <td>2023-03-13 06:06:04</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>OIP-132: wstETH/OHM Boosted Liquidity Engine</td>\n      <td>1678427799</td>\n      <td>0x991058cca07d1e6f361217927a99951d200826e2ee57...</td>\n      <td>1678427767</td>\n      <td>1678686967</td>\n      <td>65</td>\n      <td>2023-03-10 05:56:39</td>\n      <td>2023-03-10 05:56:07</td>\n      <td>2023-03-13 05:56:07</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>OIP 131: Boosted Liquidity Engine as a Key Pro...</td>\n      <td>1678126068</td>\n      <td>0xe02d0e0d6cb2ad8e6a6512ab1d138d9ec6b64919715a...</td>\n      <td>1678125946</td>\n      <td>1678467600</td>\n      <td>80</td>\n      <td>2023-03-06 18:07:48</td>\n      <td>2023-03-06 18:05:46</td>\n      <td>2023-03-10 17:00:00</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>OIP-130: Lending AMO as a key DAO project for ...</td>\n      <td>1677691772</td>\n      <td>0x0e1173ac9585c4e46723d69ef1a4ef1995390d9d3f90...</td>\n      <td>1677691749</td>\n      <td>1677950949</td>\n      <td>55</td>\n      <td>2023-03-01 17:29:32</td>\n      <td>2023-03-01 17:29:09</td>\n      <td>2023-03-04 17:29:09</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>OOP-1 Funding request for 2023 Q1 and Q2 budget.</td>\n      <td>1677453933</td>\n      <td>0xadd0850bb53fc31df236d61e2b53326748c80ef72f54...</td>\n      <td>1677453900</td>\n      <td>1677713100</td>\n      <td>60</td>\n      <td>2023-02-26 23:25:33</td>\n      <td>2023-02-26 23:25:00</td>\n      <td>2023-03-01 23:25:00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proposals_snapshots = sg.query_df([\n",
    "    proposals.title,\n",
    "    proposals.created,\n",
    "    proposals.id,\n",
    "    proposals.start,\n",
    "    proposals.end,\n",
    "    proposals.votes\n",
    "])\n",
    "\n",
    "proposals_snapshots['createdDate']=(pd.to_datetime(proposals_snapshots['proposals_created'],unit='s'))\n",
    "proposals_snapshots['startDate']=(pd.to_datetime(proposals_snapshots['proposals_start'],unit='s'))\n",
    "proposals_snapshots['endDate']=(pd.to_datetime(proposals_snapshots['proposals_end'],unit='s'))\n",
    "proposals_snapshots.head(10)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:15.552634Z",
     "end_time": "2023-04-05T19:25:16.396973Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "250\n"
     ]
    }
   ],
   "source": [
    "total_snapshots = len(proposals_snapshots)\n",
    "print(total_snapshots)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.396973Z",
     "end_time": "2023-04-05T19:25:16.413979Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "outputs": [
    {
     "data": {
      "text/plain": "                                   0                                 1     2  \\\n0      APPROVE OIP-136 bonus project      REJECT OIP-136 bonus project  None   \n1                     Approve TAP-22                     Reject TAP-22  None   \n2   Approve the changes as specified  Do not approve the changes as sp  None   \n3           Approve Incentive Budget                            Reject  None   \n4                                For                           Against  None   \n..                               ...                               ...   ...   \n95                   YES LETS DO IT!                                NO  None   \n96                               Yes                                No  None   \n97                               Yes                                No  None   \n98                               Yes                                No  None   \n99  For: Approve Balancer pool & LBP               Against: Do nothing  None   \n\n       3     4     5     6  \n0   None  None  None  None  \n1   None  None  None  None  \n2   None  None  None  None  \n3   None  None  None  None  \n4   None  None  None  None  \n..   ...   ...   ...   ...  \n95  None  None  None  None  \n96  None  None  None  None  \n97  None  None  None  None  \n98  None  None  None  None  \n99  None  None  None  None  \n\n[100 rows x 7 columns]",
      "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>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>APPROVE OIP-136 bonus project</td>\n      <td>REJECT OIP-136 bonus project</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Approve TAP-22</td>\n      <td>Reject TAP-22</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Approve the changes as specified</td>\n      <td>Do not approve the changes as sp</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Approve Incentive Budget</td>\n      <td>Reject</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>For</td>\n      <td>Against</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>95</th>\n      <td>YES LETS DO IT!</td>\n      <td>NO</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>96</th>\n      <td>Yes</td>\n      <td>No</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>Yes</td>\n      <td>No</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>98</th>\n      <td>Yes</td>\n      <td>No</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>99</th>\n      <td>For: Approve Balancer pool &amp; LBP</td>\n      <td>Against: Do nothing</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>100 rows × 7 columns</p>\n</div>"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proposals_choices = sg.query(proposals.choices)\n",
    "proposals_choices = pd.DataFrame(proposals_choices)\n",
    "proposals_choices.head(100)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.411981Z",
     "end_time": "2023-04-05T19:25:16.657726Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "proposals_choices = sg.query(proposals.choices)\n",
    "proposals_choices = pd.DataFrame(proposals_choices, columns = ['option_1', 'option_2', 'option_3', 'option_4', 'option_5','option_6','option_7'])\n",
    "proposals_choices.head(100)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "outputs": [
    {
     "data": {
      "text/plain": "                                     proposals_title  proposals_created  \\\n0  OIP-136: Cross-chain native OHM as bonus proje...         1680108558   \n1                     TAP-22 - Treasury Segmentation         1680108259   \n2  OIP-135: Olympus Grants Program 1-Year Review ...         1679402793   \n3  AP-23 - Liquidity Incentives for Aura / Balanc...         1679325165   \n4  OIP 134: Hypernative as a security and risk de...         1679244244   \n\n                                        proposals_id  proposals_start  \\\n0  0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...       1680108526   \n1  0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...       1680108233   \n2  0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...       1679402748   \n3  0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...       1679325106   \n4  0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...       1679244167   \n\n   proposals_end  proposals_votes         createdDate           startDate  \\\n0     1680367726               74 2023-03-29 16:49:18 2023-03-29 16:48:46   \n1     1680367433               63 2023-03-29 16:44:19 2023-03-29 16:43:53   \n2     1679686380               67 2023-03-21 12:46:33 2023-03-21 12:45:48   \n3     1679500800               50 2023-03-20 15:12:45 2023-03-20 15:11:46   \n4     1679503367               68 2023-03-19 16:44:04 2023-03-19 16:42:47   \n\n              endDate                                 0  \\\n0 2023-04-01 16:48:46     APPROVE OIP-136 bonus project   \n1 2023-04-01 16:43:53                    Approve TAP-22   \n2 2023-03-24 19:33:00  Approve the changes as specified   \n3 2023-03-22 16:00:00          Approve Incentive Budget   \n4 2023-03-22 16:42:47                               For   \n\n                                  1     2     3     4     5     6  \n0      REJECT OIP-136 bonus project  None  None  None  None  None  \n1                     Reject TAP-22  None  None  None  None  None  \n2  Do not approve the changes as sp  None  None  None  None  None  \n3                            Reject  None  None  None  None  None  \n4                           Against  None  None  None  None  None  ",
      "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>proposals_title</th>\n      <th>proposals_created</th>\n      <th>proposals_id</th>\n      <th>proposals_start</th>\n      <th>proposals_end</th>\n      <th>proposals_votes</th>\n      <th>createdDate</th>\n      <th>startDate</th>\n      <th>endDate</th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>OIP-136: Cross-chain native OHM as bonus proje...</td>\n      <td>1680108558</td>\n      <td>0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...</td>\n      <td>1680108526</td>\n      <td>1680367726</td>\n      <td>74</td>\n      <td>2023-03-29 16:49:18</td>\n      <td>2023-03-29 16:48:46</td>\n      <td>2023-04-01 16:48:46</td>\n      <td>APPROVE OIP-136 bonus project</td>\n      <td>REJECT OIP-136 bonus project</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>TAP-22 - Treasury Segmentation</td>\n      <td>1680108259</td>\n      <td>0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...</td>\n      <td>1680108233</td>\n      <td>1680367433</td>\n      <td>63</td>\n      <td>2023-03-29 16:44:19</td>\n      <td>2023-03-29 16:43:53</td>\n      <td>2023-04-01 16:43:53</td>\n      <td>Approve TAP-22</td>\n      <td>Reject TAP-22</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>OIP-135: Olympus Grants Program 1-Year Review ...</td>\n      <td>1679402793</td>\n      <td>0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...</td>\n      <td>1679402748</td>\n      <td>1679686380</td>\n      <td>67</td>\n      <td>2023-03-21 12:46:33</td>\n      <td>2023-03-21 12:45:48</td>\n      <td>2023-03-24 19:33:00</td>\n      <td>Approve the changes as specified</td>\n      <td>Do not approve the changes as sp</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AP-23 - Liquidity Incentives for Aura / Balanc...</td>\n      <td>1679325165</td>\n      <td>0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...</td>\n      <td>1679325106</td>\n      <td>1679500800</td>\n      <td>50</td>\n      <td>2023-03-20 15:12:45</td>\n      <td>2023-03-20 15:11:46</td>\n      <td>2023-03-22 16:00:00</td>\n      <td>Approve Incentive Budget</td>\n      <td>Reject</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>OIP 134: Hypernative as a security and risk de...</td>\n      <td>1679244244</td>\n      <td>0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...</td>\n      <td>1679244167</td>\n      <td>1679503367</td>\n      <td>68</td>\n      <td>2023-03-19 16:44:04</td>\n      <td>2023-03-19 16:42:47</td>\n      <td>2023-03-22 16:42:47</td>\n      <td>For</td>\n      <td>Against</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "olympus_governance_view = pd.DataFrame()\n",
    "olympus_governance_view = pd.concat([proposals_snapshots,proposals_choices], axis=1)\n",
    "olympus_governance_view.head(5)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.616215Z",
     "end_time": "2023-04-05T19:25:16.663728Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "outputs": [
    {
     "data": {
      "text/plain": "250"
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(olympus_governance_view)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.630728Z",
     "end_time": "2023-04-05T19:25:16.810248Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "outputs": [
    {
     "data": {
      "text/plain": "                                       proposals_title  proposals_created  \\\n0    OIP-136: Cross-chain native OHM as bonus proje...         1680108558   \n1                       TAP-22 - Treasury Segmentation         1680108259   \n2    OIP-135: Olympus Grants Program 1-Year Review ...         1679402793   \n3    AP-23 - Liquidity Incentives for Aura / Balanc...         1679325165   \n4    OIP 134: Hypernative as a security and risk de...         1679244244   \n..                                                 ...                ...   \n245                    OIP-5: Lower LP Staking Rewards         1619738500   \n246                                        Deposit SLP         1618764942   \n247                                      Loosen Policy         1618763632   \n248                                      Add DAI Bonds         1618763184   \n249                    Open an OHM-ETH Pool on Uniswap         1617377332   \n\n                                          proposals_id  proposals_start  \\\n0    0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...       1680108526   \n1    0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...       1680108233   \n2    0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...       1679402748   \n3    0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...       1679325106   \n4    0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...       1679244167   \n..                                                 ...              ...   \n245     QmTrwGU99RMqTeeCKy5oW9Le9M2mXHHdNeEWzn7T67watz       1619737200   \n246     QmZhSNWAzrDy5KiGSeBsKBXUHW6D4vJnfCuqmdai8LPETd       1618761600   \n247     QmZJb2evEjsxtLz92XBuhApWty69fVqUpFHzuVjMjCd3Se       1618761600   \n248     QmUC5xa4YaWZBV7fy241svBQ8bHSMksm8kB7avk1WyWv9m       1618761600   \n249     QmVaYYojHUCqVhM835eSWtih6CyF1koQpSinPg9Kyw21RN       1617552000   \n\n     proposals_end  proposals_votes         createdDate           startDate  \\\n0       1680367726               74 2023-03-29 16:49:18 2023-03-29 16:48:46   \n1       1680367433               63 2023-03-29 16:44:19 2023-03-29 16:43:53   \n2       1679686380               67 2023-03-21 12:46:33 2023-03-21 12:45:48   \n3       1679500800               50 2023-03-20 15:12:45 2023-03-20 15:11:46   \n4       1679503367               68 2023-03-19 16:44:04 2023-03-19 16:42:47   \n..             ...              ...                 ...                 ...   \n245     1619985600              276 2021-04-29 23:21:40 2021-04-29 23:00:00   \n246     1618934400              225 2021-04-18 16:55:42 2021-04-18 16:00:00   \n247     1618934400              229 2021-04-18 16:33:52 2021-04-18 16:00:00   \n248     1618934400              235 2021-04-18 16:26:24 2021-04-18 16:00:00   \n249     1617724800              225 2021-04-02 15:28:52 2021-04-04 16:00:00   \n\n                endDate                                 0  \\\n0   2023-04-01 16:48:46     APPROVE OIP-136 bonus project   \n1   2023-04-01 16:43:53                    Approve TAP-22   \n2   2023-03-24 19:33:00  Approve the changes as specified   \n3   2023-03-22 16:00:00          Approve Incentive Budget   \n4   2023-03-22 16:42:47                               For   \n..                  ...                               ...   \n245 2021-05-02 20:00:00                     For: Option 1   \n246 2021-04-20 16:00:00               Deposit; keep Onsen   \n247 2021-04-20 16:00:00                             Do it   \n248 2021-04-20 16:00:00                 Add; remove sales   \n249 2021-04-06 16:00:00  Add OHM/ETH pair, adjust rewards   \n\n                                    1                        2           3  \\\n0        REJECT OIP-136 bonus project                     None        None   \n1                       Reject TAP-22                     None        None   \n2    Do not approve the changes as sp                     None        None   \n3                              Reject                     None        None   \n4                             Against                     None        None   \n..                                ...                      ...         ...   \n245                     For: Option 2                  Against        None   \n246                 Deposit; no Onsen            Don't deposit        None   \n247                       Don't do it                     None        None   \n248              Add; phase out sales          Add; keep sales  Do not add   \n249     Add OHM/ETH pair, add rewards  Do not add OHM/ETH pair        None   \n\n        4     5     6  \n0    None  None  None  \n1    None  None  None  \n2    None  None  None  \n3    None  None  None  \n4    None  None  None  \n..    ...   ...   ...  \n245  None  None  None  \n246  None  None  None  \n247  None  None  None  \n248  None  None  None  \n249  None  None  None  \n\n[250 rows x 16 columns]",
      "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>proposals_title</th>\n      <th>proposals_created</th>\n      <th>proposals_id</th>\n      <th>proposals_start</th>\n      <th>proposals_end</th>\n      <th>proposals_votes</th>\n      <th>createdDate</th>\n      <th>startDate</th>\n      <th>endDate</th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>OIP-136: Cross-chain native OHM as bonus proje...</td>\n      <td>1680108558</td>\n      <td>0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907...</td>\n      <td>1680108526</td>\n      <td>1680367726</td>\n      <td>74</td>\n      <td>2023-03-29 16:49:18</td>\n      <td>2023-03-29 16:48:46</td>\n      <td>2023-04-01 16:48:46</td>\n      <td>APPROVE OIP-136 bonus project</td>\n      <td>REJECT OIP-136 bonus project</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>TAP-22 - Treasury Segmentation</td>\n      <td>1680108259</td>\n      <td>0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284...</td>\n      <td>1680108233</td>\n      <td>1680367433</td>\n      <td>63</td>\n      <td>2023-03-29 16:44:19</td>\n      <td>2023-03-29 16:43:53</td>\n      <td>2023-04-01 16:43:53</td>\n      <td>Approve TAP-22</td>\n      <td>Reject TAP-22</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>OIP-135: Olympus Grants Program 1-Year Review ...</td>\n      <td>1679402793</td>\n      <td>0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946...</td>\n      <td>1679402748</td>\n      <td>1679686380</td>\n      <td>67</td>\n      <td>2023-03-21 12:46:33</td>\n      <td>2023-03-21 12:45:48</td>\n      <td>2023-03-24 19:33:00</td>\n      <td>Approve the changes as specified</td>\n      <td>Do not approve the changes as sp</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AP-23 - Liquidity Incentives for Aura / Balanc...</td>\n      <td>1679325165</td>\n      <td>0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5...</td>\n      <td>1679325106</td>\n      <td>1679500800</td>\n      <td>50</td>\n      <td>2023-03-20 15:12:45</td>\n      <td>2023-03-20 15:11:46</td>\n      <td>2023-03-22 16:00:00</td>\n      <td>Approve Incentive Budget</td>\n      <td>Reject</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>OIP 134: Hypernative as a security and risk de...</td>\n      <td>1679244244</td>\n      <td>0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da1...</td>\n      <td>1679244167</td>\n      <td>1679503367</td>\n      <td>68</td>\n      <td>2023-03-19 16:44:04</td>\n      <td>2023-03-19 16:42:47</td>\n      <td>2023-03-22 16:42:47</td>\n      <td>For</td>\n      <td>Against</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>245</th>\n      <td>OIP-5: Lower LP Staking Rewards</td>\n      <td>1619738500</td>\n      <td>QmTrwGU99RMqTeeCKy5oW9Le9M2mXHHdNeEWzn7T67watz</td>\n      <td>1619737200</td>\n      <td>1619985600</td>\n      <td>276</td>\n      <td>2021-04-29 23:21:40</td>\n      <td>2021-04-29 23:00:00</td>\n      <td>2021-05-02 20:00:00</td>\n      <td>For: Option 1</td>\n      <td>For: Option 2</td>\n      <td>Against</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>246</th>\n      <td>Deposit SLP</td>\n      <td>1618764942</td>\n      <td>QmZhSNWAzrDy5KiGSeBsKBXUHW6D4vJnfCuqmdai8LPETd</td>\n      <td>1618761600</td>\n      <td>1618934400</td>\n      <td>225</td>\n      <td>2021-04-18 16:55:42</td>\n      <td>2021-04-18 16:00:00</td>\n      <td>2021-04-20 16:00:00</td>\n      <td>Deposit; keep Onsen</td>\n      <td>Deposit; no Onsen</td>\n      <td>Don't deposit</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>247</th>\n      <td>Loosen Policy</td>\n      <td>1618763632</td>\n      <td>QmZJb2evEjsxtLz92XBuhApWty69fVqUpFHzuVjMjCd3Se</td>\n      <td>1618761600</td>\n      <td>1618934400</td>\n      <td>229</td>\n      <td>2021-04-18 16:33:52</td>\n      <td>2021-04-18 16:00:00</td>\n      <td>2021-04-20 16:00:00</td>\n      <td>Do it</td>\n      <td>Don't do it</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>248</th>\n      <td>Add DAI Bonds</td>\n      <td>1618763184</td>\n      <td>QmUC5xa4YaWZBV7fy241svBQ8bHSMksm8kB7avk1WyWv9m</td>\n      <td>1618761600</td>\n      <td>1618934400</td>\n      <td>235</td>\n      <td>2021-04-18 16:26:24</td>\n      <td>2021-04-18 16:00:00</td>\n      <td>2021-04-20 16:00:00</td>\n      <td>Add; remove sales</td>\n      <td>Add; phase out sales</td>\n      <td>Add; keep sales</td>\n      <td>Do not add</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>249</th>\n      <td>Open an OHM-ETH Pool on Uniswap</td>\n      <td>1617377332</td>\n      <td>QmVaYYojHUCqVhM835eSWtih6CyF1koQpSinPg9Kyw21RN</td>\n      <td>1617552000</td>\n      <td>1617724800</td>\n      <td>225</td>\n      <td>2021-04-02 15:28:52</td>\n      <td>2021-04-04 16:00:00</td>\n      <td>2021-04-06 16:00:00</td>\n      <td>Add OHM/ETH pair, adjust rewards</td>\n      <td>Add OHM/ETH pair, add rewards</td>\n      <td>Do not add OHM/ETH pair</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>250 rows × 16 columns</p>\n</div>"
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "olympus_governance_view.drop_duplicates()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.644727Z",
     "end_time": "2023-04-05T19:25:16.843762Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "outputs": [
    {
     "data": {
      "text/plain": "250"
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(olympus_governance_view)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:16.676727Z",
     "end_time": "2023-04-05T19:25:16.904768Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ticker 0 proposal 0xe8117f18e79acf2e15f7254308c8d1c1138840f5c907b2e08101800196356cb0 records: 74 DB size: 74     -days ago: 4      -date 2023-04-01 16:18:43     -exit? False\n",
      "ticker 1 proposal 0xebd3cf4c019ddf8eb637c816379c8b464fc20ee78284a129bbeb1456370f8c0d records: 63 DB size: 137     -days ago: 4      -date 2023-04-01 13:15:20     -exit? False\n",
      "ticker 2 proposal 0x246ccd5ab718a160f03e3dbbd1280ebb5389d3637946f73f128af10995288538 records: 67 DB size: 204     -days ago: 12      -date 2023-03-24 15:58:12     -exit? False\n",
      "ticker 3 proposal 0xde251686e7f312a5e524ba45f4115a887e31ab6a3cb5c2400a0f273d4db2a9c5 records: 50 DB size: 254     -days ago: 14      -date 2023-03-22 10:19:54     -exit? False\n",
      "ticker 4 proposal 0x58a7bc957b83e4a6424cb72d80bf3c3b0bd8025d0da11d4717fb0a657eafcbd8 records: 68 DB size: 322     -days ago: 14      -date 2023-03-22 02:48:08     -exit? False\n",
      "ticker 5 proposal 0x0fbdc3e8adbda28970646743492623e92e3a7c2336d453a07d6a090089c72ecf records: 76 DB size: 398     -days ago: 23      -date 2023-03-12 21:10:21     -exit? False\n",
      "ticker 6 proposal 0x991058cca07d1e6f361217927a99951d200826e2ee5781a5c04d8f01e376167d records: 65 DB size: 463     -days ago: 23      -date 2023-03-12 20:05:53     -exit? False\n",
      "ticker 7 proposal 0xe02d0e0d6cb2ad8e6a6512ab1d138d9ec6b64919715a3a5257536548b590a3b3 records: 80 DB size: 543     -days ago: 26      -date 2023-03-10 04:46:45     -exit? False\n",
      "ticker 8 proposal 0x0e1173ac9585c4e46723d69ef1a4ef1995390d9d3f90b5627fb4fc6335492300 records: 55 DB size: 598     -days ago: 33      -date 2023-03-03 11:27:06     -exit? False\n",
      "Done. Total Records collected:  598\n"
     ]
    }
   ],
   "source": [
    "##this captures the ENTIRE list of people who voted\n",
    "voteTicker = 0\n",
    "totalProposals = len(olympus_governance_view)\n",
    "voteslist = pd.DataFrame()\n",
    "votesDb = pd.DataFrame()\n",
    "voteListLength = 1000\n",
    "datediff = 0\n",
    "now=0\n",
    "daysAgo=0\n",
    "#daysLimit = 90\n",
    "datediff=0\n",
    "epochlimit = (90*86400)\n",
    "ut = time.time()\n",
    "limitTimestamp = int(ut - epochlimit)\n",
    "limitDate = datetime.fromtimestamp(limitTimestamp)\n",
    "limitDate = limitDate.strftime('%m-%d-%Y')\n",
    "\n",
    "while int(datediff) < int(daysLimit):\n",
    "    proposal_id = olympus_governance_view.iloc[voteTicker,2]\n",
    "    skipValue = (voteTicker)*(1000)\n",
    "    vote_tracker = snapshot.Query.votes(\n",
    "        #orderBy = 'created',\n",
    "        #orderDirection='asc',\n",
    "        first=1000,\n",
    "        where=[\n",
    "          snapshot.Vote.proposal == proposal_id\n",
    "          #snapshot.Vote.created > limitTimestamp\n",
    "        ]\n",
    "    )\n",
    "    voting_snapshots = sg.query_df([\n",
    "    vote_tracker.id,\n",
    "    vote_tracker.voter,\n",
    "    vote_tracker.created,\n",
    "    vote_tracker.choice,\n",
    "    vote_tracker.vp\n",
    "    ])\n",
    "\n",
    "\n",
    "    voting_snapshots['proposals_id']= olympus_governance_view.iloc[voteTicker,2]\n",
    "    #voteDate = votesDb.iat[voteTicker,4]\n",
    "\n",
    "    votesDb=pd.concat([voting_snapshots, votesDb])\n",
    "    votesDb['createdDate']=(pd.to_datetime(votesDb['votes_created'],unit='s'))\n",
    "    then = votesDb.iat[voteTicker,6]\n",
    "    now = datetime.now()\n",
    "    delta = now-then\n",
    "    datediff = delta.days\n",
    "    votesDbLength = len(votesDb)\n",
    "    voteListLength = len(voting_snapshots)\n",
    "    recordTimestamp1 = votesDb.iat[voteTicker,2]\n",
    "    recordTimestamp = dt.fromtimestamp( recordTimestamp1 )\n",
    "    now = (int(dt.utcnow().timestamp()))\n",
    "    #datediff=abs(int(now) - recordTimestamp1)\n",
    "\n",
    "    print('ticker', voteTicker, 'proposal',proposal_id, 'records:',voteListLength, 'DB size:',votesDbLength, '    -days ago:', datediff, '     -date', then, '    -exit?', exit )\n",
    "    #print(proposal_id, voteDate, datediff)\n",
    "    voteTicker = voteTicker+1\n",
    "\n",
    "votesDb.drop_duplicates\n",
    "print(\"Done. Total Records collected: \", votesDbLength)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:25:33.273484Z",
     "end_time": "2023-04-05T19:25:34.793529Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "votesDb.head(100)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:48.836625Z",
     "end_time": "2023-04-05T18:10:48.878111Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "votesDb.rename(columns={\"createdDate\": \"voteDate\"}, inplace = True)\n",
    "votesDb.drop_duplicates(inplace=True)\n",
    "votesDb.drop_duplicates()\n",
    "votesDb.head(10000)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:48.853626Z",
     "end_time": "2023-04-05T18:10:49.005625Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "#olympus_governance_view.rename(columns={\"createdDate\": \"voteDate\"}, inplace = True)\n",
    "olympus_governance_view.head(200)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:48.884111Z",
     "end_time": "2023-04-05T18:10:49.035625Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "governance_data = pd.merge(votesDb, olympus_governance_view, how='inner', left_on='proposals_id', right_on='proposals_id')\n",
    "governance_data.head(10)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:48.900117Z",
     "end_time": "2023-04-05T18:10:49.084692Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "crunch_data = db.query(\"select \"\n",
    "                           \"A.proposals_id\"\n",
    "                       \"    ,A.startDate \"\n",
    "                       \"    ,A.proposals_title \"\n",
    "                       \"    ,A.votes_voter \"\n",
    "                       \"    ,A.votes_vp \"\n",
    "                       \"    ,A.voteDate \"\n",
    "                           \",sum(A.votes_vp) over (Partition by proposals_id  order by votes_vp desc, voteDate asc) as cumulative_vp \"\n",
    "                           \",sum(A.votes_vp) over (Partition by proposals_id) as total_vp \"\n",
    "                           \",(votes_vp::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as percentage_of_total_vp \"\n",
    "                           \",((sum(A.votes_vp) over (Partition by proposals_id  order by votes_vp desc, voteDate asc))::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as cum_percentage_of_total_vp \"\n",
    "                       \",round((sum(A.votes_vp) over (Partition by proposals_id  order by votes_vp desc, voteDate asc))::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as cum_percentage_of_total_vp_stepped \"\n",
    "                           \",row_number() over (Partition by proposals_id order by votes_vp desc, voteDate asc) as proposal_voter_rank \"\n",
    "\n",
    "                           \",(count(*) over (Partition by proposals_id))::decimal total_voters \"\n",
    "\n",
    "                           \",(count(*) over (Partition by proposals_id  order by votes_vp desc, voteDate asc))::decimal/(count(*) over (Partition by proposals_id))::decimal percentage_voters_counted \"\n",
    "\n",
    "                           \",round(100*(count(*) over (Partition by proposals_id  order by votes_vp desc, voteDate asc))::decimal/(count(*) over (Partition by proposals_id)))::decimal percentage_voters_counted_stepped \"\n",
    "                       \"from \"\n",
    "                       \"    governance_data  A \"\n",
    "                       #\"where   to_timestamp((votes_Created::bigint))<'2023-01-01' \"\n",
    "                       \"\"\n",
    "                       \"Group by \"\n",
    "                           \"A.proposals_id\"\n",
    "                       \"    ,A.startDate \"\n",
    "                       \"    ,A.proposals_title \"\n",
    "                       \"    ,A.votes_voter \"\n",
    "                       \"    ,A.votes_vp \"\n",
    "                       \"    ,A.voteDate \"\n",
    "                       \"\"\n",
    "                       \"Order by \"\n",
    "                       \"    A.startDate  asc \"\n",
    "                       \"    , votes_vp desc \"\n",
    "                       \"    , voteDate asc\"\n",
    "                       \"\").df()\n",
    "\n",
    "\n",
    "crunch_data.insert(0, 'DAO', spacename)\n",
    "crunch_data.drop_duplicates\n",
    "crunch_data.head(100)\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:01:58.773020Z",
     "end_time": "2023-04-05T19:01:58.824920Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "#leaders = crunch_data.loc[crunch_data['proposal_voter_rank'] <=3]\n",
    "#leader_count = leaders.votes_voter.nunique()\n",
    "#leader_count\n",
    "#DONT ASK ME WHY THIS DIDNT WORK. HAVE TO DO IT THE UGLY WAY\n",
    "\n",
    "leader_ranks = db.query(\"with leader_ranks as \"\n",
    "                        \"(Select distinct \"\n",
    "                        \"   B.proposals_id\"\n",
    "                        \"   ,B.votes_voter\"\n",
    "                        \"   ,B.proposal_voter_rank \"\n",
    "                        \"   ,(B.proposal_voter_rank +1) as leader_rank \"\n",
    "                        \"From \"\n",
    "                        \"   (select \"\n",
    "                               \"proposals_id\"\n",
    "                               \",votes_voter \"\n",
    "                               \",votes_choice\"\n",
    "                               \",votes_vp\"\n",
    "                               \",votes_created  \"\n",
    "                               \",sum(votes_vp) over (Partition by proposals_id  order by votes_vp desc, votes_created asc) as cumulative_vp\"\n",
    "                               \",sum(votes_vp) over (Partition by proposals_id) as total_vp\"\n",
    "                               \",(votes_vp::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as percentage_of_total_vp \"\n",
    "                               \",((sum(votes_vp) over (Partition by proposals_id  order by votes_vp desc, votes_created asc))::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as cum_percentage_of_total_vp \"\n",
    "                           \"    ,round((sum(votes_vp) over (Partition by proposals_id  order by votes_vp desc, votes_created asc))::decimal/sum(votes_vp::decimal) over (Partition by proposals_id)) as cum_percentage_of_total_vp_stepped \"\n",
    "                               \",row_number() over (Partition by proposals_id order by votes_vp desc, votes_created asc) as proposal_voter_rank \"\n",
    "                               \",count(votes_voter) over (Partition by proposals_id  order by votes_vp desc, votes_created asc) total_voters \"\n",
    "                               \",(count(*) over (Partition by proposals_id  order by votes_vp desc, votes_created asc))::decimal/(count(*) over (Partition by proposals_id))::decimal percentage_voters_counted \"\n",
    "                               \",round(100*(count(*) over (Partition by proposals_id  order by votes_vp desc, votes_created asc))::decimal/(count(*) over (Partition by proposals_id)))::decimal percentage_voters_counted_stepped \"\n",
    "                           \"from \"\n",
    "                           \"    governance_data  \"\n",
    "                               \"\"\n",
    "                           \"Group by \"\n",
    "                           \"    proposals_id\"\n",
    "                           \"    ,votes_voter\"\n",
    "                           \"    ,votes_choice\"\n",
    "                           \"    , votes_vp \"\n",
    "                           \"    , votes_created \"\n",
    "                           \"\"\n",
    "                           \"Order by \"\n",
    "                           \"    proposals_id \"\n",
    "                           \"    ,votes_vp desc \"\n",
    "                           \"    , votes_created asc) B \"\n",
    "                        \"where \"\n",
    "                        \"   B.cum_percentage_of_total_vp<=0.5) \"\n",
    "                        \"\"\n",
    "                        \"Select \"\n",
    "                        \"   *\"\n",
    "                        \"From crunch_data A\"\n",
    "                        \"   Join leader_ranks B on A.proposal_voter_rank = B.leader_rank and A.proposals_id = B.proposals_id\"\n",
    "                        \"\"\n",
    "                    ).df()\n",
    "leader_ranks\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:48.979631Z",
     "end_time": "2023-04-05T18:10:49.099691Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "dao_members = crunch_data.groupby('DAO').votes_voter.nunique()\n",
    "dao_members = dao_members.iloc[0]\n",
    "leader_count =leader_ranks.votes_voter.nunique()\n",
    "elite = round((leader_count)/(dao_members),4)\n",
    "\n",
    "print(dao_members, \"{0:.2%}\".format(elite))\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:10:49.024628Z",
     "end_time": "2023-04-05T18:10:49.118692Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "curve_data = db.query(\n",
    "    \"select \"\n",
    "    \"   percentage_voters_counted_stepped \"\n",
    "    \"   , avg(percentage_voters_counted) avg_percentage_voters_counted \"\n",
    "    \"   , avg(cum_percentage_of_total_vp) avg_percentage_of_total_vp \"\n",
    "    \"   ,'Grand Average' as proposal \"\n",
    "    \"from crunch_data \"\n",
    "    \" group by 1 \"\n",
    "    \"UNION ALL \"\n",
    "    \"SELECT \"\n",
    "    \"   percentage_voters_counted_stepped \"\n",
    "    \"   ,percentage_voters_counted \"\n",
    "    \"   ,cum_percentage_of_total_vp \"\n",
    "    \"   ,proposals_id \"\n",
    "    \"FROM crunch_data \"\n",
    "     ).df()\n",
    "\n",
    "curve_data.head(200)\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T19:24:09.864578Z",
     "end_time": "2023-04-05T19:24:09.901097Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "file = input('Selet a folder to save output') ##enter your file path here - the file is in the repo \"summary_stats.csv\"."
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T16:52:10.888972Z",
     "end_time": "2023-04-05T16:52:16.927494Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "path =file+'/'+spacename+'_olympus_governance_data_'+str(date.today().strftime(\"%b-%d-%Y\"))+'.csv'\n",
    "governance_data.to_csv(path, index = False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T16:52:24.643616Z",
     "end_time": "2023-04-05T16:52:24.706255Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "path =file+'/'+spacename+'_olympus_crunch_data_'+str(date.today().strftime(\"%b-%d-%Y\"))+'.csv'\n",
    "crunch_data.to_csv(path, index = False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T16:33:24.936380Z",
     "end_time": "2023-04-05T16:33:25.005550Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "path =file+'/'+spacename+'_olympus_curve_data_'+str(date.today().strftime(\"%b-%d-%Y\"))+'.csv'\n",
    "curve_data.to_csv(path, index = False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T17:07:47.900678Z",
     "end_time": "2023-04-05T17:07:47.931678Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "plt.rc(\"figure\", figsize=(40, 20))\n",
    "#sns.set_style(\"whitegrid\")\n",
    "plt.rc(\"font\", size=25)\n",
    "data_means = crunch_data.groupby(\"percentage_voters_counted_stepped\")[\"cum_percentage_of_total_vp\",\"percentage_voters_counted\"].agg(\"mean\").reset_index()\n",
    "##print(data_means)\n",
    "plot_title = spacename + ' snapshots % of vote along population with Average as X'\n",
    "\n",
    "ax=sns.scatterplot(data=crunch_data, hue = \"proposals_id\",y=\"cum_percentage_of_total_vp\",x=\"percentage_voters_counted_stepped\").set(title=plot_title,xlabel='% of voters',ylabel='% of voting power')\n",
    "chart = sns.scatterplot(data=data_means,x=\"percentage_voters_counted_stepped\",y=\"cum_percentage_of_total_vp\",zorder=3, s=800,marker='X',color = 'orange')\n",
    "plt.legend(bbox_to_anchor=(1.02, 0.99), loc='upper left', borderaxespad=0)\n",
    "#and save the chart file, too\n",
    "print(chart)\n",
    "plt.savefig(file+'\\\\'+spacename+' vote power distribution.png',transparent =False,  dpi=100)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-05T18:07:04.212807Z",
     "end_time": "2023-04-05T18:07:05.736204Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}