Banking Dashboard: Adjust intermediate tabular summaries to prepare for Tableau

[korelasidata]

Data updates

• [x] 1. In the tabular summaries notebook, adjust the FDIC bank branch details so that the overall bank name is listed as a column separately from the bank branch name. For example, in the samples below, all of these would have the same bank name "American First National Bank ", and then in a separate column we would have the branch name.

  

• [x] 2. Update hmda_lei_count logic so that the count variable name is number_of_hmda_loans. Also change name of the variable to hmda_loan_count.

• [x] 3. Left join HMDA Panel data to HMDA LAR data on lei to get bank names (respondent_name) inserted into the LAR table

  • Only need the respondent name. Can ignore the other fields.
  • Can use column subset (lei and respondent name) in the panel df join
  • Rename this column as bank_name
  • Did all lei values in HMDA get matched? What is the count/percentage that were not matched? Please post this response as a comment on the PR.

• [x] 4. SBA data already has a BankName field that provides the name of the bank. (Whew!!) To facilitate the mapping between HMDA and SBA and to get consistency between bank names in FDIC tables, left join SBA data to FDIC locations data on the FDIC identifiers below so that the Institution Name is inserted into the SBA dataset

  • SBA: BankFDICNumber mapped to...
  • FDIC institutions FDIC Certificate #

• [x] 5. Create new dictionaries called census_level_hmda_cat_summaries_long and census_level_sba_cat_summaries_long that are modified versions of census_level_hmda_cat_summaries and census_level_sba_cat_summaries such that the data are presented in long format instead of wide format

  • No need to display these in the Jupyter notebook. Just export to new Excel workbooks (along with the numerical summaries as-is) and publish them to the Google folder.
  • We don't need the Total column here.
  • This essentially skips the unstack you previously used to get them in wide format

Expected result converts this (wide format)...

... to this (long format)...

[korelasidata]

@cbharp See corrections added to bullet points 3 and 4 above. Note the bold/italic portions that I inserted/changed.

[korelasidata]

@cbharp

In census_level_hmda_cat_summaries_long and census_level_sba_cat_summaries_long:

• [x] Insert a new column called Census_Tract_no_decimal that excludes the decimal point. So Instead of "0301.02" we would have "030102". This new column should appear immediately after the existing Census Tract column.

• [x] Re-run the code that generates the Excel notebooks, and re-publish the updated files to the Google folder.