5

I have a DataFrame:

`exp_no`   'time'   'price'

  1       0:00:00     20.0
  1       7 days      45.0
  1       15 days     100.0
  2       0:00:00     20.0
  2       7 days      45.0
  2       15 days     100.0

The corresponding Django model:

class StData(models.Model):
      exp_no = models.ForeignKey(StIndex, on_delete=models.CASCADE)
      time = models.DateTimeField()
      price = models.DecimalField(max_digits=10, decimal_places=2)

I want to make a smaller table which would have exp_no, max_time and corresponding price such that:

 `exp_no`   'time'   'price'

  1       15 days     100.0
  2       15 days     100.0

In pandas, I'd do df.groupby('exp_no')['time', 'price'].max().reset_index() to get the desired table.

In Django ORM annotation to get the same result(or Queryset) if I do:

qs.values('exp_no').annotate(max_time=Max('time')).order_by()

it gives me exp_no and time, but I want to get the corresponding price as well. I have looked through this answer in SO: Django orm get latest for each group

But not sure how I get the price. Using Django 2.0 with sqlite3.

I appreciate any help.

abc123
  • 91
  • 1
  • 9

1 Answers1

5

Your question boils down to obtaining the StData object (or the values) for the record with the largest time column. So we do not need an aggregate, but obtain the smallest or largest row with respect to the time column.

Obtaining the StData object with the maximum time

You can just obtain the last() row when you order on 'time', like:

stdata_max_time = qs.order_by('time').last()  # will return a StData

or alternatively:

stdata_max_time = qs.order_by('-time').first()  # will return a StData

In case time is NULLable (not here), you can ensure that you will not retrieve a row with NULL by specifying that when ordering these should be placed as first/last elements respectively:

from django.db.models import F

# two alternatives
stdata_max_time = qs.order_by(F('time', nulls_first=True)).last()
stdata_max_time = qs.order_by(F('-time', nulls_last=True)).first()

This is thus a StData object, and you can thus retrieve all relevant information you want from that object. Since it is a StData object, it will also behave like this (so extra properties, methods, etc. you defined, will work as well).

Obtaining the values with the maximum time

This is completely similar, except that we put a .values(..) call in the query:

stdata_max_time = qs.order_by('time').values('exp_no', 'time', 'price').last()

But unless there are some really good reasons, I would personally prefer to fetch the StData object, since that means "batteries included" (you get all the logic you wrote in the StData class with that object).

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555