federal-awards-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "469d7004-a299-48fb-8700-6559641217b9",
   "metadata": {},
   "source": [
    "# Federal awards\n",
    "\n",
    "This notebook analyzes federal awards data (assistance prime transactions) downloaded from [USAspending.gov](https://www.usaspending.gov/search/?hash=966f5f0446cbe09c05fb1d16dee2885a&section=map).\n",
    "\n",
    "The objective is to find which agencies awarded funding to congressional districts 14 and 15 and certain neighborhoods in the South Bronx."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "14457b3b-aba8-48be-91ff-9b7ffa403cae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f8bdbb0c-c324-4c8c-a53e-f933ba22357d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_25527/3904420668.py:1: DtypeWarning: Columns (65,67) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\"usaspending/Assistance_PrimeTransactions_2024-11-20_H01M42S17_1.csv\")\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(\"usaspending/Assistance_PrimeTransactions_2024-11-20_H01M42S17_1.csv\")\n",
    "pd.set_option('display.float_format', '{:.0f}'.format) # show full value of float\n",
    "pd.set_option('display.max_colwidth', None) # show full strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9cf453fb-9b97-4296-8a55-d347b85a1708",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "138297"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1e29c83-7715-4bf6-bcd6-571341a2635c",
   "metadata": {},
   "source": [
    "## Setting up our working `df` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "48810153-2720-4062-bab2-18bb21721cb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "138297"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# keeping only some columns, so we don't have to work with so much\n",
    "filtered_df = df[[\"awarding_agency_name\", \"federal_action_obligation\",\n",
    "            \"total_obligated_amount\", \"total_outlayed_amount_for_overall_award\", \"action_date\",\n",
    "            \"action_date_fiscal_year\", \"awarding_sub_agency_name\", \"program_activities_funding_this_award\",\n",
    "             \"recipient_name\", \"recipient_zip_code\", \"prime_award_transaction_recipient_cd_original\",\n",
    "             \"cfda_title\", \"assistance_type_description\", \"transaction_description\", \"business_types_description\",\n",
    "             \"usaspending_permalink\"\n",
    "            ]]\n",
    "len(filtered_df) # should be the same as df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "403bce03-e89a-4cb6-b4cb-6d963cbc5cb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "awarding_agency_name                              object\n",
       "federal_action_obligation                        float64\n",
       "total_obligated_amount                           float64\n",
       "total_outlayed_amount_for_overall_award          float64\n",
       "action_date                                       object\n",
       "action_date_fiscal_year                            int64\n",
       "awarding_sub_agency_name                          object\n",
       "program_activities_funding_this_award             object\n",
       "recipient_name                                    object\n",
       "recipient_zip_code                               float64\n",
       "prime_award_transaction_recipient_cd_original     object\n",
       "cfda_title                                        object\n",
       "assistance_type_description                       object\n",
       "transaction_description                           object\n",
       "business_types_description                        object\n",
       "usaspending_permalink                             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fdc92b39-b469-4daf-8c46-ba5b06338298",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4f/fk_hbgy14cg47tw521m3y0xw0000gn/T/ipykernel_25527/2500644147.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[\"recipient_zip_code\"] = filtered_df[\"recipient_zip_code\"].fillna(0).astype(int)\n"
     ]
    }
   ],
   "source": [
    "# convert zip floats to int\n",
    "filtered_df[\"recipient_zip_code\"] = filtered_df[\"recipient_zip_code\"].fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "880774f8-52d7-4af2-8491-90c03b16e975",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4064"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# keeping only those tagged NY-14 or NY-15, which are our target congressional districts\n",
    "filtered_df = filtered_df[filtered_df[\"prime_award_transaction_recipient_cd_original\"].isin([\"NY-14\", \"NY-15\"])]\n",
    "len(filtered_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "77acc4ef-7ce7-4b6f-938b-d3cde501752a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[10451, 10455, 10459, 10454, 10456, 10474]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# target zip codes\n",
    "zip_codes = [{\n",
    "        \"zip_num\": 10451,\n",
    "        \"neighborhood\": \"Melrose\"\n",
    "    },\n",
    "    {\n",
    "        \"zip_num\": 10455,\n",
    "        \"neighborhood\": \"Longwood\"\n",
    "    },\n",
    "    {\n",
    "        \"zip_num\": 10459,\n",
    "        \"neighborhood\": \"Longwood\"\n",
    "    },\n",
    "    {\n",
    "        \"zip_num\": 10454,\n",
    "        \"neighborhood\": \"Mott Haven\"\n",
    "    },\n",
    "    {\n",
    "        \"zip_num\": 10456,\n",
    "        \"neighborhood\": \"Morrisania\"\n",
    "    },\n",
    "    {\n",
    "        \"zip_num\": 10474,\n",
    "        \"neighborhood\": \"Hunts Point\"\n",
    "    }]\n",
    "\n",
    "# converting list of dicts to df\n",
    "zip_codes = pd.DataFrame(zip_codes)\n",
    "\n",
    "# converting target column to list\n",
    "zip_list = list(zip_codes[\"zip_num\"])\n",
    "zip_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "dec62a27-d5a7-463c-9f34-e133463e26a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "452"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# keeping only those with the zip codes we need\n",
    "filtered_df = filtered_df[filtered_df[\"recipient_zip_code\"].isin(zip_list)]\n",
    "len(filtered_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3d0aa702-bdbc-470b-b95e-a7cfef89d0bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "452"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merging zip details, to make it easier for analysis?\n",
    "\n",
    "filtered_df = pd.merge(filtered_df, zip_codes, left_on=\"recipient_zip_code\", right_on=\"zip_num\", how='left')\n",
    "filtered_df = filtered_df.drop(columns=[\"zip_num\"])\n",
    "len(filtered_df) # should be the same as previous len()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "744156d8-da6d-4d11-b7b6-bcf448bc73ba",
   "metadata": {},
   "source": [
    "### Analysis\n",
    "\n",
    "We downsized our 138297-items-long dataset into 452 targeted rows. Now, we begin our analysis. The objective is to find which agencies or areas are getting the most or least federal funding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a65e8188-1ac3-4252-9e4e-6da6767d43ec",
   "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>awarding_agency_name</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>472075150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>253902948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Department of Education</td>\n",
       "      <td>3025707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>1699186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Social Security Administration</td>\n",
       "      <td>804621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Department of Labor</td>\n",
       "      <td>664273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>624662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Department of the Treasury</td>\n",
       "      <td>138706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>76484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>69475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Environmental Protection Agency</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Institute of Museum and Library Services</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>National Endowment for the Humanities</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           awarding_agency_name  \\\n",
       "0       Department of Health and Human Services   \n",
       "1   Department of Housing and Urban Development   \n",
       "2                       Department of Education   \n",
       "3                         Department of Justice   \n",
       "4                Social Security Administration   \n",
       "5                           Department of Labor   \n",
       "6             Federal Communications Commission   \n",
       "7                        Department of Commerce   \n",
       "8                    Department of the Treasury   \n",
       "9                 Small Business Administration   \n",
       "10              National Endowment for the Arts   \n",
       "11                        Department of Defense   \n",
       "12              Environmental Protection Agency   \n",
       "13     Institute of Museum and Library Services   \n",
       "14        National Endowment for the Humanities   \n",
       "15                  National Science Foundation   \n",
       "\n",
       "    total_outlayed_amount_for_overall_award  \n",
       "0                                 472075150  \n",
       "1                                 253902948  \n",
       "2                                   3025707  \n",
       "3                                   1699186  \n",
       "4                                    804621  \n",
       "5                                    664273  \n",
       "6                                    624662  \n",
       "7                                    200000  \n",
       "8                                    138706  \n",
       "9                                     76484  \n",
       "10                                    69475  \n",
       "11                                        0  \n",
       "12                                        0  \n",
       "13                                        0  \n",
       "14                                        0  \n",
       "15                                        0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by agency\n",
    "agency_outlayed_sum_df = filtered_df.groupby(\"awarding_agency_name\")[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "agency_outlayed_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5757dbd5-e54c-45be-8923-34d223f52132",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "awarding_agency_name\n",
       "Department of Housing and Urban Development    182\n",
       "Department of Health and Human Services        100\n",
       "Federal Communications Commission               56\n",
       "Small Business Administration                   46\n",
       "Department of Education                         32\n",
       "Department of Justice                           11\n",
       "National Endowment for the Arts                  8\n",
       "Department of Defense                            4\n",
       "Department of the Treasury                       3\n",
       "National Science Foundation                      2\n",
       "Department of Commerce                           2\n",
       "Department of Labor                              2\n",
       "Social Security Administration                   1\n",
       "Institute of Museum and Library Services         1\n",
       "Environmental Protection Agency                  1\n",
       "National Endowment for the Humanities            1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# counting items per agency\n",
    "filtered_df[\"awarding_agency_name\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c40db074-5788-432c-95b5-2c340594b9f4",
   "metadata": {
    "scrolled": true
   },
   "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>awarding_agency_name</th>\n",
       "      <th>awarding_sub_agency_name</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Health Resources and Services Administration</td>\n",
       "      <td>395487806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>Assistant Secretary for Housing--Federal Housing Commissioner</td>\n",
       "      <td>252853066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Administration for Children and Families</td>\n",
       "      <td>65136631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Substance Abuse and Mental Health Services Administration</td>\n",
       "      <td>9754853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Department of Education</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>3025707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Centers for Disease Control and Prevention</td>\n",
       "      <td>1695860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>Office of Justice Programs</td>\n",
       "      <td>1679186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>Assistant Secretary for Community Planning and Development</td>\n",
       "      <td>1049882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Social Security Administration</td>\n",
       "      <td>Social Security Administration</td>\n",
       "      <td>804621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Department of Labor</td>\n",
       "      <td>Employment and Training Administration</td>\n",
       "      <td>664273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>624662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>National Oceanic and Atmospheric Administration</td>\n",
       "      <td>200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Department of the Treasury</td>\n",
       "      <td>Internal Revenue Service</td>\n",
       "      <td>138706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>76484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>69475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>Offices, Boards and Divisions</td>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>Defense Logistics Agency</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Environmental Protection Agency</td>\n",
       "      <td>Environmental Protection Agency</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>Department of the Army</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Institute of Museum and Library Services</td>\n",
       "      <td>Institute of Museum and Library Services</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>National Endowment for the Humanities</td>\n",
       "      <td>National Endowment for the Humanities</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           awarding_agency_name  \\\n",
       "0       Department of Health and Human Services   \n",
       "1   Department of Housing and Urban Development   \n",
       "2       Department of Health and Human Services   \n",
       "3       Department of Health and Human Services   \n",
       "4                       Department of Education   \n",
       "5       Department of Health and Human Services   \n",
       "6                         Department of Justice   \n",
       "7   Department of Housing and Urban Development   \n",
       "8                Social Security Administration   \n",
       "9                           Department of Labor   \n",
       "10            Federal Communications Commission   \n",
       "11                       Department of Commerce   \n",
       "12                   Department of the Treasury   \n",
       "13                Small Business Administration   \n",
       "14              National Endowment for the Arts   \n",
       "15                        Department of Justice   \n",
       "16                        Department of Defense   \n",
       "17              Environmental Protection Agency   \n",
       "18                        Department of Defense   \n",
       "19     Institute of Museum and Library Services   \n",
       "20        National Endowment for the Humanities   \n",
       "21                  National Science Foundation   \n",
       "\n",
       "                                         awarding_sub_agency_name  \\\n",
       "0                    Health Resources and Services Administration   \n",
       "1   Assistant Secretary for Housing--Federal Housing Commissioner   \n",
       "2                        Administration for Children and Families   \n",
       "3       Substance Abuse and Mental Health Services Administration   \n",
       "4                                         Department of Education   \n",
       "5                      Centers for Disease Control and Prevention   \n",
       "6                                      Office of Justice Programs   \n",
       "7      Assistant Secretary for Community Planning and Development   \n",
       "8                                  Social Security Administration   \n",
       "9                          Employment and Training Administration   \n",
       "10                              Federal Communications Commission   \n",
       "11                National Oceanic and Atmospheric Administration   \n",
       "12                                       Internal Revenue Service   \n",
       "13                                  Small Business Administration   \n",
       "14                                National Endowment for the Arts   \n",
       "15                                  Offices, Boards and Divisions   \n",
       "16                                       Defense Logistics Agency   \n",
       "17                                Environmental Protection Agency   \n",
       "18                                         Department of the Army   \n",
       "19                       Institute of Museum and Library Services   \n",
       "20                          National Endowment for the Humanities   \n",
       "21                                    National Science Foundation   \n",
       "\n",
       "    total_outlayed_amount_for_overall_award  \n",
       "0                                 395487806  \n",
       "1                                 252853066  \n",
       "2                                  65136631  \n",
       "3                                   9754853  \n",
       "4                                   3025707  \n",
       "5                                   1695860  \n",
       "6                                   1679186  \n",
       "7                                   1049882  \n",
       "8                                    804621  \n",
       "9                                    664273  \n",
       "10                                   624662  \n",
       "11                                   200000  \n",
       "12                                   138706  \n",
       "13                                    76484  \n",
       "14                                    69475  \n",
       "15                                    20000  \n",
       "16                                        0  \n",
       "17                                        0  \n",
       "18                                        0  \n",
       "19                                        0  \n",
       "20                                        0  \n",
       "21                                        0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by subagency\n",
    "subagency_outlayed_sum_df = filtered_df.groupby([\"awarding_agency_name\", \"awarding_sub_agency_name\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "subagency_outlayed_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "281b1f77-fdaf-4fbe-96ae-dd065e3ca0eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "subagency_outlayed_sum_df.to_csv(\"subagency_totals.csv\", encoding=\"UTF-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "30f01a2a-1b7a-4244-8c86-470a9101a889",
   "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>recipient_zip_code</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10459</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>444480285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10455</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>107663975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10454</td>\n",
       "      <td>Mott Haven</td>\n",
       "      <td>78745589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10456</td>\n",
       "      <td>Morrisania</td>\n",
       "      <td>58769286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10451</td>\n",
       "      <td>Melrose</td>\n",
       "      <td>40403107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10474</td>\n",
       "      <td>Hunts Point</td>\n",
       "      <td>3218969</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   recipient_zip_code neighborhood  total_outlayed_amount_for_overall_award\n",
       "0               10459     Longwood                                444480285\n",
       "1               10455     Longwood                                107663975\n",
       "2               10454   Mott Haven                                 78745589\n",
       "3               10456   Morrisania                                 58769286\n",
       "4               10451      Melrose                                 40403107\n",
       "5               10474  Hunts Point                                  3218969"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by zip code \n",
    "zip_outlayed_sum_df = filtered_df.groupby([\"recipient_zip_code\", \"neighborhood\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "zip_outlayed_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1d80fc39-c7d1-4cc4-bb18-b7a9f9b61573",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_outlayed_sum_df.to_csv(\"zip_totals.csv\", encoding=\"UTF-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "13b685df-acc2-4393-9c18-2d5bff6b3768",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "recipient_zip_code\n",
       "10459    122\n",
       "10454     80\n",
       "10451     78\n",
       "10456     77\n",
       "10455     77\n",
       "10474     18\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# counting items per zip code\n",
    "filtered_df[\"recipient_zip_code\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d1b46cab-8478-4cdf-9ead-f9b4a7fd957b",
   "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>recipient_zip_code</th>\n",
       "      <th>awarding_agency_name</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10459</td>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>378433988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>90901620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10459</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>66027471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10454</td>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>46572676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10456</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>33762017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10454</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>31574391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>28447254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10456</td>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>24829214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>12290237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>9949035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10474</td>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>3190195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>2322460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>1679186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10451</td>\n",
       "      <td>Social Security Administration</td>\n",
       "      <td>804621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>703247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10454</td>\n",
       "      <td>Department of Labor</td>\n",
       "      <td>350000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Labor</td>\n",
       "      <td>314273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10454</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>188089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10456</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>163055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of the Treasury</td>\n",
       "      <td>138706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10455</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>136200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10451</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>118716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10454</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>35000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10451</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>27052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10454</td>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10455</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10456</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10459</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10474</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>14774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>10451</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>14475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>10474</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>14000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>10454</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>5432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>10459</td>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>3827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>10455</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>10456</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>10454</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>10451</td>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>10451</td>\n",
       "      <td>National Endowment for the Humanities</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>10451</td>\n",
       "      <td>Institute of Museum and Library Services</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>10451</td>\n",
       "      <td>Environmental Protection Agency</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>10474</td>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>10451</td>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>10455</td>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    recipient_zip_code                         awarding_agency_name  \\\n",
       "0                10459      Department of Health and Human Services   \n",
       "1                10455  Department of Housing and Urban Development   \n",
       "2                10459  Department of Housing and Urban Development   \n",
       "3                10454      Department of Health and Human Services   \n",
       "4                10456  Department of Housing and Urban Development   \n",
       "5                10454  Department of Housing and Urban Development   \n",
       "6                10451  Department of Housing and Urban Development   \n",
       "7                10456      Department of Health and Human Services   \n",
       "8                10455      Department of Health and Human Services   \n",
       "9                10451      Department of Health and Human Services   \n",
       "10               10474  Department of Housing and Urban Development   \n",
       "11               10455                      Department of Education   \n",
       "12               10455                        Department of Justice   \n",
       "13               10451               Social Security Administration   \n",
       "14               10451                      Department of Education   \n",
       "15               10454                          Department of Labor   \n",
       "16               10455                          Department of Labor   \n",
       "17               10451                       Department of Commerce   \n",
       "18               10454            Federal Communications Commission   \n",
       "19               10456            Federal Communications Commission   \n",
       "20               10451                   Department of the Treasury   \n",
       "21               10455            Federal Communications Commission   \n",
       "22               10451            Federal Communications Commission   \n",
       "23               10454              National Endowment for the Arts   \n",
       "24               10451                Small Business Administration   \n",
       "25               10454                        Department of Justice   \n",
       "26               10455              National Endowment for the Arts   \n",
       "27               10456                Small Business Administration   \n",
       "28               10459                Small Business Administration   \n",
       "29               10474            Federal Communications Commission   \n",
       "30               10451              National Endowment for the Arts   \n",
       "31               10474                Small Business Administration   \n",
       "32               10454                Small Business Administration   \n",
       "33               10459            Federal Communications Commission   \n",
       "34               10455                Small Business Administration   \n",
       "35               10456              National Endowment for the Arts   \n",
       "36               10454                      Department of Education   \n",
       "37               10451                  National Science Foundation   \n",
       "38               10451        National Endowment for the Humanities   \n",
       "39               10451     Institute of Museum and Library Services   \n",
       "40               10451              Environmental Protection Agency   \n",
       "41               10474                        Department of Justice   \n",
       "42               10451                        Department of Justice   \n",
       "43               10455                        Department of Defense   \n",
       "\n",
       "    total_outlayed_amount_for_overall_award  \n",
       "0                                 378433988  \n",
       "1                                  90901620  \n",
       "2                                  66027471  \n",
       "3                                  46572676  \n",
       "4                                  33762017  \n",
       "5                                  31574391  \n",
       "6                                  28447254  \n",
       "7                                  24829214  \n",
       "8                                  12290237  \n",
       "9                                   9949035  \n",
       "10                                  3190195  \n",
       "11                                  2322460  \n",
       "12                                  1679186  \n",
       "13                                   804621  \n",
       "14                                   703247  \n",
       "15                                   350000  \n",
       "16                                   314273  \n",
       "17                                   200000  \n",
       "18                                   188089  \n",
       "19                                   163055  \n",
       "20                                   138706  \n",
       "21                                   136200  \n",
       "22                                   118716  \n",
       "23                                    35000  \n",
       "24                                    27052  \n",
       "25                                    20000  \n",
       "26                                    20000  \n",
       "27                                    15000  \n",
       "28                                    15000  \n",
       "29                                    14774  \n",
       "30                                    14475  \n",
       "31                                    14000  \n",
       "32                                     5432  \n",
       "33                                     3827  \n",
       "34                                        0  \n",
       "35                                        0  \n",
       "36                                        0  \n",
       "37                                        0  \n",
       "38                                        0  \n",
       "39                                        0  \n",
       "40                                        0  \n",
       "41                                        0  \n",
       "42                                        0  \n",
       "43                                        0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by zip code and agency\n",
    "zip_agency_outlayed_sum_df = filtered_df.groupby([\"recipient_zip_code\", \"awarding_agency_name\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "zip_agency_outlayed_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5144ea7b-be44-4a46-9b82-5808e3957376",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_agency_outlayed_sum_df.to_csv(\"zip_agency_totals.csv\", encoding=\"UTF-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "edeb2033-8c15-40a1-b762-9d51fce677ea",
   "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>business_types_description</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OTHER</td>\n",
       "      <td>366006416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS)</td>\n",
       "      <td>251160019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>110893116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NONPROFIT WITHOUT 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>3577582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION</td>\n",
       "      <td>1507868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>51949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>INDEPENDENT SCHOOL DISTRICT;NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>48888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SMALL BUSINESS</td>\n",
       "      <td>24535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>INDEPENDENT SCHOOL DISTRICT</td>\n",
       "      <td>10840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CITY OR TOWNSHIP GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>COUNTY GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS);OTHER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);OTHER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>STATE GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                   business_types_description  \\\n",
       "0                                                                                                                                       OTHER   \n",
       "1                                                                                         FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS)   \n",
       "2                                                             NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "3                                                          NONPROFIT WITHOUT 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "4                                                                                     PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION   \n",
       "5                                                                                                                                  INDIVIDUAL   \n",
       "6                                 INDEPENDENT SCHOOL DISTRICT;NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "7                                                                                                                              SMALL BUSINESS   \n",
       "8                                                                                                                 INDEPENDENT SCHOOL DISTRICT   \n",
       "9                                                                                                                 CITY OR TOWNSHIP GOVERNMENT   \n",
       "10                                                                                                                          COUNTY GOVERNMENT   \n",
       "11  NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS);OTHER   \n",
       "12                                                      NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);OTHER   \n",
       "13                                                                                                                           STATE GOVERNMENT   \n",
       "\n",
       "    total_outlayed_amount_for_overall_award  \n",
       "0                                 366006416  \n",
       "1                                 251160019  \n",
       "2                                 110893116  \n",
       "3                                   3577582  \n",
       "4                                   1507868  \n",
       "5                                     51949  \n",
       "6                                     48888  \n",
       "7                                     24535  \n",
       "8                                     10840  \n",
       "9                                         0  \n",
       "10                                        0  \n",
       "11                                        0  \n",
       "12                                        0  \n",
       "13                                        0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by business type\n",
    "biztype_sum_df = filtered_df.groupby(\"business_types_description\")[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "biztype_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "552dd106-ac93-43aa-bc19-5f877edd45fc",
   "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>awarding_agency_name</th>\n",
       "      <th>business_types_description</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>361621775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS)</td>\n",
       "      <td>251160019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>106875794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>NONPROFIT WITHOUT 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>3577582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Department of Education</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>2322460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>1699186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>1693047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>1049882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Social Security Administration</td>\n",
       "      <td>PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION</td>\n",
       "      <td>804621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Department of Education</td>\n",
       "      <td>PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION</td>\n",
       "      <td>703247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Department of Labor</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>664273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>348027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>216908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Department of the Treasury</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>138706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>69475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>51949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>INDEPENDENT SCHOOL DISTRICT;NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>48888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>SMALL BUSINESS</td>\n",
       "      <td>24535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Federal Communications Commission</td>\n",
       "      <td>INDEPENDENT SCHOOL DISTRICT</td>\n",
       "      <td>10840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS);OTHER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Environmental Protection Agency</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>COUNTY GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Department of Defense</td>\n",
       "      <td>NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);OTHER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Institute of Museum and Library Services</td>\n",
       "      <td>STATE GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>National Endowment for the Humanities</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Department of Education</td>\n",
       "      <td>CITY OR TOWNSHIP GOVERNMENT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           awarding_agency_name  \\\n",
       "0       Department of Health and Human Services   \n",
       "1   Department of Housing and Urban Development   \n",
       "2       Department of Health and Human Services   \n",
       "3       Department of Health and Human Services   \n",
       "4                       Department of Education   \n",
       "5                         Department of Justice   \n",
       "6   Department of Housing and Urban Development   \n",
       "7   Department of Housing and Urban Development   \n",
       "8                Social Security Administration   \n",
       "9                       Department of Education   \n",
       "10                          Department of Labor   \n",
       "11            Federal Communications Commission   \n",
       "12            Federal Communications Commission   \n",
       "13                       Department of Commerce   \n",
       "14                   Department of the Treasury   \n",
       "15              National Endowment for the Arts   \n",
       "16                Small Business Administration   \n",
       "17            Federal Communications Commission   \n",
       "18                Small Business Administration   \n",
       "19            Federal Communications Commission   \n",
       "20                        Department of Defense   \n",
       "21              Environmental Protection Agency   \n",
       "22                        Department of Justice   \n",
       "23                        Department of Defense   \n",
       "24     Institute of Museum and Library Services   \n",
       "25        National Endowment for the Humanities   \n",
       "26                  National Science Foundation   \n",
       "27                      Department of Education   \n",
       "\n",
       "                                                                                                                   business_types_description  \\\n",
       "0                                                                                                                                       OTHER   \n",
       "1                                                                                         FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS)   \n",
       "2                                                             NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "3                                                          NONPROFIT WITHOUT 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "4                                                                                                                                       OTHER   \n",
       "5                                                             NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "6                                                             NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "7                                                                                                                                       OTHER   \n",
       "8                                                                                     PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION   \n",
       "9                                                                                     PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION   \n",
       "10                                                                                                                                      OTHER   \n",
       "11                                                                                                                                      OTHER   \n",
       "12                                                            NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "13                                                            NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "14                                                            NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "15                                                            NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "16                                                                                                                                 INDIVIDUAL   \n",
       "17                                INDEPENDENT SCHOOL DISTRICT;NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "18                                                                                                                             SMALL BUSINESS   \n",
       "19                                                                                                                INDEPENDENT SCHOOL DISTRICT   \n",
       "20  NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);FOR-PROFIT ORGANIZATION (OTHER THAN SMALL BUSINESS);OTHER   \n",
       "21                                                            NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION)   \n",
       "22                                                                                                                          COUNTY GOVERNMENT   \n",
       "23                                                      NONPROFIT WITH 501C3 IRS STATUS (OTHER THAN AN INSTITUTION OF HIGHER EDUCATION);OTHER   \n",
       "24                                                                                                                           STATE GOVERNMENT   \n",
       "25                                                                                                                                      OTHER   \n",
       "26                                                                                    PUBLIC/STATE CONTROLLED INSTITUTION OF HIGHER EDUCATION   \n",
       "27                                                                                                                CITY OR TOWNSHIP GOVERNMENT   \n",
       "\n",
       "    total_outlayed_amount_for_overall_award  \n",
       "0                                 361621775  \n",
       "1                                 251160019  \n",
       "2                                 106875794  \n",
       "3                                   3577582  \n",
       "4                                   2322460  \n",
       "5                                   1699186  \n",
       "6                                   1693047  \n",
       "7                                   1049882  \n",
       "8                                    804621  \n",
       "9                                    703247  \n",
       "10                                   664273  \n",
       "11                                   348027  \n",
       "12                                   216908  \n",
       "13                                   200000  \n",
       "14                                   138706  \n",
       "15                                    69475  \n",
       "16                                    51949  \n",
       "17                                    48888  \n",
       "18                                    24535  \n",
       "19                                    10840  \n",
       "20                                        0  \n",
       "21                                        0  \n",
       "22                                        0  \n",
       "23                                        0  \n",
       "24                                        0  \n",
       "25                                        0  \n",
       "26                                        0  \n",
       "27                                        0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by agency and business type\n",
    "agency_biztype_sum_df = filtered_df.groupby([\"awarding_agency_name\", \"business_types_description\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "agency_biztype_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "12431240-5470-45e1-869b-3aa80ebc7a6c",
   "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>awarding_agency_name</th>\n",
       "      <th>recipient_name</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>URBAN HEALTH PLAN, INC</td>\n",
       "      <td>194957876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>LA CASA DE SALUD INC</td>\n",
       "      <td>183476111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>EAST SIDE HOUSE, INC.</td>\n",
       "      <td>46572676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>NEW VISION COMMUNITY REDEVELOPMENT HOUSING DEVELOPMENT FUND CORPORATION</td>\n",
       "      <td>31640391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>WEST FARMS ESTATES CO L.P</td>\n",
       "      <td>22627040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>BELINDA AFRICAN CARRIBEAN MARKET LLC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>BRIGHT FUTURES 1-2-3 GROUP FAMILY DAYCARE</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>BUUNNI COFFEE LLC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>National Science Foundation</td>\n",
       "      <td>RESEARCH FOUNDATION OF THE CITY UNIVERSITY OF NEW YORK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>TAXI DRIVER</td>\n",
       "      <td>-1000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>145 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            awarding_agency_name  \\\n",
       "0        Department of Health and Human Services   \n",
       "1        Department of Health and Human Services   \n",
       "2        Department of Health and Human Services   \n",
       "3    Department of Housing and Urban Development   \n",
       "4    Department of Housing and Urban Development   \n",
       "..                                           ...   \n",
       "140                Small Business Administration   \n",
       "141                Small Business Administration   \n",
       "142                Small Business Administration   \n",
       "143                  National Science Foundation   \n",
       "144                Small Business Administration   \n",
       "\n",
       "                                                              recipient_name  \\\n",
       "0                                                     URBAN HEALTH PLAN, INC   \n",
       "1                                                       LA CASA DE SALUD INC   \n",
       "2                                                      EAST SIDE HOUSE, INC.   \n",
       "3    NEW VISION COMMUNITY REDEVELOPMENT HOUSING DEVELOPMENT FUND CORPORATION   \n",
       "4                                                  WEST FARMS ESTATES CO L.P   \n",
       "..                                                                       ...   \n",
       "140                                     BELINDA AFRICAN CARRIBEAN MARKET LLC   \n",
       "141                                BRIGHT FUTURES 1-2-3 GROUP FAMILY DAYCARE   \n",
       "142                                                        BUUNNI COFFEE LLC   \n",
       "143                   RESEARCH FOUNDATION OF THE CITY UNIVERSITY OF NEW YORK   \n",
       "144                                                              TAXI DRIVER   \n",
       "\n",
       "     total_outlayed_amount_for_overall_award  \n",
       "0                                  194957876  \n",
       "1                                  183476111  \n",
       "2                                   46572676  \n",
       "3                                   31640391  \n",
       "4                                   22627040  \n",
       "..                                       ...  \n",
       "140                                        0  \n",
       "141                                        0  \n",
       "142                                        0  \n",
       "143                                        0  \n",
       "144                                    -1000  \n",
       "\n",
       "[145 rows x 3 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by agency and recipient\n",
    "agency_recipient_sum_df = filtered_df.groupby([\"awarding_agency_name\", \"recipient_name\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "agency_recipient_sum_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d3c9c788-9da1-4692-96ad-e1a1b96753c5",
   "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>awarding_agency_name</th>\n",
       "      <th>awarding_sub_agency_name</th>\n",
       "      <th>recipient_name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>total_outlayed_amount_for_overall_award</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Health Resources and Services Administration</td>\n",
       "      <td>URBAN HEALTH PLAN, INC</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>194957876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Health Resources and Services Administration</td>\n",
       "      <td>LA CASA DE SALUD INC</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>182418768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Administration for Children and Families</td>\n",
       "      <td>EAST SIDE HOUSE, INC.</td>\n",
       "      <td>Mott Haven</td>\n",
       "      <td>46572676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>Assistant Secretary for Housing--Federal Housing Commissioner</td>\n",
       "      <td>NEW VISION COMMUNITY REDEVELOPMENT HOUSING DEVELOPMENT FUND CORPORATION</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>31640391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Department of Housing and Urban Development</td>\n",
       "      <td>Assistant Secretary for Housing--Federal Housing Commissioner</td>\n",
       "      <td>WEST FARMS ESTATES CO L.P</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>22627040</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>149</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>BUUNNI COFFEE LLC</td>\n",
       "      <td>Hunts Point</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>Offices, Boards and Divisions</td>\n",
       "      <td>NEW YORK, CITY OF</td>\n",
       "      <td>Melrose</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Department of Health and Human Services</td>\n",
       "      <td>Administration for Children and Families</td>\n",
       "      <td>PUBLIC PREPATORY NETWORK INC.</td>\n",
       "      <td>Longwood</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>National Endowment for the Arts</td>\n",
       "      <td>YOUNG URBAN CHRISTIANS &amp; ARTISTS INC</td>\n",
       "      <td>Melrose</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>Small Business Administration</td>\n",
       "      <td>TAXI DRIVER</td>\n",
       "      <td>Melrose</td>\n",
       "      <td>-1000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>154 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            awarding_agency_name  \\\n",
       "0        Department of Health and Human Services   \n",
       "1        Department of Health and Human Services   \n",
       "2        Department of Health and Human Services   \n",
       "3    Department of Housing and Urban Development   \n",
       "4    Department of Housing and Urban Development   \n",
       "..                                           ...   \n",
       "149                Small Business Administration   \n",
       "150                        Department of Justice   \n",
       "151      Department of Health and Human Services   \n",
       "152              National Endowment for the Arts   \n",
       "153                Small Business Administration   \n",
       "\n",
       "                                          awarding_sub_agency_name  \\\n",
       "0                     Health Resources and Services Administration   \n",
       "1                     Health Resources and Services Administration   \n",
       "2                         Administration for Children and Families   \n",
       "3    Assistant Secretary for Housing--Federal Housing Commissioner   \n",
       "4    Assistant Secretary for Housing--Federal Housing Commissioner   \n",
       "..                                                             ...   \n",
       "149                                  Small Business Administration   \n",
       "150                                  Offices, Boards and Divisions   \n",
       "151                       Administration for Children and Families   \n",
       "152                                National Endowment for the Arts   \n",
       "153                                  Small Business Administration   \n",
       "\n",
       "                                                              recipient_name  \\\n",
       "0                                                     URBAN HEALTH PLAN, INC   \n",
       "1                                                       LA CASA DE SALUD INC   \n",
       "2                                                      EAST SIDE HOUSE, INC.   \n",
       "3    NEW VISION COMMUNITY REDEVELOPMENT HOUSING DEVELOPMENT FUND CORPORATION   \n",
       "4                                                  WEST FARMS ESTATES CO L.P   \n",
       "..                                                                       ...   \n",
       "149                                                        BUUNNI COFFEE LLC   \n",
       "150                                                        NEW YORK, CITY OF   \n",
       "151                                            PUBLIC PREPATORY NETWORK INC.   \n",
       "152                                     YOUNG URBAN CHRISTIANS & ARTISTS INC   \n",
       "153                                                              TAXI DRIVER   \n",
       "\n",
       "    neighborhood  total_outlayed_amount_for_overall_award  \n",
       "0       Longwood                                194957876  \n",
       "1       Longwood                                182418768  \n",
       "2     Mott Haven                                 46572676  \n",
       "3       Longwood                                 31640391  \n",
       "4       Longwood                                 22627040  \n",
       "..           ...                                      ...  \n",
       "149  Hunts Point                                        0  \n",
       "150      Melrose                                        0  \n",
       "151     Longwood                                        0  \n",
       "152      Melrose                                        0  \n",
       "153      Melrose                                    -1000  \n",
       "\n",
       "[154 rows x 5 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting by agency and recipient\n",
    "agency_recipient_sum_df = filtered_df.groupby([\"awarding_agency_name\", \"awarding_sub_agency_name\", \"recipient_name\", \"neighborhood\"])[\"total_outlayed_amount_for_overall_award\"]\\\n",
    ".sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\n",
    "\n",
    "agency_recipient_sum_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}