Data Journalism Backup Site

Here is my optional tagline

Nov. 3 Class

by | Nov 3, 2016 | Fall 2016 Daily-Class

Interviews: Due Monday, Nov. 7, 11:59 PM

See Assignment #2 in Blackboard

  1. Bring a Digital SLR Camera and a Audio Recorder to the Interviews.
  2. Tell the people we will be using a fact-check process and someone will be calling back to double-check facts from this interview before we publish. Obtain the email address and cell number of each person you interview and put that on the interview sheet.
  3. I need a FULL VERBATIM TRANSCRIPT of each interview. I need the audio files. No exceptions. Upload transcript, audio and images to Google Drive space for our class: https://drive.google.com/drive/folders/0B9N10qKqBa2zVGtWbVctNU9kZTQ?usp=sharing

Trupp, Monk and Ball: Marshallese community

Flores, Messina, Lyster, Billmyre:  Hispanic community

Dobrin,Thomas, Taylor Pratt: Bankers in the Northwest Arkansas area

Taylor Pray, Eley, Ward:  Asian community
–Quincy includes questions based on his immigration & salary research for Asians.

For each group, gather the data and information to ask the following questions:

–Rejection rate for combined conventional and FHA mortgages in 2014 and how that compares to whites.
–Median household income for these groups vs whites
–What is the trend for these groups prior to 2014.
–How the banks perform outreach or marketing to their communities.
–Additional questions based on your research.
–A specific game plan telling me when and how you are going to conduct these interviews and divide up the labor.
Options could be your group picks one person to make the calls and the others will do background research to prepare for the interview or will transcribe the recording afterwards. Another option is the entire group goes to the interview. Another option is two people do the interview and a third person does charts or some new data work.

This page will have some useful background for the assignment.

Summary Work So Far

 

Analysis of Full HMDA Dataset

Review Homework – Analyze 2014 Flat Files

–Analyze denial reasons by race, ethnicity
–Loan Approvals and Denials by Minority Population %
–Loan amount by race
–Loan Approvals and Denials by sex and race

Relational Databases: How They Work

Link on Common Fields
Inner, Left, Right joins
Tableau can join fields. Common Field is Respondent ID.
**Before you link on anything, understand what is the nature of the common field and try to see what could go wrong/
RID – Respondent or Reporter ID
A ten-digit number used to identify a HMDA reporting institution.
Bank Data
Joint LAR with Bank files in Tableau

Questions:

1) Total bank lending, largest to smallest, with a grand total column
2) Banks and lending by race, ethnicity. (Tip: Do a separate calculation for Hispanics — ethnic category)
3) Banks with the highest rejection percentage rate
4) Banks with the highest rejection percentage rate by race and by ethnicity.

PROJECTS FOR THURSDAY IN-CLASS

David:
– Revised Applications by Rekjection Rate, 10-31-16
—eliminate the N/A
Julia:
Revise “reasons for denial HMDA data”
cut out the information not provided by applicant and consider normalizing these as a percentage of all applications.
Nanci:
Acceptance Gender and Race
the Y axis — it says “value” – what is it? percentage of something. it needs to be labeled. Also, having labels on the bars would be really useful.
Revise Loan Purpose: Refinance from 10-31
—eliminate Grand Total
—New chart Title: “rejection rate for loan refinancing, 2014, NW Arkansas”
—Label the bars
Taylor Pray:
Revise Rate of Denials by Race
—Cut info not provided by applicant.
—Eliminate the legend
Ashton:
put the source of the data in the title slide, HMDA 2014 , FFIEC. Change the “value” on X axis to number of incidents.
Quincy:
Chart #3 – I would cut out the info not provided by applicant in the Hispanic denial chart. Fix the X axis label to show just “Count of Denial Reasons”
Chart #5 – Loan Amount average by race – Let’s fix this and get dollar signs on the labels. I want to double check your spreadsheet calculations on this too.
Chart #6 – Rejection reason by minority population. Let’s sort it – highest to smallest, L—>R. Get rid of the null category.

Math on Tableau – Percentages

Create chart with Race Labeled in Columns and Action type, Count, in Rows.
Filter Race Labeled to Show Asians, Blacks, Indians and Native Hawaiians
Color Action Type
Label Action Type, Convert to Count
Calculate Percentages in Chart
Anaylsis | Percentage Of   (calcs percentages of the overall totals)
Click CNT (Action Type) in Labels
Edit Table Calculation
Compute Using Specific Dimensions
Click only Action Type
*Calculation is now % of Total Count of Action Type
Filter This to Denials and Approvals in Marks, actions
all-loans-denials-2014-flat-files-10-26-16

Review of Pivot Table Instructions- HMDA Flat Files

We are continuing work with the FLAT FILES and the Aggregate Table 4-2.

Here are the base results for the Conventional, FHA, VA mortgages

totals-table-4-1-4-2

Method for Analyzing 2014 Flat Files and Rejections By Race

Row Labels
Total Applications

Column Labels
–Race Labeled
–Values

Values
–% Denied (Calculated as Action Type, % of Total)
—Count of Action type

Report Filter
Loan Type
Loan Purpose

filter-black-loans

 

Background:
https://www.ffiec.gov/hmda/hmdarawdata2015.htm#by_msa

See CODING SHEETS
“File Formats & Documentation (PDFs)”
See Code Sheet, Inst Record Format, LAR Record Format, MSA Office, Reporter Panel

 

Calculations and Pivot Tables