def calculate_comparative_rows(data, aggregation_level, data_format,
unique_id):
# for quarter we need to average summation
quarter_comparative_dict = {}
if data_format == 'quarter':
for i in range(0, len(data)):
key = data[i][unique_id]
if key not in quarter_comparative_dict.keys():
quarter_comparative_dict[key] = data[i]
else:
for k, v in data[i].items():
if k not in ['state_name', 'district_name', unique_id]:
quarter_comparative_dict[key][
k] += data[i][k] if data[i][k] else 0
else:
quarter_comparative_dict[key][k] = data[i][k]
data = []
for _, v in quarter_comparative_dict.items():
data.append(v)
for i in range(0, len(data)):
for k, v in data[i].items():
if k not in ['state_name', 'district_name', unique_id]:
data[i][k] = handle_average(v)
response = []
for i in range(0, len(data)):
response.append(
calculate_percentage_single_row(deepcopy(data[i]), False))
response = prepare_structure_comparative(deepcopy(response),
aggregation_level)
return response
def prepare_quarter_dict(data, data_period, unique_id):
latest_value_cols = [
'num_launched_districts', 'num_launched_blocks', 'num_launched_awcs',
'num_launched_states'
]
# for quarter we need to average summation
quarter_comparative_dict = {}
if data_period == 'quarter':
for i in range(0, len(data)):
key = data[i][unique_id]
if key not in quarter_comparative_dict.keys():
quarter_comparative_dict[key] = data[i]
else:
for k, v in data[i].items():
if k in latest_value_cols:
quarter_comparative_dict[key][k] = max(
quarter_comparative_dict[key][k],
data[i][k] if data[i][k] else 0)
elif k not in ['state_name', 'district_name', unique_id]:
quarter_comparative_dict[key][
k] += data[i][k] if data[i][k] else 0
else:
quarter_comparative_dict[key][k] = data[i][k]
data = []
for _, v in quarter_comparative_dict.items():
data.append(v)
for i in range(0, len(data)):
for k, v in data[i].items():
if k not in ['state_name', 'district_name', unique_id
] + latest_value_cols:
data[i][k] = handle_average(v)
return data
def calculate_aggregated_row(data, aggregation_level, data_format):
aggregated_row = {}
# for quarter we need to average summation
if data_format == 'quarter':
for k, v in data.items():
aggregated_row[k] = handle_average(v) if v else 0
else:
for k, v in data.items():
aggregated_row[k] = v if v else 0
aggregated_row = calculate_percentage_single_row(deepcopy(aggregated_row))
# rounding values
for col in [
'num_launched_districts', 'num_launched_blocks',
'num_launched_states'
]:
aggregated_row[col] = round(aggregated_row[col])
aggregated_row = prepare_structure_aggregated_row(
deepcopy(aggregated_row), aggregated_row['num_launched_states'],
aggregation_level)
return aggregated_row
def quarter_wise(self, filters, order_by, aggregation_level):
"""
:param filters: quaterwise filter (months in [month array])
:param order_by: order by columns
:return: excel_rows
"""
headers_comprehensive, cols_comprehensive, headers_summary, cols_summary, cols_to_fetch = self.row_constants
query_set = PoshanProgressReportView.objects.filter(
**filters).order_by(*order_by)
if not self.show_test:
query_set = apply_exclude(self.config['domain'], query_set)
all_cols = list(set(cols_comprehensive + cols_to_fetch))
# it used to uniquely identify the row
# for district wise data we need district id
# for statewise we need state id
unique_id = 'district_id'
if aggregation_level == 1:
unique_id = 'state_id'
cols = cols_to_fetch[:]
cols.append(unique_id) # used as key for the dict
data = query_set.values(*cols)
row_data_dict = {}
dummy_row = [0 for _ in range(0, len(all_cols))]
latest_value_cols = [
'num_launched_districts', 'num_launched_blocks',
'num_launched_awcs'
]
# update the dict
# {'unique_id': [contains the excel row with sum of col values for all months eg. m1+m2+m3]}
for row in data:
launched = True if row['num_launched_awcs'] > 0 else False
if row[unique_id] not in row_data_dict.keys():
row_data_dict[row[unique_id]] = dummy_row[:]
row_data = row_data_dict[row[unique_id]][:]
for k, v in row.items():
if k in ['state_name', 'district_name']:
row_data[all_cols.index(k)] = v
elif k in latest_value_cols:
row_data[all_cols.index(k)] = max(
row_data[all_cols.index(k)], v if
(v and launched is True) else 0)
elif k != unique_id:
row_data[all_cols.index(k)] += v if (
v and launched is True) else 0
row_data_dict[row[unique_id]] = row_data
# stores names and ids not numbers like state_name, id
named_cols = ['state_name', 'district_name', unique_id]
total_row = [0 for _ in range(0, len(all_cols))]
# calculating average and getting total row
# m1+m2+m3/3
for k, v in row_data_dict.items():
launched = True if v[all_cols.index(
'num_launched_awcs')] > 0 else False
for col in all_cols:
if col not in named_cols:
val = v[all_cols.index(col)]
if col not in latest_value_cols:
val = handle_average(val)
row_data_dict[k][all_cols.index(col)] = val
total_row[all_cols.index(col)] += round(val) if (
val and launched is True) else 0
else:
total_row[all_cols.index(col)] = 'Total'
row_data_dict['total_row'] = total_row
# calculating percentage
# percent(current col) = 100 * (actual_value(prev col) / expected_value(prev prev col))
for k, v in row_data_dict.items():
row = v[:]
row_data_dict[k] = self.__calculate_percentage_in_rows(
row, all_cols)
# marking all the unlaunched states as Not Launched
if row_data_dict[k][all_cols.index('num_launched_awcs')] == 0:
for col in all_cols:
if col not in named_cols:
row_data_dict[k][all_cols.index(col)] = 'Not Launched'
# for district wise the number of launched districts are always one
# so removing this column from report
if aggregation_level == 2:
headers_summary.remove('Number of Districts Covered')
headers_comprehensive.remove('Number of Districts Covered')
cols_summary.remove('num_launched_districts')
cols_comprehensive.remove('num_launched_districts')
if self.layout != 'comprehensive':
headers = headers_summary
for k, v in row_data_dict.items():
row_data_dict[k] = [
v[all_cols.index(column)] for column in cols_summary
]
else:
headers = headers_comprehensive
for k, v in row_data_dict.items():
row_data_dict[k] = [
v[all_cols.index(column)] for column in cols_comprehensive
]
excel_rows = [headers]
for _, v in row_data_dict.items():
excel_rows.append(v)
return excel_rows
def quarter_wise(self, filters, order_by, aggregation_level):
"""
:param filters: quaterwise filter (months in [month array])
:param order_by: order by columns
:return: excel_rows
"""
headers_comprehensive, cols_comprehensive, headers_summary, cols_summary, cols_to_fetch = self.row_constants
query_set = PoshanProgressReportView.objects.filter(
**filters).order_by(*order_by)
if not self.show_test:
query_set = apply_exclude(self.config['domain'], query_set)
all_cols = list(set(cols_comprehensive + cols_to_fetch))
# it used to uniquely identify the row
# for district wise data we need district id
# for statewise we need state id
unique_id = 'district_id'
if aggregation_level == 1:
unique_id = 'state_id'
cols = cols_to_fetch[:]
cols.append(unique_id) # used as key for the dict
data = query_set.values(*cols)
row_data_dict = {}
dummy_row = [0 for _ in range(0, len(all_cols))]
headers = headers_comprehensive
# update the dict
# {'unique_id': [contains the excel row with sum of col values for all months eg. m1+m2+m3]}
for row in data:
if row[unique_id] not in row_data_dict.keys():
row_data_dict[row[unique_id]] = dummy_row[:]
row_data = row_data_dict[row[unique_id]][:]
for k, v in row.items():
if k in ['state_name', 'district_name']:
row_data[all_cols.index(k)] = v
elif k != unique_id:
row_data[all_cols.index(k)] += v if v else 0
row_data_dict[row[unique_id]] = row_data
# calculating average
# m1+m2+m3/3
total_row = [0 for _ in range(0, len(all_cols))]
for k, v in row_data_dict.items():
for col in all_cols:
if col not in ['state_name', 'district_name', unique_id]:
val = v[all_cols.index(col)]
row_data_dict[k][all_cols.index(col)] = handle_average(val)
total_row[all_cols.index(col)] += val if val else 0
elif col in ['state_name', 'district_name']:
total_row[all_cols.index(col)] = 'Total'
row_data_dict["total_row"] = total_row
# calculating percentage
# percent(current col) = 100 * (actual_value(prev col) / expected_value(prev prev col))
for k, v in row_data_dict.items():
row = v[:]
row_data_dict[k] = self.__calculate_percentage_in_rows(
row, all_cols)
# rounding remaining values
for col in ['num_launched_districts', 'num_launched_blocks']:
row_data_dict[k][all_cols.index(col)] = round(
row_data_dict[k][all_cols.index(col)])
if self.layout != 'comprehensive':
headers = headers_summary
for k, v in row_data_dict.items():
row_data_dict[k] = [
v[all_cols.index(column)] for column in cols_summary
]
else:
for k, v in row_data_dict.items():
row_data_dict[k] = [
v[all_cols.index(column)] for column in cols_comprehensive
]
excel_rows = [headers]
for _, v in row_data_dict.items():
excel_rows.append(v)
return excel_rows