def pivot(queryset, row, column, data, aggregation=Sum, choices='auto'):
"""
Takes a queryset and pivots it. The result is a table with one record
per unique value in the `row` column, a column for each unique value in the `column` column
and values in the table aggregated by the data column.
:param queryset: a QuerySet, Model, or Manager
:param row: string, name of column that will key the rows
:param column: string, name of column that will define columns
:param data: column name or Combinable
:param aggregation: aggregation function to apply to data column
:return: ValuesQueryset
"""
queryset = _get_queryset(queryset)
column_values = get_column_values(queryset, column, choices)
annotations = _get_annotations(column, column_values, data, aggregation)
values = [row]
row_choices = get_field_choices(queryset, row)
if row_choices:
whens = (When(Q(**{row: value}),
then=Value(display_value, output_field=CharField()))
for value, display_value in row_choices)
row_display = Case(*whens)
queryset = queryset.annotate(
**{'get_' + row + '_display': row_display})
values.append('get_' + row + '_display')
return queryset.values(*values).annotate(**annotations)
def pivot(queryset,
rows,
column,
data,
aggregation=Sum,
choices='auto',
display_transform=lambda s: s,
default=None,
row_range=()):
"""
Takes a queryset and pivots it. The result is a table with one record
per unique value in the `row` column, a column for each unique value in the `column` column
and values in the table aggregated by the data column.
:param queryset: a QuerySet, Model, or Manager
:param rows: list of strings, name of columns that will key the rows
:param column: string, name of column that will define columns
:param data: column name or Combinable
:param aggregation: aggregation function to apply to data column
:param display_transform: function that takes a string and returns a string
:param default: default value to pass to the aggregate function when no record is found
:param row_range: iterable with the expected range of rows in the result
:return: ValuesQueryset
"""
values = [rows] if isinstance(rows, six.string_types) else list(rows)
queryset = _get_queryset(queryset)
column_values = get_column_values(queryset, column, choices)
annotations = _get_annotations(column,
column_values,
data,
aggregation,
display_transform,
default=default)
for row in values:
row_choices = get_field_choices(queryset, row)
if row_choices:
whens = (When(Q(**{row: value}),
then=Value(display_value, output_field=CharField()))
for value, display_value in row_choices)
row_display = Case(*whens)
queryset = queryset.annotate(
**{'get_' + row + '_display': row_display})
values.append('get_' + row + '_display')
values_list = queryset.values(*values).annotate(**annotations)
if row_range:
attributes = [value[0] for value in column_values]
values_list = default_fill(values_list,
values[0],
row_range,
fill_value=default,
fill_attributes=attributes)
return values_list
def multi_histogram(queryset, column, bins, slice_on, choices):
"""
Returns a table of histograms, one for each unique value of field in queryset.
:param queryset: A Queryet, Model, or Manager
:param column: The column we are aggregating into a histogram
:param bins: An ordered iterable of left endpoints of the bins. Must have at least two elements.
The endpoints must be a convertible to strings by force_text
:param slice_on: A field of the queryset that we are slicing the histograms on
:return: A ValuesQuerySet
"""
queryset = _get_queryset(queryset)
field_values = get_column_values(queryset, slice_on, choices)
bins = [force_text(bin) for bin in bins]
whens = tuple(
between_include_start(column, bins[k], bins[k + 1],
Value(force_text(bins[k])))
for k in range(len(bins) - 1)) + (When(
Q(**{column + '__gte': bins[-1]}), Value(force_text(bins[-1]))), )
ordering_whens = tuple(
between_include_start(column, bins[k], bins[k + 1], Value(k))
for k in range(len(bins) - 1)) + (When(
Q(**{column + '__gte': bins[-1]}), Value(len(bins) - 1)), )
bin_annotation = {
'bin': Case(*whens, output_field=CharField()),
'order': Case(*ordering_whens, output_field=IntegerField())
}
histogram_annotation = {
display_value: Count(
Case(When(Q(**{slice_on: field_value}), then=1),
output_field=IntegerField()))
for field_value, display_value in field_values
}
qs = queryset.annotate(
**bin_annotation).order_by('order').values('bin').filter(
bin__isnull=False).annotate(**histogram_annotation)
return _zero_fill(qs, bins, field_values)