CODEWITHSUNDEEP
djangomathdjango-modelsdjango-viewsaggregate
Alex Winkler
Alex Winkler

Reputation: 489

Django Calculate Mean Of 2 Fields Inside Multiple Objects

I'm trying to do a very simple math problem but I don't know how to convert it into python. Basically I need to calculate the mean entry price for a trade based on multiple "buy" entries. To do that all one needs to do is calculate 

∑ (entry.amount * entry.price) / ∑ (entry.amount)

This should be the variable "total_entry_price2" in the end.

  1. Where am I going wrong with the calculation? How Can I add all the ∑'s together?
  2. Is this the best way to do it?

models.py

class Trade(models.Model):
    ... 

class Entry(models.Model):
    ...
    trade = models.ForeignKey(Trade, on_delete=models.CASCADE)
    amount = models.FloatField()
    price = models.FloatField()
    entry_type = models.CharField(max_length=3, choices=ENTRY_TYPE_CHOICES, default=BUY)

views.py

@login_required
def trade_detail_view(request, pk):
    logger.info('trade detail view')
    if request.method == 'GET':
        trade = get_object_or_404(Trade, pk=pk)
        entries = Entry.objects.filter(trade=trade)
        entries_buy = Entry.objects.filter(trade=trade, entry_type="buy")
        patterns = Pattern.objects.filter(trade=trade)

        for entry in entries_buy:
            total_entry_price = Sum(entry.amount * entry.price)
            total_entry_price2 = total_entry_price / entries_buy.aggregate(Sum('amount'))
            print(total_entry_price2)

        context = {
            'trade': trade,
            'entries': entries,
            'patterns': patterns,
            'max_amount': entries_buy.aggregate(Sum('amount')),
            'total_fees': entries.aggregate(Sum('fee')),
            'entry_price': entries_buy.aggregate(Avg('price'))
        }

Current Terminal print:

Sum(Value(60.0)) / Value({'amount__sum': 40.0})
Sum(Value(10.0)) / Value({'amount__sum': 40.0})

Example data

enter image description here

The correct answer should be $1.75

(30 * 2 + 10 * 1) / 40 = 1.75

Final Solution (added upon from Oleg Russkin's Answer)

The revisions I did are as follows:

total_entry_cost = entries_buy.annotate(
    s=F('amount') * F('price')
).aggregate(
    total_entry_cost=ExpressionWrapper(
        Sum(
            Cast('s', output_field=models.FloatField())
        ) / Sum('amount'),
        output_field=models.FloatField()
    )
)['total_entry_cost']
print(total_entry_cost)

Upvotes: 1

Views: 548

Answers (1)

Oleg Russkin
Oleg Russkin

Reputation: 4404

Example query to calculate required value.

Cast() to float may be avoided if the result of Sum is float, not an integer.

from django.db import models
from django.db.models import ExpressionWrapper, F, Sum
from django.db.models.functions import Cast


total_entry_price2 = Entry.objects.annotate(
    s=F('amount')+F('price')
).aggregate(
    price2=ExpressionWrapper(
        Sum(
            Cast('s',output_field=models.FloatField())
        ) / Sum('amount'),
        output_field=models.FloatField()
    )
)['price2']

# Actual result of the query is dictioanry
# so we get the key
# {'price2': 0.59633706227207}

Updated Answer By OP

This answer almost got us all the way. It was my fault not to be more clear on the exact answer I was looking for. I updated my question near the end to reflect it.

The revisions I did are as follows:

total_entry_cost = entries_buy.annotate(
    s=F('amount') * F('price')
).aggregate(
    total_entry_cost=ExpressionWrapper(
        Sum(
            Cast('s', output_field=models.FloatField())
        ) / Sum('amount'),
        output_field=models.FloatField()
    )
)['total_entry_cost']
print(total_entry_cost)

Upvotes: 2

Related Questions