def get_development_profile(geo_code, geo_level, _session): development_data = {} if geo_level != 'vdc': district_amounts_table = get_datatable('district_aid_amounts') district_aid_amounts, _ = district_amounts_table.get_stat_data( geo_level, geo_code, percent=False) district_projects_table = get_datatable('district_aid_projects') district_aid_projects, _ = district_projects_table.get_stat_data( geo_level, geo_code, percent=False) development_data = dict( is_vdc=False, area_has_data=True, district_aid_amounts={ 'name': 'District-level development assistance in US Dollars', 'values': { 'this': district_aid_amounts['amount']['values']['this'] } }, district_aid_projects={ 'name': 'Active district-level development assistance ' 'projects', 'values': { 'this': district_aid_projects['total']['values']['this'] } }) return development_data
def get_ecd_educators_profile(geo, session): # These values will be filled as information becomes available. table = get_datatable('ecd_educators') ecd_educators, _ = table.get_stat_data(geo, percent=False) table = get_datatable('ecd_children_enrolled') children_enrolled, _ = table.get_stat_data(geo, percent=False) children_per_practitioner = ratio( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], ecd_educators['practitioners_for_ages_3_to_5']['values']['this']) _, children_age_3_to_5_in_area = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=['3', '4', '5'], recode=ECD_AGE_CATEGORIES) return { "children_per_practitioner": { "name": "Number of children enrolled in centres for each practitioner", "values": { "this": children_per_practitioner } }, "children_per_trained_practitioner": { "name": "Number of children enrolled in centres for each trained practitioner *", "values": { "this": None } }, "children_per_untrained_practitioner": { "name": "Number of children enrolled in centres for each untrained practitioner *", "values": { "this": None } }, "practitioners_for_ages_3_to_5": { "name": "Number of practitioners in the area for children aged 3-5.", "values": { "this": ecd_educators['practitioners_for_ages_3_to_5']['values'] ['this'] } }, 'children_age_3_to_5_in_area': { "name": "Children (age 3-5) living in the area", "values": { "this": children_age_3_to_5_in_area } }, }
def dispatch(self, *args, **kwargs): try: self.table = get_datatable(kwargs['table']) except KeyError: raise Http404 return super(TableDetailView, self).dispatch(*args, **kwargs)
def get_economic_opportunities_profile(geo_code, geo_level, session): table = get_datatable('youth').model (emp_dep, neets_dep, prop_multid_poor, youth_mpi) = session.query( table.c.emp_dep, table.c.neets_dep, table.c.prop_multid_poor, table.c.youth_mpi) \ .filter(table.c.geo_level == geo_level) \ .filter(table.c.geo_code == geo_code) \ .one() final_data = { 'emp_dep': { "name": "Proportion of youth living in households where no working-age adults (age 18-64) are employed", "values": {"this": float(emp_dep) or 0.0}, }, 'neets_dep': { "name": "Proportion of youth who are not in education, employment or training", "values": {"this": float(neets_dep) or 0.0}, }, 'prop_multid_poor': { "name": "Proportion of youth who are multidimensionally poor", "values": {"this": float(prop_multid_poor) or 0.0}, }, 'youth_mpi': { "name": "Youth Multidimensional Poverty Index score", "values": {"this": float(youth_mpi) or 0.0}, } } return final_data
def setup_table(self): table_id = self.table_id or get_table_id(self.fields) try: self.table = get_datatable(table_id).get_db_table(year=self.release_year) self.stdout.write("Table for fields %s is %s" % (self.fields, self.table.id)) except KeyError: raise CommandError("Couldn't establish which table to use for these fields. Have you added a FieldTable entry in wazimap_za/tables.py?\nFields: %s" % self.fields)
def get_election_data(geo, election, session): party_data, total_valid_votes = get_stat_data( ['party'], geo, session, table_dataset=election['dataset'], recode=lambda f, v: make_party_acronym(v), order_by='-total') results = { 'name': election['name'], 'party_distribution': party_data, 'geo_version': election['geo_version'] } # voter registration and turnout table = get_datatable('voter_turnout_%s' % election['table_code']) results.update( table.get_stat_data( geo, 'registered_voters', percent=False, recode={'registered_voters': 'Number of registered voters'})[0]) results.update( table.get_stat_data( geo, 'total_votes', percent=True, total='registered_voters', recode={'total_votes': 'Of registered voters cast their vote'})[0]) return results
def get_profile(geo_code, geo_level, profile_name=None): # Census data table = get_datatable('population_2011') _, total_pop = table.get_stat_data(geo_level, geo_code, percent=False) geo = geo_data.get_geography(geo_code, geo_level) population_density = None if geo.square_kms: population_density = total_pop / geo.square_kms indicator_calc = IndicatorCalculator(geo_code) indicator_calc.calculate() indicators = {} indicators['cash_at_year_end'] = indicator_calc.cash_at_year_end() indicators['cash_coverage'] = indicator_calc.cash_coverage() indicators['op_budget_diff'] = indicator_calc.op_budget_diff() indicators['cap_budget_diff'] = indicator_calc.cap_budget_diff() indicators['rep_maint_perc_ppe'] = indicator_calc.rep_maint_perc_ppe() indicators['wasteful_exp'] = indicator_calc.wasteful_exp_perc_exp() indicators['expenditure_trends'] = indicator_calc.expenditure_trends() indicators['revenue_breakdown'] = indicator_calc.revenue_breakdown() indicators['expenditure_trends'] = indicator_calc.expenditure_trends() indicators['expenditure_functional_breakdown'] = indicator_calc.expenditure_functional_breakdown() return { 'total_population': total_pop, 'population_density': population_density, 'mayoral_staff': indicator_calc.mayoral_staff(), 'muni_contact': indicator_calc.muni_contact(), 'audit_opinions': indicator_calc.audit_opinions(), 'indicators': indicators, }
def setup_table(self): table_id = self.table_id or get_table_id(self.fields) try: self.table = get_datatable(table_id).get_db_table(year=self.release_year) self.stdout.write("Table for fields %s is %s" % (self.fields, self.table.id)) except KeyError: raise CommandError("Couldn't establish which table to use for these fields. Have you added a FieldTable entry in youthexporer/tables.py?\nFields: %s" % self.fields)
def dispatch(self, *args, **kwargs): try: self.table = get_datatable(kwargs['table']) # There's no dataset context set here, so we use the latest year self.release = self.table.get_release(year='latest') except KeyError: raise Http404 return super(TableDetailView, self).dispatch(*args, **kwargs)
def get_ecd_educators_profile(geo, session): # These values will be filled as information becomes available. table = get_datatable('ecd_educators') ecd_educators, _ = table.get_stat_data( geo, percent=False) table = get_datatable('ecd_children_enrolled') children_enrolled, _ = table.get_stat_data( geo, percent=False) children_per_practitioner = ratio( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], ecd_educators['practitioners_for_ages_3_to_5']['values']['this']) _, children_age_3_to_5_in_area = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=['3', '4', '5'], recode=ECD_AGE_CATEGORIES) return { "children_per_practitioner": { "name": "Number of children enrolled in centres for each practitioner", "values": {"this": children_per_practitioner} }, "children_per_trained_practitioner": { "name": "Number of children enrolled in centres for each trained practitioner *", "values": {"this": None} }, "children_per_untrained_practitioner": { "name": "Number of children enrolled in centres for each untrained practitioner *", "values": {"this": None} }, "practitioners_for_ages_3_to_5": { "name": "Number of practitioners in the area for children aged 3-5.", "values": {"this": ecd_educators['practitioners_for_ages_3_to_5']['values']['this']} }, 'children_age_3_to_5_in_area': { "name": "Children (age 3-5) living in the area", "values": {"this": children_age_3_to_5_in_area} }, }
def setup_table(self): table_id = self.table_id or get_table_id(self.fields) try: self.table = get_datatable(table_id) self.stdout.write("Table for fields %s is %s" % (self.fields, self.table.id)) except KeyError: raise CommandError( "Couldn't establish which table to use for these fields. " "Have you added a FieldTable entry in wazimap_za/tables.py?\nFields: %s" % self.fields)
def get_living_environment_profile(geo_code, geo_level, session): table = get_datatable('youth').model (lighting_dep, heating_dep, cooking_dep, toilet_dep, water_dep, dwell_dep, asset_dep, emp_dep, neets_dep, prop_multid_poor, youth_mpi) = session.query( table.c.light_dep, table.c.heat_dep, table.c.cook_dep, table.c.toilet_dep, table.c.water_dep, table.c.dwell_dep, table.c.asset_dep, table.c.emp_dep, table.c.neets_dep, table.c.prop_multid_poor, table.c.youth_mpi) \ .filter(table.c.geo_level == geo_level) \ .filter(table.c.geo_code == geo_code) \ .one() final_data = { 'lighting_dep': { "name": "Proportion of youth living in households without use of electricity, gas or solar energy for lighting", "values": {"this": float(lighting_dep) or 0.0}, }, 'heating_dep': { "name": "Proportion of youth living in households without use of electricity, gas or solar energy for heating", "values": {"this": float(heating_dep) or 0.0}, }, 'cooking_dep': { "name": "Proportion of youth living in households without use of electricity, gas or solar energy for cooking", "values": {"this": float(cooking_dep) or 0.0}, }, 'toilet_dep': { "name": "Proportion of youth living in households without a flush toilet", "values": {"this": float(toilet_dep) or 0.0}, }, 'water_dep': { "name": "Proportion of youth living in households without piped water on site", "values": {"this": float(water_dep) or 0.0}, }, 'dwell_dep': { "name": "Proportion of youth living in households that are informal shacks/traditional dwellings/caravans/tents/other", "values": {"this": float(dwell_dep) or 0.0}, }, 'asset_dep': { "name": 'Proportion of youth living in households that do not own more than two of the following "small" assets: radio, TV, landline, mobile phone, bike, motorbike or refrigerator AND does not own a car or truck', "values": {"this": float(asset_dep) or 0.0}, } } return final_data
def get_ecd_budgets_profile(geo, session): table = get_datatable('ecd_grants') ecd_grants, _ = table.get_stat_data(geo, percent=False) # http://www.gov.za/services/child-care-social-benefits/child-support-grant monthly_csg = 350.00 csg = ecd_grants['child_support_grant']['values']['this'] child_support_grants = { "name": "Learners in centres receiving child support grants", "values": { "this": csg } } child_support_grants_amount = { "name": "Approximate monthly amount paid as child support grants to children in ECD centres. (Learners in centres receiving grants x Child support grant amount *)", "values": { "this": csg * monthly_csg } } # These values will be filled as information becomes available. ecd_subsidies_budgeted = { "name": "Amount budgeted for early learning subsidies", "values": { "this": None } } ecd_subsidies_paid = { "name": "Amount paid for early learning subsidies", "values": { "this": None } } children_receiving_subsidy = { "name": "Children receiving an early learning subsidy", "values": { "this": None } } return { "ecd_subsidies_budgeted": ecd_subsidies_budgeted, "ecd_subsidies_paid": ecd_subsidies_paid, "child_support_grants": child_support_grants, "child_support_grants_amount": child_support_grants_amount, "children_receiving_subsidy": children_receiving_subsidy }
def get(self, request, *args, **kwargs): try: self.geo_ids = request.GET.get('geo_ids', '').split(',') geo_version = request.GET.get('geo_version', None) self.data_geos, self.info_geos = self.get_geos( self.geo_ids, geo_version) except LocationNotFound as e: return render_json_error(e.message, 404) try: self.table_ids = request.GET.get('table_ids', '').split(',') self.tables = [get_datatable(t) for t in self.table_ids] except KeyError as e: return render_json_error('Unknown table: %s' % e.message, 404) # tables should all be from the same dataset datasets = set(t.dataset for t in self.tables) if len(datasets) > 1: return render_json_error( "All tables must belong to the same dataset.", 400) self.dataset = list(datasets)[0] self.year = kwargs['release'] if settings.WAZIMAP['latest_release_year'] == self.year: self.year = 'latest' self.available_releases = get_page_releases(self.dataset.name, self.data_geos[0], self.year, filter_releases=False) self.release = None for table in self.tables: release = table.get_release(year=self.year) if not release: return render_json_error( "No release %s for table %s." % (kwargs['release'], table.name.upper()), 400) # different? if self.release and self.release != release: return render_json_error( "All tables must have the same release.", 400) self.release = release with dataset_context(year=self.release.year): if kwargs.get('action') == 'show': return self.show(request) if kwargs.get('action') == 'download': return self.download(request)
def get_demographics_profile(geo, session): simple_v6pop = get_datatable('st_v6pop') total_users = OrderedDict() total_isps = OrderedDict() total_v6 = OrderedDict() parent = None try: total_users, _ = simple_v6pop.get_stat_data(geo, 'total_users') except Exception as e: total_users = {'total_users': {'numerators': {'this': 0}}} try: total_isps, _ = simple_v6pop.get_stat_data(geo, 'total_isps') except Exception as e: total_isps = {'total_users': {'numerators': {'this': 0}}} try: total_v6, _ = simple_v6pop.get_stat_data(geo, 'total_v6') except Exception as e: total_v6 = {'total_users': {'numerators': {'this': 0}}} try: parent = geo.parent.name except Exception as e: parent = None return { 'has_data': True, 'total_users': { "name": "People", "values": { "this": total_users['total_users']['numerators']['this'] } }, 'total_isps': { "name": "ISPs", "values": { "this": total_isps['total_isps']['numerators']['this'] } }, 'total_v6': { "name": "IPv6", "values": { "this": total_v6['total_v6']['numerators']['this'] } }, 'parent': parent }
def get_hospitals_profile(geo, session): # population group _, total_pop = get_stat_data(['population group'], geo, session, table_dataset='Census 2011') # Hospitals table = get_datatable('hospitals_2012') keys = [ 'regional_hospital', 'central_hospital', 'district_hospital', 'clinic', 'chc' ] hospital_breakdown, total_hospitals = table.get_stat_data( geo, keys, percent=False, recode={'chc': 'Community health centre'}) people_per_hospital = ratio(total_pop, total_hospitals) _, ecd_children = get_stat_data(['age in completed years'], geo, session, table_name='ageincompletedyears', only=['0', '1', '2', '3', '4', '5']) children_0_to_5_per_hospital = ratio(ecd_children, total_hospitals) return { "total_hospitals": { "name": "Hospitals / Clinics", "values": { "this": total_hospitals } }, "hospital_breakdown": hospital_breakdown, "people_per_hospital": { "name": "People living in the area for each hospital / clinic", "values": { "this": people_per_hospital } }, "children_0_to_5_per_hospital": { "name": "Children (aged 0-5 years) living in the area for each hospital / clinic", "values": { "this": children_0_to_5_per_hospital } }, }
def get_crime_profile(geo, session): child_crime, total = get_stat_data( ['crime'], geo, session, only=['Neglect and ill-treatment of children'], percent=False) table = get_datatable(get_table_id(['crime'])) return { 'dataset': table.dataset_name, 'crime_against_children': { 'name': 'Crimes of neglect and ill-treatment of children in 2014', 'values': {'this': total}, 'metadata': {'universe': 'Crimes in 2014'}, }, }
def get_crime_profile(geo_code, geo_level, session): child_crime, total = get_stat_data( ['crime'], geo_level, geo_code, session, only=['Neglect and ill-treatment of children'], percent=False) table = get_datatable(get_table_id(['crime'])) return { 'dataset': table.dataset_name, 'crime_against_children': { 'name': 'Crimes of neglect and ill-treatment of children in 2014', 'values': {'this': total}, 'metadata': {'universe': 'Crimes in 2014'}, }, }
def get_health_profile(geo_code, geo_level, session): table = get_datatable('youth').model disab_dep = session.query( table.c.disab_dep) \ .filter(table.c.geo_level == geo_level) \ .filter(table.c.geo_code == geo_code) \ .one()[0] final_data = { 'disab_dep': { "name": "Proportion of youth experiencing difficulty in one or more of the following functions: hearing, vision, communication, mobility, cognition and self-care", "values": {"this": float(disab_dep) or 0.0}, } } return final_data
def get(self, request, *args, **kwargs): try: self.geo_ids = request.GET.get('geo_ids', '').split(',') self.data_geos, self.info_geos = self.get_geos(self.geo_ids) except LocationNotFound as e: return render_json_error(e.message, 404) try: self.table_ids = request.GET.get('table_ids', '').split(',') self.tables = [get_datatable(t) for t in self.table_ids] except KeyError as e: return render_json_error('Unknown table: %s' % e.message, 404) if kwargs.get('action') == 'show': return self.show(request) if kwargs.get('action') == 'download': return self.download(request)
def get_schools_profile(geo, session): # population group _, total_pop = get_stat_data( ['population group'], geo, session, table_dataset='Census 2011') # Schools table = get_datatable('schools_2015') keys = ['primary_schools', 'combined_schools', 'intermediate_schools', 'secondary_schools'] school_breakdown, total_schools = table.get_stat_data( geo, keys, percent=False) primary_school_ages = ['6', '7', '8', '9', '10', '11', '12', '13'] secondary_school_ages = ['14', '15', '16', '17', '18'] _, total_primary_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=primary_school_ages) _, total_secondary_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=secondary_school_ages) children_per_primary_school = ratio(total_primary_children, school_breakdown['primary_schools']['values']['this']) children_per_secondary_school = ratio(total_secondary_children, school_breakdown['secondary_schools']['values']['this']) final_data = { 'total_schools': { "name": "Schools", "values": {"this": total_schools} }, "school_breakdown": school_breakdown, "children_per_primary_school": { "name": "Children (6-13 years) in the area for each primary school", "values": {"this": children_per_primary_school} }, "children_per_secondary_school": { "name": "Children (14-18 years) for each secondary school", "values": {"this": children_per_secondary_school} } } return final_data
def get_ecd_budgets_profile(geo, session): table = get_datatable('ecd_grants') ecd_grants, _ = table.get_stat_data( geo, percent=False) # http://www.gov.za/services/child-care-social-benefits/child-support-grant monthly_csg = 350.00 csg = ecd_grants['child_support_grant']['values']['this'] child_support_grants = { "name": "Learners in centres receiving child support grants", "values": {"this": csg} } child_support_grants_amount = { "name": "Approximate monthly amount paid as child support grants to children in ECD centres. (Learners in centres receiving grants x Child support grant amount *)", "values": {"this": csg * monthly_csg} } # These values will be filled as information becomes available. ecd_subsidies_budgeted = { "name": "Amount budgeted for early learning subsidies", "values": {"this": None} } ecd_subsidies_paid = { "name": "Amount paid for early learning subsidies", "values": {"this": None} } children_receiving_subsidy = { "name": "Children receiving an early learning subsidy", "values": {"this": None} } return { "ecd_subsidies_budgeted": ecd_subsidies_budgeted, "ecd_subsidies_paid": ecd_subsidies_paid, "child_support_grants": child_support_grants, "child_support_grants_amount": child_support_grants_amount, "children_receiving_subsidy": children_receiving_subsidy }
def get_election_data(geo_code, geo_level, election, session): party_data, total_valid_votes = get_stat_data( ['party'], geo_level, geo_code, session, table_dataset=election['dataset'], recode=lambda f, v: make_party_acronym(v), order_by='-total') results = { 'name': election['name'], 'party_distribution': party_data, } # voter registration and turnout table = get_datatable('voter_turnout_%s' % election['table_code']) results.update(table.get_stat_data(geo_level, geo_code, 'registered_voters', percent=False, recode={'registered_voters': 'Number of registered voters'})[0]) results.update(table.get_stat_data(geo_level, geo_code, 'total_votes', percent=True, total='registered_voters', recode={'total_votes': 'Of registered voters cast their vote'})[0]) return results
def get_disasters_profile(geo_code, geo_level, session): disasters_data = {} if geo_level != 'vdc': flood_damage_table = get_datatable('flood_damage') flood_deaths, _ = flood_damage_table.get_stat_data( geo_level, geo_code, percent=False) disasters_data = dict( is_vdc=False, area_has_data=True, flood_deaths={ 'name': 'Total (flood + heavy rainfall + landslide)', 'values': { 'this': flood_deaths['total']['values']['this'] } }, ) return disasters_data
def get_profile(geo_code, geo_level, profile_name=None): # Census data table = get_datatable('population_2011') _, total_pop = table.get_stat_data(geo_level, geo_code, percent=False) geo = geo_data.get_geography(geo_code, geo_level) population_density = None if geo.square_kms: population_density = total_pop / geo.square_kms indicator_calc = IndicatorCalculator(geo_code) indicator_calc.calculate() indicators = {} indicators['cash_at_year_end'] = indicator_calc.cash_at_year_end() indicators['cash_coverage'] = indicator_calc.cash_coverage() indicators['op_budget_diff'] = indicator_calc.op_budget_diff() indicators['cap_budget_diff'] = indicator_calc.cap_budget_diff() indicators['rep_maint_perc_ppe'] = indicator_calc.rep_maint_perc_ppe() indicators['wasteful_exp'] = indicator_calc.wasteful_exp_perc_exp() indicators['expenditure_trends'] = indicator_calc.expenditure_trends() indicators['revenue_breakdown'] = indicator_calc.revenue_breakdown() indicators['expenditure_trends'] = indicator_calc.expenditure_trends() indicators[ 'expenditure_functional_breakdown'] = indicator_calc.expenditure_functional_breakdown( ) return { 'total_population': total_pop, 'population_density': population_density, 'mayoral_staff': indicator_calc.mayoral_staff(), 'muni_contact': indicator_calc.muni_contact(), 'audit_opinions': indicator_calc.audit_opinions(), 'indicators': indicators, }
def get_hospitals_profile(geo, session): # population group _, total_pop = get_stat_data( ['population group'], geo, session, table_dataset='Census 2011') # Hospitals table = get_datatable('hospitals_2012') keys = ['regional_hospital', 'central_hospital', 'district_hospital', 'clinic', 'chc'] hospital_breakdown, total_hospitals = table.get_stat_data( geo, keys, percent=False, recode={'chc': 'Community health centre'}) people_per_hospital = ratio(total_pop, total_hospitals) _, ecd_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=['0', '1', '2', '3', '4', '5']) children_0_to_5_per_hospital = ratio(ecd_children, total_hospitals) return { "total_hospitals": { "name": "Hospitals / Clinics", "values": {"this": total_hospitals} }, "hospital_breakdown": hospital_breakdown, "people_per_hospital": { "name": "People living in the area for each hospital / clinic", "values": {"this": people_per_hospital} }, "children_0_to_5_per_hospital": { "name": "Children (aged 0-5 years) living in the area for each hospital / clinic", "values": {"this": children_0_to_5_per_hospital} }, }
def get_service_delivery_profile(geo, session): # water source water_src_data, total_wsrc = get_stat_data( ["source of water"], geo, session, recode=SHORT_WATER_SOURCE_CATEGORIES, order_by="-total", ) # water from a service provider total_water_sp = 0.0 perc_water_sp = 0.0 if current_context().get("year") == "latest": water_supplier_data, total_wspl = get_stat_data( ["supplier of water"], geo, session, recode=SHORT_WATER_SUPPLIER_CATEGORIES, order_by="-total", ) water_sp = ["Service provider", "Water scheme"] for key in water_sp: if key in water_supplier_data: total_water_sp += water_supplier_data[key]["numerators"]["this"] perc_water_sp = percent(total_water_sp, total_wspl) else: if "Service provider" in water_src_data: total_water_sp = water_src_data["Service provider"]["numerators"]["this"] perc_water_sp = percent(total_water_sp, total_wsrc) percentage_water_from_service_provider = { "name": "Are getting water from a regional or local service provider", "numerators": {"this": total_water_sp}, "values": {"this": perc_water_sp}, } # refuse disposal refuse_disp_data, total_ref = get_stat_data( ["refuse disposal"], geo, session, recode=SHORT_REFUSE_DISPOSAL_CATEGORIES, order_by="-total", ) total_ref_sp = 0.0 for k, v in refuse_disp_data.iteritems(): if k.startswith("Service provider"): total_ref_sp += v["numerators"]["this"] sp_name_2011 = ( "Are getting refuse disposal from a local authority or private company" ) sp_name_2016 = "Are getting refuse disposal from a local authority, private company or community members" percentage_ref_disp_from_service_provider = { "name": sp_name_2011 if str(current_context().get("year")) == "2011" else sp_name_2016, "numerators": {"this": total_ref_sp}, "values": {"this": percent(total_ref_sp, total_ref)}, } # electricity if geo.version == "2011" and str(current_context().get("year")) == "2011": elec_attrs = [ "electricity for cooking", "electricity for heating", "electricity for lighting", ] elec_table = get_datatable("electricityforcooking_electricityforheating_electr") objects = elec_table.get_rows_for_geo(geo, session) total_elec = 0.0 total_some_elec = 0.0 elec_access_data = { "total_all_elec": { "name": "Have electricity for everything", "numerators": {"this": 0.0}, }, "total_some_not_all_elec": { "name": "Have electricity for some things", "numerators": {"this": 0.0}, }, "total_no_elec": {"name": "No electricity", "numerators": {"this": 0.0}}, } for obj in objects: total_elec += obj.total has_some = False has_all = True for attr in elec_attrs: val = not getattr(obj, attr).startswith("no ") has_all = has_all and val has_some = has_some or val if has_some: total_some_elec += obj.total if has_all: elec_access_data["total_all_elec"]["numerators"]["this"] += obj.total elif has_some: elec_access_data["total_some_not_all_elec"]["numerators"][ "this" ] += obj.total else: elec_access_data["total_no_elec"]["numerators"]["this"] += obj.total set_percent_values(elec_access_data, total_elec) add_metadata( elec_access_data, elec_table, elec_table.get_release(current_context().get("year")), ) if current_context().get("year") == "latest": # We don't have this data for 2011 elec_access, _ = get_stat_data( ["access to electricity"], geo, session, table_universe="Population", recode=ELECTRICITY_ACCESS_RECODE, order_by="-total", ) # toilets toilet_data, total_toilet = get_stat_data( ["toilet facilities"], geo, session, exclude_zero=True, recode=COLLAPSED_TOILET_CATEGORIES, order_by="-total", ) total_flush_toilet = 0.0 total_no_toilet = 0.0 for key, data in toilet_data.iteritems(): if key.startswith("Flush") or key.startswith("Chemical"): total_flush_toilet += data["numerators"]["this"] if key == "None": total_no_toilet += data["numerators"]["this"] profile = { "water_source_distribution": water_src_data, "percentage_water_from_service_provider": percentage_water_from_service_provider, "refuse_disposal_distribution": refuse_disp_data, "percentage_ref_disp_from_service_provider": percentage_ref_disp_from_service_provider, "percentage_flush_toilet_access": { "name": "Have access to flush or chemical toilets", "numerators": {"this": total_flush_toilet}, "values": {"this": percent(total_flush_toilet, total_toilet)}, }, "percentage_no_toilet_access": { "name": "Have no access to any toilets", "numerators": {"this": total_no_toilet}, "values": {"this": percent(total_no_toilet, total_toilet)}, }, "toilet_facilities_distribution": toilet_data, } if current_context().get("year") == "latest": profile.update( { "water_supplier_distribution": water_supplier_data, "electricity_access": elec_access, "percentage_no_electricity_access": { "name": "Have no access to electricity", "numerators": elec_access["No access to electricity"]["numerators"], "values": elec_access["No access to electricity"]["values"], }, } ) if geo.version == "2011": profile.update( { "percentage_electricity_access": { "name": "Have electricity for at least one of cooking, heating or lighting", "numerators": {"this": total_some_elec}, "values": {"this": percent(total_some_elec, total_elec)}, }, "electricity_access_distribution": elec_access_data, } ) return profile
def get_schools_profile(geo, session, year): schools_dist = LOCATIONNOTFOUND region_dist = LOCATIONNOTFOUND category_dist = LOCATIONNOTFOUND top_schools_40_more = [] top_schools_40_less = [] lowest_schools_40_less = [] lowest_schools_40_more = [] gpa_dist_data = [] gender_dist = [] total_schools = 0 median = 0 reg = 'region' if geo.geo_level == "country": reg = 'region' elif geo.geo_level == "region": reg = 'district' elif geo.geo_level == "district": reg = 'ward' with dataset_context(year='2017'): try: schools_dist, total_schools = get_stat_data(['ownership'], geo=geo, table_name='secondary_school', session=session, only={ 'year_of_result': [year]}) except Exception as e: pass try: region_dist, total_schools = get_stat_data([reg], geo=geo, session=session, only={ 'year_of_result': [year]}) except Exception as e: pass try: category_dist, _ = get_stat_data(['more_than_40'], geo=geo, session=session, only={'year_of_result': [year]}) except Exception as e: pass try: gender_dist, _ = get_stat_data(['gender'], geo=geo, session=session, only={'year_of_result': [year]}) except Exception: pass try: # ownership status # school_dist_data, _ = get_stat_data('age in completed years',geo=geo, session=session, only={'year_of_result': [year]}) # Choosing sorting option # Sorting will only be done using national_rank all, as regional and district ranks are unknown for some result esp historical Base.metadata.reflect() rank_column = Base.metadata.tables[ 'secondary_school'].c.national_rank_all # Getting top for schools with more than 40 students top_schools_40_more = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like( "yes%")) \ .order_by(asc(cast(rank_column, Integer))) \ .all() # Getting top for schools with less than 40 students top_schools_40_less = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like("no%")) \ .order_by(asc(cast(rank_column, Integer))) \ .all() # Getting lowest schools with more than 40 students lowest_schools_40_more = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like( "yes%")) \ .order_by(desc(cast(rank_column, Integer))) \ .all() # Getting lowest for schools with less than 40 students lowest_schools_40_less = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like("no%")) \ .order_by(desc(cast(rank_column, Integer))) \ .all() # median gpa db_model_age = get_datatable('code_name_avg_gpa') objects = db_model_age.get_rows_for_geo(geo, session) median = calculate_median(objects, 'avg_gpa') # gpa in 1 point groups def gpa_recode(f, x): gpa = x if gpa >= 4: return '4+' bucket = 1 * (gpa / 1) return '%d-%d' % (bucket, bucket + 2) gpa_dist_data, total_schools = get_stat_data( 'avg_gpa', geo, session, table_fields=['code', 'name', 'avg_gpa'], recode=gpa_recode, exclude=['unspecified'], only={'year_of_result': [year]}) total_private = 0.0 for data in schools_dist.get('Non-Government', {}).values(): if 'numerators' in data: total_private += data['numerators']['this'] except Exception as e: pass return { 'schools_distribution': schools_dist, 'region_distribution': region_dist, 'category_distribution': category_dist, 'best_schools_more_40': top_schools_40_more, 'worst_schools_more_40': lowest_schools_40_more, 'best_schools_less_40': top_schools_40_less, 'worst_schools_less_40': lowest_schools_40_less, 'gpa_group_distribution': gpa_dist_data, 'gender_distribution': gender_dist, 'total_schools': { "name": "Schools", "values": {"this": total_schools} }, 'median_gpa': { "name": "Median GPA", "values": {"this": median}, }, }
def get_demographics_profile(geo, session): # population group pop_dist_data, total_pop = get_stat_data( ["population group"], geo, session, table_dataset="Census and Community Survey" ) # language language_data, _ = get_stat_data( ["language"], geo, session, table_dataset="Census and Community Survey", order_by="-total", ) language_most_spoken = language_data[language_data.keys()[0]] # age groups age_dist_data, total_age = get_stat_data( ["age groups in 5 years"], geo, session, table_dataset="Census and Community Survey", recode=COLLAPSED_AGE_CATEGORIES, key_order=( "0-9", "10-19", "20-29", "30-39", "40-49", "50-59", "60-69", "70-79", "80+", ), ) # sex sex_data, _ = get_stat_data( ["gender"], geo, session, table_universe="Population", table_dataset="Census and Community Survey", ) final_data = { "language_distribution": language_data, "language_most_spoken": language_most_spoken, "population_group_distribution": pop_dist_data, "age_group_distribution": age_dist_data, "sex_ratio": sex_data, "total_population": {"name": "People", "values": {"this": total_pop}}, } if geo.square_kms: final_data["population_density"] = { "name": "people per square kilometre", "values": {"this": total_pop / geo.square_kms}, } # median age/age category age_table = get_datatable("ageincompletedyears") objects = sorted( age_table.get_rows_for_geo(geo, session), key=lambda x: int(getattr(x, "age in completed years")), ) # median age median = calculate_median(objects, "age in completed years") final_data["median_age"] = {"name": "Median age", "values": {"this": median}} # age category age_dist, _ = get_stat_data( ["age in completed years"], geo, session, table_dataset="Census and Community Survey", table_name="ageincompletedyearssimplified", key_order=["Under 18", "18 to 64", "65 and over"], recode={"< 18": "Under 18", ">= 65": "65 and over"}, ) final_data["age_category_distribution"] = age_dist # citizenship citizenship_dist, _ = get_stat_data( ["citizenship"], geo, session, table_dataset="Census and Community Survey", order_by="-total", ) sa_citizen = citizenship_dist["Yes"]["numerators"]["this"] final_data["citizenship_distribution"] = citizenship_dist final_data["citizenship_south_african"] = { "name": "South African citizens", "values": {"this": percent(sa_citizen, total_pop)}, "numerators": {"this": sa_citizen}, } # migration province_of_birth_dist, _ = get_stat_data( ["province of birth"], geo, session, table_dataset="Census and Community Survey", exclude_zero=True, order_by="-total", ) final_data["province_of_birth_distribution"] = province_of_birth_dist def region_recode(field, key): if key == "Born in South Africa": return "South Africa" else: return {"Not applicable": "Other"}.get(key, key) region_of_birth_dist, _ = get_stat_data( ["region of birth"], geo, session, table_dataset="Census and Community Survey", exclude_zero=True, order_by="-total", recode=region_recode, ) if "South Africa" in region_of_birth_dist: born_in_sa = region_of_birth_dist["South Africa"]["numerators"]["this"] else: born_in_sa = 0 final_data["region_of_birth_distribution"] = region_of_birth_dist final_data["born_in_south_africa"] = { "name": "Born in South Africa", "values": {"this": percent(born_in_sa, total_pop)}, "numerators": {"this": born_in_sa}, } return final_data
def get_poverty_profile(geo, session, display_profile, comparative=False): youth_income_poor_by_age_group, _ = get_stat_data( ['income poverty', 'age group'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', percent_grouping=['age group'], slices=['Income-poor'], recode=INCOME_POVERTY_AGE_GROUP_RECODE) youth_income_poor_by_pop_group, _ = get_stat_data( ['income poverty', 'population group'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', table_name='youth_income_poverty_population_group_gender', percent_grouping=['population group'], slices=['Income-poor'], key_order={'population group': POPULATION_GROUP_ORDER}) youth_income_poor_by_gender, _ = get_stat_data( ['income poverty', 'gender'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', percent_grouping=['gender'], slices=['Income-poor'], key_order={'gender': GENDER_ORDER}) with dataset_context(year='2011'): youth_multid_poverty, _ = get_stat_data( ['multidimensionally poor'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', key_order=('Multidimensionally poor', 'Non-poor'),) youth_multid_poor_by_pop_group, _ = get_stat_data( ['multidimensionally poor', 'population group'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', table_name='youth_multidimensionally_poor_population_group_gender', percent_grouping=['population group'], slices=['Multidimensionally poor'], key_order={'population group': POPULATION_GROUP_ORDER}) youth_multid_poor_by_gender, _ = get_stat_data( ['multidimensionally poor', 'gender'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', percent_grouping=['gender'], slices=['Multidimensionally poor'], key_order={'gender': GENDER_ORDER}) youth_mpi_table = get_datatable('youth_mpi_score') youth_mpi_score, _ = youth_mpi_table.get_stat_data( geo, percent=False) youth_mpi_score['youth_mpi_score']['name'] = 'Youth MPI score (0-1)*' final_data = { 'youth_income_poor': { "name": "Of youth live in income-poor households *", "values": {"this": youth_income_poor_by_age_group['Youth (15-24)']['values']['this']} }, 'youth_income_poor_by_age_group': youth_income_poor_by_age_group, 'youth_income_poor_by_pop_group': youth_income_poor_by_pop_group, 'youth_income_poor_by_gender': youth_income_poor_by_gender, 'youth_multid_poor': { "name": "Of youth are multidimensionally poor*", "values": {"this": youth_multid_poverty['Multidimensionally poor']['values']['this']} }, 'youth_multid_poor_by_pop_group': youth_multid_poor_by_pop_group, 'youth_multid_poor_by_gender': youth_multid_poor_by_gender, 'youth_multid_poverty': youth_multid_poverty, 'youth_mpi_score_stat': youth_mpi_score['youth_mpi_score'], 'youth_mpi_score': youth_mpi_score } return final_data
def get_elections_profile(geo_code, geo_level, session): if geo_level != 'vdc': # voters by sex voter_sex_dist, total_voters = get_stat_data( 'voter_sex', geo_level, geo_code, session, table_fields=['voter_sex']) # electoral bodies local_electoral_bodies_dist, total_electoral_bodies = get_stat_data( ['local electoral body'], geo_level, geo_code, session) parties_table = get_datatable('political_parties') political_parties, _ = parties_table.get_stat_data(geo_level, geo_code, percent=False) polling_places_table = get_datatable('polling_places') polling_places, _ = polling_places_table.get_stat_data(geo_level, geo_code, percent=False) # mayoral party mayoral_party_dict, _ = get_stat_data(['mayoral party'], geo_level, geo_code, session, order_by='-total') mayoralities = sum([ mayoral_party_dict[name]['numerators']['this'] for name in [item for item in mayoral_party_dict] if name != 'metadata' ]) # deputy mayoral party deputy_mayoral_party_dict, _ = get_stat_data(['deputy mayoral party'], geo_level, geo_code, session, order_by='-total') deputy_mayoralities = sum([ deputy_mayoral_party_dict[name]['numerators']['this'] for name in [item for item in deputy_mayoral_party_dict] if name != 'metadata' ]) election_data = { 'area_has_data': True, 'is_vdc': False, 'total_voters': { 'name': 'Voters', 'values': { 'this': total_voters } }, 'voter_distribution': voter_sex_dist, 'total_electoral_bodies': { 'name': 'Electoral Bodies', 'values': { 'this': total_electoral_bodies } }, 'local_electoral_bodies_distribution': local_electoral_bodies_dist, 'political_parties': { 'name': 'Number of registered political parties', 'values': { 'this': political_parties['number of registered political parties'] ['values']['this'] } }, 'polling_places': { 'name': 'Number of polling places', 'values': { 'this': polling_places['number of polling places']['values'] ['this'] } }, 'mayoral_party_distribution': mayoral_party_dict, 'total_mayoralities': { 'name': 'Mayoralities', 'values': { 'this': mayoralities } }, 'deputy_mayoral_party_distribution': deputy_mayoral_party_dict, 'total_deputy_mayoralities': { 'name': 'Deputy mayoralities', 'values': { 'this': deputy_mayoralities } } } else: election_data = {'area_has_data': False} return election_data
def get_safety_profile(geo, session, display_profile, comparative=False): def rate_per_10k_pop(value, population): if not value: return None return value / population * 10000 final_data = {} if display_profile == 'WC': youth_pop_table = get_datatable('youth_population') youth, pop_total = youth_pop_table.get_stat_data( geo, total='total_pop', percent='False') with dataset_context(year='2015'): victims_by_age_group_per_10k_pop, total_victims_per_10k_pop = get_stat_data( ['age group'], geo, session, table_universe='Victims of crime per 10,000 population in age group', table_dataset='Police Crime Statistics 2006-2015', percent=False) accused_by_age_group_per_10k_pop, total_accused_per_10k_pop = get_stat_data( ['age group'], geo, session, table_universe='Accused of crime per 10,000 population in age group', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_victims_by_offence_per_10k_youth, _ = get_stat_data( ['type of offence'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015') youth_accused_by_offence_per_10k_youth, _ = get_stat_data( ['type of offence'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015') youth_victims_by_pop_group_per_10k, _ = get_stat_data( ['population group'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_accused_by_pop_group_per_10k, _ = get_stat_data( ['population group'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_victims_by_gender_per_10k, _ = get_stat_data( ['gender'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False, key_order=GENDER_ORDER) youth_accused_by_gender_per_10k, _ = get_stat_data( ['gender'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False, key_order=GENDER_ORDER) youth_victims_by_year, _ = get_stat_data( ['year'], geo, session, table_universe='Youth victims of crime', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_accused_by_year, _ = get_stat_data( ['year'], geo, session, table_universe='Youth accused of crimes', table_dataset='Police Crime Statistics 2006-2015', percent=False) crimes_by_year, _ = get_stat_data( ['type of crime', 'year'], geo, session, table_universe='Crimes', table_dataset='Police Crime Statistics 2006-2015', percent=False) contact_crimes_by_year = crimes_by_year['Contact crime'] contact_crimes_by_year['metadata'] = crimes_by_year['metadata'] property_crimes_by_year = crimes_by_year['Property crime'] property_crimes_by_year['metadata'] = crimes_by_year['metadata'] contact_crimes_per_10k_pop = rate_per_10k_pop(contact_crimes_by_year['2014-15']['values']['this'], pop_total) property_crimes_per_10k_pop = rate_per_10k_pop(property_crimes_by_year['2014-15']['values']['this'], pop_total) final_data = { 'youth_victims_per_10k_youth': { "name": "Youth victims of contact crime per 10,000 youth", "values": {"this": victims_by_age_group_per_10k_pop['15-24']['values']['this']} }, 'youth_accused_per_10k_youth': { "name": "Youth accused of contact crime per 10,000 youth", "values": {"this": accused_by_age_group_per_10k_pop['15-24']['values']['this']} }, 'victims_by_age_group_per_10k_pop': victims_by_age_group_per_10k_pop, 'accused_by_age_group_per_10k_pop': accused_by_age_group_per_10k_pop, 'youth_victims_by_offence_per_10k_youth': youth_victims_by_offence_per_10k_youth, 'youth_accused_by_offence_per_10k_youth': youth_accused_by_offence_per_10k_youth, 'youth_victims_by_pop_group_per_10k': youth_victims_by_pop_group_per_10k, 'youth_accused_by_pop_group_per_10k': youth_accused_by_pop_group_per_10k, 'youth_victims_by_gender_per_10k': youth_victims_by_gender_per_10k, 'youth_accused_by_gender_per_10k': youth_accused_by_gender_per_10k, 'youth_victims_by_year': youth_victims_by_year, 'youth_accused_by_year': youth_accused_by_year, 'contact_crimes_per_10k_pop': { "name": "Contact crimes per 10,000 population", "values": {"this": contact_crimes_per_10k_pop} }, 'property_crimes_per_10k_pop': { "name": "Property-related crime per 10,000 population", "values": {"this": property_crimes_per_10k_pop} }, 'contact_crimes_by_year': contact_crimes_by_year, 'property_crimes_by_year': property_crimes_by_year } return final_data
def get_safety_profile(geo, session, display_profile, comparative=False): def rate_per_10k_pop(value, population): if not value: return None return value / population * 10000 final_data = {} if display_profile == 'WC': youth_pop_table = get_datatable('youth_population') youth, pop_total = youth_pop_table.get_stat_data(geo, total='total_pop', percent='False') with dataset_context(year='2015'): victims_by_age_group_per_10k_pop, total_victims_per_10k_pop = get_stat_data( ['age group'], geo, session, table_universe= 'Victims of crime per 10,000 population in age group', table_dataset='Police Crime Statistics 2006-2015', percent=False) accused_by_age_group_per_10k_pop, total_accused_per_10k_pop = get_stat_data( ['age group'], geo, session, table_universe= 'Accused of crime per 10,000 population in age group', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_victims_by_offence_per_10k_youth, _ = get_stat_data( ['type of offence'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015') youth_accused_by_offence_per_10k_youth, _ = get_stat_data( ['type of offence'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015') youth_victims_by_pop_group_per_10k, _ = get_stat_data( ['population group'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_accused_by_pop_group_per_10k, _ = get_stat_data( ['population group'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_victims_by_gender_per_10k, _ = get_stat_data( ['gender'], geo, session, table_universe='Youth victims of crime, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False, key_order=GENDER_ORDER) youth_accused_by_gender_per_10k, _ = get_stat_data( ['gender'], geo, session, table_universe='Youth accused of crimes, per 10,000 youth', table_dataset='Police Crime Statistics 2006-2015', percent=False, key_order=GENDER_ORDER) youth_victims_by_year, _ = get_stat_data( ['year'], geo, session, table_universe='Youth victims of crime', table_dataset='Police Crime Statistics 2006-2015', percent=False) youth_accused_by_year, _ = get_stat_data( ['year'], geo, session, table_universe='Youth accused of crimes', table_dataset='Police Crime Statistics 2006-2015', percent=False) crimes_by_year, _ = get_stat_data( ['type of crime', 'year'], geo, session, table_universe='Crimes', table_dataset='Police Crime Statistics 2006-2015', percent=False) contact_crimes_by_year = crimes_by_year['Contact crime'] contact_crimes_by_year['metadata'] = crimes_by_year['metadata'] property_crimes_by_year = crimes_by_year['Property crime'] property_crimes_by_year['metadata'] = crimes_by_year['metadata'] contact_crimes_per_10k_pop = rate_per_10k_pop( contact_crimes_by_year['2014-15']['values']['this'], pop_total) property_crimes_per_10k_pop = rate_per_10k_pop( property_crimes_by_year['2014-15']['values']['this'], pop_total) final_data = { 'youth_victims_per_10k_youth': { "name": "Youth victims of contact crime per 10,000 youth", "values": { "this": victims_by_age_group_per_10k_pop['15-24']['values']['this'] } }, 'youth_accused_per_10k_youth': { "name": "Youth accused of contact crime per 10,000 youth", "values": { "this": accused_by_age_group_per_10k_pop['15-24']['values']['this'] } }, 'victims_by_age_group_per_10k_pop': victims_by_age_group_per_10k_pop, 'accused_by_age_group_per_10k_pop': accused_by_age_group_per_10k_pop, 'youth_victims_by_offence_per_10k_youth': youth_victims_by_offence_per_10k_youth, 'youth_accused_by_offence_per_10k_youth': youth_accused_by_offence_per_10k_youth, 'youth_victims_by_pop_group_per_10k': youth_victims_by_pop_group_per_10k, 'youth_accused_by_pop_group_per_10k': youth_accused_by_pop_group_per_10k, 'youth_victims_by_gender_per_10k': youth_victims_by_gender_per_10k, 'youth_accused_by_gender_per_10k': youth_accused_by_gender_per_10k, 'youth_victims_by_year': youth_victims_by_year, 'youth_accused_by_year': youth_accused_by_year, 'contact_crimes_per_10k_pop': { "name": "Contact crimes per 10,000 population", "values": { "this": contact_crimes_per_10k_pop } }, 'property_crimes_per_10k_pop': { "name": "Property-related crime per 10,000 population", "values": { "this": property_crimes_per_10k_pop } }, 'contact_crimes_by_year': contact_crimes_by_year, 'property_crimes_by_year': property_crimes_by_year } return final_data
def get_ecd_centres_profile(geo, session): children_age_groups, total_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=['3', '4', '5', '6'], recode=ECD_AGE_CATEGORIES, percent=False, key_order=['0-2', '3-5', '6-7']) children_3_to_5 = children_age_groups['3-5']['values']['this'] # This will not be needed when the column names for centres are changed. reg_recode = { 'registration_incomplete-access_denied': 'Registration incomplete', 'registration_incomplete-closed': 'Registration incomplete', 'registration_incomplete-not_found': 'Registration incomplete', } table = get_datatable('ecd_centres_by_registration') ecd_centres_by_registration, total_ecd_centres = table.get_stat_data( geo, percent=True, recode=reg_recode) table = get_datatable('ecd_children_enrolled') children_enrolled, _ = table.get_stat_data(geo, percent=False) children_enrolled['children_enrolled_age_3_to_5'][ 'name'] = 'Children enrolled in ECD centres' children_3_to_5_coverage = percent( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], children_3_to_5) children_3_to_5_per_ecd_centre = ratio(children_3_to_5, total_ecd_centres) children_3_to_5_per_ecd_centre_enrolled = ratio( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], total_ecd_centres) table = get_datatable('ecd_centres_by_type') ecd_centres_by_type, _ = table.get_stat_data(geo, key_order=[ 'community_based', 'home_based', 'school_based', 'other', 'not_specified' ]) table = get_datatable('ecd_grade_r') grade_r, _ = table.get_stat_data(geo, percent=False) grade_r['centres_with_grade_r_learners'][ 'name'] = "Centres with Grade R learners" # Currently there's no data available for these datapoints. # They are displayed in the template to promote this fact. registered_ecd_programmes = { "name": "Registered ECD programmes", "values": { "this": None }, } children_in_ecd_programmes = { "name": "Children in programmes", "values": { "this": None }, } children_in_play_groups = { "name": "Children in play groups", "values": { "this": None }, } children_grade_r_age = { "name": "Children of Grade R age (6 years)", "values": { "this": children_age_groups['6']['values']['this'] } } schools_with_grade_r_learners = { "name": "Schools with Grade R learners", "values": { "this": None } } return { "total_ecd_centres": { "name": "Number of ECD centres", "values": { "this": total_ecd_centres } }, "ecd_centres_by_registration": ecd_centres_by_registration, "ecd_centres_by_type": ecd_centres_by_type, "registered_ecd_programmes": registered_ecd_programmes, "children_enrolled_age_3_to_5": children_enrolled['children_enrolled_age_3_to_5'], "children_3_to_5_coverage": { "name": "Children living in the area who are enrolled in ECD centres. (Children enrolled in centres / Children living in the area)", "values": { "this": children_3_to_5_coverage } }, "children_3_to_5_per_ecd_centre": { "name": "Average number of children living in the area for each ECD centre", "values": { "this": children_3_to_5_per_ecd_centre } }, "children_3_to_5_per_ecd_centre_enrolled": { "name": "Average number of children enrolled in each ECD centre", "values": { "this": children_3_to_5_per_ecd_centre_enrolled } }, "children_in_ecd_programmes": children_in_ecd_programmes, "children_in_play_groups": children_in_play_groups, "children_grade_r_age": children_grade_r_age, "ecd_centres_with_grade_r_learners": grade_r['centres_with_grade_r_learners'], "schools_with_grade_r_learners": schools_with_grade_r_learners }
def get_profile(geo_code, geo_level, profile_name=None): api_query_strings = { 'aggregate': '{cube}/aggregate?aggregates={aggregate}&cut={cut}&drilldown=item.code|item.label|financial_period.period&page=0&order=financial_period.period:desc', 'facts': '{cube}/facts?&cut={cut}&fields={fields}&page=0', } # Census data table = get_datatable('population_2011') _, total_pop = table.get_stat_data(geo_level, geo_code, percent=False) line_items = { 'op_exp_actual': { 'cube': 'incexp', 'aggregate': 'amount.sum', 'cut': { 'item.code': '4600', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year', }, 'query_type': 'aggregate', }, 'op_exp_budget': { 'cube': 'incexp', 'aggregate': 'amount.sum', 'cut': { 'item.code': '4600', 'amount_type.label': 'Adjusted Budget', 'demarcation.code': str(geo_code), }, 'query_type': 'aggregate', }, 'cash_flow': { 'cube': 'cflow', 'aggregate': 'amount.sum', 'cut': { 'item.code': '4200', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year' }, 'query_type': 'aggregate', }, 'cap_exp_actual': { 'cube': 'capital', 'aggregate': 'asset_register_summary.sum', 'cut': { 'item.code': '4100', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year' }, 'query_type': 'aggregate', }, 'cap_exp_budget': { 'cube': 'capital', 'aggregate': 'asset_register_summary.sum', 'cut': { 'item.code': '4100', 'amount_type.label': 'Adjusted Budget', 'demarcation.code': str(geo_code), }, 'query_type': 'aggregate', }, 'rep_maint': { 'cube': 'repmaint', 'aggregate': 'amount.sum', 'cut': { 'item.code': '5005', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year' }, 'query_type': 'aggregate', }, 'ppe': { 'cube': 'bsheet', 'aggregate': 'amount.sum', 'cut': { 'item.code': '1300', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year', }, 'query_type': 'aggregate', }, 'invest_prop': { 'cube': 'bsheet', 'aggregate': 'amount.sum', 'cut': { 'item.code': '1401', 'amount_type.label': 'Audited Actual', 'demarcation.code': str(geo_code), 'period_length.length': 'year', }, 'query_type': 'aggregate', }, 'officials': { 'query_type': 'facts', 'cube': 'officials', 'cut': { 'municipality.demarcation_code': str(geo_code), }, 'fields': [ 'role.role', 'contact_details.title', 'contact_details.name', 'contact_details.email_address', 'contact_details.phone_number', 'contact_details.fax_number'], 'annual': False, 'value_label': '' }, 'contact_details' : { 'query_type': 'facts', 'cube': 'municipalities', 'cut': { 'municipality.demarcation_code': str(geo_code), }, 'fields': [ 'municipality.phone_number', 'municipality.street_address_1', 'municipality.street_address_2', 'municipality.street_address_3', 'municipality.street_address_4', 'municipality.url' ], 'annual': False, 'value_label': '' }, 'audit_opinions' : { 'query_type': 'facts', 'cube': 'audit_opinions', 'cut': { 'municipality.demarcation_code': str(geo_code), }, 'fields': [ 'opinion.code', 'opinion.label', 'financial_year_end.year' ], 'annual': True, 'value_label': 'opinion.label' } } api_response = {} results = defaultdict(dict) years = set() for item, params in line_items.iteritems(): if params['query_type'] == 'aggregate': url = API_URL + api_query_strings['aggregate'].format( aggregate=params['aggregate'], cube=params['cube'], cut='|'.join('{!s}:{!r}'.format(k, v) for (k, v) in params['cut'].iteritems()).replace("'", '"') ) elif params['query_type'] == 'facts': url = API_URL + api_query_strings['facts'].format( cube=params['cube'], cut='|'.join('{!s}:{!r}'.format(k, v) for (k, v) in params['cut'].iteritems()).replace("'", '"'), fields=','.join(field for field in params['fields']) ) api_response[item] = requests.get(url, verify=False).json() if params['query_type'] == 'facts': if params['annual']: results[item], years = annual_facts_from_response(item, api_response, line_items, years) else: results[item] = facts_from_response(item, api_response, line_items) else: results[item], years = aggregate_from_response(item, api_response, line_items, years) cash_coverage = OrderedDict() op_budget_diff = OrderedDict() cap_budget_diff = OrderedDict() rep_maint_perc_ppe = OrderedDict() for year in sorted(list(years), reverse=True): try: cash_coverage[year] = ratio( results['cash_flow'][year], (results['op_exp_actual'][year] / 12), 1) except KeyError: cash_coverage[year] = None try: op_budget_diff[year] = percent( (results['op_exp_budget'][year] - results['op_exp_actual'][year]), results['op_exp_budget'][year], 1) except KeyError: op_budget_diff[year] = None try: cap_budget_diff[year] = percent( (results['cap_exp_budget'][year] - results['cap_exp_actual'][year]), results['cap_exp_budget'][year]) except KeyError: cap_budget_diff[year] = None try: rep_maint_perc_ppe[year] = percent(results['rep_maint'][year], (results['ppe'][year] + results['invest_prop'][year])) except KeyError: rep_maint_perc_ppe[year] = None cash_at_year_end = OrderedDict([ (k, v) for k, v in results['cash_flow'].iteritems() ]) mayoral_staff = [] exclude_roles = ['Speaker', 'Secretary of Speaker'] for official in results['officials']: if not official['role.role'] in exclude_roles: mayoral_staff.append({ 'role': official['role.role'], 'title': official['contact_details.title'], 'name': official['contact_details.name'], 'office_phone': official['contact_details.phone_number'], 'fax_number': official['contact_details.fax_number'], 'email': official['contact_details.email_address'] }) muni_contact = results['contact_details'][0] contact_details = { 'street_address_1': muni_contact['municipality.street_address_1'], 'street_address_2': muni_contact['municipality.street_address_2'], 'street_address_3': muni_contact['municipality.street_address_3'], 'street_address_4': muni_contact['municipality.street_address_4'], 'phone_number': muni_contact['municipality.phone_number'], 'url': muni_contact['municipality.url'].lower() } audit_opinions = OrderedDict(sorted(results['audit_opinions'].items(), key=lambda t: t[0], reverse=True)) return { 'total_population': total_pop, 'cash_coverage': cash_coverage, 'op_budget_diff': op_budget_diff, 'cap_budget_diff': cap_budget_diff, 'rep_maint_perc_ppe': rep_maint_perc_ppe, 'mayoral_staff': mayoral_staff, 'contact_details': contact_details, 'audit_opinions': audit_opinions, 'cash_at_year_end': cash_at_year_end}
def handle(self, *args, **options): self.session = get_session() self.verbosity = options.get('verbosity', 0) self.table_id = options.get('table') self.geo_version = options.get('geo_version') self.store_missing_entries = options.get('store_missing_entries', False) self.dryrun = options.get('dryrun') self.geos = self.get_geos(self.geo_version) self.wc_geos = GeographyYouth.objects.filter(version='2011') self.db_tables = [] self.fields_by_table = {} self.keys_by_table = {} self.missing_keys_by_table = {} self.missing_geos_by_table = {} self.missing_geos_by_simple_table = {} self.simple_tables = {} self.field_tables = {} if self.table_id: table = get_datatable(self.table_id) if type(table) == FieldTable: self.field_tables[table.id] = table else: self.simple_tables[table.id] = table elif ONLY_CHECK_TABLES: for table_id in ONLY_CHECK_TABLES: table = get_datatable(table_id) if type(table) == FieldTable: self.field_tables[table.id] = table else: self.simple_tables[table.id] = table else: self.tables = DATA_TABLES self.field_tables = FIELD_TABLES self.simple_tables = { k: v for k, v in DATA_TABLES.iteritems() if k not in FIELD_TABLES.keys() } for table_id, table in self.field_tables.iteritems(): if table.db_table in self.db_tables: # Multiple field tables can refer to the same underlying db table continue self.db_tables.append(table.db_table) self.stdout.write("Checking table: %s" % (table.id)) self.fields_by_table[table.id] = table.fields self.keys_by_table[table_id] = self.get_table_keys( table, table.fields) rows = self.session.query(table.model).filter( table.model.geo_version == self.geo_version).all() missing_keys = self.get_missing_keys(table, rows, table.fields) if missing_keys: self.missing_keys_by_table[table.id] = missing_keys missing_geos = self.get_missing_geos(table, rows) if missing_geos: self.missing_geos_by_table[table.id] = missing_geos if self.missing_keys_by_table: self.stdout.write("Missing keys for tables:") for table in self.missing_keys_by_table.iterkeys(): self.stdout.write("%s" % (table)) if self.missing_geos_by_table: self.stdout.write("Missing geos for tables:") for table in self.missing_geos_by_table.iterkeys(): self.stdout.write("%s" % table) if self.store_missing_entries: if self.missing_keys_by_table: self.store_missing_keys() self.stdout.write("Missing keys stored in database.") if self.missing_geos_by_table: self.store_missing_geos() self.stdout.write("Missing geos stored in database.") else: self.stdout.write( "Run command with --store-missing-entries to populate missing keys with 0 and missing geos with null" ) for table_id, table in self.simple_tables.iteritems(): self.stdout.write("Checking table: %s" % (table.id)) rows = self.session.query(table.model).filter( table.model.geo_version == self.geo_version).all() missing_geos = self.get_missing_geos(table, rows) if missing_geos: self.missing_geos_by_simple_table[table.id] = missing_geos if self.missing_geos_by_simple_table: self.stdout.write("Missing geos for Simple tables:") for table in self.missing_geos_by_simple_table.iterkeys(): self.stdout.write("%s" % table) if self.store_missing_entries: self.store_simple_table_missing_geos() self.stdout.write( "Missing geos for Simple tables stored in database.") self.session.close()
def get_ecd_centres_profile(geo, session): children_age_groups, total_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=['3', '4', '5', '6'], recode=ECD_AGE_CATEGORIES, percent=False, key_order=['0-2', '3-5', '6-7']) children_3_to_5 = children_age_groups['3-5']['values']['this'] # This will not be needed when the column names for centres are changed. reg_recode = { 'registration_incomplete-access_denied': 'Registration incomplete', 'registration_incomplete-closed': 'Registration incomplete', 'registration_incomplete-not_found': 'Registration incomplete', } table = get_datatable('ecd_centres_by_registration') ecd_centres_by_registration, total_ecd_centres = table.get_stat_data( geo, percent=True, recode=reg_recode) table = get_datatable('ecd_children_enrolled') children_enrolled, _ = table.get_stat_data( geo, percent=False) children_enrolled['children_enrolled_age_3_to_5']['name'] = 'Children enrolled in ECD centres' children_3_to_5_coverage = percent( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], children_3_to_5) children_3_to_5_per_ecd_centre = ratio( children_3_to_5, total_ecd_centres) children_3_to_5_per_ecd_centre_enrolled = ratio( children_enrolled['children_enrolled_age_3_to_5']['values']['this'], total_ecd_centres) table = get_datatable('ecd_centres_by_type') ecd_centres_by_type, _ = table.get_stat_data( geo, key_order=['community_based', 'home_based', 'school_based', 'other', 'not_specified']) table = get_datatable('ecd_grade_r') grade_r, _ = table.get_stat_data( geo, percent=False) grade_r['centres_with_grade_r_learners']['name'] = "Centres with Grade R learners" # Currently there's no data available for these datapoints. # They are displayed in the template to promote this fact. registered_ecd_programmes = { "name": "Registered ECD programmes", "values": {"this": None}, } children_in_ecd_programmes = { "name": "Children in programmes", "values": {"this": None}, } children_in_play_groups = { "name": "Children in play groups", "values": {"this": None}, } children_grade_r_age = { "name": "Children of Grade R age (6 years)", "values": {"this": children_age_groups['6']['values']['this']} } schools_with_grade_r_learners = { "name": "Schools with Grade R learners", "values": {"this": None} } return { "total_ecd_centres": { "name": "Number of ECD centres", "values": {"this": total_ecd_centres} }, "ecd_centres_by_registration": ecd_centres_by_registration, "ecd_centres_by_type": ecd_centres_by_type, "registered_ecd_programmes": registered_ecd_programmes, "children_enrolled_age_3_to_5": children_enrolled['children_enrolled_age_3_to_5'], "children_3_to_5_coverage": { "name": "Children living in the area who are enrolled in ECD centres. (Children enrolled in centres / Children living in the area)", "values": {"this": children_3_to_5_coverage} }, "children_3_to_5_per_ecd_centre": { "name": "Average number of children living in the area for each ECD centre", "values": {"this": children_3_to_5_per_ecd_centre} }, "children_3_to_5_per_ecd_centre_enrolled": { "name": "Average number of children enrolled in each ECD centre", "values": {"this": children_3_to_5_per_ecd_centre_enrolled} }, "children_in_ecd_programmes": children_in_ecd_programmes, "children_in_play_groups": children_in_play_groups, "children_grade_r_age": children_grade_r_age, "ecd_centres_with_grade_r_learners": grade_r['centres_with_grade_r_learners'], "schools_with_grade_r_learners": schools_with_grade_r_learners }
def get_poverty_profile(geo, session, display_profile, comparative=False): youth_income_poor_by_age_group, _ = get_stat_data( ['income poverty', 'age group'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', percent_grouping=['age group'], slices=['Income-poor'], recode=INCOME_POVERTY_AGE_GROUP_RECODE) youth_income_poor_by_pop_group, _ = get_stat_data( ['income poverty', 'population group'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', table_name='youth_income_poverty_population_group_gender', percent_grouping=['population group'], slices=['Income-poor'], key_order={'population group': POPULATION_GROUP_ORDER}) youth_income_poor_by_gender, _ = get_stat_data( ['income poverty', 'gender'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', percent_grouping=['gender'], slices=['Income-poor'], key_order={'gender': GENDER_ORDER}) with dataset_context(year='2011'): youth_multid_poverty, _ = get_stat_data( ['multidimensionally poor'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', key_order=('Multidimensionally poor', 'Non-poor'), ) youth_multid_poor_by_pop_group, _ = get_stat_data( ['multidimensionally poor', 'population group'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', table_name='youth_multidimensionally_poor_population_group_gender', percent_grouping=['population group'], slices=['Multidimensionally poor'], key_order={'population group': POPULATION_GROUP_ORDER}) youth_multid_poor_by_gender, _ = get_stat_data( ['multidimensionally poor', 'gender'], geo, session, table_universe='Youth', table_dataset='Multidimensional poverty', percent_grouping=['gender'], slices=['Multidimensionally poor'], key_order={'gender': GENDER_ORDER}) youth_mpi_table = get_datatable('youth_mpi_score') youth_mpi_score, _ = youth_mpi_table.get_stat_data(geo, percent=False) youth_mpi_score['youth_mpi_score']['name'] = 'Youth MPI score (0-1)*' final_data = { 'youth_income_poor': { "name": "Of youth live in income-poor households *", "values": { "this": youth_income_poor_by_age_group['Youth (15-24)']['values'] ['this'] } }, 'youth_income_poor_by_age_group': youth_income_poor_by_age_group, 'youth_income_poor_by_pop_group': youth_income_poor_by_pop_group, 'youth_income_poor_by_gender': youth_income_poor_by_gender, 'youth_multid_poor': { "name": "Of youth are multidimensionally poor*", "values": { "this": youth_multid_poverty['Multidimensionally poor']['values'] ['this'] } }, 'youth_multid_poor_by_pop_group': youth_multid_poor_by_pop_group, 'youth_multid_poor_by_gender': youth_multid_poor_by_gender, 'youth_multid_poverty': youth_multid_poverty, 'youth_mpi_score_stat': youth_mpi_score['youth_mpi_score'], 'youth_mpi_score': youth_mpi_score } return final_data
def get_households_profile(geo, session): # head of household # gender head_gender_dist, total_households = get_stat_data( ["gender of household head"], geo, session, table_universe="Households", order_by="gender of household head", ) female_heads = head_gender_dist["Female"]["numerators"]["this"] # age u18_table = get_datatable("genderofheadofhouseholdunder18") objects = u18_table.get_rows_for_geo(geo, session) total_under_18 = float(sum(o[0] for o in objects)) # tenure tenure_data, _ = get_stat_data( ["tenure status"], geo, session, table_universe="Households", recode=HOUSEHOLD_OWNERSHIP_RECODE, order_by="-total", ) owned = 0 for key, data in tenure_data.iteritems(): if key.startswith("Owned"): owned += data["numerators"]["this"] # annual household income if geo.version == "2011": HOUSEHOLD_INCOME_RECODE = HOUSEHOLD_INCOME_RECODE_2011 else: HOUSEHOLD_INCOME_RECODE = COLLAPSED_ANNUAL_INCOME_CATEGORIES income_dist_data, _ = get_stat_data( ["annual household income"], geo, session, table_universe="Households", exclude=["Unspecified", "Not applicable"], recode=HOUSEHOLD_INCOME_RECODE, key_order=HOUSEHOLD_INCOME_RECODE.values(), ) # median income median = calculate_median_stat(income_dist_data) median_income = HOUSEHOLD_INCOME_ESTIMATE[median] # type of dwelling type_of_dwelling_dist, _ = get_stat_data( ["type of dwelling"], geo, session, table_universe="Households", recode=TYPE_OF_DWELLING_RECODE, order_by="-total", ) informal = type_of_dwelling_dist["Shack"]["numerators"]["this"] # household goods household_goods, _ = get_stat_data( ["household goods"], geo, session, table_universe="Households", recode=HOUSEHOLD_GOODS_RECODE, key_order=sorted(HOUSEHOLD_GOODS_RECODE.values()), ) return { "total_households": { "name": "Households", "values": {"this": total_households}, }, "owned": { "name": "Households fully owned or being paid off", "values": {"this": percent(owned, total_households)}, "numerators": {"this": owned}, }, "type_of_dwelling_distribution": type_of_dwelling_dist, "informal": { "name": "Households that are informal dwellings (shacks)", "values": {"this": percent(informal, total_households)}, "numerators": {"this": informal}, }, "tenure_distribution": tenure_data, "household_goods": household_goods, "annual_income_distribution": income_dist_data, "median_annual_income": { "name": "Average annual household income", "values": {"this": median_income}, }, "head_of_household": { "gender_distribution": head_gender_dist, "female": { "name": "Households with women as their head", "values": {"this": percent(female_heads, total_households)}, "numerators": {"this": female_heads}, }, "under_18": { "name": "Households with heads under 18 years old", "values": {"this": total_under_18}, }, }, }
def get_health_profile(geo_code, geo_level, session): if geo_level != 'vdc': # childbirth childbirth_assistance_dist, total_childbirths = get_stat_data( ['delivery type'], geo_level, geo_code, session, recode=dict(ASSISTED_CHILDBIRTH_RECODES), key_order=ASSISTED_CHILDBIRTH_RECODES.values()) births_at_health_facilities_table = get_datatable( 'births_at_health_facility') births_at_health_facilities, _ = births_at_health_facilities_table\ .get_stat_data(geo_level, geo_code, percent=False) births_with_sba_table = get_datatable('births_with_skilled_attendant') births_with_sba, _ = births_with_sba_table.get_stat_data(geo_level, geo_code, percent=False) births_with_non_sba_table = get_datatable( 'births_with_non_sba_health_worker') births_with_health_worker, _ = births_with_non_sba_table.get_stat_data( geo_level, geo_code, percent=False) maternal_childbirth_deaths_dist, _ = get_stat_data( ['maternal death in childbirth'], geo_level, geo_code, session, recode=dict(MATERNAL_CHILDBIRTH_DEATH_RECODES), key_order=MATERNAL_CHILDBIRTH_DEATH_RECODES.values()) # safe drinking water (UNDP and Open Nepal) safe_water_dist_data, _ = get_stat_data( 'safe water', geo_level, geo_code, session, recode=dict(SAFE_WATER_RECODES), key_order=SAFE_WATER_RECODES.values()) _, undp_survey_pop = get_stat_data( 'poverty', geo_level, geo_code, session, recode=dict(demographics.POVERTY_RECODES), key_order=demographics.POVERTY_RECODES.values()) total_with_safe_water = \ safe_water_dist_data['Access to Safe Water']['numerators']['this'] child_nourishment_table = get_datatable('child_nourishment') child_nourishment, _ = child_nourishment_table.get_stat_data( geo_level, geo_code, percent=False) health_facilities_data, _ = get_stat_data( ['facilitytype'], geo_level, geo_code, session, recode=dict(HEALTH_CENTER_RECODES), order_by='-total', percent=False) health_data = { 'area_has_data': True, 'is_vdc': False, 'total_assisted_childbirths': { 'name': 'Assisted Childbirths', 'values': { 'this': total_childbirths } }, 'childbirth_assistance_distribution': childbirth_assistance_dist, 'safe_water_dist': safe_water_dist_data, 'safe_water_population': { 'name': 'Estimated Population', 'values': { 'this': undp_survey_pop } }, 'percent_with_safe_water': { 'name': 'of people have access to safe drinking water', 'numerators': { 'this': total_with_safe_water }, 'values': { 'this': round(total_with_safe_water / undp_survey_pop * 100, 2) } }, 'children_malnourished': { 'name': 'of children under age five are malnourished', 'values': { 'this': child_nourishment['percent malnourished']['values']['this'] } }, 'births_at_health_facilities': { 'name': 'of all deliveries at institutions', 'values': { 'this': births_at_health_facilities[ 'percent deliveries at institutions']['values']['this'] } }, 'births_with_sba': { 'name': 'of all deliveries attended by a skilled birth ' 'attendant', 'values': { 'this': births_with_sba['percent deliveries with sba']['values'] ['this'] } }, 'births_with_health_worker': { 'name': 'of all deliveries attended by a health worker other ' 'than a skilled birth attendant', 'values': { 'this': births_with_health_worker[ 'percent deliveries with non sba health worker'] ['values']['this'] } }, 'maternal_death_in_childbirth_distribution': maternal_childbirth_deaths_dist, 'health_facilities_data': health_facilities_data } else: health_data = {'area_has_data': False} return health_data
def get_schools_profile(geo, session): # population group _, total_pop = get_stat_data(['population group'], geo, session, table_dataset='Census 2011') # Schools table = get_datatable('schools_2015') keys = [ 'primary_schools', 'combined_schools', 'intermediate_schools', 'secondary_schools' ] school_breakdown, total_schools = table.get_stat_data(geo, keys, percent=False) primary_school_ages = ['6', '7', '8', '9', '10', '11', '12', '13'] secondary_school_ages = ['14', '15', '16', '17', '18'] _, total_primary_children = get_stat_data(['age in completed years'], geo, session, table_name='ageincompletedyears', only=primary_school_ages) _, total_secondary_children = get_stat_data( ['age in completed years'], geo, session, table_name='ageincompletedyears', only=secondary_school_ages) children_per_primary_school = ratio( total_primary_children, school_breakdown['primary_schools']['values']['this']) children_per_secondary_school = ratio( total_secondary_children, school_breakdown['secondary_schools']['values']['this']) final_data = { 'total_schools': { "name": "Schools", "values": { "this": total_schools } }, "school_breakdown": school_breakdown, "children_per_primary_school": { "name": "Children (6-13 years) in the area for each primary school", "values": { "this": children_per_primary_school } }, "children_per_secondary_school": { "name": "Children (14-18 years) for each secondary school", "values": { "this": children_per_secondary_school } } } return final_data
def get_demographics_profile(geo, session, display_profile, comparative=False): youth_pop_table = get_datatable('youth_population') youth_pop, pop_total = youth_pop_table.get_stat_data(geo, total='total_pop', percent='False') youth_age_group_data, _ = get_stat_data( ['age groups in 10 years'], geo, session, table_universe='Population', table_dataset='Census and Community Survey') youth_gender_data, _ = get_stat_data( ['gender'], geo, session, table_fields=['gender', 'population group'], table_universe='Youth', table_dataset='Census and Community Survey', key_order=GENDER_ORDER) population_group_order = (POPULATION_GROUP_ORDER_2016 if current_context().get('year') == 'latest' else POPULATION_GROUP_ORDER) youth_pop_group_data, _ = get_stat_data( ['population group'], geo, session, table_fields=['population group', 'gender'], table_universe='Youth', table_dataset='Census and Community Survey', table_name='youth_population_group_gender', key_order=population_group_order) youth_language_data, _ = get_stat_data( ['language'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', order_by='-total') youth_language_most_spoken = youth_language_data[ youth_language_data.keys()[0]] youth_province_birth_data, _ = get_stat_data( ['province of birth'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=PROVINCE_ORDER) youth_region_birth_data, _ = get_stat_data( ['region of birth'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=REGION_ORDER) youth_region_birth_data['SADC']['name'] = 'SADC*' youth_citizenship_data, _ = get_stat_data( ['citizenship'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=CITIZENSHIP_ORDER) final_data = { 'total_population': { "name": "People", "values": { "this": pop_total } }, 'youth_population_total': { "name": "Youth aged 15-24", "values": { "this": youth_pop['youth_pop']['numerators']['this'] } }, 'youth_population_perc': { "name": "Of the population are youth aged 15-24", "values": { "this": youth_pop['youth_pop']['values']['this'] }, }, 'youth_population_by_age_group': youth_age_group_data, 'youth_population_by_gender': youth_gender_data, 'youth_population_by_pop_group': youth_pop_group_data, 'youth_language_most_spoken': youth_language_most_spoken, 'youth_population_by_language': youth_language_data, 'youth_born_in_sa': { "name": "Of the youth population were born in South Africa", "values": { "this": youth_region_birth_data['South Africa']['values']['this'] }, }, 'youth_by_province_of_birth': youth_province_birth_data, 'youth_by_region_of_birth': youth_region_birth_data, 'youth_sa_citizenship': { 'name': 'of the youth population are South African citizens', 'values': { 'this': youth_citizenship_data['Yes']['values']['this'] } }, 'youth_by_citizenship': youth_citizenship_data, } # The following info is displayed in the block over the map if geo.square_kms: final_data['population_density'] = { 'name': "youth per square kilometre", 'values': { "this": youth_pop['youth_pop']['numerators']['this'] / geo.square_kms } } return final_data
def get_schools_profile(geo, session, year): schools_dist = LOCATIONNOTFOUND region_dist = LOCATIONNOTFOUND category_dist = LOCATIONNOTFOUND top_schools_40_more = [] top_schools_40_less = [] lowest_schools_40_less = [] lowest_schools_40_more = [] gpa_dist_data = [] gender_dist = [] total_schools = 0 median = 0 reg = 'region' if geo.geo_level == "country": reg = 'region' elif geo.geo_level == "region": reg = 'district' elif geo.geo_level == "district": reg = 'ward' with dataset_context(year='2017'): try: schools_dist, total_schools = get_stat_data( ['ownership'], geo=geo, table_name='secondary_school', session=session, only={'year_of_result': [year]}) except Exception as e: pass try: region_dist, total_schools = get_stat_data( [reg], geo=geo, session=session, only={'year_of_result': [year]}) except Exception as e: pass try: category_dist, _ = get_stat_data(['more_than_40'], geo=geo, session=session, only={'year_of_result': [year]}) except Exception as e: pass try: gender_dist, _ = get_stat_data(['gender'], geo=geo, session=session, only={'year_of_result': [year]}) except Exception: pass try: # ownership status # school_dist_data, _ = get_stat_data('age in completed years',geo=geo, session=session, only={'year_of_result': [year]}) # Choosing sorting option # Sorting will only be done using national_rank all, as regional and district ranks are unknown for some result esp historical Base.metadata.reflect() rank_column = Base.metadata.tables[ 'secondary_school'].c.national_rank_all # Getting top for schools with more than 40 students top_schools_40_more = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like( "yes%")) \ .order_by(asc(cast(rank_column, Integer))) \ .all() # Getting top for schools with less than 40 students top_schools_40_less = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like("no%")) \ .order_by(asc(cast(rank_column, Integer))) \ .all() # Getting lowest schools with more than 40 students lowest_schools_40_more = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like( "yes%")) \ .order_by(desc(cast(rank_column, Integer))) \ .all() # Getting lowest for schools with less than 40 students lowest_schools_40_less = session.query( Base.metadata.tables['secondary_school']) \ .filter(Base.metadata.tables[ 'secondary_school'].c.geo_level == geo.geo_level) \ .filter( Base.metadata.tables['secondary_school'].c.geo_code == geo.geo_code) \ .filter( Base.metadata.tables['secondary_school'].c.year_of_result == year) \ .filter( Base.metadata.tables['secondary_school'].c.more_than_40.like("no%")) \ .order_by(desc(cast(rank_column, Integer))) \ .all() # median gpa db_model_age = get_datatable('code_name_avg_gpa') objects = db_model_age.get_rows_for_geo(geo, session) median = calculate_median(objects, 'avg_gpa') # gpa in 1 point groups def gpa_recode(f, x): gpa = x if gpa >= 4: return '4+' bucket = 1 * (gpa / 1) return '%d-%d' % (bucket, bucket + 2) gpa_dist_data, total_schools = get_stat_data( 'avg_gpa', geo, session, table_fields=['code', 'name', 'avg_gpa'], recode=gpa_recode, exclude=['unspecified'], only={'year_of_result': [year]}) total_private = 0.0 for data in schools_dist.get('Non-Government', {}).values(): if 'numerators' in data: total_private += data['numerators']['this'] except Exception as e: pass return { 'schools_distribution': schools_dist, 'region_distribution': region_dist, 'category_distribution': category_dist, 'best_schools_more_40': top_schools_40_more, 'worst_schools_more_40': lowest_schools_40_more, 'best_schools_less_40': top_schools_40_less, 'worst_schools_less_40': lowest_schools_40_less, 'gpa_group_distribution': gpa_dist_data, 'gender_distribution': gender_dist, 'total_schools': { "name": "Schools", "values": { "this": total_schools } }, 'median_gpa': { "name": "Median GPA", "values": { "this": median }, }, }
def get_demographics_profile(geo_code, geo_level, session): # population by sex sex_dist_data, total_pop = get_stat_data( 'sex', geo_level, geo_code, session, table_fields=['disability', 'sex']) if total_pop > 0: # population by disability disability_dist_data, total_disabled = get_stat_data( 'disability', geo_level, geo_code, session, table_fields=['disability', 'sex'], recode=dict(DISABILITY_RECODES), key_order=DISABILITY_RECODES.values(), exclude=['NO_DISABILITY']) demographic_data = { 'has_data': True, 'sex_ratio': sex_dist_data, 'disability_ratio': disability_dist_data, 'total_population': { "name": "People", "values": {"this": total_pop} }, 'total_disabled': { 'name': 'People', 'values': {'this': total_disabled}, }, 'percent_disabled': { 'name': 'Are disabled', 'values': {'this': round(total_disabled / float(total_pop) * 100, 2)}, }, 'is_vdc': True } if geo_level != 'vdc': income_table = get_datatable('per_capita_income') per_capita_income, _ = income_table.get_stat_data( geo_level, geo_code, percent=False) lifeexpectancy_table = get_datatable('lifeexpectancy') life_expectancy, _ = lifeexpectancy_table.get_stat_data( geo_level, geo_code, percent=False) # population projection for 2031 pop_2031_dist_data, pop_projection_2031 = get_stat_data( 'sex', geo_level, geo_code, session, table_fields=['sex'], table_name='population_projection_2031') # poverty (UNDP and Open Nepal) poverty_dist_data, undp_survey_pop = get_stat_data( 'poverty', geo_level, geo_code, session, recode=dict(POVERTY_RECODES), key_order=POVERTY_RECODES.values()) total_in_poverty = \ poverty_dist_data['In Poverty']['numerators']['this'] # language language_data, _ = get_stat_data( ['language'], geo_level, geo_code, session, order_by='-total') language_most_spoken = language_data[language_data.keys()[0]] # caste or ethnic group caste_data, _ = get_stat_data(['caste or ethnic group'], geo_level, geo_code, session, order_by='-total') most_populous_caste = caste_data[caste_data.keys()[0]] citizenship_data, _ = get_stat_data( ['citizenship', 'sex'], geo_level, geo_code, session, order_by='-total' ) citizenship_by_sex = { 'Nepal': citizenship_data['Nepal'], 'India': citizenship_data['India'], 'China': citizenship_data['China'], 'Others': citizenship_data['Others'], 'metadata': citizenship_data['metadata'] } citizenship_distribution, _ = get_stat_data( 'citizenship', geo_level, geo_code, session, order_by='-total') # age # age in 10 year groups def age_recode(f, x): age = int(x) if age >= 80: return '80+' bucket = 10 * (age / 10) return '%d-%d' % (bucket, bucket + 9) age_dist_data, _ = get_stat_data( 'age in completed years', geo_level, geo_code, session, table_fields=['age in completed years', 'sex'], recode=age_recode, table_name='age_sex') ordered_age_dist_data = OrderedDict( sorted(age_dist_data.items(), key=lambda age_range: age_range[0]) ) # age category def age_cat_recode(f, x): age = int(x.replace('+', '')) if age < 20: return 'Under 20' elif age >= 60: return '60 and over' else: return '20 to 59' age_cats, _ = get_stat_data( 'age in completed years', geo_level, geo_code, session, table_fields=['age in completed years', 'sex'], recode=age_cat_recode, table_name='age_sex') ordered_age_cats_data = OrderedDict( [('Under 20', age_cats['Under 20']), ('20 to 59', age_cats['20 to 59']), ('60 and over', age_cats['60 and over']), ('metadata', age_cats['metadata'])] ) # median age db_model_age = get_model_from_fields( ['age in completed years', 'sex'], geo_level) objects = get_objects_by_geo(db_model_age, geo_code, geo_level, session, ['age in completed years']) objects = sorted((o for o in objects if getattr(o, 'age in completed years') != 'unspecified'), key=lambda x: int(getattr(x, 'age in completed years') .replace('+', ''))) median_age = calculate_median(objects, 'age in completed years') # add non-VDC data demographic_data['is_vdc'] = False demographic_data['per_capita_income'] = { 'name': 'Per capita income in US dollars', 'values': {'this': per_capita_income['income']['values']['this']} } demographic_data['life_expectancy'] = { 'name': 'Life expectancy in years', 'values': {'this': life_expectancy['years']['values']['this']} } demographic_data['pop_2031_dist'] = pop_2031_dist_data demographic_data['pop_projection_2031'] = { "name": "Projected in 2031", "values": {"this": pop_projection_2031} } demographic_data['poverty_dist'] = poverty_dist_data demographic_data['poverty_population'] = { 'name': 'Estimated Population', 'values': {'this': undp_survey_pop} } demographic_data['percent_impoverished'] = { 'name': 'Are in poverty', 'numerators': {'this': total_in_poverty}, 'values': { 'this': round( total_in_poverty / undp_survey_pop * 100, 2)} } demographic_data['language_distribution'] = language_data demographic_data['language_most_spoken'] = language_most_spoken demographic_data['ethnic_distribution'] = caste_data demographic_data['most_populous_caste'] = most_populous_caste demographic_data['citizenship_by_sex'] = citizenship_by_sex demographic_data['citizenship_distribution'] = citizenship_distribution demographic_data['age_group_distribution'] = ordered_age_dist_data demographic_data['age_category_distribution'] = \ ordered_age_cats_data demographic_data['median_age'] = { 'name': 'Median age', 'values': {'this': median_age}, } else: demographic_data = { 'area_has_data': False } return demographic_data
def get_demographics_profile(geo, session, display_profile, comparative=False): youth_pop_table = get_datatable('youth_population') youth_pop, pop_total = youth_pop_table.get_stat_data( geo, total='total_pop', percent='False') youth_age_group_data, _ = get_stat_data( ['age groups in 10 years'], geo, session, table_universe='Population', table_dataset='Census and Community Survey') youth_gender_data, _ = get_stat_data( ['gender'], geo, session, table_fields = ['gender', 'population group'], table_universe='Youth', table_dataset='Census and Community Survey', key_order=GENDER_ORDER) population_group_order = (POPULATION_GROUP_ORDER_2016 if current_context().get('year') == 'latest' else POPULATION_GROUP_ORDER) youth_pop_group_data, _ = get_stat_data( ['population group'], geo, session, table_fields = ['population group', 'gender'], table_universe='Youth', table_dataset='Census and Community Survey', table_name='youth_population_group_gender', key_order=population_group_order) youth_language_data, _ = get_stat_data( ['language'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', order_by='-total' ) youth_language_most_spoken = youth_language_data[youth_language_data.keys()[0]] youth_province_birth_data, _ = get_stat_data( ['province of birth'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=PROVINCE_ORDER) youth_region_birth_data, _ = get_stat_data( ['region of birth'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=REGION_ORDER) youth_region_birth_data['SADC']['name'] = 'SADC*' youth_citizenship_data, _ = get_stat_data( ['citizenship'], geo, session, table_universe='Youth', table_dataset='Census and Community Survey', key_order=CITIZENSHIP_ORDER) final_data = { 'total_population': { "name": "People", "values": {"this": pop_total} }, 'youth_population_total': { "name": "Youth aged 15-24", "values": {"this": youth_pop['youth_pop']['numerators']['this']} }, 'youth_population_perc': { "name": "Of the population are youth aged 15-24", "values": {"this": youth_pop['youth_pop']['values']['this']}, }, 'youth_population_by_age_group': youth_age_group_data, 'youth_population_by_gender': youth_gender_data, 'youth_population_by_pop_group': youth_pop_group_data, 'youth_language_most_spoken': youth_language_most_spoken, 'youth_population_by_language': youth_language_data, 'youth_born_in_sa': { "name": "Of the youth population were born in South Africa", "values": {"this": youth_region_birth_data['South Africa']['values']['this']}, }, 'youth_by_province_of_birth': youth_province_birth_data, 'youth_by_region_of_birth': youth_region_birth_data, 'youth_sa_citizenship': { 'name': 'of the youth population are South African citizens', 'values': {'this': youth_citizenship_data['Yes']['values']['this']} }, 'youth_by_citizenship': youth_citizenship_data, } # The following info is displayed in the block over the map if geo.square_kms: final_data['population_density'] = { 'name': "youth per square kilometre", 'values': {"this": youth_pop['youth_pop']['numerators']['this'] / geo.square_kms} } return final_data
def get_demographics_profile(geo_code, geo_level): sex_dist_data, total_pop = get_datatable('population_data_2014').\ get_stat_data(geo_level, geo_code) return { 'sex_distribution': sex_dist_data, }