clean-up.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c47e5002-6713-47ba-967e-194b6650f254",
   "metadata": {},
   "source": [
    "# Data clean-up\n",
    "\n",
    "Here, we prepare our data before visualization. Based on the election districts map from [NYC Open Data](https://data.cityofnewyork.us/City-Government/Election-Districts/h2n3-98hq), what we need are the digits / number of the assembly districts + digits / number of the election districts to match."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0a964590-0b62-49d4-9ba3-c4b4f7133936",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0f5eee7c-3c25-4df7-8780-0b3b04eb3680",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "719"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"electoral_district_votes.csv\")\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a95aed4e-5aa8-4912-b5ab-ca73993df9f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "electoral_district              object\n",
       "reported                        object\n",
       "Harris-Walz_Democratic           int64\n",
       "Trump-Vance_Republican           int64\n",
       "Trump-Vance_Conservative         int64\n",
       "Harris-Walz_Working_Families     int64\n",
       "write_in                         int64\n",
       "assembly_district               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "565e42c3-1f6a-4980-98cd-22b05647dd55",
   "metadata": {},
   "source": [
    "### We need a column that shows both the Assembly District and Electoral District numbers \n",
    "\n",
    "This follows the format of our map and geocodes for viz, i.e., *AE*0*ED*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "49045e6e-c385-4c09-9b2d-b9a69550b570",
   "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>electoral_district</th>\n",
       "      <th>reported</th>\n",
       "      <th>Harris-Walz_Democratic</th>\n",
       "      <th>Trump-Vance_Republican</th>\n",
       "      <th>Trump-Vance_Conservative</th>\n",
       "      <th>Harris-Walz_Working_Families</th>\n",
       "      <th>write_in</th>\n",
       "      <th>assembly_district</th>\n",
       "      <th>ed_map_for_viz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ED 1</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>371</td>\n",
       "      <td>168</td>\n",
       "      <td>7</td>\n",
       "      <td>26</td>\n",
       "      <td>3</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ED 2</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>503</td>\n",
       "      <td>200</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ED 3</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>471</td>\n",
       "      <td>144</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ED 4</td>\n",
       "      <td>83.33%</td>\n",
       "      <td>94</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ED 5</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>470</td>\n",
       "      <td>178</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>ED 55</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>448</td>\n",
       "      <td>166</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>ED 56</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>ED 57</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>ED 58</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>ED 59</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87059</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>719 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    electoral_district reported  Harris-Walz_Democratic  \\\n",
       "0                 ED 1   99.00%                     371   \n",
       "1                 ED 2   99.00%                     503   \n",
       "2                 ED 3   99.00%                     471   \n",
       "3                 ED 4   83.33%                      94   \n",
       "4                 ED 5   99.00%                     470   \n",
       "..                 ...      ...                     ...   \n",
       "714              ED 55   99.00%                     448   \n",
       "715              ED 56   99.00%                       4   \n",
       "716              ED 57   99.00%                       0   \n",
       "717              ED 58   99.00%                       0   \n",
       "718              ED 59   99.00%                       0   \n",
       "\n",
       "     Trump-Vance_Republican  Trump-Vance_Conservative  \\\n",
       "0                       168                         7   \n",
       "1                       200                         7   \n",
       "2                       144                        18   \n",
       "3                        27                         2   \n",
       "4                       178                        11   \n",
       "..                      ...                       ...   \n",
       "714                     166                        13   \n",
       "715                       3                         0   \n",
       "716                       0                         0   \n",
       "717                       0                         0   \n",
       "718                       0                         0   \n",
       "\n",
       "     Harris-Walz_Working_Families  write_in assembly_district ed_map_for_viz  \n",
       "0                              26         3           AD   77          77001  \n",
       "1                              17         6           AD   77          77002  \n",
       "2                              12         2           AD   77          77003  \n",
       "3                               6         2           AD   77          77004  \n",
       "4                              11         2           AD   77          77005  \n",
       "..                            ...       ...               ...            ...  \n",
       "714                            18         8           AD   87          87055  \n",
       "715                             1         0           AD   87          87056  \n",
       "716                             0         0           AD   87          87057  \n",
       "717                             0         0           AD   87          87058  \n",
       "718                             0         0           AD   87          87059  \n",
       "\n",
       "[719 rows x 9 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"ad_number\"] = df[\"assembly_district\"].str.extract(r\"(\\d+)\").astype(int)\n",
    "df[\"ed_number\"] = df[\"electoral_district\"].str.extract(r\"(\\d+)\").astype(int)\n",
    "\n",
    "# Create new column with the desired format\n",
    "df[\"ed_map_for_viz\"] = df[\"ad_number\"].astype(str) + df[\"ed_number\"].astype(str).str.zfill(3)\n",
    "df.drop(columns=[\"ad_number\", \"ed_number\"], inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a7b217e-0baf-4a46-86bf-4150ed60ef54",
   "metadata": {},
   "source": [
    "### We need a column that adds the votes per candidate\n",
    "\n",
    "Currently, we're seeing Harris-Walz votes from Democratic and Working Families voters, and Trump-Vance votes from Republican and Conservative voters. What we want are the total votes and total votes per candidate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "16d38a8c-633a-45b8-9ce6-e938f6e077e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# getting total votes\n",
    "\n",
    "df[\"total_votes\"] = df[\"Trump-Vance_Republican\"] + df[\"Trump-Vance_Conservative\"] + \\\n",
    "df[\"Harris-Walz_Democratic\"] + df[\"Harris-Walz_Working_Families\"] +\\\n",
    "df[\"write_in\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d581b53f-a83b-4f76-a165-f5f66f9149ca",
   "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>electoral_district</th>\n",
       "      <th>reported</th>\n",
       "      <th>Harris-Walz_Democratic</th>\n",
       "      <th>Trump-Vance_Republican</th>\n",
       "      <th>Trump-Vance_Conservative</th>\n",
       "      <th>Harris-Walz_Working_Families</th>\n",
       "      <th>write_in</th>\n",
       "      <th>assembly_district</th>\n",
       "      <th>ed_map_for_viz</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>trump_votes</th>\n",
       "      <th>harris_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ED 1</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>371</td>\n",
       "      <td>168</td>\n",
       "      <td>7</td>\n",
       "      <td>26</td>\n",
       "      <td>3</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77001</td>\n",
       "      <td>575</td>\n",
       "      <td>175</td>\n",
       "      <td>397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ED 2</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>503</td>\n",
       "      <td>200</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77002</td>\n",
       "      <td>733</td>\n",
       "      <td>207</td>\n",
       "      <td>520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ED 3</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>471</td>\n",
       "      <td>144</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77003</td>\n",
       "      <td>647</td>\n",
       "      <td>162</td>\n",
       "      <td>483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ED 4</td>\n",
       "      <td>83.33%</td>\n",
       "      <td>94</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77004</td>\n",
       "      <td>131</td>\n",
       "      <td>29</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ED 5</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>470</td>\n",
       "      <td>178</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77005</td>\n",
       "      <td>672</td>\n",
       "      <td>189</td>\n",
       "      <td>481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>ED 55</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>448</td>\n",
       "      <td>166</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87055</td>\n",
       "      <td>653</td>\n",
       "      <td>179</td>\n",
       "      <td>466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>ED 56</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87056</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>ED 57</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87057</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>ED 58</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87058</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>ED 59</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87059</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>719 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    electoral_district reported  Harris-Walz_Democratic  \\\n",
       "0                 ED 1   99.00%                     371   \n",
       "1                 ED 2   99.00%                     503   \n",
       "2                 ED 3   99.00%                     471   \n",
       "3                 ED 4   83.33%                      94   \n",
       "4                 ED 5   99.00%                     470   \n",
       "..                 ...      ...                     ...   \n",
       "714              ED 55   99.00%                     448   \n",
       "715              ED 56   99.00%                       4   \n",
       "716              ED 57   99.00%                       0   \n",
       "717              ED 58   99.00%                       0   \n",
       "718              ED 59   99.00%                       0   \n",
       "\n",
       "     Trump-Vance_Republican  Trump-Vance_Conservative  \\\n",
       "0                       168                         7   \n",
       "1                       200                         7   \n",
       "2                       144                        18   \n",
       "3                        27                         2   \n",
       "4                       178                        11   \n",
       "..                      ...                       ...   \n",
       "714                     166                        13   \n",
       "715                       3                         0   \n",
       "716                       0                         0   \n",
       "717                       0                         0   \n",
       "718                       0                         0   \n",
       "\n",
       "     Harris-Walz_Working_Families  write_in assembly_district ed_map_for_viz  \\\n",
       "0                              26         3           AD   77          77001   \n",
       "1                              17         6           AD   77          77002   \n",
       "2                              12         2           AD   77          77003   \n",
       "3                               6         2           AD   77          77004   \n",
       "4                              11         2           AD   77          77005   \n",
       "..                            ...       ...               ...            ...   \n",
       "714                            18         8           AD   87          87055   \n",
       "715                             1         0           AD   87          87056   \n",
       "716                             0         0           AD   87          87057   \n",
       "717                             0         0           AD   87          87058   \n",
       "718                             0         0           AD   87          87059   \n",
       "\n",
       "     total_votes  trump_votes  harris_votes  \n",
       "0            575          175           397  \n",
       "1            733          207           520  \n",
       "2            647          162           483  \n",
       "3            131           29           100  \n",
       "4            672          189           481  \n",
       "..           ...          ...           ...  \n",
       "714          653          179           466  \n",
       "715            8            3             5  \n",
       "716            0            0             0  \n",
       "717            0            0             0  \n",
       "718            0            0             0  \n",
       "\n",
       "[719 rows x 12 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting total votes per candidate\n",
    "\n",
    "df[\"trump_votes\"] = df[\"Trump-Vance_Republican\"] + df[\"Trump-Vance_Conservative\"]\n",
    "df[\"harris_votes\"] = df[\"Harris-Walz_Democratic\"] + df[\"Harris-Walz_Working_Families\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02ef001d-c0ac-4e0f-a319-cb7a346a47f4",
   "metadata": {},
   "source": [
    "### Vote differential per election district\n",
    "\n",
    "We will get the difference in Harris and Trump votes. In this case, negative values will mean more votes for Harris (Democrat), and positive values will indicate more votes for Trump (Republican). This will bring clarity to what values we will be using for our choropleth map. \n",
    "\n",
    "What we will see in our viz, then, will be shades of red and blue that shows margins of victory. **Lands don't vote; people do.**\n",
    "\n",
    "Update: Because we will be using this to compare votes against previous election years, we will convert vote counts to percentages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3fc567fe-4d5e-47a9-ad4c-81eeb1805ec1",
   "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>electoral_district</th>\n",
       "      <th>reported</th>\n",
       "      <th>Harris-Walz_Democratic</th>\n",
       "      <th>Trump-Vance_Republican</th>\n",
       "      <th>Trump-Vance_Conservative</th>\n",
       "      <th>Harris-Walz_Working_Families</th>\n",
       "      <th>write_in</th>\n",
       "      <th>assembly_district</th>\n",
       "      <th>ed_map_for_viz</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>trump_votes</th>\n",
       "      <th>harris_votes</th>\n",
       "      <th>vote_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ED 1</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>371</td>\n",
       "      <td>168</td>\n",
       "      <td>7</td>\n",
       "      <td>26</td>\n",
       "      <td>3</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77001</td>\n",
       "      <td>575</td>\n",
       "      <td>175</td>\n",
       "      <td>397</td>\n",
       "      <td>-222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ED 2</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>503</td>\n",
       "      <td>200</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77002</td>\n",
       "      <td>733</td>\n",
       "      <td>207</td>\n",
       "      <td>520</td>\n",
       "      <td>-313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ED 3</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>471</td>\n",
       "      <td>144</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77003</td>\n",
       "      <td>647</td>\n",
       "      <td>162</td>\n",
       "      <td>483</td>\n",
       "      <td>-321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ED 4</td>\n",
       "      <td>83.33%</td>\n",
       "      <td>94</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77004</td>\n",
       "      <td>131</td>\n",
       "      <td>29</td>\n",
       "      <td>100</td>\n",
       "      <td>-71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ED 5</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>470</td>\n",
       "      <td>178</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77005</td>\n",
       "      <td>672</td>\n",
       "      <td>189</td>\n",
       "      <td>481</td>\n",
       "      <td>-292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>ED 55</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>448</td>\n",
       "      <td>166</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87055</td>\n",
       "      <td>653</td>\n",
       "      <td>179</td>\n",
       "      <td>466</td>\n",
       "      <td>-287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>ED 56</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87056</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>ED 57</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87057</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>ED 58</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87058</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>ED 59</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87059</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>719 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    electoral_district reported  Harris-Walz_Democratic  \\\n",
       "0                 ED 1   99.00%                     371   \n",
       "1                 ED 2   99.00%                     503   \n",
       "2                 ED 3   99.00%                     471   \n",
       "3                 ED 4   83.33%                      94   \n",
       "4                 ED 5   99.00%                     470   \n",
       "..                 ...      ...                     ...   \n",
       "714              ED 55   99.00%                     448   \n",
       "715              ED 56   99.00%                       4   \n",
       "716              ED 57   99.00%                       0   \n",
       "717              ED 58   99.00%                       0   \n",
       "718              ED 59   99.00%                       0   \n",
       "\n",
       "     Trump-Vance_Republican  Trump-Vance_Conservative  \\\n",
       "0                       168                         7   \n",
       "1                       200                         7   \n",
       "2                       144                        18   \n",
       "3                        27                         2   \n",
       "4                       178                        11   \n",
       "..                      ...                       ...   \n",
       "714                     166                        13   \n",
       "715                       3                         0   \n",
       "716                       0                         0   \n",
       "717                       0                         0   \n",
       "718                       0                         0   \n",
       "\n",
       "     Harris-Walz_Working_Families  write_in assembly_district ed_map_for_viz  \\\n",
       "0                              26         3           AD   77          77001   \n",
       "1                              17         6           AD   77          77002   \n",
       "2                              12         2           AD   77          77003   \n",
       "3                               6         2           AD   77          77004   \n",
       "4                              11         2           AD   77          77005   \n",
       "..                            ...       ...               ...            ...   \n",
       "714                            18         8           AD   87          87055   \n",
       "715                             1         0           AD   87          87056   \n",
       "716                             0         0           AD   87          87057   \n",
       "717                             0         0           AD   87          87058   \n",
       "718                             0         0           AD   87          87059   \n",
       "\n",
       "     total_votes  trump_votes  harris_votes  vote_counts  \n",
       "0            575          175           397         -222  \n",
       "1            733          207           520         -313  \n",
       "2            647          162           483         -321  \n",
       "3            131           29           100          -71  \n",
       "4            672          189           481         -292  \n",
       "..           ...          ...           ...          ...  \n",
       "714          653          179           466         -287  \n",
       "715            8            3             5           -2  \n",
       "716            0            0             0            0  \n",
       "717            0            0             0            0  \n",
       "718            0            0             0            0  \n",
       "\n",
       "[719 rows x 13 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vote differential: actual value of vote counts\n",
    "\n",
    "df[\"vote_counts\"] = df[\"trump_votes\"] - df[\"harris_votes\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e380a114-b743-4c12-ab59-cd4d8c456e2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# vote differential: percentages\n",
    "\n",
    "df[\"trump_votes_pct\"] = (df[\"trump_votes\"] / df[\"total_votes\"]) * 100\n",
    "df[\"harris_votes_pct\"] = (df[\"harris_votes\"] / df[\"total_votes\"]) * 100\n",
    "\n",
    "df[\"vote_counts_pct\"] = df[\"trump_votes_pct\"] - df[\"harris_votes_pct\"]\n",
    "df.fillna({\"vote_counts_pct\": 0}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8feb1a63-1e3b-4ef9-bde1-ce600b03510f",
   "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>electoral_district</th>\n",
       "      <th>reported</th>\n",
       "      <th>Harris-Walz_Democratic</th>\n",
       "      <th>Trump-Vance_Republican</th>\n",
       "      <th>Trump-Vance_Conservative</th>\n",
       "      <th>Harris-Walz_Working_Families</th>\n",
       "      <th>write_in</th>\n",
       "      <th>assembly_district</th>\n",
       "      <th>ed_map_for_viz</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>trump_votes</th>\n",
       "      <th>harris_votes</th>\n",
       "      <th>vote_counts</th>\n",
       "      <th>trump_votes_pct</th>\n",
       "      <th>harris_votes_pct</th>\n",
       "      <th>vote_counts_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ED 1</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>371</td>\n",
       "      <td>168</td>\n",
       "      <td>7</td>\n",
       "      <td>26</td>\n",
       "      <td>3</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77001</td>\n",
       "      <td>575</td>\n",
       "      <td>175</td>\n",
       "      <td>397</td>\n",
       "      <td>-222</td>\n",
       "      <td>30.434783</td>\n",
       "      <td>69.043478</td>\n",
       "      <td>-38.608696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ED 2</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>503</td>\n",
       "      <td>200</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77002</td>\n",
       "      <td>733</td>\n",
       "      <td>207</td>\n",
       "      <td>520</td>\n",
       "      <td>-313</td>\n",
       "      <td>28.240109</td>\n",
       "      <td>70.941337</td>\n",
       "      <td>-42.701228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ED 3</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>471</td>\n",
       "      <td>144</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77003</td>\n",
       "      <td>647</td>\n",
       "      <td>162</td>\n",
       "      <td>483</td>\n",
       "      <td>-321</td>\n",
       "      <td>25.038640</td>\n",
       "      <td>74.652241</td>\n",
       "      <td>-49.613601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ED 4</td>\n",
       "      <td>83.33%</td>\n",
       "      <td>94</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77004</td>\n",
       "      <td>131</td>\n",
       "      <td>29</td>\n",
       "      <td>100</td>\n",
       "      <td>-71</td>\n",
       "      <td>22.137405</td>\n",
       "      <td>76.335878</td>\n",
       "      <td>-54.198473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ED 5</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>470</td>\n",
       "      <td>178</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>AD   77</td>\n",
       "      <td>77005</td>\n",
       "      <td>672</td>\n",
       "      <td>189</td>\n",
       "      <td>481</td>\n",
       "      <td>-292</td>\n",
       "      <td>28.125000</td>\n",
       "      <td>71.577381</td>\n",
       "      <td>-43.452381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>ED 55</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>448</td>\n",
       "      <td>166</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87055</td>\n",
       "      <td>653</td>\n",
       "      <td>179</td>\n",
       "      <td>466</td>\n",
       "      <td>-287</td>\n",
       "      <td>27.411945</td>\n",
       "      <td>71.362940</td>\n",
       "      <td>-43.950995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>ED 56</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87056</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>37.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>-25.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>ED 57</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87057</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>ED 58</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87058</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>ED 59</td>\n",
       "      <td>99.00%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AD   87</td>\n",
       "      <td>87059</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>719 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    electoral_district reported  Harris-Walz_Democratic  \\\n",
       "0                 ED 1   99.00%                     371   \n",
       "1                 ED 2   99.00%                     503   \n",
       "2                 ED 3   99.00%                     471   \n",
       "3                 ED 4   83.33%                      94   \n",
       "4                 ED 5   99.00%                     470   \n",
       "..                 ...      ...                     ...   \n",
       "714              ED 55   99.00%                     448   \n",
       "715              ED 56   99.00%                       4   \n",
       "716              ED 57   99.00%                       0   \n",
       "717              ED 58   99.00%                       0   \n",
       "718              ED 59   99.00%                       0   \n",
       "\n",
       "     Trump-Vance_Republican  Trump-Vance_Conservative  \\\n",
       "0                       168                         7   \n",
       "1                       200                         7   \n",
       "2                       144                        18   \n",
       "3                        27                         2   \n",
       "4                       178                        11   \n",
       "..                      ...                       ...   \n",
       "714                     166                        13   \n",
       "715                       3                         0   \n",
       "716                       0                         0   \n",
       "717                       0                         0   \n",
       "718                       0                         0   \n",
       "\n",
       "     Harris-Walz_Working_Families  write_in assembly_district ed_map_for_viz  \\\n",
       "0                              26         3           AD   77          77001   \n",
       "1                              17         6           AD   77          77002   \n",
       "2                              12         2           AD   77          77003   \n",
       "3                               6         2           AD   77          77004   \n",
       "4                              11         2           AD   77          77005   \n",
       "..                            ...       ...               ...            ...   \n",
       "714                            18         8           AD   87          87055   \n",
       "715                             1         0           AD   87          87056   \n",
       "716                             0         0           AD   87          87057   \n",
       "717                             0         0           AD   87          87058   \n",
       "718                             0         0           AD   87          87059   \n",
       "\n",
       "     total_votes  trump_votes  harris_votes  vote_counts  trump_votes_pct  \\\n",
       "0            575          175           397         -222        30.434783   \n",
       "1            733          207           520         -313        28.240109   \n",
       "2            647          162           483         -321        25.038640   \n",
       "3            131           29           100          -71        22.137405   \n",
       "4            672          189           481         -292        28.125000   \n",
       "..           ...          ...           ...          ...              ...   \n",
       "714          653          179           466         -287        27.411945   \n",
       "715            8            3             5           -2        37.500000   \n",
       "716            0            0             0            0              NaN   \n",
       "717            0            0             0            0              NaN   \n",
       "718            0            0             0            0              NaN   \n",
       "\n",
       "     harris_votes_pct  vote_counts_pct  \n",
       "0           69.043478       -38.608696  \n",
       "1           70.941337       -42.701228  \n",
       "2           74.652241       -49.613601  \n",
       "3           76.335878       -54.198473  \n",
       "4           71.577381       -43.452381  \n",
       "..                ...              ...  \n",
       "714         71.362940       -43.950995  \n",
       "715         62.500000       -25.000000  \n",
       "716               NaN         0.000000  \n",
       "717               NaN         0.000000  \n",
       "718               NaN         0.000000  \n",
       "\n",
       "[719 rows x 16 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e2a83aa3-7740-4970-84e1-095b1ef0a004",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"2024_votes_full.csv\", encoding=\"UTF-8\", index=False)"
   ]
  }
 ],
 "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}