analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "68b794cf-746f-45e1-b71e-6c7918859427",
   "metadata": {},
   "source": [
    "### seiu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ed1c6a43-5768-45bf-b312-c71c70e89833",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "02341e88-1ad4-437e-b32b-03f58cd7a47e",
   "metadata": {},
   "outputs": [],
   "source": [
    "SEIU_COMMITTEES = pd.DataFrame([\n",
    "    ['2005226', 'LOCAL 0032BJ PA AMERICAN DREAM FUND'],\n",
    "    ['20150138', 'SEIU HEALTHCARE PA COPE'],\n",
    "    ['8000703', 'PA SEIU COPE (SOC SVCS)'],\n",
    "    ['9900041', 'PSSU LOCAL 668 SEIU COPE FUND'],\n",
    "    ['20190384', 'SEIU CAREGIVERS ACTION']],\n",
    "    columns=['Committee Number', 'Committee Name'])\n",
    "\n",
    "EXPENSES_2023 = [\n",
    "    # LOCAL 0032BJ PA AMERICAN DREAM FUND -- from own report\n",
    "    ['387640', '2005226', 2023, '', 2, 'CMT ELECTED HELEN DEE SPADE', '', '', '', '', 15237, 20230322, 500, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'FRIENDS OF COREY OCONNOR', '', '', '', '', 15222, 20230322, 1000, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'FRIENDS OF SARA INNAMORATO', '', '', '', '', 15201, 20230322, 40000, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'BARB WARWICK FOR CITY COUNCIL', '', '', '', '', 15207, 20230411, 1000, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'FRIENDS OF BOB CHARLAND', '', '', '', '', 15203, 20230411, 1000, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'FRIENDS OF DEB GROSS', '', '', '', '', 15201, 20230411, 1000, ''],\n",
    "    ['387640', '2005226', 2023, '', 2, 'FRIENDS OF KHARI MOSLEY', '', '', '', '', 15206, 20230411, 1000, ''],\n",
    "\n",
    "    # SEIU HEALTHCARE PA COPE -- from own report\n",
    "    ['386887', '20150138', 2023, '', 1, 'SHERI GRAUBERT', '', '', '', '', 15218, 20230307, 300, 'IN KIND INNAMORATO'],\n",
    "    ['386887', '20150138', 2023, '', 1, 'CHRIS IVEY', '', '', '', '', 15218, 20230317, 4000, 'IN KIND NNAMORATO'],\n",
    "    ['388044', '20150138', 2023, '', 2, 'FRIENDS OF SARA INNAMORATO', '', '', '', '', 15201, 20230403, 95000, ''],\n",
    "    ['388044', '20150138', 2023, '', 2, 'FRIENDS OF DEB GROSS', '', '', '', '', 15206, 20230501, 1000, ''],\n",
    "    ['388044', '20150138', 2023, '', 2, 'FRIENDS OF BOBBY WILSON', '', '', '', '', 15212, 20230501, 1000, ''],\n",
    "    ['388044', '20150138', 2023, '', 2, 'BARB WARWICK FOR CITY COUNCIL', '', '', '', '', 15207, 20230501, 1000, ''],\n",
    "    ['388044', '20150138', 2023, '', 2, 'FRIENDS OF KHARI MOSLEY', '', '', '', '', 15206, 20230501, 1000, ''],\n",
    "    ['388044', '20150138', 2023, '', 2, 'FRIENDS OF BOB CHARLAND', '', '', '', '', 15203, 20230501, 1000, ''],\n",
    "\n",
    "    # PA SEIU COPE (SOC SVCS) -- from own report\n",
    "    ['386705', '8000703', 2023, '', 1, 'FRIENDS OF ARVIND VENKAT', '', '', '', '', 15090, 20230308, 250, ''],\n",
    "    ['386705', '8000703', 2023, '', 1, 'LATASHA MAYES FOR STATE REPRESENTATIVE', '', '', '', '', 15206, 20230308, 250, ''],\n",
    "    ['386705', '8000703', 2023, '', 1, 'SEIU', '', '', '', '', 15206, 20230125, 241.76, 'IN KIND LINDSEY WILLIAMS'],\n",
    "\n",
    "    # PA SEIU COPE (SOC SVCS) -- from Innamo report\n",
    "    ['0', '8000703', 2023, '', 2, 'ETC', '', '', '', '', 15201, 20230501, 50000, 'IN KIND INNAMORATO'],\n",
    "\n",
    "    # PSSU LOCAL 668 SEIU COPE FUND -- from Innamo report\n",
    "    ['0', '9900041', 2023, '', 2, 'FRIENDS OF SARA INNAMORATO', '', '', '', '', 15201, 20230428, 100000, ''],\n",
    "\n",
    "    # 'SEIU CAREGIVERS ACTION' -- from own report\n",
    "    ['388037', '20190384', 2023, '', 2, 'WFP', '', '', '', '', 15201, 20230425, 75000, 'IN KIND INNAMORATO']    \n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "861800ad-d19c-4cc0-9852-c1d0c070597a",
   "metadata": {},
   "source": [
    "### Import all expenses, link to SEIU committees"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6bd0bb3e-e552-40fe-b473-6f358d1d95fa",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/m6/nmg96kbd65n3tz43pzrdnnw00000gn/T/ipykernel_51941/1594577243.py:15: DtypeWarning: Columns (12) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  pd.read_csv('input/expense_2013.csv', names=names201221, encoding='unicode_escape'),\n"
     ]
    }
   ],
   "source": [
    "names201221 = [\n",
    "    'filer_id', 'election_year', 'cycle',\n",
    "    'name', 'address1', 'address2', 'city', 'state', 'zipcode',\n",
    "    'date', 'amount', 'description', 'blank'\n",
    "]\n",
    "\n",
    "names2022 = [\n",
    "    'report_id', 'filer_id', 'election_year', 'filer_date', 'cycle',\n",
    "    'name', 'address1', 'address2', 'city', 'state', 'zipcode',\n",
    "    'date', 'amount', 'description'\n",
    "]\n",
    "\n",
    "expenses = pd.concat([\n",
    "    pd.read_csv('input/expense_2012.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2013.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2014.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2015.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2016.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2017.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2018.csv', names=names201221, encoding='unicode_escape', skiprows=[1333, 22823, 62725, 108098]),\n",
    "    pd.read_csv('input/expense_2019.csv', names=names201221, encoding='unicode_escape'),\n",
    "    pd.read_csv('input/expense_2020.csv', names=names201221, encoding='unicode_escape', skiprows=[60172]),\n",
    "    pd.read_csv('input/expense_2021.csv', names=names201221, encoding='unicode_escape', skiprows=[14403, 66485, 66486, 66487, 66488, 66489, 66490, 66491, 87279]),\n",
    "    pd.read_csv('input/expense_2022.csv', usecols=names2022, names=names2022),\n",
    "    pd.DataFrame(EXPENSES_2023, columns=names2022)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2bbafd30-a920-4e57-a9f6-a65717882923",
   "metadata": {},
   "outputs": [],
   "source": [
    "expenses = expenses[['filer_id', 'name', 'zipcode', 'date', 'amount', 'description']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b232106c-c786-4638-a859-d8514daf6703",
   "metadata": {},
   "outputs": [],
   "source": [
    "expenses.loc[expenses.description.isna(), 'description'] = ''\n",
    "expenses.loc[expenses.name.isna(), 'name'] = ''\n",
    "\n",
    "expenses['description'] = expenses.description.str.upper()\n",
    "expenses['name'] = expenses.name.str.upper()\n",
    "expenses['zipcode'] = expenses.zipcode.astype(str)\n",
    "\n",
    "### Remove malformated vendor\n",
    "expenses = expenses.drop(expenses[expenses['name'] == 'NCH SOFTWARE'].index)\n",
    "\n",
    "expenses['date'] = pd.to_datetime(expenses.date, format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ffb22f96-a71d-45d8-8e8c-e3454f9f8d23",
   "metadata": {},
   "outputs": [],
   "source": [
    "expenses = pd.merge(expenses, SEIU_COMMITTEES, left_on='filer_id', right_on='Committee Number')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7c9714c-cdf5-4240-b2ef-8c2c4e95967f",
   "metadata": {},
   "source": [
    "### Peduto-Ravenstahl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e29f46d2-df7a-493a-bc11-d09cb47fac57",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>25000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA SEIU COPE (SOC SVCS)</th>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      amount\n",
       "Committee Name                              \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND  25000.0\n",
       "PA SEIU COPE (SOC SVCS)               5000.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('PEDUTO')) &\n",
    "    (expenses.date.dt.year.isin([2012, 2013]))\n",
    "], index='Committee Name', values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21ea649d-bd28-4a05-b1ae-15e7f610c97d",
   "metadata": {},
   "source": [
    "### Wilson-Harris"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6d72f9af-18fb-4831-8c86-bdcc585b237e",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>50385.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       amount\n",
       "Committee Name                               \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND  50385.18"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.description.str.contains('BOBBY WILSON') |\n",
    "    expenses.description.str.contains('DARLENE HARRIS')) &\n",
    "    (expenses.date.dt.year == 2019)\n",
    "], index=['Committee Name'], values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9e3be933-ec57-42f3-839a-1bc9881b0f83",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIU HEALTHCARE PA COPE</th>\n",
       "      <td>2500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      amount\n",
       "Committee Name                              \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND  10000.0\n",
       "SEIU HEALTHCARE PA COPE               2500.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('BOBBY WILSON')) &\n",
    "    (expenses.date.dt.year == 2019)\n",
    "], index='Committee Name', values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22cc904f-7e48-4412-bf6e-3207e32a239b",
   "metadata": {},
   "source": [
    "### Kinkead-Ravenstahl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "54c11b83-498e-4a46-9008-fa2c177efcdf",
   "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>filer_id</th>\n",
       "      <th>name</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>date</th>\n",
       "      <th>amount</th>\n",
       "      <th>description</th>\n",
       "      <th>Committee Number</th>\n",
       "      <th>Committee Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [filer_id, name, zipcode, date, amount, description, Committee Number, Committee Name]\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expenses[\n",
    "    (expenses.description.str.contains('KINKEAD') |\n",
    "    expenses.description.str.contains('RAVENSTAHL')) &\n",
    "    (expenses.date.dt.year.isin([2019, 2020]))\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6b8dc1a5-5714-4d5c-97a2-cb3f99e09455",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PA SEIU COPE (SOC SVCS)</th>\n",
       "      <td>52000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          amount\n",
       "Committee Name                  \n",
       "PA SEIU COPE (SOC SVCS)  52000.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('KINKEAD')) &\n",
    "    (expenses.date.dt.year.isin([2019, 2020]))\n",
    "], index='Committee Name', values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37e5b3fd-095c-4de7-a359-9621db809fb0",
   "metadata": {},
   "source": [
    "### Gainey-Peduto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "dfaea359-0aaa-466d-a1d7-491fb1b38929",
   "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></th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SEIU CAREGIVERS ACTION</th>\n",
       "      <th>ALLEGHENY COUNTY JUSTICE FOR ALL</th>\n",
       "      <td>204000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIU HEALTHCARE PA COPE</th>\n",
       "      <th>ALLEGHENY COUNTY JUSTICE FOR ALL</th>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <th>GOOD JOBS PITTSBURGH</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                        amount\n",
       "Committee Name                      name                                      \n",
       "SEIU CAREGIVERS ACTION              ALLEGHENY COUNTY JUSTICE FOR ALL  204000.0\n",
       "SEIU HEALTHCARE PA COPE             ALLEGHENY COUNTY JUSTICE FOR ALL  150000.0\n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND GOOD JOBS PITTSBURGH              100000.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.isin(['ALLEGHENY COUNTY JUSTICE FOR ALL', 'GOOD JOBS PITTSBURGH'])) &\n",
    "    (expenses.date.dt.year.isin([2021]))\n",
    "], index=['Committee Name', 'name'], values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)\n",
    "\n",
    "# RED HORSE STRATEGIES ??"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "036b8184-7699-4fdd-b7ed-5c4290338902",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>4000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     amount\n",
       "Committee Name                             \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND  4000.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('PEDUTO')) &\n",
    "    (expenses.date.dt.year.isin([2020, 2021]))\n",
    "], index='Committee Name', values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "98b973a4-cda0-4087-9410-596ac0f56713",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIU HEALTHCARE PA COPE</th>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PSSU LOCAL 668 SEIU COPE FUND</th>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     amount\n",
       "Committee Name                             \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND  5000.0\n",
       "SEIU HEALTHCARE PA COPE              5000.0\n",
       "PSSU LOCAL 668 SEIU COPE FUND        1000.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('GAINEY') |\n",
    "     expenses.description.str.contains('GAINEY')) &\n",
    "    (expenses.date.dt.year.isin([2020, 2021]))\n",
    "], index='Committee Name', values='amount', aggfunc='sum').sort_values(by='amount', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7112c7b-24b0-494d-b8b9-22ed16c0d5ce",
   "metadata": {},
   "source": [
    "### Innamorato"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c7c5f0e9-59b8-458d-a7dd-4a52612fce18",
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Committee Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LOCAL 0032BJ PA AMERICAN DREAM FUND</th>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA SEIU COPE (SOC SVCS)</th>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PSSU LOCAL 668 SEIU COPE FUND</th>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIU CAREGIVERS ACTION</th>\n",
       "      <td>75000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIU HEALTHCARE PA COPE</th>\n",
       "      <td>95300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>360300.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       amount\n",
       "Committee Name                               \n",
       "LOCAL 0032BJ PA AMERICAN DREAM FUND   40000.0\n",
       "PA SEIU COPE (SOC SVCS)               50000.0\n",
       "PSSU LOCAL 668 SEIU COPE FUND        100000.0\n",
       "SEIU CAREGIVERS ACTION                75000.0\n",
       "SEIU HEALTHCARE PA COPE               95300.0\n",
       "Total                                360300.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(expenses[\n",
    "    (expenses.name.str.contains('INNAMORATO') |\n",
    "     expenses.description.str.contains('INNAMORATO')) &\n",
    "    (expenses.date.dt.year == 2023)\n",
    "], index='Committee Name', values='amount', aggfunc='sum', margins=True, margins_name='Total')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ded39311-0832-4311-8ddc-704f351f8734",
   "metadata": {},
   "source": [
    "### SEIU total spend over time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0ce46933-9a34-4f71-a044-783d773ac583",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'election_year'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[16], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m totalspend \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpivot_table\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m      2\u001b[0m \u001b[43m    \u001b[49m\u001b[43mexpenses\u001b[49m\u001b[43m[\u001b[49m\n\u001b[1;32m      3\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mzipcode\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[43m[\u001b[49m\u001b[43m:\u001b[49m\u001b[38;5;241;43m3\u001b[39;49m\u001b[43m]\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43misin\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m150\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m151\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m152\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      4\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mBOBBY WILSON\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      5\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdescription\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mBOBBY WILSON\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      6\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdescription\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mDARLENE HARRIS\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      7\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mKINKEAD\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      8\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43misin\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mALLEGHENY COUNTY JUSTICE FOR ALL\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mGOOD JOBS PITTSBURGH\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m      9\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mPEDUTO\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m     10\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mINNAMORATO\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m|\u001b[39;49m\n\u001b[1;32m     11\u001b[0m \u001b[43m        \u001b[49m\u001b[43m(\u001b[49m\u001b[43mexpenses\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdescription\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcontains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mINNAMORATO\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     12\u001b[0m \u001b[43m    \u001b[49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m     13\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mCommittee Name\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m     14\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43melection_year\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m     15\u001b[0m \u001b[43m    \u001b[49m\u001b[43mvalues\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mamount\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m     16\u001b[0m \u001b[43m    \u001b[49m\u001b[43maggfunc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43msum\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\n\u001b[1;32m     17\u001b[0m \u001b[43m)\u001b[49m\u001b[38;5;241m.\u001b[39mfillna(\u001b[38;5;241m0\u001b[39m)\u001b[38;5;241m.\u001b[39mtranspose()\n\u001b[1;32m     19\u001b[0m totalspend[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mHealth-related\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m totalspend[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSEIU HEALTHCARE PA COPE\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m+\u001b[39m totalspend[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSEIU CAREGIVERS ACTION\u001b[39m\u001b[38;5;124m'\u001b[39m]\n\u001b[1;32m     21\u001b[0m totalspend \u001b[38;5;241m=\u001b[39m totalspend\u001b[38;5;241m.\u001b[39mrename(columns\u001b[38;5;241m=\u001b[39m{\n\u001b[1;32m     22\u001b[0m     \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mLOCAL 0032BJ PA AMERICAN DREAM FUND\u001b[39m\u001b[38;5;124m'\u001b[39m: \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m32BJ\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m     23\u001b[0m     \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mPSSU LOCAL 668 SEIU COPE FUND\u001b[39m\u001b[38;5;124m'\u001b[39m: \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m668\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m     24\u001b[0m     \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mPA SEIU COPE (SOC SVCS)\u001b[39m\u001b[38;5;124m'\u001b[39m: \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mState Council\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[1;32m     25\u001b[0m })\n",
      "File \u001b[0;32m~/.local/share/virtualenvs/seiuspend-S3xRT4_2/lib/python3.9/site-packages/pandas/core/reshape/pivot.py:97\u001b[0m, in \u001b[0;36mpivot_table\u001b[0;34m(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed, sort)\u001b[0m\n\u001b[1;32m     94\u001b[0m     table \u001b[38;5;241m=\u001b[39m concat(pieces, keys\u001b[38;5;241m=\u001b[39mkeys, axis\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m1\u001b[39m)\n\u001b[1;32m     95\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m table\u001b[38;5;241m.\u001b[39m__finalize__(data, method\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpivot_table\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m---> 97\u001b[0m table \u001b[38;5;241m=\u001b[39m \u001b[43m__internal_pivot_table\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m     98\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m     99\u001b[0m \u001b[43m    \u001b[49m\u001b[43mvalues\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    100\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    101\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcolumns\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    102\u001b[0m \u001b[43m    \u001b[49m\u001b[43maggfunc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    103\u001b[0m \u001b[43m    \u001b[49m\u001b[43mfill_value\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    104\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmargins\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    105\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdropna\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    106\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmargins_name\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    107\u001b[0m \u001b[43m    \u001b[49m\u001b[43mobserved\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    108\u001b[0m \u001b[43m    \u001b[49m\u001b[43msort\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    109\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    110\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m table\u001b[38;5;241m.\u001b[39m__finalize__(data, method\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpivot_table\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/.local/share/virtualenvs/seiuspend-S3xRT4_2/lib/python3.9/site-packages/pandas/core/reshape/pivot.py:166\u001b[0m, in \u001b[0;36m__internal_pivot_table\u001b[0;34m(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed, sort)\u001b[0m\n\u001b[1;32m    163\u001b[0m             \u001b[38;5;28;01mpass\u001b[39;00m\n\u001b[1;32m    164\u001b[0m     values \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlist\u001b[39m(values)\n\u001b[0;32m--> 166\u001b[0m grouped \u001b[38;5;241m=\u001b[39m \u001b[43mdata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgroupby\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkeys\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mobserved\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mobserved\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43msort\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msort\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    167\u001b[0m agged \u001b[38;5;241m=\u001b[39m grouped\u001b[38;5;241m.\u001b[39magg(aggfunc)\n\u001b[1;32m    169\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m dropna \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(agged, ABCDataFrame) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(agged\u001b[38;5;241m.\u001b[39mcolumns):\n",
      "File \u001b[0;32m~/.local/share/virtualenvs/seiuspend-S3xRT4_2/lib/python3.9/site-packages/pandas/core/frame.py:8262\u001b[0m, in \u001b[0;36mDataFrame.groupby\u001b[0;34m(self, by, axis, level, as_index, sort, group_keys, observed, dropna)\u001b[0m\n\u001b[1;32m   8259\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mYou have to supply one of \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mby\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m and \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlevel\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m   8260\u001b[0m axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_axis_number(axis)\n\u001b[0;32m-> 8262\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mDataFrameGroupBy\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   8263\u001b[0m \u001b[43m    \u001b[49m\u001b[43mobj\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8264\u001b[0m \u001b[43m    \u001b[49m\u001b[43mkeys\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mby\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8265\u001b[0m \u001b[43m    \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8266\u001b[0m \u001b[43m    \u001b[49m\u001b[43mlevel\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlevel\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8267\u001b[0m \u001b[43m    \u001b[49m\u001b[43mas_index\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mas_index\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8268\u001b[0m \u001b[43m    \u001b[49m\u001b[43msort\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msort\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8269\u001b[0m \u001b[43m    \u001b[49m\u001b[43mgroup_keys\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mgroup_keys\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8270\u001b[0m \u001b[43m    \u001b[49m\u001b[43mobserved\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mobserved\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8271\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdropna\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdropna\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   8272\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/share/virtualenvs/seiuspend-S3xRT4_2/lib/python3.9/site-packages/pandas/core/groupby/groupby.py:931\u001b[0m, in \u001b[0;36mGroupBy.__init__\u001b[0;34m(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, observed, dropna)\u001b[0m\n\u001b[1;32m    928\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdropna \u001b[38;5;241m=\u001b[39m dropna\n\u001b[1;32m    930\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m grouper \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m--> 931\u001b[0m     grouper, exclusions, obj \u001b[38;5;241m=\u001b[39m \u001b[43mget_grouper\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    932\u001b[0m \u001b[43m        \u001b[49m\u001b[43mobj\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    933\u001b[0m \u001b[43m        \u001b[49m\u001b[43mkeys\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    934\u001b[0m \u001b[43m        \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    935\u001b[0m \u001b[43m        \u001b[49m\u001b[43mlevel\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlevel\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    936\u001b[0m \u001b[43m        \u001b[49m\u001b[43msort\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msort\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    937\u001b[0m \u001b[43m        \u001b[49m\u001b[43mobserved\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mobserved\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    938\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdropna\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdropna\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    939\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    941\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj \u001b[38;5;241m=\u001b[39m obj\n\u001b[1;32m    942\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39maxis \u001b[38;5;241m=\u001b[39m obj\u001b[38;5;241m.\u001b[39m_get_axis_number(axis)\n",
      "File \u001b[0;32m~/.local/share/virtualenvs/seiuspend-S3xRT4_2/lib/python3.9/site-packages/pandas/core/groupby/grouper.py:985\u001b[0m, in \u001b[0;36mget_grouper\u001b[0;34m(obj, key, axis, level, sort, observed, validate, dropna)\u001b[0m\n\u001b[1;32m    983\u001b[0m         in_axis, level, gpr \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m, gpr, \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m    984\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m--> 985\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(gpr)\n\u001b[1;32m    986\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(gpr, Grouper) \u001b[38;5;129;01mand\u001b[39;00m gpr\u001b[38;5;241m.\u001b[39mkey \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m    987\u001b[0m     \u001b[38;5;66;03m# Add key to exclusions\u001b[39;00m\n\u001b[1;32m    988\u001b[0m     exclusions\u001b[38;5;241m.\u001b[39madd(gpr\u001b[38;5;241m.\u001b[39mkey)\n",
      "\u001b[0;31mKeyError\u001b[0m: 'election_year'"
     ]
    }
   ],
   "source": [
    "totalspend = pd.pivot_table(\n",
    "    expenses[\n",
    "        (expenses.zipcode.str[:3].isin(['150', '151', '152'])) |\n",
    "        (expenses.name.str.contains('BOBBY WILSON')) |\n",
    "        (expenses.description.str.contains('BOBBY WILSON')) |\n",
    "        (expenses.description.str.contains('DARLENE HARRIS')) |\n",
    "        (expenses.name.str.contains('KINKEAD')) |\n",
    "        (expenses.name.isin(['ALLEGHENY COUNTY JUSTICE FOR ALL', 'GOOD JOBS PITTSBURGH'])) |\n",
    "        (expenses.name.str.contains('PEDUTO')) |\n",
    "        (expenses.name.str.contains('INNAMORATO')) |\n",
    "        (expenses.description.str.contains('INNAMORATO'))\n",
    "    ],\n",
    "    index='Committee Name',\n",
    "    columns='election_year',\n",
    "    values='amount',\n",
    "    aggfunc='sum'\n",
    ").fillna(0).transpose()\n",
    "\n",
    "totalspend['Health-related'] = totalspend['SEIU HEALTHCARE PA COPE'] + totalspend['SEIU CAREGIVERS ACTION']\n",
    "\n",
    "totalspend = totalspend.rename(columns={\n",
    "    'LOCAL 0032BJ PA AMERICAN DREAM FUND': '32BJ',\n",
    "    'PSSU LOCAL 668 SEIU COPE FUND': '668',\n",
    "    'PA SEIU COPE (SOC SVCS)': 'State Council'\n",
    "})\n",
    "\n",
    "totalspend[['Health-related', '32BJ', '668', 'State Council']].to_csv('output/chart.csv')"
   ]
  }
 ],
 "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}