Analysis/LWCF_Grant_Value_by_County_Rollup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rollup of Total Grant Value for all US Counties\n",
    "\n",
    "Grant information by state was compiled by [Investigate West](http://www.invw.org/2012/06/11/lwcf-grants-database-1283/) for the period 1965-2011."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import re\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Grant ID &amp; Element</th>\n",
       "      <th>Grant Name</th>\n",
       "      <th>Sponsor</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Grant Amount</th>\n",
       "      <th>Year Approved</th>\n",
       "      <th>Year Completed</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3 - XXX</td>\n",
       "      <td>INDIAN PEAKS - BAKER CANYON</td>\n",
       "      <td>STATE OF UTAH</td>\n",
       "      <td>BEAVER</td>\n",
       "      <td>UT</td>\n",
       "      <td>5985.92</td>\n",
       "      <td>1966</td>\n",
       "      <td>1966</td>\n",
       "      <td>Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4 - XXX</td>\n",
       "      <td>WEST BEAR RIVER BAY</td>\n",
       "      <td>STATE OF UTAH</td>\n",
       "      <td>BOX ELDER</td>\n",
       "      <td>UT</td>\n",
       "      <td>18500.00</td>\n",
       "      <td>1966</td>\n",
       "      <td>1967</td>\n",
       "      <td>Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6 - XXX</td>\n",
       "      <td>WELLSVILLE PARK</td>\n",
       "      <td>CITY OF WELLSVILLE</td>\n",
       "      <td>CACHE</td>\n",
       "      <td>UT</td>\n",
       "      <td>934.25</td>\n",
       "      <td>1966</td>\n",
       "      <td>1967</td>\n",
       "      <td>Acquisition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2 - XXX</td>\n",
       "      <td>FARMINGTON BAY WATERFOWL AREA</td>\n",
       "      <td>STATE OF UTAH</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>UT</td>\n",
       "      <td>15150.00</td>\n",
       "      <td>1966</td>\n",
       "      <td>1967</td>\n",
       "      <td>Acquisition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11 - XXX</td>\n",
       "      <td>4 CITY PARKS</td>\n",
       "      <td>CITY OF BOUNTIFUL</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>UT</td>\n",
       "      <td>16225.15</td>\n",
       "      <td>1966</td>\n",
       "      <td>1966</td>\n",
       "      <td>Development</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Grant ID & Element                     Grant Name             Sponsor  \\\n",
       "0            3 - XXX    INDIAN PEAKS - BAKER CANYON       STATE OF UTAH   \n",
       "1            4 - XXX            WEST BEAR RIVER BAY       STATE OF UTAH   \n",
       "2            6 - XXX                WELLSVILLE PARK  CITY OF WELLSVILLE   \n",
       "3            2 - XXX  FARMINGTON BAY WATERFOWL AREA       STATE OF UTAH   \n",
       "4           11 - XXX                   4 CITY PARKS   CITY OF BOUNTIFUL   \n",
       "\n",
       "      County State  Grant Amount  Year Approved  Year Completed         Type  \n",
       "0     BEAVER    UT       5985.92           1966            1966  Development  \n",
       "1  BOX ELDER    UT      18500.00           1966            1967  Development  \n",
       "2      CACHE    UT        934.25           1966            1967  Acquisition  \n",
       "3      DAVIS    UT      15150.00           1966            1967  Acquisition  \n",
       "4      DAVIS    UT      16225.15           1966            1966  Development  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Collect all file names and process into DataFrames\n",
    "state_files = glob.glob('./Data/StateGrantData/*.xlsx')\n",
    "dfs = []\n",
    "\n",
    "for file in state_files:\n",
    "    dfs.append(pd.read_excel(file))\n",
    "\n",
    "all_states = pd.concat(dfs, ignore_index=True)\n",
    "all_states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 41145 entries, 0 to 41144\n",
      "Data columns (total 9 columns):\n",
      "Grant ID & Element    41145 non-null object\n",
      "Grant Name            41145 non-null object\n",
      "Sponsor               41145 non-null object\n",
      "County                41145 non-null object\n",
      "State                 41145 non-null object\n",
      "Grant Amount          41145 non-null float64\n",
      "Year Approved         41145 non-null int64\n",
      "Year Completed        41145 non-null int64\n",
      "Type                  41145 non-null object\n",
      "dtypes: float64(1), int64(2), object(6)\n",
      "memory usage: 2.8+ MB\n"
     ]
    }
   ],
   "source": [
    "all_states.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total grant value: $3,821,751,060\n",
      "Total grants: 41,145\n"
     ]
    }
   ],
   "source": [
    "print('Total grant value: ${0:,.0f}'.format(all_states['Grant Amount'].sum()))\n",
    "print('Total grants: {0:,.0f}'.format(all_states['Grant Amount'].count()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA',\n",
       "       'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME',\n",
       "       'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM',\n",
       "       'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX',\n",
       "       'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_states['State'].sort_values().unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count states - should be 51 (includes DC)\n",
    "all_states['State'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fix structural issues in county names to tie to county id data\n",
    "\n",
    "# Make all counties lowercase\n",
    "all_states['County'] = all_states['County'].map(lambda s: s.lower())\n",
    "\n",
    "# Change all occurences of \"saint\" to \"st.\"\n",
    "all_states['County'] = all_states['County'].map(lambda s: s.replace('saint', 'st.'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group County Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>state</th>\n",
       "      <th>county_orig</th>\n",
       "      <th>num_grants</th>\n",
       "      <th>grant_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians east</td>\n",
       "      <td>5</td>\n",
       "      <td>123553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians west</td>\n",
       "      <td>1</td>\n",
       "      <td>52405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK</td>\n",
       "      <td>anchorage</td>\n",
       "      <td>67</td>\n",
       "      <td>9475507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AK</td>\n",
       "      <td>bethel</td>\n",
       "      <td>3</td>\n",
       "      <td>366133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AK</td>\n",
       "      <td>dillingham</td>\n",
       "      <td>2</td>\n",
       "      <td>96120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state     county_orig  num_grants  grant_value\n",
       "0    AK  aleutians east           5       123553\n",
       "1    AK  aleutians west           1        52405\n",
       "2    AK       anchorage          67      9475507\n",
       "3    AK          bethel           3       366133\n",
       "4    AK      dillingham           2        96120"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create grouped grant data\n",
    "all_counties = all_states.groupby(['State', 'County'])['Grant Amount'].agg(['count', 'sum'])\n",
    "\n",
    "# Reset multi-index to flatten columns\n",
    "all_counties = all_counties.reset_index()\n",
    "\n",
    "# Re-name columns\n",
    "all_counties.columns = ['state', 'county_orig', 'num_grants', 'grant_value']\n",
    "\n",
    "# Round grant_value column to whole dollars, convert type to int\n",
    "all_counties['grant_value'] = all_counties['grant_value'].map(lambda n: int(round(n)))\n",
    "\n",
    "all_counties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multi-county, planning, and no county rows\n",
    "drop_rows = ['multi-county', 'planning', 'no county', 'county name missing']\n",
    "all_counties = all_counties[~all_counties['county_orig'].isin(drop_rows)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>state</th>\n",
       "      <th>county_orig</th>\n",
       "      <th>num_grants</th>\n",
       "      <th>grant_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2280</th>\n",
       "      <td>RI</td>\n",
       "      <td>bristol</td>\n",
       "      <td>17</td>\n",
       "      <td>2504765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2281</th>\n",
       "      <td>RI</td>\n",
       "      <td>kent</td>\n",
       "      <td>52</td>\n",
       "      <td>4462590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2283</th>\n",
       "      <td>RI</td>\n",
       "      <td>newport</td>\n",
       "      <td>35</td>\n",
       "      <td>4809098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2285</th>\n",
       "      <td>RI</td>\n",
       "      <td>providence</td>\n",
       "      <td>139</td>\n",
       "      <td>13877173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2286</th>\n",
       "      <td>RI</td>\n",
       "      <td>washington</td>\n",
       "      <td>75</td>\n",
       "      <td>12637118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state county_orig  num_grants  grant_value\n",
       "2280    RI     bristol          17      2504765\n",
       "2281    RI        kent          52      4462590\n",
       "2283    RI     newport          35      4809098\n",
       "2285    RI  providence         139     13877173\n",
       "2286    RI  washington          75     12637118"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_counties[all_counties['state'] == 'RI']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2946"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_counties['county_orig'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>state</th>\n",
       "      <th>county_orig</th>\n",
       "      <th>num_grants</th>\n",
       "      <th>grant_value</th>\n",
       "      <th>county_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians east</td>\n",
       "      <td>5</td>\n",
       "      <td>123553</td>\n",
       "      <td>aleutians east AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians west</td>\n",
       "      <td>1</td>\n",
       "      <td>52405</td>\n",
       "      <td>aleutians west AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK</td>\n",
       "      <td>anchorage</td>\n",
       "      <td>67</td>\n",
       "      <td>9475507</td>\n",
       "      <td>anchorage AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AK</td>\n",
       "      <td>bethel</td>\n",
       "      <td>3</td>\n",
       "      <td>366133</td>\n",
       "      <td>bethel AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AK</td>\n",
       "      <td>dillingham</td>\n",
       "      <td>2</td>\n",
       "      <td>96120</td>\n",
       "      <td>dillingham AK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state     county_orig  num_grants  grant_value         county_key\n",
       "0    AK  aleutians east           5       123553  aleutians east AK\n",
       "1    AK  aleutians west           1        52405  aleutians west AK\n",
       "2    AK       anchorage          67      9475507       anchorage AK\n",
       "3    AK          bethel           3       366133          bethel AK\n",
       "4    AK      dillingham           2        96120      dillingham AK"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create key column to merge on\n",
    "all_counties['county_key'] = all_counties['county_orig'] + ' ' + all_counties['state']\n",
    "all_counties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process County ID Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id    state          county\n",
       "0  01001  Alabama  Autauga County\n",
       "1  01003  Alabama  Baldwin County\n",
       "2  01005  Alabama  Barbour County\n",
       "3  01007  Alabama     Bibb County\n",
       "4  01009  Alabama   Blount County"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import county id data\n",
    "county_ids = pd.read_csv('./Data/county_names.csv', sep=',', dtype=str)\n",
    "county_ids.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3219"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_ids['county'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create state name and abbrev dictionary\n",
    "st_name = county_ids['state'].unique()\n",
    "st_abbrev = ['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA',\n",
    "             'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA',\n",
    "             'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY',\n",
    "             'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX',\n",
    "             'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY', 'PR']\n",
    "\n",
    "\n",
    "state_dict = dict(zip(st_name, st_abbrev))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>state_abbv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id    state          county state_abbv\n",
       "0  01001  Alabama  Autauga County         AL\n",
       "1  01003  Alabama  Baldwin County         AL\n",
       "2  01005  Alabama  Barbour County         AL\n",
       "3  01007  Alabama     Bibb County         AL\n",
       "4  01009  Alabama   Blount County         AL"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add state abbreviation column\n",
    "county_ids['state_abbv'] = county_ids['state'].map(lambda s: state_dict[s])\n",
    "county_ids.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>state_abbv</th>\n",
       "      <th>county_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2310</th>\n",
       "      <td>44001</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>Bristol County</td>\n",
       "      <td>RI</td>\n",
       "      <td>bristol RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2311</th>\n",
       "      <td>44003</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>RI</td>\n",
       "      <td>kent RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2312</th>\n",
       "      <td>44005</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>Newport County</td>\n",
       "      <td>RI</td>\n",
       "      <td>newport RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2313</th>\n",
       "      <td>44007</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>Providence County</td>\n",
       "      <td>RI</td>\n",
       "      <td>providence RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2314</th>\n",
       "      <td>44009</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>Washington County</td>\n",
       "      <td>RI</td>\n",
       "      <td>washington RI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id         state             county state_abbv     county_key\n",
       "2310  44001  Rhode Island     Bristol County         RI     bristol RI\n",
       "2311  44003  Rhode Island        Kent County         RI        kent RI\n",
       "2312  44005  Rhode Island     Newport County         RI     newport RI\n",
       "2313  44007  Rhode Island  Providence County         RI  providence RI\n",
       "2314  44009  Rhode Island  Washington County         RI  washington RI"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Edit county names to tie to grant data county names, create key column to merge on\n",
    "county_ids['county_key'] = county_ids['county'].map(lambda s: s.lower())\n",
    "\n",
    "pattern = re.compile(r' county| census area| municipality| borough| city and borough| parish')\n",
    "county_ids['county_key'] = county_ids['county_key'].str.replace(pattern, '')\n",
    "\n",
    "county_ids['county_key'] = county_ids['county_key'] + ' ' + county_ids['state_abbv']\n",
    "\n",
    "county_ids[county_ids['state_abbv'] == 'RI']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>id</th>\n",
       "      <th>county_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>autauga AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>baldwin AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>barbour AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>bibb AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>blount AL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  county_key\n",
       "0  01001  autauga AL\n",
       "1  01003  baldwin AL\n",
       "2  01005  barbour AL\n",
       "3  01007     bibb AL\n",
       "4  01009   blount AL"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unnecessary columns for merge\n",
    "county_ids.drop(['state', 'county', 'state_abbv'], axis=1, inplace=True)\n",
    "county_ids.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge Grant and County ID DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>state</th>\n",
       "      <th>county_orig</th>\n",
       "      <th>num_grants</th>\n",
       "      <th>grant_value</th>\n",
       "      <th>county_key</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians east</td>\n",
       "      <td>5</td>\n",
       "      <td>123553</td>\n",
       "      <td>aleutians east AK</td>\n",
       "      <td>02013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians west</td>\n",
       "      <td>1</td>\n",
       "      <td>52405</td>\n",
       "      <td>aleutians west AK</td>\n",
       "      <td>02016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK</td>\n",
       "      <td>anchorage</td>\n",
       "      <td>67</td>\n",
       "      <td>9475507</td>\n",
       "      <td>anchorage AK</td>\n",
       "      <td>02020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AK</td>\n",
       "      <td>bethel</td>\n",
       "      <td>3</td>\n",
       "      <td>366133</td>\n",
       "      <td>bethel AK</td>\n",
       "      <td>02050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AK</td>\n",
       "      <td>dillingham</td>\n",
       "      <td>2</td>\n",
       "      <td>96120</td>\n",
       "      <td>dillingham AK</td>\n",
       "      <td>02070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AK</td>\n",
       "      <td>fairbanks north star</td>\n",
       "      <td>23</td>\n",
       "      <td>2765459</td>\n",
       "      <td>fairbanks north star AK</td>\n",
       "      <td>02090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AK</td>\n",
       "      <td>haines</td>\n",
       "      <td>10</td>\n",
       "      <td>2978362</td>\n",
       "      <td>haines AK</td>\n",
       "      <td>02100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AK</td>\n",
       "      <td>juneau</td>\n",
       "      <td>18</td>\n",
       "      <td>1945648</td>\n",
       "      <td>juneau AK</td>\n",
       "      <td>02110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AK</td>\n",
       "      <td>kenai peninsula</td>\n",
       "      <td>35</td>\n",
       "      <td>3052184</td>\n",
       "      <td>kenai peninsula AK</td>\n",
       "      <td>02122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AK</td>\n",
       "      <td>ketchikan gateway</td>\n",
       "      <td>9</td>\n",
       "      <td>809122</td>\n",
       "      <td>ketchikan gateway AK</td>\n",
       "      <td>02130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AK</td>\n",
       "      <td>kodiak island</td>\n",
       "      <td>10</td>\n",
       "      <td>1052114</td>\n",
       "      <td>kodiak island AK</td>\n",
       "      <td>02150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AK</td>\n",
       "      <td>lake and peninsula</td>\n",
       "      <td>1</td>\n",
       "      <td>42702</td>\n",
       "      <td>lake and peninsula AK</td>\n",
       "      <td>02164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AK</td>\n",
       "      <td>matanuska-susitna</td>\n",
       "      <td>23</td>\n",
       "      <td>3381020</td>\n",
       "      <td>matanuska-susitna AK</td>\n",
       "      <td>02170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AK</td>\n",
       "      <td>nome</td>\n",
       "      <td>1</td>\n",
       "      <td>154000</td>\n",
       "      <td>nome AK</td>\n",
       "      <td>02180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AK</td>\n",
       "      <td>north slope</td>\n",
       "      <td>4</td>\n",
       "      <td>480041</td>\n",
       "      <td>north slope AK</td>\n",
       "      <td>02185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AK</td>\n",
       "      <td>northwest arctic</td>\n",
       "      <td>2</td>\n",
       "      <td>68662</td>\n",
       "      <td>northwest arctic AK</td>\n",
       "      <td>02188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AK</td>\n",
       "      <td>petersburg</td>\n",
       "      <td>2</td>\n",
       "      <td>557892</td>\n",
       "      <td>petersburg AK</td>\n",
       "      <td>02195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AK</td>\n",
       "      <td>prince of wales-hyder</td>\n",
       "      <td>7</td>\n",
       "      <td>377884</td>\n",
       "      <td>prince of wales-hyder AK</td>\n",
       "      <td>02198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AK</td>\n",
       "      <td>sitka</td>\n",
       "      <td>9</td>\n",
       "      <td>1361882</td>\n",
       "      <td>sitka AK</td>\n",
       "      <td>02220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AK</td>\n",
       "      <td>skagway</td>\n",
       "      <td>2</td>\n",
       "      <td>149179</td>\n",
       "      <td>skagway AK</td>\n",
       "      <td>02230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AK</td>\n",
       "      <td>southeast fairbanks</td>\n",
       "      <td>2</td>\n",
       "      <td>300381</td>\n",
       "      <td>southeast fairbanks AK</td>\n",
       "      <td>02240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AK</td>\n",
       "      <td>valdez-cordova</td>\n",
       "      <td>14</td>\n",
       "      <td>1115356</td>\n",
       "      <td>valdez-cordova AK</td>\n",
       "      <td>02261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AK</td>\n",
       "      <td>wrangell</td>\n",
       "      <td>3</td>\n",
       "      <td>136971</td>\n",
       "      <td>wrangell AK</td>\n",
       "      <td>02275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AK</td>\n",
       "      <td>yakutat</td>\n",
       "      <td>1</td>\n",
       "      <td>115180</td>\n",
       "      <td>yakutat AK</td>\n",
       "      <td>02282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AK</td>\n",
       "      <td>yukon-koyukuk</td>\n",
       "      <td>4</td>\n",
       "      <td>100368</td>\n",
       "      <td>yukon-koyukuk AK</td>\n",
       "      <td>02290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state            county_orig  num_grants  grant_value  \\\n",
       "0     AK         aleutians east           5       123553   \n",
       "1     AK         aleutians west           1        52405   \n",
       "2     AK              anchorage          67      9475507   \n",
       "3     AK                 bethel           3       366133   \n",
       "4     AK             dillingham           2        96120   \n",
       "5     AK   fairbanks north star          23      2765459   \n",
       "6     AK                 haines          10      2978362   \n",
       "7     AK                 juneau          18      1945648   \n",
       "8     AK        kenai peninsula          35      3052184   \n",
       "9     AK      ketchikan gateway           9       809122   \n",
       "10    AK          kodiak island          10      1052114   \n",
       "11    AK     lake and peninsula           1        42702   \n",
       "12    AK      matanuska-susitna          23      3381020   \n",
       "13    AK                   nome           1       154000   \n",
       "14    AK            north slope           4       480041   \n",
       "15    AK       northwest arctic           2        68662   \n",
       "16    AK             petersburg           2       557892   \n",
       "17    AK  prince of wales-hyder           7       377884   \n",
       "18    AK                  sitka           9      1361882   \n",
       "19    AK                skagway           2       149179   \n",
       "20    AK    southeast fairbanks           2       300381   \n",
       "21    AK         valdez-cordova          14      1115356   \n",
       "22    AK               wrangell           3       136971   \n",
       "23    AK                yakutat           1       115180   \n",
       "24    AK          yukon-koyukuk           4       100368   \n",
       "\n",
       "                  county_key     id  \n",
       "0          aleutians east AK  02013  \n",
       "1          aleutians west AK  02016  \n",
       "2               anchorage AK  02020  \n",
       "3                  bethel AK  02050  \n",
       "4              dillingham AK  02070  \n",
       "5    fairbanks north star AK  02090  \n",
       "6                  haines AK  02100  \n",
       "7                  juneau AK  02110  \n",
       "8         kenai peninsula AK  02122  \n",
       "9       ketchikan gateway AK  02130  \n",
       "10          kodiak island AK  02150  \n",
       "11     lake and peninsula AK  02164  \n",
       "12      matanuska-susitna AK  02170  \n",
       "13                   nome AK  02180  \n",
       "14            north slope AK  02185  \n",
       "15       northwest arctic AK  02188  \n",
       "16             petersburg AK  02195  \n",
       "17  prince of wales-hyder AK  02198  \n",
       "18                  sitka AK  02220  \n",
       "19                skagway AK  02230  \n",
       "20    southeast fairbanks AK  02240  \n",
       "21         valdez-cordova AK  02261  \n",
       "22               wrangell AK  02275  \n",
       "23                yakutat AK  02282  \n",
       "24          yukon-koyukuk AK  02290  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data = pd.merge(all_counties, county_ids, how='left', on='county_key')\n",
    "\n",
    "all_data[all_data['state'] == 'AK']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2946 entries, 0 to 2945\n",
      "Data columns (total 6 columns):\n",
      "state          2946 non-null object\n",
      "county_orig    2946 non-null object\n",
      "num_grants     2946 non-null int64\n",
      "grant_value    2946 non-null int64\n",
      "county_key     2946 non-null object\n",
      "id             2946 non-null object\n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 161.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check for null values in id column\n",
    "all_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>num_grants</th>\n",
       "      <th>grant_value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>02013</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians east</td>\n",
       "      <td>5</td>\n",
       "      <td>123553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02016</th>\n",
       "      <td>AK</td>\n",
       "      <td>aleutians west</td>\n",
       "      <td>1</td>\n",
       "      <td>52405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02020</th>\n",
       "      <td>AK</td>\n",
       "      <td>anchorage</td>\n",
       "      <td>67</td>\n",
       "      <td>9475507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02050</th>\n",
       "      <td>AK</td>\n",
       "      <td>bethel</td>\n",
       "      <td>3</td>\n",
       "      <td>366133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02070</th>\n",
       "      <td>AK</td>\n",
       "      <td>dillingham</td>\n",
       "      <td>2</td>\n",
       "      <td>96120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      state          county  num_grants  grant_value\n",
       "id                                                  \n",
       "02013    AK  aleutians east           5       123553\n",
       "02016    AK  aleutians west           1        52405\n",
       "02020    AK       anchorage          67      9475507\n",
       "02050    AK          bethel           3       366133\n",
       "02070    AK      dillingham           2        96120"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create simple DataFrame to export to json\n",
    "grants_by_county = all_data.drop(['county_key'], axis=1)\n",
    "grants_by_county.columns = ['state', 'county', 'num_grants', 'grant_value', 'id']\n",
    "grants_by_county.set_index('id', inplace=True)\n",
    "\n",
    "grants_by_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Min county grant value: $443\n",
      "Max county grant value: $58,641,286\n",
      "Total LWCF by county: $3,512,205,989\n"
     ]
    }
   ],
   "source": [
    "print('Min county grant value: ${0:,.0f}'.format(grants_by_county['grant_value'].min()))\n",
    "print('Max county grant value: ${0:,.0f}'.format(grants_by_county['grant_value'].max()))\n",
    "print('Total LWCF by county: ${0:,.0f}'.format(grants_by_county['grant_value'].sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2.946000e+03\n",
       "mean     1.192195e+06\n",
       "std      2.654758e+06\n",
       "min      4.430000e+02\n",
       "25%      1.580910e+05\n",
       "50%      4.056090e+05\n",
       "75%      1.067659e+06\n",
       "max      5.864129e+07\n",
       "Name: grant_value, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grants_by_county['grant_value'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export data to JSON to use to create interactive map\n",
    "# grants_by_county.to_json('./GrantMap/us_grants_by_county.json', orient='index')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}