CODEWITHSUNDEEP
pythondjangoduplicatesdjango-querysetaverage
Maxime Michel
Maxime Michel

Reputation: 615

Average duplicates from specific columns in QuerySet in Django

I have a Django Model I am using that looks like this:

class FinancialAid(models.Model):
    RECEIVED_EVERY = (("J", "Jours"),
        ("S", "Semaines"),
        ("M", "Mois"))
    ID = models.AutoField(primary_key=True)
    Name = models.CharField(max_length=100, null=True, blank=True)
    Value = models.IntegerField(null=True, blank=True, default=-1)
    ReceivedEvery = models.CharField(max_length=15, null=True, blank=True, choices=RECEIVED_EVERY)
    Exchange = models.ForeignKey('Exchange', on_delete=models.CASCADE)

I am able to access the information I want but I have an issue in that I often have duplicates within my QuerySet over the Name and ReceivedEvery columns but not always on the Value and never on the Exchange.

Say I have the following QuerySet expanded out:

financial_aid_qs = [
    (ID=1, Name="Aid1", Value=100, ReceivedEvery="M", Exchange=Exchange.objects.get(pk=1)),
    (ID=2, Name="Aid2", Value=200, ReceivedEvery="S", Exchange=Exchange.objects.get(pk=2)),
    (ID=3, Name="Aid1", Value=150, ReceivedEvery="M", Exchange=Exchange.objects.get(pk=3)),
    (ID=4, Name="Aid3", Value=100, ReceivedEvery="M", Exchange=Exchange.objects.get(pk=4))
]

As you can see, I have the same Name and ReceivedEvery for indexes 1 and 3. What I would like to do is get a QuerySet (or ValuesQuerySet although I seem to recall this was removed) that will contain all the different FinancialAid objects in terms of Name and average out the Value of FinancialAid objects that have the same Name. Ideally it would take into account the fact that ReceivedEvery can differ even though the Name is the same (This is all an average so doesn't need to be perfect which is why for the sake of this, one month is 30 days or 4 weeks).

With all this, the result should look like this:

financial_aid_qs = [
    (Name="Aid1", Value=125, ReceivedEvery="M"),
    (Name="Aid2", Value=200, ReceivedEvery="S"),
    (Name="Aid3", Value=100, ReceivedEvery="M")
]

As you can see, for Aid1, the value has been averaged to 125 from 100 and 150 and the other Aids haven't been modified.

Upvotes: 1

Views: 165

Answers (1)

dirkgroten
dirkgroten

Reputation: 20692

Use values() to aggregate over a grouped query, e.g.:

financial_aid_qs = FinancialAid.objects.values("Name").annotate(avg_value=Avg("Value"))

This will return a queryset (when executed a list) of dictionaries:

financial_aid_qs = [
    {'Name': "Aid1", 'avg_value': 125},
    {'Name': "Aid2", 'avg_value': 200},
    ...
]

If you want to differentiate also by ReceivedEvery, then you just group by both values:

financial_aid_qs = FinancialAid.objects.values('Name', 'ReceivedEvery').annotate(...)

>>> [{'Name': "Aid1", 'ReceivedEvery': "M", 'avg_value': 125}, 
     {'Name': "Aid1", 'ReceivedEvery': "S", 'avg_value': 200}, ...]

Here, weekly and monthly donations will be separated for the same "Name".

Use a conditional expression to create new values depending on others:

financial_aid_qs = FinancialAid.objects.annotate(
    factor=Case(
        When(ReceivedEvery="S", then=Value(4)),
        When(ReceivedEvery="M", then=Value(1)),
        output_field=IntegerField()
    )).annotate(
        total=F('factor')*F('Value')
    ).values('Name').annotate(
        avg_value=Avg('total')
    )

Upvotes: 1

Related Questions