packet_parsing__index_plumbing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "adcd4dfd",
   "metadata": {},
   "source": [
    "# Packet Parsing - Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "53e6bf50",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pdfplumber\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dbe6bf14",
   "metadata": {},
   "outputs": [],
   "source": [
    "comms_index_pg_start = 0\n",
    "comms_index_pg_end = 1\n",
    "# comms_fpath = \"./pubcomm/raw_pdfs/7_11_2023; CLK - Communication; Public; ; REGULAR; Supplemental Communicatons 2.pdf\"\n",
    "comms_fpath = \"./raw_pdfs/7_11_2023; CLK - Communication; Public; ; REGULAR; Supplemental Communicatons 3.pdf\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "2f0da42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf = pdfplumber.open(comms_fpath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "9fb52ef7",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Use pdfplumber or OCR\n",
    "### Index sometimes can be plumbed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "9515f9f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Page:1>, <Page:2>]"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_pages = pdf.pages[comms_index_pg_start: comms_index_pg_end + 1]\n",
    "index_pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "16a91e52",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for page in index_pages:\n",
    "    print(page.extract_text())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "3ba0a061",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_pages[1].extract_text()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1850ff60",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pytesseract\n",
    "\n",
    "from pdf2image import convert_from_path\n",
    "from PIL import Image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "09856f0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pages = convert_from_path(comms_fpath, 350)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9fbdbf94",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf_folder = \"./pdf_images_3/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9a7ba7ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CAN SKIP - ALREADY SAVED DURING CONTENT PARSING\n",
    "# for idx, page in enumerate(pages[comms_index_pg_start: comms_index_pg_end + 1]):\n",
    "#     # Save pages as images in the pdf\n",
    "#     page.save(index_pdf_folder + 'page'+ str(idx) +'.jpg', 'JPEG')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1d9e60e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_text_from_pg_img(img_path):\n",
    "    return pytesseract.image_to_string(Image.open(img_path))\n",
    "\n",
    "def get_all_files(dir_path):\n",
    "    \"\"\"\n",
    "    Lists all files in specified directory.\n",
    "\n",
    "    `dir_path`: path to target directory\n",
    "    \"\"\"\n",
    "    dir_list = os.listdir(dir_path)\n",
    "\n",
    "    print(\"Files and directories in '\", dir_path, \"' :\")\n",
    "    print(dir_list)\n",
    "    return sorted(dir_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "db9e629c",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"./index_set_3.txt\", \"w\") as f:\n",
    "    for img_path_idx in range(comms_index_pg_start, comms_index_pg_end + 1):\n",
    "        img_path = f\"{pdf_folder}page{img_path_idx}.jpg\"\n",
    "        page_text = get_text_from_pg_img(img_path)\n",
    "        f.write(page_text)\n",
    "        f.write(\"\\n----\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "21355205",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"./index_set_3.txt\", \"r\") as f:\n",
    "    text = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ac946458",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['SUPPLEMENTAL',\n",
       "  'COMMUNICATIONS AND',\n",
       "  'REPORTS 3.',\n",
       "  '',\n",
       "  'BERKELEY CITY COUNCIL',\n",
       "  'REGULAR MEETING',\n",
       "  '',\n",
       "  'DATE OF MEETING: TUESDAY, JULY 11, 2023',\n",
       "  'TIME: 6:00 P.M.',\n",
       "  '',\n",
       "  'The agenda packet for this meeting was distributed/posted on June 29, 2023. Communications in this',\n",
       "  'supplement were received after 12pm on July 10, 2023. This communication packet was distributed/posted',\n",
       "  'on July 13, 2023.',\n",
       "  '',\n",
       "  'Consent Calendar',\n",
       "  '',\n",
       "  'Each item in this supplement follows the corresponding item on the City Council Agenda',\n",
       "  'for this date.'],\n",
       " ['#7: Contract: Insight Housing (formerly Berkeley Food and Housing Project)',\n",
       "  'to operate interim housing at the Super 8',\n",
       "  '166. Steve Lipson'],\n",
       " ['#28: Relinquishment of Council Office Budget Funds and Co-Sponsorship of',\n",
       "  'the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project',\n",
       "  '167. Beatriz Leyva-Cutler and Santiago Casal —',\n",
       "  '',\n",
       "  'item #29: Resolution Opposing Tokyo Electric Power Company and the',\n",
       "  'Government of Japan’s Planned Discharge of Wastewater from Fukushima',\n",
       "  'Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  '',\n",
       "  '168. Tsukuru Fors',\n",
       "  '',\n",
       "  '169. Phoebe Thomas Sorgen',\n",
       "  '',\n",
       "  '170. David McGuire',\n",
       "  '',\n",
       "  '171. Torrance Carroll',\n",
       "  '',\n",
       "  '172. Bindu Desai',\n",
       "  '',\n",
       "  '173. Eva Frye',\n",
       "  '',\n",
       "  '174. Yuka Saito',\n",
       "  '',\n",
       "  '175. Laura Feldman',\n",
       "  '',\n",
       "  '176. Linda Roman',\n",
       "  '',\n",
       "  '177. Liz Taylor',\n",
       "  '',\n",
       "  '----',\n",
       "  '178. Jacqueline Cabasso, on behalf of Western States Legal Foundation',\n",
       "  '179. Eri Tomita',\n",
       "  '',\n",
       "  '180. Heiko Hasegawa',\n",
       "  '',\n",
       "  '181. Desiree McGill',\n",
       "  '',\n",
       "  'Action Calendar — New Business'],\n",
       " ['#30: Zoning Ordinance Amendments to Title 23 of the Berkeley municipal',\n",
       "  'Code to Conform to State Law, and Make Non-Substantive Technical Edits',\n",
       "  '182. Presentation, submitted by Planning and Development',\n",
       "  '',\n",
       "  'Action Calendar — New Business'],\n",
       " ['#31: Staff Shortages: City Services Constrained by Staff Retention Challenges',\n",
       "  'and Delayed Hiring',\n",
       "  '',\n",
       "  '183. Presentation, submitted by the Auditor',\n",
       "  '',\n",
       "  '184. Presentation, submitted by the City Manager'],\n",
       " ['#32: Referral Response: Affordable Housing Preference Policy for Rental',\n",
       "  'Housing Created Through the Below Market Rate and Housing Trust Fund Programs',\n",
       "  '185. Derethia DuVal'],\n",
       " ['#33: Adoption — Civic Center Phase II — Design Concept',\n",
       "  '186. Linda Currie',\n",
       "  '187. Beth Lamont',\n",
       "  '188. Diane Ross-Leech',\n",
       "  '189. Kelly Hammargren',\n",
       "  '190. Tom Kelly .',\n",
       "  '191. Kathleen Giustino : oy',\n",
       "  '192. Erin Diehm',\n",
       "  '193. Andra Mitchell',\n",
       "  '194. Jack Kurzweil',\n",
       "  '',\n",
       "  'Action Calendar'],\n",
       " ['#35: De-Prioritizing the enforcement of laws imposing criminal penalties for',\n",
       "  'Entheogenic/Psychedelic plants and fungi for personal use',\n",
       "  '195. IsabelleGaston ©',\n",
       "  '',\n",
       "  'Miscellaneous Communications',\n",
       "  '196. Mr. Eid',\n",
       "  '',\n",
       "  '----']]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_speakers_list = [item.strip().split(\"\\n\") for item in text.split(\"Item \")]\n",
    "items_speakers_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d49a22a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'item_num': '7',\n",
       "  'item_subject': 'Contract: Insight Housing (formerly Berkeley Food and Housing Project) to operate interim housing at the Super 8',\n",
       "  'comms_num': '166',\n",
       "  'name': 'Steve Lipson'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project',\n",
       "  'comms_num': '167',\n",
       "  'name': 'Beatriz Leyva'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '168',\n",
       "  'name': 'Tsukuru Fors'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '169',\n",
       "  'name': 'Phoebe Thomas'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '170',\n",
       "  'name': 'David McGuire'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '171',\n",
       "  'name': 'Torrance Carroll'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '172',\n",
       "  'name': 'Bindu Desai'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '173',\n",
       "  'name': 'Eva Frye'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '174',\n",
       "  'name': 'Yuka Saito'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '175',\n",
       "  'name': 'Laura Feldman'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '176',\n",
       "  'name': 'Linda Roman'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean',\n",
       "  'comms_num': '177',\n",
       "  'name': 'Liz Taylor'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean            ----',\n",
       "  'comms_num': '178',\n",
       "  'name': 'Jacqueline Cabasso'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean            ----',\n",
       "  'comms_num': '179',\n",
       "  'name': 'Eri Tomita'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean            ----',\n",
       "  'comms_num': '180',\n",
       "  'name': 'Heiko Hasegawa'},\n",
       " {'item_num': '28',\n",
       "  'item_subject': 'Relinquishment of Council Office Budget Funds and Co-Sponsorship of the Cesar Chavez Dolores Huerta Tribute Site Curriculum Project  item #29: Resolution Opposing Tokyo Electric Power Company and the Government of Japan’s Planned Discharge of Wastewater from Fukushima Daiichi Nuclear Power Plant into the Pacific Ocean            ----',\n",
       "  'comms_num': '181',\n",
       "  'name': 'Desiree McGill'},\n",
       " {'item_num': '30',\n",
       "  'item_subject': 'Zoning Ordinance Amendments to Title 23 of the Berkeley municipal Code to Conform to State Law, and Make Non-Substantive Technical Edits',\n",
       "  'comms_num': '182',\n",
       "  'name': 'Presentation'},\n",
       " {'item_num': '31',\n",
       "  'item_subject': 'Staff Shortages: City Services Constrained by Staff Retention Challenges and Delayed Hiring',\n",
       "  'comms_num': '183',\n",
       "  'name': 'Presentation'},\n",
       " {'item_num': '31',\n",
       "  'item_subject': 'Staff Shortages: City Services Constrained by Staff Retention Challenges and Delayed Hiring',\n",
       "  'comms_num': '184',\n",
       "  'name': 'Presentation'},\n",
       " {'item_num': '32',\n",
       "  'item_subject': 'Referral Response: Affordable Housing Preference Policy for Rental Housing Created Through the Below Market Rate and Housing Trust Fund Programs',\n",
       "  'comms_num': '185',\n",
       "  'name': 'Derethia DuVal'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '186',\n",
       "  'name': 'Linda Currie'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '187',\n",
       "  'name': 'Beth Lamont'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '188',\n",
       "  'name': 'Diane Ross'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '189',\n",
       "  'name': 'Kelly Hammargren'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '190',\n",
       "  'name': 'Tom Kelly'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '191',\n",
       "  'name': 'Kathleen Giustino'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '192',\n",
       "  'name': 'Erin Diehm'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '193',\n",
       "  'name': 'Andra Mitchell'},\n",
       " {'item_num': '33',\n",
       "  'item_subject': 'Adoption — Civic Center Phase II — Design Concept',\n",
       "  'comms_num': '194',\n",
       "  'name': 'Jack Kurzweil'},\n",
       " {'item_num': '35',\n",
       "  'item_subject': 'De-Prioritizing the enforcement of laws imposing criminal penalties for Entheogenic/Psychedelic plants and fungi for personal use',\n",
       "  'comms_num': '195',\n",
       "  'name': 'IsabelleGaston '},\n",
       " {'item_num': '35',\n",
       "  'item_subject': 'De-Prioritizing the enforcement of laws imposing criminal penalties for Entheogenic/Psychedelic plants and fungi for personal use  Miscellaneous Communications',\n",
       "  'comms_num': '196',\n",
       "  'name': 'Mr'}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_speakers_dict_list = []\n",
    "for item_speakers in items_speakers_list[1:]:\n",
    "    item = \"\"\n",
    "    for line in item_speakers:\n",
    "        m = re.search(r\"([0-9]+)\\.\\s*([-A-Za-z]+\\s*[A-Z]*)\", line, re.IGNORECASE)\n",
    "        if not m:\n",
    "            item = f\"{item} {line}\"\n",
    "        else:\n",
    "            name = m.group(2)\n",
    "            if name not in [\"Supplemental material\", \"Revised material\"]:\n",
    "                try:\n",
    "                    item_speaker_obj = {\n",
    "                        \"item_num\": re.search(r\"#([0-9]+):\", item).group(1),\n",
    "                        \"item_subject\": re.search(r\"#[0-9]+:\\s*(.*\\s*.*)\", item).group(1).strip(),\n",
    "                        \"comms_num\": m.group(1),\n",
    "                        \"name\": m.group(2)\n",
    "                    }\n",
    "                    item_speakers_dict_list.append(item_speaker_obj)\n",
    "                except:\n",
    "                    print(item)\n",
    "item_speakers_dict_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "bb2831e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_speakers_df = pd.DataFrame(item_speakers_dict_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cafbdb7c",
   "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>item_num</th>\n",
       "      <th>item_subject</th>\n",
       "      <th>comms_num</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>Contract: Insight Housing (formerly Berkeley F...</td>\n",
       "      <td>166</td>\n",
       "      <td>Steve Lipson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>167</td>\n",
       "      <td>Beatriz Leyva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>168</td>\n",
       "      <td>Tsukuru Fors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>169</td>\n",
       "      <td>Phoebe Thomas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>170</td>\n",
       "      <td>David McGuire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>171</td>\n",
       "      <td>Torrance Carroll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>172</td>\n",
       "      <td>Bindu Desai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>173</td>\n",
       "      <td>Eva Frye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>174</td>\n",
       "      <td>Yuka Saito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>175</td>\n",
       "      <td>Laura Feldman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>176</td>\n",
       "      <td>Linda Roman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>177</td>\n",
       "      <td>Liz Taylor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>178</td>\n",
       "      <td>Jacqueline Cabasso</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>179</td>\n",
       "      <td>Eri Tomita</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>180</td>\n",
       "      <td>Heiko Hasegawa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>28</td>\n",
       "      <td>Relinquishment of Council Office Budget Funds ...</td>\n",
       "      <td>181</td>\n",
       "      <td>Desiree McGill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>30</td>\n",
       "      <td>Zoning Ordinance Amendments to Title 23 of the...</td>\n",
       "      <td>182</td>\n",
       "      <td>Presentation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>31</td>\n",
       "      <td>Staff Shortages: City Services Constrained by ...</td>\n",
       "      <td>183</td>\n",
       "      <td>Presentation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>31</td>\n",
       "      <td>Staff Shortages: City Services Constrained by ...</td>\n",
       "      <td>184</td>\n",
       "      <td>Presentation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>32</td>\n",
       "      <td>Referral Response: Affordable Housing Preferen...</td>\n",
       "      <td>185</td>\n",
       "      <td>Derethia DuVal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>186</td>\n",
       "      <td>Linda Currie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>187</td>\n",
       "      <td>Beth Lamont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>188</td>\n",
       "      <td>Diane Ross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>189</td>\n",
       "      <td>Kelly Hammargren</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>190</td>\n",
       "      <td>Tom Kelly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>191</td>\n",
       "      <td>Kathleen Giustino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>192</td>\n",
       "      <td>Erin Diehm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>193</td>\n",
       "      <td>Andra Mitchell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>33</td>\n",
       "      <td>Adoption — Civic Center Phase II — Design Concept</td>\n",
       "      <td>194</td>\n",
       "      <td>Jack Kurzweil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>35</td>\n",
       "      <td>De-Prioritizing the enforcement of laws imposi...</td>\n",
       "      <td>195</td>\n",
       "      <td>IsabelleGaston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>35</td>\n",
       "      <td>De-Prioritizing the enforcement of laws imposi...</td>\n",
       "      <td>196</td>\n",
       "      <td>Mr</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item_num                                       item_subject comms_num  \\\n",
       "0         7  Contract: Insight Housing (formerly Berkeley F...       166   \n",
       "1        28  Relinquishment of Council Office Budget Funds ...       167   \n",
       "2        28  Relinquishment of Council Office Budget Funds ...       168   \n",
       "3        28  Relinquishment of Council Office Budget Funds ...       169   \n",
       "4        28  Relinquishment of Council Office Budget Funds ...       170   \n",
       "5        28  Relinquishment of Council Office Budget Funds ...       171   \n",
       "6        28  Relinquishment of Council Office Budget Funds ...       172   \n",
       "7        28  Relinquishment of Council Office Budget Funds ...       173   \n",
       "8        28  Relinquishment of Council Office Budget Funds ...       174   \n",
       "9        28  Relinquishment of Council Office Budget Funds ...       175   \n",
       "10       28  Relinquishment of Council Office Budget Funds ...       176   \n",
       "11       28  Relinquishment of Council Office Budget Funds ...       177   \n",
       "12       28  Relinquishment of Council Office Budget Funds ...       178   \n",
       "13       28  Relinquishment of Council Office Budget Funds ...       179   \n",
       "14       28  Relinquishment of Council Office Budget Funds ...       180   \n",
       "15       28  Relinquishment of Council Office Budget Funds ...       181   \n",
       "16       30  Zoning Ordinance Amendments to Title 23 of the...       182   \n",
       "17       31  Staff Shortages: City Services Constrained by ...       183   \n",
       "18       31  Staff Shortages: City Services Constrained by ...       184   \n",
       "19       32  Referral Response: Affordable Housing Preferen...       185   \n",
       "20       33  Adoption — Civic Center Phase II — Design Concept       186   \n",
       "21       33  Adoption — Civic Center Phase II — Design Concept       187   \n",
       "22       33  Adoption — Civic Center Phase II — Design Concept       188   \n",
       "23       33  Adoption — Civic Center Phase II — Design Concept       189   \n",
       "24       33  Adoption — Civic Center Phase II — Design Concept       190   \n",
       "25       33  Adoption — Civic Center Phase II — Design Concept       191   \n",
       "26       33  Adoption — Civic Center Phase II — Design Concept       192   \n",
       "27       33  Adoption — Civic Center Phase II — Design Concept       193   \n",
       "28       33  Adoption — Civic Center Phase II — Design Concept       194   \n",
       "29       35  De-Prioritizing the enforcement of laws imposi...       195   \n",
       "30       35  De-Prioritizing the enforcement of laws imposi...       196   \n",
       "\n",
       "                  name  \n",
       "0         Steve Lipson  \n",
       "1        Beatriz Leyva  \n",
       "2         Tsukuru Fors  \n",
       "3        Phoebe Thomas  \n",
       "4        David McGuire  \n",
       "5     Torrance Carroll  \n",
       "6          Bindu Desai  \n",
       "7             Eva Frye  \n",
       "8           Yuka Saito  \n",
       "9        Laura Feldman  \n",
       "10         Linda Roman  \n",
       "11          Liz Taylor  \n",
       "12  Jacqueline Cabasso  \n",
       "13          Eri Tomita  \n",
       "14      Heiko Hasegawa  \n",
       "15      Desiree McGill  \n",
       "16        Presentation  \n",
       "17        Presentation  \n",
       "18        Presentation  \n",
       "19      Derethia DuVal  \n",
       "20        Linda Currie  \n",
       "21         Beth Lamont  \n",
       "22          Diane Ross  \n",
       "23    Kelly Hammargren  \n",
       "24           Tom Kelly  \n",
       "25   Kathleen Giustino  \n",
       "26          Erin Diehm  \n",
       "27      Andra Mitchell  \n",
       "28       Jack Kurzweil  \n",
       "29     IsabelleGaston   \n",
       "30                  Mr  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_speakers_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b4f6d91e",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_speakers_df.to_csv(\"./item_speakers_set_3.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cab0dd6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}