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?


Show source
| python   | django   | python-3.x   2017-01-02 04:01 2 Answers

Answers ( 2 )

  1. 2017-01-02 04:01

    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
    
  2. 2017-01-02 04:01

    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
    
◀ Go back