Calculating percentile from django queryset

Question

Let's say I have a django queryset of objects User. Each User has an average_score. How can I determine the user's percentile (top 10%, top 20%, etc) based on the average_score?

If I were able to sort the queryset, I could theoretically take the index divided by the total count (eg 5th place out of 100 total is top 5%) but since we can't sort querysets– what can I do here?

Alex Vyushkov · Answer 1 · 2017-01-02T18:16:29.183

1

Updated snippet to take into account possible division by zero error. It gets the number of objects with average_score less than specified using __lte queryset filter (Less than or equal to) and divided by the total number of objects in the database.

Everything is done on the database end, and (almost) no data is transferred back and forth, so it should work efficiently even with large number of user objects in the database

    queryset = User.objects.all()
    total_count = queryset.count()
    if total_count:
        percentile = float(queryset.filter(average_score__lte=average_score).count())/total_count
    else:
        return 0.0

edited Jan 02 '17 at 18:16

answered Jan 02 '17 at 03:32

Alex Vyushkov

650
1
6
21

Be careful about division by zero !! Your snippet don't catch this error. – Wilfried Jan 02 '17 at 09:02
Right, I will update the snippet. This shouldn't happen, though - average_score comes from a User object so there will be at least one object in the database. – Alex Vyushkov Jan 02 '17 at 18:06
1

http://stackoverflow.com/questions/11360858/what-is-the-eafp-principle-in-python Just a SO post about EAFP principe. Used in Sanca answer. More pytonic. – Wilfried Jan 02 '17 at 19:39

binpy · Answer 2 · 2017-01-02T04:13:56.210

few months ago, i had a similiar problem to find average transactions for this year, such as script below. hopefully can help..

import time
from django import template
from yourapp.models import Transaction

register = template.Library()
now_year = time.strftime("%Y")

@register.simple_tag
def graph_average_income_by_year():

    try:
        transactions = Transaction.objects.filter(paid=True)\
                                          .filter(payment_date__year=now_year)
        count_transactions = transactions.count()
        incomes = [ p.total_transfer for p in transactions ]
        return ("%.1f" % (float(sum(incomes))/count_transactions) )
    except:
        return 0.0

Calculating percentile from django queryset

2 Answers2