Django Pivot QuerySet of multiple models from long to wide format

Question

I wonder how to render a table using data from multiple models. In the database the data is stored in long format but it should be rendered in wide format.

Using a data model like this:

class Municipality(models.Model):
    mun_name = models.CharField(max_length=150)
    country = models.CharField(max_length=150)
    
    
class District(models.Model):
    dis_name = models.CharField(max_length=150)
    municipality = models.ForeignKey(Municipality, on_delete = models.PROTECT)
    
    
    
class DistrictStatistics(models.Model):
    district = models.ForeignKey(district, on_delete = models.PROTECT)
    measurement = models.ForeignKey(Measurement, on_delete = models.PROTECT)
    value = models.IntegerField()
    
    class Meta:
        unique_together =  ["District", "Measurement"]
    
    
class Measurement(models.Model):
    measurement_name = models.CharField(max_length=150, primary_key=True)
    description = models.TextField()

This is a simplified model for illustration purposes.

Querying like this should give a list of all values in what is known as long format.

def measurement_list(request):
    dist_statistic = DistrictStatistics.objects.select_related('District__Municipality').all()
    
    context = {
        'statistics':dist_statistic
    }
    
    return render(request, 'table_template.html', context)

The HTML template would look like this:

<table>
    <tr>
        <th>Municipality Name</th>
        <th>Measurement</th>
        <th>Value</th>
    </tr>
    {% for row in statistics %}
    <tr>
        <td>{{ row.District.City.name }}</td>
        <td>{{ row.Measurement }}</td>
        <td>{{ row.value }}</td>
    </tr>
    {% endfor %}
</table>

This leads to a table looking like this:

 Municipality Name      Measurement     Value
 City A                 Inhabitants     25120
 City A                 Bus Stops          15
 City B                 Inhabitants     60000
 City C                 Inhabitants     12300
 City C                 Public Parks        2

However, I want to have it look like this:

 Municipality Name      Inhabitants         Bus Stops           Public Parks
 City A                   25120                 15                  0
 City B                   60000                  0                  0
 City C                   12300                  0                  2

What would be the most efficitent way to achieve this?

Answer 1

Just prepare the data in the view to render a table:

from collections import Counter, defaultdict


def measurement_list(request):
    dist_statistic = DistrictStatistics.objects.select_related(
        'district__municipality', 'measurement'
    )
    data = defaulddict(Counter)
    measurements = set()
    for stat in dist_statistic:
        if stat.value is not None:
            data[stat.district.municipality][stat.measurement] += stat.value
        measurements.add(stat.measurement)
    table = {
        k: [v.get(measurement) for measurement in measurements]
        for k, v in data.items()
    }

    context = {
        'table': table,
        'measurements': measurements,
    }

    return render(request, 'table_template.html', context)

then we can render this with:

<table>
    <tr>
        <th>Municipality Name</th>
        {% for measurement in measurements %}
            <th>{{ measurement }}</th>
        {% endfor %}
    </tr>
    {% for city, row in table.items %}
    <tr>
        <td>{{ city }}</td>
        {% for cell in row %}
            <td>{{ cell }}</td>
        {% endfor %}
    </tr>
    {% endfor %}
</table>