Discussion for Oct 20: Pivot Tables
Part 1: Pivot tables
Pivot tables are a powerful tool in Excel to analyze and tabulate complex spreadsheets.
Follow the instructions on this NICAR tipsheet called pivot tables.. You will be using a practice dataset called worldbank.
Create a pivot table using WorldBank.
#1 Following the NICAR handout instructions, create a list of the top countries on this disbarment list. Sum up the violations and then sort the list, highest to lowest.
Save these on separate tabs on a single spreadsheet and upload it to a blog post on WordPress.
Class Exercise: Pivot Tables
Download this data on race, income and lending.
#1 DETERMINE TOTAL APPLICATIONS RACE AND INCOME CATEGORY
ROW: RACE / ETHNICITY
COLUMN: SUM OF TOTAL APPLICANTS
COLUMN LABELS: INCOME PERCENTILE
FILTER COLUMN LABELS TO ELIMINATE THE “TOTALS”
#2 DETERMINE TOTAL REJECTIONS BY RACE AND INCOME CATEGORY
ROW: RACE / ETHNICITY
COLUMN: SUM OF TOTAL DENIALS
COLUMN LABELS: INCOME PERCENTILE
FILTER COLUMN LABELS TO ELIMINATE THE “TOTALS”
#3 DETERMINE TOTAL REJECTIONS BY RACE AND INCOME CATEGORY, <50% INCOME
ROW: RACE / ETHNICITY
COLUMN: SUM OF TOTAL DENIALS
COLUMN: SUM OF TOTAL APPLICANTS
COLUMN LABELS: INCOME PERCENTILE
Homework
Blog Post Due 11:59 pm Monday, Oct. 24
Download this data on race, income and lending.
#1 Finish this in-class exercise and create a chart in Tableau. Upload a .jpeg image into your blog post.
DETERMINE TOTAL REJECTIONS BY RACE AND INCOME CATEGORY, <50% INCOME
ROW: RACE / ETHNICITY
COLUMN: SUM OF TOTAL DENIALS
COLUMN: SUM OF TOTAL APPLICANTS
COLUMN LABELS: INCOME PERCENTILE
#2 Prepare for interviews
It’s time to begin interviewing people in the community about our findings. I will review these questions and discuss them with each group on Tuesday, and then you all will start the interviews right after class Tuesday. The goal is each group will do at least one interview by the end of the week.
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.
Trupp, Monk and Ball: Prepare questions for Marshallese community, identify people/ groups you will call or visit.
Flores, Messina, Lyster, Billmyre: Prepare questions for Hispanic community, identify people/ groups you will call or visit.
Dobrin,Thomas, Taylor Pratt: Prepare questions for bankers in the Northwest Arkansas area, identify people/ groups you will call or visit.
Taylor Pray, Eley, Ward: Prepare questions for Asian community, identify people / groups you will call or visit
–Quincy prepares questions from his analysis of Asians, salaries and immigration.
This page will have some useful background for the assignment.
Each group can hand in one set of questions and a game plan by 11:59 pm Monday.
Thanks.
Recent Comments