def sum_in_date(x='date', y='net_sales', filter_dict=None, model='WikiItem', app=DEFAULT_APP, sort=True, limit=100000):
"""
Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts.
>>> from django.db import connection
>>> connection.close()
>>> x, y = sum_in_date(y='net_sales', filter_dict={'model__startswith': 'LC60'}, model='WikiItem', limit=5, sort=1)
>>> len(x) == len(y) == 5
True
>>> y[1] >= y[0]
True
"""
sort = sort_prefix(sort)
model = get_model(model, app)
filter_dict = filter_dict or {}
objects = model.objects.filter(**filter_dict)
# only the x values are now in the queryset (datetime information)
objects = objects.values(x)
objects = objects.annotate(y=models.Sum(y))
if sort is not None:
# FIXME: this duplicates the dict of lists sort below
objects = objects.order_by(sort + 'y')
objects = objects.all()
if limit:
objects = objects[:int(limit)]
objects = util.sod_transposed(objects)
if sort is not None:
objects = sorted_dict_of_lists(objects, field_names=['y', x], reverse=bool(sort=='-'))
if not x in objects or not 'y' in objects:
return [], []
else:
return objects[x], objects['y']
def count_in_category(x='call_type', filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=True, limit=1000):
"""
Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts.
>>> x, y = count_in_category(x='call_type', filter_dict={'model__startswith': 'LC60'}, limit=5, sort=1)
>>> len(x) == len(y) == 5
True
>>> y[1] >= y[0]
True
"""
sort = sort_prefix(sort)
model = get_model(model, app)
filter_dict = filter_dict or {}
x = fuzzy.extractOne(str(x), model._meta.get_all_field_names())[0]
objects = model.objects.filter(**filter_dict)
objects = objects.values(x)
objects = objects.annotate(y=models.Count(x))
if sort is not None:
objects = objects.order_by(sort + 'y')
objects = objects.all()
if limit:
objects = objects[:int(limit)]
objects = normalize_choices(util.sod_transposed(objects), field_name=x, app=app, human_readable=True)
if not objects:
return None
objects = consolidated_counts(objects, field_name=x, count_name='y')
if sort is not None:
objects = sorted_dict_of_lists(objects, field_names=['y', x], reverse=bool(sort))
return objects[x], objects['y']
def count_in_date(x='date_time', filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=True, limit=100000):
"""
Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts.
>>> from django.db import connection
>>> connection.close()
>>> x, y = count_in_date(x='date', filter_dict={'model__icontains': 'LC5'}, limit=5, sort=1)
>>> len(x) == len(y) == 5
True
>>> y[1] >= y[0]
True
"""
sort = sort_prefix(sort)
model = get_model(model, app)
filter_dict = filter_dict or {}
x = fuzzy.extractOne(str(x), model._meta.get_all_field_names())[0]
objects = model.objects.filter(**filter_dict)
objects = objects.extra({'date_bin_for_counting': 'date(%s)' % x})
objects = objects.values('date_bin_for_counting')
objects = objects.annotate(count_of_records_per_date_bin=models.Count('pk'))
# FIXME: this duplicates the dict of lists sort below
if sort is not None:
objects = objects.order_by(sort + 'date_bin_for_counting')
objects = objects.all()
if limit:
objects = objects[:int(limit)]
objects = util.sod_transposed(objects)
if sort is not None:
objects = sorted_dict_of_lists(objects, field_names=['count_of_records_per_date_bin', 'date_bin_for_counting'], reverse=bool(sort))
#logger.info(x)
return objects['date_bin_for_counting'], objects['count_of_records_per_date_bin']
def sequence_from_filter_spec(field_names, filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=None, limit=5000):
field_names = listify(field_names)
# TODO: enable +1 to mean increasing order on 1st column
sort_char = sort_prefix(sort)
model = get_model(model, app)
filter_dict = filter_dict or {}
objects = model.objects.filter(**filter_dict)
# only the x values are now in the queryset (datetime information)
objects = objects.values(*field_names)
if sort is not None:
# FIXME: this duplicates the dict of lists sort below
objects = objects.order_by(sort_char + field_names[-1])
objects = objects.all()
if limit:
objects = objects[:int(limit)]
objects = util.sod_transposed(objects)
if sort is not None:
if len(field_names) > 1:
objects = sorted_dict_of_lists(objects, field_names=[field_names[-1]] + field_names[:-1], reverse=bool(sort_prefix))
else:
objects = sorted_dict_of_lists(objects, field_names=field_names, reverse=bool(sort))
return tuple(objects[fn] for fn in field_names)
def format_fields(x, y, filter_dict={'model__startswith': 'LC60'}, model=DEFAULT_MODEL, app=DEFAULT_APP, count_x=None, count_y=None, order_by=None, limit=1000, aggregate=None, sum_x=None, sum_y=None):
model = get_model(model, app)
if order_by in ('x', 'y', '+x', '+y', '-x', '-y', x, y, '+' + x, '+' + y, '-' + x, '-' + y):
order_by += '_value'
if isinstance(x, basestring):
count_x = bool(count_x) or x.endswith('__count')
count_y = bool(count_y)or y.endswith('__count')
objects = model.objects.filter(**filter_dict)
if aggregate:
objects = objects.extra({'x_value': aggregate})
objects = objects.values('x_value')
x = 'x_value'
objects = objects.annotate(y_value=models.Count('pk'))
y = 'y_value'
else:
if count_x:
objects = objects.annotate(x_value=models.Count(x))
x = 'x_value'
if count_y:
objects = objects.annotate(y_value=models.Count(y))
y = 'y_value'
if sum_x:
objects = objects.annotate(x_value=models.Sum(x))
x = 'x_value'
if sum_y:
objects = objects.annotate(y_value=models.Sum(y))
y = 'y_value'
objects = objects.values(x, y)
if order_by:
objects = objects.order_by(order_by)
objects = objects.all()
if limit:
objects = objects[:limit]
return util.sod_transposed(objects)
