CODEWITHSUNDEEP
djangoaggregate
Regis Santos
Regis Santos

Reputation: 3749

Django annotate group by month

How to sum price group by month?

I try.

import itertools
qs = Contract.objects.values('created', 'proposal__price')
grouped = itertools.groupby(qs, lambda d: d.get('created').strftime('%Y-%m'))
data = [{'month': month, 'quant': sum(list(this_day))} for month, this_day in grouped]
print(data)

But result is no expected.

I need this similar result

[{'month': '2016-04', 'quant': 8650}, {'month': '2016-05', 'quant': 9050}]

Upvotes: 2

Views: 907

Answers (3)

Regis Santos
Regis Santos

Reputation: 3749

My code adapted from @C14L.

import itertools
# from .models import Contract
c = Contract.objects.all().values('created', 'proposal__price')
gr = itertools.groupby(c, lambda d: d.get('created').strftime('%Y-%m'))
dt = [{'month': month, 'quant': sum([x['proposal__price'] for x in quant])} for month, quant in gr]
dt

Thanks.

Upvotes: 0

Ian Price
Ian Price

Reputation: 7616

Start by extracting the month and all your values

from django.db import connection
select = {'month': connection.ops.date_trunc_sql('month', 'created')}
qs = Contract.objects.extra(select=select).values('month').annotate(my_total=Sum('proposal__price'))

Now we can use a function to group by dict keys like so: 

from itertools import groupby
from operator import attrgetter
get_y = attrgetter('month')
from collections import defaultdict, Counter
def solve(dataset, group_by_key, sum_value_keys):
    dic = defaultdict(Counter)
    for item in dataset:
        key = item[group_by_key]
        vals = {k:item[k] for k in sum_value_keys}
        dic[key].update(vals)
    return dic

Apply it to your queryset's newly annotated my_total, grouped by month:

solved = solve(qs, 'month', ['my_total'])

And you'll have grouped sums by month (month being a datetime object you can change manipulate to meet your needs):

for i in solved: print(i, ":", solved[i]['my_total'])
>>> datetime.datetime(2015, 9, 1, 0, 0, tzinfo=<UTC>) : 67614.23
>>> datetime.datetime(2015, 1, 1, 0, 0, tzinfo=<UTC>) : 54792.39

Now you can extract those values :)

Upvotes: 0

C14L
C14L

Reputation: 12548

Your this_day inside sum(list(this_day)) is a dict, so you need to build a list with a list comprehension. Example

>>> import itertools
>>> from django.contrib.auth.models import User
>>> li = User.objects.all().values('date_joined', 'username')
>>> gr = itertools.groupby(li, lambda d: d.get('date_joined').strftime('%Y-%m'))
>>> dt = [{'m': m, 'q': sum([len(x['username']) for x in q])} for m, q in gr]
>>> dt
[{'m': '2005-06', 'q': 11}, {'m': '2006-10', 'q': 22}, 
{'m': '2005-06', 'q': 179}, {'m': '2006-08', 'q': 10}, 
{'m': '2006-09', 'q': 30}, {'m': '2005-06', 'q': 74}, ... ]

Or, for your code, probably something like this

data = [{'month': month, 'quant': sum([x['proposal__price'] for x in this_day])} 
        for month, this_day in grouped]

Upvotes: 1

Related Questions