Campaign_finance_check.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "21be7af5-c501-4ba2-b45c-575e38773833",
   "metadata": {},
   "source": [
    "# Checking the aggregated data against its sources "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0646888-a841-4cb0-8e29-89d182ba130d",
   "metadata": {},
   "source": [
    "## Agreggating the source JSON files into a single dataframe and grouping based on the column filerName"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "2fc4c17e-977a-4896-96cf-a7ed556d3e6c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "# Function to read all JSON files from a folder into a single DataFrame\n",
    "def read_jsons_from_folder(folder_path):\n",
    "    all_files = [f for f in os.listdir(folder_path) if f.endswith('.json')]\n",
    "    df_list = []\n",
    "    \n",
    "    for file in all_files:\n",
    "        file_path = os.path.join(folder_path, file)\n",
    "        df = pd.read_json(file_path)\n",
    "        df_list.append(df)\n",
    "    \n",
    "    # Concatenate all DataFrames into a single DataFrame\n",
    "    combined_df = pd.concat(df_list, ignore_index=True)\n",
    "    return combined_df\n",
    "folder_path = 'data/'\n",
    "combined_df = read_jsons_from_folder(folder_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "86f5bdfb-4dfc-4895-a7f0-ebed9dc0eca3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# List of names to filter 'filerName' column by\n",
    "names_to_filter = ['Maple', 'Steinberg', 'Guerra',\n",
    "                   'Talamantes', 'Valenzuela', 'Kaplan', \n",
    "                   'Vang', 'Hume', 'Kennedy', 'Sterna', \n",
    "                   'Desmond', 'Jennings', 'Loloee', 'Frost']\n",
    "\n",
    "# Create a function to check if any substring in a list appears in a string\n",
    "def contains_any_substring(string, substrings):\n",
    "    return any(sub.lower() in string.lower() for sub in substrings)\n",
    "\n",
    "# Filter the DataFrame to include only rows where 'filerName' contains any of the specified names\n",
    "filtered_df_partial_match = combined_df[combined_df['filerName'].apply(lambda x: contains_any_substring(x, names_to_filter))]\n",
    "\n",
    "# Group by 'filerName' and aggregate the 'amount'\n",
    "aggregated_by_filer_partial_match = filtered_df_partial_match.groupby('filerName').agg({'amount': 'sum'}).reset_index()\n",
    "\n",
    "# Display the aggregated DataFrame\n",
    "# aggregated_by_filer_partial_match"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f6117d4-bfa0-45ff-ae70-0ebbe8ce9dfb",
   "metadata": {},
   "source": [
    "## Comparing totals to the aggregated JSON data object flattened into a dataframe reveal discrepencies for every campaign total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "54d14cee-032c-4b89-bdcf-90c4ec865721",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "aggregated_totals = pd.read_json('src/lib/data.json')\n",
    "# Flatten the 'data' column into its own DataFrame\n",
    "data_flattened_df = pd.json_normalize(aggregated_totals['data'])\n",
    "\n",
    "# Explode the 'committees' and 'contributors' columns to unpack the nested lists\n",
    "exploded_committees = data_flattened_df.explode('committees')\n",
    "exploded_contributors = data_flattened_df.explode('contributors')\n",
    "\n",
    "# Flatten the dictionaries in the exploded 'committees' and 'contributors' columns\n",
    "flattened_committees = pd.json_normalize(exploded_committees['committees'])\n",
    "flattened_contributors = pd.json_normalize(exploded_contributors['contributors'])\n",
    "\n",
    "# Merge the flattened DataFrames back with the original columns (excluding the exploded ones)\n",
    "final_committees_df = pd.concat([exploded_committees.drop('committees', axis=1).reset_index(drop=True), flattened_committees], axis=1)\n",
    "final_contributors_df = pd.concat([exploded_contributors.drop('contributors', axis=1).reset_index(drop=True), flattened_contributors], axis=1)\n",
    "\n",
    "# Aggregate contributions by 'filerName'\n",
    "aggregated_contributions = final_contributors_df.groupby([ 'filerName']).agg({'amount': 'sum'}).reset_index()\n",
    "\n",
    "# Display the aggregated DataFrames\n",
    "# aggregated_contributions\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "94f2cfd1-6db9-46fc-ada8-afe0f8256ced",
   "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>filerName</th>\n",
       "      <th>amount_individual_files</th>\n",
       "      <th>amount_aggregated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Caity Maple for City Council 2022</td>\n",
       "      <td>189032.32</td>\n",
       "      <td>189032.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Caity Maple for City Council 2026</td>\n",
       "      <td>10271.00</td>\n",
       "      <td>10271.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Eric Guerra for City Council 2015</td>\n",
       "      <td>214918.62</td>\n",
       "      <td>214918.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Eric Guerra for City Council 2016</td>\n",
       "      <td>115796.62</td>\n",
       "      <td>115796.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Eric Guerra for City Council 2020</td>\n",
       "      <td>190794.00</td>\n",
       "      <td>190794.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Eric Guerra for City Council 2024</td>\n",
       "      <td>72191.03</td>\n",
       "      <td>72191.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>For A Better Sacramento, a Committee to Recall...</td>\n",
       "      <td>33312.85</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Hume for Supervisor 2022</td>\n",
       "      <td>737219.93</td>\n",
       "      <td>737219.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Karina Talamantes for City Council 2022</td>\n",
       "      <td>310656.30</td>\n",
       "      <td>310656.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Karina Talamantes for City Council 2026</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Karina Talamantes for SCOE Trustee 2018</td>\n",
       "      <td>35290.54</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Karina Talamantes for SCOE Trustee 2022</td>\n",
       "      <td>34750.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Katie Valenzuela for Sacramento City Council 2020</td>\n",
       "      <td>68424.74</td>\n",
       "      <td>68424.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Katie Valenzuela for Sacramento City Council 2024</td>\n",
       "      <td>80159.00</td>\n",
       "      <td>80159.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Lisa Kaplan for City Council 2022</td>\n",
       "      <td>315329.40</td>\n",
       "      <td>315329.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Lisa Kaplan, Natomas School Board, 2018</td>\n",
       "      <td>42600.54</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Lisa Kaplan, Natomas School Board, 2022</td>\n",
       "      <td>19516.90</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Mai Vang for City Council 2020</td>\n",
       "      <td>192053.27</td>\n",
       "      <td>192053.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mai Vang for City Council 2024</td>\n",
       "      <td>87248.35</td>\n",
       "      <td>87248.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Mai Vang for School Board 2016</td>\n",
       "      <td>60012.41</td>\n",
       "      <td>60012.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Mayor Darrell Steinberg Committee for Sacramen...</td>\n",
       "      <td>811570.01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Patrick Kennedy for Supervisor 2018</td>\n",
       "      <td>85670.00</td>\n",
       "      <td>85670.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Patrick Kennedy for Supervisor 2022</td>\n",
       "      <td>143967.70</td>\n",
       "      <td>143967.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Rich Desmond for Supervisor 2020</td>\n",
       "      <td>399240.56</td>\n",
       "      <td>435589.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Rich Desmond for Supervisor 2024</td>\n",
       "      <td>105967.00</td>\n",
       "      <td>69618.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Rick Jennings for City Council 2014</td>\n",
       "      <td>155946.77</td>\n",
       "      <td>155946.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Rick Jennings for City Council 2018</td>\n",
       "      <td>168195.47</td>\n",
       "      <td>168195.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Rick Jennings for City Council 2022</td>\n",
       "      <td>61300.00</td>\n",
       "      <td>61300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Sacramentans Against the Recall of Councilmemb...</td>\n",
       "      <td>33000.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Sacramentans for Jobs and Economic Growth supp...</td>\n",
       "      <td>82670.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Sacramentans for Public Libraries 2016 - Yes o...</td>\n",
       "      <td>76673.60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Sacramentans for Public Libraries 2016, City C...</td>\n",
       "      <td>37500.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Sacramento Working Families supporting Valenzu...</td>\n",
       "      <td>61668.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Sean Loloee for City Council 2020</td>\n",
       "      <td>125528.81</td>\n",
       "      <td>125528.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Sean Loloee for City Council 2024</td>\n",
       "      <td>4905.00</td>\n",
       "      <td>4905.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Steinberg for Sacramento Mayor 2016</td>\n",
       "      <td>2294038.81</td>\n",
       "      <td>2294038.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Steinberg for Sacramento Mayor 2020</td>\n",
       "      <td>455788.93</td>\n",
       "      <td>455788.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Steinberg for Sacramento Mayor 2024</td>\n",
       "      <td>410656.71</td>\n",
       "      <td>410656.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Sue Frost for Supervisor 2016</td>\n",
       "      <td>151059.03</td>\n",
       "      <td>250453.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Sue Frost for Supervisor 2020</td>\n",
       "      <td>229870.40</td>\n",
       "      <td>141825.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Sue Frost for Supervisor 2024</td>\n",
       "      <td>20899.00</td>\n",
       "      <td>9549.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>The Coalition for Jobs and Economic Growth sup...</td>\n",
       "      <td>47819.69</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Yes on Measure L Sac Kids 2022 Vang Ballot Mea...</td>\n",
       "      <td>279089.19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Yes on Measure U - Mayor Darrell Steinberg Com...</td>\n",
       "      <td>477696.51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            filerName  \\\n",
       "0                   Caity Maple for City Council 2022   \n",
       "1                   Caity Maple for City Council 2026   \n",
       "2                   Eric Guerra for City Council 2015   \n",
       "3                   Eric Guerra for City Council 2016   \n",
       "4                   Eric Guerra for City Council 2020   \n",
       "5                   Eric Guerra for City Council 2024   \n",
       "6   For A Better Sacramento, a Committee to Recall...   \n",
       "7                            Hume for Supervisor 2022   \n",
       "8             Karina Talamantes for City Council 2022   \n",
       "9             Karina Talamantes for City Council 2026   \n",
       "10            Karina Talamantes for SCOE Trustee 2018   \n",
       "11            Karina Talamantes for SCOE Trustee 2022   \n",
       "12  Katie Valenzuela for Sacramento City Council 2020   \n",
       "13  Katie Valenzuela for Sacramento City Council 2024   \n",
       "14                  Lisa Kaplan for City Council 2022   \n",
       "15            Lisa Kaplan, Natomas School Board, 2018   \n",
       "16            Lisa Kaplan, Natomas School Board, 2022   \n",
       "17                     Mai Vang for City Council 2020   \n",
       "18                     Mai Vang for City Council 2024   \n",
       "19                     Mai Vang for School Board 2016   \n",
       "20  Mayor Darrell Steinberg Committee for Sacramen...   \n",
       "21                Patrick Kennedy for Supervisor 2018   \n",
       "22                Patrick Kennedy for Supervisor 2022   \n",
       "23                   Rich Desmond for Supervisor 2020   \n",
       "24                   Rich Desmond for Supervisor 2024   \n",
       "25                Rick Jennings for City Council 2014   \n",
       "26                Rick Jennings for City Council 2018   \n",
       "27                Rick Jennings for City Council 2022   \n",
       "28  Sacramentans Against the Recall of Councilmemb...   \n",
       "29  Sacramentans for Jobs and Economic Growth supp...   \n",
       "30  Sacramentans for Public Libraries 2016 - Yes o...   \n",
       "31  Sacramentans for Public Libraries 2016, City C...   \n",
       "32  Sacramento Working Families supporting Valenzu...   \n",
       "33                  Sean Loloee for City Council 2020   \n",
       "34                  Sean Loloee for City Council 2024   \n",
       "35                Steinberg for Sacramento Mayor 2016   \n",
       "36                Steinberg for Sacramento Mayor 2020   \n",
       "37                Steinberg for Sacramento Mayor 2024   \n",
       "38                      Sue Frost for Supervisor 2016   \n",
       "39                      Sue Frost for Supervisor 2020   \n",
       "40                      Sue Frost for Supervisor 2024   \n",
       "41  The Coalition for Jobs and Economic Growth sup...   \n",
       "42  Yes on Measure L Sac Kids 2022 Vang Ballot Mea...   \n",
       "43  Yes on Measure U - Mayor Darrell Steinberg Com...   \n",
       "\n",
       "    amount_individual_files  amount_aggregated  \n",
       "0                 189032.32          189032.32  \n",
       "1                  10271.00           10271.00  \n",
       "2                 214918.62          214918.62  \n",
       "3                 115796.62          115796.62  \n",
       "4                 190794.00          190794.00  \n",
       "5                  72191.03           72191.03  \n",
       "6                  33312.85                NaN  \n",
       "7                 737219.93          737219.93  \n",
       "8                 310656.30          310656.30  \n",
       "9                   2000.00                NaN  \n",
       "10                 35290.54                NaN  \n",
       "11                 34750.00                NaN  \n",
       "12                 68424.74           68424.74  \n",
       "13                 80159.00           80159.00  \n",
       "14                315329.40          315329.40  \n",
       "15                 42600.54                NaN  \n",
       "16                 19516.90                NaN  \n",
       "17                192053.27          192053.27  \n",
       "18                 87248.35           87248.35  \n",
       "19                 60012.41           60012.41  \n",
       "20                811570.01                NaN  \n",
       "21                 85670.00           85670.00  \n",
       "22                143967.70          143967.70  \n",
       "23                399240.56          435589.56  \n",
       "24                105967.00           69618.00  \n",
       "25                155946.77          155946.77  \n",
       "26                168195.47          168195.47  \n",
       "27                 61300.00           61300.00  \n",
       "28                 33000.00                NaN  \n",
       "29                 82670.00                NaN  \n",
       "30                 76673.60                NaN  \n",
       "31                 37500.00                NaN  \n",
       "32                 61668.33                NaN  \n",
       "33                125528.81          125528.81  \n",
       "34                  4905.00            4905.00  \n",
       "35               2294038.81         2294038.81  \n",
       "36                455788.93          455788.93  \n",
       "37                410656.71          410656.71  \n",
       "38                151059.03          250453.59  \n",
       "39                229870.40          141825.84  \n",
       "40                 20899.00            9549.00  \n",
       "41                 47819.69                NaN  \n",
       "42                279089.19                NaN  \n",
       "43                477696.51                NaN  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# from individual json files\n",
    "# aggregated_by_filer_partial_match\n",
    "# from aggregated data.json\n",
    "# aggregated_contributions\n",
    "aggregated_by_filer_partial_match.merge(aggregated_contributions, on=\"filerName\", how=\"left\", suffixes=[\"_individual_files\", \"_aggregated\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5732592b",
   "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.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}