def get_education_profile(geo_code, geo_level, session):
db_model = get_model_from_fields(['highest educational level'], geo_level, table_name='highesteducationallevel20')
objects = get_objects_by_geo(db_model, geo_code, geo_level, session)
edu_dist_data = {}
get_or_higher = 0.0
fet_or_higher = 0.0
total = 0.0
for i, obj in enumerate(objects):
category_val = getattr(obj, 'highest educational level')
# increment counters
total += obj.total
if category_val in EDUCATION_GET_OR_HIGHER:
get_or_higher += obj.total
if category_val in EDUCATION_FET_OR_HIGHER:
fet_or_higher += obj.total
# add data points for category
edu_dist_data[str(i)] = {
"name": category_val,
"numerators": {"this": obj.total},
}
edu_dist_data = collapse_categories(edu_dist_data,
COLLAPSED_EDUCATION_CATEGORIES,
key_order=EDUCATION_KEY_ORDER)
edu_split_data = {
'percent_get_or_higher': {
"name": "Completed Grade 9 or higher",
"numerators": {"this": get_or_higher},
},
'percent_fet_or_higher': {
"name": "Completed Matric or higher",
"numerators": {"this": fet_or_higher},
}
}
# calculate percentages
for data in (edu_dist_data, edu_split_data):
for fields in data.values():
fields["values"] = {"this": round(fields["numerators"]["this"]
/ total * 100, 2)}
edu_dist_data['metadata'] = {'universe': 'Invididuals aged 20 and older'}
edu_split_data['metadata'] = {'universe': 'Invididuals aged 20 and older'}
add_metadata(edu_dist_data, db_model)
return {'educational_attainment_distribution': edu_dist_data,
'educational_attainment': edu_split_data}
def get_education_profile(geo, session):
db_model = get_model_from_fields(['highest educational level'], geo.geo_level, table_name='highesteducationallevel20')
objects = get_objects_by_geo(db_model, geo, session)
edu_dist_data = {}
get_or_higher = 0.0
fet_or_higher = 0.0
total = 0.0
for i, obj in enumerate(objects):
category_val = getattr(obj, 'highest educational level')
# increment counters
total += obj.total
if category_val in EDUCATION_GET_OR_HIGHER:
get_or_higher += obj.total
if category_val in EDUCATION_FET_OR_HIGHER:
fet_or_higher += obj.total
# add data points for category
edu_dist_data[str(i)] = {
"name": category_val,
"numerators": {"this": obj.total},
}
edu_dist_data = collapse_categories(edu_dist_data,
COLLAPSED_EDUCATION_CATEGORIES,
key_order=EDUCATION_KEY_ORDER)
edu_split_data = {
'percent_get_or_higher': {
"name": "Completed Grade 9 or higher",
"numerators": {"this": get_or_higher},
},
'percent_fet_or_higher': {
"name": "Completed Matric or higher",
"numerators": {"this": fet_or_higher},
}
}
# calculate percentages
for data in (edu_dist_data, edu_split_data):
for fields in data.values():
fields["values"] = {"this": round(fields["numerators"]["this"]
/ total * 100, 2)}
edu_dist_data['metadata'] = {'universe': 'Invididuals aged 20 and older'}
edu_split_data['metadata'] = {'universe': 'Invididuals aged 20 and older'}
add_metadata(edu_dist_data, db_model)
return {'educational_attainment_distribution': edu_dist_data,
'educational_attainment': edu_split_data}
def get_education_profile(geo_code, geo_level, session):
youth_completed_grade9, _ = get_stat_data(['completed grade9'], geo_level, geo_code, session, table_name='youth_age_16_to_17_gender_completed_grade9')
youth_gender_completed_grade9, _ = get_stat_data(['gender', 'completed grade9'], geo_level, geo_code, session, table_name='youth_age_16_to_17_gender_completed_grade9')
db_model_gender_completed_grade9 = get_model_from_fields(['gender'], geo_level, table_name='youth_age_16_to_17_gender_completed_grade9')
gender_completed_grade9_data = OrderedDict(( # census data refers to sex as gender
('Female', {
"name": "Female",
"values": {"this": youth_gender_completed_grade9['Female']['Yes']['values']['this']},
"numerators": {"this": youth_gender_completed_grade9['Female']['Yes']['numerators']['this']},
}),
('Male', {
"name": "Male",
"values": {"this": youth_gender_completed_grade9['Male']['Yes']['values']['this']},
"numerators": {"this": youth_gender_completed_grade9['Male']['Yes']['numerators']['this']},
}),
))
add_metadata(gender_completed_grade9_data, db_model_gender_completed_grade9)
youth_education_level, youth_pop_20_to_24 = get_stat_data(['education level'], geo_level, geo_code, session, table_name='youth_age_20_to_24_gender_education_level')
matric_or_equiv = (
youth_education_level['Matric']['numerators']['this'] +
youth_education_level['Tertiary']['numerators']['this'] +
youth_education_level['Some secondary']['numerators']['this'])
final_data = {
'youth_completed_grade9': youth_completed_grade9,
'youth_perc_completed_grade9': {
"name": "Of youth aged 16-17 have completed grade 9",
"values": {"this": youth_completed_grade9['Yes']['values']['this']},
},
'youth_gender_completed_grade9': gender_completed_grade9_data,
'youth_perc_matric': {
"name": "Of youth aged 20-24 have completed matric or matric equivalent",
"values": {"this": percent(matric_or_equiv, youth_pop_20_to_24)},
},
'youth_education_level': youth_education_level
}
return final_data
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')
if 'Service provider' in water_src_data:
total_water_sp = water_src_data['Service provider']['numerators'][
'this']
else:
total_water_sp = 0.0
# electricity
elec_attrs = [
'electricity for cooking', 'electricity for heating',
'electricity for lighting'
]
db_model_elec = get_model_from_fields(elec_attrs, geo.geo_level)
objects = get_objects_by_geo(db_model_elec, 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
for data, total in zip((elec_access_data, ), (total_elec, )):
for fields in data.values():
fields["values"] = {
"this": percent(fields["numerators"]["this"], total)
}
add_metadata(elec_access_data, db_model_elec)
# 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']
return {
'water_source_distribution': water_src_data,
'percentage_water_from_service_provider': {
"name":
"Are getting water from a regional or local service provider",
"numerators": {
"this": total_water_sp
},
"values": {
"this": percent(total_water_sp, total_wsrc)
},
},
'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,
'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,
}
def get_stat_data(
self,
fields,
geo,
session,
order_by=None,
percent=True,
total=None,
only=None,
exclude=None,
exclude_zero=False,
recode=None,
key_order=None,
percent_grouping=None,
slices=None,
year=None,
db_table=None,
):
"""
This is our primary helper routine for building a dictionary suitable for
a place's profile page, based on a statistic.
It sums over the data for ``fields`` in the database for the place identified by
``geo`` and calculates numerators and values. If multiple fields are given,
it creates nested result dictionaries.
Control the rows that are included or ignored using ``only``, ``exclude`` and ``exclude_zero``.
The field values can be recoded using ``recode`` and and re-ordered using ``key_order``.
:param fields: the census field to build stats for. Specify a list of fields to build
nested statistics. If multiple fields are specified, then the values
of parameters such as ``only``, ``exclude`` and ``recode`` will change.
These must be fields in `api.models.census.census_fields`, e.g. 'highest educational level'
:type fields: str or list
:param geo: the geograhy object
:param dbsession session: sqlalchemy session
:param str order_by: field to order by, or None for default, eg. '-total'
:param bool percent: should we calculate percentages, or just sum raw values?
:param list percent_grouping: when calculating percentages, which fields should rows be grouped by?
Default: none of them -- calculate each entry as a percentage of the
whole dataset. Ignored unless ``percent`` is ``True``.
:param int total: the total value to use for percentages, or None to total columns automatically
:param list only: only include these field values. If ``fields`` has many items, this must be a dict
mapping field names to a list of strings.
:type only: dict or list
:param exclude: ignore these field values. If ``fields`` has many items, this must be a dict
mapping field names to a list of strings. Field names are checked
before any recoding.
:type exclude: dict or list
:param bool exclude_zero: ignore fields that have a zero or null total
:param recode: function or dict to recode values of ``key_field``. If ``fields`` is a singleton,
then the keys of this dict must be the values to recode from, otherwise
they must be the field names and then the values. If this is a lambda,
it is called with the field name and its value as arguments.
:type recode: dict or lambda
:param key_order: ordering for keys in result dictionary. If ``fields`` has many items,
this must be a dict from field names to orderings.
The default ordering is determined by ``order``.
:type key_order: dict or list
:param list slices: return only a slice of the final data, by choosing a single value for each
field in the field list, as specified in the slice list.
:param str year: release year to use. None will try to use the current dataset context, and 'latest'
will use the latest release.
:param str db_table: database table and release to use. None will try
to use `year` if given, and the current dataset context.
:return: (data-dictionary, total)
"""
if not isinstance(fields, list):
fields = [fields]
n_fields = len(fields)
many_fields = n_fields > 1
if order_by is None:
order_by = fields[0]
if only is not None:
if not isinstance(only, dict):
if many_fields:
raise ValueError(
"If many fields are given, then only must be a dict. I got %s instead"
% only
)
else:
only = {fields[0]: set(only)}
if exclude is not None:
if not isinstance(exclude, dict):
if many_fields:
raise ValueError(
"If many fields are given, then exclude must be a dict. I got %s instead"
% exclude
)
else:
exclude = {fields[0]: set(exclude)}
if key_order:
if not isinstance(key_order, dict):
if many_fields:
raise ValueError(
"If many fields are given, then key_order must be a dict. I got %s instead"
% key_order
)
else:
key_order = {fields[0]: key_order}
else:
key_order = {}
if recode:
if not isinstance(recode, dict) or not many_fields:
recode = dict((f, recode) for f in fields)
# get the release and underlying database table
db_table = db_table or self.get_db_table(year=year)
objects = self.get_rows_for_geo(
geo,
session,
fields=fields,
order_by=order_by,
only=only,
exclude=exclude,
db_table=db_table,
)
if total is not None and many_fields:
raise ValueError("Cannot specify a total if many fields are given")
if total and percent_grouping:
raise ValueError("Cannot specify a total if percent_grouping is given")
if total is None and percent and self.total_column is None:
# The table doesn't support calculating percentages, but the caller
# has asked for a percentage without providing a total value to use.
# Either specify a total, or specify percent=False
raise ValueError(
"Asking for a percent on table %s that doesn't support totals and no total parameter specified."
% self.name
)
# sanity check the percent grouping
if percent:
if percent_grouping:
for field in percent_grouping:
if field not in fields:
raise ValueError(
"Field '%s' specified in percent_grouping must be in the fields list."
% field
)
# re-order percent grouping to be same order as in the field list
percent_grouping = [f for f in fields if f in percent_grouping]
else:
percent_grouping = None
root_data = OrderedDict()
running_total = 0
group_totals = {}
grand_total = -1
def get_recoded_key(recode, field, key):
recoder = recode[field]
if isinstance(recoder, dict):
return recoder.get(key, key)
else:
return recoder(field, key)
def get_data_object(obj):
""" Recurse down the list of fields and return the
final resting place for data for this stat. """
data = root_data
for i, field in enumerate(fields):
key = getattr(obj, field)
if recode and field in recode:
key = get_recoded_key(recode, field, key)
else:
key = capitalize(key)
# enforce key ordering the first time we see this field
if (not data or data.keys() == ["metadata"]) and field in key_order:
for fld in key_order[field]:
data[fld] = OrderedDict()
# ensure it's there
if key not in data:
data[key] = OrderedDict()
data = data[key]
# default values for intermediate fields
if data is not None and i < n_fields - 1:
data["metadata"] = {"name": key}
# data is now the dict where the end value is going to go
if not data:
data["name"] = key
data["numerators"] = {"this": 0.0}
return data
# run the stats for the objects
for obj in objects:
if not obj.total and exclude_zero:
continue
if (
self.denominator_key
and getattr(obj, self.fields[-1]) == self.denominator_key
):
grand_total = obj.total
# don't include the denominator key in the output
continue
# get the data dict where these values must go
data = get_data_object(obj)
if not data:
continue
if obj.total is not None:
data["numerators"]["this"] += obj.total
running_total += obj.total
else:
# TODO: sanity check this is the right thing to do for multiple fields with
# nested nulls -- does aggregating over nulls treat them as zero, or should we
# treat them as null?
data["numerators"]["this"] = None
if percent_grouping:
if obj.total is not None:
group_key = tuple()
for field in percent_grouping:
key = getattr(obj, field)
if recode and field in recode:
# Group by recoded keys
key = get_recoded_key(recode, field, key)
group_key = group_key + (key,)
data["_group_key"] = group_key
group_totals[group_key] = group_totals.get(group_key, 0) + obj.total
if grand_total == -1:
grand_total = running_total if total is None else total
# add in percentages
def calc_percent(data):
for key, data in data.items():
if not key == 'metadata':
if 'numerators' in data:
if percent:
if "_group_key" in data:
total = group_totals[data.pop("_group_key")]
else:
total = grand_total
if (
total is not None
and data["numerators"]["this"] is not None
):
perc = (
0
if total == 0
else (data["numerators"]["this"] / total * 100)
)
data["values"] = {"this": round(perc, 2)}
else:
data["values"] = {"this": None}
else:
data["values"] = dict(data["numerators"])
data["numerators"]["this"] = None
else:
calc_percent(data)
calc_percent(root_data)
if slices:
for v in slices:
root_data = root_data[v]
add_metadata(root_data, self, db_table.active_release)
return root_data, grand_total
def get_stat_data(
self,
geo,
fields=None,
key_order=None,
percent=True,
total=None,
recode=None,
year=None,
):
""" Get a data dictionary for a place from this table.
This fetches the values for each column in this table and returns a data
dictionary for those values, with appropriate names and metadata.
:param geo: the geography
:param str or list fields: the columns to fetch stats for. By default, all columns except
geo-related and the total column (if any) are used.
:param str key_order: explicit ordering of (recoded) keys, or None for the default order.
Default order is the order in +fields+ if given, otherwise
it's the natural column order from the DB.
:param bool percent: should we calculate percentages, or just include raw values?
:param int total: the total value to use for percentages, name of a
field, or None to use the sum of all retrieved fields (default)
:param dict recode: map from field names to strings to recode column names. Many fields
can be recoded to the same thing, their values will be summed.
:param str year: release year to use. None will try to use the current dataset context, and 'latest'
will use the latest release.
:return: (data-dictionary, total)
"""
db_table = self.get_db_table(year=year or current_context().get("year"))
model = db_table.model
columns = self.columns(db_table)
session = get_session()
try:
if fields is not None and not isinstance(fields, list):
fields = [fields]
if fields:
for f in fields:
if f not in columns:
raise ValueError(
"Invalid field/column '%s' for table '%s'. Valid columns are: %s"
% (f, self.id, ", ".join(columns.keys()))
)
else:
fields = columns.keys()
if self.total_column:
fields.remove(self.total_column)
recode = recode or {}
if recode:
# change lambda to dicts
if not isinstance(recode, dict):
recode = {f: recode(f) for f in fields}
# is the total column valid?
if isinstance(total, basestring) and total not in columns:
raise ValueError(
"Total column '%s' isn't one of the columns for table '%s'. Valid columns are: %s"
% (total, self.id, ", ".join(columns.keys()))
)
# table columns to fetch
cols = [model.__table__.columns[c] for c in fields]
if (
total is not None
and isinstance(total, basestring)
and total not in cols
):
cols.append(total)
# do the query. If this returns no data, row is None
row = (
session.query(*cols)
.filter(
model.geo_level == geo.geo_level,
model.geo_code == geo.geo_code,
model.geo_version == geo.version,
)
.first()
)
if row is None:
row = ZeroRow()
# what's our denominator?
if total is None:
# sum of all columns
total = sum(getattr(row, f) or 0 for f in fields)
elif isinstance(total, basestring):
total = getattr(row, total)
# Now build a data dictionary based on the columns in +row+.
# Multiple columns may be recoded into one, so we have to
# accumulate values as we go.
results = OrderedDict()
key_order = (
key_order or fields
) # default key order is just the list of fields
for field in key_order:
val = getattr(row, field) or 0
# recode the key for this field, default is to keep it the same
key = recode.get(field, field)
# set the recoded field name, noting that the key may already
# exist if another column recoded to it
field_info = results.setdefault(
key, {"name": recode.get(field, columns[field]["name"])}
)
if percent:
# sum up existing values, if any
val = val + field_info.get("numerators", {}).get("this", 0)
field_info["values"] = {"this": p(val, total)}
field_info["numerators"] = {"this": val}
else:
# sum up existing values, if any
val = val + field_info.get("values", {}).get("this", 0)
field_info["values"] = {"this": val}
add_metadata(results, self, db_table.active_release)
return results, total
finally:
session.close()
def get_stat_data(self, geo_level, geo_code, fields=None, key_order=None,
percent=True, total=None, recode=None):
""" Get a data dictionary for a place from this table.
This fetches the values for each column in this table and returns a data
dictionary for those values, with appropriate names and metadata.
:param str geo_level: the geographical level
:param str geo_code: the geographical code
:param str or list fields: the columns to fetch stats for. By default, all columns except
geo-related and the total column (if any) are used.
:param str key_order: explicit ordering of (recoded) keys, or None for the default order.
Default order is the order in +fields+ if given, otherwise
it's the natural column order from the DB.
:param bool percent: should we calculate percentages, or just include raw values?
:param int total: the total value to use for percentages, name of a
field, or None to use the sum of all retrieved fields (default)
:param dict recode: map from field names to strings to recode column names. Many fields
can be recoded to the same thing, their values will be summed.
:return: (data-dictionary, total)
"""
session = get_session()
try:
if fields is not None and not isinstance(fields, list):
fields = [fields]
if fields:
for f in fields:
if f not in self.columns:
raise ValueError("Invalid field/column '%s' for table '%s'. Valid columns are: %s" % (
f, self.id, ', '.join(self.columns.keys())))
else:
fields = self.columns.keys()
if self.total_column:
fields.remove(self.total_column)
recode = recode or {}
if recode:
# change lambda to dicts
if not isinstance(recode, dict):
recode = {f: recode(f) for f in fields}
# is the total column valid?
if isinstance(total, basestring) and total not in self.columns:
raise ValueError("Total column '%s' isn't one of the columns for table '%s'. Valid columns are: %s" % (
total, self.id, ', '.join(self.columns.keys())))
# table columns to fetch
cols = [self.model.columns[c] for c in fields]
if total is not None and isinstance(total, basestring) and total not in cols:
cols.append(total)
# do the query. If this returns no data, row is None
row = session\
.query(*cols)\
.filter(self.model.c.geo_level == geo_level,
self.model.c.geo_code == geo_code)\
.first()
if row is None:
row = ZeroRow()
# what's our denominator?
if total is None:
# sum of all columns
total = sum(getattr(row, f) or 0 for f in fields)
elif isinstance(total, basestring):
total = getattr(row, total)
# Now build a data dictionary based on the columns in +row+.
# Multiple columns may be recoded into one, so we have to
# accumulate values as we go.
results = OrderedDict()
key_order = key_order or fields # default key order is just the list of fields
for field in key_order:
val = getattr(row, field) or 0
# recode the key for this field, default is to keep it the same
key = recode.get(field, field)
# set the recoded field name, noting that the key may already
# exist if another column recoded to it
field_info = results.setdefault(key, {'name': recode.get(field, self.columns[field]['name'])})
if percent:
# sum up existing values, if any
val = val + field_info.get('numerators', {}).get('this', 0)
field_info['values'] = {'this': p(val, total)}
field_info['numerators'] = {'this': val}
else:
# sum up existing values, if any
val = val + field_info.get('values', {}).get('this', 0)
field_info['values'] = {'this': val}
add_metadata(results, self)
return results, total
finally:
session.close()
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')
if 'Service provider' in water_src_data:
total_water_sp = water_src_data['Service provider']['numerators']['this']
else:
total_water_sp = 0.0
# electricity
elec_attrs = ['electricity for cooking',
'electricity for heating',
'electricity for lighting']
db_model_elec = get_model_from_fields(elec_attrs, geo.geo_level)
objects = get_objects_by_geo(db_model_elec, 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
for data, total in zip((elec_access_data,), (total_elec,)):
for fields in data.values():
fields["values"] = {"this": percent(fields["numerators"]["this"], total)}
add_metadata(elec_access_data, db_model_elec)
# 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']
return {
'water_source_distribution': water_src_data,
'percentage_water_from_service_provider': {
"name": "Are getting water from a regional or local service provider",
"numerators": {"this": total_water_sp},
"values": {"this": percent(total_water_sp, total_wsrc)},
},
'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,
'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,
}
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')
if 'Service provider' in water_src_data:
total_water_sp = water_src_data['Service provider']['numerators']['this']
else:
total_water_sp = 0.0
# refuse disposal
db_model_ref = get_model_from_fields(['refuse disposal'], geo.geo_level)
objects = get_objects_by_geo(db_model_ref, geo, session, order_by='-total')
refuse_disp_data = OrderedDict()
total_ref = 0.0
total_ref_sp = 0.0
for obj in objects:
attr = getattr(obj, 'refuse disposal')
disp = SHORT_REFUSE_DISPOSAL_CATEGORIES[attr]
refuse_disp_data[disp] = {
"name": disp,
"numerators": {"this": obj.total},
}
total_ref += obj.total
if attr.startswith('Removed by local authority'):
total_ref_sp += obj.total
set_percent_values(refuse_disp_data, total_ref)
add_metadata(refuse_disp_data, db_model_ref)
# electricity
if geo.version == '2011':
elec_attrs = ['electricity for cooking',
'electricity for heating',
'electricity for lighting']
db_model_elec = get_model_from_fields(elec_attrs, geo.geo_level)
objects = get_objects_by_geo(db_model_elec, 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, db_model_elec)
# 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': {
"name": "Are getting water from a regional or local service provider",
"numerators": {"this": total_water_sp},
"values": {"this": percent(total_water_sp, total_wsrc)},
},
'refuse_disposal_distribution': refuse_disp_data,
'percentage_ref_disp_from_service_provider': {
"name": "Are getting refuse disposal from a local authority or private company",
"numerators": {"this": total_ref_sp},
"values": {"this": percent(total_ref_sp, total_ref)},
},
'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 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_demographics_profile(geo_code, geo_level, session):
# population group
pop_dist_data, total_pop = get_stat_data(
['population group'], geo_level, geo_code, session)
# language
language_data, _ = get_stat_data(
['language'], geo_level, geo_code, session, 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_level, geo_code, session,
table_name='agegroupsin5years',
recode=COLLAPSED_AGE_CATEGORIES,
key_order=('0-9', '10-19',
'20-29', '30-39',
'40-49', '50-59',
'60-69', '70-79',
'80+'))
# sex
db_model_sex = get_model_from_fields(['gender'], geo_level, table_name='gender')
query = session.query(func.sum(db_model_sex.total)) \
.filter(db_model_sex.gender == 'Male')
query = query.filter(db_model_sex.geo_code == geo_code)
total_male = query.one()[0]
sex_data = OrderedDict(( # census data refers to sex as gender
('Female', {
"name": "Female",
"values": {"this": round((total_pop - total_male) / total_pop * 100, 2)},
"numerators": {"this": total_pop - total_male},
}),
('Male', {
"name": "Male",
"values": {"this": round(total_male / total_pop * 100, 2)},
"numerators": {"this": total_male},
}),
))
add_metadata(sex_data, db_model_sex)
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},
}
}
geo = geo_data.get_geography(geo_code, geo_level)
if geo.square_kms:
final_data['population_density'] = {
'name': "people per square kilometre",
'values': {"this": total_pop / geo.square_kms},
}
# median age/age category
db_model_age = get_model_from_fields(
['age in completed years'], geo_level,
table_name='ageincompletedyears'
)
objects = sorted(
get_objects_by_geo(db_model_age, geo_code, geo_level, 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_level, geo_code, session,
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_level, geo_code, session,
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_level, geo_code, session,
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 key
region_of_birth_dist, _ = get_stat_data(
['region of birth'], geo_level, geo_code, session,
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_stat_data(self, fields, geo, session, order_by=None,
percent=True, total=None, only=None, exclude=None, exclude_zero=False,
recode=None, key_order=None, percent_grouping=None, slices=None, year=None,
db_table=None):
"""
This is our primary helper routine for building a dictionary suitable for
a place's profile page, based on a statistic.
It sums over the data for ``fields`` in the database for the place identified by
``geo`` and calculates numerators and values. If multiple fields are given,
it creates nested result dictionaries.
Control the rows that are included or ignored using ``only``, ``exclude`` and ``exclude_zero``.
The field values can be recoded using ``recode`` and and re-ordered using ``key_order``.
:param fields: the census field to build stats for. Specify a list of fields to build
nested statistics. If multiple fields are specified, then the values
of parameters such as ``only``, ``exclude`` and ``recode`` will change.
These must be fields in `api.models.census.census_fields`, e.g. 'highest educational level'
:type fields: str or list
:param geo: the geograhy object
:param dbsession session: sqlalchemy session
:param str order_by: field to order by, or None for default, eg. '-total'
:param bool percent: should we calculate percentages, or just sum raw values?
:param list percent_grouping: when calculating percentages, which fields should rows be grouped by?
Default: none of them -- calculate each entry as a percentage of the
whole dataset. Ignored unless ``percent`` is ``True``.
:param int total: the total value to use for percentages, or None to total columns automatically
:param list only: only include these field values. If ``fields`` has many items, this must be a dict
mapping field names to a list of strings.
:type only: dict or list
:param exclude: ignore these field values. If ``fields`` has many items, this must be a dict
mapping field names to a list of strings. Field names are checked
before any recoding.
:type exclude: dict or list
:param bool exclude_zero: ignore fields that have a zero or null total
:param recode: function or dict to recode values of ``key_field``. If ``fields`` is a singleton,
then the keys of this dict must be the values to recode from, otherwise
they must be the field names and then the values. If this is a lambda,
it is called with the field name and its value as arguments.
:type recode: dict or lambda
:param key_order: ordering for keys in result dictionary. If ``fields`` has many items,
this must be a dict from field names to orderings.
The default ordering is determined by ``order``.
:type key_order: dict or list
:param list slices: return only a slice of the final data, by choosing a single value for each
field in the field list, as specified in the slice list.
:param str year: release year to use. None will try to use the current dataset context, and 'latest'
will use the latest release.
:param str db_table: database table and release to use. None will try
to use `year` if given, and the current dataset context.
:return: (data-dictionary, total)
"""
if not isinstance(fields, list):
fields = [fields]
n_fields = len(fields)
many_fields = n_fields > 1
if order_by is None:
order_by = fields[0]
if only is not None:
if not isinstance(only, dict):
if many_fields:
raise ValueError("If many fields are given, then only must be a dict. I got %s instead" % only)
else:
only = {fields[0]: set(only)}
if exclude is not None:
if not isinstance(exclude, dict):
if many_fields:
raise ValueError("If many fields are given, then exclude must be a dict. I got %s instead" % exclude)
else:
exclude = {fields[0]: set(exclude)}
if key_order:
if not isinstance(key_order, dict):
if many_fields:
raise ValueError("If many fields are given, then key_order must be a dict. I got %s instead" % key_order)
else:
key_order = {fields[0]: key_order}
else:
key_order = {}
if recode:
if not isinstance(recode, dict) or not many_fields:
recode = dict((f, recode) for f in fields)
# get the release and underlying database table
db_table = db_table or self.get_db_table(year=year)
objects = self.get_rows_for_geo(geo, session, fields=fields, order_by=order_by, only=only, exclude=exclude, db_table=db_table)
if total is not None and many_fields:
raise ValueError("Cannot specify a total if many fields are given")
if total and percent_grouping:
raise ValueError("Cannot specify a total if percent_grouping is given")
if total is None and percent and self.total_column is None:
# The table doesn't support calculating percentages, but the caller
# has asked for a percentage without providing a total value to use.
# Either specify a total, or specify percent=False
raise ValueError("Asking for a percent on table %s that doesn't support totals and no total parameter specified." % self.name)
# sanity check the percent grouping
if percent:
if percent_grouping:
for field in percent_grouping:
if field not in fields:
raise ValueError("Field '%s' specified in percent_grouping must be in the fields list." % field)
# re-order percent grouping to be same order as in the field list
percent_grouping = [f for f in fields if f in percent_grouping]
else:
percent_grouping = None
root_data = OrderedDict()
running_total = 0
group_totals = {}
grand_total = -1
def get_recoded_key(recode, field, key):
recoder = recode[field]
if isinstance(recoder, dict):
return recoder.get(key, key)
else:
return recoder(field, key)
def get_data_object(obj):
""" Recurse down the list of fields and return the
final resting place for data for this stat. """
data = root_data
for i, field in enumerate(fields):
key = getattr(obj, field)
if recode and field in recode:
key = get_recoded_key(recode, field, key)
else:
key = capitalize(key)
# enforce key ordering the first time we see this field
if (not data or data.keys() == ['metadata']) and field in key_order:
for fld in key_order[field]:
data[fld] = OrderedDict()
# ensure it's there
if key not in data:
data[key] = OrderedDict()
data = data[key]
# default values for intermediate fields
if data is not None and i < n_fields - 1:
data['metadata'] = {'name': key}
# data is now the dict where the end value is going to go
if not data:
data['name'] = key
data['numerators'] = {'this': 0.0}
return data
# run the stats for the objects
for obj in objects:
if not obj.total and exclude_zero:
continue
if self.denominator_key and getattr(obj, self.fields[-1]) == self.denominator_key:
grand_total = obj.total
# don't include the denominator key in the output
continue
# get the data dict where these values must go
data = get_data_object(obj)
if not data:
continue
if obj.total is not None:
data['numerators']['this'] += obj.total
running_total += obj.total
else:
# TODO: sanity check this is the right thing to do for multiple fields with
# nested nulls -- does aggregating over nulls treat them as zero, or should we
# treat them as null?
data['numerators']['this'] = None
if percent_grouping:
if obj.total is not None:
group_key = tuple()
for field in percent_grouping:
key = getattr(obj, field)
if recode and field in recode:
# Group by recoded keys
key = get_recoded_key(recode, field, key)
group_key = group_key + (key,)
data['_group_key'] = group_key
group_totals[group_key] = group_totals.get(group_key, 0) + obj.total
if grand_total == -1:
grand_total = running_total if total is None else total
# add in percentages
def calc_percent(data):
for key, data in data.items():
if not key == 'metadata':
if 'numerators' in data:
if percent:
if '_group_key' in data:
total = group_totals[data.pop('_group_key')]
else:
total = grand_total
if total is not None and data['numerators']['this'] is not None:
perc = 0 if total == 0 else (data['numerators']['this'] / total * 100)
data['values'] = {'this': round(perc, 2)}
else:
data['values'] = {'this': None}
else:
data['values'] = dict(data['numerators'])
data['numerators']['this'] = None
else:
calc_percent(data)
calc_percent(root_data)
if slices:
for v in slices:
root_data = root_data[v]
add_metadata(root_data, self, db_table.active_release)
return root_data, grand_total
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