def minority_aggregation_as_json(request): """ aggregates minority population ranges and LAR counts for a lender in an MSA by tract, msa and county """ msa_target_lma_sum = 0 msa_target_mma_sum = 0 msa_target_hma_sum = 0 msa_peer_lma_sum = 0 msa_peer_mma_sum = 0 msa_peer_hma_sum = 0 msa_stats = {} lar_data = loan_originations_as_json(request) lender = get_object_or_404(Institution, pk=request.GET.get('lender')) metro = get_object_or_404(Geo, geo_type=Geo.METRO_TYPE, geoid=request.GET.get('metro')) peer_request = HttpRequest() peer_request.GET['lender'] = lender.institution_id peer_request.GET['metro'] = metro.geoid peer_request.GET['peers'] = 'true' peer_lar_data = loan_originations_as_json(peer_request) msa_counties = Geo.objects.filter(geo_type=Geo.COUNTY_TYPE, cbsa=metro.cbsa, year=metro.year) county_stats = {} for county in msa_counties: county_tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, state=county.state, county=county.county, year=metro.year) minority_area_stats = get_minority_area_stats(lar_data, peer_lar_data, county_tracts) county_stats[county.geoid] = assemble_stats(*minority_area_stats) county_stats[county.geoid]['name'] = county.name #tally target msa counts msa_target_lma_sum += county_stats[county.geoid]['lma'] msa_target_mma_sum += county_stats[county.geoid]['mma'] msa_target_hma_sum += county_stats[county.geoid]['hma'] #tally peer msa counts msa_peer_lma_sum += county_stats[county.geoid]['peer_lma'] msa_peer_mma_sum += county_stats[county.geoid]['peer_mma'] msa_peer_hma_sum += county_stats[county.geoid]['peer_hma'] #msa msa_minority_area_stats = (msa_target_lma_sum, msa_target_mma_sum, msa_target_hma_sum, msa_peer_lma_sum, msa_peer_mma_sum, msa_peer_hma_sum) msa_stats = assemble_stats(*msa_minority_area_stats) return { 'msa': msa_stats, 'counties': county_stats, }
def minority_aggregation_as_json(request): """ aggregates minority population ranges and LAR counts for a lender in an MSA by tract, msa and county """ lar_data = loan_originations_as_json(request) lender = Institution.objects.get(institution_id=request.GET.get('lender')) tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, cbsa=request.GET.get('metro')) metro = Geo.objects.get(geo_type=Geo.METRO_TYPE, geoid=request.GET.get('metro')) lender_stats = assemble_stats(lar_data, tracts) # MSA msa_pop, msa_minority_ct, msa_minority_pct = tally_msa_minority_stats(tracts) msa_stats = { 'minority_ct': msa_minority_ct, 'minority_pct': round(msa_minority_pct, 3) } # PEERS peers = lender.get_peer_list(metro, True, True) if peers: peer_data_collector = [] for peer in peers: peer_request = HttpRequest() peer_request.GET['lender'] = peer.institution.institution_id peer_request.GET['metro']= metro.geoid peer_lar_data = loan_originations_as_json(peer_request) peer_data_collector.append(assemble_stats(peer_lar_data, tracts)) if len(peer_data_collector) == 1: peer_stats = peer_data_collector[0] else: peer_stats = combine_peer_stats(peer_data_collector) # ODDS target_mm = lender_stats['hma'] + lender_stats['mma'] target_non = lender_stats['lma'] peer_mm = peer_stats['hma'] + peer_stats['mma'] peer_non = peer_stats['lma'] odds = odds_ratio(target_mm, target_non, peer_mm, peer_non) else: peer_stats = None odds = None # BY COUNTY county_ids = sorted(set([tract.geoid[:5] for tract in tracts])) county_stats = {county_id: {} for county_id in county_ids} for county_id in county_ids: county_tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, state=county_id[:2], county=county_id[2:]) county_stats[county_id] = assemble_stats(lar_data, county_tracts) return { 'msa': msa_stats, 'lender': lender_stats, 'peers': peer_stats, 'odds': odds, 'counties': county_stats, }
def minority_aggregation_as_json(request): """ aggregates minority population ranges and LAR counts for a lender in an MSA by tract, msa and county """ msa_target_lma_sum = 0 msa_target_mma_sum = 0 msa_target_hma_sum = 0 msa_peer_lma_sum = 0 msa_peer_mma_sum = 0 msa_peer_hma_sum = 0 msa_stats = {} lar_data = loan_originations_as_json(request) lender = get_object_or_404(Institution, pk=request.GET.get('lender')) metro = get_object_or_404(Geo, geo_type=Geo.METRO_TYPE, geoid=request.GET.get('metro')) peer_request = HttpRequest() peer_request.GET['lender'] = lender.institution_id peer_request.GET['metro']= metro.geoid peer_request.GET['peers'] = 'true' peer_lar_data = loan_originations_as_json(peer_request) msa_counties = Geo.objects.filter(geo_type=Geo.COUNTY_TYPE, cbsa=metro.geoid) county_stats = {} for county in msa_counties: county_tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, state=county.state, county=county.county) minority_area_stats = get_minority_area_stats(lar_data, peer_lar_data, county_tracts) county_stats[county.geoid] = assemble_stats(*minority_area_stats) county_stats[county.geoid]['name'] = county.name #tally target msa counts msa_target_lma_sum += county_stats[county.geoid]['lma'] msa_target_mma_sum += county_stats[county.geoid]['mma'] msa_target_hma_sum += county_stats[county.geoid]['hma'] #tally peer msa counts msa_peer_lma_sum += county_stats[county.geoid]['peer_lma'] msa_peer_mma_sum += county_stats[county.geoid]['peer_mma'] msa_peer_hma_sum += county_stats[county.geoid]['peer_hma'] #msa msa_minority_area_stats = (msa_target_lma_sum, msa_target_mma_sum, msa_target_hma_sum, msa_peer_lma_sum, msa_peer_mma_sum, msa_peer_hma_sum) msa_stats = assemble_stats(*msa_minority_area_stats) return { 'msa': msa_stats, 'counties': county_stats, }
def all(request): """This endpoint allows multiple statistical queries to be made in a single HTTP request""" hmda = loan_originations_as_json(request) minority = race_summary_as_json(request) responses = {'minority' : minority, 'loanVolume': hmda} return HttpResponse(json.dumps(responses), content_type='application/json')
def test_assemble_stats(self): """should calculate and return a dict of lender loan totals by minority area""" lar_data = loan_originations_as_json(request) lender = Institution.objects.get(institution_id=request.GET.get('lender')) metro = Geo.objects.get(geo_type=Geo.METRO_TYPE, geoid=request.GET.get('metro')) peer_request = HttpRequest() peer_request.GET['lender'] = lender.institution_id peer_request.GET['metro']= metro.geoid peer_request.GET['peers'] = 'true' peer_request.GET['action_taken'] = '1,2,3,4,5' peer_lar_data = loan_originations_as_json(peer_request) tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, cbsa=request.GET.get('metro')) lender_stats = assemble_stats(*get_minority_area_stats(lar_data, peer_lar_data, tracts)) self.assertEqual(lender_stats['hma_pct'], 0) self.assertEqual(lender_stats['lma_pct'], 1) self.assertEqual(lender_stats['mma_pct'], 0) self.assertEqual(lender_stats['lma'], 7) self.assertEqual(lender_stats['mma'], 0) self.assertEqual(lender_stats['hma'], 0) self.assertEqual(lender_stats['lar_total'], 7)
def test_assemble_stats(self): """should calculate and return a dict of lender loan totals by minority area""" lar_data = loan_originations_as_json(request) tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, cbsa=request.GET.get('metro')) lender_stats = assemble_stats(lar_data, tracts) self.assertEqual(lender_stats['hma_pct'], 0) self.assertEqual(lender_stats['lma_pct'], 1) self.assertEqual(lender_stats['mma_pct'], 0) self.assertEqual(lender_stats['lma'], 7) self.assertEqual(lender_stats['mma'], 0) self.assertEqual(lender_stats['hma'], 0) self.assertEqual(lender_stats['lar_total'], 7)
def msa(request): """returns simplified tract shapes for dot-density mapping, with loan volume""" try: metro = request.GET.get('metro') tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, cbsa=metro) tract_loans = loan_originations_as_json(request) except: return HttpResponseBadRequest("request failed; details: %s" % request) else: try: with open("/var/www/static/tracts/%s.json" % metro, 'r') as f: local_tracts = json.loads(f.read()) except: local_tracts = None tracts_out = {"type": "FeatureCollection", "features": []} if local_tracts: for tract_id in local_tracts: volume = 0 if tract_id in tract_loans and tract_loans[tract_id]['volume']: volume += tract_loans[tract_id]['volume'] tracts_out['features'].append({ "type": "Feature", "geometry": { "type": "Polygon", "coordinates": local_tracts[tract_id] }, "properties": { "tract_id": tract_id, "volume": volume } }) else: for tract in tracts: volume = 0 if tract.geoid in tract_loans and isinstance( tract_loans[tract.geoid]['volume'], int): volume += tract_loans[tract.geoid]['volume'] tracts_out['features'].append({ "type": "Feature", "geometry": { "type": "Polygon", "coordinates": tract.geom.simplify(0.001).coords }, "properties": { "tract_id": tract.geoid, "volume": volume } }) context = {'tracts': tracts_out} return HttpResponse(json.dumps(context), content_type='application/json')
def race_summary_csv(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') year = request.GET.get('year') if institution_id and metro: lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_Year%s_Lender%s_MSA%s.csv' % ( year, institution_id, metro) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"Originated Loans"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) if lar_data.get(obj.geoid.geoid, None): lar_count = lar_data[obj.geoid.geoid]['volume'] num_households = lar_data[obj.geoid.geoid]['num_households'] else: lar_count = 0 # if there's no loan data we can still get household data num_households = get_object_or_404( Census2010Households, geoid_id=obj.geoid.geoid).total writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response else: raise Http404("Invalid Institution or Metro")
def race_summary_csv(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') year = request.GET.get('year') if institution_id and metro: lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_Year%s_Lender%s_MSA%s.csv' % (year, institution_id, metro) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"Originated Loans"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) if lar_data.get(obj.geoid.geoid,None): lar_count = lar_data[obj.geoid.geoid]['volume'] num_households = lar_data[obj.geoid.geoid]['num_households'] else: lar_count = 0 # if there's no loan data we can still get household data num_households = get_object_or_404(Census2010Households, geoid_id=obj.geoid.geoid).total writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response else: raise Http404("Invalid Institution or Metro")
def race_summary_csv(request): lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_2013_Lender%s_MSA%s.csv' % (request.GET.get('lender'), request.GET.get('metro')) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) #response.write(u'\ufeff'.encode('utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"HMDA LAR Count"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) try: lar_count = lar_data[obj.geoid.geoid]['volume'] except: lar_count = 0 try: num_households = lar_data[obj.geoid.geoid]['num_households'] except: num_households = 0 writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response
def test_combine_peer_stats(self): """should return a dict collated minority stats for a group of lender peers""" tracts = Geo.objects.filter(geo_type=Geo.TRACT_TYPE, cbsa=request.GET.get('metro')) metro = Geo.objects.get(geo_type=Geo.METRO_TYPE, geoid=request.GET.get('metro')) lender = Institution.objects.get( institution_id=request.GET.get('lender')) peers = lender.get_peer_list(metro, None, None) peer_data_collector = [] for peer in peers: peer_request = HttpRequest() peer_request.GET['lender'] = peer.institution.institution_id peer_request.GET['metro'] = metro.geoid peer_lar_data = loan_originations_as_json(peer_request) peer_data_collector.append(assemble_stats(peer_lar_data, tracts)) peer_stats = combine_peer_stats(peer_data_collector) self.assertEqual(peer_stats['hma_pct'], 0.0) self.assertEqual(peer_stats['lma_pct'], 1.0) self.assertEqual(peer_stats['mma_pct'], 0.0) self.assertEqual(peer_stats['lma'], 7) self.assertEqual(peer_stats['mma'], 0) self.assertEqual(peer_stats['hma'], 0) self.assertEqual(peer_stats['lar_total'], 7)
def hmda(request): """This endpoint returns hmda data using params from the request""" return HttpResponse(json.dumps(loan_originations_as_json(request)))