Parse SD monthly voter registration report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6507088a-306a-47d1-97f7-57f3d07ad247",
   "metadata": {},
   "source": [
    "# Parse SD monthly voter registration report\n",
    "\n",
    "Uses:\n",
    "- `requests` to get the PDF from [the Secretary of State's website](https://sdsos.gov/elections-voting/upcoming-elections/voter-registration-totals/voter-registration-by-county.aspx)\n",
    "- `pdfplumber` to parse and validate the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b2b53d3d-b352-40e3-9e9f-b8af14d72735",
   "metadata": {},
   "outputs": [],
   "source": [
    "from io import BytesIO\n",
    "from datetime import datetime\n",
    "import csv\n",
    "from pathlib import Path\n",
    "\n",
    "import requests\n",
    "import pdfplumber"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3c20d214-6797-4301-bdc4-037cff26fb60",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://sdsos.gov/elections-voting/assets/StatewideVoterRegistrationTotals/StatewideVotersByCounty_11.5.2024.pdf'\n",
    "req = requests.get(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "be80d888-da26-44bb-baa4-226bfc0314d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf = pdfplumber.open(BytesIO(req.content))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "75b29825-8e5d-4a99-9bc4-5d5eb05a678b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assumes a 2-page PDF with a table spanning both pages\n",
    "if len(pdf.pages) != 2:\n",
    "    raise Exception(f'Report page count is {len(pdf.pages)}, not 2 -- check it out: {url}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "00eae6d5-a16b-4d97-94aa-1a6d632b2eb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "lines = pdf.pages[0].extract_table() + pdf.pages[1].extract_table()\n",
    "\n",
    "# guarding against some PDFs parsed with an extra column\n",
    "lines = [x[:-1] if not x[-1] else x for x in lines]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fcabf216-1de9-4626-8ce4-395e59da2bc7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['Statewide Report by County - November 5, 2024',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['County',\n",
       "  'Democratic',\n",
       "  'Libertarian',\n",
       "  'Republican',\n",
       "  'Independent',\n",
       "  'OTH',\n",
       "  'No Party\\nAffiliation',\n",
       "  'No Labels',\n",
       "  'Inactive',\n",
       "  'Total Active'],\n",
       " ['Aurora', '518', '2', '982', '230', '2', '100', '', '107', '1834'],\n",
       " ['Beadle',\n",
       "  '2,501',\n",
       "  '26',\n",
       "  '5,113',\n",
       "  '1,383',\n",
       "  '41',\n",
       "  '1,123',\n",
       "  '1',\n",
       "  '787',\n",
       "  '10188'],\n",
       " ['Bennett', '685', '6', '727', '207', '2', '197', '', '190', '1824'],\n",
       " ['Bon Homme', '888', '13', '2,369', '480', '', '230', '', '291', '3980'],\n",
       " ['Brookings',\n",
       "  '5,103',\n",
       "  '106',\n",
       "  '9,938',\n",
       "  '3,624',\n",
       "  '21',\n",
       "  '2,794',\n",
       "  '1',\n",
       "  '1594',\n",
       "  '21587'],\n",
       " ['Brown',\n",
       "  '7,278',\n",
       "  '99',\n",
       "  '12,860',\n",
       "  '3,582',\n",
       "  '10',\n",
       "  '1,950',\n",
       "  '2',\n",
       "  '1132',\n",
       "  '25781'],\n",
       " ['Brule', '903', '15', '1,746', '466', '4', '213', '', '173', '3347'],\n",
       " ['Buffalo', '533', '2', '151', '73', '4', '94', '', '120', '857'],\n",
       " ['Butte', '777', '56', '4,572', '877', '19', '641', '', '1365', '6942'],\n",
       " ['Campbell', '104', '1', '909', '76', '', '40', '', '73', '1130'],\n",
       " ['Charles Mix', '1,786', '9', '2,713', '588', '', '343', '', '446', '5439'],\n",
       " ['Clark', '543', '4', '1,641', '265', '', '152', '', '142', '2605'],\n",
       " ['Clay', '2,894', '37', '3,037', '1,439', '11', '1,133', '5', '1231', '8556'],\n",
       " ['Codington',\n",
       "  '3,894',\n",
       "  '63',\n",
       "  '10,431',\n",
       "  '2,605',\n",
       "  '28',\n",
       "  '1,832',\n",
       "  '',\n",
       "  '1625',\n",
       "  '18853'],\n",
       " ['Corson', '969', '5', '742', '282', '2', '284', '', '234', '2284'],\n",
       " ['Custer', '1,055', '36', '5,110', '1,114', '23', '655', '1', '992', '7994'],\n",
       " ['Davison',\n",
       "  '2,785',\n",
       "  '33',\n",
       "  '6,857',\n",
       "  '1,905',\n",
       "  '36',\n",
       "  '1,471',\n",
       "  '3',\n",
       "  '1275',\n",
       "  '13090'],\n",
       " ['Day', '1,269', '11', '1,910', '588', '', '164', '', '228', '3942'],\n",
       " ['Deuel', '592', '10', '1,762', '485', '', '276', '', '122', '3125'],\n",
       " ['Dewey', '1,774', '3', '765', '426', '', '383', '', '241', '3351'],\n",
       " ['Douglas', '205', '1', '1,665', '144', '', '93', '', '114', '2108'],\n",
       " ['Edmunds', '519', '7', '1,777', '255', '2', '169', '', '143', '2729'],\n",
       " ['Fall River', '893', '41', '3,950', '904', '2', '641', '1', '398', '6432'],\n",
       " ['Faulk', '195', '1', '1,127', '217', '', '39', '', '60', '1579'],\n",
       " ['Grant', '1,058', '18', '2,724', '706', '11', '342', '', '341', '4859'],\n",
       " ['Gregory', '617', '4', '1,853', '343', '', '85', '', '155', '2902'],\n",
       " ['Haakon', '117', '2', '1,168', '86', '2', '29', '', '62', '1404'],\n",
       " ['Hamlin', '543', '7', '3,066', '371', '', '254', '', '224', '4241'],\n",
       " ['Hand', '402', '7', '1,649', '187', '4', '51', '', '97', '2300'],\n",
       " ['Hanson', '599', '6', '1,882', '495', '6', '367', '', '487', '3355'],\n",
       " ['Harding', '63', '6', '835', '75', '1', '42', '', '51', '1022'],\n",
       " ['Hughes', '2,146', '46', '7,345', '1,401', '18', '936', '', '1046', '11892'],\n",
       " ['Hutchinson', '719', '14', '3,427', '525', '8', '323', '', '214', '5016'],\n",
       " ['Hyde', '150', '1', '657', '95', '', '29', '', '43', '932'],\n",
       " ['Jackson', '583', '6', '916', '166', '1', '164', '', '131', '1836'],\n",
       " ['Jerauld', '330', '6', '721', '132', '1', '68', '', '70', '1258'],\n",
       " ['Jones', '62', '1', '525', '60', '', '28', '1', '53', '677'],\n",
       " ['Kingsbury', '672', '7', '2,297', '395', '2', '310', '', '282', '3683'],\n",
       " ['Lake', '1,903', '25', '4,327', '960', '5', '606', '2', '2140', '7828'],\n",
       " ['Lawrence',\n",
       "  '3,938',\n",
       "  '173',\n",
       "  '12,171',\n",
       "  '3,263',\n",
       "  '41',\n",
       "  '2,626',\n",
       "  '4',\n",
       "  '2396',\n",
       "  '22216'],\n",
       " ['Lincoln',\n",
       "  '10,127',\n",
       "  '231',\n",
       "  '25,302',\n",
       "  '7,728',\n",
       "  '80',\n",
       "  '5,277',\n",
       "  '10',\n",
       "  '5025',\n",
       "  '48755'],\n",
       " ['Lyman', '658', '4', '1,160', '218', '1', '151', '', '108', '2192'],\n",
       " ['Marshall', '966', '2', '1,305', '383', '2', '168', '', '165', '2826'],\n",
       " ['McCook', '793', '8', '2,389', '523', '3', '271', '', '150', '3987'],\n",
       " ['McPherson', '191', '8', '1,331', '132', '', '68', '', '163', '1730'],\n",
       " ['Meade',\n",
       "  '2,818',\n",
       "  '155',\n",
       "  '12,840',\n",
       "  '2,837',\n",
       "  '20',\n",
       "  '2,099',\n",
       "  '2',\n",
       "  '1637',\n",
       "  '20771'],\n",
       " ['Mellette', '411', '1', '502', '182', '', '63', '', '64', '1159'],\n",
       " ['Miner', '433', '5', '846', '233', '', '110', '', '81', '1627'],\n",
       " ['Minnehaha',\n",
       "  '36,159',\n",
       "  '740',\n",
       "  '58,652',\n",
       "  '22,570',\n",
       "  '253',\n",
       "  '18,426',\n",
       "  '16',\n",
       "  '17710',\n",
       "  '136816'],\n",
       " ['Moody', '1,307', '15', '1,945', '702', '4', '412', '1', '210', '4386'],\n",
       " ['Oglala Lakota',\n",
       "  '5,256',\n",
       "  '19',\n",
       "  '587',\n",
       "  '980',\n",
       "  '1',\n",
       "  '1,217',\n",
       "  '',\n",
       "  '637',\n",
       "  '8060'],\n",
       " ['Pennington',\n",
       "  '18,026',\n",
       "  '679',\n",
       "  '46,455',\n",
       "  '13,160',\n",
       "  '211',\n",
       "  '12,031',\n",
       "  '24',\n",
       "  '9965',\n",
       "  '90586'],\n",
       " ['Perkins', '259', '4', '1,565', '194', '4', '157', '', '178', '2183'],\n",
       " ['Potter', '181', '1', '1,346', '108', '6', '41', '1', '145', '1684'],\n",
       " ['Roberts', '2,382', '10', '2,240', '938', '19', '607', '', '495', '6196'],\n",
       " ['Sanborn', '358', '7', '988', '165', '', '126', '1', '62', '1645'],\n",
       " ['Spink', '1,006', '10', '2,549', '449', '3', '229', '', '207', '4246'],\n",
       " ['Stanley', '335', '7', '1,568', '189', '', '113', '', '203', '2212'],\n",
       " ['Sully', '162', '5', '822', '109', '', '36', '', '68', '1134'],\n",
       " ['Todd', '2,745', '7', '662', '581', '5', '557', '', '764', '4557'],\n",
       " ['Tripp', '568', '7', '2,438', '348', '7', '223', '', '381', '3591'],\n",
       " ['Turner', '1,132', '15', '3,643', '904', '', '364', '', '240', '6058'],\n",
       " ['Union',\n",
       "  '2,318',\n",
       "  '45',\n",
       "  '6,576',\n",
       "  '2,205',\n",
       "  '9',\n",
       "  '1,463',\n",
       "  '1',\n",
       "  '2203',\n",
       "  '12617'],\n",
       " ['Walworth', '580', '8', '2,473', '336', '11', '254', '', '472', '3662'],\n",
       " ['Yankton',\n",
       "  '3,497',\n",
       "  '62',\n",
       "  '7,460',\n",
       "  '2,559',\n",
       "  '14',\n",
       "  '1,316',\n",
       "  '',\n",
       "  '1597',\n",
       "  '14908'],\n",
       " ['Ziebach', '751', '5', '435', '173', '', '245', '', '96', '1609'],\n",
       " ['Total',\n",
       "  '145,984',\n",
       "  '2,976',\n",
       "  '316,496',\n",
       "  '90,381',\n",
       "  '960',\n",
       "  '67,301',\n",
       "  '77',\n",
       "  '63,901',\n",
       "  '624,175'],\n",
       " [\"Independent (IND) / No Party Affiliation (NPA) – South Dakota does not have an Independent Party. If a person completes a registration card to change that person's name,\\naddress, phone number, or email address, and leaves the field for party affiliation blank, the county auditor must list the person's party affiliation as the most recent party affiliation\\nregistered for that person. If a person completes a registration card to register to vote for the first time in the state and leaves the field for party affiliation blank, the county auditor must\\nregister that person's party affiliation as no party affiliation.\",\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['SDCL 12-1-3 (22) and SDCL 12-4-5',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['OTHER – This category is for voters who write any other political party on their voter registration form that is not currently a recognized political party in South Dakota.',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['NOTE - The South Dakota Democratic Party has opened their primary election up for all voters registered as independent/no party affiliation to vote in their primary. Voters registered\\nin the “other” category are not allowed to vote in the SD Democratic Party primary.',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['The Libertarian Party became a recognized party on June 17, 2016.',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None],\n",
       " ['The Constitution Party became a recognized party on March 31, 2016. By statutes SDCL 12-5-1.5 and 12-1-3, the Constitution Party lost party status on November 14, 2018. The No\\nLabels Party became a recognized party on August 4, 2023.',\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None]]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e6cd53a9-2f58-402a-9b1a-5e5c9b3046dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# report date is in the first line\n",
    "report_date = lines.pop(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9b5d3f66-568d-4d26-a0f4-d8600ae0af9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-11-05\n"
     ]
    }
   ],
   "source": [
    "report_date = report_date[0].split('-')[-1].strip()\n",
    "report_date = datetime.strptime(report_date, '%B %d, %Y').date().isoformat()\n",
    "\n",
    "print(report_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6fdeee0c-061a-4977-8194-8e88e8a8deee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wrote /Users/cjwinchester/sd-voter-registration-data/pdfs/2024-11-05.pdf\n"
     ]
    }
   ],
   "source": [
    "# save the PDF\n",
    "outpath = (Path('pdfs') / f'{report_date}.pdf').resolve()\n",
    "\n",
    "with open(outpath, 'wb') as outfile:\n",
    "    outfile.write(req.content)\n",
    "\n",
    "print(f'Wrote {outpath}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2e5d21b2-be5d-4d9e-998c-d01c4df2af11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# standardizing column names\n",
    "column_fixes = {\n",
    "    'oth': 'other',\n",
    "    'no party\\naffiliation': 'npa',\n",
    "    'no labels': 'no_labels',\n",
    "    'npa/ind': 'npa_ind'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2be7ac8a-fe25-403d-a49b-9bd91ff4639b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# headers are in the first line of the data table\n",
    "headers = lines.pop(0)\n",
    "headers = [column_fixes.get(x.lower(), x.lower()) for x in headers]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b8f74e2e-38b0-46d3-9de5-fa9d344a43d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['county',\n",
       " 'democratic',\n",
       " 'libertarian',\n",
       " 'republican',\n",
       " 'independent',\n",
       " 'other',\n",
       " 'npa',\n",
       " 'no_labels',\n",
       " 'inactive',\n",
       " 'total active']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "87b4e954-d9ec-49b8-b6e3-fc53040bce60",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data = []\n",
    "\n",
    "# loop over the remaining lines\n",
    "for line in lines:\n",
    "\n",
    "    first_cell = line[0].lower()\n",
    "\n",
    "    # skip lines if repeated headers\n",
    "    if 'statewide' in first_cell or 'county' in first_cell:\n",
    "        continue\n",
    "\n",
    "    # the 'Total' line in the table means we're done\n",
    "    if 'total' in first_cell:\n",
    "        break\n",
    "\n",
    "    # check that the number of values matches the number of headers\n",
    "    assert(len(line) == len(headers))\n",
    "\n",
    "    # zip it up into a dict\n",
    "    county_data = dict(zip(headers, line))\n",
    "\n",
    "    # delete the aggregate value totaling everything up\n",
    "    del county_data['total active']\n",
    "\n",
    "    # cast number strings to integers\n",
    "    for key in county_data:\n",
    "        if key == 'county' or not county_data[key]:\n",
    "            continue\n",
    "\n",
    "        county_data[key] = int(county_data[key].replace(',', ''))\n",
    "\n",
    "    # add the report date\n",
    "    county_data['date'] = report_date\n",
    "    data.append(county_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6571d1b4-ba88-4b3b-9fce-0f9cdd6721d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# doublecheck: 66 counties in SD\n",
    "assert(len(data) == 66)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "25fe47d7-bd47-46f5-b4bf-b2972e761421",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'county': 'Aurora',\n",
       " 'democratic': 518,\n",
       " 'libertarian': 2,\n",
       " 'republican': 982,\n",
       " 'independent': 230,\n",
       " 'other': 2,\n",
       " 'npa': 100,\n",
       " 'no_labels': '',\n",
       " 'inactive': 107,\n",
       " 'date': '2024-11-05'}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1cb251c3-ef9f-4898-9bc7-02b3230b52b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# shift 'date' to the beginning of headers list for the output CSV\n",
    "csv_headers = list(data[0].keys())\n",
    "csv_headers = csv_headers[-1:] + csv_headers[:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1ddeeb67-a213-45bc-bffe-10b25ebad375",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wrote file: /Users/cjwinchester/sd-voter-registration-data/data/2024-11-05.csv\n"
     ]
    }
   ],
   "source": [
    "# write to file\n",
    "filepath = (Path('data') / f'{report_date}.csv').resolve()\n",
    "\n",
    "with open(filepath, 'w') as outfile:\n",
    "    writer = csv.DictWriter(\n",
    "        outfile,\n",
    "        fieldnames=csv_headers\n",
    "    )\n",
    "\n",
    "    writer.writeheader()\n",
    "    writer.writerows(data)\n",
    "\n",
    "    print(f'Wrote file: {filepath}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d7b2a96f-de03-4cb9-b420-620aea8e3f46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Party list: americans_elect, constitution, democratic, inactive, independent, libertarian, no_labels, npa, npa_ind, npa_ind_oth, other, reform, republican\n",
      "--------------------\n",
      "Wrote sd-voter-registration-data.csv\n",
      "Wrote sd-voter-registration-data-simplified.csv\n",
      "Wrote README.md\n"
     ]
    }
   ],
   "source": [
    "%run build_files.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4bf0181-950a-46e7-8686-0ff0fc90064e",
   "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}