Django Calculate Aggregated Totals

Question

I am trying to build a table that will aggregate stats based on a user ID. This is the first time asking a question, so bear with me if I miss anything major.

Here is the model:

class Batting(models.Model):
  player = models.ForeignKey(
    'Player',
    on_delete=models.CASCADE,
    null=True,
    )
  game = models.ForeignKey(
    'Game',
    on_delete=models.CASCADE,
    null=True,
    blank=True,
    )
  season = models.ForeignKey(
    'Season', 
    on_delete=models.CASCADE,
    null=True,
    )
  games_played = models.IntegerField(
    blank=True,
    default=1,
    )
  plate_appearances = models.IntegerField(
    blank=True,
    default=0,
    )
  at_bats = models.DecimalField(
    default=0,
    max_digits=6,
    decimal_places=3,
    )
  hits = models.DecimalField(
    blank=True,
    max_digits=6,
    decimal_places=3,
    default=0,
    )
  ...
  batting_average = models.DecimalField(
    max_digits=6,
    decimal_places=3,
    editable=False,
    null=True
    )
  slugging_percentage = models.DecimalField(
    max_digits=6,
    decimal_places=3,
    editable=False,
    null=True
    )
  on_base_percentage = models.DecimalField(
    max_digits=6,
    decimal_places=3,
    editable=False,
    null=True
    )
  on_base_plus_slugging_percentage = models.DecimalField(
    max_digits=6,
    decimal_places=3,
    editable=False,
    null=True
    )

  def save(self, *args, **kwargs):
        self.total_bases = self.singles + (self.doubles * 2) + (self.triples * 3) + (self.home_runs * 4)
        self.extra_base_hits = self.doubles + self.triples + self.home_runs
        self.batting_average = float(self.hits) / float(self.at_bats)
        self.slugging_percentage = self.total_bases / float(self.at_bats)
        self.on_base_percentage = (self.hits + self.base_on_balls + self.hit_by_pitch) / float(self.at_bats + self.base_on_balls + self.sacrifice_flys + self.hit_by_pitch)
        self.on_base_plus_slugging_percentage = (self.hits + self.base_on_balls + self.hit_by_pitch) / float(self.at_bats + self.base_on_balls + self.sacrifice_flys + self.hit_by_pitch)
        super(Batting, self).save(*args, **kwargs)

  class Meta:
    verbose_name = u'Batting Stat'
    verbose_name_plural = u'Batting Stats'

  def __unicode__(self):
    return self.player.last_name

Here is the view:

def stats(request):
  batting_stats = Batting.objects.values('player__id').annotate(
    fn=F('player__first_name'),
    ln=F('player__last_name'),
    total_hits=Sum(('hits'),output_field=FloatField()),
    total_at_bats=Sum(('at_bats'),output_field=FloatField()),
    avg=Avg('batting_average'),
    slg=Avg('slugging_percentage'),
    obp=Avg('on_base_percentage'),
    ops=Avg('on_base_plus_slugging_percentage'),
    )

  return render(request, 'stats.html', {
    'batting_stats': batting_stats,
    })

The problem I've encountered is that avg, slg, obp, and ops are all calculations in the model, and Averaging them in the View is averaging the averages as opposed to calculating the totals being aggregated based on player ID.

I've tried utilizing the Aggregate function, but haven't found a way to make it work. Is there a way to combine the use of Annotate and Aggregate that will allow me to consolidate stats under a single Player ID and apply those same calculations to the totals?

Answer 1

As I can see, actually you need to do quite the same calculations for single Batting instance and for your stats view. So I'll make it in one function and call it from two points:

def count_averages(data, result_recipient=None):
    result_recipient = data if result_recipient=None else result_recipient
    result_recipient.total_bases = data.singles + (data.doubles * 2) + (data.triples * 3) + (data.home_runs * 4)
    result_recipient.extra_base_hits = data.doubles + data.triples + data.home_runs
    ...

def save(self, *args, **kwargs):
    count_averages(self)
    super(Batting, self).save(*args, **kwargs)

def stats(request):
    sum_of_all_batting_stats = Batting.objects.filter('player__id'=player__id).annotate(
      sum('singles'),
      sum('doubles'),
      sum('triples'),
      ...
    )
    batting_stats = {}
    count_averages(self, batting_stats)

    return render(request, 'stats.html', {
        'batting_stats': batting_stats,
    })

This is not 100% valid code, but you should've got the idea: you move your calculations to the separated function and call in on 2 different data - 1) one Batting instance and 2) sum of all Batting instances.

P.S. If you calculate it every time for every view call, that can cause performance issues. I'd recommend storing aggregated stats in a db table or some cache.