Django - query extracts most frequent values field by field

user19484869 0
Viewed 41

i would like to return a single tuple with, field by field, the most frequent values. My models is as follows:

class myTable(models.Model):  
        a = models.IntegerField(blank = True, default = 0)
        b = models.IntegerField(blank = True, default = 0)
        c = models.IntegerField(blank = True, default = 0)
        d = models.IntegerField(blank = True, default = 0)

My DB is as follows:

ID a b c d
0 2 4 1 0
1 3 4 2 3
2 2 3 1 3
3 1 2 6 2

The single tuple that I want to return is constitued like that: a=2, b=4, c=1, d=3 (a is 2,3,2 and 1 so the most frequent value is 2). How can I do that?

django-queryset django-rest-framework django python
2 Answers

One way to do this:

from django.db.models import Count

fields = ["a", "b", "c", "d"]
field_most_frequent_mapping = {}
for field in fields:
    field_most_frequent_mapping[field] = (
        myTable.objects.values(field)
        .annotate(field_count=Count(field))
        .order_by("-field_count")[0][field]
    )

You'll get a dictionary instead of a tuple, but you can work out the rest ;)

anthony22610

I think you will have to do 4 separate queries:

from django.db.models import Count
fields = 'a', 'b', 'c', 'd'

result = [
    {
        field_name: (
               myTable.objects
               .annotate(value=F(field_name))  # rename field for more readable output
               .values('value')
               .annotate(count=Count('value'))
               .order_by('-count')
               .first()
        )
    }
    for field_name in fields
]

This will produce following result:

# result
[
    {'a': {'value': 2, 'count': 2}},
    ...
    {'d': {'value': 3, 'count': 2}},
]
Ersain0
Related