governance/snapshot-vote-analysis/2021-11-18 voting centralization analysis/Voting Power Centralization Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7ced0c89-59ce-4811-a9d0-582423a291e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71803e6b-051d-447b-af62-15699c8b00bf",
   "metadata": {},
   "source": [
    "# Using GraphQL to pull data from Snapshot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6ad162e3-8d06-4440-98fa-192ba7478320",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_vote_df_from_proposal_id(proposal_id):\n",
    "    url = 'https://hub.snapshot.org/graphql/'\n",
    "    vote_query = create_vote_query(proposal_id)\n",
    "    vote_response = requests.post(url, json={'query': vote_query})\n",
    "    print(proposal_response.status_code,proposal_response.reason,proposal_id)\n",
    "    vote_json = vote_response.json()\n",
    "    vote_df = vote_json_to_df(vote_json)\n",
    "    return vote_df\n",
    "    \n",
    "def create_vote_query(proposal_id):\n",
    "    vote_query = '''\n",
    "    query Votes {\n",
    "      votes (\n",
    "        first: 9999999\n",
    "        skip: 0\n",
    "        where: {\n",
    "          proposal: \"'''+ proposal_id + '''\"\n",
    "        }\n",
    "        orderBy: \"created\",\n",
    "        orderDirection: desc\n",
    "      ) {\n",
    "        id\n",
    "        ipfs\n",
    "        voter\n",
    "        created\n",
    "        space {\n",
    "          id\n",
    "        }\n",
    "        proposal {\n",
    "          id\n",
    "        }\n",
    "        choice\n",
    "        metadata\n",
    "        vp\n",
    "        vp_by_strategy\n",
    "        vp_state\n",
    "      }\n",
    "    }\n",
    "    '''\n",
    "    return vote_query\n",
    "def vote_json_to_df(vote_json):\n",
    "    row_as_series_list=[]\n",
    "    for vote in vote_json['data']['votes']:\n",
    "        id = vote['id']\n",
    "        ipfs = vote['ipfs']\n",
    "        voter = vote['voter']\n",
    "        created = vote['created']\n",
    "        space = vote['space']['id']\n",
    "        proposal = vote['proposal']['id']\n",
    "        choice = vote['choice']\n",
    "        #metadata = vote['metadata']: {},\n",
    "        vp = vote['vp']\n",
    "        vp_by_strategy = tuple(vote['vp_by_strategy'])\n",
    "        vp_state = vote['vp_state']\n",
    "        row_as_series  = pd.Series(\n",
    "            data=(\n",
    "                id,\n",
    "                ipfs,\n",
    "                voter,\n",
    "                created,\n",
    "                space,\n",
    "                proposal,\n",
    "                choice,\n",
    "                #metadata,\n",
    "                vp,\n",
    "                vp_by_strategy,\n",
    "                vp_state,\n",
    "                ),\n",
    "            index=(\n",
    "                'vote_id',\n",
    "                'ipfs',\n",
    "                'voter',\n",
    "                'created',\n",
    "                'space',\n",
    "                'proposal',\n",
    "                'choice',\n",
    "                #'metadata',\n",
    "                'vp',\n",
    "                'vp_by_strategy',\n",
    "                'vp_state',\n",
    "            )\n",
    "        )\n",
    "        row_as_series_list.append(row_as_series)\n",
    "    return_df = pd.DataFrame(row_as_series_list)\n",
    "    return return_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "88074786-3bc1-414d-b2f6-650d5c264fee",
   "metadata": {},
   "outputs": [],
   "source": [
    "proposal_query = '''\n",
    "query Proposals {\n",
    "  proposals (\n",
    "    first: 10000000\n",
    "    where: {\n",
    "      space_in: [\"index-coop.eth\"],\n",
    "      state: \"closed\"\n",
    "    },\n",
    "    orderBy: \"created\",\n",
    "    orderDirection: desc\n",
    "  ) {\n",
    "    id\n",
    "    title\n",
    "    body\n",
    "    choices\n",
    "    start\n",
    "    end\n",
    "    snapshot\n",
    "    state\n",
    "    author\n",
    "    scores\n",
    "    scores_by_strategy\n",
    "    scores_state\n",
    "    scores_total\n",
    "    scores_updated\n",
    "    created\n",
    "    votes\n",
    "    space {\n",
    "      id\n",
    "      name\n",
    "    }\n",
    "  }\n",
    "}\n",
    "'''\n",
    "#print(proposal_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "57f12908-acfd-420c-a9f6-6e487f149557",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200 OK\n"
     ]
    }
   ],
   "source": [
    "url = 'https://hub.snapshot.org/graphql/'\n",
    "proposal_response = requests.post(url, json={'query': proposal_query})\n",
    "print(proposal_response.status_code,proposal_response.reason)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "31db89e7-53e9-48c3-a2db-936ae6d04c86",
   "metadata": {},
   "outputs": [],
   "source": [
    "proposal_json = proposal_response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fe8a7a98-7c24-4d42-a47a-fc475d79e227",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dump Proposal Dataframe\n",
    "proposal_row_list = []\n",
    "for proposal in proposal_json['data']['proposals']:\n",
    "    id = proposal['id']\n",
    "    title = proposal['title']\n",
    "    body = proposal['body']\n",
    "    choices = tuple(proposal['choices'])\n",
    "    scores = tuple(proposal['scores'])\n",
    "    scores_total = proposal['scores_total']\n",
    "    votes = proposal['votes']\n",
    "    created = proposal['created']\n",
    "    \n",
    "    proposal_row = pd.Series(data=[id,created, title,body,choices,scores,scores_total,votes],\n",
    "                      index = ['proposal_id','created','title','body','choices','scores','scores_total','votes']\n",
    "                     )\n",
    "    proposal_row_list.append(proposal_row)\n",
    "\n",
    "proposals_df = pd.DataFrame(proposal_row_list)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0abe75f-bc7e-49f4-92db-d6fa627af7f3",
   "metadata": {},
   "source": [
    "## Cleaning Proposal dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6fb82ad8-7a1d-4c06-a9a2-3183ebed56c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_proposal_category(title):\n",
    "    '''\n",
    "    Logic for parsing the title to categorize the proposal.\n",
    "    '''\n",
    "    if ('DG1' in title or 'Decision Gate 1' in title):\n",
    "        category = 'DG1'\n",
    "    elif ('DG2' in title or 'Decision Gate 2' in title): \n",
    "        category = 'DG2'\n",
    "    elif ('IIP' in title): \n",
    "        category = 'IIP'\n",
    "    else: \n",
    "        category = 'Metagovernance'\n",
    "    return category\n",
    "def make_proposal_result(row):\n",
    "    '''\n",
    "    Logic for checking if the proposal passed or not. \n",
    "    1 is FOR\n",
    "    2 is AGAINST\n",
    "    '''\n",
    "    category = row['category']\n",
    "    percentage_for = row['percentage_for']\n",
    "    #print(category)\n",
    "    #print(percentage_for)\n",
    "    #print(type(percentage_for))\n",
    "    if category == 'DG2':\n",
    "        if percentage_for >.6:\n",
    "            return 1 \n",
    "        else:\n",
    "            return 2\n",
    "    else:\n",
    "        if percentage_for >.5:\n",
    "            return 1\n",
    "        else:\n",
    "            return 2\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "839ed1a6-51e8-437a-b6cd-4b7f8a48d152",
   "metadata": {},
   "outputs": [],
   "source": [
    "proposals_df['created'] = pd.to_datetime(proposals_df['created'],unit='s')\n",
    "proposals_df['category'] = proposals_df['title'].apply(make_proposal_category) # Make category column\n",
    "proposals_df = proposals_df[proposals_df['choices'].apply(len) == 2] # Remove multi-choice proposals\n",
    "proposals_df = proposals_df[proposals_df['scores_total'] !=0] # Remove dummy proposals\n",
    "proposals_df['reached_quorum'] = proposals_df['scores_total'] > 100000 # 100k is a placeholder value for quorum \n",
    "proposals_df['percentage_for'] = proposals_df['scores'].map(lambda x: x[0])/proposals_df['scores_total']\n",
    "proposals_df['result'] = proposals_df.apply(make_proposal_result,axis=1) # Make category column\n",
    "proposals_df.to_json('proposals_df.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6a49fef8-d803-4d1f-9fd4-efb2a5af5d4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>proposal_id</th>\n",
       "      <th>created</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>choices</th>\n",
       "      <th>scores</th>\n",
       "      <th>scores_total</th>\n",
       "      <th>votes</th>\n",
       "      <th>category</th>\n",
       "      <th>reached_quorum</th>\n",
       "      <th>percentage_for</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x6bffe77e0c6da784b8e82b07ff1b2988bcfc50b60281...</td>\n",
       "      <td>2021-12-08 19:18:14</td>\n",
       "      <td>[BALANCER-undefined] MTA &lt;&gt; BAL Treasury Swap</td>\n",
       "      <td>This proposal is for voting on Balancer's prop...</td>\n",
       "      <td>(Yes, let's do it, No, this is not the way)</td>\n",
       "      <td>(3622.3267330135936, 0)</td>\n",
       "      <td>3622.326733</td>\n",
       "      <td>8</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>False</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QmXBS8j6F64gkaV78jyvtDMDzvUHvugJNqCrncWeecE5au</td>\n",
       "      <td>2021-12-06 17:39:48</td>\n",
       "      <td>[AAVE-undefined] Add G-UNI to Aave V2 AMM Market</td>\n",
       "      <td>This proposal is for voting on Aave's proposal...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(28890.0203218128, 0)</td>\n",
       "      <td>28890.020322</td>\n",
       "      <td>29</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>False</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>QmceW9t1BpsuvJYue8VsR7hodte6L95rUV2pGSZALJX2vb</td>\n",
       "      <td>2021-11-30 23:18:26</td>\n",
       "      <td>[COMPOUND-72] Risk Parameter Updates for WBTC,...</td>\n",
       "      <td>This proposal is for voting on Compound's prop...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(24980.931571880352, 0)</td>\n",
       "      <td>24980.931572</td>\n",
       "      <td>49</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>False</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Qmf9zQAiL1eJwDhy7SPXhNjSNhga3xnHrxLp373amCGnzV</td>\n",
       "      <td>2021-11-30 17:31:32</td>\n",
       "      <td>[AAVE-50] Q2 Dynamic Risk Parameters</td>\n",
       "      <td>This proposal is for voting on Aave's proposal...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(287762.3443888976, 0)</td>\n",
       "      <td>287762.344389</td>\n",
       "      <td>36</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>True</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xc6b656f97240505912508f6a47ca0bf117732833da22...</td>\n",
       "      <td>2021-11-29 16:30:42</td>\n",
       "      <td>IIP-113: Index Coop Product Launch &amp; Maintenan...</td>\n",
       "      <td>Status: Proposed\\nAuthor(s): @cavalier_eth, @d...</td>\n",
       "      <td>(FOR, AGAINST)</td>\n",
       "      <td>(109994.5318624229, 470487.62399540434)</td>\n",
       "      <td>580482.155858</td>\n",
       "      <td>95</td>\n",
       "      <td>IIP</td>\n",
       "      <td>True</td>\n",
       "      <td>0.189488</td>\n",
       "      <td>2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>QmSSZzLPbLpegBWfxPBTVAdvUZoXAL2V6Hi58YHEi6bemW</td>\n",
       "      <td>2020-12-16 02:13:40</td>\n",
       "      <td>IIP-7 CoinShares Gold and Cryptoassets Index L...</td>\n",
       "      <td>## Simple Summary\\n\\nThe CoinShares Gold and C...</td>\n",
       "      <td>(FOR, AGAINST)</td>\n",
       "      <td>(207237.6314297594, 136489.5390568919)</td>\n",
       "      <td>343727.170487</td>\n",
       "      <td>93</td>\n",
       "      <td>IIP</td>\n",
       "      <td>True</td>\n",
       "      <td>0.602913</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>QmethJXKnCMd89wWBaxCBRcK2Hu8Dhx5E5AM7XeHEKsL1n</td>\n",
       "      <td>2020-12-11 21:20:11</td>\n",
       "      <td>[COMPOUND-32] Distribute COMP to Affected User...</td>\n",
       "      <td>This proposal is for voting on Compound's prop...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(67189.52469698468, 108859.87904773426)</td>\n",
       "      <td>176049.403745</td>\n",
       "      <td>77</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>True</td>\n",
       "      <td>0.381652</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>QmTdYHoyT5eLWSZduHkivVxs3Vree1EUQUYCk8uoW4Q1EA</td>\n",
       "      <td>2020-12-07 21:50:10</td>\n",
       "      <td>[COMPOUND-31] Adjusting Reserve Factors</td>\n",
       "      <td>This proposal is for voting on Compound's prop...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(108963.72591013898, 0.01090957898367475)</td>\n",
       "      <td>108963.736820</td>\n",
       "      <td>45</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>True</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>QmRw6FoXJRhm4WLaWVuRgsXhE7ZfWvAKY42E386ksugfUC</td>\n",
       "      <td>2020-12-06 06:10:07</td>\n",
       "      <td>[COMPOUND-30] COMP Contributor Grants</td>\n",
       "      <td>This proposal is for voting on Compound's prop...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(138478.01939721234, 85.89796163862161)</td>\n",
       "      <td>138563.917359</td>\n",
       "      <td>82</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>True</td>\n",
       "      <td>0.999380</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>QmR8dNJPcZTcmSP5g86iaGUiArs9DeYTEZkSf7Dg2hVavS</td>\n",
       "      <td>2020-11-18 20:51:14</td>\n",
       "      <td>[AAVE-1] Upgrades for seamless transition to A...</td>\n",
       "      <td>This proposal is for voting on Aave's proposal...</td>\n",
       "      <td>(For, Against)</td>\n",
       "      <td>(39436.73855537037, 0)</td>\n",
       "      <td>39436.738555</td>\n",
       "      <td>46</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>False</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>206 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           proposal_id             created  \\\n",
       "0    0x6bffe77e0c6da784b8e82b07ff1b2988bcfc50b60281... 2021-12-08 19:18:14   \n",
       "1       QmXBS8j6F64gkaV78jyvtDMDzvUHvugJNqCrncWeecE5au 2021-12-06 17:39:48   \n",
       "2       QmceW9t1BpsuvJYue8VsR7hodte6L95rUV2pGSZALJX2vb 2021-11-30 23:18:26   \n",
       "3       Qmf9zQAiL1eJwDhy7SPXhNjSNhga3xnHrxLp373amCGnzV 2021-11-30 17:31:32   \n",
       "4    0xc6b656f97240505912508f6a47ca0bf117732833da22... 2021-11-29 16:30:42   \n",
       "..                                                 ...                 ...   \n",
       "203     QmSSZzLPbLpegBWfxPBTVAdvUZoXAL2V6Hi58YHEi6bemW 2020-12-16 02:13:40   \n",
       "204     QmethJXKnCMd89wWBaxCBRcK2Hu8Dhx5E5AM7XeHEKsL1n 2020-12-11 21:20:11   \n",
       "205     QmTdYHoyT5eLWSZduHkivVxs3Vree1EUQUYCk8uoW4Q1EA 2020-12-07 21:50:10   \n",
       "206     QmRw6FoXJRhm4WLaWVuRgsXhE7ZfWvAKY42E386ksugfUC 2020-12-06 06:10:07   \n",
       "210     QmR8dNJPcZTcmSP5g86iaGUiArs9DeYTEZkSf7Dg2hVavS 2020-11-18 20:51:14   \n",
       "\n",
       "                                                 title  \\\n",
       "0        [BALANCER-undefined] MTA <> BAL Treasury Swap   \n",
       "1     [AAVE-undefined] Add G-UNI to Aave V2 AMM Market   \n",
       "2    [COMPOUND-72] Risk Parameter Updates for WBTC,...   \n",
       "3                 [AAVE-50] Q2 Dynamic Risk Parameters   \n",
       "4    IIP-113: Index Coop Product Launch & Maintenan...   \n",
       "..                                                 ...   \n",
       "203  IIP-7 CoinShares Gold and Cryptoassets Index L...   \n",
       "204  [COMPOUND-32] Distribute COMP to Affected User...   \n",
       "205            [COMPOUND-31] Adjusting Reserve Factors   \n",
       "206              [COMPOUND-30] COMP Contributor Grants   \n",
       "210  [AAVE-1] Upgrades for seamless transition to A...   \n",
       "\n",
       "                                                  body  \\\n",
       "0    This proposal is for voting on Balancer's prop...   \n",
       "1    This proposal is for voting on Aave's proposal...   \n",
       "2    This proposal is for voting on Compound's prop...   \n",
       "3    This proposal is for voting on Aave's proposal...   \n",
       "4    Status: Proposed\\nAuthor(s): @cavalier_eth, @d...   \n",
       "..                                                 ...   \n",
       "203  ## Simple Summary\\n\\nThe CoinShares Gold and C...   \n",
       "204  This proposal is for voting on Compound's prop...   \n",
       "205  This proposal is for voting on Compound's prop...   \n",
       "206  This proposal is for voting on Compound's prop...   \n",
       "210  This proposal is for voting on Aave's proposal...   \n",
       "\n",
       "                                         choices  \\\n",
       "0    (Yes, let's do it, No, this is not the way)   \n",
       "1                                 (For, Against)   \n",
       "2                                 (For, Against)   \n",
       "3                                 (For, Against)   \n",
       "4                                 (FOR, AGAINST)   \n",
       "..                                           ...   \n",
       "203                               (FOR, AGAINST)   \n",
       "204                               (For, Against)   \n",
       "205                               (For, Against)   \n",
       "206                               (For, Against)   \n",
       "210                               (For, Against)   \n",
       "\n",
       "                                        scores   scores_total  votes  \\\n",
       "0                      (3622.3267330135936, 0)    3622.326733      8   \n",
       "1                        (28890.0203218128, 0)   28890.020322     29   \n",
       "2                      (24980.931571880352, 0)   24980.931572     49   \n",
       "3                       (287762.3443888976, 0)  287762.344389     36   \n",
       "4      (109994.5318624229, 470487.62399540434)  580482.155858     95   \n",
       "..                                         ...            ...    ...   \n",
       "203     (207237.6314297594, 136489.5390568919)  343727.170487     93   \n",
       "204    (67189.52469698468, 108859.87904773426)  176049.403745     77   \n",
       "205  (108963.72591013898, 0.01090957898367475)  108963.736820     45   \n",
       "206    (138478.01939721234, 85.89796163862161)  138563.917359     82   \n",
       "210                     (39436.73855537037, 0)   39436.738555     46   \n",
       "\n",
       "           category  reached_quorum  percentage_for  result  \n",
       "0    Metagovernance           False        1.000000       1  \n",
       "1    Metagovernance           False        1.000000       1  \n",
       "2    Metagovernance           False        1.000000       1  \n",
       "3    Metagovernance            True        1.000000       1  \n",
       "4               IIP            True        0.189488       2  \n",
       "..              ...             ...             ...     ...  \n",
       "203             IIP            True        0.602913       1  \n",
       "204  Metagovernance            True        0.381652       2  \n",
       "205  Metagovernance            True        1.000000       1  \n",
       "206  Metagovernance            True        0.999380       1  \n",
       "210  Metagovernance           False        1.000000       1  \n",
       "\n",
       "[206 rows x 12 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proposals_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d69b28c-5db6-4a6c-aabf-5eece49e293c",
   "metadata": {},
   "source": [
    "### Double check that the choices are in the correct order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3cc369a8-8605-4c81-a9de-513e182084d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(FOR, AGAINST)                                                         102\n",
       "(For, Against)                                                          87\n",
       "(Approve, Reject)                                                        4\n",
       "(FOR , AGAINST)                                                          2\n",
       "(Yes, let's do it, No, this is not the way)                              1\n",
       "(Yes, No)                                                                1\n",
       "(Fund Balancer Grants DAO Wave 2, Do not fund)                           1\n",
       "(Stop BAL for Gas, Continue BAL for Gas)                                 1\n",
       "(Approve, Deny)                                                          1\n",
       "(Proceed with \"Whale\" Sponsorship, Do not proceed with sponsorship)      1\n",
       "(Approve Grant, Deny Grant)                                              1\n",
       "(Yes, I support this proposal, No, I am against this proposal)           1\n",
       "(yes, I vote for this proposal, no, I vote against this proposal)        1\n",
       "(Yes we would love a Telegram, Discord is enough)                        1\n",
       "(FOR, AGAINST )                                                          1\n",
       "Name: choices, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proposals_df['choices'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8771e568-215d-4633-b0a7-413bd13d0623",
   "metadata": {},
   "source": [
    "When we did this, we wanted the first choice to always be \"yes\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ac530b1",
   "metadata": {},
   "source": [
    "## Pull votes from Snapshot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4410ce7c-6b4a-4cc9-866d-45258f8d757d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200 OK 0x6bffe77e0c6da784b8e82b07ff1b2988bcfc50b60281ba6035a9fc88854fb3d7\n",
      "200 OK QmXBS8j6F64gkaV78jyvtDMDzvUHvugJNqCrncWeecE5au\n",
      "200 OK QmceW9t1BpsuvJYue8VsR7hodte6L95rUV2pGSZALJX2vb\n",
      "200 OK Qmf9zQAiL1eJwDhy7SPXhNjSNhga3xnHrxLp373amCGnzV\n",
      "200 OK 0xc6b656f97240505912508f6a47ca0bf117732833da22817252d3c1c7100fcd69\n",
      "200 OK 0x44ba0f55de0c4d6875b36f3f7cdacb3b144bd782d9535c52862cd8c41fd1848f\n",
      "200 OK 0x1aa4fdb6f42d866c7ba5f80ceea61358051039df7a22385a29c8401c1146f7cf\n",
      "200 OK 0xba1e51dcaa304833ec3307b0c95fdf525443cfe477d682c735206bdf6ebe5a62\n",
      "200 OK 0xd031343b706075a757650903fc1d3f5e9676f6eff682cc6b1c5692a1607b763f\n",
      "200 OK 0x4dee2def2333c40a5fdbe244cc0e641f099acb44edccc9ba8101dfbf37555353\n",
      "200 OK 0xbecffc979a7e3c1d98844a239987ceeeaa8f0c43d49fc8995e7905371636675b\n",
      "200 OK 0x0b70d0cdd97874eca00769fe1c323351a3e7c473bff876b2662e8d3e0a1e75bb\n",
      "200 OK 0xab2226aff5ab574d15e357c0815e65a89b817dc741bb5bfa1a4d48802010c984\n",
      "200 OK 0x81ba2833504ea67c0d82e923a500afb54aa62ca74eeded97be6050d947a47e8e\n",
      "200 OK QmaGZeBayJGQpFZWYSxrKndyf65ukyeTd9bFqjpDcbWMHG\n",
      "200 OK QmPmuC4JZKy4kYqA7eBGHAgzpQUQNyGCb3wRZUudBKP3GN\n",
      "200 OK QmTsi5WNdBknEBKEjXnzR5PRGRtv9FFLRYyoo5KzoJkvmB\n",
      "200 OK 0xe3fff4b6a16f460fba6477b25fd531e7dd54cf8981323fc08508a99217234495\n",
      "200 OK QmVxXii1pWq3b7Hpw24pmqebNb1svehcHWwJke3Q1SPEaR\n",
      "200 OK QmdWAGRC1G5GnNff7jGcf2kjmNQ9v7KEYkNjTgDbQQAD4c\n",
      "200 OK 0x940fea8c7965da18b12fc6a081478204d41cda975ca8acdf220a17b0b99193aa\n",
      "200 OK 0x31a11f7f37c0319785cad3ab7353745731c3d33908b5ebd073c41276dd98264c\n",
      "200 OK 0xc6735f6bba6dc6ba537077d08f4db0bf3695ec1086ddc8144d264a757e69061f\n",
      "200 OK 0x40edf5d2828a0473626a8a7bfa22a8755fb847a95563e238665ba00ecfb279ca\n",
      "200 OK 0xf617a54ce920d92e800bc8137367c97662446f5fc6e167f93049f840009ee1c4\n",
      "200 OK QmSL8mUPtKp78YrsWmLw7t8X3jH348ZXjUeyEVyanJxV2A\n",
      "200 OK QmSgaTqTbDaMDcoGdiDGy5HhYuLxZpraifsD4UxA1h8FdH\n",
      "200 OK QmV1AkcZ2oJYDzPUGkdevE4NvBSAHoQwAkRV7ytFJyEUzg\n",
      "200 OK 0x6e77a0e6d1fd3f2b73df0e4dad1df2daf17279370397f4f61af420076e2a69bc\n",
      "200 OK 0x26ea01b07411488227517a164bdfa5ee5c06792bd9761c00082b559d750a04f4\n",
      "200 OK 0xf7bf46dc0009184e394704585daace8f1dd43b62cb64b60aea712d00f58edddd\n",
      "200 OK 0xe3e6f74a3ce4f06213b0e4ac80fa4750917f5b9d67bb57b64208e7e9b0254dd9\n",
      "200 OK QmaF8HkHF7c6u86SVdiZ3BnBRRRp3qzqrHaSo3QWeM6pqt\n",
      "200 OK QmcLmFvPcHNuYuN8rXkQGMtAW4mxoucJ4ECfxtmKLobBvD\n",
      "200 OK QmYt65PZBqiAcM8qD6DRQCKgemPAikFgJWPHxxS9yHR5Ae\n",
      "200 OK QmcXqirKXRxp1pSDG36ytWhC1WHCsGk1ZsVxH7yiDMvrAR\n",
      "200 OK 0x625cc581a6b7ec394564e436ada135fea1f38dd985ecaaf6a695b6f518c0162e\n",
      "200 OK 0xbf656da9c6607015e4471974835aaf954433069587b078f92763a847b3c141ac\n",
      "200 OK QmbbpCkypmXYZbBf65N9syoAWsEHXZPo4QgsHapZDC6hZa\n",
      "200 OK QmRNRvcpubDpuQZseWk1wTJ5EGc8vADF6DYifYJdCDkxRC\n",
      "200 OK QmXzJ3X7taEnG3cMk18gunEvNqJUUeyFbJwJ4Hv6LrkD5h\n",
      "200 OK QmVs198Fptpxem37JbJXbXfo8H9RhdsCKaxcQ1xhc6aKir\n",
      "200 OK Qmc7MN1BsWRwADoHqwmXC8vntAbgM7sPHsTwY2cYZ87omT\n",
      "200 OK QmZd8YiEmcvio2AbjzfKJt1FYEvbZU2LUjQr1Swwt3KD2i\n",
      "200 OK QmdjPoGA3nFrL3kdVkBUBWDiKuRoDpMvtSgSEH9RaN6Do5\n",
      "200 OK QmbCJzRtpSWN4H81C1Ws7mJXjpYTPj1JfsAtZgvvDH4Th8\n",
      "200 OK QmcJudcVzrWh22NmFKXmAMP6FugU4beQoAqTwAKLLMa5wQ\n",
      "200 OK QmZVFzHAr8CcrcN2pSgMJ1zrrbt87owbQ1VB2jFfCCP9RF\n",
      "200 OK QmTbLrUm4pHX6xXy2WbaDHcNWTGdDQmjEH8X81jaVJFVkH\n",
      "200 OK QmapbbsGeaqs2dU7Up51UXKZdt2jHuCZ7qkaok3apTouS8\n",
      "200 OK QmS5kdbHkPKs7DCotw2eNQ63cm5WKbAwJiS3KA2zocLdPq\n",
      "200 OK QmWb4943NgRZysYyJSvM1VM98rstmoHqa1BR78PKanCTXa\n",
      "200 OK Qme4RCnpqoRdFP8hDbJf9bSD1xwNU73bbV7RU8PewPBx6h\n",
      "200 OK QmVCw2xE8RU4rJNdPQNpRSGvvadoSgFvVKsLr9TEZ21N6X\n",
      "200 OK QmPR2ozqX71MxYR9dbCwbjoyqAyo93SvLK2RaMU9Sfz7x4\n",
      "200 OK QmW11v7wH4jftquv4nTf1ecAuhkyiNvnXFS2uGCVoDLL37\n",
      "200 OK QmYPKh6oy2YeCtutxtNmSxUQwUaztADJur8YypQn4VGTTp\n",
      "200 OK QmVCk8KLaiRS8QCRFdJyHpirAzCNEfRnnD3JXa2veKY2Ng\n",
      "200 OK QmXvqAyhiachqJdVNVaFCv8vEGXovjJuQhhA1kbsDiBNbs\n",
      "200 OK QmbNMSzTBrUDzTFymFZTmZHGA94RU397KLobvvqVydhcat\n",
      "200 OK QmcXEdcHLCHByzJ6n4EzzQAH3bcEBfiAAnb6pfhbNESgpK\n",
      "200 OK QmS27iWx16i7d9JjUmHFmTSEr6p69Ypi5bFwQmTAP2WnkY\n",
      "200 OK QmUGBkQhx4oSH4aVuwN9deY93nd96fvF5GkGjLjEVCVxZ2\n",
      "200 OK QmUKV2n9xLxRtAmvXBnAJUAeXcYHpugHmTyd6uqjW66i9e\n",
      "200 OK QmZUJVNzSpx7HF5AEAYPJbbMWADaVW3q31wevNZrsXpbLE\n",
      "200 OK QmYV5ep5p5YkP2GEQfkBEz3V46UFdJvFk2RXxa8bQdL54i\n",
      "200 OK QmZrEwKJzk6W7De8yhU9GXCr3ANBzd3i1VZuKrJ9TSbeDM\n",
      "200 OK QmeCxMEWRiJVp6J37NsLBWQC6gF4Y667LmSQ4xyY8eMY5F\n",
      "200 OK QmT2mXGFrPaZxDv5X24Atvt29eebyuCjPW4Mm5JvGuXetP\n",
      "200 OK QmUPRoz427hLpG5kWvr18Aami3eywQnais5jX22JbdUYys\n",
      "200 OK QmZuFFVjTSboPHR7DRHmJ4uqXW1SEUPWE53aMwZ5M55rQq\n",
      "200 OK QmaikKf56LASjR6VoyJ3Q4aGwUHJcsuWrkEA2sQVNyaHAy\n",
      "200 OK QmPC98CVvLXyB6r1eF6KD2kjfGwgsUPUJEPfpvVJZaY7xK\n",
      "200 OK QmTYSEAYEmr9VFYgB9AoHbDD898nxsCgdoSg8fDSxLBkqW\n",
      "200 OK QmSLu4P6MLiXMCbbANVPydenz8Gs84k5Pg1P6VTaGo2GYC\n",
      "200 OK QmdbrV5YNqohuTQgdwsywWdcRB7Mth9xae6GMMUB9mjKjn\n",
      "200 OK QmdzouACaVbDLNSzca8aWBJr87hCtjuWNrTSbXBqQ4K5y9\n",
      "200 OK QmZa8WsStWo84iz1wGCsPzuM1TJTAi4DvsP2yvhu7669CZ\n",
      "200 OK QmZJzVGRgsu4AZVWyr4zqeuQLYfbjmq3n7dGGJmrNNKp7b\n",
      "200 OK QmTMh6j7h8DYXW2jbBYCQjEWv19MsBUw37DadWLhVX2exs\n",
      "200 OK QmcLCw61tXdSLsCxDTSVGjzTgNyMbPR1mabrWzMhicZQLs\n",
      "200 OK QmRgdgq6oqvP2yXNp12EcMJEz8QumTHKseTUY4954JeE8U\n",
      "200 OK QmRpucw5B4EfUXoRMpnSYLBBQVbssQXX3eJn74ra16JhRi\n",
      "200 OK QmRaLeum8W8BVAu6aTV7ZWzpf1LH9uf7Fr1PfDSf6jYo5N\n",
      "200 OK QmXQPEZnP6KQEVeDzvyx4JW2ESEhBfudXan9FBdsRhMNDS\n",
      "200 OK QmVq5ALGKRbCCYoPuU8wXww1tyEzJ1bUH6YExYVMhzmAig\n",
      "200 OK QmbPu5Q779tRGsLsnSuwTFNb2TkK4ktVEF3XLkefnYgSG7\n",
      "200 OK QmWthugptUz7xVxFdEb5mWf2VrTiHpwZiEJVzEittr6Kpg\n",
      "200 OK QmZTCSy4sAFHHTaYjZbiiQrDsV3qqYnSEPf6bav63hv8jP\n",
      "200 OK QmXMqAqKTFqFJoSFQBqWSAUr3WPNTrN4CNYmiBHgfG6qXU\n",
      "200 OK QmTYWLzKhaEbgXiaDjG3qoHTY4YCV8hdJNJk9AdFsRQVTP\n",
      "200 OK QmWD785mHAVuGc7VjPXSZEgkUEpSPqBrVQMnrXZGhydHZb\n",
      "200 OK QmQR1HZw8rBKUonkDSQnC379zRfDX1ggxBkucVGpLJwwuE\n",
      "200 OK QmNhU6Y6icN7pdiM2YcTzTf8D5bUuqWfkAeRnPh2E5WV2n\n",
      "200 OK QmZ4wifjaELSWL55xqW2PmkYuq1iP1bRYtnSeZixQeomMp\n",
      "200 OK QmRGG1FYYmoDufRCGAHnGpmB98GTFdWThsmzZ3w4aWdmAd\n",
      "200 OK QmXYWFbY4YsyRffdHB5JSL3KbgjWJPQ3J34LwVRxo2LGLL\n",
      "200 OK QmPUPcwTsPqjiCoJZRQzMgArN7rfMRKjbMvMnd4XMJhajz\n",
      "200 OK QmSVp7bmMwbDauGQS6VSSc3BgUMA2SkZmRkQYYTRufBi6f\n",
      "200 OK QmcFwGZmzTVR1wG548EDmm2jFDSdCeX6YeDqCgwYwjS9Fh\n",
      "200 OK QmY3C7qUtdm14ZCivHzRWDzw3UduUgxKmQdJ9iTe4ewCXX\n",
      "200 OK QmWwUL42vgm4qqbD61bUAX54qpmaWmPujtcMtndS54ffxt\n",
      "200 OK QmZscyNmGiqsmKQE8uiRAZ7ZnoomkapXqukBv16H1JgiU9\n",
      "200 OK QmUCRe5pNsTen7iVQMwKKGbW16dMGYirSvZMQ4fzmBVYaN\n",
      "200 OK QmPZAhAtuZ1L8D8xaibZMgiCz3MxAJi16mDdt87ZstR7FX\n",
      "200 OK QmSTs2ggzVm5kUXi47wWEnJdUa9GNy3kuxovcGA2Pe1vQZ\n",
      "200 OK QmdwhyR1eNciCc39jvwYmbtriCtEr7bkmFHNT7f4L5DH9k\n",
      "200 OK QmS4QcTaqJynHjc3uFkai9uvKApsz7CR6VqUtUQXuP9qhy\n",
      "200 OK QmVSHoBfYAeqyJ42UoEEQfSCDNBBSBD4MgAvExLwQxyaFQ\n",
      "200 OK QmRF2jTSxHuK5vyHj4RHk8NBZYdoZvqbvqUnUokCE39jcW\n",
      "200 OK Qma9WtQvnCFriA6X2KZ8tsbvJGpqnSKVbGhsu9rYy1Jyvx\n",
      "200 OK QmdQJo13rhP5hnpDkc46H1ZrZgyo3u73Sgf2DptWn4Z5Mf\n",
      "200 OK QmXsZPgif2LcD8669dh3EBnvwuF7MjvbBZACgGR2k2SE1d\n",
      "200 OK QmP7QzdTJezxUvYVqcg8Zeev4oQVZuFDM7nLExYxkiociU\n",
      "200 OK QmV3DbngbqifK4emn5U1edwFdkAwwgNnewPGcNwhANhv23\n",
      "200 OK QmeZfRUFFSL4ngGvYMfamjSJDqia4fzV15CXpcFiJ7qn1T\n",
      "200 OK QmdqUd3RrPxoxQK8PgD1DbcifNrvd5hFWhY1T5Mcu2bMJa\n",
      "200 OK QmcTDf7ba8iPePN4DGGWQwPMGnHQWATZZbeaVrCBrWoX5X\n",
      "200 OK QmP3Ur3hnDtMXPfatR24TojyH3BRYQ92FWNjh8dU22YpTY\n",
      "200 OK QmWRqe3PbX8z1mZTzjPd29WMLi9iBzgHM1y6SeQgaEifhb\n",
      "200 OK QmU22KuNvzr289Pd5K2wo97hkiE4m24R1D6GF3hQ1tAotJ\n",
      "200 OK QmfEFdo3byE9XsgfZrP2XG7VJwoeYSb3m56oF93hDeDMth\n",
      "200 OK QmSFp29qpcEnzvjr1UhciyVL7No1gMLDwnGLRjiX8448TQ\n",
      "200 OK QmZrsaX8qDPUgN5q2c2J5XZju6vMYoabxxYkScwjqDNjgu\n",
      "200 OK QmZVE4h3Pr83ikxinYedTriPFBdBZdDAcrG49EsYaUn8Mv\n",
      "200 OK QmUiJvwhzNayCCDZzcqHJQ8VusLUfx2NPz2khTE5zaE9dm\n",
      "200 OK QmdXkzHzo9NvMBXxX9zFY6rwDv4yVTtfCnNgrxsNFbYzZg\n",
      "200 OK QmackATMfesHuqanUK4GhLZSJjDpZJgn67JifGG1fhs792\n",
      "200 OK QmT2LyX3aPYaRg9sjt42TwNR31ubtHjd6DwsmCUv74pdhP\n",
      "200 OK QmWWDhfw9T2HYY6UnR3KMhK3T7BfHXko6UhWTnNn2wVmyC\n",
      "200 OK QmURjixKJf8HjybVmqrke5FLdzMB9UnTnFUXnhBBX3F5Sq\n",
      "200 OK Qmd95WcjJSza4gHRXUPqawgBw1YBW1QJ1b1Gv9nXsFbH9U\n",
      "200 OK QmX9gAX6ZAuJNvZ6Rb2PrXfB3DvVdssFsn7Tg3Vao1F6Mo\n",
      "200 OK QmWZpnRgTZvL87pik2L6XCBoAgdPPRhH5tFFFzy5Ej7mF2\n",
      "200 OK QmYGcveyFdVYGBeV4MZ93N2jCZF31Mg9ydEiUq43G6e28d\n",
      "200 OK QmWhMGeP81ypQEZbrhLNf8VT2RwGH2x5JpbcFRTykUxoZF\n",
      "200 OK QmZtEd1x95N91Eg1mwo5DoNA1NPFetrX9zccYxGZKivBzo\n",
      "200 OK QmYib818xxZ8vNPwPkeH8sQ3jmC45DfYFZDS574yGBx7ri\n",
      "200 OK Qmb9hJ1kQtjh6heyAxdrwgNQA34U84DLvQ9AAYmv1yEXbv\n",
      "200 OK QmVXe2ukLeknrHfxhcy9Ku1gQRxWf9JhNqoMssWYsY6KJT\n",
      "200 OK QmbGj5P9oJZWjoybkaKhAZD5ELmexN4zKJAe6VViu9kUJN\n",
      "200 OK QmS9uZV1UzTWmEv3HPzCDv2fh6WT45xhcXLkF2QQFFr9W4\n",
      "200 OK QmYTBsvzLtAbTxaqv2ZGSsG7pQaz7zj6cfT4i4vNd7r5rw\n",
      "200 OK QmZqR9ZjikjFE8TSS84VcS3q8wBeUQJ7qZEhxBvYPyqBU4\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200 OK QmeBLQBrtAukRwsv8235xc8HA21i3bL7xEThHhcAWNAhSh\n",
      "200 OK QmdgNav7hJBCwk1MQyEvV1Y2Lm83pDHegK2bEAV73cmL7i\n",
      "200 OK QmYUmTZwZfjKXPcNucBCRzwWTNLucYBP6UpPxztr9sN5Ci\n",
      "200 OK QmWTBBjj4cty1rmVuyujjV1hvNVVCBQaHbx3FSxKkEfD4R\n",
      "200 OK QmXxzDhCtAgN9aPXyMRosD8UaNMajyHv3LqzbyhJeDBcxG\n",
      "200 OK QmZ1VHJWfo1rZjPdki3NgLu9m32QqfbkVU5TXhS5TFVmpG\n",
      "200 OK QmZwfWV6h1wjFKvjhnKJXJSDY3tYdxyE6RyqpMCDABZPqQ\n",
      "200 OK QmUvb2cLaVw96Ua69rtvJoVvZ6QJr6ypyUYCX9Hkb4YBNf\n",
      "200 OK QmeqmnsThQNCjdhVno9Fhjtk3zkkUUKT6SkrQNBpRpENbi\n",
      "200 OK QmU3wEFKty118Tws75zmf2VHtBGPDcqCvLWn8ehN4PsYhL\n",
      "200 OK QmfYyqbcsrHtkcsZ3LGcVyorCN72p511jzv6m2wbYvCjZv\n",
      "200 OK QmPm9X4XYTuoALKAjUyQTB5Ra3jpeJuzpmaurniP3Ly1MS\n",
      "200 OK QmSreGqJJ4z1XzG1mympZqTVG8DCNRs5S1Pzk2T5VeUofY\n",
      "200 OK QmbvjYrAKDHKcRQHagxX5UZHgAJeCz9j3FUpTEzW2eEizV\n",
      "200 OK QmNXd7DkRELDHBajFPYhLgqzA92zWJECNt7m5hhPRwXe6U\n",
      "200 OK QmWCsf6bwsg2Xt5GbGM5DTjUDD4JWce1irVxEhmza8bj8C\n",
      "200 OK QmbyA286dMzYRQDuYdMghCAUKXQH3hZWp4FQ5m8kYoL5Ki\n",
      "200 OK QmPDTLqmThdHqpG8TSXBmkHEYYZetc4dbNh3CCHRUfphnn\n",
      "200 OK QmSYRYTvuy1XuSP5dgwFoUM21uJQgSfRXuULwv4GvKuQmS\n",
      "200 OK QmPokVV38XZz1dip8p4aitHPQLUdYnhKDKpyADNMn7GVLt\n",
      "200 OK QmfGRkXA5gj2ske8Ci45wZnA9BRQg38iQ9vkpegABi9EbW\n",
      "200 OK QmNeM26WeUs43RvbF71rbqEutgSZQTUDRpUWy6tsJ27pha\n",
      "200 OK QmTbkoreSsv4wwybA3EjjhKaCMCeNYeCFjCr93MTd7bERJ\n",
      "200 OK QmeBhJUWTJoPVk9hqaLftcXNKJkjs63bs4VRBvViX6WniX\n",
      "200 OK QmVRZZXE4NFb8mVrYp4Hz5iHHf9AhD8enE2jEtJwFkKovN\n",
      "200 OK Qmc2DPHoKnyYxRjQfwbpbBngt5xHZrLBgkywGqZm7hHnEU\n",
      "200 OK QmYBG5zerdSkC9TGwguy5biCS5h2cg49PQCHCukJqHmfE1\n",
      "200 OK QmRyCX1DYp6s7aGXGMyPC45FX917vXYkZgksXg9fjo8GrP\n",
      "200 OK QmUPP126SvEUidcT7noCcLa4LSc3e7t3VkWLiD8EJ3vjUt\n",
      "200 OK QmaE8dWs4pgMcG8Fu4oxq8aGyveDxv6YVLJHiRVpiSnsfK\n",
      "200 OK Qmc5naesmRQ36yYVc2U2jJPvykLqVg4BWfBZWbjC9mAjM7\n",
      "200 OK QmbkocKzcWsfysBSUKWhLYDoMgNhWudxXH58ntwN2XoqN3\n",
      "200 OK QmWHAmKhyLDPU1QmkGgfSGHn29247kNLqEmTFF5xCeEbvp\n",
      "200 OK QmcQXqTYJVWv8v2eMXvmBMGkPc2pwbUdHA7763DQtXGsM9\n",
      "200 OK QmYW59Dc9ZwBSGkjMRvfJ9d5s2gesrhr5tMRTTpEM3sMDa\n",
      "200 OK QmTctgNNBg8zpbA6ZDhhMKnAfsC4x4dBRZwDPEaomCDXxr\n",
      "200 OK QmeauYbCZcfY6GwFvyFhFYDfTxzyMwLb2fTj5zcAFHyarY\n",
      "200 OK QmTPVkgfJBB1go2DCFhmacjgSWrLUzTFimdTGdB7d9Q2ao\n",
      "200 OK QmWmesADCdPTWnJdNN8Pj2PgycfoGCdVcVqA8rbwwgQMAV\n",
      "200 OK QmQvBnXgRyFMXTBqaBe7Vf2rwu8vMoBqSdDwuqDK8W2gQZ\n",
      "200 OK QmadsabYMJC96jU2S2kPCSh1suVfDVApGLwrux2WwsHd7x\n",
      "200 OK QmcQL9BjxmqJTpfqRzr6FU83sWLhRswoxwdpDKgJvzXeCc\n",
      "200 OK QmTF9aXtWxZMUVHGfym5q7RGgLaeLDhnVWDMtoWqgsPq45\n",
      "200 OK QmaoYPF9hhUvawsqeeewiXdd8YV23qWeh52qoCCeMwR8YB\n",
      "200 OK QmQnXS3YnQhH1R9XicjPPPgmvmGU5XvM4sCTntZVcizVcd\n",
      "200 OK QmP8qvMC2zf5bkw2f8s6AekLq5uzJnyWUdh8aVTPtqAYvG\n",
      "200 OK Qmeaho3rEC8pM9t3FwhCQ86rdYtrpo2iDqHSxMcf2nu51Z\n",
      "200 OK QmYHV2vdTaSubtNJefSoYx82ypsmtzrT7CGUU1EHsXzHC3\n",
      "200 OK Qmd6ugsDsiurzhbxs4AKsnLf9nxU8tmS25vdpc7wr5E1WC\n",
      "200 OK QmYQk1KQNN7gcaDpvy7w6C9rEjVJSanWdRPfebRo1CMewa\n",
      "200 OK QmQwQn4k324kMKPjsSX6ZEzjkkKWh1DNfAN2mQ3dd5aP1a\n",
      "200 OK QmTHitGwcvQbKzytzE4FAvSFRakbfwzU8atQtSrQim76CN\n",
      "200 OK QmehWukEWUEe3NeGKQm5ZnaRsD22k4Yq9Wk6P3b8zv4uJ4\n",
      "200 OK QmSQQQLYjJxXz2Hj3rtTLqKEpwyDF51D2rNDfuFuhnmpnM\n",
      "200 OK QmfMqLAiJmgjTpNaXyVFkZtCaV2kBDtzp4dMhzdvzQCrKZ\n",
      "200 OK QmZ7AWrHWrY6T6wsNujTEYNgMwmCjDGhT4bw4SAQR75rXr\n",
      "200 OK QmWhA9sQpJnxoQfzxxoD7gdjhFEPTvBaDUp53FWsoxn5ZG\n",
      "200 OK QmUmAQvj8aaR86315iUWcwyMdyJHuLhrswtLDqfk5dUU3s\n",
      "200 OK QmPnF5pxsLKS9i3KdNpVtqQcLYcYoDCYM5ocYGmYKw8hrU\n",
      "200 OK QmSSZzLPbLpegBWfxPBTVAdvUZoXAL2V6Hi58YHEi6bemW\n",
      "200 OK QmethJXKnCMd89wWBaxCBRcK2Hu8Dhx5E5AM7XeHEKsL1n\n",
      "200 OK QmTdYHoyT5eLWSZduHkivVxs3Vree1EUQUYCk8uoW4Q1EA\n",
      "200 OK QmRw6FoXJRhm4WLaWVuRgsXhE7ZfWvAKY42E386ksugfUC\n",
      "200 OK QmXvTwJQ7hadUtvGwTH2YnPX3CQbzRw1N2S7VXRJbewy7Y\n",
      "200 OK QmTiaAkd9dndEmWNk9wfpRKrTZxbaMdtWhcXtbSgbV73TB\n",
      "200 OK QmaRR26Ry11ushRsbmjL8kRKXuC8BoojXF9vFR1ziYQUJL\n",
      "200 OK QmR8dNJPcZTcmSP5g86iaGUiArs9DeYTEZkSf7Dg2hVavS\n",
      "200 OK QmbgZXshCtFswGewZTnU6m6Fis9mxnPjzx3FCW26Bey7US\n",
      "200 OK QmbizqmA6vQPyBewof9pDfXhUNsmhj4fSvSnNoSmbpJdJU\n",
      "200 OK QmcYq4VmrvK9UVmf6vcKeUXSbFqjr8FWApaMSeGZ6dio9Q\n",
      "200 OK QmdHTiDXwTzCEearGR2xf6yNnckJjx6DBCuNyxg5WfmToq\n",
      "200 OK QmeazxzsMNfAXu7F4XaUpRV4GB3UYFfvZkBBy3bJiRTBR7\n",
      "200 OK QmeZ51dTKVn6GvLovQGiD5yFpMQD6QtgaD8uKtFVCBqRpg\n",
      "200 OK Qmf3F6ADGZB2wj9EChTvRYLjHCwb83bKugsgnojm8sgjPQ\n",
      "200 OK QmfSqSb6UBRDBSH6jdcJsemEowXd7jmpfS1DzKumkgyCwq\n",
      "200 OK QmPJUvAkyzFfKfLK3GZUJFAKzPa7LWvy3HHTKMEZiXc6xf\n",
      "200 OK QmQGntYFTnheB66Ei975s9CjNUmJuZpicn6dHSepvqzban\n",
      "200 OK QmQMY3Gq67aSkvVw3mAbRrxhw8mff5MJ6Ey434zdHaBQq2\n",
      "200 OK QmQwPPDoByYoKsLudfMTSNLeXLmyo4NpRsEuu1DDbyvuHU\n",
      "200 OK QmR2FFwDqEpNNzg2PcbYPEowpvKgEU3LnJ23BiAevPCNEz\n",
      "200 OK QmRjPcfJYg7FnP794tFiAaFkPimUfgrAL2o7GqLXjNhysU\n",
      "200 OK QmRXRpfQcqPeo51QNngVRJS2dRvWfnX249Jqas69x2627t\n",
      "200 OK QmSAudjx1xD6hge3DuPvQbv6PSZxnTB8RksEaVarxodRqF\n",
      "200 OK QmSQYRsV6FL1Q7wXuKndQxvGskvHWcqLKmT6auPbsR5tyn\n",
      "200 OK QmTijEtnRGhLpdeZ9Wk75GX3tfBG5S5d6z1mLorh5pJoj6\n",
      "200 OK QmTQ9W92Dm9TCYJxARw5vxrR5crsdgxHVHBUFzcn9yvbjG\n",
      "200 OK QmUaJa9F4HQBh6cJrLxu2aqsusHyeL2XfZRwPvuLT24pjM\n",
      "200 OK QmV1oHPa4NS6dEtd2q12NzMjV6v3ToEZy96iUFDrmbT1K5\n",
      "200 OK QmVbFvebLNA5L5GQ9N4iam7xer9fGz4Xn61uarCPgc1TBs\n",
      "200 OK QmVg9JZXxWjkZVZExu8Efz2sU21ESh92ejPEgpFfqapM1E\n",
      "200 OK QmWs5ym3Z9HtbktL7iwH9p6eStkRwHvq8JfMxRHT2Tzyde\n",
      "200 OK QmWvoQPuvmwNU93MK2QQvLqMkvtcjuKaDM36DuDWVsFSbe\n",
      "200 OK QmXoh8jPzEdv8mZ4wm1mNhncRvqHQVj5FwqvuUWgeupJRq\n",
      "200 OK QmYRzjaXVARaEcCLpe8dTmxZFB2BUQ17ivL57gmYx4MsYw\n",
      "200 OK QmZA1BwRuPmFh1BFd5zcP2nTp3whtBaMfr97XHvrpMqQtw\n",
      "200 OK QmZATriLnBLGPVK7WsJDszqhbMV2xYN3KLp1EPSWXAvsAH\n",
      "200 OK QmZpPxgHQ3UQ9xT5pPNAPGXXap9GXR4MrWP4N1Zeuh5TfA\n"
     ]
    }
   ],
   "source": [
    "# Dump Vote Dataframe\n",
    "votes_df_list = []\n",
    "for proposal in proposal_json['data']['proposals']:\n",
    "    #print(proposal['state'],proposal['choices'])\n",
    "    if proposal['state'] != 'closed':\n",
    "        continue\n",
    "    else:\n",
    "        votes_for_proposals_df = make_vote_df_from_proposal_id(proposal['id'])\n",
    "        votes_df_list.append(votes_for_proposals_df)\n",
    "        \n",
    "all_votes_df = pd.concat(votes_df_list,ignore_index=True)\n",
    "all_votes_df['created'] = pd.to_datetime(all_votes_df['created'],unit='s') #Convert to datetime\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a49fd2c-f931-4045-8e03-9ebabf90391d",
   "metadata": {},
   "source": [
    "## Cleaning Vote Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a013d1cd-084e-4fcd-ac3c-1f8cbb41c71a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_zero_vp_proposals(all_votes_df):\n",
    "    vp_sum_df = all_votes_df.groupby('proposal')['vp'].sum() \n",
    "    zero_vp_proposals = vp_sum_df[vp_sum_df==0].index\n",
    "    all_votes_df = all_votes_df[~all_votes_df['proposal'].isin(zero_vp_proposals)]\n",
    "    return all_votes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1aa1dff8-0402-442a-8053-8308280d195a",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_votes_df = remove_zero_vp_proposals(all_votes_df) \n",
    "all_votes_df.to_json('all_votes_df.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca6699c8-2272-4857-920a-db032d8d6e02",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c0bb06b8-365b-45aa-80a5-54cc136346fd",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82bfff50-4059-4809-bdf0-4e490bf90bbc",
   "metadata": {},
   "source": [
    "###  What percentage of proposals are unanimous?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c3f8e7bf-7719-487e-a930-5dbf39de833c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZAAAAEWCAYAAABIVsEJAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAow0lEQVR4nO3de7wcdX3/8dc7Jydywu0QiZaEhABCVEQBD7fGWqDaAIqkaAuKCt4oPy/VatFgraDVkhZbr1ikVhFFEBEjii3yMOIFipAY5GoUIUBOUBIggBglCZ/fH/PdZM5md8+czc45e3k/H4/zOLuzM7Of7+zsfGa+3+9+RxGBmZnZWE2a6ADMzKwzOYGYmVlTnEDMzKwpTiBmZtYUJxAzM2uKE4iZmTXFCcQKk/QRSWsl/WaiYzGbCJJWSnrJRMfRLpxAWkzSiyRdL+lRSQ9Luk7Swdu4zlMl/aRq2oWSPrJt0Y4phlnAe4DnRsSf1Hj9CEnXpseRm36tpD9I+l1KPldI2m284h5NrW07ju99hKSn0rap/H17ImKpRdIUSf8uaVWK7R5JH5/ouGqRdHtuG27K7XO/k/T+OsvMkRSSJjf5nmenv837fq9xAmkhSTsB3wE+DUwDZgIfAv44kXHV0sSXZg/goYh4sIm3e3tE7ADsCwwCYz4ISepr4n07weqI2CH3d9xYV1DitjkTGAIOAXYEjgSWt/INmj14V4uI/SrbEPgxaZ9Lf//SivewrTmBtNa+ABFxSURsioj1EfG9iLilMoOkt0i6U9Ljku6QdFCavlDSr3PT/ypNfw5wPnB4OptaJ+k04GTgvfmzVkkzJH1D0pp0tvh3ufc9W9Llkr4i6THg1OrgJe0s6aK0/L2SPiBpUrpkvwaYkd7vwmY2TkQ8DHwDeF56v2dLuiZdqa2Q9De5WC6U9J+SvivpCeBISbPSFcwaSQ9J+kxu/jem7fqIpKsl7ZF7LSSdLulX6fXzlNlq26b5XyZpuaTHJN0v6eyq7fT6tH0ekvRPylVrpO1V+SwfknSZpGlj3VaSnpOu3tals+tXNNo2VcueJGlp1bS/l3Rlenxs2scelzQs6R/qhHEw8M2IWB2ZlRFxUW6dNT+PtA0+kLbRg2mf2jm9Vjnrf5Ok+4AlaXrNzy99Th9P63lU0i2SnjeG7Vg3FuBH6f+69PkfLmlvSUtSedZKuljSYNH36zkR4b8W/QE7AQ8BXwKOAXapev2vgWGyL6aAZwF75F6bQZbUTwSeAHZLr50K/KRqXRcCH8k9nwQsAz4ITAH2Au4G5qfXzwY2AAvSvAM14r8I+BbZ2eYc4JfAm9JrRwCrmtgm1wJvTo93JTtgfBnYHrgfeAMwGTgIWAvslyvfo8C8FO/2wM/Jrl62B7YDXpTmXQDcBTwnresDwPW5GILsynAQmA2sAY5usG2PAPZP7/t84LfAgvTac4HfAS9K2/ljabu+JL3+LuAGYHfgacDngEvqbJua2xToT+V5f3qPo4DHgbl1ts12VctPTfPvk5t2E3BSevwA8Gfp8S7AQXXi+wBwH/DWtD2Ue62vwefxxhT/XsAOwBXAl9Nrc9LncVFabqDR5wfMJ9uvB8m+M88hfS8K7nNFYpmcW/ZZwEvTZzedLMl8Ivf6yspn7b9wAmn5Bs128AuBVcBG4Ergmem1q4F3FlzPzcDx6fGpjJ5ADgXuq5rnTOCL6fHZwI8avF8fWVXbc3PT/ha4Nj0+guYTyO+BdWTJ8+L0xTwR+HHVvJ8DzsqV76Lca4eTHfgn13iP/yEluvR8UnrPPdLzIB3c0vPLgIX1tm2N9X8C+Hh6/EFyCYHsYP0kWxLIncBf5F7fjSzB1Ir7COCptG0qf38D/BnwG2BSbt5LgLNrbZs6MX8F+GB6vA9ZQpmant+XPtudRllHH/A24Lq0b6wGTinweXwfeGvu+dzKNmDLQXuvIp8fWfL8JXBYfnsU2OfePIZYtipDbv4FwPLc85U4gWz+cxVWi0XEnRFxakTsTlZVM4PsAAQwC/h1reVStcjNqcpiXVp21zG89R5kVUzrcut4P/DM3Dz3N1h+V7Kz3Xtz0+4la8fZVn8XEYMRMTMiTo6INSneQ6viPRnIN9Dn450F3BsRG2usfw/gk7n1PEx2tpqPPd9z7PdkZ6M1STpU0g9S1cyjwOls+Sxm5OOKiN+TXXXmY/lmLpY7gU2M/BzyVqdtU/m7rPIeEfFUbr7qz6LRZwnwVeDV6fFrgMUpVoBXAscC90r6oaTDa60gsmrY8yJiHtkVwEeBL6Sqv0afxwy23o8mU39frPv5RcQS4DPAecBvJV2grK2xqCKxbCbpGZIuTVV7j5El4rF8D3uKE0iJIuIXZGeLlTrb+4G9q+dL9b3/BbwdeHpEDAK3kX2JIDtL2mr1Vc/vB+6pOhjtGBHHNlgmby3ZmdkeuWmzya4aynA/8MOqeHeIiP+Xmyeq5p+t2o2u9wN/W7WugYi4vkActbbJV8muHGdFxM5k7SSVz+IBsuopACQNAE+viuWYqli2i4ixbMfVwCxJ+e9n9Wcx2jDa3wN2lXQAWSL56uYFI26KiOOBZwCLya7IGoqsPe884BGyarxGn8dqtt6PNpJVBdaKv+HnFxGfiogXAvuRtTOeMVq8BWOptQ3PSdOfHxE7Aa9ly2dvVZxAWkhZo/B7JO2ens8i+/LekGb5PPAPkl6YGgeflZLH9mQ77Zq03BvYknQg29l3lzSlatpeuec3Ao9Jep+kAUl9kp6ngl2II2IT2YHko5J2THG9m+wMrAzfAfaV9DpJ/env4HR2W8uNZAfvRZK2l7SdpHnptfOBMyXtB5s7A/x1wThqbdsdgYcj4g+SDiE7g6+4HDhO0p+mZT7EyAPM+WTbsNIIPF3S8QVjqfgpWRvYe9N2OQI4Dri06ArSlcHlwLlkPQKvSfFMkXSypJ0jYgPwGNkV0lYkvUtZF9UBSZMlnUK2bZbT+PO4BPh7SXtK2gH4F+Brda5WoMHnl/aJQyX1p23yh3rx1tEoljVkVYj579GOZG1c6yTNZGzJquc4gbTW42RtET9V1jvmBrIrifcARMTXyaoBvprmXQxMi4g7gH8H/o/sgLY/Wb1zxRLgduA3ktamaf8NPDdd9i9OCeA44ADgHrIris8DO1PcO8i+pHcDP0lxfmEMyxcWEY8DfwmcRHaW+BvgX8kaL2vNXynfs8jq8FeRtaMQEd9My16aqh1uI+vEUEStbftW4MOSHidr89h8hh4Rt5Ntp0vJDqCPAw+ypav2J8muXr6Xlr+BbJ8oLCKeBF6RyrAW+Czw+nRFOxZfBV4CfL3q4P06YGXaVqeTnWXXsp5sv/xNiuNtwCsj4u5GnwfZPvNlsgboe8gO+u9oUN5Gn99OZFfnj5BVPz1E1nGhqLqxpCq9jwLXpe/RYWQnBAeRdVK4iqzR3epQxGhXwmZWTzqrXUfW4+meCQ7HbFz5CsRsjCQdJ2mqpO3JzoZvJeudY9ZTnEDMxu54smq31WRdZE8KX8pbD3IVlpmZNcVXIGZm1pSWDGQ2nnbdddeYM2fORIdhZtZRli1btjYiprdynR2XQObMmcPSpUtHn9HMzDaTdO/oc42Nq7DMzKwpTiBmZtYUJxAzM2uKE4iZmTXFCcTMzJpSWi8sSV8AXg48GBFb3YJSksgGnjuW7P4Mp0bEz8qIZfHyYT707dt55Pcbylh9UyR4xg5T+O3jT9Z8fZLgqdxvPKf2T+LJTcHGp0b+8HNwoJ/1Gzbxx41PMVb9k2BDjcWm9k/iX054PgsOzG4/sXj5MGdecQvra8w8c3CAM+bP3TxvxeLlw5x95e2sW79lm4tsyOHqZRYvH+bcq1ewet16Bqf2EwGPrt/AzgP9SLDu9xtGTJ+RW/4Di2/lkp/ez6YI+iRefegsPrJg/60+cwkioE/aPO+miK1iya9vkuBpkyfxhw1PjXjPfMzD69aPWNecpw9w3a8fHrEt+gSb0scmwZ/uNY2f3bdu8/acJHjNobM3x129LfLbsLqMlXnz8dWbbtZqpf0SXdKLyYZFvqhOAjmWbFTMY8lGK/1kRIw6aunQ0FCMpRvv4uXDnHH5z9mwyb+4HwsBOw/0jzh41TPQ38c5J+w/4uB6xtd/zoan6m/zyjIAZ15xK+s3jGWE7mz5g2bvvNXBGmDe3tO4ceUjhT/zSixfX3pfzfW1KubR7POM7Vn1yB8KrXfe3tP42X2Pjph3oL+PV75wJt9YNrzV9PznY71J0rKIGGrpOsscykTSHOA7dRLI58hul3pJer4COCIiHmi0zrEmkHmLljC8bv2Y4raxmzk4wHULjwKKb/OZgwMAbfH57DK1v9AVajvFXEvlaqha/vOx3lRGApnIHxLOZORtLVelaVslEEmnAacBzJ49e0xvsrpNv+jdJr+di27zdvpsilZvtlPMtdRKHtD+cVtnmshG9Fq3iay590fEBRExFBFD06eP7Zf4M9IZo5Urv52LbvMZgwMd9/m0e8yqc/PVdo7ZOtdEJpBVwKzc893JhsduqTPmz6W/z7c0LtNAfx9nzJ+7+fkZ8+fSP6nxNq8sc8b8uQz09zX1nvP2nlbztXl7TxvTZz7Q38fgQH+h+bYl5iKKRl2vjIKttn3152PWKhOZQK4EXp/uDX4Y8Oho7R/NWHDgTM591QvYZeroB4jxJMEzd5wy+oxlxlBnep+2PgjVM3NwYKsG2gUHzuTcv37BVgdl1VhmwYEzOeeE/Zk5OIDIeiSNZnCgn3NO2J+L33I4rz1sNn3ptLtP4rWHzebitxy+1WdeOTPPz5uP5exX7FczKfRPyuKuF3P1uuoltSIG+vs4+bDZm7fFLlP7t9qG+TJuP2XrGuinAnbYbvLmddT6fMxapcxeWJcARwC7kt3n+yygHyAizk/deD8DHE3WjfcNETFq6/hYG9GrHfjh7xWq7x4s2AOp1nJ/3PhUzV4wQMPulVl32ca9e/oknoqoXddXxyTB4XtNY+VD6wt3+WzU9XmXqf2cddx+pRyU9lx4Vd2y1esy3Cqt7v46WmeCwYF+tn/a5FHfr15c9baVgHsWvazpuK07dVwvrDJsawIpcpCuHPAr/fyLapQoYOuun9XdK4v0XqocHOrNu8vUfqZOGf2gVESjeMrqGlrvPTuxF1Gjfa3o9qu1jtH2z07cVla+buuFNSEqX9j8Af7IZ0/nB79YU/OgW/3lzf8YrtFy1QeGeYuWbHUgWb9hE+devWLzvEV6ylQaQ8+YP7fmgaWVVwaNkll17K1Sr1ydWIef39eqf3BYNLGfe/WKuvtNN20r60w9l0CAzfXYReaDxtVORdVLDvnpMwYHGh608weHVsZWT73fFFSU0TV0PMo1mlZWZRXd1+pptN+0w7ay3taTCWQstvUAUFEvOeS7V9Y6o6w3/EcrY6unUfKA8rqGll2uRqqrjIbXrefMK27dHNd4G22/mchtZebBFHMWLx9m3qIl7LnwKuYtWsLi5cMtW3etrp/V1Q3VPZJmDg7w8RMPYOWil3HdwqPG/UAxs0GC6NaqkkZVRhOhyH5jNlF8BZI0e+ZZtLqjaHVDO51R1roignJ7YU20IlWN46Wyb63fsKmp9hOzsjmBJI3OPOt9WceadNopORTRi3XsRaoax0P1vrUpYvOVRzdvf+ssTiBJM2eezSSdTtNpSW9btUvPpl7Yt6zzOYEkYz3zXLx8uG6PqU4YuM73jKitrKuusW7vdqpKM6unZxLIaF/gsZx5VqoX6mn3gevaradRu2n1VVcz27tdqtLMGumJXliVL/DwuvUEW77A+V5WtXpA1fulcK3qhYrxru5opudYu/U06nbNbG/3vrJO0BNXIEXrk4ueeTaqRhjPgeuavZJw9cj4amZ792IHBus8PZFAWn3ArFe9MHNwYFy/4M02tLp6ZHw1u717rQODdZ6eqMKq90Vt9oBZVvXCWKujmk2Mrh4ZX97e1q16IoG0+gs8lvaSooq001RrNjGWEb/V5+1t3apnhnNv926rzQxj3mio73Yqm5lNPA/nvg3avT7ZDa1m1ml6JoG0Oze0mlmn6Yk2kE7ghlYz6zS+AmkTro4ys07jBNJG2qk6qt07HZjZxHMCsa14rKzu4xMCK4MTSA8a7WDiocS7i08IrCxuRO8xRX6w6LGyuosHz7SyOIH0mCIHk1YP/WITyycEVhYnkB5T5GDiLsXdxScEVhYnkB5T5GDisZu6i08IrCxuRO8xRz57OhffcB/5EdAG+vs48tnTmbdoyYiG9XpjcFln8W+MrCxOID1k8fJhvrFseETyEHDQ7J35xrLhCe+l466m5Wmn3xhZ93AVVg+p1YAewA13PzLhvXSaGc7ezCaWE0gPqdeAvqnOkP7j2UvHXU3NOo+rsErWTtUy9Ub87ZNqJpHx7KXjrqZmncdXICVqt2qZer1xXn3orAnvpeOupmadxwmkRONZLVPkfur1uud+ZMH+E95t111NzTpPqVVYko4GPgn0AZ+PiEVVr+8MfAWYnWL5WER8scyYxtN4VcuMZayjer1xJrqXjruamnWe0hKIpD7gPOClwCrgJklXRsQdudneBtwREcdJmg6skHRxRDxZVlzjqdm7DI5Vtwx+ONFJzMzGpswqrEOAuyLi7pQQLgWOr5ongB0lCdgBeBjYWGJM42q8qmXcAG1mE6HMBDITuD/3fFWalvcZ4DnAauBW4J0R8VT1iiSdJmmppKVr1qwpK96WG68hQdwAbWYTocw2ENWYVt1XdD5wM3AUsDdwjaQfR8RjIxaKuAC4AGBoaKj2jxba1HhUy5wxf+6INhBwA7SZla/MK5BVwKzc893JrjTy3gBcEZm7gHuAZ5cYU1fy4IdmNhHKvAK5CdhH0p7AMHAS8Jqqee4D/gL4saRnAnOBu0uMqWu5AdrMxltpCSQiNkp6O3A1WTfeL0TE7ZJOT6+fD/wzcKGkW8mqvN4XEWvLisnMzFqn1N+BRMR3ge9WTTs/93g18JdlxmBmZuXwL9HNzKwpTiBmZtYUJxAzM2uKE4iZmTXFCcTMzJriBGJmZk1xAjEzs6Y4gZiZWVOcQMzMrClOIGZm1pRShzLpdYuXD/sWrWbWtZxASjKW+5SbmXUiV2GVpNF9ys3MuoETSEl8n3Iz63ZOICXxfcrNrNs1TCCS+iR9ZbyC6SZnzJ/LQH/fiGm+T7mZdZOGjegRsUnSdElTIuLJ8QqqG1Qayt0Ly8y6VZFeWCuB6yRdCTxRmRgR/1FWUN2iG+9T7q7JZlZRJIGsTn+TgB3LDcfambsmm1neqAkkIj4EIGnH7Gn8rvSorC016prsBGLWe0bthSXpeZKWA7cBt0taJmm/8kOzduOuyWaWV6Qb7wXAuyNij4jYA3gP8F/lhmXtyF2TzSyvSALZPiJ+UHkSEdcC25cWkbUtd002s7wijeh3S/on4Mvp+WuBe8oLydqVuyabWV6RBPJG4EPAFen5j4A3lBZRG3LX1S2a7ZrsbWjWfeomEEknRMQVEfGIpLMi4pHxDKxduOvqtvM2NOtOjdpAPpB7/P2yA2lXHlV323kbmnWnRglEdR73FHdd3XbehmbdqVEbyICkA8mSzHbp8eZEEhE/Kzu4djBjcIDhGgc6d10tztvQrDs1ugJ5APgP4GPAb9Ljf09/Hys/tPbgrqvbztvQrDvVvQKJiCPHM5B25a6r287b0Kw7KSImOoYxGRoaiqVLl050GGZmHUXSsogYauU6fUdCMzNrSqkJRNLRklZIukvSwjrzHCHpZkm3S/phmfGYdYPFy4eZt2gJey68inmLlrB4+fBEh2Q9atRfoksScDKwV0R8WNJs4E8i4sZRlusDzgNeCqwCbpJ0ZUTckZtnEPgscHRE3CfpGc0Xxaz7+UeZ1k6KXIF8FjgceHV6/jhZYhjNIcBdEXF3uh3upcDxVfO8BrgiIu4DiIgHC0Vt1qP8o0xrJ0USyKER8TbgDwBpSJMpBZabCdyfe74qTcvbF9hF0rXpPiOvr7UiSadJWipp6Zo1awq8tVl38o8yrZ0USSAbUnVUAEiaDjxVYLlav16v7vI1GXgh8DJgPvBPkvbdaqGICyJiKCKGpk+fXuCtzbqT78li7aTIaLyfAr4JPEPSR4FXMXKcrHpWAbNyz3cnu7d69TxrI+IJ4AlJPwJeAPyywPqb5pFhrdXGa586Y/7cEW0g4B9l2sQpck/0iyUtA/6C7KpiQUTcWWDdNwH7SNoTGAZOImvzyPsW8BlJk8mqxQ4FPj6G+MfMjZDWauO5T/lHmdZOivTCmgY8CFySm9YfERsaLRcRGyW9Hbga6AO+EBG3Szo9vX5+RNwp6X+BW8iqxT4fEbc1X5zRNWqE9JfQmjHe+1Sz92Qxa7UiVVg/I6uKeoTsCmQQeEDSg8BbImJZvQUj4rvAd6umnV/1/Fzg3LGF3Tw3QlqreZ+yXlWkEf1/gWMjYteIeDpwDHAZ8FayLr4dxY2Q1mrep6xXFUkgQxFxdeVJRHwPeHFE3AA8rbTISuKRYa3VvE9ZrypShfWwpPeR/RAQ4ETgkdS1t0h33rbiRkhrNe9T1qtGHY1X0q7AWcCLyNpAfgJ8CHgUmB0Rd5UdZJ5H4zUzG7syRuMt0o13LfCOOi+Pa/IwM7P2UaQb73TgvcB+wHaV6RFxVIlxmZlZmyvSiH4x8AtgT7Kqq5VkPxI0M7MeViSBPD0i/hvYEBE/jIg3AoeVHJeZmbW5Ir2wKr84f0DSy8jGs9q9vJDMzKwTFEkgH5G0M/Ae4NPATsC7ygzKzMzaX5EE8khEPErWbfdIAEnzSo3KzMzaXpE2kE8XnGZmZj2k7hWIpMOBPwWmS3p37qWdyEbXNTOzHtaoCmsKsEOaZ8fc9MfIbiplZmY9rG4CiYgfAj+UdGFE3DuOMZmZWQco0oj+NEkXAHPy8/uX6GZmva1IAvk6cD7weWDTKPP2HN9f3cx6VZEEsjEi/rP0SDqQ769uZr2sSDfeb0t6q6TdJE2r/JUeWQdodC9sM7NuV+QK5JT0/4zctAD2an04ncX3wjazXlbkfiB7jkcgnWjG4ADDNZKF74VtZr1g1CosSVMlfSD1xELSPpJeXn5o7c/3wjazXlakDeSLwJNkv0oHWAV8pLSIOsiCA2dyzgn7M3NwAAEzBwc454T93YBuZj2hSBvI3hFxoqRXA0TEekkqOa6OseDAmU4YZtaTilyBPClpgKzhHEl7A38sNSozM2t7Ra5AzgL+F5gl6WJgHnBqmUGZmVn7K9IL6xpJPyO7ja2Ad0bE2tIjMzOztlakF9Zfkf0a/aqI+A6wUdKC0iMzM7O2VqQN5Kx0R0IAImIdWbWWmZn1sCIJpNY8RdpOzMysixVJIEsl/YekvSXtJenjwLKyAzMzs/ZWJIG8g+yHhF8DLgPWA28rMygzM2t/DauiJPUB34qIl4xTPGZm1iEaXoFExCbg95J2bmblko6WtELSXZIWNpjvYEmbJE3YvdYXLx9m3qIl7LnwKuYtWsLi5cMTFYqZWUco0hj+B+BWSdcAT1QmRsTfNVooXb2cB7yUbPysmyRdGRF31JjvX4Grxxh7y/jGUGZmY1ckgVyV/sbqEOCuiLgbQNKlwPHAHVXzvQP4BnBwE++xTSq3o601JHvlxlBOIGZmtRX5JfqX0lhYsyNiLLfamwncn3u+Cjg0P4OkmcBfAUfRIIFIOg04DWD27NljCKG+6quOWnxjKGuFyonK6nXrmTE4wBnz5/rExLpCkV+iHwfcTDYeFpIOkHRlgXXXGrE3qp5/AnhfamupKyIuiIihiBiaPn16gbceXa3b0VbzjaGa4/akLSonKsPr1hNsqR7t5W1i3aNIN96zyaqj1gFExM1AkbsUrgJm5Z7vDqyummcIuFTSSuBVwGfHa5iU0a4ufGOo5viAOVKtE5VK9ahZpyuSQDbmhzJJqq8karkJ2EfSnpKmACcBI65cImLPiJgTEXOAy4G3RsTiAuveZo2uLnxjqOb5gDlSvRMVV49aNyiSQG6T9BqgL93O9tPA9aMtFBEbgbeT9a66E7gsIm6XdLqk07cp6haodzvaT5x4ANctPMrJo0k+YI5U70TF1aPWDYr+En0/sptIfRV4FHhXkZVHxHcjYt+I2DsiPpqmnR8R59eY99SIuLxw5NvIt6Mthw+YI9U7UXH1qHWDur2wJG0HnA48C7gVODxdVXQN34629c6YP3er3m29fMCs7F/uhWXdqFE33i8BG4AfA8cAz6HglYf1Lh8wt+YTFetWjRLIcyNifwBJ/w3cOD4hWafzAdOsNzRqA9lQedBtVVdmZrbtGl2BvEDSY+mxgIH0XEBExE6lR2dmZm2rbgKJiL56r5mZmRXpxmtmZrYVJxAzM2uKE4iZmTWlyP1AzKxDeOh4G09OIGZdwnfWtPHmKiyzLuGRkG28OYGYdQmPhGzjzQnErEt4JGQbb04gZl3CQ8fbeHMjulmX8EjINt6cQMy6iEdCtvHkKiwzM2uKE4iZmTXFCcTMzJriBGJmZk1xAjEzs6Y4gZiZWVOcQMzMrClOIGZm1hQnEDMza4oTiJmZNcVDmfQI36nOzFrNCaQH+E51ZlYGV2H1AN+pzszK4ATSA3ynOjMrgxNID/Cd6sysDE4gXWLx8mHmLVrCnguvYt6iJSxePrz5Nd+pzszKUGoCkXS0pBWS7pK0sMbrJ0u6Jf1dL+kFZcbTrSqN5MPr1hNsaSSvJJEFB87knBP2Z+bgAAJmDg5wzgn7uwHdzLZJab2wJPUB5wEvBVYBN0m6MiLuyM12D/DnEfGIpGOAC4BDy4qpWzVqJK8kCd+pzsxarcxuvIcAd0XE3QCSLgWOBzYnkIi4Pjf/DcDuJcbTtdxIbtbd2vV3XGVWYc0E7s89X5Wm1fMm4H9qvSDpNElLJS1ds2ZNC0PsDm4kN+teo1VRT6QyE4hqTIuaM0pHkiWQ99V6PSIuiIihiBiaPn16C0PsDm4kN+te7fw7rjKrsFYBs3LPdwdWV88k6fnA54FjIuKhEuPpWpVL2Xa8xDWzbdPOVdRlJpCbgH0k7QkMAycBr8nPIGk2cAXwuoj4ZYmxdD03kpt1pxmDAwzXSBbtUEVdWhVWRGwE3g5cDdwJXBYRt0s6XdLpabYPAk8HPivpZklLy4rHzKwTtXMVtSJqNku0raGhoVi61HnGzHpHK3phSVoWEUOtjMuj8ZqNs3btkmntq12rqJ1AzMaRh9a3buKxsMzGUTt3yTQbKycQs3HUzl0yzcbKCcRsHHnUAOsmTiBm46idu2SajZUb0a0jdEvPJY8aYN3ECcTaXrf1XGrXLplmY+UqLGt77rlk1p6cQKztueeSWXtyArG2555LZu3JCcTannsumbUnN6Jb23PPJbP25ARiHcE9l8zaj6uwzMysKU4gZmbWFCcQMzNrihOImZk1xQnEzMya4gRiZmZNcQIxM7Om+Hcgo+iWYcTNzFrNCaSBbhtG3MyslVyF1YCHETczq88JpAEPI25mVp8TSAMeRtzMrD4nkAY8jLiZWX1uRG/Aw4ibmdXnBDIKDyNuZlabq7DMzKwpTiBmZtYUJxAzM2uKE4iZmTXFCcTMzJqiiJjoGMZE0hrg3iYX3xVY28JwJlq3lQe6r0zdVh7ovjJ1W3mgdpn2iIjprXyTjksg20LS0ogYmug4WqXbygPdV6ZuKw90X5m6rTwwfmVyFZaZmTXFCcTMzJrSawnkgokOoMW6rTzQfWXqtvJA95Wp28oD41SmnmoDMTOz1um1KxAzM2sRJxAzM2tKTyQQSUdLWiHpLkkL2yCeL0h6UNJtuWnTJF0j6Vfp/y65185Msa+QND83/YWSbk2vfUqS0vSnSfpamv5TSXNyy5yS3uNXkk5pUXlmSfqBpDsl3S7pnV1Qpu0k3Sjp56lMH+r0MqX19klaLuk7XVKelSmWmyUt7ZIyDUq6XNIv0nfq8LYtU0R09R/QB/wa2AuYAvwceO4Ex/Ri4CDgtty0fwMWpscLgX9Nj5+bYn4asGcqS1967UbgcEDA/wDHpOlvBc5Pj08CvpYeTwPuTv93SY93aUF5dgMOSo93BH6Z4u7kMgnYIT3uB34KHNbJZUrrfjfwVeA7nb7fpXWvBHatmtbpZfoS8Ob0eAow2K5lmrCD6Hj9pQ14de75mcCZbRDXHEYmkBXAbunxbsCKWvECV6cy7Qb8Ijf91cDn8vOkx5PJfpGq/Dzptc8Bry6hbN8CXtotZQKmAj8DDu3kMgG7A98HjmJLAunY8qR1rWTrBNKxZQJ2Au4hdXBq9zL1QhXWTOD+3PNVaVq7eWZEPACQ/j8jTa8X/8z0uHr6iGUiYiPwKPD0ButqmXQ5fCDZGXtHlylV99wMPAhcExGdXqZPAO8FnspN6+TyAATwPUnLJJ3WBWXaC1gDfDFVNX5e0vbtWqZeSCCqMa2T+i7Xi79RuZpZZptJ2gH4BvCuiHis0ax14mirMkXEpog4gOzM/RBJz2swe1uXSdLLgQcjYlnRRerE0BblyZkXEQcBxwBvk/TiBvN2Qpkmk1Vv/2dEHAg8QVZlVc+ElqkXEsgqYFbu+e7A6gmKpZHfStoNIP1/ME2vF/+q9Lh6+ohlJE0GdgYebrCubSapnyx5XBwRV3RDmSoiYh1wLXA0nVumecArJK0ELgWOkvSVDi4PABGxOv1/EPgmcEiHl2kVsCpd7QJcTpZQ2rNMraiHbOc/sox+N1kDU6URfb82iGsOI9tAzmVkI9m/pcf7MbKR7G62NJLdRNawW2kkOzZNfxsjG8kuS4+nkdWv7pL+7gGmtaAsAi4CPlE1vZPLNB0YTI8HgB8DL+/kMuXKdgRb2kA6tjzA9sCOucfXkyX5ji1TWvePgbnp8dmpPG1Zpgk9iI7XH3AsWc+gXwP/2AbxXAI8AGwgy/pvIquD/D7wq/R/Wm7+f0yxryD1pEjTh4Db0mufYcvIAtsBXwfuIuuJsVdumTem6XcBb2hReV5Edql7C3Bz+ju2w8v0fGB5KtNtwAfT9I4tU27dR7AlgXRsecjaC36e/m4nfbc7uUxpvQcAS9O+t5jsYN6WZfJQJmZm1pReaAMxM7MSOIGYmVlTnEDMzKwpTiBmZtYUJxAzM2uKE4h1LUn/qGwk3VvSaK2HTmAsP00x3CdpTXp8c34k1Ny8p0qaUWCdF0p6VSkBmxUweaIDMCuDpMPJfvh3UET8UdKuZD8k3ZZ1To5s7KAxi4hD0zpOBYYi4u0NZj+VrP9+O46YYLaZr0CsW+0GrI2IPwJExNpIw15IOljS9cru9XGjpB2V3f/ji+n+CcslHZnmPVXS1yV9m2zQvu2V3c/lpjTf8Wm+/dK6bk5XPPuMFqCkAyTdkOb/pqRd0hXFEHBxWteApA+m97tN0gWV+zqYTTQnEOtW3wNmSfqlpM9K+nMASVOArwHvjIgXAC8B1pMN70BE7E82rPWXJG2X1nU4cEpEHEX2q98lEXEwcCRwbhot9XTgk5ENvjjEyJFQ67kIeF9EPB+4FTgrIi4n+xXyyRFxQESsBz4TEQdHxPPIhlV5+bZtGrPWcAKxrhQRvwNeCJxGNjz211L10VzggYi4Kc33WKqWehHw5TTtF8C9wL5pdddExMPp8V8CC9Mw79eSDQsxG/g/4P2S3gfskQ78dUnamWysrR+mSV8iu9FYLUemNpRbye7lsV/R7WBWJreBWNeKiE1kB/lr08H3FLIbQ9Uav6dRtdATVfO9MiJWVM1zp6SfAi8Drpb05ohY0nTwlTfLroI+S9Zucr+ks8mSltmE8xWIdSVJc6vaIQ4gu6r4BTBD0sFpvh3TkNY/Ak5O0/Ylu6qoThKQ3c3tHZV2CEkHpv97AXdHxKeAK8kGY6wrIh4FHpH0Z2nS64DK1cjjZLcGhi3JYm2634p7XVnb8BWIdasdgE9LGgQ2ko0uelpEPCnpxPTaAFn7x0vIzvLPT1cqG4FTU++t6vX+M9md/W5JSWQlWZvEicBrJW0AfgN8uECMp6T3nEo2DPcb0vQL0/T1ZO0v/0XWRrKSbIhus7bg0XjNzKwprsIyM7OmOIGYmVlTnEDMzKwpTiBmZtYUJxAzM2uKE4iZmTXFCcTMzJry/wE7zgk7ust2ggAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# What percentage of proposals are unanimous?\n",
    "proposals_df['percentage_for'] = proposals_df['scores'].map(lambda x: x[0])/proposals_df['scores_total']\n",
    "plt.scatter(proposals_df['scores_total'],proposals_df['percentage_for'])\n",
    "plt.xlabel('Scores Total')\n",
    "plt.ylabel('Percentage For')\n",
    "plt.title('Scatter of \"Percentage For vs Scores Total\"')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "974566f7-b4e4-4a6c-bd38-54dff8c9bc27",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e9d289c1-a7d9-4808-9900-5ce0a6b0aa2e",
   "metadata": {},
   "source": [
    "# Deciding Vote Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "8103a5f7-e5a4-4b53-88f1-f63b1894eaba",
   "metadata": {},
   "outputs": [],
   "source": [
    "def winning_threshold(choice,category):\n",
    "    if category == 'DG2':\n",
    "        if choice == 1:\n",
    "            return .6\n",
    "        else:\n",
    "            return .4\n",
    "    else:\n",
    "        return .5\n",
    "    \n",
    "def is_deciding_vote(row):\n",
    "    '''\n",
    "    Assuming that a vote is ALREADY on the winning side,\n",
    "    A vote is a considered a deciding vote if the following statement holds:\n",
    "    'If the vote had switched sides, then it would have still been on the winning side.'\n",
    "    '''\n",
    "    vp = row['vp']\n",
    "    choice = row['choice']\n",
    "    category = row['category']\n",
    "    scores_total = row['scores_total']\n",
    "    coalition_vp = row['coalition_vp']\n",
    "    reached_quorum = row['reached_quorum']\n",
    "    \n",
    "    if not reached_quorum:\n",
    "        return \"no quorum\"\n",
    "    else:\n",
    "        #old_choice = choice\n",
    "        #old_my_team_score= coalition_vp\n",
    "        #old_enemy_team_score = scores_total-coalition_vp\n",
    "\n",
    "        new_choice = (choice) % 2 + 1 # 1 goes to 2, 2 goes to 1\n",
    "        new_coalition_vp = scores_total-(coalition_vp-vp)\n",
    "\n",
    "\n",
    "        new_percentage = new_coalition_vp/scores_total\n",
    "\n",
    "        new_wins = new_percentage > winning_threshold(new_choice,category)\n",
    "        \n",
    "        if new_wins:\n",
    "            return 'Was Deciding Voter'\n",
    "        else:\n",
    "            return 'Not Deciding Voter'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fefc1e9c-3c5c-47b1-aceb-0fa6d4c0b1a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge\n",
    "merged_df = pd.merge(all_votes_df,proposals_df,left_on='proposal',right_on='proposal_id',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "03ea9267-98ea-46e4-af3f-cef977cad003",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create columns\n",
    "merged_df['coalition_vp'] = merged_df.apply(lambda row: row.scores[row.choice-1],axis=1)\n",
    "merged_df['coalition_contribution'] = merged_df['vp']/merged_df['coalition_vp']\n",
    "merged_df['is_winning_coalition'] = merged_df['choice']==merged_df['result']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "27ca0211-ca87-4a50-8c22-1970b36807a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/gk/ws1y65fn3l3_f44tyj7ms26h0000gn/T/ipykernel_66483/1840099.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  winning_coalition_df['is_deciding_vote'] = winning_coalition_df.apply(is_deciding_vote,axis=1)\n"
     ]
    }
   ],
   "source": [
    "# Winning Coalition DF has all of the data denormalized and in one giant DF, with all of the processing\n",
    "# steps attached to it.\n",
    "winning_coalition_df = merged_df[merged_df['is_winning_coalition']]\n",
    "winning_coalition_df['is_deciding_vote'] = winning_coalition_df.apply(is_deciding_vote,axis=1)\n",
    "\n",
    "# We ignore the error here since we want to work with the copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "3afcf9ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dump all votes to csv\n",
    "winning_coalition_df.to_csv('all_votes_processed.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7c5b228b-9e40-4d0f-a419-fe82a164cd48",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pivot\n",
    "deciding_vote_df = winning_coalition_df.sort_values(by='coalition_contribution',ascending=False).groupby(['voter','is_deciding_vote']).size()\\\n",
    "                       .reset_index(name='counts')\\\n",
    "                       .pivot_table(index='voter',columns='is_deciding_vote',values='counts',fill_value=0)\\\n",
    "                       .sort_values(by='Was Deciding Voter',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7a99d393-fa69-4259-9f17-ffd78a3118a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "voter_by_vp_df = winning_coalition_df.groupby('voter').agg(mean_vp=('vp', 'mean'),\n",
    "                                                           min_vp=('vp', 'min'),\n",
    "                                                           max_vp=('vp', 'max'),\n",
    "                                                          )\n",
    "#voter_by_vp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "d670cf88-ac51-4b6b-86d8-c51184114649",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_deciding_vote_df = deciding_vote_df.merge(voter_by_vp_df,on='voter',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "af03c266",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dump by_Voter DF to CSV for upload to google sheets\n",
    "merged_deciding_vote_df.to_csv('merged_deciding_vote_by_voter.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d82476f-bbdc-462b-8384-2bbb8a1272b8",
   "metadata": {},
   "source": [
    "### Deciding voters and how much VP they have"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "5883265f-59e8-451d-9643-a914035e45e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Not Deciding Voter</th>\n",
       "      <th>Was Deciding Voter</th>\n",
       "      <th>no quorum</th>\n",
       "      <th>mean_vp</th>\n",
       "      <th>min_vp</th>\n",
       "      <th>max_vp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>voter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0x8627be8D8b6D250c87f802D0c48C26FA807A0857</th>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>187922.0</td>\n",
       "      <td>187922.0</td>\n",
       "      <td>187922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x4a3e950c35c6d9c2d8f5F0a6CC03aF9942134840</th>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>71242.0</td>\n",
       "      <td>44093.0</td>\n",
       "      <td>80640.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x365047213ABe97401e8Bfa2c48D12d2731601F98</th>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>196330.0</td>\n",
       "      <td>123949.0</td>\n",
       "      <td>235515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x4EC7CdF61405758f5cED5E454c0B4b0F4F043DF0</th>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>68307.0</td>\n",
       "      <td>66832.0</td>\n",
       "      <td>68569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xcfc50541c3dEaf725ce738EF87Ace2Ad778Ba0C5</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>50476.0</td>\n",
       "      <td>48639.0</td>\n",
       "      <td>52790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xb647055A9915bF9c8021a684E175A353525b9890</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x9fB64b232dEa3ba6e020F517b2BC8aFd0145880c</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41225.0</td>\n",
       "      <td>41225.0</td>\n",
       "      <td>41225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xB933AEe47C438f22DE0747D57fc239FE37878Dd1</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>31187.0</td>\n",
       "      <td>29522.0</td>\n",
       "      <td>31395.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xaAa604E8f5318373D1785766D4f7310AF6CC4891</th>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>24018.0</td>\n",
       "      <td>22950.0</td>\n",
       "      <td>24768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x000000064730640b7d670408d74280924883064F</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>119.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>119.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xADD4aeE42817461b0c75324684B1cc8A3887ECcc</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>116.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>138.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xACa4AB44dcD2Ec6982bb5238Bf1Daf268C159699</th>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>283.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAD0206f327c23087bc579Ece7A5fdBC82696fDFE</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAD70c33FA39D55aD7d88dfc9186726e6C3758921</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAE0Fd8D80c04bfE9Fa9ADCf2BfAAe82e865dE527</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xADEE57ED85DCd0663f486c9b2588f256883C4556</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xABE4709439529E6fcF9C47dC7DDCbA72B0673F7a</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAE1692D8D045716c948B707550e91DBd2C55169A</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAE934ED0CAA49C06fb4192Cd091e65226fFF2A9b</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xAEB7C6E65AAa0C6939e79D866969b3837867ecC8</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Not Deciding Voter  \\\n",
       "voter                                                            \n",
       "0x8627be8D8b6D250c87f802D0c48C26FA807A0857                   0   \n",
       "0x4a3e950c35c6d9c2d8f5F0a6CC03aF9942134840                  12   \n",
       "0x365047213ABe97401e8Bfa2c48D12d2731601F98                   0   \n",
       "0x4EC7CdF61405758f5cED5E454c0B4b0F4F043DF0                  10   \n",
       "0xcfc50541c3dEaf725ce738EF87Ace2Ad778Ba0C5                   7   \n",
       "0xb647055A9915bF9c8021a684E175A353525b9890                   2   \n",
       "0x9fB64b232dEa3ba6e020F517b2BC8aFd0145880c                   0   \n",
       "0xB933AEe47C438f22DE0747D57fc239FE37878Dd1                   8   \n",
       "0xaAa604E8f5318373D1785766D4f7310AF6CC4891                  29   \n",
       "0x000000064730640b7d670408d74280924883064F                   4   \n",
       "0xADD4aeE42817461b0c75324684B1cc8A3887ECcc                   1   \n",
       "0xACa4AB44dcD2Ec6982bb5238Bf1Daf268C159699                  25   \n",
       "0xAD0206f327c23087bc579Ece7A5fdBC82696fDFE                   0   \n",
       "0xAD70c33FA39D55aD7d88dfc9186726e6C3758921                   1   \n",
       "0xAE0Fd8D80c04bfE9Fa9ADCf2BfAAe82e865dE527                   0   \n",
       "0xADEE57ED85DCd0663f486c9b2588f256883C4556                   0   \n",
       "0xABE4709439529E6fcF9C47dC7DDCbA72B0673F7a                   0   \n",
       "0xAE1692D8D045716c948B707550e91DBd2C55169A                   2   \n",
       "0xAE934ED0CAA49C06fb4192Cd091e65226fFF2A9b                   4   \n",
       "0xAEB7C6E65AAa0C6939e79D866969b3837867ecC8                   0   \n",
       "\n",
       "                                            Was Deciding Voter  no quorum  \\\n",
       "voter                                                                       \n",
       "0x8627be8D8b6D250c87f802D0c48C26FA807A0857                  33          0   \n",
       "0x4a3e950c35c6d9c2d8f5F0a6CC03aF9942134840                   8          0   \n",
       "0x365047213ABe97401e8Bfa2c48D12d2731601F98                   6          0   \n",
       "0x4EC7CdF61405758f5cED5E454c0B4b0F4F043DF0                   3          0   \n",
       "0xcfc50541c3dEaf725ce738EF87Ace2Ad778Ba0C5                   2          0   \n",
       "0xb647055A9915bF9c8021a684E175A353525b9890                   1          0   \n",
       "0x9fB64b232dEa3ba6e020F517b2BC8aFd0145880c                   1          0   \n",
       "0xB933AEe47C438f22DE0747D57fc239FE37878Dd1                   1          0   \n",
       "0xaAa604E8f5318373D1785766D4f7310AF6CC4891                   1         10   \n",
       "0x000000064730640b7d670408d74280924883064F                   0          9   \n",
       "0xADD4aeE42817461b0c75324684B1cc8A3887ECcc                   0          1   \n",
       "0xACa4AB44dcD2Ec6982bb5238Bf1Daf268C159699                   0          8   \n",
       "0xAD0206f327c23087bc579Ece7A5fdBC82696fDFE                   0          1   \n",
       "0xAD70c33FA39D55aD7d88dfc9186726e6C3758921                   0          0   \n",
       "0xAE0Fd8D80c04bfE9Fa9ADCf2BfAAe82e865dE527                   0          1   \n",
       "0xADEE57ED85DCd0663f486c9b2588f256883C4556                   0          1   \n",
       "0xABE4709439529E6fcF9C47dC7DDCbA72B0673F7a                   0          1   \n",
       "0xAE1692D8D045716c948B707550e91DBd2C55169A                   0          0   \n",
       "0xAE934ED0CAA49C06fb4192Cd091e65226fFF2A9b                   0          5   \n",
       "0xAEB7C6E65AAa0C6939e79D866969b3837867ecC8                   0          1   \n",
       "\n",
       "                                             mean_vp    min_vp    max_vp  \n",
       "voter                                                                     \n",
       "0x8627be8D8b6D250c87f802D0c48C26FA807A0857  187922.0  187922.0  187922.0  \n",
       "0x4a3e950c35c6d9c2d8f5F0a6CC03aF9942134840   71242.0   44093.0   80640.0  \n",
       "0x365047213ABe97401e8Bfa2c48D12d2731601F98  196330.0  123949.0  235515.0  \n",
       "0x4EC7CdF61405758f5cED5E454c0B4b0F4F043DF0   68307.0   66832.0   68569.0  \n",
       "0xcfc50541c3dEaf725ce738EF87Ace2Ad778Ba0C5   50476.0   48639.0   52790.0  \n",
       "0xb647055A9915bF9c8021a684E175A353525b9890  100000.0  100000.0  100000.0  \n",
       "0x9fB64b232dEa3ba6e020F517b2BC8aFd0145880c   41225.0   41225.0   41225.0  \n",
       "0xB933AEe47C438f22DE0747D57fc239FE37878Dd1   31187.0   29522.0   31395.0  \n",
       "0xaAa604E8f5318373D1785766D4f7310AF6CC4891   24018.0   22950.0   24768.0  \n",
       "0x000000064730640b7d670408d74280924883064F     119.0     119.0     119.0  \n",
       "0xADD4aeE42817461b0c75324684B1cc8A3887ECcc     116.0      94.0     138.0  \n",
       "0xACa4AB44dcD2Ec6982bb5238Bf1Daf268C159699     283.0       0.0     445.0  \n",
       "0xAD0206f327c23087bc579Ece7A5fdBC82696fDFE       0.0       0.0       0.0  \n",
       "0xAD70c33FA39D55aD7d88dfc9186726e6C3758921       1.0       1.0       1.0  \n",
       "0xAE0Fd8D80c04bfE9Fa9ADCf2BfAAe82e865dE527       0.0       0.0       0.0  \n",
       "0xADEE57ED85DCd0663f486c9b2588f256883C4556       0.0       0.0       0.0  \n",
       "0xABE4709439529E6fcF9C47dC7DDCbA72B0673F7a       0.0       0.0       0.0  \n",
       "0xAE1692D8D045716c948B707550e91DBd2C55169A      32.0      31.0      32.0  \n",
       "0xAE934ED0CAA49C06fb4192Cd091e65226fFF2A9b       0.0       0.0       0.0  \n",
       "0xAEB7C6E65AAa0C6939e79D866969b3837867ecC8       0.0       0.0       0.0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_deciding_vote_df.round(0).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "4c051ca9-204f-439d-b5eb-c0cb727906cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def sum_true(grp):\n",
    "    return np.sum(grp=='Was Deciding Voter')\n",
    "def create_has_deciding_vote(row):\n",
    "    if row['reached_quorum'] == False:\n",
    "        return 'no quorum'\n",
    "    else:\n",
    "        if row['num_deciding_votes']>0:\n",
    "            return 'Has Deciding Voter'\n",
    "        else:\n",
    "            return 'No Deciding Voter'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "cd9d3563-aff8-4785-9fc1-cb53645ff9b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "deciding_votes_by_proposal_df = winning_coalition_df.groupby(['proposal','category','votes','reached_quorum'])\\\n",
    "    .agg(num_deciding_votes=('is_deciding_vote',sum_true)).reset_index()\n",
    "                       \n",
    "deciding_votes_by_proposal_df['has_deciding_vote'] = deciding_votes_by_proposal_df.apply(create_has_deciding_vote,axis=1) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15ca44e3-5faf-44e0-95eb-6e2c78ca7a9a",
   "metadata": {},
   "source": [
    " Note, there are two proposals with more than 1 deciding vote"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a8dbbbd-74b8-4601-bbb8-5baf5061fc3c",
   "metadata": {},
   "source": [
    "### Number of deciding votes by Proposal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a95d6061-e6cd-4d5e-99db-bbfac26612cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>proposal</th>\n",
       "      <th>category</th>\n",
       "      <th>votes</th>\n",
       "      <th>reached_quorum</th>\n",
       "      <th>num_deciding_votes</th>\n",
       "      <th>has_deciding_vote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x0b70d0cdd97874eca00769fe1c323351a3e7c473bff8...</td>\n",
       "      <td>DG2</td>\n",
       "      <td>61</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x1aa4fdb6f42d866c7ba5f80ceea61358051039df7a22...</td>\n",
       "      <td>IIP</td>\n",
       "      <td>64</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>No Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x26ea01b07411488227517a164bdfa5ee5c06792bd976...</td>\n",
       "      <td>IIP</td>\n",
       "      <td>54</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x31a11f7f37c0319785cad3ab7353745731c3d33908b5...</td>\n",
       "      <td>DG1</td>\n",
       "      <td>66</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x40edf5d2828a0473626a8a7bfa22a8755fb847a95563...</td>\n",
       "      <td>DG1</td>\n",
       "      <td>57</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Qmf9zQAiL1eJwDhy7SPXhNjSNhga3xnHrxLp373amCGnzV</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>36</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>QmfEFdo3byE9XsgfZrP2XG7VJwoeYSb3m56oF93hDeDMth</td>\n",
       "      <td>IIP</td>\n",
       "      <td>59</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>No Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>QmfGRkXA5gj2ske8Ci45wZnA9BRQg38iQ9vkpegABi9EbW</td>\n",
       "      <td>IIP</td>\n",
       "      <td>85</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>No Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>QmfMqLAiJmgjTpNaXyVFkZtCaV2kBDtzp4dMhzdvzQCrKZ</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>20</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>Has Deciding Voter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>QmfYyqbcsrHtkcsZ3LGcVyorCN72p511jzv6m2wbYvCjZv</td>\n",
       "      <td>Metagovernance</td>\n",
       "      <td>62</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>no quorum</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>206 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              proposal        category  votes  \\\n",
       "0    0x0b70d0cdd97874eca00769fe1c323351a3e7c473bff8...             DG2     61   \n",
       "1    0x1aa4fdb6f42d866c7ba5f80ceea61358051039df7a22...             IIP     64   \n",
       "2    0x26ea01b07411488227517a164bdfa5ee5c06792bd976...             IIP     54   \n",
       "3    0x31a11f7f37c0319785cad3ab7353745731c3d33908b5...             DG1     66   \n",
       "4    0x40edf5d2828a0473626a8a7bfa22a8755fb847a95563...             DG1     57   \n",
       "..                                                 ...             ...    ...   \n",
       "201     Qmf9zQAiL1eJwDhy7SPXhNjSNhga3xnHrxLp373amCGnzV  Metagovernance     36   \n",
       "202     QmfEFdo3byE9XsgfZrP2XG7VJwoeYSb3m56oF93hDeDMth             IIP     59   \n",
       "203     QmfGRkXA5gj2ske8Ci45wZnA9BRQg38iQ9vkpegABi9EbW             IIP     85   \n",
       "204     QmfMqLAiJmgjTpNaXyVFkZtCaV2kBDtzp4dMhzdvzQCrKZ  Metagovernance     20   \n",
       "205     QmfYyqbcsrHtkcsZ3LGcVyorCN72p511jzv6m2wbYvCjZv  Metagovernance     62   \n",
       "\n",
       "     reached_quorum  num_deciding_votes   has_deciding_vote  \n",
       "0              True                   1  Has Deciding Voter  \n",
       "1              True                   0   No Deciding Voter  \n",
       "2              True                   1  Has Deciding Voter  \n",
       "3              True                   1  Has Deciding Voter  \n",
       "4              True                   1  Has Deciding Voter  \n",
       "..              ...                 ...                 ...  \n",
       "201            True                   1  Has Deciding Voter  \n",
       "202            True                   0   No Deciding Voter  \n",
       "203            True                   0   No Deciding Voter  \n",
       "204            True                   1  Has Deciding Voter  \n",
       "205           False                   0           no quorum  \n",
       "\n",
       "[206 rows x 6 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deciding_votes_by_proposal_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "208eb66e-cf3c-4f23-b5aa-43e990f6d13b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>has_deciding_vote</th>\n",
       "      <th>Has Deciding Voter</th>\n",
       "      <th>No Deciding Voter</th>\n",
       "      <th>no quorum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>DG1</th>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DG2</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IIP</th>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metagovernance</th>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "has_deciding_vote  Has Deciding Voter  No Deciding Voter  no quorum\n",
       "category                                                           \n",
       "DG1                                 8                  6          5\n",
       "DG2                                 5                  5          2\n",
       "IIP                                30                 35          8\n",
       "Metagovernance                     10                 11         81"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deciding_vote_by_proposal_pivoted = deciding_votes_by_proposal_df.groupby(['category','has_deciding_vote']).size()\\\n",
    ".reset_index(name='counts')\\\n",
    ".pivot_table(index='category',columns='has_deciding_vote',values='counts',fill_value=0)\n",
    "deciding_vote_by_proposal_pivoted\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "e6bfc753-e375-4c4c-a859-d3773e61be9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dump to CSVs for transparency\n",
    "deciding_votes_by_proposal_df.to_csv('deciding_votes_by_proposal_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "883dee64-bf2b-4a1b-a986-56ca5f9d0cf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "deciding_vote_by_proposal_pivoted.to_csv('deciding_votes_by_proposal_pivoted_df.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}