examples/transcripts.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with CDP Transcripts\n",
    "\n",
    "Methods for retrieving open access data.\n",
    "\n",
    "Prior to reading this example, it is recommended to read the [transcript_formats](../docs/transcript_formats.md) documentation.\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 [cdp-seattle]>, file_store: <GCSFileStore [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": [
    "### Find all transcripts\n",
    "\n",
    "Simple query the transcript table!"
   ]
  },
  {
   "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>transcript_id</th>\n",
       "      <th>confidence</th>\n",
       "      <th>event_id</th>\n",
       "      <th>file_id</th>\n",
       "      <th>created</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0008c494-66e4-4459-894c-52935580f776</td>\n",
       "      <td>0.950079</td>\n",
       "      <td>56eeb4fc-079f-425e-bd76-8727b78365dd</td>\n",
       "      <td>445896ff-8eca-4594-a49b-7b088244cb6d</td>\n",
       "      <td>2019-08-08 08:55:27.733798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00fd1e9f-9718-4ba9-bc24-01ddaad4fff2</td>\n",
       "      <td>0.946712</td>\n",
       "      <td>cd2b1685-e6ef-4084-a9d0-d8ee38e2a1da</td>\n",
       "      <td>29e15ed4-a03b-4d64-9078-c7a13101df6e</td>\n",
       "      <td>2019-08-08 17:00:39.151725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>021b763c-437f-4bcb-b458-4eec1b9c9058</td>\n",
       "      <td>0.919946</td>\n",
       "      <td>33ab51f9-911b-4965-82ba-e7a62ab26221</td>\n",
       "      <td>5c91e7bd-4e76-4ff3-b16e-25e4ff8bd6a6</td>\n",
       "      <td>2019-10-22 01:55:25.680446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02b4e049-07a6-4355-a16c-2b3066ec75a8</td>\n",
       "      <td>0.925703</td>\n",
       "      <td>e1064f64-da37-480f-9de3-b4d622dd08d9</td>\n",
       "      <td>99f1a4f2-0ea2-449a-99af-d6163550f024</td>\n",
       "      <td>2019-08-08 16:09:00.554294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>069f42b1-94de-4edd-b197-8d4d8ca7563d</td>\n",
       "      <td>0.946464</td>\n",
       "      <td>3932cb65-bc2f-48d6-8604-e0ddd8896491</td>\n",
       "      <td>6527b5c6-4ff8-468b-b356-480569762212</td>\n",
       "      <td>2019-08-08 07:13:36.980601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          transcript_id  confidence  \\\n",
       "0  0008c494-66e4-4459-894c-52935580f776    0.950079   \n",
       "1  00fd1e9f-9718-4ba9-bc24-01ddaad4fff2    0.946712   \n",
       "2  021b763c-437f-4bcb-b458-4eec1b9c9058    0.919946   \n",
       "3  02b4e049-07a6-4355-a16c-2b3066ec75a8    0.925703   \n",
       "4  069f42b1-94de-4edd-b197-8d4d8ca7563d    0.946464   \n",
       "\n",
       "                               event_id                               file_id  \\\n",
       "0  56eeb4fc-079f-425e-bd76-8727b78365dd  445896ff-8eca-4594-a49b-7b088244cb6d   \n",
       "1  cd2b1685-e6ef-4084-a9d0-d8ee38e2a1da  29e15ed4-a03b-4d64-9078-c7a13101df6e   \n",
       "2  33ab51f9-911b-4965-82ba-e7a62ab26221  5c91e7bd-4e76-4ff3-b16e-25e4ff8bd6a6   \n",
       "3  e1064f64-da37-480f-9de3-b4d622dd08d9  99f1a4f2-0ea2-449a-99af-d6163550f024   \n",
       "4  3932cb65-bc2f-48d6-8604-e0ddd8896491  6527b5c6-4ff8-468b-b356-480569762212   \n",
       "\n",
       "                     created  \n",
       "0 2019-08-08 08:55:27.733798  \n",
       "1 2019-08-08 17:00:39.151725  \n",
       "2 2019-10-22 01:55:25.680446  \n",
       "3 2019-08-08 16:09:00.554294  \n",
       "4 2019-08-08 07:13:36.980601  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "transcripts = pd.DataFrame(seattle.database.select_rows_as_list(\"transcript\"))\n",
    "transcripts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join file, event, and body information\n",
    "\n",
    "While the above results are somewhat useful, we should probably merge information from the other tables too..."
   ]
  },
  {
   "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>transcript_id</th>\n",
       "      <th>confidence</th>\n",
       "      <th>event_id</th>\n",
       "      <th>file_id</th>\n",
       "      <th>created_transcript</th>\n",
       "      <th>content_type</th>\n",
       "      <th>filename</th>\n",
       "      <th>description_transcript</th>\n",
       "      <th>created_file</th>\n",
       "      <th>uri</th>\n",
       "      <th>...</th>\n",
       "      <th>created_event</th>\n",
       "      <th>body_id</th>\n",
       "      <th>legistar_event_link</th>\n",
       "      <th>source_uri</th>\n",
       "      <th>legistar_event_id</th>\n",
       "      <th>event_datetime</th>\n",
       "      <th>agenda_file_uri</th>\n",
       "      <th>name</th>\n",
       "      <th>description_event</th>\n",
       "      <th>created_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0008c494-66e4-4459-894c-52935580f776</td>\n",
       "      <td>0.950079</td>\n",
       "      <td>56eeb4fc-079f-425e-bd76-8727b78365dd</td>\n",
       "      <td>445896ff-8eca-4594-a49b-7b088244cb6d</td>\n",
       "      <td>2019-08-08 08:55:27.733798</td>\n",
       "      <td>None</td>\n",
       "      <td>0985c2571e2da7fd37cf2e5fc3535dc280ce9d42c886d3...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 08:55:17.193110</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/0985c2571e2da7fd3...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 08:55:19.421827</td>\n",
       "      <td>758a36d7-c5aa-4cba-af00-0a1ce44ccb39</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/mayor-and-counci...</td>\n",
       "      <td>3915</td>\n",
       "      <td>2019-05-02 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Housing, Health, Energy, and Workers’ Rights C...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 08:02:05.625714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00fd1e9f-9718-4ba9-bc24-01ddaad4fff2</td>\n",
       "      <td>0.946712</td>\n",
       "      <td>cd2b1685-e6ef-4084-a9d0-d8ee38e2a1da</td>\n",
       "      <td>29e15ed4-a03b-4d64-9078-c7a13101df6e</td>\n",
       "      <td>2019-08-08 17:00:39.151725</td>\n",
       "      <td>None</td>\n",
       "      <td>7e729d2068c5babe6672565664e2c01176a51b759f913d...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 17:00:27.905998</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/7e729d2068c5babe6...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 17:00:30.978199</td>\n",
       "      <td>3f69227d-5cdf-4e48-8c77-2526a6ca190d</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/mayor-and-counci...</td>\n",
       "      <td>3900</td>\n",
       "      <td>2019-04-24 14:00:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Finance and Neighborhoods Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 06:45:28.462804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>021b763c-437f-4bcb-b458-4eec1b9c9058</td>\n",
       "      <td>0.919946</td>\n",
       "      <td>33ab51f9-911b-4965-82ba-e7a62ab26221</td>\n",
       "      <td>5c91e7bd-4e76-4ff3-b16e-25e4ff8bd6a6</td>\n",
       "      <td>2019-10-22 01:55:25.680446</td>\n",
       "      <td>None</td>\n",
       "      <td>ed3d2c9e9cc745be6a9db227dce5f6aacd44cef643bbc1...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-22 01:55:03.957929</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/ed3d2c9e9cc745be6...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-10-22 01:55:07.861191</td>\n",
       "      <td>6385675d-e4ce-484e-b35d-3461b52edd22</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>4165</td>\n",
       "      <td>2019-10-21 14:00:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>City Council</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:57:38.039158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>069f42b1-94de-4edd-b197-8d4d8ca7563d</td>\n",
       "      <td>0.946464</td>\n",
       "      <td>3932cb65-bc2f-48d6-8604-e0ddd8896491</td>\n",
       "      <td>6527b5c6-4ff8-468b-b356-480569762212</td>\n",
       "      <td>2019-08-08 07:13:36.980601</td>\n",
       "      <td>None</td>\n",
       "      <td>ce90894107a4e81431a190e2236ea213f07eb5622a15ce...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 07:13:27.296433</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/ce90894107a4e8143...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 07:13:29.580029</td>\n",
       "      <td>d1be6b39-22e1-4ca9-8644-9128e74c2248</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/mayor-and-counci...</td>\n",
       "      <td>4045</td>\n",
       "      <td>2019-07-31 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Gender Equity, Safe Communities, New Americans...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 07:10:43.521005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06adbfed-e0d8-4884-bc8a-267868bacf89</td>\n",
       "      <td>0.937053</td>\n",
       "      <td>d07291a5-4ec0-4ebf-9736-740d35fbab50</td>\n",
       "      <td>68fa2d4f-2526-4799-a07e-0419036e80fc</td>\n",
       "      <td>2019-08-08 06:20:26.667629</td>\n",
       "      <td>None</td>\n",
       "      <td>cb5428b8f63b30578fcc78ed83e2e13b9186dfffe2367a...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 06:20:21.773707</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/cb5428b8f63b30578...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 06:20:24.110208</td>\n",
       "      <td>f6986d6f-4b0b-407c-8e13-1ec873158a66</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/mayor-and-counci...</td>\n",
       "      <td>4016</td>\n",
       "      <td>2019-07-09 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Civil Rights, Utilities, Economic Development,...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 06:20:23.903175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          transcript_id  confidence  \\\n",
       "0  0008c494-66e4-4459-894c-52935580f776    0.950079   \n",
       "1  00fd1e9f-9718-4ba9-bc24-01ddaad4fff2    0.946712   \n",
       "2  021b763c-437f-4bcb-b458-4eec1b9c9058    0.919946   \n",
       "3  069f42b1-94de-4edd-b197-8d4d8ca7563d    0.946464   \n",
       "4  06adbfed-e0d8-4884-bc8a-267868bacf89    0.937053   \n",
       "\n",
       "                               event_id                               file_id  \\\n",
       "0  56eeb4fc-079f-425e-bd76-8727b78365dd  445896ff-8eca-4594-a49b-7b088244cb6d   \n",
       "1  cd2b1685-e6ef-4084-a9d0-d8ee38e2a1da  29e15ed4-a03b-4d64-9078-c7a13101df6e   \n",
       "2  33ab51f9-911b-4965-82ba-e7a62ab26221  5c91e7bd-4e76-4ff3-b16e-25e4ff8bd6a6   \n",
       "3  3932cb65-bc2f-48d6-8604-e0ddd8896491  6527b5c6-4ff8-468b-b356-480569762212   \n",
       "4  d07291a5-4ec0-4ebf-9736-740d35fbab50  68fa2d4f-2526-4799-a07e-0419036e80fc   \n",
       "\n",
       "          created_transcript content_type  \\\n",
       "0 2019-08-08 08:55:27.733798         None   \n",
       "1 2019-08-08 17:00:39.151725         None   \n",
       "2 2019-10-22 01:55:25.680446         None   \n",
       "3 2019-08-08 07:13:36.980601         None   \n",
       "4 2019-08-08 06:20:26.667629         None   \n",
       "\n",
       "                                            filename description_transcript  \\\n",
       "0  0985c2571e2da7fd37cf2e5fc3535dc280ce9d42c886d3...                   None   \n",
       "1  7e729d2068c5babe6672565664e2c01176a51b759f913d...                   None   \n",
       "2  ed3d2c9e9cc745be6a9db227dce5f6aacd44cef643bbc1...                   None   \n",
       "3  ce90894107a4e81431a190e2236ea213f07eb5622a15ce...                   None   \n",
       "4  cb5428b8f63b30578fcc78ed83e2e13b9186dfffe2367a...                   None   \n",
       "\n",
       "                created_file  \\\n",
       "0 2019-08-08 08:55:17.193110   \n",
       "1 2019-08-08 17:00:27.905998   \n",
       "2 2019-10-22 01:55:03.957929   \n",
       "3 2019-08-08 07:13:27.296433   \n",
       "4 2019-08-08 06:20:21.773707   \n",
       "\n",
       "                                                 uri  ...  \\\n",
       "0  gs://cdp-seattle.appspot.com/0985c2571e2da7fd3...  ...   \n",
       "1  gs://cdp-seattle.appspot.com/7e729d2068c5babe6...  ...   \n",
       "2  gs://cdp-seattle.appspot.com/ed3d2c9e9cc745be6...  ...   \n",
       "3  gs://cdp-seattle.appspot.com/ce90894107a4e8143...  ...   \n",
       "4  gs://cdp-seattle.appspot.com/cb5428b8f63b30578...  ...   \n",
       "\n",
       "               created_event                               body_id  \\\n",
       "0 2019-08-08 08:55:19.421827  758a36d7-c5aa-4cba-af00-0a1ce44ccb39   \n",
       "1 2019-08-08 17:00:30.978199  3f69227d-5cdf-4e48-8c77-2526a6ca190d   \n",
       "2 2019-10-22 01:55:07.861191  6385675d-e4ce-484e-b35d-3461b52edd22   \n",
       "3 2019-08-08 07:13:29.580029  d1be6b39-22e1-4ca9-8644-9128e74c2248   \n",
       "4 2019-08-08 06:20:24.110208  f6986d6f-4b0b-407c-8e13-1ec873158a66   \n",
       "\n",
       "                                 legistar_event_link  \\\n",
       "0  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "1  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "2  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "3  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "4  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "\n",
       "                                          source_uri legistar_event_id  \\\n",
       "0  http://www.seattlechannel.org/mayor-and-counci...              3915   \n",
       "1  http://www.seattlechannel.org/mayor-and-counci...              3900   \n",
       "2  http://www.seattlechannel.org/FullCouncil?vide...              4165   \n",
       "3  http://www.seattlechannel.org/mayor-and-counci...              4045   \n",
       "4  http://www.seattlechannel.org/mayor-and-counci...              4016   \n",
       "\n",
       "       event_datetime                                    agenda_file_uri  \\\n",
       "0 2019-05-02 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "1 2019-04-24 14:00:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "2 2019-10-21 14:00:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "3 2019-07-31 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "4 2019-07-09 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "\n",
       "                                                name description_event  \\\n",
       "0  Housing, Health, Energy, and Workers’ Rights C...              None   \n",
       "1                Finance and Neighborhoods Committee              None   \n",
       "2                                       City Council              None   \n",
       "3  Gender Equity, Safe Communities, New Americans...              None   \n",
       "4  Civil Rights, Utilities, Economic Development,...              None   \n",
       "\n",
       "                created_body  \n",
       "0 2019-08-08 08:02:05.625714  \n",
       "1 2019-08-08 06:45:28.462804  \n",
       "2 2019-08-08 04:57:38.039158  \n",
       "3 2019-08-08 07:10:43.521005  \n",
       "4 2019-08-08 06:20:23.903175  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the other tables\n",
    "events = pd.DataFrame(seattle.database.select_rows_as_list(\"event\"))\n",
    "bodies = pd.DataFrame(seattle.database.select_rows_as_list(\"body\"))\n",
    "files = pd.DataFrame(seattle.database.select_rows_as_list(\"file\"))\n",
    "\n",
    "# Merge the tables\n",
    "events = events.merge(bodies, left_on=\"body_id\", right_on=\"body_id\", suffixes=(\"_event\", \"_body\"))\n",
    "transcripts = transcripts.merge(files, left_on=\"file_id\", right_on=\"file_id\", suffixes=(\"_transcript\", \"_file\"))\n",
    "transcripts = transcripts.merge(events, left_on=\"event_id\", right_on=\"event_id\", suffixes=(\"_transcript\", \"_event\"))\n",
    "transcripts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Downloading and reading a transcript\n",
    "\n",
    "A manifest is great, but you probably want to work with the transcripts locally."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PosixPath('/home/maxfield/active/cdptools/examples/7e729d2068c5babe6672565664e2c01176a51b759f913d30aab13bcb2cb771f6_ts_sentences_transcript_0.json')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Download to local\n",
    "save_path = seattle.file_store.download_file(transcripts.loc[1][\"filename\"])\n",
    "save_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'text': 'Hi, good afternoon, everybody and welcome to our finance and neighborhoods committee.', 'start_time': 16.0, 'end_time': 20.7}\n",
      "{'text': 'This is our April 24th.', 'start_time': 20.7, 'end_time': 23.5}\n",
      "{'text': '2019 meeting.', 'start_time': 23.5, 'end_time': 25.0}\n"
     ]
    }
   ],
   "source": [
    "# Read the transcript\n",
    "import json\n",
    "with open(save_path, \"r\") as read_in:\n",
    "    transcript = json.load(read_in)\n",
    "    for s in transcript[\"data\"][:3]:\n",
    "        print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling multiple transcripts for a single event\n",
    "\n",
    "A single event may have multiple versions of a transcripts. In this case you can decide how you want to filter them down, but generally, a decent solution is to group by `id_event`  then choose the most recent. You can do this with `pandas.Series.idxmax()`.\n",
    "\n",
    "[stackoverflow](https://stackoverflow.com/questions/10202570/pandas-dataframe-find-row-where-values-for-column-is-maximal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>transcript_id</th>\n",
       "      <th>confidence</th>\n",
       "      <th>event_id</th>\n",
       "      <th>file_id</th>\n",
       "      <th>created_transcript</th>\n",
       "      <th>content_type</th>\n",
       "      <th>filename</th>\n",
       "      <th>description_transcript</th>\n",
       "      <th>created_file</th>\n",
       "      <th>uri</th>\n",
       "      <th>...</th>\n",
       "      <th>created_event</th>\n",
       "      <th>body_id</th>\n",
       "      <th>legistar_event_link</th>\n",
       "      <th>source_uri</th>\n",
       "      <th>legistar_event_id</th>\n",
       "      <th>event_datetime</th>\n",
       "      <th>agenda_file_uri</th>\n",
       "      <th>name</th>\n",
       "      <th>description_event</th>\n",
       "      <th>created_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0992b12d-3062-442c-9852-8d0db89c56f5</td>\n",
       "      <td>0.945244</td>\n",
       "      <td>0684e33e-0582-49b1-b24a-fb94666af072</td>\n",
       "      <td>3cbb94de-ff70-4a56-b048-ccf089914a68</td>\n",
       "      <td>2019-08-08 15:16:43.574340</td>\n",
       "      <td>None</td>\n",
       "      <td>20b624e641163c7c808548ccd81b1035abc3f8f9275eb3...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 15:16:38.656239</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/20b624e641163c7c8...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 15:16:40.904996</td>\n",
       "      <td>fb71fff7-0a11-4dab-a45b-4c65d7098a83</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/CouncilBriefings...</td>\n",
       "      <td>3834</td>\n",
       "      <td>2019-03-11 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Council Briefing</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:46:11.549790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>d0b8fb90-7a82-4024-ab50-4159641f7b30</td>\n",
       "      <td>0.945700</td>\n",
       "      <td>06ce7221-4899-4129-8361-d82fc66992ae</td>\n",
       "      <td>466120db-246a-4218-a070-9123d4427f27</td>\n",
       "      <td>2019-08-08 04:46:41.202181</td>\n",
       "      <td>None</td>\n",
       "      <td>e8d80518f82842c149de46fba8a703bf2cf9c6e4eb3c1b...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:46:35.825902</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/e8d80518f82842c14...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-08 04:46:37.974962</td>\n",
       "      <td>fb71fff7-0a11-4dab-a45b-4c65d7098a83</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/CouncilBriefings...</td>\n",
       "      <td>4041</td>\n",
       "      <td>2019-07-22 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Council Briefing</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:46:11.549790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>72c21e59-5100-4bed-a54c-beea836ba355</td>\n",
       "      <td>0.945288</td>\n",
       "      <td>07f936f4-e454-4712-8813-515c0ceb56af</td>\n",
       "      <td>370839da-3e73-4317-8d9f-d302a51cc623</td>\n",
       "      <td>2019-09-28 00:27:41.417738</td>\n",
       "      <td>None</td>\n",
       "      <td>e4c97bd4a477f5416a98c4a94093ce3cf57463b90ac4da...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-09-28 00:27:33.302149</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/e4c97bd4a477f5416...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-09-28 00:27:35.626002</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4134</td>\n",
       "      <td>2019-09-26 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>7efebc6f-2d30-458a-83a4-3c65bed4057a</td>\n",
       "      <td>0.940499</td>\n",
       "      <td>0b178831-dfa9-468f-80b8-881470e838f0</td>\n",
       "      <td>10fdb1cc-c19c-4340-8869-a3d88933a40d</td>\n",
       "      <td>2019-10-07 22:13:45.913262</td>\n",
       "      <td>None</td>\n",
       "      <td>b7460175bc7e51272b4f66bff0bb3f0eddacc0cb3b5ed4...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-07 22:13:35.999331</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/b7460175bc7e51272...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-10-07 22:13:39.400247</td>\n",
       "      <td>fb71fff7-0a11-4dab-a45b-4c65d7098a83</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/CouncilBriefings...</td>\n",
       "      <td>4146</td>\n",
       "      <td>2019-10-07 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>Council Briefing</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:46:11.549790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>33c55fcf-6ce0-4f35-9b94-f967ad58101b</td>\n",
       "      <td>0.919899</td>\n",
       "      <td>0d36e2b2-5505-401e-81ea-0a459b006732</td>\n",
       "      <td>3316fa96-e6bc-4205-8a20-c426de582d40</td>\n",
       "      <td>2019-08-13 07:20:11.869321</td>\n",
       "      <td>None</td>\n",
       "      <td>675782da8d98c8531f594ac3d8db1236d025063bb2b3cc...</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-13 07:19:10.005527</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/675782da8d98c8531...</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-13 07:19:13.691073</td>\n",
       "      <td>6385675d-e4ce-484e-b35d-3461b52edd22</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>http://www.seattlechannel.org/FullCouncil?vide...</td>\n",
       "      <td>4069</td>\n",
       "      <td>2019-08-12 14:00:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>City Council</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 04:57:38.039158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            transcript_id  confidence  \\\n",
       "6    0992b12d-3062-442c-9852-8d0db89c56f5    0.945244   \n",
       "122  d0b8fb90-7a82-4024-ab50-4159641f7b30    0.945700   \n",
       "72   72c21e59-5100-4bed-a54c-beea836ba355    0.945288   \n",
       "78   7efebc6f-2d30-458a-83a4-3c65bed4057a    0.940499   \n",
       "35   33c55fcf-6ce0-4f35-9b94-f967ad58101b    0.919899   \n",
       "\n",
       "                                 event_id  \\\n",
       "6    0684e33e-0582-49b1-b24a-fb94666af072   \n",
       "122  06ce7221-4899-4129-8361-d82fc66992ae   \n",
       "72   07f936f4-e454-4712-8813-515c0ceb56af   \n",
       "78   0b178831-dfa9-468f-80b8-881470e838f0   \n",
       "35   0d36e2b2-5505-401e-81ea-0a459b006732   \n",
       "\n",
       "                                  file_id         created_transcript  \\\n",
       "6    3cbb94de-ff70-4a56-b048-ccf089914a68 2019-08-08 15:16:43.574340   \n",
       "122  466120db-246a-4218-a070-9123d4427f27 2019-08-08 04:46:41.202181   \n",
       "72   370839da-3e73-4317-8d9f-d302a51cc623 2019-09-28 00:27:41.417738   \n",
       "78   10fdb1cc-c19c-4340-8869-a3d88933a40d 2019-10-07 22:13:45.913262   \n",
       "35   3316fa96-e6bc-4205-8a20-c426de582d40 2019-08-13 07:20:11.869321   \n",
       "\n",
       "    content_type                                           filename  \\\n",
       "6           None  20b624e641163c7c808548ccd81b1035abc3f8f9275eb3...   \n",
       "122         None  e8d80518f82842c149de46fba8a703bf2cf9c6e4eb3c1b...   \n",
       "72          None  e4c97bd4a477f5416a98c4a94093ce3cf57463b90ac4da...   \n",
       "78          None  b7460175bc7e51272b4f66bff0bb3f0eddacc0cb3b5ed4...   \n",
       "35          None  675782da8d98c8531f594ac3d8db1236d025063bb2b3cc...   \n",
       "\n",
       "    description_transcript               created_file  \\\n",
       "6                     None 2019-08-08 15:16:38.656239   \n",
       "122                   None 2019-08-08 04:46:35.825902   \n",
       "72                    None 2019-09-28 00:27:33.302149   \n",
       "78                    None 2019-10-07 22:13:35.999331   \n",
       "35                    None 2019-08-13 07:19:10.005527   \n",
       "\n",
       "                                                   uri  ...  \\\n",
       "6    gs://cdp-seattle.appspot.com/20b624e641163c7c8...  ...   \n",
       "122  gs://cdp-seattle.appspot.com/e8d80518f82842c14...  ...   \n",
       "72   gs://cdp-seattle.appspot.com/e4c97bd4a477f5416...  ...   \n",
       "78   gs://cdp-seattle.appspot.com/b7460175bc7e51272...  ...   \n",
       "35   gs://cdp-seattle.appspot.com/675782da8d98c8531...  ...   \n",
       "\n",
       "                 created_event                               body_id  \\\n",
       "6   2019-08-08 15:16:40.904996  fb71fff7-0a11-4dab-a45b-4c65d7098a83   \n",
       "122 2019-08-08 04:46:37.974962  fb71fff7-0a11-4dab-a45b-4c65d7098a83   \n",
       "72  2019-09-28 00:27:35.626002  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "78  2019-10-07 22:13:39.400247  fb71fff7-0a11-4dab-a45b-4c65d7098a83   \n",
       "35  2019-08-13 07:19:13.691073  6385675d-e4ce-484e-b35d-3461b52edd22   \n",
       "\n",
       "                                   legistar_event_link  \\\n",
       "6    https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "122  https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "72   https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "78   https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "35   https://seattle.legistar.com/MeetingDetail.asp...   \n",
       "\n",
       "                                            source_uri legistar_event_id  \\\n",
       "6    http://www.seattlechannel.org/CouncilBriefings...              3834   \n",
       "122  http://www.seattlechannel.org/CouncilBriefings...              4041   \n",
       "72   http://www.seattlechannel.org/BudgetCommittee?...              4134   \n",
       "78   http://www.seattlechannel.org/CouncilBriefings...              4146   \n",
       "35   http://www.seattlechannel.org/FullCouncil?vide...              4069   \n",
       "\n",
       "         event_datetime                                    agenda_file_uri  \\\n",
       "6   2019-03-11 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "122 2019-07-22 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "72  2019-09-26 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "78  2019-10-07 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "35  2019-08-12 14:00:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "\n",
       "                        name description_event               created_body  \n",
       "6           Council Briefing              None 2019-08-08 04:46:11.549790  \n",
       "122         Council Briefing              None 2019-08-08 04:46:11.549790  \n",
       "72   Select Budget Committee              None 2019-08-08 05:53:34.743281  \n",
       "78          Council Briefing              None 2019-08-08 04:46:11.549790  \n",
       "35              City Council              None 2019-08-08 04:57:38.039158  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group rows\n",
    "most_recent_transcripts = []\n",
    "grouped = transcripts.groupby(\"event_id\")\n",
    "for name, group in grouped:\n",
    "    most_recent = group.loc[group[\"created_transcript\"].idxmax()]\n",
    "    most_recent_transcripts.append(most_recent)\n",
    "    \n",
    "most_recent = pd.DataFrame(most_recent_transcripts)\n",
    "most_recent.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A function to download the most recent transcripts\n",
    "\n",
    "To make gathering the most recent transcripts easier, we have written two helper functions that wrap this example up to do all this for us.\n",
    "\n",
    "_**Note:** The utility functions shown below use complex queries to acheive the same functionality but run server side to make the process even faster. Additionally, they select the transcript with the highest confidence for each event rather than the most recent._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>event_id</th>\n",
       "      <th>source_uri</th>\n",
       "      <th>legistar_event_id</th>\n",
       "      <th>event_datetime</th>\n",
       "      <th>agenda_file_uri</th>\n",
       "      <th>minutes_file_uri</th>\n",
       "      <th>video_uri</th>\n",
       "      <th>created_event</th>\n",
       "      <th>body_id</th>\n",
       "      <th>legistar_event_link</th>\n",
       "      <th>...</th>\n",
       "      <th>file_id</th>\n",
       "      <th>created_transcript</th>\n",
       "      <th>content_type</th>\n",
       "      <th>filename</th>\n",
       "      <th>created_event</th>\n",
       "      <th>description_event</th>\n",
       "      <th>uri</th>\n",
       "      <th>name</th>\n",
       "      <th>description_body</th>\n",
       "      <th>created_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>047109ac-c502-4c0a-98d1-7612a104d244</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4169</td>\n",
       "      <td>2019-11-01 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://video.seattle.gov/media/council/budget...</td>\n",
       "      <td>2019-11-02 04:48:08.980073</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>...</td>\n",
       "      <td>e066cb24-f6bf-4a0c-9ac3-fbf38645cc10</td>\n",
       "      <td>2019-11-02 04:48:47.499943</td>\n",
       "      <td>None</td>\n",
       "      <td>1a3757f22eafdfdc0ed389b2cbda5457c1c66f963e8bf1...</td>\n",
       "      <td>2019-11-02 04:48:05.442495</td>\n",
       "      <td>None</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/1a3757f22eafdfdc0...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0492402b-8dec-43fd-bdc9-55f0744c05db</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4157</td>\n",
       "      <td>2019-10-18 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://video.seattle.gov/media/council/budget...</td>\n",
       "      <td>2019-10-19 19:19:25.649458</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>...</td>\n",
       "      <td>be892865-3c35-46f4-a01f-525890918d1a</td>\n",
       "      <td>2019-10-19 19:19:30.590282</td>\n",
       "      <td>None</td>\n",
       "      <td>a20233822ca8e28dd95763e60b11834fc43346b3a8771a...</td>\n",
       "      <td>2019-10-19 19:19:22.103761</td>\n",
       "      <td>None</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/a20233822ca8e28dd...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07f936f4-e454-4712-8813-515c0ceb56af</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4134</td>\n",
       "      <td>2019-09-26 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://video.seattle.gov/media/council/budget...</td>\n",
       "      <td>2019-09-28 00:27:35.626002</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>...</td>\n",
       "      <td>370839da-3e73-4317-8d9f-d302a51cc623</td>\n",
       "      <td>2019-09-28 00:27:41.417738</td>\n",
       "      <td>None</td>\n",
       "      <td>e4c97bd4a477f5416a98c4a94093ce3cf57463b90ac4da...</td>\n",
       "      <td>2019-09-28 00:27:33.302149</td>\n",
       "      <td>None</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/e4c97bd4a477f5416...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1101bdea-009c-48aa-a618-546e8204e0e7</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4158</td>\n",
       "      <td>2019-10-21 14:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://video.seattle.gov/media/council/budget...</td>\n",
       "      <td>2019-10-22 17:50:08.769328</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>...</td>\n",
       "      <td>7c8007e2-3afd-4e6e-86cd-1ff6405de290</td>\n",
       "      <td>2019-10-22 17:50:13.616808</td>\n",
       "      <td>None</td>\n",
       "      <td>18d02fab377743108df48535f14f64b6f7dd1682f48f96...</td>\n",
       "      <td>2019-10-22 17:50:05.013769</td>\n",
       "      <td>None</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/18d02fab377743108...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1e085d19-f880-4f55-9127-3e0006dc3b41</td>\n",
       "      <td>http://www.seattlechannel.org/BudgetCommittee?...</td>\n",
       "      <td>4159</td>\n",
       "      <td>2019-10-22 09:30:00</td>\n",
       "      <td>http://legistar2.granicus.com/seattle/meetings...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://video.seattle.gov/media/council/budget...</td>\n",
       "      <td>2019-10-23 20:57:57.981081</td>\n",
       "      <td>07d342d1-86f6-4074-9206-bcb45aab017e</td>\n",
       "      <td>https://seattle.legistar.com/MeetingDetail.asp...</td>\n",
       "      <td>...</td>\n",
       "      <td>90d25712-d96c-498a-aff7-0ea1d1db14e3</td>\n",
       "      <td>2019-10-23 20:58:01.704461</td>\n",
       "      <td>None</td>\n",
       "      <td>85622ec63f60e791dbb47510daf159bd9668abdb8e68b8...</td>\n",
       "      <td>2019-10-23 20:57:54.087763</td>\n",
       "      <td>None</td>\n",
       "      <td>gs://cdp-seattle.appspot.com/85622ec63f60e791d...</td>\n",
       "      <td>Select Budget Committee</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-08-08 05:53:34.743281</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               event_id  \\\n",
       "0  047109ac-c502-4c0a-98d1-7612a104d244   \n",
       "1  0492402b-8dec-43fd-bdc9-55f0744c05db   \n",
       "2  07f936f4-e454-4712-8813-515c0ceb56af   \n",
       "3  1101bdea-009c-48aa-a618-546e8204e0e7   \n",
       "4  1e085d19-f880-4f55-9127-3e0006dc3b41   \n",
       "\n",
       "                                          source_uri  legistar_event_id  \\\n",
       "0  http://www.seattlechannel.org/BudgetCommittee?...               4169   \n",
       "1  http://www.seattlechannel.org/BudgetCommittee?...               4157   \n",
       "2  http://www.seattlechannel.org/BudgetCommittee?...               4134   \n",
       "3  http://www.seattlechannel.org/BudgetCommittee?...               4158   \n",
       "4  http://www.seattlechannel.org/BudgetCommittee?...               4159   \n",
       "\n",
       "       event_datetime                                    agenda_file_uri  \\\n",
       "0 2019-11-01 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "1 2019-10-18 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "2 2019-09-26 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "3 2019-10-21 14:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "4 2019-10-22 09:30:00  http://legistar2.granicus.com/seattle/meetings...   \n",
       "\n",
       "  minutes_file_uri                                          video_uri  \\\n",
       "0             None  https://video.seattle.gov/media/council/budget...   \n",
       "1             None  https://video.seattle.gov/media/council/budget...   \n",
       "2             None  https://video.seattle.gov/media/council/budget...   \n",
       "3             None  https://video.seattle.gov/media/council/budget...   \n",
       "4             None  https://video.seattle.gov/media/council/budget...   \n",
       "\n",
       "               created_event                               body_id  \\\n",
       "0 2019-11-02 04:48:08.980073  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "1 2019-10-19 19:19:25.649458  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "2 2019-09-28 00:27:35.626002  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "3 2019-10-22 17:50:08.769328  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "4 2019-10-23 20:57:57.981081  07d342d1-86f6-4074-9206-bcb45aab017e   \n",
       "\n",
       "                                 legistar_event_link  ...  \\\n",
       "0  https://seattle.legistar.com/MeetingDetail.asp...  ...   \n",
       "1  https://seattle.legistar.com/MeetingDetail.asp...  ...   \n",
       "2  https://seattle.legistar.com/MeetingDetail.asp...  ...   \n",
       "3  https://seattle.legistar.com/MeetingDetail.asp...  ...   \n",
       "4  https://seattle.legistar.com/MeetingDetail.asp...  ...   \n",
       "\n",
       "                                file_id         created_transcript  \\\n",
       "0  e066cb24-f6bf-4a0c-9ac3-fbf38645cc10 2019-11-02 04:48:47.499943   \n",
       "1  be892865-3c35-46f4-a01f-525890918d1a 2019-10-19 19:19:30.590282   \n",
       "2  370839da-3e73-4317-8d9f-d302a51cc623 2019-09-28 00:27:41.417738   \n",
       "3  7c8007e2-3afd-4e6e-86cd-1ff6405de290 2019-10-22 17:50:13.616808   \n",
       "4  90d25712-d96c-498a-aff7-0ea1d1db14e3 2019-10-23 20:58:01.704461   \n",
       "\n",
       "  content_type                                           filename  \\\n",
       "0         None  1a3757f22eafdfdc0ed389b2cbda5457c1c66f963e8bf1...   \n",
       "1         None  a20233822ca8e28dd95763e60b11834fc43346b3a8771a...   \n",
       "2         None  e4c97bd4a477f5416a98c4a94093ce3cf57463b90ac4da...   \n",
       "3         None  18d02fab377743108df48535f14f64b6f7dd1682f48f96...   \n",
       "4         None  85622ec63f60e791dbb47510daf159bd9668abdb8e68b8...   \n",
       "\n",
       "               created_event description_event  \\\n",
       "0 2019-11-02 04:48:05.442495              None   \n",
       "1 2019-10-19 19:19:22.103761              None   \n",
       "2 2019-09-28 00:27:33.302149              None   \n",
       "3 2019-10-22 17:50:05.013769              None   \n",
       "4 2019-10-23 20:57:54.087763              None   \n",
       "\n",
       "                                                 uri                     name  \\\n",
       "0  gs://cdp-seattle.appspot.com/1a3757f22eafdfdc0...  Select Budget Committee   \n",
       "1  gs://cdp-seattle.appspot.com/a20233822ca8e28dd...  Select Budget Committee   \n",
       "2  gs://cdp-seattle.appspot.com/e4c97bd4a477f5416...  Select Budget Committee   \n",
       "3  gs://cdp-seattle.appspot.com/18d02fab377743108...  Select Budget Committee   \n",
       "4  gs://cdp-seattle.appspot.com/85622ec63f60e791d...  Select Budget Committee   \n",
       "\n",
       "  description_body               created_body  \n",
       "0             None 2019-08-08 05:53:34.743281  \n",
       "1             None 2019-08-08 05:53:34.743281  \n",
       "2             None 2019-08-08 05:53:34.743281  \n",
       "3             None 2019-08-08 05:53:34.743281  \n",
       "4             None 2019-08-08 05:53:34.743281  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transcript_manifest = seattle.get_transcript_manifest()\n",
    "transcript_manifest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "047109ac-c502-4c0a-98d1-7612a104d244 /home/maxfield/active/cdptools/examples/1a3757f22eafdfdc0ed389b2cbda5457c1c66f963e8bf15545d01dc1b17fdf76_ts_sentences_transcript_0.json\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/maxfield/active/cdptools/cdptools/research_utils/transcripts.py:177: UserWarning: DataFrame columns are not unique, some columns will be omitted.\n",
      "  for transcript_details in selected_transcripts.to_dict(\"records\"):\n"
     ]
    }
   ],
   "source": [
    "event_corpus_map = seattle.download_transcripts()\n",
    "for event_id, path in event_corpus_map.items():\n",
    "    print(event_id, path)\n",
    "    break"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}