notebooks/9.1_stanford_dime.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "31afb26b-443f-4036-9e3d-09e89be657cb",
   "metadata": {},
   "source": [
    "# Contrib data from Standford DIME"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27cc2b66-40af-4072-86e7-b35bd99a9cb2",
   "metadata": {},
   "source": [
    "## Downloading the contribuation list from Stanford DIME\n",
    "https://data.stanford.edu/dime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dd481f16-bfca-477e-983a-87d395b4ebed",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Downloading...\n",
      "From (uriginal): https://drive.google.com/uc?id=0B5SFXStyeVhrb1JnaVYzT2p6WWM\n",
      "From (redirected): https://drive.google.com/uc?id=0B5SFXStyeVhrb1JnaVYzT2p6WWM&confirm=t&uuid=ea39d9a7-d0c7-4ad9-96f4-19fa6fce79fb\n",
      "To: /home/distill1994/ethnicolor/data/dime/contribDB_2014.csv\n",
      "100%|██████████| 10.7G/10.7G [15:47<00:00, 11.3MB/s]  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'contribDB_2014.csv'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gdown\n",
    "\n",
    "# downloading the contribDB_2014.csv of size 10GB\n",
    "# https://drive.google.com/file/d/0B5SFXStyeVhrb1JnaVYzT2p6WWM/view?usp=drive_link&resourcekey=0-WEXQFkUpA46AEUev_ucR7w\n",
    "\n",
    "url = 'https://drive.google.com/uc?id=0B5SFXStyeVhrb1JnaVYzT2p6WWM'\n",
    "output = 'contribDB_2014.csv'\n",
    "gdown.download(url, output, quiet=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ea82babe-a898-453d-bec3-e14136f853ca",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"cycle\",\"transaction.id\",\"transaction.type\",\"amount\",\"date\",\"bonica.cid\",\"contributor.name\",\"contributor.lname\",\"contributor.fname\",\"contributor.mname\",\"contributor.suffix\",\"contributor.title\",\"contributor.ffname\",\"contributor.type\",\"contributor.gender\",\"contributor.address\",\"contributor.city\",\"contributor.state\",\"contributor.zipcode\",\"contributor.occupation\",\"contributor.employer\",\"is.corp\",\"recipient.name\",\"bonica.rid\",\"recipient.party\",\"recipient.type\",\"recipient.state\",\"seat\",\"election.type\",\"latitude\",\"longitude\",\"gis.confidence\",\"contributor.district.90s\",\"contributor.district.00s\",\"contributor.district.10s\",\"censustract\",\"efec.memo\",\"efec.memo2\",\"efec.transaction.id.orig\",\"bk.ref.transaction.id\",\"efec.org.orig\",\"efec.comid.orig\",\"efec.form.type\",\"excluded.from.scaling\",\"contributor.cfscore\",\"candidate.cfscore\"\n",
      "2014,\"CA:1424021_INC634_2014_1_\",\"15\",1789.15,\"2013-11-19\",3836518376,\"state of california\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"sacramento\",\"ca\",95814,\"\",\"\",\"\",\"yes on prop 93, a coalition of business, labor, teachers, lawmakers, public safety and assemblymembers de leon, nunez, b\",\"comm9995154\",\"NA\",\"COMM\",\"CA\",\"state:ballot\",\"\",38.580173,-121.506897,0.688,\"CA05\",\"CA05\",\"CA06\",\"06067000700\",\"\",,\"INC634\",\"\",\"\",\"\",\"I\",1,-0.36,-0.87\n",
      "2014,\"CA:1436949_INC342_2014_0_\",\"15\",408.26,\"2009-01-07\",32780584235,\"golden state business bank\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"upland\",\"ca\",91786,\"\",\"\",\"corp\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.105282,-117.662033,1,\"CA41\",\"CA26\",\"CA31\",\"06071000824\",\"\",,\"INC342\",\"\",\"\",\"\",\"I\",1,0.05,0.03\n",
      "2014,\"CA:1436949_INC343_2014_0_\",\"15\",842.06,\"2009-01-12\",32780584235,\"golden state business bank\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"upland\",\"ca\",91786,\"\",\"\",\"corp\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.105282,-117.662033,1,\"CA41\",\"CA26\",\"CA31\",\"06071000824\",\"\",,\"INC343\",\"\",\"\",\"\",\"I\",1,0.05,0.03\n",
      "2014,\"CA:1436949_INC352_2014_0_\",\"15\",230.53,\"2009-01-21\",3839554438,\"arrowhead credit union\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"san bernardino\",\"ca\",92402,\"\",\"\",\"\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.06741,-117.283325,0.805,\"CA42\",\"CA41\",\"CA31\",\"06071012400\",\"\",,\"INC352\",\"\",\"\",\"\",\"I\",1,-0.05,0.03\n",
      "2014,\"CA:1436949_INC356_2014_0_\",\"15\",409.44,\"2009-02-07\",32780584235,\"golden state business bank\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"upland\",\"ca\",91786,\"\",\"\",\"corp\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.105282,-117.662033,1,\"CA41\",\"CA26\",\"CA31\",\"06071000824\",\"\",,\"INC356\",\"\",\"\",\"\",\"I\",1,0.05,0.03\n",
      "2014,\"CA:1436949_INC358_2014_0_\",\"15\",141.75,\"2009-01-31\",3839554438,\"arrowhead credit union\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"san bernardino\",\"ca\",92402,\"\",\"\",\"\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.06741,-117.283325,0.805,\"CA42\",\"CA41\",\"CA31\",\"06071012400\",\"\",,\"INC358\",\"\",\"\",\"\",\"I\",1,-0.05,0.03\n",
      "2014,\"CA:1436949_INC360_2014_0_\",\"15\",2.17,\"2009-01-31\",3839554438,\"arrowhead credit union\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"san bernardino\",\"ca\",92402,\"\",\"\",\"\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.06741,-117.283325,0.805,\"CA42\",\"CA41\",\"CA31\",\"06071012400\",\"\",,\"INC360\",\"\",\"\",\"\",\"I\",1,-0.05,0.03\n",
      "2014,\"CA:1436949_INC362_2014_0_\",\"15\",208.7,\"2009-02-26\",3839554438,\"arrowhead credit union\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"san bernardino\",\"ca\",92402,\"\",\"\",\"\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.06741,-117.283325,0.805,\"CA42\",\"CA41\",\"CA31\",\"06071012400\",\"\",,\"INC362\",\"\",\"\",\"\",\"I\",1,-0.05,0.03\n",
      "2014,\"CA:1436949_INC373_2014_0_\",\"15\",370.83,\"2009-03-07\",32780584235,\"golden state business bank\",\"\",\"\",\"\",\"\",\"\",\"\",\"C\",\"\",\"\",\"upland\",\"ca\",91786,\"\",\"\",\"corp\",\"brulte, jim\",\"cand27195\",\"200\",\"CAND\",\"CA\",\"state:office\",\"\",34.105282,-117.662033,1,\"CA41\",\"CA26\",\"CA31\",\"06071000824\",\"\",,\"INC373\",\"\",\"\",\"\",\"I\",1,0.05,0.03\n"
     ]
    }
   ],
   "source": [
    "# peaking first 10 rows\n",
    "!head -10 contribDB_2014.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3a9537dc-be1d-4765-a53d-0aa12c889c40",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cbaf1918-5ddd-4294-bdba-ea2cc19f9a5d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# take a small chunk, to peak data\n",
    "!head -1000 data/dime/contribDB_2014.csv > data/dime/contribDB_2014_min.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "703b8bc4-4fe0-4157-b9f5-6a76042bf22d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/dime/contribDB_2014_min.csv', engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7d49376a-92dd-4151-95e3-0bd1b5b490e0",
   "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>cycle</th>\n",
       "      <th>transaction.id</th>\n",
       "      <th>transaction.type</th>\n",
       "      <th>amount</th>\n",
       "      <th>date</th>\n",
       "      <th>bonica.cid</th>\n",
       "      <th>contributor.name</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "      <th>contributor.mname</th>\n",
       "      <th>...</th>\n",
       "      <th>efec.memo</th>\n",
       "      <th>efec.memo2</th>\n",
       "      <th>efec.transaction.id.orig</th>\n",
       "      <th>bk.ref.transaction.id</th>\n",
       "      <th>efec.org.orig</th>\n",
       "      <th>efec.comid.orig</th>\n",
       "      <th>efec.form.type</th>\n",
       "      <th>excluded.from.scaling</th>\n",
       "      <th>contributor.cfscore</th>\n",
       "      <th>candidate.cfscore</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>CA:1424021_INC634_2014_1_</td>\n",
       "      <td>15</td>\n",
       "      <td>1789.15</td>\n",
       "      <td>2013-11-19</td>\n",
       "      <td>3836518376</td>\n",
       "      <td>state of california</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.36</td>\n",
       "      <td>-0.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>CA:1436949_INC342_2014_0_</td>\n",
       "      <td>15</td>\n",
       "      <td>408.26</td>\n",
       "      <td>2009-01-07</td>\n",
       "      <td>32780584235</td>\n",
       "      <td>golden state business bank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC342</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014</td>\n",
       "      <td>CA:1436949_INC343_2014_0_</td>\n",
       "      <td>15</td>\n",
       "      <td>842.06</td>\n",
       "      <td>2009-01-12</td>\n",
       "      <td>32780584235</td>\n",
       "      <td>golden state business bank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC343</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>CA:1436949_INC352_2014_0_</td>\n",
       "      <td>15</td>\n",
       "      <td>230.53</td>\n",
       "      <td>2009-01-21</td>\n",
       "      <td>3839554438</td>\n",
       "      <td>arrowhead credit union</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC352</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014</td>\n",
       "      <td>CA:1436949_INC356_2014_0_</td>\n",
       "      <td>15</td>\n",
       "      <td>409.44</td>\n",
       "      <td>2009-02-07</td>\n",
       "      <td>32780584235</td>\n",
       "      <td>golden state business bank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC356</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   cycle             transaction.id transaction.type   amount        date   \n",
       "0   2014  CA:1424021_INC634_2014_1_               15  1789.15  2013-11-19  \\\n",
       "1   2014  CA:1436949_INC342_2014_0_               15   408.26  2009-01-07   \n",
       "2   2014  CA:1436949_INC343_2014_0_               15   842.06  2009-01-12   \n",
       "3   2014  CA:1436949_INC352_2014_0_               15   230.53  2009-01-21   \n",
       "4   2014  CA:1436949_INC356_2014_0_               15   409.44  2009-02-07   \n",
       "\n",
       "    bonica.cid            contributor.name contributor.lname   \n",
       "0   3836518376         state of california               NaN  \\\n",
       "1  32780584235  golden state business bank               NaN   \n",
       "2  32780584235  golden state business bank               NaN   \n",
       "3   3839554438      arrowhead credit union               NaN   \n",
       "4  32780584235  golden state business bank               NaN   \n",
       "\n",
       "  contributor.fname contributor.mname  ... efec.memo  efec.memo2   \n",
       "0               NaN               NaN  ...       NaN         NaN  \\\n",
       "1               NaN               NaN  ...       NaN         NaN   \n",
       "2               NaN               NaN  ...       NaN         NaN   \n",
       "3               NaN               NaN  ...       NaN         NaN   \n",
       "4               NaN               NaN  ...       NaN         NaN   \n",
       "\n",
       "  efec.transaction.id.orig bk.ref.transaction.id efec.org.orig   \n",
       "0                   INC634                   NaN           NaN  \\\n",
       "1                   INC342                   NaN           NaN   \n",
       "2                   INC343                   NaN           NaN   \n",
       "3                   INC352                   NaN           NaN   \n",
       "4                   INC356                   NaN           NaN   \n",
       "\n",
       "   efec.comid.orig efec.form.type excluded.from.scaling  contributor.cfscore   \n",
       "0              NaN              I                   1.0                -0.36  \\\n",
       "1              NaN              I                   1.0                 0.05   \n",
       "2              NaN              I                   1.0                 0.05   \n",
       "3              NaN              I                   1.0                -0.05   \n",
       "4              NaN              I                   1.0                 0.05   \n",
       "\n",
       "  candidate.cfscore  \n",
       "0             -0.87  \n",
       "1              0.03  \n",
       "2              0.03  \n",
       "3              0.03  \n",
       "4              0.03  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cb0937ba-cae6-4a19-bd53-f8fc16cd99f9",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cycle', 'transaction.id', 'transaction.type', 'amount', 'date',\n",
       "       'bonica.cid', 'contributor.name', 'contributor.lname',\n",
       "       'contributor.fname', 'contributor.mname', 'contributor.suffix',\n",
       "       'contributor.title', 'contributor.ffname', 'contributor.type',\n",
       "       'contributor.gender', 'contributor.address', 'contributor.city',\n",
       "       'contributor.state', 'contributor.zipcode', 'contributor.occupation',\n",
       "       'contributor.employer', 'is.corp', 'recipient.name', 'bonica.rid',\n",
       "       'recipient.party', 'recipient.type', 'recipient.state', 'seat',\n",
       "       'election.type', 'latitude', 'longitude', 'gis.confidence',\n",
       "       'contributor.district.90s', 'contributor.district.00s',\n",
       "       'contributor.district.10s', 'censustract', 'efec.memo', 'efec.memo2',\n",
       "       'efec.transaction.id.orig', 'bk.ref.transaction.id', 'efec.org.orig',\n",
       "       'efec.comid.orig', 'efec.form.type', 'excluded.from.scaling',\n",
       "       'contributor.cfscore', 'candidate.cfscore'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6f92cdb-9971-4fb4-92d0-11bffe7ee3e3",
   "metadata": {},
   "source": [
    "## Dropping rest of the columns to reduce dataset size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "790d564e-e042-47c2-a0b1-f94aedb93ca2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10G\tdata/dime/contribDB_2014.csv\n"
     ]
    }
   ],
   "source": [
    "!du -sh data/dime/contribDB_2014.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9b22e862-8d35-4017-9a89-ce824e8b30fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get only first name and last names\n",
    "!awk -F, '{print $4\",\"$8\",\"$9}' data/dime/contribDB_2014.csv > data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c0366653-c909-4b5f-8e6a-de6f6ae44b9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "475M\tdata/dime/contribDB_2014_names.csv\n"
     ]
    }
   ],
   "source": [
    "!du -sh data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "80693cff-29ac-4f7e-b27b-08fe448b6069",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23161961 data/dime/contribDB_2014_names.csv\n"
     ]
    }
   ],
   "source": [
    "!wc -l data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "3fc64c50-1299-4923-9dc3-3d8eeb7493ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"amount\",\"contributor.lname\",\"contributor.fname\"\n",
      "1789.15,\"\",\"\"\n",
      "408.26,\"\",\"\"\n",
      "842.06,\"\",\"\"\n",
      "230.53,\"\",\"\"\n",
      "409.44,\"\",\"\"\n",
      "141.75,\"\",\"\"\n",
      "2.17,\"\",\"\"\n",
      "208.7,\"\",\"\"\n",
      "370.83,\"\",\"\"\n"
     ]
    }
   ],
   "source": [
    "!head -10 data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "3b4f756f-4973-46d0-97ea-e94349038d9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "!sed -i 's/\"//g' data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "be4d5fd7-2dea-435b-9ff5-5f9157ca182e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amount,contributor.lname,contributor.fname\n",
      "1789.15,,\n",
      "408.26,,\n",
      "842.06,,\n",
      "230.53,,\n",
      "409.44,,\n",
      "141.75,,\n",
      "2.17,,\n",
      "208.7,,\n",
      "370.83,,\n"
     ]
    }
   ],
   "source": [
    "!head -10 data/dime/contribDB_2014_names.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "9bc4ae6c-0b0c-4660-901a-f79588ac9771",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/dime/contribDB_2014_names.csv', engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "591563a5-eab2-45e6-9a7d-69372dd7439b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23161960, 3)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25e71b50-01b4-4aed-83f0-a2affdebf740",
   "metadata": {},
   "source": [
    "## Dropping Nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "6e54317d-0006-432a-99be-fdda3e0e841c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "2c139345-0f2d-424c-a8b5-3e5b26a7e404",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21342434, 3)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d56aeb20-9de0-438a-a899-f0df2c3b9a79",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>500</td>\n",
       "      <td>county &amp; municipal employees</td>\n",
       "      <td>local 2620 pac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1000</td>\n",
       "      <td>phelps &amp; phillips</td>\n",
       "      <td>llp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100</td>\n",
       "      <td>janet gray</td>\n",
       "      <td>hayes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>thomas m</td>\n",
       "      <td>dailey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>200</td>\n",
       "      <td>margaret</td>\n",
       "      <td>giberson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount              contributor.lname contributor.fname\n",
       "42    500   county & municipal employees    local 2620 pac\n",
       "47   1000              phelps & phillips               llp\n",
       "67    100                     janet gray             hayes\n",
       "74    100                       thomas m            dailey\n",
       "77    200                       margaret          giberson"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aaa9c40-481f-478c-9be4-e1851780125d",
   "metadata": {},
   "source": [
    "## Dropping orgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "0a1a86bc-0681-4068-9f2b-8cc0ee55bb12",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[df['contributor.lname'].str.contains(\"&|employee|associates|\\d\",regex=True)].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09310749-a6b1-492b-b41a-23e44d710ca3",
   "metadata": {},
   "source": [
    "## Make title case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "018e5bb2-8d87-4c24-a915-1c6324817e5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['contributor.lname'] = df['contributor.lname'].str.title()\n",
    "df['contributor.fname'] = df['contributor.fname'].str.title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "eb8fe8cf-443f-474f-bc22-c514e1da725e",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100</td>\n",
       "      <td>Janet Gray</td>\n",
       "      <td>Hayes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>Thomas M</td>\n",
       "      <td>Dailey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>200</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>200</td>\n",
       "      <td>Gregory F</td>\n",
       "      <td>Hooper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>300</td>\n",
       "      <td>William Spencer</td>\n",
       "      <td>Lewis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount contributor.lname contributor.fname\n",
       "67    100        Janet Gray             Hayes\n",
       "74    100          Thomas M            Dailey\n",
       "77    200          Margaret          Giberson\n",
       "78    200         Gregory F            Hooper\n",
       "79    300   William Spencer             Lewis"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "0560dc28-7331-43cf-8268-9a184d6395b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21325572, 3)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bce137c-00d7-4d66-99dd-c41fe29d6b71",
   "metadata": {},
   "source": [
    "## Remove special characters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "8f348385-ae6e-46bf-88e7-4f8dbfa2de99",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['contributor.lname'] = df['contributor.lname'].str.replace(\"[^a-zA-Z' -]\", '', regex=True)\n",
    "df['contributor.fname'] = df['contributor.fname'].str.replace(\"[^a-zA-Z' -]\", '', regex=True)\n",
    "\n",
    "df['contributor.lname'] = df['contributor.lname'].str.strip()\n",
    "df['contributor.fname'] = df['contributor.fname'].str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dd16973-4476-44ea-ae6a-3898a4fe5674",
   "metadata": {},
   "source": [
    "## Droping names less than 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "ee9e29e9-f8dc-453a-8ec9-143a9e03ddb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[df['contributor.lname'].str.len() < 2].index)\n",
    "df = df.drop(df[df['contributor.fname'].str.len() < 2].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "c042b345-1f30-4d92-b31e-9bb1cae6d3fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21283306, 3)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "f9da8799-a588-4571-97cf-ae52b933e9e1",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>361683</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Richard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364327</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Robert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367698</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430992</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431330</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22988014</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22988015</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23025781</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23025782</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Ralph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23061393</th>\n",
       "      <td>100</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Richard</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>294 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         amount contributor.lname contributor.fname\n",
       "361683      100             Smith           Richard\n",
       "364327      100             Smith            Robert\n",
       "367698      100             Smith             Ralph\n",
       "430992      100             Smith             Ralph\n",
       "431330      100             Smith             Ralph\n",
       "...         ...               ...               ...\n",
       "22988014    100             Smith             Ralph\n",
       "22988015    100             Smith             Ralph\n",
       "23025781    100             Smith             Ralph\n",
       "23025782    100             Smith             Ralph\n",
       "23061393    100             Smith           Richard\n",
       "\n",
       "[294 rows x 3 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['contributor.lname'] == 'Smith']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "18ab1060-78e3-4688-90b5-031fa8f39fea",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>794</th>\n",
       "      <td>150</td>\n",
       "      <td>Jean</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>250</td>\n",
       "      <td>Scott</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1113</th>\n",
       "      <td>100</td>\n",
       "      <td>Paul</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1323</th>\n",
       "      <td>6500</td>\n",
       "      <td>Gerrie</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1324</th>\n",
       "      <td>2500</td>\n",
       "      <td>Gerrie</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23161531</th>\n",
       "      <td>270</td>\n",
       "      <td>Nicole</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23161532</th>\n",
       "      <td>270</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23161533</th>\n",
       "      <td>275</td>\n",
       "      <td>Brenda</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23161534</th>\n",
       "      <td>270</td>\n",
       "      <td>Brenda</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23161884</th>\n",
       "      <td>250</td>\n",
       "      <td>Regina L</td>\n",
       "      <td>Smith</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>152473 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         amount contributor.lname contributor.fname\n",
       "794         150              Jean             Smith\n",
       "1105        250             Scott             Smith\n",
       "1113        100              Paul             Smith\n",
       "1323       6500            Gerrie             Smith\n",
       "1324       2500            Gerrie             Smith\n",
       "...         ...               ...               ...\n",
       "23161531    270            Nicole             Smith\n",
       "23161532    270              Mary             Smith\n",
       "23161533    275            Brenda             Smith\n",
       "23161534    270            Brenda             Smith\n",
       "23161884    250          Regina L             Smith\n",
       "\n",
       "[152473 rows x 3 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['contributor.fname'] == 'Smith']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50fffd90-429f-47f4-a45a-8665232d027b",
   "metadata": {},
   "source": [
    "## Majority of lnames and fnames are swapped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "033ecc2b-5484-4c15-a1ca-82a86113ae9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['name_first'] = df['contributor.lname']\n",
    "df['name_last'] = df['contributor.fname']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81d647f6-b605-4570-b3c5-8df075f35ee7",
   "metadata": {},
   "source": [
    "## Removing middle names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "ed24a8df-fc45-449d-b37e-5e17d3321168",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['name_first'] = df.name_first.apply(lambda x: x.split()[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8cbd966-61ea-4598-b81d-0505540ea15d",
   "metadata": {},
   "source": [
    "## find first names that has lastname ans swap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "4cea37c8-80e5-4727-be63-b951bfa38e6f",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100</td>\n",
       "      <td>Janet Gray</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>Janet</td>\n",
       "      <td>Hayes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>Thomas M</td>\n",
       "      <td>Dailey</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>Dailey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>200</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>200</td>\n",
       "      <td>Gregory F</td>\n",
       "      <td>Hooper</td>\n",
       "      <td>Gregory</td>\n",
       "      <td>Hooper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>300</td>\n",
       "      <td>William Spencer</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>William</td>\n",
       "      <td>Lewis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount contributor.lname contributor.fname name_first name_last\n",
       "67    100        Janet Gray             Hayes      Janet     Hayes\n",
       "74    100          Thomas M            Dailey     Thomas    Dailey\n",
       "77    200          Margaret          Giberson   Margaret  Giberson\n",
       "78    200         Gregory F            Hooper    Gregory    Hooper\n",
       "79    300   William Spencer             Lewis    William     Lewis"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ed5eb5d-5979-481c-812c-b080c061e0df",
   "metadata": {},
   "source": [
    "## Drop if first and last names are matched."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "140d530a-ba97-45d3-a71e-983d2bf1342a",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28494</th>\n",
       "      <td>120</td>\n",
       "      <td>Joseph M</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>Joseph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34490</th>\n",
       "      <td>120</td>\n",
       "      <td>Maung</td>\n",
       "      <td>Maung</td>\n",
       "      <td>Maung</td>\n",
       "      <td>Maung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35023</th>\n",
       "      <td>120</td>\n",
       "      <td>Hareesh</td>\n",
       "      <td>Hareesh</td>\n",
       "      <td>Hareesh</td>\n",
       "      <td>Hareesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56121</th>\n",
       "      <td>1000</td>\n",
       "      <td>Inc Including Its Affiliated Entity The Vons C...</td>\n",
       "      <td>Inc</td>\n",
       "      <td>Inc</td>\n",
       "      <td>Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93277</th>\n",
       "      <td>120</td>\n",
       "      <td>Diko Androus</td>\n",
       "      <td>Diko</td>\n",
       "      <td>Diko</td>\n",
       "      <td>Diko</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>23028852</th>\n",
       "      <td>10</td>\n",
       "      <td>Aungkhin B</td>\n",
       "      <td>Aungkhin</td>\n",
       "      <td>Aungkhin</td>\n",
       "      <td>Aungkhin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23044344</th>\n",
       "      <td>500</td>\n",
       "      <td>Shannon</td>\n",
       "      <td>Shannon</td>\n",
       "      <td>Shannon</td>\n",
       "      <td>Shannon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23059050</th>\n",
       "      <td>100</td>\n",
       "      <td>Wei</td>\n",
       "      <td>Wei</td>\n",
       "      <td>Wei</td>\n",
       "      <td>Wei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23084390</th>\n",
       "      <td>25</td>\n",
       "      <td>Tamas</td>\n",
       "      <td>Tamas</td>\n",
       "      <td>Tamas</td>\n",
       "      <td>Tamas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23091961</th>\n",
       "      <td>25</td>\n",
       "      <td>Tamas I</td>\n",
       "      <td>Tamas</td>\n",
       "      <td>Tamas</td>\n",
       "      <td>Tamas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7238 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         amount                                  contributor.lname   \n",
       "28494       120                                           Joseph M  \\\n",
       "34490       120                                              Maung   \n",
       "35023       120                                            Hareesh   \n",
       "56121      1000  Inc Including Its Affiliated Entity The Vons C...   \n",
       "93277       120                                       Diko Androus   \n",
       "...         ...                                                ...   \n",
       "23028852     10                                         Aungkhin B   \n",
       "23044344    500                                            Shannon   \n",
       "23059050    100                                                Wei   \n",
       "23084390     25                                              Tamas   \n",
       "23091961     25                                            Tamas I   \n",
       "\n",
       "         contributor.fname name_first name_last  \n",
       "28494               Joseph     Joseph    Joseph  \n",
       "34490                Maung      Maung     Maung  \n",
       "35023              Hareesh    Hareesh   Hareesh  \n",
       "56121                  Inc        Inc       Inc  \n",
       "93277                 Diko       Diko      Diko  \n",
       "...                    ...        ...       ...  \n",
       "23028852          Aungkhin   Aungkhin  Aungkhin  \n",
       "23044344           Shannon    Shannon   Shannon  \n",
       "23059050               Wei        Wei       Wei  \n",
       "23084390             Tamas      Tamas     Tamas  \n",
       "23091961             Tamas      Tamas     Tamas  \n",
       "\n",
       "[7238 rows x 5 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['name_first'] == df['name_last']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "23c3b0e9-68d9-4f91-8c11-2b3b79288508",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[df['name_first'] == df['name_last']].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "f51f5453-e206-42ac-83c9-b6a86cb0fc6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[df['name_first'].str.len() < 2].index)\n",
    "df = df.drop(df[df['name_last'].str.len() < 2].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "52414511-329b-409b-899a-5b771cd725d0",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100</td>\n",
       "      <td>Janet Gray</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>Janet</td>\n",
       "      <td>Hayes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>Thomas M</td>\n",
       "      <td>Dailey</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>Dailey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>200</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>200</td>\n",
       "      <td>Gregory F</td>\n",
       "      <td>Hooper</td>\n",
       "      <td>Gregory</td>\n",
       "      <td>Hooper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>300</td>\n",
       "      <td>William Spencer</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>William</td>\n",
       "      <td>Lewis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount contributor.lname contributor.fname name_first name_last\n",
       "67    100        Janet Gray             Hayes      Janet     Hayes\n",
       "74    100          Thomas M            Dailey     Thomas    Dailey\n",
       "77    200          Margaret          Giberson   Margaret  Giberson\n",
       "78    200         Gregory F            Hooper    Gregory    Hooper\n",
       "79    300   William Spencer             Lewis    William     Lewis"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "9ad70cb4-509f-4786-be9e-f3e09f02d9a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_names = df['name_last'].value_counts().loc[lambda x: x>10000].reset_index()['name_last'].values\n",
    "first_names = df['name_first'].value_counts().loc[lambda x: x>10000].reset_index()['name_first'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "caeac853-cb8b-45fd-9fe9-a5faec1bc58a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['full_name'] = df['name_last'] + ' ' + df['name_first']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "364f49dd-53a5-4847-99b0-019dcf3a6f9b",
   "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>amount</th>\n",
       "      <th>contributor.lname</th>\n",
       "      <th>contributor.fname</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "      <th>full_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100</td>\n",
       "      <td>Janet Gray</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>Janet</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>Hayes Janet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>100</td>\n",
       "      <td>Thomas M</td>\n",
       "      <td>Dailey</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>Dailey</td>\n",
       "      <td>Dailey Thomas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>200</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Giberson</td>\n",
       "      <td>Giberson Margaret</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>200</td>\n",
       "      <td>Gregory F</td>\n",
       "      <td>Hooper</td>\n",
       "      <td>Gregory</td>\n",
       "      <td>Hooper</td>\n",
       "      <td>Hooper Gregory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>300</td>\n",
       "      <td>William Spencer</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>William</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Lewis William</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   amount contributor.lname contributor.fname name_first name_last   \n",
       "67    100        Janet Gray             Hayes      Janet     Hayes  \\\n",
       "74    100          Thomas M            Dailey     Thomas    Dailey   \n",
       "77    200          Margaret          Giberson   Margaret  Giberson   \n",
       "78    200         Gregory F            Hooper    Gregory    Hooper   \n",
       "79    300   William Spencer             Lewis    William     Lewis   \n",
       "\n",
       "            full_name  \n",
       "67        Hayes Janet  \n",
       "74      Dailey Thomas  \n",
       "77  Giberson Margaret  \n",
       "78     Hooper Gregory  \n",
       "79      Lewis William  "
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "cd168c2f-074d-47a3-9be0-3cdb00fa2eff",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['amount'] = df['amount'].apply(lambda x: pd.to_numeric(x, errors='coerce'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "53b5f1c6-34c0-4cde-813e-fef050918899",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21086687, 6)"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "bade68af-c43a-41b2-bf73-9029be21ccb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "2e3311ed-a354-45b4-bc74-45781631b7bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21086682, 6)"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "27a08411-facd-4a15-bd30-cf012b367974",
   "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>full_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>35.0</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A Antipolo Aurora</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6295576</th>\n",
       "      <td>Zyzanski Greg</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6295577</th>\n",
       "      <td>Zyzanski Gregory</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6295578</th>\n",
       "      <td>Zyzda Marilyn</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6295579</th>\n",
       "      <td>Zzzzzzzzzzzjohnson Kenneth</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6295580</th>\n",
       "      <td>Zzzzzzzzzzzjohnson Kenneth</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6295581 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name  amount    sum\n",
       "0                   A Anderson Nate     0.5    1.0\n",
       "1                   A Anderson Nate     3.5    3.5\n",
       "2                   A Anderson Nate     5.0   20.0\n",
       "3                   A Anderson Nate    35.0  280.0\n",
       "4                 A Antipolo Aurora     5.0    5.0\n",
       "...                             ...     ...    ...\n",
       "6295576               Zyzanski Greg    20.0   20.0\n",
       "6295577            Zyzanski Gregory    10.0   20.0\n",
       "6295578               Zyzda Marilyn     5.0    5.0\n",
       "6295579  Zzzzzzzzzzzjohnson Kenneth     1.5    1.5\n",
       "6295580  Zzzzzzzzzzzjohnson Kenneth    15.0   15.0\n",
       "\n",
       "[6295581 rows x 3 columns]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['full_name','amount'], as_index=False)['amount'].agg(['sum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "6cc09bdf-9fbf-4fa9-84c1-40f8424009d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf = df.groupby(['full_name'], as_index=False)['amount'].agg(['sum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "fdb4e36c-c3b9-4fa7-b864-99d0b7ab7802",
   "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>full_name</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>304.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Antipolo Aurora</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A Baird Thomas</td>\n",
       "      <td>600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A Balaban Stephen</td>\n",
       "      <td>1024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A Bean Jennifer</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           full_name     sum\n",
       "0    A Anderson Nate   304.5\n",
       "1  A Antipolo Aurora     5.0\n",
       "2     A Baird Thomas   600.0\n",
       "3  A Balaban Stephen  1024.0\n",
       "4    A Bean Jennifer    55.0"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "65f0cc55-d7fe-44b1-a60e-354f216dd62b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cdf = df.groupby(['full_name'], as_index=False)['amount'].agg(['count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "4317ba81-5c90-4e7f-a9f8-0cdc597016ea",
   "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>full_name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Anderson Nate</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Antipolo Aurora</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A Baird Thomas</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A Balaban Stephen</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A Bean Jennifer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           full_name  count\n",
       "0    A Anderson Nate     15\n",
       "1  A Antipolo Aurora      1\n",
       "2     A Baird Thomas      2\n",
       "3  A Balaban Stephen      1\n",
       "4    A Bean Jennifer      2"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "e504d446-b376-4d45-b42a-04a834e8d18b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2016797, 2)\n",
      "(2016797, 2)\n"
     ]
    }
   ],
   "source": [
    "print(cdf.shape)\n",
    "print(sdf.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "e2b663ae-386b-4ceb-8a50-5d7048cbb599",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = \"A Anderson Nate\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "c7c9a3ce-7c82-4e86-b250-c18bc4143970",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.merge(sdf, cdf, on=['full_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "9de18f40-1166-485e-a7d6-50de0a1a1b99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2016797, 3)"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "436930ca-ddb7-4ee6-b49a-69e06fdae3f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['full_name'] = final_df['full_name'].apply(lambda x : ' '.join([i for i in x.split() if len(i) > 2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "52351f3b-ac86-4a72-b91f-cbf87fb5db7a",
   "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>full_name</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anderson Nate</td>\n",
       "      <td>304.5</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Antipolo Aurora</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Baird Thomas</td>\n",
       "      <td>600.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Balaban Stephen</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bean Jennifer</td>\n",
       "      <td>55.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         full_name     sum  count\n",
       "0    Anderson Nate   304.5     15\n",
       "1  Antipolo Aurora     5.0      1\n",
       "2     Baird Thomas   600.0      2\n",
       "3  Balaban Stephen  1024.0      1\n",
       "4    Bean Jennifer    55.0      2"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "0b2a9046-89df-41fa-a17e-7927f4fbbfeb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2016797, 3)"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "e14a6274-f0c7-4758-beff-a999a036e53d",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "c270ec82-2b8e-4d64-8be8-c271bf0b339c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2016556, 3)"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "cff498a9-ea15-47b1-bd81-0f66f4706ddf",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df.drop(final_df[final_df['full_name'].str.contains(\"Professional|Advocate|Employee|Assoc|Sponsored\",regex=True)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "37fc0e54-074f-4d31-b14d-1ceedd62020a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2016464, 3)"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "76713b26-acf1-47fa-afcd-2b91162a7e1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(\"data/dime/contribDB_2014_final.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4adac5f2-9ef3-48d5-b12d-41e68e05ecd5",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}