MakeTables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top Lenders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "topLenders = \"\"\"\n",
    "SELECT year, Table1.lei, respondent_name, SUM(amount) AS \"total\", COUNT(*) AS loans\n",
    "FROM \n",
    "\n",
    "(SELECT \"Activity Year\" AS year, \"Legal Entity Identifier (LEI)\" AS lei, \"Occupancy Type\" AS occupancy,\n",
    "\"Loan Type\" AS loan_type, \"Loan Amount\" AS amount, \"Interest Rate\" AS rate, \"Census Tract\" AS census_tract,\n",
    "Income AS income, \"Rate Spread\" AS rate_spread, \"Total Loan Costs\" AS loan_costs,\n",
    "\"Origination Charges\" AS origination_charge, \"Discount Points\" AS discount_points,\n",
    "\"Lender Credits\" AS lender_credits, \"Loan Term\" AS term, \"Property Value\" AS prop_value,\n",
    "\"Total Units\" AS units\n",
    "FROM MLAR \n",
    "WHERE \"Loan Purpose\" = 1 -- loan was for purchase \n",
    "AND \"Action Taken\" = 1 -- loan was approved\n",
    "AND \"Business or Commercial Purpose\" != 1 -- loan not for business purpose\n",
    "AND \"Reverse Mortgage\" != 1 -- not a reverse mortgage\n",
    "AND \"Open-End Line of Credit\" != 1 -- not open-end line of credit\n",
    "AND \"Total Units\" IN (1, 2, 3, 4) -- not multifamily\n",
    ") AS Table1\n",
    "LEFT JOIN Institutions\n",
    "ON Table1.lei = Institutions.lei\n",
    "\n",
    "GROUP BY year, Table1.lei\n",
    "ORDER BY \"total\" DESC, loans DESC\n",
    "LIMIT 100\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>year</th>\n",
       "      <th>lei</th>\n",
       "      <th>respondent_name</th>\n",
       "      <th>total</th>\n",
       "      <th>loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300HW662MN1WU8550</td>\n",
       "      <td>United Wholesale Mortgage</td>\n",
       "      <td>89984280000</td>\n",
       "      <td>230790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>549300HW662MN1WU8550</td>\n",
       "      <td>United Wholesale Mortgage</td>\n",
       "      <td>85714715000</td>\n",
       "      <td>220379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>549300FGXN1K3HLB1R50</td>\n",
       "      <td>ROCKET MORTGAGE</td>\n",
       "      <td>51829020000</td>\n",
       "      <td>155136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300FGXN1K3HLB1R50</td>\n",
       "      <td>ROCKET MORTGAGE</td>\n",
       "      <td>44795750000</td>\n",
       "      <td>129872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>KB1H1DSPRFMYMCUFXT09</td>\n",
       "      <td>WELLS FARGO BANK NA</td>\n",
       "      <td>40248005000</td>\n",
       "      <td>67945</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year                   lei            respondent_name        total   loans\n",
       "0  2023  549300HW662MN1WU8550  United Wholesale Mortgage  89984280000  230790\n",
       "1  2022  549300HW662MN1WU8550  United Wholesale Mortgage  85714715000  220379\n",
       "2  2022  549300FGXN1K3HLB1R50            ROCKET MORTGAGE  51829020000  155136\n",
       "3  2023  549300FGXN1K3HLB1R50            ROCKET MORTGAGE  44795750000  129872\n",
       "4  2022  KB1H1DSPRFMYMCUFXT09        WELLS FARGO BANK NA  40248005000   67945"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with sql.connect('./cleaned/hmda.db') as connection:\n",
    "    tl = pd.read_sql(topLenders, connection)\n",
    "tl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "byTotal = tl.loc[tl.total.isin(tl.groupby('year').total.nlargest(10)), ['year', 'respondent_name', 'total']].sort_values(\n",
    "    by=['year', 'total'], ascending=[True, False])\n",
    "byTotal['total'] = round(byTotal.total / 1000000000, 2)\n",
    "byTotal['row_filter'] = 'Total (billion $)'\n",
    "byTotal.rename(columns={'year':'Year', 'respondent_name':'Originator', 'total':'Value'}, inplace=True)\n",
    "\n",
    "byLoans = tl.loc[tl.loans.isin(tl.groupby('year').loans.nlargest(10)), ['year', 'respondent_name', 'loans']].sort_values(\n",
    "    by=['year', 'loans'], ascending=[True, False])\n",
    "byLoans['loans'] = round(byLoans.loans / 1000, 2)\n",
    "byLoans['row_filter'] = 'Loans (thousands)'\n",
    "byLoans.rename(columns={'year':'Year', 'respondent_name':'Originator', 'loans':'Value'}, inplace=True)\n",
    "\n",
    "new_name = {\n",
    "    'Bank of America NA':'Bank of America',\n",
    "    'CALIBER HOME LOANS, INC.':'Caliber',\n",
    "    'CROSSCOUNTRY MORTGAGE, LLC':'CrossCountry',\n",
    "    'DHI Mortgage Company Limited':'DHI',\n",
    "    'FAIRWAY INDEPENDENT MORT CORP':'Fairway',\n",
    "    'GUARANTEED RATE, INC':'Guaranteed Rate',\n",
    "    'JPMorgan Chase Bank, NA':'JPMorgan Chase',\n",
    "    'LOANDEPOT.COM, LLC':'LoanDepot',\n",
    "    'Lennar Mortgage, LLC':'Lennar',\n",
    "    'MOVEMENT MORTGAGE, LLC':'Movement Mortgage',\n",
    "    'Mortgage Research Center':'Mortgage Research Center',\n",
    "    'ROCKET MORTGAGE':'Rocket',\n",
    "    'US BANK, N.A.':'US Bank',\n",
    "    'United Wholesale Mortgage':'UWM',\n",
    "    'WELLS FARGO BANK NA':'Wells Fargo'\n",
    "}\n",
    "\n",
    "tl = pd.concat([byTotal, byLoans], ignore_index=True)\n",
    "tl['Originator'] = [new_name[x] for x in tl.Originator]\n",
    "tl.to_csv('./Flourish/TopLenders.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### County-level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthlyPayment = \"\"\"SELECT \"Activity Year\" AS year, SUBSTR(\"Census Tract\",1,2) AS state, SUBSTR(\"Census Tract\",3,3) AS county, \n",
    "\"Loan Amount\" AS amount, \"Interest Rate\" AS rate, Income/12 AS monthly_income \n",
    "FROM MLAR \n",
    "WHERE \"Loan Purpose\" = 1 -- loan was for purhchase \n",
    "AND \"Action Taken\" = 1 -- loan was approved\n",
    "AND \"Business or Commercial Purpose\" != 1 -- loan not for business purpose\n",
    "AND \"Reverse Mortgage\" != 1 -- not a reverse mortgage\n",
    "AND \"Open-End Line of Credit\" != 1 -- not open-end line of credit\n",
    "AND \"Total Units\" IN (1, 2, 3, 4) -- not multifamily\n",
    "--special\n",
    "AND state NOTNULL AND county NOTNULL \n",
    "AND \"Loan Term\" = 360\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>amount</th>\n",
       "      <th>rate</th>\n",
       "      <th>monthly_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>29</td>\n",
       "      <td>071</td>\n",
       "      <td>285000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>55</td>\n",
       "      <td>025</td>\n",
       "      <td>605000</td>\n",
       "      <td>4.875</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>55</td>\n",
       "      <td>111</td>\n",
       "      <td>345000</td>\n",
       "      <td>5.875</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>011</td>\n",
       "      <td>165000</td>\n",
       "      <td>8.625</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>003</td>\n",
       "      <td>1125000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year state county   amount   rate  monthly_income\n",
       "0  2022    29    071   285000  7.000             9.0\n",
       "1  2022    55    025   605000  4.875            10.0\n",
       "2  2022    55    111   345000  5.875            10.0\n",
       "3  2022    12    011   165000  8.625             6.0\n",
       "4  2022    01    003  1125000  5.000            27.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with sql.connect('./cleaned/hmda.db') as connection:\n",
    "    mp = pd.read_sql(monthlyPayment, connection)\n",
    "mp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep only counties with at least 100 originations in both years\n",
    "mp['state_county'] = mp.state + mp.county\n",
    "counts = mp.groupby(['year','state_county']).amount.count().reset_index()\n",
    "state_counties = counts.loc[counts.amount>=100, 'state_county']\n",
    "state_counties = pd.DataFrame(state_counties.value_counts())\n",
    "\n",
    "mp = mp.loc[mp.state_county.isin(state_counties.loc[state_counties['count']==2].index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "mp['monthly_payment'] = (mp.rate/100/12*mp.amount)/(1 - ((1 + (mp.rate/100/12)) ** (-360)))\n",
    "mp['perc_income'] = mp.monthly_payment / (mp.monthly_income * 1000) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Year</th>\n",
       "      <th>Loans</th>\n",
       "      <th>Median Rate</th>\n",
       "      <th>Median Monthly Payment</th>\n",
       "      <th>Median % of Income</th>\n",
       "      <th>% of Recipients Paying &gt;28% of Income</th>\n",
       "      <th>state_county</th>\n",
       "      <th>County</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>955</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1315</td>\n",
       "      <td>20.6</td>\n",
       "      <td>21.0</td>\n",
       "      <td>01001</td>\n",
       "      <td>Autauga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>5456</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1508</td>\n",
       "      <td>20.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>507</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1099</td>\n",
       "      <td>20.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>01009</td>\n",
       "      <td>Blount</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022</td>\n",
       "      <td>1083</td>\n",
       "      <td>5.1</td>\n",
       "      <td>889</td>\n",
       "      <td>20.2</td>\n",
       "      <td>21.0</td>\n",
       "      <td>01015</td>\n",
       "      <td>Calhoun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>214</td>\n",
       "      <td>5.2</td>\n",
       "      <td>827</td>\n",
       "      <td>19.2</td>\n",
       "      <td>18.0</td>\n",
       "      <td>01017</td>\n",
       "      <td>Chambers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3975</th>\n",
       "      <td>2023</td>\n",
       "      <td>278</td>\n",
       "      <td>6.2</td>\n",
       "      <td>831</td>\n",
       "      <td>23.1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>72135</td>\n",
       "      <td>Toa Alta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>2023</td>\n",
       "      <td>322</td>\n",
       "      <td>6.2</td>\n",
       "      <td>765</td>\n",
       "      <td>22.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>72137</td>\n",
       "      <td>Toa Baja</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>2023</td>\n",
       "      <td>329</td>\n",
       "      <td>6.5</td>\n",
       "      <td>756</td>\n",
       "      <td>21.8</td>\n",
       "      <td>25.0</td>\n",
       "      <td>72139</td>\n",
       "      <td>Trujillo Alto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3978</th>\n",
       "      <td>2023</td>\n",
       "      <td>135</td>\n",
       "      <td>6.5</td>\n",
       "      <td>739</td>\n",
       "      <td>20.5</td>\n",
       "      <td>25.0</td>\n",
       "      <td>72143</td>\n",
       "      <td>Vega Alta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>2023</td>\n",
       "      <td>178</td>\n",
       "      <td>6.4</td>\n",
       "      <td>676</td>\n",
       "      <td>21.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>72145</td>\n",
       "      <td>Vega Baja</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3980 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Loans  Median Rate  Median Monthly Payment  Median % of Income  \\\n",
       "0     2022    955          5.0                    1315                20.6   \n",
       "1     2022   5456          4.8                    1508                20.0   \n",
       "2     2022    507          5.2                    1099                20.5   \n",
       "3     2022   1083          5.1                     889                20.2   \n",
       "4     2022    214          5.2                     827                19.2   \n",
       "...    ...    ...          ...                     ...                 ...   \n",
       "3975  2023    278          6.2                     831                23.1   \n",
       "3976  2023    322          6.2                     765                22.5   \n",
       "3977  2023    329          6.5                     756                21.8   \n",
       "3978  2023    135          6.5                     739                20.5   \n",
       "3979  2023    178          6.4                     676                21.2   \n",
       "\n",
       "      % of Recipients Paying >28% of Income state_county         County  \n",
       "0                                      21.0        01001        Autauga  \n",
       "1                                      22.0        01003        Baldwin  \n",
       "2                                      24.0        01009         Blount  \n",
       "3                                      21.0        01015        Calhoun  \n",
       "4                                      18.0        01017       Chambers  \n",
       "...                                     ...          ...            ...  \n",
       "3975                                   31.0        72135       Toa Alta  \n",
       "3976                                   30.0        72137       Toa Baja  \n",
       "3977                                   25.0        72139  Trujillo Alto  \n",
       "3978                                   25.0        72143      Vega Alta  \n",
       "3979                                   29.0        72145      Vega Baja  \n",
       "\n",
       "[3980 rows x 8 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mp_grouped = mp.groupby(['year', 'state', 'county']).agg({\n",
    "    'state_county':'count',\n",
    "    'rate':'median',\n",
    "    'monthly_payment':'median',\n",
    "    'perc_income':'median'\n",
    "})\n",
    "mp_grouped['perc_over_28'] = mp.loc[mp.perc_income>28].groupby(['year','state','county']).perc_income.count().div(\n",
    "    mp.groupby(['year', 'state', 'county']).perc_income.count()\n",
    ")\n",
    "mp_grouped = mp_grouped.reset_index().rename(columns={\n",
    "    'year':'Year',\n",
    "    'state_county':'Loans',\n",
    "    'rate':'Median Rate',\n",
    "    'monthly_payment':'Median Monthly Payment',\n",
    "    'perc_income':'Median % of Income',\n",
    "    'perc_over_28':'% of Recipients Paying >28% of Income'\n",
    "})\n",
    "mp_grouped['state_county'] = mp_grouped.state + mp_grouped.county\n",
    "\n",
    "mp_grouped['% of Recipients Paying >28% of Income'] = round(mp_grouped['% of Recipients Paying >28% of Income'].mul(100))\n",
    "mp_grouped['Median Rate'] = round(mp_grouped['Median Rate'],1)\n",
    "mp_grouped['Median % of Income'] = round(mp_grouped['Median % of Income'],1)\n",
    "mp_grouped['Median Monthly Payment'] = mp_grouped['Median Monthly Payment'].astype('int')\n",
    "\n",
    "# add county names and fips code for geo matching\n",
    "counties = pd.read_excel('./cleaned/CountyNames.xlsx', dtype='str')\n",
    "mp_grouped = mp_grouped.merge(counties, on='state_county', how='left')\n",
    "mp_grouped.rename(columns={'NAME':'County'}, inplace=True)\n",
    "mp_grouped.drop(columns=['state','county', 'STATEFP', 'COUNTYFP'], inplace=True)\n",
    "mp_grouped"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>Loans</th>\n",
       "      <th>Median Rate</th>\n",
       "      <th>Median Monthly Payment</th>\n",
       "      <th>Median % of Income</th>\n",
       "      <th>% of Recipients Paying &gt;28% of Income</th>\n",
       "      <th>state_county</th>\n",
       "      <th>County</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1993</th>\n",
       "      <td>2023</td>\n",
       "      <td>837</td>\n",
       "      <td>6.8</td>\n",
       "      <td>1135</td>\n",
       "      <td>22.7</td>\n",
       "      <td>30.0</td>\n",
       "      <td>01015</td>\n",
       "      <td>Calhoun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>2023</td>\n",
       "      <td>328</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1295</td>\n",
       "      <td>22.9</td>\n",
       "      <td>30.0</td>\n",
       "      <td>01021</td>\n",
       "      <td>Chilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>2023</td>\n",
       "      <td>877</td>\n",
       "      <td>6.8</td>\n",
       "      <td>1589</td>\n",
       "      <td>22.7</td>\n",
       "      <td>29.0</td>\n",
       "      <td>01051</td>\n",
       "      <td>Elmore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>2023</td>\n",
       "      <td>783</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1164</td>\n",
       "      <td>23.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>01055</td>\n",
       "      <td>Etowah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>2023</td>\n",
       "      <td>308</td>\n",
       "      <td>6.8</td>\n",
       "      <td>1242</td>\n",
       "      <td>22.1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>01071</td>\n",
       "      <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3969</th>\n",
       "      <td>2023</td>\n",
       "      <td>176</td>\n",
       "      <td>6.2</td>\n",
       "      <td>846</td>\n",
       "      <td>22.3</td>\n",
       "      <td>29.0</td>\n",
       "      <td>72085</td>\n",
       "      <td>Las Piedras</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3972</th>\n",
       "      <td>2023</td>\n",
       "      <td>377</td>\n",
       "      <td>6.5</td>\n",
       "      <td>616</td>\n",
       "      <td>21.6</td>\n",
       "      <td>32.0</td>\n",
       "      <td>72113</td>\n",
       "      <td>Ponce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3975</th>\n",
       "      <td>2023</td>\n",
       "      <td>278</td>\n",
       "      <td>6.2</td>\n",
       "      <td>831</td>\n",
       "      <td>23.1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>72135</td>\n",
       "      <td>Toa Alta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>2023</td>\n",
       "      <td>322</td>\n",
       "      <td>6.2</td>\n",
       "      <td>765</td>\n",
       "      <td>22.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>72137</td>\n",
       "      <td>Toa Baja</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>2023</td>\n",
       "      <td>178</td>\n",
       "      <td>6.4</td>\n",
       "      <td>676</td>\n",
       "      <td>21.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>72145</td>\n",
       "      <td>Vega Baja</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>809 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Loans  Median Rate  Median Monthly Payment  Median % of Income  \\\n",
       "1993  2023    837          6.8                    1135                22.7   \n",
       "1996  2023    328          6.6                    1295                22.9   \n",
       "2003  2023    877          6.8                    1589                22.7   \n",
       "2005  2023    783          6.6                    1164                23.0   \n",
       "2010  2023    308          6.8                    1242                22.1   \n",
       "...    ...    ...          ...                     ...                 ...   \n",
       "3969  2023    176          6.2                     846                22.3   \n",
       "3972  2023    377          6.5                     616                21.6   \n",
       "3975  2023    278          6.2                     831                23.1   \n",
       "3976  2023    322          6.2                     765                22.5   \n",
       "3979  2023    178          6.4                     676                21.2   \n",
       "\n",
       "      % of Recipients Paying >28% of Income state_county       County  \n",
       "1993                                   30.0        01015      Calhoun  \n",
       "1996                                   30.0        01021      Chilton  \n",
       "2003                                   29.0        01051       Elmore  \n",
       "2005                                   30.0        01055       Etowah  \n",
       "2010                                   30.0        01071      Jackson  \n",
       "...                                     ...          ...          ...  \n",
       "3969                                   29.0        72085  Las Piedras  \n",
       "3972                                   32.0        72113        Ponce  \n",
       "3975                                   31.0        72135     Toa Alta  \n",
       "3976                                   30.0        72137     Toa Baja  \n",
       "3979                                   29.0        72145    Vega Baja  \n",
       "\n",
       "[809 rows x 8 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mp_grouped.loc[(mp_grouped.Year==2023)&(mp_grouped['% of Recipients Paying >28% of Income']>28)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "mp_grouped.loc[(mp_grouped.Year==2023)&(mp_grouped.Loans>=100)].to_excel('./Flourish/2023_CountyLevel.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fell\n",
       "True     1908\n",
       "False      82\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_change = mp_grouped[['Year', 'Loans', 'state_county']].pivot(index='state_county', columns='Year', values='Loans')\n",
    "loans_change['change'] = loans_change[2023] - loans_change[2022]\n",
    "loans_change['fell'] = [x < 0 for x in loans_change['change']]\n",
    "loans_change.fell.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rose\n",
       "True    1990\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_rose = mp_grouped[['Year', 'Median Rate', 'state_county']].pivot(index='state_county', columns='Year', values='Median Rate')\n",
    "rate_rose['change'] = rate_rose[2023] - rate_rose[2022]\n",
    "rate_rose['rose'] = [x > 0 for x in rate_rose['change']]\n",
    "rate_rose.rose.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rose\n",
       "True     1887\n",
       "False     103\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unaffordable_rose = mp_grouped[['Year', '% of Recipients Paying >28% of Income', 'state_county']].pivot(index='state_county', columns='Year', values='% of Recipients Paying >28% of Income')\n",
    "unaffordable_rose['change'] = unaffordable_rose[2023] - unaffordable_rose[2022]\n",
    "unaffordable_rose['rose'] = [x > 0 for x in unaffordable_rose['change']]\n",
    "unaffordable_rose.rose.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overall change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "statsByYear = \"\"\"\n",
    "SELECT \"Activity Year\" AS year, \n",
    "\"Loan Amount\" AS amount, \"Interest Rate\" AS rate, Income/12*1000 AS monthly_income \n",
    "FROM MLAR \n",
    "WHERE \"Loan Purpose\" = 1 -- loan was for purchase \n",
    "AND \"Action Taken\" = 1 -- loan was approved\n",
    "AND \"Business or Commercial Purpose\" != 1 -- loan not for business purpose\n",
    "AND \"Reverse Mortgage\" != 1 -- not a reverse mortgage\n",
    "AND \"Open-End Line of Credit\" != 1 -- not open-end line of credit\n",
    "AND \"Total Units\" IN (1, 2, 3, 4) -- not multifamily\n",
    "--special\n",
    "AND \"Loan Term\" = 360\n",
    "\"\"\""
   ]
  },
  {
   "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>year</th>\n",
       "      <th>amount</th>\n",
       "      <th>rate</th>\n",
       "      <th>monthly_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>285000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>9000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>605000</td>\n",
       "      <td>4.875</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>345000</td>\n",
       "      <td>5.875</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022</td>\n",
       "      <td>165000</td>\n",
       "      <td>8.625</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>1125000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>27000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year   amount   rate  monthly_income\n",
       "0  2022   285000  7.000          9000.0\n",
       "1  2022   605000  4.875         10000.0\n",
       "2  2022   345000  5.875         10000.0\n",
       "3  2022   165000  8.625          6000.0\n",
       "4  2022  1125000  5.000         27000.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with sql.connect('./cleaned/hmda.db') as connection:\n",
    "    byYear = pd.read_sql(statsByYear, connection)\n",
    "byYear.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>rate</th>\n",
       "      <th>monthly_payment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>4.990</td>\n",
       "      <td>1672.605613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023</th>\n",
       "      <td>6.625</td>\n",
       "      <td>1981.651507</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rate  monthly_payment\n",
       "year                        \n",
       "2022  4.990      1672.605613\n",
       "2023  6.625      1981.651507"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "byYear['monthly_payment'] = (byYear.rate/100/12*byYear.amount)/(1 - ((1 + (byYear.rate/100/12)) ** (-360)))\n",
    "byYear['perc_income'] = byYear.monthly_payment / (byYear.monthly_income) * 100\n",
    "\n",
    "byYear[['year', 'rate', 'monthly_payment']].groupby('year').median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "rates = byYear.dropna(subset=['rate'])[['year', 'rate']]\n",
    "rates = rates.loc[rates.rate>0]\n",
    "rates.loc[rates.rate>50, 'rate'] = rates.loc[rates.rate>50, 'rate'] / 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = list(range(1,60))\n",
    "bins = [float(x)/4 for x in bins]\n",
    "\n",
    "rates['bin'] = pd.cut(rates.rate, bins=bins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "rates.groupby(['year', 'bin']).count().reset_index().to_csv('./Flourish/RateDistribution.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "byYear.dropna(how='any', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "share_income = byYear.copy()[['year', 'perc_income']]\n",
    "share_income['bin'] = pd.cut(share_income.perc_income, list(range(0, 100, 5)))\n",
    "share_income = share_income.groupby(['year', 'bin']).count().reset_index()\n",
    "share_income['bin'] = [str(x).split(', ')[1].rstrip(']') for x in share_income['bin']]\n",
    "share_income.to_csv('./Flourish/PercIncomeDistribution.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "notOnly30 = \"\"\"\n",
    "SELECT \"Activity Year\" AS year, \n",
    "\"Loan Amount\" AS amount, \"Interest Rate\" AS rate, Income/12*1000 AS monthly_income \n",
    "FROM MLAR \n",
    "WHERE \"Loan Purpose\" = 1 -- loan was for purchase \n",
    "AND \"Action Taken\" = 1 -- loan was approved\n",
    "AND \"Business or Commercial Purpose\" != 1 -- loan not for business purpose\n",
    "AND \"Reverse Mortgage\" != 1 -- not a reverse mortgage\n",
    "AND \"Open-End Line of Credit\" != 1 -- not open-end line of credit\n",
    "AND \"Total Units\" IN (1, 2, 3, 4) -- not multifamily\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>year</th>\n",
       "      <th>amount</th>\n",
       "      <th>rate</th>\n",
       "      <th>monthly_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>285000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>9000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>265000</td>\n",
       "      <td>6.875</td>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>605000</td>\n",
       "      <td>4.875</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022</td>\n",
       "      <td>345000</td>\n",
       "      <td>5.875</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022</td>\n",
       "      <td>165000</td>\n",
       "      <td>8.625</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  amount   rate  monthly_income\n",
       "0  2022  285000  7.000          9000.0\n",
       "1  2022  265000  6.875          5000.0\n",
       "2  2022  605000  4.875         10000.0\n",
       "3  2022  345000  5.875         10000.0\n",
       "4  2022  165000  8.625          6000.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with sql.connect('./cleaned/hmda.db') as connection:\n",
    "    byYearNotOnly30 = pd.read_sql(notOnly30, connection)\n",
    "byYearNotOnly30.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>amount</th>\n",
       "      <th>loans</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>1.48</td>\n",
       "      <td>4028915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023</th>\n",
       "      <td>1.14</td>\n",
       "      <td>3193534</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      amount    loans\n",
       "year                 \n",
       "2022    1.48  4028915\n",
       "2023    1.14  3193534"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summ = byYearNotOnly30[['year', 'amount']].groupby('year').sum().merge(\n",
    "    byYearNotOnly30[['year', 'amount']].groupby('year').count().rename(columns={'amount':'loans'}),\n",
    "    left_index=True, right_index=True\n",
    ")\n",
    "summ['amount'] = round(summ['amount'] / 1000000000000, 2)\n",
    "summ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}