group by week for each year in django

0
votes

I did find this comment from @HenryHeikkinen very usefull How to group by week in postgresql

So I did correct my code. But still it is not ok. I did find some situation when it is not working correctly. When year is chaning during first week.

My data model keeps some value for each day in year. I want to group data by week and compare several years.

year 2018 is interesting 1.1.2018 is monday so first week of 2018 start at that day. last week of 2018 is 52 and starting at 24.12.2018

31.12.2018 is monday next week, but it is first week of 2019 and thats OK. But grouping by week, the result for that week (week 1 of year 2019) has week date = 31.12.2018, so extracting year info from that gives 2018 instead 2019 and thats make duplicity for week 1 of 2018.

Year 2019 has that problem at beging and at the end.

  • Week 1 of 2019 start at 31.12.2018
  • Week 1 of 2020 start at 30.12.2019

here is my django code which return week data

self.dataday_set.annotate(ww=TruncWeek('date')) \
                            .values('ww') \
                            .annotate(consumption=Sum('consumption'), amount=Max('amount'), w=ExtractWeek('ww'), r=ExtractYear('ww')) \
                            .order_by('-ww')

here is output section between 2017 and 2018, which is OK

{'ww': datetime.date(2018, 1, 8), 'consumption': Decimal('42.39700'), 'amount': Decimal('12878.230'), 'w': 2, 'r': 2018}
{'ww': datetime.date(2018, 1, 1), 'consumption': Decimal('56.60000'), 'amount': Decimal('12835.833'), 'w': 1, 'r': 2018}
{'ww': datetime.date(2017, 12, 25), 'consumption': Decimal('45.95800'), 'amount': Decimal('12779.233'), 'w': 52, 'r': 2017}
{'ww': datetime.date(2017, 12, 18), 'consumption': Decimal('43.71600'), 'amount': Decimal('12733.275'), 'w': 51, 'r': 2017}

section between 2018 and 2019, and as you can see week 1 of 2019 is listed as week of 2018

{'ww': datetime.date(2019, 1, 14), 'consumption': Decimal('40.03400'), 'amount': Decimal('14911.079'), 'w': 3, 'r': 2019}
{'ww': datetime.date(2019, 1, 7), 'consumption': Decimal('47.14400'), 'amount': Decimal('14871.045'), 'w': 2, 'r': 2019}
{'ww': datetime.date(2018, 12, 31), 'consumption': Decimal('52.35000'), 'amount': Decimal('14823.901'), 'w': 1, 'r': 2018}
{'ww': datetime.date(2018, 12, 24), 'consumption': Decimal('48.69800'), 'amount': Decimal('14771.551'), 'w': 52, 'r': 2018}
{'ww': datetime.date(2018, 12, 17), 'consumption': Decimal('45.07700'), 'amount': Decimal('14722.853'), 'w': 51, 'r': 2018}
{'ww': datetime.date(2018, 12, 10), 'consumption': Decimal('44.17100'), 'amount': Decimal('14677.776'), 'w': 50, 'r': 2018}

and section between 2019 and 2020, same problem

{'ww': datetime.date(2020, 1, 13), 'consumption': Decimal('39.31200'), 'amount': Decimal('16976.174'), 'w': 3, 'r': 2020}
{'ww': datetime.date(2020, 1, 6), 'consumption': Decimal('50.84500'), 'amount': Decimal('16936.862'), 'w': 2, 'r': 2020}
{'ww': datetime.date(2019, 12, 30), 'consumption': Decimal('51.67200'), 'amount': Decimal('16886.017'), 'w': 1, 'r': 2019}
{'ww': datetime.date(2019, 12, 23), 'consumption': Decimal('49.71200'), 'amount': Decimal('16834.345'), 'w': 52, 'r': 2019}
{'ww': datetime.date(2019, 12, 16), 'consumption': Decimal('43.23600'), 'amount': Decimal('16784.633'), 'w': 51, 'r': 2019}
{'ww': datetime.date(2019, 12, 9), 'consumption': Decimal('41.13100'), 'amount': Decimal('16741.397'), 'w': 50, 'r': 2019}

I am passing that queryset to pandas dataframe and creating pivot table from field r w and consumption.

Thanks for any tip how could I solve this in django instead of creating some custom code. Or maybe there is nice solution with pandas using just ww and consumption field to create pivot table where I have years in row and weeks in columns

djangopostgresqlgroup-byweek-numberannotate