{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "Created by: [SmirkyGraphs](https://smirkygraphs.github.io/). Code: [Github](https://github.com/SmirkyGraphs/Python-Notebooks). Source: [RI Legislature Site](http://www.rilin.state.ri.us/pages/legislation.aspx).\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Held for Further Study: Where Bills Go to Die" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bills in Rhode Island face a big hurdle, making it out of committee and not being **\"Held for Further Study\"**. Once a bill is introduced and sent to committee, the first vote it recieves is whether or not it should be \"Held for Further Study\". A bill held for further study is indefinitely postpone, however can be considered again by the committee. Despite the naming, no study actually takes place, and if reintroduced, no study is prestended.\n", "\n", "This notebook will take a look at how many bills end up \"held for further study\" and which chamber they were started in from 2007-2019.\n", "
" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('./data/clean/bill_actions.csv')\n", "\n", "# removes resolutions\n", "df = df[~df['bill_id'].str.contains('R')]\n", "\n", "# remove duplicate (created when splitting action_type)\n", "df = df.drop_duplicates(subset=(['action', 'lookup_id']))\n", "\n", "# adding action_num increments of bill\n", "df['action_num'] = df.groupby('lookup_id').cumcount()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "total held for study: 64% (16509)\n", "total died held for study: 63% (10473)\n" ] } ], "source": [ "# total bills introduced\n", "total_bills = df['lookup_id'].nunique()\n", "\n", "# total bills ever \"held for further study\"\n", "held_for_study = df[df['type'] == 'held for further study']['lookup_id'].unique().tolist()\n", "\n", "# total bills that never left \"held for further study\"\n", "last_action = df.groupby('lookup_id')['type'].agg('last').reset_index()\n", "last_action = last_action[last_action['type'] == 'held for further study']\n", "died_in_study = last_action['lookup_id'].unique().tolist()\n", "\n", "# final calcs\n", "\n", "total_held_for_study = int(round(len(held_for_study)/total_bills, 2) * 100)\n", "total_died_held = int(round(len(died_in_study)/len(held_for_study), 2) * 100)\n", "\n", "print(f'total held for study: {total_held_for_study}% ({len(held_for_study)})')\n", "print(f'total died held for study: {total_died_held}% ({len(died_in_study)})')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Bills Held & Died for Further Study by Year (Percents Compared to Total Bills)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count_totalcount_ever_held(% total)_ever_heldcount_died_held(% total)_died_held(% held)_died_held
session
20072255127556%80936%63%
20082159120156%77936%65%
20091999112456%74037%66%
2010186191049%61833%68%
20111926117361%76740%65%
20121908104355%65134%62%
20131907123465%70837%56%
20142036129864%77238%59%
20151896130569%83544%64%
20162068147371%91144%62%
20171963155579%96149%62%
20181935147376%97851%66%
20191788144581%94453%65%
\n", "
" ], "text/plain": [ " count_total count_ever_held (% total)_ever_held count_died_held \\\n", "session \n", "2007 2255 1275 56% 809 \n", "2008 2159 1201 56% 779 \n", "2009 1999 1124 56% 740 \n", "2010 1861 910 49% 618 \n", "2011 1926 1173 61% 767 \n", "2012 1908 1043 55% 651 \n", "2013 1907 1234 65% 708 \n", "2014 2036 1298 64% 772 \n", "2015 1896 1305 69% 835 \n", "2016 2068 1473 71% 911 \n", "2017 1963 1555 79% 961 \n", "2018 1935 1473 76% 978 \n", "2019 1788 1445 81% 944 \n", "\n", " (% total)_died_held (% held)_died_held \n", "session \n", "2007 36% 63% \n", "2008 36% 65% \n", "2009 37% 66% \n", "2010 33% 68% \n", "2011 40% 65% \n", "2012 34% 62% \n", "2013 37% 56% \n", "2014 38% 59% \n", "2015 44% 64% \n", "2016 44% 62% \n", "2017 49% 62% \n", "2018 51% 66% \n", "2019 53% 65% " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.loc[df['lookup_id'].isin(died_in_study), 'ended_held_for_study'] = True\n", "df.loc[df['lookup_id'].isin(held_for_study), 'ever_held_for_study'] = True\n", "\n", "held = df.drop_duplicates('lookup_id')\n", "held = held.groupby('session')['lookup_id', 'ever_held_for_study', 'ended_held_for_study'].count()\n", "\n", "held['percent_ever_held'] = (round(held['ever_held_for_study']/held['lookup_id'], 2)*100).astype(int).astype(str) + '%'\n", "held['percent_ended_held'] = (round(held['ended_held_for_study']/held['lookup_id'], 2)*100).astype(int).astype(str) + '%'\n", "\n", "held['x'] = (round(held['ended_held_for_study']/held['ever_held_for_study'], 2)*100).astype(int).astype(str) + '%'\n", "\n", "held.columns = ['count_total', 'count_ever_held', 'count_died_held', \n", " '(% total)_ever_held', '(% total)_died_held', '(% held)_died_held']\n", "\n", "cols = ['count_total','count_ever_held','(% total)_ever_held','count_died_held','(% total)_died_held', '(% held)_died_held']\n", "held = held.reindex(columns=cols)\n", "held" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Bills Held & Died for Further Study by Year (Seperated by Chamber)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count_totalcount_ever_held(% total)_ever_heldcount_died_held(% total)_died_held(% held)_died_held
chamber_originsession
house2007127981364%51941%64%
2008118473762%50242%68%
2009111473266%48544%66%
2010103762060%45844%74%
2011104875672%52850%70%
2012104563761%42240%66%
2013107675770%46043%61%
2014112681572%52647%65%
2015107281476%55652%68%
2016114090679%58952%65%
2017111995385%62456%65%
2018111291382%62556%68%
201997382685%59361%72%
senate200797646247%29030%63%
200897546448%27728%60%
200988539244%25528%65%
201082429035%16019%55%
201187841747%23927%56%
201286340647%22927%56%
201383147756%24830%52%
201491048353%24627%51%
201582449160%27934%56%
201692856761%32235%56%
201784460271%33740%56%
201882356068%35343%63%
201981561976%35143%56%
\n", "
" ], "text/plain": [ " count_total count_ever_held (% total)_ever_held \\\n", "chamber_origin session \n", "house 2007 1279 813 64% \n", " 2008 1184 737 62% \n", " 2009 1114 732 66% \n", " 2010 1037 620 60% \n", " 2011 1048 756 72% \n", " 2012 1045 637 61% \n", " 2013 1076 757 70% \n", " 2014 1126 815 72% \n", " 2015 1072 814 76% \n", " 2016 1140 906 79% \n", " 2017 1119 953 85% \n", " 2018 1112 913 82% \n", " 2019 973 826 85% \n", "senate 2007 976 462 47% \n", " 2008 975 464 48% \n", " 2009 885 392 44% \n", " 2010 824 290 35% \n", " 2011 878 417 47% \n", " 2012 863 406 47% \n", " 2013 831 477 56% \n", " 2014 910 483 53% \n", " 2015 824 491 60% \n", " 2016 928 567 61% \n", " 2017 844 602 71% \n", " 2018 823 560 68% \n", " 2019 815 619 76% \n", "\n", " count_died_held (% total)_died_held (% held)_died_held \n", "chamber_origin session \n", "house 2007 519 41% 64% \n", " 2008 502 42% 68% \n", " 2009 485 44% 66% \n", " 2010 458 44% 74% \n", " 2011 528 50% 70% \n", " 2012 422 40% 66% \n", " 2013 460 43% 61% \n", " 2014 526 47% 65% \n", " 2015 556 52% 68% \n", " 2016 589 52% 65% \n", " 2017 624 56% 65% \n", " 2018 625 56% 68% \n", " 2019 593 61% 72% \n", "senate 2007 290 30% 63% \n", " 2008 277 28% 60% \n", " 2009 255 28% 65% \n", " 2010 160 19% 55% \n", " 2011 239 27% 56% \n", " 2012 229 27% 56% \n", " 2013 248 30% 52% \n", " 2014 246 27% 51% \n", " 2015 279 34% 56% \n", " 2016 322 35% 56% \n", " 2017 337 40% 56% \n", " 2018 353 43% 63% \n", " 2019 351 43% 56% " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "chamber = df.drop_duplicates('lookup_id').copy()\n", "\n", "chamber.loc[chamber['bill_id'].str.contains('H'), 'chamber_origin'] = 'house'\n", "chamber.loc[chamber['bill_id'].str.contains('S'), 'chamber_origin'] = 'senate'\n", "\n", "held = chamber.groupby(['chamber_origin', 'session'])['lookup_id', 'ever_held_for_study', 'ended_held_for_study'].count()\n", "held['percent_ever_held'] = (round(held['ever_held_for_study']/held['lookup_id'], 2)*100).astype(int).astype(str) + '%'\n", "held['percent_ended_held'] = (round(held['ended_held_for_study']/held['lookup_id'], 2)*100).astype(int).astype(str) + '%'\n", "\n", "held['x'] = (round(held['ended_held_for_study']/held['ever_held_for_study'], 2)*100).astype(int).astype(str) + '%'\n", "\n", "held.columns = ['count_total', 'count_ever_held', 'count_died_held', \n", " '(% total)_ever_held', '(% total)_died_held', '(% held)_died_held']\n", "\n", "cols = ['count_total','count_ever_held','(% total)_ever_held','count_died_held','(% total)_died_held', '(% held)_died_held']\n", "held = held.reindex(columns=cols)\n", "held" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Making Extract of Bills Held for Study" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('./data/clean/bill_actions.csv')\n", "\n", "# removes resolutions\n", "df = df[~df['bill_id'].str.contains('R')]\n", "\n", "# remove duplicate (created when splitting action_type)\n", "df = df.drop_duplicates(subset=(['action', 'lookup_id']))\n", "\n", "# adding action_num increments of bill\n", "df['action_num'] = df.groupby('lookup_id').cumcount()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# creating dataset for held for study tableau sheet\n", "df.loc[df['lookup_id'].isin(died_in_study), 'ended_held_for_study'] = True\n", "df.loc[df['lookup_id'].isin(held_for_study), 'ever_held_for_study'] = True\n", "\n", "df = df.fillna(False)\n", "\n", "committee = df.groupby('lookup_id')['action'].agg(['first']).reset_index()\n", "committee['first'] = committee['first'].apply(lambda x: x.split(', ')[-1])\n", "committee = committee.rename(columns={'first':'committee'})\n", "\n", "\n", "df = df.merge(committee, on='lookup_id')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "df.to_csv('./data/clean/held_for_study.csv', index=False)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }