examples/voting_records.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Voting Records\n",
    "\n",
    "Methods for retrieving open access data.\n",
    "\n",
    "A database schema diagram for production instances of CDP may be found [here](https://github.com/CouncilDataProject/cdptools/blob/master/docs/resources/database_diagram.pdf)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<CDPInstance [database: <CloudFirestoreDatabase [stg-cdp-seattle]>, file_store: <GCSFileStore [stg-cdp-seattle.appspot.com]>]>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from cdptools import CDPInstance, configs\n",
    "\n",
    "seattle = CDPInstance(configs.SEATTLE)\n",
    "seattle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get all votes for a single person\n",
    "\n",
    "Query and merge the `person` and `vote` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>created_person</th>\n",
       "      <th>email</th>\n",
       "      <th>full_name</th>\n",
       "      <th>legistar_person_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>phone</th>\n",
       "      <th>website</th>\n",
       "      <th>created_vote</th>\n",
       "      <th>decision</th>\n",
       "      <th>event_minutes_item_id</th>\n",
       "      <th>legistar_event_item_vote_id</th>\n",
       "      <th>vote_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:34:14.510429</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>650b4bc0-6dfe-4c8e-b987-326e6b326d6f</td>\n",
       "      <td>46473</td>\n",
       "      <td>02ea892d-c24d-4d91-91df-a29cd6735080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-21 00:29:28.257667</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>9679b518-db5b-4c3e-ab54-e35a933e1ccc</td>\n",
       "      <td>46659</td>\n",
       "      <td>0b001131-b59c-413f-8d8e-07b6b5df20f6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:22:20.345379</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>01c4b5c4-2d02-44b2-808b-e8f71c77c70d</td>\n",
       "      <td>46725</td>\n",
       "      <td>105291a1-ce97-452c-b0ee-91ce1a314471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:28:34.894883</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>cfe4d044-beab-4b05-9e74-27eefdcaeda2</td>\n",
       "      <td>47003</td>\n",
       "      <td>1564337b-f145-4f61-9d14-875faa8351c6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-21 00:13:15.404858</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>8a5309d2-2b18-4ce2-bbb7-e66626ae9565</td>\n",
       "      <td>46978</td>\n",
       "      <td>18bdd900-23f2-42a8-b30b-5ee7fd18abff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              created_person                        email        full_name  \\\n",
       "0 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "1 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "2 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "3 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "4 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "\n",
       "   legistar_person_id                             person_id         phone  \\\n",
       "0                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "1                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "2                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "3                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "4                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "\n",
       "                                   website               created_vote  \\\n",
       "0  http://www.seattle.gov/council/mosqueda 2019-07-20 23:34:14.510429   \n",
       "1  http://www.seattle.gov/council/mosqueda 2019-07-21 00:29:28.257667   \n",
       "2  http://www.seattle.gov/council/mosqueda 2019-07-20 23:22:20.345379   \n",
       "3  http://www.seattle.gov/council/mosqueda 2019-07-20 23:28:34.894883   \n",
       "4  http://www.seattle.gov/council/mosqueda 2019-07-21 00:13:15.404858   \n",
       "\n",
       "   decision                 event_minutes_item_id  \\\n",
       "0  In Favor  650b4bc0-6dfe-4c8e-b987-326e6b326d6f   \n",
       "1  In Favor  9679b518-db5b-4c3e-ab54-e35a933e1ccc   \n",
       "2  In Favor  01c4b5c4-2d02-44b2-808b-e8f71c77c70d   \n",
       "3  In Favor  cfe4d044-beab-4b05-9e74-27eefdcaeda2   \n",
       "4  In Favor  8a5309d2-2b18-4ce2-bbb7-e66626ae9565   \n",
       "\n",
       "   legistar_event_item_vote_id                               vote_id  \n",
       "0                        46473  02ea892d-c24d-4d91-91df-a29cd6735080  \n",
       "1                        46659  0b001131-b59c-413f-8d8e-07b6b5df20f6  \n",
       "2                        46725  105291a1-ce97-452c-b0ee-91ce1a314471  \n",
       "3                        47003  1564337b-f145-4f61-9d14-875faa8351c6  \n",
       "4                        46978  18bdd900-23f2-42a8-b30b-5ee7fd18abff  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Filter down people to just Teresa\n",
    "teresa = seattle.database.select_rows_as_list(\"person\", filters=[(\"full_name\", \"Teresa Mosqueda\")])\n",
    "teresa = pd.DataFrame(teresa)\n",
    "\n",
    "# Pull voting data\n",
    "votes = pd.DataFrame(seattle.database.select_rows_as_list(\"vote\"))\n",
    "\n",
    "# Merge data\n",
    "teresa_voting_record = teresa.merge(\n",
    "    votes,\n",
    "    left_on=\"person_id\",\n",
    "    right_on=\"person_id\",\n",
    "    suffixes=(\"_person\", \"_vote\")\n",
    ")\n",
    "\n",
    "teresa_voting_record.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is great, but it's missing context of what was actually being voted on. Currently we can see that she voted on instances of `event_minutes_item`, but what were those. Follow the same procedure of `select_rows_as_list` -> `merge` to join the data of the other tables to Teresa's voting record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>created_person</th>\n",
       "      <th>email</th>\n",
       "      <th>full_name</th>\n",
       "      <th>legistar_person_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>phone</th>\n",
       "      <th>website</th>\n",
       "      <th>created_vote</th>\n",
       "      <th>decision_voting_record</th>\n",
       "      <th>event_minutes_item_id</th>\n",
       "      <th>...</th>\n",
       "      <th>name</th>\n",
       "      <th>agenda_file_uri</th>\n",
       "      <th>body_id</th>\n",
       "      <th>created</th>\n",
       "      <th>event_datetime</th>\n",
       "      <th>legistar_event_id</th>\n",
       "      <th>legistar_event_link</th>\n",
       "      <th>minutes_file_uri</th>\n",
       "      <th>source_uri</th>\n",
       "      <th>video_uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:34:14.510429</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>650b4bc0-6dfe-4c8e-b987-326e6b326d6f</td>\n",
       "      <td>...</td>\n",
       "      <td>Reappointment of Torgie Madison as member, Com...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>f934cfa0-4285-404f-8072-4163df0a03b3</td>\n",
       "      <td>2019-07-20 23:33:41.354756</td>\n",
       "      <td>2019-06-24 14:00:00</td>\n",
       "      <td>4007</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>http://video.seattle.gov:8080/media/council/co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:33:44.529431</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>b0ba203e-7668-4241-8862-7b1a6cdb0def</td>\n",
       "      <td>...</td>\n",
       "      <td>June 24, 2019</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>f934cfa0-4285-404f-8072-4163df0a03b3</td>\n",
       "      <td>2019-07-20 23:33:41.354756</td>\n",
       "      <td>2019-06-24 14:00:00</td>\n",
       "      <td>4007</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>http://video.seattle.gov:8080/media/council/co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:33:52.379179</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>2eac84c1-800a-4605-b15d-eb47542a1a3f</td>\n",
       "      <td>...</td>\n",
       "      <td>CB 119548</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>f934cfa0-4285-404f-8072-4163df0a03b3</td>\n",
       "      <td>2019-07-20 23:33:41.354756</td>\n",
       "      <td>2019-06-24 14:00:00</td>\n",
       "      <td>4007</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>http://video.seattle.gov:8080/media/council/co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:33:48.434502</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>5b361b64-3a51-4a47-a3dd-8eafa34e6e2e</td>\n",
       "      <td>...</td>\n",
       "      <td>June 17, 2019</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>f934cfa0-4285-404f-8072-4163df0a03b3</td>\n",
       "      <td>2019-07-20 23:33:41.354756</td>\n",
       "      <td>2019-06-24 14:00:00</td>\n",
       "      <td>4007</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>http://video.seattle.gov:8080/media/council/co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2019-07-20 23:22:09.665851</td>\n",
       "      <td>Teresa.Mosqueda@seattle.gov</td>\n",
       "      <td>Teresa Mosqueda</td>\n",
       "      <td>594</td>\n",
       "      <td>c4ded0b8-912a-4f68-b436-0d52d5ca66e0</td>\n",
       "      <td>206-684-8806</td>\n",
       "      <td>http://www.seattle.gov/council/mosqueda</td>\n",
       "      <td>2019-07-20 23:34:04.248787</td>\n",
       "      <td>In Favor</td>\n",
       "      <td>ad3f5be7-1dec-405f-a931-3b680d1807e0</td>\n",
       "      <td>...</td>\n",
       "      <td>Council Land Use Action to approve a temporary...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>f934cfa0-4285-404f-8072-4163df0a03b3</td>\n",
       "      <td>2019-07-20 23:33:41.354756</td>\n",
       "      <td>2019-06-24 14:00:00</td>\n",
       "      <td>4007</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>http://video.seattle.gov:8080/media/council/co...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               created_person                        email        full_name  \\\n",
       "0  2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "31 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "30 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "25 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "24 2019-07-20 23:22:09.665851  Teresa.Mosqueda@seattle.gov  Teresa Mosqueda   \n",
       "\n",
       "    legistar_person_id                             person_id         phone  \\\n",
       "0                  594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "31                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "30                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "25                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "24                 594  c4ded0b8-912a-4f68-b436-0d52d5ca66e0  206-684-8806   \n",
       "\n",
       "                                    website               created_vote  \\\n",
       "0   http://www.seattle.gov/council/mosqueda 2019-07-20 23:34:14.510429   \n",
       "31  http://www.seattle.gov/council/mosqueda 2019-07-20 23:33:44.529431   \n",
       "30  http://www.seattle.gov/council/mosqueda 2019-07-20 23:33:52.379179   \n",
       "25  http://www.seattle.gov/council/mosqueda 2019-07-20 23:33:48.434502   \n",
       "24  http://www.seattle.gov/council/mosqueda 2019-07-20 23:34:04.248787   \n",
       "\n",
       "   decision_voting_record                 event_minutes_item_id  ...  \\\n",
       "0                In Favor  650b4bc0-6dfe-4c8e-b987-326e6b326d6f  ...   \n",
       "31               In Favor  b0ba203e-7668-4241-8862-7b1a6cdb0def  ...   \n",
       "30               In Favor  2eac84c1-800a-4605-b15d-eb47542a1a3f  ...   \n",
       "25               In Favor  5b361b64-3a51-4a47-a3dd-8eafa34e6e2e  ...   \n",
       "24               In Favor  ad3f5be7-1dec-405f-a931-3b680d1807e0  ...   \n",
       "\n",
       "                                                 name  \\\n",
       "0   Reappointment of Torgie Madison as member, Com...   \n",
       "31                                      June 24, 2019   \n",
       "30                                          CB 119548   \n",
       "25                                      June 17, 2019   \n",
       "24  Council Land Use Action to approve a temporary...   \n",
       "\n",
       "                                      agenda_file_uri  \\\n",
       "0   http://legistar2.granicus.com/seattle/meetings...   \n",
       "31  http://legistar2.granicus.com/seattle/meetings...   \n",
       "30  http://legistar2.granicus.com/seattle/meetings...   \n",
       "25  http://legistar2.granicus.com/seattle/meetings...   \n",
       "24  http://legistar2.granicus.com/seattle/meetings...   \n",
       "\n",
       "                                 body_id                    created  \\\n",
       "0   f934cfa0-4285-404f-8072-4163df0a03b3 2019-07-20 23:33:41.354756   \n",
       "31  f934cfa0-4285-404f-8072-4163df0a03b3 2019-07-20 23:33:41.354756   \n",
       "30  f934cfa0-4285-404f-8072-4163df0a03b3 2019-07-20 23:33:41.354756   \n",
       "25  f934cfa0-4285-404f-8072-4163df0a03b3 2019-07-20 23:33:41.354756   \n",
       "24  f934cfa0-4285-404f-8072-4163df0a03b3 2019-07-20 23:33:41.354756   \n",
       "\n",
       "        event_datetime  legistar_event_id  \\\n",
       "0  2019-06-24 14:00:00               4007   \n",
       "31 2019-06-24 14:00:00               4007   \n",
       "30 2019-06-24 14:00:00               4007   \n",
       "25 2019-06-24 14:00:00               4007   \n",
       "24 2019-06-24 14:00:00               4007   \n",
       "\n",
       "                                  legistar_event_link  \\\n",
       "0   https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "31  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "30  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "25  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "24  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "\n",
       "                                     minutes_file_uri  \\\n",
       "0   http://legistar2.granicus.com/seattle/meetings...   \n",
       "31  http://legistar2.granicus.com/seattle/meetings...   \n",
       "30  http://legistar2.granicus.com/seattle/meetings...   \n",
       "25  http://legistar2.granicus.com/seattle/meetings...   \n",
       "24  http://legistar2.granicus.com/seattle/meetings...   \n",
       "\n",
       "                                           source_uri  \\\n",
       "0   http://www.seattlechannel.org/FullCouncil?vide...   \n",
       "31  http://www.seattlechannel.org/FullCouncil?vide...   \n",
       "30  http://www.seattlechannel.org/FullCouncil?vide...   \n",
       "25  http://www.seattlechannel.org/FullCouncil?vide...   \n",
       "24  http://www.seattlechannel.org/FullCouncil?vide...   \n",
       "\n",
       "                                            video_uri  \n",
       "0   http://video.seattle.gov:8080/media/council/co...  \n",
       "31  http://video.seattle.gov:8080/media/council/co...  \n",
       "30  http://video.seattle.gov:8080/media/council/co...  \n",
       "25  http://video.seattle.gov:8080/media/council/co...  \n",
       "24  http://video.seattle.gov:8080/media/council/co...  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get event minutes item context data\n",
    "event_minutes_items = pd.DataFrame(seattle.database.select_rows_as_list(\"event_minutes_item\"))\n",
    "minutes_items = pd.DataFrame(seattle.database.select_rows_as_list(\"minutes_item\"))\n",
    "events = pd.DataFrame(seattle.database.select_rows_as_list(\"event\"))\n",
    "\n",
    "# Merge minutes item data\n",
    "event_minutes_items = event_minutes_items.merge(\n",
    "    minutes_items,\n",
    "    left_on=\"minutes_item_id\",\n",
    "    right_on=\"minutes_item_id\",\n",
    "    suffixes=(\"_event_minutes_item\", \"_minutes_item\")\n",
    ")\n",
    "\n",
    "# Merge event data\n",
    "event_minutes_items = event_minutes_items.merge(\n",
    "    events,\n",
    "    left_on=\"event_id\",\n",
    "    right_on=\"event_id\",\n",
    "    suffixes=(\"_event_minutes_item\", \"_event\")\n",
    ")\n",
    "\n",
    "# Merge above event minutes item data with Teresa's voting record\n",
    "teresa_voting_record = teresa_voting_record.merge(\n",
    "    event_minutes_items,\n",
    "    left_on=\"event_minutes_item_id\",\n",
    "    right_on=\"event_minutes_item_id\",\n",
    "    suffixes=(\"_voting_record\", \"_event_minutes_item\")\n",
    ")\n",
    "\n",
    "# Sort by event datetime\n",
    "teresa_voting_record = teresa_voting_record.sort_values(by=[\"event_datetime\"])\n",
    "teresa_voting_record.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "created_person                                        2019-07-20 23:22:09.665851\n",
       "email                                                Teresa.Mosqueda@seattle.gov\n",
       "full_name                                                        Teresa Mosqueda\n",
       "legistar_person_id                                                           594\n",
       "person_id                                   c4ded0b8-912a-4f68-b436-0d52d5ca66e0\n",
       "phone                                                               206-684-8806\n",
       "website                                  http://www.seattle.gov/council/mosqueda\n",
       "created_vote                                          2019-07-20 23:34:14.510429\n",
       "decision_voting_record                                                  In Favor\n",
       "event_minutes_item_id                       650b4bc0-6dfe-4c8e-b987-326e6b326d6f\n",
       "legistar_event_item_vote_id                                                46473\n",
       "vote_id                                     02ea892d-c24d-4d91-91df-a29cd6735080\n",
       "created_event_minutes_item                            2019-07-20 23:34:13.119658\n",
       "decision_event_minutes_item                                                 Pass\n",
       "event_id                                    846f548a-23b4-4af8-8889-0ce04071bfe7\n",
       "index                                                                         23\n",
       "minutes_item_id                             841cab51-7265-4798-b926-2edd0b858949\n",
       "created_minutes_item                                  2019-07-20 23:34:12.755735\n",
       "legistar_event_item_id                                                     65077\n",
       "matter                                                                Appt 01374\n",
       "name                           Reappointment of Torgie Madison as member, Com...\n",
       "agenda_file_uri                http://legistar2.granicus.com/seattle/meetings...\n",
       "body_id                                     f934cfa0-4285-404f-8072-4163df0a03b3\n",
       "created                                               2019-07-20 23:33:41.354756\n",
       "event_datetime                                               2019-06-24 14:00:00\n",
       "legistar_event_id                                                           4007\n",
       "legistar_event_link            https://seattle.legistar.com/MeetingDetail.asp...\n",
       "minutes_file_uri               http://legistar2.granicus.com/seattle/meetings...\n",
       "source_uri                     http://www.seattlechannel.org/FullCouncil?vide...\n",
       "video_uri                      http://video.seattle.gov:8080/media/council/co...\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "teresa_voting_record.loc[0]"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}