notebooks/01_fl_dat.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "85541ac9-1810-497d-9e06-02a059e17c0e",
   "metadata": {},
   "source": [
    "### Get All FL Voter Reg. Emails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8d597d0e-b686-4e3b-9646-a5088a3b9d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dcd61428-f0d6-4ca8-89fa-ec5bab8b7735",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../data/VoterDetail/BRA_20220621.txt\n",
      "../data/VoterDetail/BRE_20220621.txt\n",
      "../data/VoterDetail/BRO_20220621.txt\n",
      "../data/VoterDetail/CAL_20220621.txt\n",
      "../data/VoterDetail/CHA_20220621.txt\n",
      "../data/VoterDetail/CIT_20220621.txt\n",
      "../data/VoterDetail/CLA_20220621.txt\n",
      "../data/VoterDetail/CLL_20220621.txt\n",
      "../data/VoterDetail/CLM_20220621.txt\n",
      "../data/VoterDetail/DAD_20220621.txt\n",
      "../data/VoterDetail/DES_20220621.txt\n",
      "../data/VoterDetail/DIX_20220621.txt\n",
      "../data/VoterDetail/DUV_20220621.txt\n",
      "../data/VoterDetail/ESC_20220621.txt\n",
      "../data/VoterDetail/FLA_20220621.txt\n",
      "../data/VoterDetail/FRA_20220621.txt\n",
      "../data/VoterDetail/GAD_20220621.txt\n",
      "../data/VoterDetail/GIL_20220621.txt\n",
      "../data/VoterDetail/GLA_20220621.txt\n",
      "../data/VoterDetail/GUL_20220621.txt\n",
      "../data/VoterDetail/HAM_20220621.txt\n",
      "../data/VoterDetail/HAR_20220621.txt\n",
      "../data/VoterDetail/HEN_20220621.txt\n",
      "../data/VoterDetail/HER_20220621.txt\n",
      "../data/VoterDetail/HIG_20220621.txt\n",
      "../data/VoterDetail/HIL_20220621.txt\n",
      "../data/VoterDetail/HOL_20220621.txt\n",
      "../data/VoterDetail/IND_20220621.txt\n",
      "../data/VoterDetail/JAC_20220621.txt\n",
      "../data/VoterDetail/JEF_20220621.txt\n",
      "../data/VoterDetail/LAF_20220621.txt\n",
      "../data/VoterDetail/LAK_20220621.txt\n",
      "../data/VoterDetail/LEE_20220621.txt\n",
      "../data/VoterDetail/LEO_20220621.txt\n",
      "../data/VoterDetail/LEV_20220621.txt\n",
      "../data/VoterDetail/LIB_20220621.txt\n",
      "../data/VoterDetail/MAD_20220621.txt\n",
      "../data/VoterDetail/MAN_20220621.txt\n",
      "../data/VoterDetail/MON_20220621.txt\n",
      "../data/VoterDetail/MRN_20220621.txt\n",
      "../data/VoterDetail/MRT_20220621.txt\n",
      "../data/VoterDetail/NAS_20220621.txt\n",
      "../data/VoterDetail/OKA_20220621.txt\n"
     ]
    },
    {
     "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>17</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>37</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11/08/1984</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>09/16/1983</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>08/08/1965</td>\n",
       "      <td>hallronnie4120@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>3.0</td>\n",
       "      <td>07/07/1987</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12/19/1981</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>9.0</td>\n",
       "      <td>09/02/2001</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953001</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>3.0</td>\n",
       "      <td>07/29/1965</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>07/12/1994</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11/29/1970</td>\n",
       "      <td>benjamin.baldridge@live.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8953005 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          17   19   20          21                            37\n",
       "0        NaN    F  5.0  11/08/1984                              \n",
       "1        NaN    M  5.0  09/16/1983                              \n",
       "2        NaN    M  5.0  08/08/1965     hallronnie4120@yahoo.com \n",
       "3        NaN    F  3.0  07/07/1987                              \n",
       "4        NaN    M  5.0  12/19/1981                              \n",
       "...      ...  ...  ...         ...                           ...\n",
       "8953000  NaN    M  9.0  09/02/2001                              \n",
       "8953001  NaN    M  3.0  07/29/1965                              \n",
       "8953002  NaN    M  5.0  07/12/1994                              \n",
       "8953003  NaN    M  5.0  11/29/1970  benjamin.baldridge@live.com \n",
       "8953004  NaN  NaN  NaN         NaN                           NaN\n",
       "\n",
       "[8953005 rows x 5 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Specify the directory path containing the CSV files\n",
    "directory_path = '../data/VoterDetail/'\n",
    "\n",
    "# Get a list of all CSV file paths in the directory\n",
    "csv_files = glob.glob(os.path.join(directory_path, '*.txt'))\n",
    "\n",
    "dataframes = []\n",
    "\n",
    "for fn in sorted(csv_files):\n",
    "    print(fn)\n",
    "    a_df = pd.read_csv(fn, delimiter='\\t', header=None, usecols=[17, 19, 20, 21, 37])\n",
    "    dataframes.append(a_df)\n",
    "\n",
    "df = pd.concat(dataframes, ignore_index=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1445cd11-5e93-4b2c-9db8-4f0ed1180195",
   "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>zipcode</th>\n",
       "      <th>gender</th>\n",
       "      <th>race</th>\n",
       "      <th>birth_yr</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>08/08/1965</td>\n",
       "      <td>hallronnie4120@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1.0</td>\n",
       "      <td>03/24/1985</td>\n",
       "      <td>bkcombs911@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>01/03/1951</td>\n",
       "      <td>jiffy123@embarqmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>08/15/1965</td>\n",
       "      <td>LINZYROBIN65@GMAIL.COM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>04/29/1985</td>\n",
       "      <td>hotgirl102806@yahoo.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8952992</th>\n",
       "      <td>114325720</td>\n",
       "      <td>F</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12/11/1994</td>\n",
       "      <td>ACROSARIO.CONTACT@GMAIL.COM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8952995</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>02/06/1964</td>\n",
       "      <td>jlemsp@hotmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8952996</th>\n",
       "      <td>32531</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>04/17/1994</td>\n",
       "      <td>Johnanhank94@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8952998</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11/18/2002</td>\n",
       "      <td>TJSTEW@YMAIL.COM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8953003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11/29/1970</td>\n",
       "      <td>benjamin.baldridge@live.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1400924 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           zipcode gender  race    birth_yr                        email\n",
       "2              NaN      M   5.0  08/08/1965     hallronnie4120@yahoo.com\n",
       "5              NaN      M   1.0  03/24/1985         bkcombs911@gmail.com\n",
       "8              NaN      F   5.0  01/03/1951      jiffy123@embarqmail.com\n",
       "9              NaN      F   5.0  08/15/1965       LINZYROBIN65@GMAIL.COM\n",
       "13             NaN      F   5.0  04/29/1985      hotgirl102806@yahoo.com\n",
       "...            ...    ...   ...         ...                          ...\n",
       "8952992  114325720      F   4.0  12/11/1994  ACROSARIO.CONTACT@GMAIL.COM\n",
       "8952995        NaN      F   5.0  02/06/1964           jlemsp@hotmail.com\n",
       "8952996      32531      M   5.0  04/17/1994       Johnanhank94@gmail.com\n",
       "8952998        NaN      M   5.0  11/18/2002             TJSTEW@YMAIL.COM\n",
       "8953003        NaN      M   5.0  11/29/1970  benjamin.baldridge@live.com\n",
       "\n",
       "[1400924 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns = {17: 'zipcode', \n",
    "           19: 'gender', \n",
    "           20: 'race', \n",
    "           21: 'birth_yr', \n",
    "           37: 'email'}, inplace = True)\n",
    "df['email'] = df['email'].str.strip()\n",
    "df['email'].replace('', np.nan, inplace = True)\n",
    "df.dropna(subset = ['email'], inplace = True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7ea56b23-78c4-400e-ae56-6c46fb4d4753",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "race_lit\n",
       "NH White            784117\n",
       "Hispanic            329273\n",
       "NH Black            190008\n",
       "Asian                31351\n",
       "Other                27650\n",
       "Unknown              24429\n",
       "Multi-Racial         10221\n",
       "Native Americans      3875\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Recode values in column 'A'\n",
    "recode_map = {1: 'Native Americans', \n",
    "              2: 'Asian', \n",
    "              3: 'NH Black', \n",
    "              4: 'Hispanic', \n",
    "              5: 'NH White', \n",
    "              6: 'Other', \n",
    "              7: 'Multi-Racial', \n",
    "              9: 'Unknown'}\n",
    "df['race_lit'] = df['race'].replace(recode_map)\n",
    "df['race_lit'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5a93e3f4-2d1d-4332-951f-a324acdbfea7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"data/fl_emails.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9d866daf-cb89-49fe-989b-4670324438e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1400924, 6)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "511ca1de-1926-4abd-94e9-0b5b4404656d",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}