Django groupby date column not behaving as expected

Question

This is a simple operation in mysql - I basically just want a count of each date that appears in a row in a given table (with some additional filtering).

Here's a steamlined models.py:

class foo(models.Model):
    sentence = models.TextField()
    posted = models.DateField()

Here's the current attempt I've made at querying the foo model:

redinfo = foo.objects.all().order_by('posted').values('posted').annotate(total=Count('posted'))

However when I print this to screen (in this case using pandas as follow:

df2 = pd.DataFrame(list(redinfo.values()))
print(df2)

I end up with a table that looks like this:

ID  sentence    posted  total
0   1528    01/11/2020  1
1   1529    01/11/2020  1
2   1530    01/11/2020  1
3   1531    01/11/2020  1
4   1532    01/11/2020  1
…   …   …   …
8612    7431    21/02/2021  1
8613    7432    21/02/2021  1
8614    7433    21/02/2021  1
8615    7434    21/02/2021  1
8616    8510    21/02/2021  1

I've tried all sorts of changed to the queryset, mostly comprising of doing the operations in other orders, such as:

redinfo = foo.objects.all().values('posted').annotate(total=Count('posted')).order_by('posted')
redinfo = foo.objects.all().annotate(total=Count('posted')).order_by('posted')
redinfo = foo.objects.all().order_by('posted').annotate(total=Count('posted'))

None of which has worked. Where am I going wrong with this? I've based the above attempts on these SO questions... Django equivalent for count and group by and How to query as GROUP BY in django?

Answer 1

You need to remove the .values() while converting to data frame as calling values() again will override your initial call of values('posted')

df2 = pd.DataFrame(list(redinfo))
print(df2)