hmda_pennsylvania_2018-2023.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "82747ae5-e9d0-428e-9523-22607599034c",
   "metadata": {},
   "source": [
    "# Mortgage Data From the Home Mortgage Disclosure Act\n",
    "I'm really interested in housing data. The Consumer Financial Production Bureau makes a LOT of that kind of thing available for free, but the datasets are enormous. I'm still figuring out the best way for me to pursue my analysis.<br>\n",
    "<br>\n",
    "But in the meantime, I was able to download loan application records for mortgages in 2023 and do a bit of refining for the analysis I want to conduct.<br><br>\n",
    "According to [its website](https://ffiec.cfpb.gov/data-publication/snapshot-national-loan-level-dataset/2023), The Snapshot National Loan Level Dataset files contain the national HMDA datasets as of May 1, 2024 for all HMDA reporters, as modified by the Bureau to protect applicant and borrower privacy. The snapshot files are available to download in both .csv and pipe delimited text file formats.\n",
    "<br>\n",
    "Use caution when analyzing loan amount and income, which do not have an upper limit and may contain outliers.\n",
    "<br><br>\n",
    "**Source**<br>\n",
    "“HMDA - Home Mortgage Disclosure Act.” 2024. Cfpb.gov. 2024. https://ffiec.cfpb.gov/data-publication/snapshot-national-loan-level-dataset/2023."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73ea474e-8e1b-441d-bdcc-8b85fe8c2ddc",
   "metadata": {},
   "source": [
    "## 2023\n",
    "#### Snapshot (Pennsylvania)\n",
    "I think it's reasonable to do one state at the time. I have some personal interest in Pennsylvania, so I'll start there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b8bb17ff-acb1-4204-be8b-76ce9b62437b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# I probably won't need all of these for this analysis, but this is a comprehensive list of the libraries I ever need\n",
    "\n",
    "import requests\n",
    "from datetime import datetime\n",
    "import duckdb\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import geopandas as gpd\n",
    "import folium\n",
    "import re\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import StandardScaler, OneHotEncoder, MinMaxScaler\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.metrics import classification_report, accuracy_score\n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.metrics import confusion_matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f7209999-cd50-4da0-9262-4a79fd00d8bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(423016, 99)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# From the source url (listed in Cell 1), I downloaded loan application records for 2023.\n",
    "\n",
    "# The .csv is huge, and sometimes my system doesn't like reading it all at once.\n",
    "# A work-around is the attractively named process of chunking.\n",
    "\n",
    "# First create an output list to store filtered chunks\n",
    "filtered_chunks = []\n",
    "\n",
    "# Then define the chunk size. I usually try 10K to start, but you can adjust based on your available memory.\n",
    "chunk_size = 10_000\n",
    "\n",
    "# Read the CSV file in chunks, and like my NFIP project, I usually look at these .csvs by state. I like to start with Pennsylvania.\n",
    "# Here's my for loop to read the .csv in chunks:\n",
    "\n",
    "for chunk in pd.read_csv('2023_public_lar_csv.csv', chunksize = chunk_size, low_memory = False):\n",
    "    \n",
    "    # I don't need to read the entire file, just rows where state_code is 'PA'\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    \n",
    "    # And I append each filtered chunk to the output list we defined up top\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "# After the whole thing is read, concatenate all filtered chunks into a single DataFrame\n",
    "lar23 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "# Optional: Save filtered data to a new CSV file so if I need to go back and redo some cleaning, I can some of the waiting\n",
    "lar23.to_csv('lar23.csv', index = False)\n",
    "\n",
    "# And lastly I'll check the size of this PA-filtered .csv for data-wrangling in Jupyter\n",
    "lar23.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cb1fb4b4-e2d4-4a9c-ab4d-785d4d915e4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['activity_year', 'lei', 'derived_msa_md', 'state_code', 'county_code',\n",
       "       'census_tract', 'conforming_loan_limit', 'derived_loan_product_type',\n",
       "       'derived_dwelling_category', 'derived_ethnicity', 'derived_race',\n",
       "       'derived_sex', 'action_taken', 'purchaser_type', 'preapproval',\n",
       "       'loan_type', 'loan_purpose', 'lien_status', 'reverse_mortgage',\n",
       "       'open_end_line_of_credit', 'business_or_commercial_purpose',\n",
       "       'loan_amount', 'combined_loan_to_value_ratio', 'interest_rate',\n",
       "       'rate_spread', 'hoepa_status', 'total_loan_costs',\n",
       "       'total_points_and_fees', 'origination_charges', 'discount_points',\n",
       "       'lender_credits', 'loan_term', 'prepayment_penalty_term',\n",
       "       'intro_rate_period', 'negative_amortization', 'interest_only_payment',\n",
       "       'balloon_payment', 'other_nonamortizing_features', 'property_value',\n",
       "       'construction_method', 'occupancy_type',\n",
       "       'manufactured_home_secured_property_type',\n",
       "       'manufactured_home_land_property_interest', 'total_units',\n",
       "       'multifamily_affordable_units', 'income', 'debt_to_income_ratio',\n",
       "       'applicant_credit_score_type', 'co_applicant_credit_score_type',\n",
       "       'applicant_ethnicity_1', 'applicant_ethnicity_2',\n",
       "       'applicant_ethnicity_3', 'applicant_ethnicity_4',\n",
       "       'applicant_ethnicity_5', 'co_applicant_ethnicity_1',\n",
       "       'co_applicant_ethnicity_2', 'co_applicant_ethnicity_3',\n",
       "       'co_applicant_ethnicity_4', 'co_applicant_ethnicity_5',\n",
       "       'applicant_ethnicity_observed', 'co_applicant_ethnicity_observed',\n",
       "       'applicant_race_1', 'applicant_race_2', 'applicant_race_3',\n",
       "       'applicant_race_4', 'applicant_race_5', 'co_applicant_race_1',\n",
       "       'co_applicant_race_2', 'co_applicant_race_3', 'co_applicant_race_4',\n",
       "       'co_applicant_race_5', 'applicant_race_observed',\n",
       "       'co_applicant_race_observed', 'applicant_sex', 'co_applicant_sex',\n",
       "       'applicant_sex_observed', 'co_applicant_sex_observed', 'applicant_age',\n",
       "       'co_applicant_age', 'applicant_age_above_62',\n",
       "       'co_applicant_age_above_62', 'submission_of_application',\n",
       "       'initially_payable_to_institution', 'aus_1', 'aus_2', 'aus_3', 'aus_4',\n",
       "       'aus_5', 'denial_reason_1', 'denial_reason_2', 'denial_reason_3',\n",
       "       'denial_reason_4', 'tract_population',\n",
       "       'tract_minority_population_percent',\n",
       "       'ffiec_msa_md_median_family_income', 'tract_to_msa_income_percentage',\n",
       "       'tract_owner_occupied_units', 'tract_one_to_four_family_homes',\n",
       "       'tract_median_age_of_housing_units'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Well, 423K rows isn't so bad.\n",
    "\n",
    "# Let's see what columns I'm working with\n",
    "# I know there will be 99...\n",
    "\n",
    "lar23.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1ca49f44-e206-4605-b21e-2e0bcf2a2e65",
   "metadata": {},
   "outputs": [],
   "source": [
    "# I created a function to focus the data on that's most important to me:\n",
    "# Approved applications only,\n",
    "# For primary residence,\n",
    "# No reverse morgages,\n",
    "# No business or commercial purposes,\n",
    "# No interest-only payments,\n",
    "# Occupancy types no greater than 4 units,\n",
    "# And Pennsylvania county codes only.\n",
    "\n",
    "def filter_lar(df):\n",
    "    filtered_df = df[\n",
    "        (df['action_taken'] == 1) &\n",
    "        (df['loan_purpose'] == 1) &\n",
    "        (df['reverse_mortgage'].isin([2, 1111])) &\n",
    "        (df['business_or_commercial_purpose'].isin([2, 1111])) &\n",
    "        (df['interest_only_payment'].isin([2, 1111])) &\n",
    "        (df['occupancy_type'] == 1) &\n",
    "        (df['total_units'].isin([1, 2, 3, 4])) &\n",
    "        (df['county_code'].astype(str).str.startswith('42'))\n",
    "    ]\n",
    "    \n",
    "    # Then I dropped the columns I don't need. (Hopefully I'll ditch more as I go forward, but this helps for now.)\n",
    "    columns_to_drop = [\n",
    "        'derived_msa_md', 'state_code', 'census_tract', 'derived_dwelling_category', \n",
    "        'conforming_loan_limit', 'action_taken', 'loan_purpose', 'lien_status', \n",
    "        'reverse_mortgage', 'hoepa_status', 'negative_amortization', 'interest_only_payment', \n",
    "        'occupancy_type', 'submission_of_application', 'initially_payable_to_institution',\n",
    "        'denial_reason_1', 'denial_reason_2', 'denial_reason_3', 'denial_reason_4'\n",
    "    ]\n",
    "    \n",
    "    filtered_df = filtered_df.drop(columns = columns_to_drop, errors = 'ignore')\n",
    "    \n",
    "    return filtered_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "da22f694-f876-46b0-980c-e8d4fc4b3bb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47759, 80)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now I apply the function to lar23\n",
    "lar23_filtered = filter_lar(lar23)\n",
    "\n",
    "# And see what kind of shape I'm looking at now\n",
    "lar23_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "60ad90cd-af52-4577-9f0b-fc44c530ba3d",
   "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>activity_year</th>\n",
       "      <th>lei</th>\n",
       "      <th>county_code</th>\n",
       "      <th>derived_loan_product_type</th>\n",
       "      <th>derived_ethnicity</th>\n",
       "      <th>derived_race</th>\n",
       "      <th>derived_sex</th>\n",
       "      <th>purchaser_type</th>\n",
       "      <th>preapproval</th>\n",
       "      <th>loan_type</th>\n",
       "      <th>...</th>\n",
       "      <th>aus_3</th>\n",
       "      <th>aus_4</th>\n",
       "      <th>aus_5</th>\n",
       "      <th>tract_population</th>\n",
       "      <th>tract_minority_population_percent</th>\n",
       "      <th>ffiec_msa_md_median_family_income</th>\n",
       "      <th>tract_to_msa_income_percentage</th>\n",
       "      <th>tract_owner_occupied_units</th>\n",
       "      <th>tract_one_to_four_family_homes</th>\n",
       "      <th>tract_median_age_of_housing_units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1255</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42125.0</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5319</td>\n",
       "      <td>7.56</td>\n",
       "      <td>101900</td>\n",
       "      <td>127.88</td>\n",
       "      <td>1698</td>\n",
       "      <td>1929</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1261</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42027.0</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2983</td>\n",
       "      <td>18.44</td>\n",
       "      <td>115500</td>\n",
       "      <td>119.02</td>\n",
       "      <td>967</td>\n",
       "      <td>1393</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1266</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42043.0</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>Race Not Available</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6484</td>\n",
       "      <td>40.72</td>\n",
       "      <td>102700</td>\n",
       "      <td>106.86</td>\n",
       "      <td>1388</td>\n",
       "      <td>1929</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42087.0</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2372</td>\n",
       "      <td>8.26</td>\n",
       "      <td>77800</td>\n",
       "      <td>53.55</td>\n",
       "      <td>613</td>\n",
       "      <td>959</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1273</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42061.0</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4624</td>\n",
       "      <td>5.95</td>\n",
       "      <td>77800</td>\n",
       "      <td>90.06</td>\n",
       "      <td>1508</td>\n",
       "      <td>2212</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      activity_year                   lei  county_code  \\\n",
       "1255           2023  549300RN01LBYR8ZVX74      42125.0   \n",
       "1261           2023  549300RN01LBYR8ZVX74      42027.0   \n",
       "1266           2023  549300RN01LBYR8ZVX74      42043.0   \n",
       "1269           2023  549300RN01LBYR8ZVX74      42087.0   \n",
       "1273           2023  549300RN01LBYR8ZVX74      42061.0   \n",
       "\n",
       "     derived_loan_product_type       derived_ethnicity        derived_race  \\\n",
       "1255   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1261   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1266            FHA:First Lien      Hispanic or Latino  Race Not Available   \n",
       "1269   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1273   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "\n",
       "     derived_sex  purchaser_type  preapproval  loan_type  ...  aus_3  aus_4  \\\n",
       "1255      Female               0            2          1  ...    NaN    NaN   \n",
       "1261       Joint              71            2          1  ...    NaN    NaN   \n",
       "1266      Female              71            2          2  ...    NaN    NaN   \n",
       "1269       Joint               0            2          1  ...    NaN    NaN   \n",
       "1273      Female               0            2          1  ...    NaN    NaN   \n",
       "\n",
       "      aus_5 tract_population tract_minority_population_percent  \\\n",
       "1255    NaN             5319                              7.56   \n",
       "1261    NaN             2983                             18.44   \n",
       "1266    NaN             6484                             40.72   \n",
       "1269    NaN             2372                              8.26   \n",
       "1273    NaN             4624                              5.95   \n",
       "\n",
       "     ffiec_msa_md_median_family_income tract_to_msa_income_percentage  \\\n",
       "1255                            101900                         127.88   \n",
       "1261                            115500                         119.02   \n",
       "1266                            102700                         106.86   \n",
       "1269                             77800                          53.55   \n",
       "1273                             77800                          90.06   \n",
       "\n",
       "     tract_owner_occupied_units tract_one_to_four_family_homes  \\\n",
       "1255                       1698                           1929   \n",
       "1261                        967                           1393   \n",
       "1266                       1388                           1929   \n",
       "1269                        613                            959   \n",
       "1273                       1508                           2212   \n",
       "\n",
       "     tract_median_age_of_housing_units  \n",
       "1255                                43  \n",
       "1261                                51  \n",
       "1266                                42  \n",
       "1269                                73  \n",
       "1273                                51  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Whoa. That eliminated almost 90% of my rows! I almost want to go back and expand my dataset...\n",
    "# But no. This is just 2023 PA data, and it's still nearly 50K datapoints. Ideally I'd like to concatenate more years and/or states,\n",
    "# so I'll stick to these narrowing parameters.\n",
    "\n",
    "# Let's preview the dataframe\n",
    "lar23_filtered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c72e0108-8963-4475-82c8-fbcfdc192640",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Right away I see something I want to fix, but first I gotta shorten this variable name; I'll be using it a lot.\n",
    "\n",
    "lar = lar23_filtered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1be6eefc-89e0-4c3d-961a-fc3b62e42d0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Great. Now it looks like the county_codes are floats. Am I right?\n",
    "\n",
    "lar.county_code.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "efd10d7a-2239-43da-94f9-85de7d1f0e6f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Yep. I wish FIPS codes always came to us as strings; I've never had a case where I needed them to be a numeric value.\n",
    "# So first I convert the numeric value to strings.\n",
    "\n",
    "lar['county_code'] = lar['county_code'].astype(int).astype(str)\n",
    "\n",
    "# And check that I did it properly\n",
    "lar.county_code.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fca9b915-7875-4f50-890c-bd8687e40a7a",
   "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>activity_year</th>\n",
       "      <th>lei</th>\n",
       "      <th>county_code</th>\n",
       "      <th>derived_loan_product_type</th>\n",
       "      <th>derived_ethnicity</th>\n",
       "      <th>derived_race</th>\n",
       "      <th>derived_sex</th>\n",
       "      <th>purchaser_type</th>\n",
       "      <th>preapproval</th>\n",
       "      <th>loan_type</th>\n",
       "      <th>...</th>\n",
       "      <th>aus_3</th>\n",
       "      <th>aus_4</th>\n",
       "      <th>aus_5</th>\n",
       "      <th>tract_population</th>\n",
       "      <th>tract_minority_population_percent</th>\n",
       "      <th>ffiec_msa_md_median_family_income</th>\n",
       "      <th>tract_to_msa_income_percentage</th>\n",
       "      <th>tract_owner_occupied_units</th>\n",
       "      <th>tract_one_to_four_family_homes</th>\n",
       "      <th>tract_median_age_of_housing_units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1255</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42125</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5319</td>\n",
       "      <td>7.56</td>\n",
       "      <td>101900</td>\n",
       "      <td>127.88</td>\n",
       "      <td>1698</td>\n",
       "      <td>1929</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1261</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42027</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2983</td>\n",
       "      <td>18.44</td>\n",
       "      <td>115500</td>\n",
       "      <td>119.02</td>\n",
       "      <td>967</td>\n",
       "      <td>1393</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1266</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42043</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>Race Not Available</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6484</td>\n",
       "      <td>40.72</td>\n",
       "      <td>102700</td>\n",
       "      <td>106.86</td>\n",
       "      <td>1388</td>\n",
       "      <td>1929</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42087</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2372</td>\n",
       "      <td>8.26</td>\n",
       "      <td>77800</td>\n",
       "      <td>53.55</td>\n",
       "      <td>613</td>\n",
       "      <td>959</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1273</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42061</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4624</td>\n",
       "      <td>5.95</td>\n",
       "      <td>77800</td>\n",
       "      <td>90.06</td>\n",
       "      <td>1508</td>\n",
       "      <td>2212</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      activity_year                   lei county_code  \\\n",
       "1255           2023  549300RN01LBYR8ZVX74       42125   \n",
       "1261           2023  549300RN01LBYR8ZVX74       42027   \n",
       "1266           2023  549300RN01LBYR8ZVX74       42043   \n",
       "1269           2023  549300RN01LBYR8ZVX74       42087   \n",
       "1273           2023  549300RN01LBYR8ZVX74       42061   \n",
       "\n",
       "     derived_loan_product_type       derived_ethnicity        derived_race  \\\n",
       "1255   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1261   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1266            FHA:First Lien      Hispanic or Latino  Race Not Available   \n",
       "1269   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "1273   Conventional:First Lien  Not Hispanic or Latino               White   \n",
       "\n",
       "     derived_sex  purchaser_type  preapproval  loan_type  ...  aus_3  aus_4  \\\n",
       "1255      Female               0            2          1  ...    NaN    NaN   \n",
       "1261       Joint              71            2          1  ...    NaN    NaN   \n",
       "1266      Female              71            2          2  ...    NaN    NaN   \n",
       "1269       Joint               0            2          1  ...    NaN    NaN   \n",
       "1273      Female               0            2          1  ...    NaN    NaN   \n",
       "\n",
       "      aus_5 tract_population tract_minority_population_percent  \\\n",
       "1255    NaN             5319                              7.56   \n",
       "1261    NaN             2983                             18.44   \n",
       "1266    NaN             6484                             40.72   \n",
       "1269    NaN             2372                              8.26   \n",
       "1273    NaN             4624                              5.95   \n",
       "\n",
       "     ffiec_msa_md_median_family_income tract_to_msa_income_percentage  \\\n",
       "1255                            101900                         127.88   \n",
       "1261                            115500                         119.02   \n",
       "1266                            102700                         106.86   \n",
       "1269                             77800                          53.55   \n",
       "1273                             77800                          90.06   \n",
       "\n",
       "     tract_owner_occupied_units tract_one_to_four_family_homes  \\\n",
       "1255                       1698                           1929   \n",
       "1261                        967                           1393   \n",
       "1266                       1388                           1929   \n",
       "1269                        613                            959   \n",
       "1273                       1508                           2212   \n",
       "\n",
       "     tract_median_age_of_housing_units  \n",
       "1255                                43  \n",
       "1261                                51  \n",
       "1266                                42  \n",
       "1269                                73  \n",
       "1273                                51  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One thing done. But did this transfer to the main dataset?\n",
    "\n",
    "lar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "29b0f2dd-ae57-4f9a-9c00-2053cf8d1dfa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47759, 80)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# GORGEOUS. What an efficient way to turn my FIPS floats into strings.\n",
    "# For now, I'll save this to a .csv, whether I'm done for now or not. If my computer crashes, I don't want to have to run that humungous 2023 LAR .csv again.\n",
    "# Let's throw the shape down here for posterity first.\n",
    "\n",
    "lar.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "daf8c874-9aca-4650-a5cb-ce1d20587622",
   "metadata": {},
   "outputs": [],
   "source": [
    "# And now preserve this cleaned file as a .csv\n",
    "\n",
    "lar.to_csv('lar_filtered_pa_23.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b93f69ab-747f-4393-80dc-95ecd6fe537a",
   "metadata": {},
   "source": [
    "## 2022 is a bigger file.\n",
    "### Will my chunking `for` loop work to read it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "88e0be46-1942-481b-9a67-1f559de709bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(585665, 99)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_chunks = []\n",
    "\n",
    "chunk_size = 10_000\n",
    "\n",
    "for chunk in pd.read_csv('2022_public_lar_csv.csv', chunksize = chunk_size, low_memory = False):\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "lar22 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "lar22.to_csv('lar22.csv', index = False)\n",
    "\n",
    "lar22.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "56d0fe42-b9f8-4fd2-acd5-fe10e7bd61eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62117, 80)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Holy smokes, it did.\n",
    "# Well, I already know what columns it has (and which I don't need).\n",
    "# How many rows do we think my filter function will eliminate? Will it be 90% again?\n",
    "\n",
    "lar22_filtered = filter_lar(lar22)\n",
    "\n",
    "# And see what kind of shape I'm looking at now\n",
    "lar22_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8581db80-04c7-472c-9294-ffb4f1382763",
   "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>activity_year</th>\n",
       "      <th>lei</th>\n",
       "      <th>county_code</th>\n",
       "      <th>derived_loan_product_type</th>\n",
       "      <th>derived_ethnicity</th>\n",
       "      <th>derived_race</th>\n",
       "      <th>derived_sex</th>\n",
       "      <th>purchaser_type</th>\n",
       "      <th>preapproval</th>\n",
       "      <th>loan_type</th>\n",
       "      <th>...</th>\n",
       "      <th>aus_3</th>\n",
       "      <th>aus_4</th>\n",
       "      <th>aus_5</th>\n",
       "      <th>tract_population</th>\n",
       "      <th>tract_minority_population_percent</th>\n",
       "      <th>ffiec_msa_md_median_family_income</th>\n",
       "      <th>tract_to_msa_income_percentage</th>\n",
       "      <th>tract_owner_occupied_units</th>\n",
       "      <th>tract_one_to_four_family_homes</th>\n",
       "      <th>tract_median_age_of_housing_units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>RVDPPPGHCGZ40J4VQ731</td>\n",
       "      <td>42101</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Female</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4826</td>\n",
       "      <td>94.76</td>\n",
       "      <td>75000</td>\n",
       "      <td>25.0</td>\n",
       "      <td>507</td>\n",
       "      <td>2330</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022</td>\n",
       "      <td>RVDPPPGHCGZ40J4VQ731</td>\n",
       "      <td>42045</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3417</td>\n",
       "      <td>52.53</td>\n",
       "      <td>75000</td>\n",
       "      <td>133.0</td>\n",
       "      <td>886</td>\n",
       "      <td>1124</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022</td>\n",
       "      <td>RVDPPPGHCGZ40J4VQ731</td>\n",
       "      <td>42029</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4956</td>\n",
       "      <td>12.53</td>\n",
       "      <td>131400</td>\n",
       "      <td>165.0</td>\n",
       "      <td>1852</td>\n",
       "      <td>2000</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022</td>\n",
       "      <td>RVDPPPGHCGZ40J4VQ731</td>\n",
       "      <td>42133</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Ethnicity Not Available</td>\n",
       "      <td>Race Not Available</td>\n",
       "      <td>Female</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3295</td>\n",
       "      <td>14.78</td>\n",
       "      <td>93300</td>\n",
       "      <td>97.0</td>\n",
       "      <td>1018</td>\n",
       "      <td>1229</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2022</td>\n",
       "      <td>RVDPPPGHCGZ40J4VQ731</td>\n",
       "      <td>42069</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Male</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4163</td>\n",
       "      <td>50.47</td>\n",
       "      <td>74100</td>\n",
       "      <td>47.0</td>\n",
       "      <td>735</td>\n",
       "      <td>1341</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    activity_year                   lei county_code derived_loan_product_type  \\\n",
       "1            2022  RVDPPPGHCGZ40J4VQ731       42101   Conventional:First Lien   \n",
       "7            2022  RVDPPPGHCGZ40J4VQ731       42045   Conventional:First Lien   \n",
       "8            2022  RVDPPPGHCGZ40J4VQ731       42029   Conventional:First Lien   \n",
       "9            2022  RVDPPPGHCGZ40J4VQ731       42133   Conventional:First Lien   \n",
       "20           2022  RVDPPPGHCGZ40J4VQ731       42069   Conventional:First Lien   \n",
       "\n",
       "          derived_ethnicity        derived_race derived_sex  purchaser_type  \\\n",
       "1    Not Hispanic or Latino               Asian      Female               1   \n",
       "7    Not Hispanic or Latino               White       Joint               3   \n",
       "8    Not Hispanic or Latino               White       Joint               3   \n",
       "9   Ethnicity Not Available  Race Not Available      Female               3   \n",
       "20   Not Hispanic or Latino               Asian        Male               1   \n",
       "\n",
       "    preapproval  loan_type  ...  aus_3  aus_4  aus_5 tract_population  \\\n",
       "1             2          1  ...    NaN    NaN    NaN             4826   \n",
       "7             2          1  ...    NaN    NaN    NaN             3417   \n",
       "8             2          1  ...    NaN    NaN    NaN             4956   \n",
       "9             2          1  ...    NaN    NaN    NaN             3295   \n",
       "20            2          1  ...    NaN    NaN    NaN             4163   \n",
       "\n",
       "   tract_minority_population_percent ffiec_msa_md_median_family_income  \\\n",
       "1                              94.76                             75000   \n",
       "7                              52.53                             75000   \n",
       "8                              12.53                            131400   \n",
       "9                              14.78                             93300   \n",
       "20                             50.47                             74100   \n",
       "\n",
       "   tract_to_msa_income_percentage tract_owner_occupied_units  \\\n",
       "1                            25.0                        507   \n",
       "7                           133.0                        886   \n",
       "8                           165.0                       1852   \n",
       "9                            97.0                       1018   \n",
       "20                           47.0                        735   \n",
       "\n",
       "   tract_one_to_four_family_homes tract_median_age_of_housing_units  \n",
       "1                            2330                                67  \n",
       "7                            1124                                75  \n",
       "8                            2000                                30  \n",
       "9                            1229                                17  \n",
       "20                           1341                                68  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Nice.\n",
    "\n",
    "# OK, let's shorten that variable name and repeat the cleaning we did from 2023.\n",
    "# I'll keep a 22 on this one since I don't want to risk losing my 2023 data in lar.\n",
    "# In fact, let's rename lar to lar23 to keep things organized and confusion-free. (Or at least confusion-minimal.)\n",
    "\n",
    "lar23 = lar\n",
    "lar22 = lar22_filtered\n",
    "\n",
    "# Now we can take care of that pesky county_code column\n",
    "\n",
    "lar22['county_code'] = lar22['county_code'].astype(int).astype(str)\n",
    "lar22.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7b835fbf-36e8-486c-b111-850d5e576cd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62117, 80)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Beautiful. Throw the shape down here...\n",
    "\n",
    "lar22.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "69b196ed-9c0e-43b2-bcd0-7fc53292e054",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Good to see it's the same as right after we ran the filter function.\n",
    "# So my converting the county_codes from floats to strings didn't mess with the shape.\n",
    "\n",
    "# Let's .csv this baby!\n",
    "\n",
    "lar22.to_csv('lar_filtered_pa_22.csv', index = False)\n",
    "\n",
    "# Do I dare try 2021?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb89e0ae-bb6c-4f61-abbc-a8c566cc1233",
   "metadata": {},
   "source": [
    "## 2021\n",
    "### I guess I do.\n",
    "##### One kinda bummer is that HMDA has more detailed data for 2022 and 2021, the one-year files that incorporate adjustments to the 2021 national HMDA datasets, submitted as of May 1, 2023. For 2021, that includes all updates to the Loan Application Register (LAR) and Transmittal Sheet (TS) made in the 12 months following the 2021 reporting deadline of March 1, 2022.\n",
    "##### But the 2022 Snapshot was still bigger than the 2023 one. And I've had enough heartburn trying to read big files from CFPB before.\n",
    "##### I'll stick to Snapshot files. (For now.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f9a0d427-1653-4c77-a250-e3763c0e19cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(861415, 99)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Heads up! CFPB changed the naming convention here. The 2021 Public LAR Snapshot downloads as 2021_public_lar.csv \n",
    "# whereas 2022 and 2023 had \"_csv\" at the end of the file name\n",
    "# I missed that the first time and got an error code due to the file name not existing in my directory\n",
    "\n",
    "filtered_chunks = []\n",
    "\n",
    "chunk_size = 10_000\n",
    "\n",
    "for chunk in pd.read_csv('2021_public_lar.csv', chunksize = chunk_size, low_memory = False):\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "lar21 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "lar21.to_csv('lar21.csv', index = False)\n",
    "\n",
    "lar21.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c5ff9c01-49e9-4d59-98d4-912dbb98742e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(82091, 80)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 860K, we're getting close to 1 million applications per year. Let's filter that baby.\n",
    "\n",
    "lar21_filtered = filter_lar(lar21)\n",
    "\n",
    "lar21_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "3bb52105-ca87-4c0e-8999-17499a4bda5b",
   "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>activity_year</th>\n",
       "      <th>lei</th>\n",
       "      <th>county_code</th>\n",
       "      <th>derived_loan_product_type</th>\n",
       "      <th>derived_ethnicity</th>\n",
       "      <th>derived_race</th>\n",
       "      <th>derived_sex</th>\n",
       "      <th>purchaser_type</th>\n",
       "      <th>preapproval</th>\n",
       "      <th>loan_type</th>\n",
       "      <th>...</th>\n",
       "      <th>aus_3</th>\n",
       "      <th>aus_4</th>\n",
       "      <th>aus_5</th>\n",
       "      <th>tract_population</th>\n",
       "      <th>tract_minority_population_percent</th>\n",
       "      <th>ffiec_msa_md_median_family_income</th>\n",
       "      <th>tract_to_msa_income_percentage</th>\n",
       "      <th>tract_owner_occupied_units</th>\n",
       "      <th>tract_one_to_four_family_homes</th>\n",
       "      <th>tract_median_age_of_housing_units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9586</th>\n",
       "      <td>2021</td>\n",
       "      <td>5493008NWHQT1R22C024</td>\n",
       "      <td>42129</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1990</td>\n",
       "      <td>9.20</td>\n",
       "      <td>84200</td>\n",
       "      <td>84.0</td>\n",
       "      <td>598</td>\n",
       "      <td>919</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9587</th>\n",
       "      <td>2021</td>\n",
       "      <td>5493008NWHQT1R22C024</td>\n",
       "      <td>42101</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Female</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5806</td>\n",
       "      <td>92.83</td>\n",
       "      <td>67500</td>\n",
       "      <td>27.0</td>\n",
       "      <td>629</td>\n",
       "      <td>1945</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9592</th>\n",
       "      <td>2021</td>\n",
       "      <td>5493008NWHQT1R22C024</td>\n",
       "      <td>42045</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6468</td>\n",
       "      <td>13.81</td>\n",
       "      <td>67500</td>\n",
       "      <td>144.0</td>\n",
       "      <td>1594</td>\n",
       "      <td>2226</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9593</th>\n",
       "      <td>2021</td>\n",
       "      <td>5493008NWHQT1R22C024</td>\n",
       "      <td>42101</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2912</td>\n",
       "      <td>100.00</td>\n",
       "      <td>67500</td>\n",
       "      <td>58.0</td>\n",
       "      <td>627</td>\n",
       "      <td>1376</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9597</th>\n",
       "      <td>2021</td>\n",
       "      <td>5493008NWHQT1R22C024</td>\n",
       "      <td>42029</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3635</td>\n",
       "      <td>12.27</td>\n",
       "      <td>116300</td>\n",
       "      <td>136.0</td>\n",
       "      <td>1085</td>\n",
       "      <td>1385</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      activity_year                   lei county_code  \\\n",
       "9586           2021  5493008NWHQT1R22C024       42129   \n",
       "9587           2021  5493008NWHQT1R22C024       42101   \n",
       "9592           2021  5493008NWHQT1R22C024       42045   \n",
       "9593           2021  5493008NWHQT1R22C024       42101   \n",
       "9597           2021  5493008NWHQT1R22C024       42029   \n",
       "\n",
       "     derived_loan_product_type       derived_ethnicity  \\\n",
       "9586            FHA:First Lien  Not Hispanic or Latino   \n",
       "9587            FHA:First Lien  Not Hispanic or Latino   \n",
       "9592   Conventional:First Lien  Not Hispanic or Latino   \n",
       "9593            FHA:First Lien  Not Hispanic or Latino   \n",
       "9597   Conventional:First Lien  Not Hispanic or Latino   \n",
       "\n",
       "                   derived_race derived_sex  purchaser_type  preapproval  \\\n",
       "9586                      White      Female              71            2   \n",
       "9587  Black or African American      Female               9            2   \n",
       "9592                      White       Joint              71            2   \n",
       "9593  Black or African American      Female              71            2   \n",
       "9597                      White      Female              71            2   \n",
       "\n",
       "      loan_type  ...  aus_3  aus_4  aus_5 tract_population  \\\n",
       "9586          2  ...    NaN    NaN    NaN             1990   \n",
       "9587          2  ...    NaN    NaN    NaN             5806   \n",
       "9592          1  ...    NaN    NaN    NaN             6468   \n",
       "9593          2  ...    NaN    NaN    NaN             2912   \n",
       "9597          1  ...    NaN    NaN    NaN             3635   \n",
       "\n",
       "     tract_minority_population_percent ffiec_msa_md_median_family_income  \\\n",
       "9586                              9.20                             84200   \n",
       "9587                             92.83                             67500   \n",
       "9592                             13.81                             67500   \n",
       "9593                            100.00                             67500   \n",
       "9597                             12.27                            116300   \n",
       "\n",
       "     tract_to_msa_income_percentage tract_owner_occupied_units  \\\n",
       "9586                           84.0                        598   \n",
       "9587                           27.0                        629   \n",
       "9592                          144.0                       1594   \n",
       "9593                           58.0                        627   \n",
       "9597                          136.0                       1085   \n",
       "\n",
       "     tract_one_to_four_family_homes tract_median_age_of_housing_units  \n",
       "9586                            919                                76  \n",
       "9587                           1945                                76  \n",
       "9592                           2226                                67  \n",
       "9593                           1376                                76  \n",
       "9597                           1385                                32  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Nice, down even more than 90%. These will concatenate nicely.\n",
    "\n",
    "lar21 = lar21_filtered\n",
    "\n",
    "lar21['county_code'] = lar21['county_code'].astype(int).astype(str)\n",
    "lar21.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "e753dcf5-3743-48be-9a9b-3391d7e63201",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar21.to_csv('lar_filtered_pa_21.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2504874-c7cf-4827-a492-30852110a696",
   "metadata": {},
   "source": [
    "## 2020\n",
    "##### For 2020 I actually have to use the One-Year dataset, as Snapshot isn't available.\n",
    "##### Fingers crossed it reads!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "51e7c3ed-f208-4458-aa67-17a47837ee70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(792007, 99)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_chunks = []\n",
    "\n",
    "chunk_size = 10_000\n",
    "\n",
    "for chunk in pd.read_csv('2020_public_lar_one_year.csv', chunksize = chunk_size, low_memory = False):\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "lar20 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "lar20.to_csv('lar20.csv', index = False)\n",
    "\n",
    "lar20.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "70c61b91-6fdf-4cf5-9ca7-927e57be30ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(67237, 80)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Well we're going down in size.\n",
    "\n",
    "lar20_filtered = filter_lar(lar20)\n",
    "\n",
    "lar20_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "7f59bb95-9d02-4b01-84dc-10eb5f0d87fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar20 = lar20_filtered\n",
    "\n",
    "# It occurs to me I could have waited till I concatenated all the years and only had to convert the county_codes to strings once.\n",
    "# I imagine I'll be doing more formatting/cleaning with the eventual concatenated file.\n",
    "# But when I started it with 2023, I didn't know if I'd be able to use more years.\n",
    "# Oh well! It's not too many more keystrokes. And I think I have to stop at 2018; that's when the data storage format changes.\n",
    "# It's totally feasible to fetch pre-2018 data, and with a little attention you can merge it with 2018 and after,\n",
    "# but that takes a little more work than I want to do on data cleaning/wrangling for this current free-time experimenting.\n",
    "# I'd rather dive into some visualizations and analysis. If my findings prompt me to check earlier data, I will.\n",
    "\n",
    "lar20['county_code'] = lar20['county_code'].astype(int).astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4912f456-7fa1-4bcf-86b8-14f519533c6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar20.to_csv('lar_filtered_pa_20.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0d9e423-3611-4579-8ea1-277045b4cfc1",
   "metadata": {},
   "source": [
    "## 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "ab39841e-29c2-4077-9bad-d80aa6f0a3ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(598793, 99)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_chunks = []\n",
    "\n",
    "chunk_size = 10_000\n",
    "\n",
    "for chunk in pd.read_csv('2019_public_lar_one_year.csv', chunksize = chunk_size, low_memory = False):\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "lar19 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "lar19.to_csv('lar19.csv', index = False)\n",
    "\n",
    "lar19.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0ca26fe6-b89e-487f-af02-bad214b3f5c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(55224, 80)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lar19_filtered = filter_lar(lar19)\n",
    "\n",
    "lar19_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "fa5bcde7-822c-46c6-8ab7-70bf0a5c4842",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar19 = lar19_filtered\n",
    "\n",
    "lar19['county_code'] = lar19['county_code'].astype(int).astype(str)\n",
    "\n",
    "lar19.to_csv('lar_filtered_pa_19.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ba309c2-d9c2-4115-be9b-defe2799e777",
   "metadata": {},
   "source": [
    "## 2018\n",
    "##### I'm actually moving into the Three-Year Dataset now. The Three-Year files incorporate adjustments to the 2018 national HMDA datasets, submitted as of December 31, 2021. They include all updates to the Loan Application Register (LAR) and Transmittal Sheet (TS) made in the 36 months following the 2018 reporting deadline of March 1, 2019. Files are available to download in both .csv and pipe delimited text file formats.\n",
    "##### I'll have to check if this one includes a different array of columns than the previous files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "59f0a7c9-dd05-4f0f-a92b-06db1a2ca261",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(548976, 99)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note that this file was saved with an extra underscore. As my downloading, it arrived with this file name: 2018_public__lar_three_year.csv\n",
    "\n",
    "filtered_chunks = []\n",
    "\n",
    "chunk_size = 10_000\n",
    "\n",
    "for chunk in pd.read_csv('2018_public__lar_three_year.csv', chunksize = chunk_size, low_memory = False):\n",
    "    filtered_chunk = chunk[chunk['state_code'] == 'PA']\n",
    "    filtered_chunks.append(filtered_chunk)\n",
    "\n",
    "lar18 = pd.concat(filtered_chunks, ignore_index = True)\n",
    "\n",
    "lar18.to_csv('lar18.csv', index = False)\n",
    "\n",
    "lar18.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "2f44def6-10de-4f40-aa9a-bb079c50eb95",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['activity_year', 'lei', 'derived_msa_md', 'state_code', 'county_code',\n",
       "       'census_tract', 'conforming_loan_limit', 'derived_loan_product_type',\n",
       "       'derived_dwelling_category', 'derived_ethnicity', 'derived_race',\n",
       "       'derived_sex', 'action_taken', 'purchaser_type', 'preapproval',\n",
       "       'loan_type', 'loan_purpose', 'lien_status', 'reverse_mortgage',\n",
       "       'open_end_line_of_credit', 'business_or_commercial_purpose',\n",
       "       'loan_amount', 'combined_loan_to_value_ratio', 'interest_rate',\n",
       "       'rate_spread', 'hoepa_status', 'total_loan_costs',\n",
       "       'total_points_and_fees', 'origination_charges', 'discount_points',\n",
       "       'lender_credits', 'loan_term', 'prepayment_penalty_term',\n",
       "       'intro_rate_period', 'negative_amortization', 'interest_only_payment',\n",
       "       'balloon_payment', 'other_nonamortizing_features', 'property_value',\n",
       "       'construction_method', 'occupancy_type',\n",
       "       'manufactured_home_secured_property_type',\n",
       "       'manufactured_home_land_property_interest', 'total_units',\n",
       "       'multifamily_affordable_units', 'income', 'debt_to_income_ratio',\n",
       "       'applicant_credit_score_type', 'co_applicant_credit_score_type',\n",
       "       'applicant_ethnicity_1', 'applicant_ethnicity_2',\n",
       "       'applicant_ethnicity_3', 'applicant_ethnicity_4',\n",
       "       'applicant_ethnicity_5', 'co_applicant_ethnicity_1',\n",
       "       'co_applicant_ethnicity_2', 'co_applicant_ethnicity_3',\n",
       "       'co_applicant_ethnicity_4', 'co_applicant_ethnicity_5',\n",
       "       'applicant_ethnicity_observed', 'co_applicant_ethnicity_observed',\n",
       "       'applicant_race_1', 'applicant_race_2', 'applicant_race_3',\n",
       "       'applicant_race_4', 'applicant_race_5', 'co_applicant_race_1',\n",
       "       'co_applicant_race_2', 'co_applicant_race_3', 'co_applicant_race_4',\n",
       "       'co_applicant_race_5', 'applicant_race_observed',\n",
       "       'co_applicant_race_observed', 'applicant_sex', 'co_applicant_sex',\n",
       "       'applicant_sex_observed', 'co_applicant_sex_observed', 'applicant_age',\n",
       "       'co_applicant_age', 'applicant_age_above_62',\n",
       "       'co_applicant_age_above_62', 'submission_of_application',\n",
       "       'initially_payable_to_institution', 'aus_1', 'aus_2', 'aus_3', 'aus_4',\n",
       "       'aus_5', 'denial_reason_1', 'denial_reason_2', 'denial_reason_3',\n",
       "       'denial_reason_4', 'tract_population',\n",
       "       'tract_minority_population_percent',\n",
       "       'ffiec_msa_md_median_family_income', 'tract_to_msa_income_percentage',\n",
       "       'tract_owner_occupied_units', 'tract_one_to_four_family_homes',\n",
       "       'tract_median_age_of_housing_units'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Well we have 99 columns. Let's look at what they're called.\n",
    "\n",
    "lar18.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "10f047c9-72b6-4e9a-8b52-d2686b8f7393",
   "metadata": {},
   "outputs": [],
   "source": [
    "# To refresh my memory, here are the columns the 2023 Snapshot read in with:\n",
    "\n",
    "# Index(['activity_year', 'lei', 'derived_msa_md', 'state_code', 'county_code',\n",
    "#       'census_tract', 'conforming_loan_limit', 'derived_loan_product_type',\n",
    "#       'derived_dwelling_category', 'derived_ethnicity', 'derived_race',\n",
    "#       'derived_sex', 'action_taken', 'purchaser_type', 'preapproval',\n",
    "#       'loan_type', 'loan_purpose', 'lien_status', 'reverse_mortgage',\n",
    "#       'open_end_line_of_credit', 'business_or_commercial_purpose',\n",
    "#       'loan_amount', 'combined_loan_to_value_ratio', 'interest_rate',\n",
    "#       'rate_spread', 'hoepa_status', 'total_loan_costs',\n",
    "#       'total_points_and_fees', 'origination_charges', 'discount_points',\n",
    "#       'lender_credits', 'loan_term', 'prepayment_penalty_term',\n",
    "#       'intro_rate_period', 'negative_amortization', 'interest_only_payment',\n",
    "#       'balloon_payment', 'other_nonamortizing_features', 'property_value',\n",
    "#       'construction_method', 'occupancy_type',\n",
    "#       'manufactured_home_secured_property_type',\n",
    "#       'manufactured_home_land_property_interest', 'total_units',\n",
    "#       'multifamily_affordable_units', 'income', 'debt_to_income_ratio',\n",
    "#       'applicant_credit_score_type', 'co_applicant_credit_score_type',\n",
    "#       'applicant_ethnicity_1', 'applicant_ethnicity_2',\n",
    "#       'applicant_ethnicity_3', 'applicant_ethnicity_4',\n",
    "#       'applicant_ethnicity_5', 'co_applicant_ethnicity_1',\n",
    "#       'co_applicant_ethnicity_2', 'co_applicant_ethnicity_3',\n",
    "#       'co_applicant_ethnicity_4', 'co_applicant_ethnicity_5',\n",
    "#       'applicant_ethnicity_observed', 'co_applicant_ethnicity_observed',\n",
    "#       'applicant_race_1', 'applicant_race_2', 'applicant_race_3',\n",
    "#       'applicant_race_4', 'applicant_race_5', 'co_applicant_race_1',\n",
    "#       'co_applicant_race_2', 'co_applicant_race_3', 'co_applicant_race_4',\n",
    "#       'co_applicant_race_5', 'applicant_race_observed',\n",
    "#       'co_applicant_race_observed', 'applicant_sex', 'co_applicant_sex',\n",
    "#       'applicant_sex_observed', 'co_applicant_sex_observed', 'applicant_age',\n",
    "#       'co_applicant_age', 'applicant_age_above_62',\n",
    "#       'co_applicant_age_above_62', 'submission_of_application',\n",
    "#       'initially_payable_to_institution', 'aus_1', 'aus_2', 'aus_3', 'aus_4',\n",
    "#       'aus_5', 'denial_reason_1', 'denial_reason_2', 'denial_reason_3',\n",
    "#       'denial_reason_4', 'tract_population',\n",
    "#       'tract_minority_population_percent',\n",
    "#       'ffiec_msa_md_median_family_income', 'tract_to_msa_income_percentage',\n",
    "#       'tract_owner_occupied_units', 'tract_one_to_four_family_homes',\n",
    "#       'tract_median_age_of_housing_units'],\n",
    "#      dtype='object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "55a0d04a-c3c6-45a9-9e8e-f9be3f7c0350",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(52963, 80)"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I think we're good to run the filter function\n",
    "\n",
    "lar18_filtered = filter_lar(lar18)\n",
    "\n",
    "lar18_filtered.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "24734176-b45c-4b31-9c80-3b30fc29b8bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar18 = lar18_filtered\n",
    "\n",
    "lar18['county_code'] = lar18['county_code'].astype(int).astype(str)\n",
    "\n",
    "lar18.to_csv('lar_filtered_pa_18.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30b46aeb-8138-47f3-8c60-d5f9b3d5078f",
   "metadata": {},
   "source": [
    "# Me Oh My\n",
    "# I'm ready to concatenate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8cfa7fbe-3800-4bc4-a24e-d478cebf774f",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar23 = pd.read_csv('lar_filtered_pa_23.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ab7c4cba-8c3c-4f3d-897e-1f794547dff5",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar22 = pd.read_csv('lar_filtered_pa_22.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cc978b44-91f1-40c3-a1ae-8712a90b31b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar21 = pd.read_csv('lar_filtered_pa_21.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2f39c154-fa03-45df-89ab-9b99b96dbc90",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar20 = pd.read_csv('lar_filtered_pa_20.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c998da4f-5f07-4b07-893c-06ef5b8bb340",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar19 = pd.read_csv('lar_filtered_pa_19.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5e691313-7a92-4757-a1be-032437455fcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar18 = pd.read_csv('lar_filtered_pa_18.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a415ab3e-9b98-4b53-95a7-7a1a3d029cc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar1823 = pd.concat([lar23, lar22, lar21, lar20, lar19, lar18], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ccdca865-aed1-48eb-b00b-fdc5a1e8c6ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(367391, 80)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lar1823.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "02350537-0dab-4a70-b485-29dcc542aea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "lar1823.to_csv('lar_filtered_pa_18-23.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e4132af4-89a7-4d0e-8d0a-74c511c6e4ca",
   "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>activity_year</th>\n",
       "      <th>lei</th>\n",
       "      <th>county_code</th>\n",
       "      <th>derived_loan_product_type</th>\n",
       "      <th>derived_ethnicity</th>\n",
       "      <th>derived_race</th>\n",
       "      <th>derived_sex</th>\n",
       "      <th>purchaser_type</th>\n",
       "      <th>preapproval</th>\n",
       "      <th>loan_type</th>\n",
       "      <th>...</th>\n",
       "      <th>aus_3</th>\n",
       "      <th>aus_4</th>\n",
       "      <th>aus_5</th>\n",
       "      <th>tract_population</th>\n",
       "      <th>tract_minority_population_percent</th>\n",
       "      <th>ffiec_msa_md_median_family_income</th>\n",
       "      <th>tract_to_msa_income_percentage</th>\n",
       "      <th>tract_owner_occupied_units</th>\n",
       "      <th>tract_one_to_four_family_homes</th>\n",
       "      <th>tract_median_age_of_housing_units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42125</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5319</td>\n",
       "      <td>7.56</td>\n",
       "      <td>101900</td>\n",
       "      <td>127.88</td>\n",
       "      <td>1698</td>\n",
       "      <td>1929</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42027</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2983</td>\n",
       "      <td>18.44</td>\n",
       "      <td>115500</td>\n",
       "      <td>119.02</td>\n",
       "      <td>967</td>\n",
       "      <td>1393</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42043</td>\n",
       "      <td>FHA:First Lien</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>Race Not Available</td>\n",
       "      <td>Female</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6484</td>\n",
       "      <td>40.72</td>\n",
       "      <td>102700</td>\n",
       "      <td>106.86</td>\n",
       "      <td>1388</td>\n",
       "      <td>1929</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42087</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Joint</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2372</td>\n",
       "      <td>8.26</td>\n",
       "      <td>77800</td>\n",
       "      <td>53.55</td>\n",
       "      <td>613</td>\n",
       "      <td>959</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023</td>\n",
       "      <td>549300RN01LBYR8ZVX74</td>\n",
       "      <td>42061</td>\n",
       "      <td>Conventional:First Lien</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4624</td>\n",
       "      <td>5.95</td>\n",
       "      <td>77800</td>\n",
       "      <td>90.06</td>\n",
       "      <td>1508</td>\n",
       "      <td>2212</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   activity_year                   lei  county_code derived_loan_product_type  \\\n",
       "0           2023  549300RN01LBYR8ZVX74        42125   Conventional:First Lien   \n",
       "1           2023  549300RN01LBYR8ZVX74        42027   Conventional:First Lien   \n",
       "2           2023  549300RN01LBYR8ZVX74        42043            FHA:First Lien   \n",
       "3           2023  549300RN01LBYR8ZVX74        42087   Conventional:First Lien   \n",
       "4           2023  549300RN01LBYR8ZVX74        42061   Conventional:First Lien   \n",
       "\n",
       "        derived_ethnicity        derived_race derived_sex  purchaser_type  \\\n",
       "0  Not Hispanic or Latino               White      Female               0   \n",
       "1  Not Hispanic or Latino               White       Joint              71   \n",
       "2      Hispanic or Latino  Race Not Available      Female              71   \n",
       "3  Not Hispanic or Latino               White       Joint               0   \n",
       "4  Not Hispanic or Latino               White      Female               0   \n",
       "\n",
       "   preapproval  loan_type  ...  aus_3  aus_4  aus_5 tract_population  \\\n",
       "0            2          1  ...    NaN    NaN    NaN             5319   \n",
       "1            2          1  ...    NaN    NaN    NaN             2983   \n",
       "2            2          2  ...    NaN    NaN    NaN             6484   \n",
       "3            2          1  ...    NaN    NaN    NaN             2372   \n",
       "4            2          1  ...    NaN    NaN    NaN             4624   \n",
       "\n",
       "  tract_minority_population_percent ffiec_msa_md_median_family_income  \\\n",
       "0                              7.56                            101900   \n",
       "1                             18.44                            115500   \n",
       "2                             40.72                            102700   \n",
       "3                              8.26                             77800   \n",
       "4                              5.95                             77800   \n",
       "\n",
       "  tract_to_msa_income_percentage tract_owner_occupied_units  \\\n",
       "0                         127.88                       1698   \n",
       "1                         119.02                        967   \n",
       "2                         106.86                       1388   \n",
       "3                          53.55                        613   \n",
       "4                          90.06                       1508   \n",
       "\n",
       "  tract_one_to_four_family_homes tract_median_age_of_housing_units  \n",
       "0                           1929                                43  \n",
       "1                           1393                                51  \n",
       "2                           1929                                42  \n",
       "3                            959                                73  \n",
       "4                           2212                                51  \n",
       "\n",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lar1823.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df22f105-a20e-4838-b8f7-8812a76fb2ba",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}