One of Django powerful features is the Django ORM (Object-Relational Mapping), which allows developers to interact with the database using Python code instead of SQL. In this article, we'll explore how to filter rows in Django by row average. We’ll look into practical examples and code snippets to guide you through the process.

## Understanding the Context

Before diving into the code, let's clarify what we mean by "filtering rows by row average." Suppose you have a Django model representing students' scores in different subjects, and you want to filter students whose average score across subjects meets a certain threshold.

### Setting Up the Django Model

First, let's create a Django model to represent the data. We'll use a simple example with students and their scores in three subjects: Math, Science, and English.

```
from django.db import models
class Student(models.Model):
name = models.CharField(max_length=100)
math_score = models.IntegerField()
science_score = models.IntegerField()
english_score = models.IntegerField()
def __str__(self):
return self.name
```

In this model, each student has a name and scores for Math, Science, and English. Our goal is to filter students based on their average score across these three subjects.

### Calculating the Row Average

To filter students by their average score, we need to calculate the average score for each student. This can be achieved using Django's `annotate`

and `F`

expressions. The `annotate`

method is used to add calculated fields to each model instance, and `F`

expressions allow us to refer to model fields directly in queries.

```
from django.db.models import F, FloatField
from django.db.models.functions import Cast
# Calculate the average score for each student
students_with_avg_score = Student.objects.annotate(
average_score=(
(F('math_score') + F('science_score') + F('english_score')) / 3.0
)
)
for student in students_with_avg_score:
print(f'{student.name}: {student.average_score}')
```

In this example, we use the `annotate`

method to add an `average_score`

field to each student. The average score is calculated by summing the scores and dividing by 3. The result is a queryset where each student has an additional field `average_score`

.

## Filtering by Row Average

Now that we have the average score calculated, we can filter the students based on this value. For instance, let's filter students whose average score is greater than or equal to 75.

```
# Filter students with an average score of at least 75
high_achievers = students_with_avg_score.filter(average_score__gte=75)
for student in high_achievers:
print(f'{student.name}: {student.average_score}')
```

In this code, we use the `filter`

method to get students whose `average_score`

is greater than or equal to 75. The `__gte`

lookup is used to perform a "greater than or equal to" comparison.

## Advanced Filtering: Dynamic Subjects

In real-world applications, the number of subjects might not be fixed. Let's consider a scenario where subjects are stored in a separate model and linked to students via a foreign key.

```
class Subject(models.Model):
name = models.CharField(max_length=100)
class Score(models.Model):
student = models.ForeignKey(Student, on_delete=models.CASCADE)
subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
score = models.IntegerField()
def __str__(self):
return f'{self.student.name} - {self.subject.name}'
```

In this setup, each score is linked to a student and a subject. To calculate the average score dynamically, we need to aggregate the scores for each student.

```
from django.db.models import Avg
# Calculate the average score for each student
students_with_dynamic_avg_score = Student.objects.annotate(
average_score=Avg('score__score')
)
for student in students_with_dynamic_avg_score:
print(f'{student.name}: {student.average_score}')
```

Here, we use the `Avg`

aggregation function to calculate the average score for each student dynamically. The `score__score`

lookup follows the foreign key relationship from `Student`

to `Score`

and then to the `score`

field.

### Filtering Dynamic Averages

Finally, let's filter students with a dynamic average score of at least 75.

```
# Filter students with a dynamic average score of at least 75
dynamic_high_achievers = students_with_dynamic_avg_score.filter(average_score__gte=75)
for student in dynamic_high_achievers:
print(f'{student.name}: {student.average_score}')
```

This code snippet filters students based on their dynamically calculated average score, similar to our previous example.

### Conclusion

In this article, we've explored how to filter rows in Django by row average using both fixed and dynamic subject lists. By leveraging Django's powerful ORM and aggregation functions, we can efficiently perform complex queries and calculations on our data.

By following these examples, you should be able to apply similar techniques to your own Django projects, enabling more sophisticated data analysis and filtering capabilities.