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?

You do this per `District`, what if there are multiple statistics for districts that have the same city, should these be summed up? — Willem Van Onsem, Jul 11 '23 at 12:37
both, summed up aswell as multiple lines per city would be interesting. For the latter, it would be necessary to add the district name as an attribute in an extra column. For the sake of simplicity in this example, I would assume that there is only one district per city. I wanted to have several different models involed, hence the distinction between city and district. — MightyRavendark, Jul 11 '23 at 13:32

Willem Van Onsem · Accepted Answer · 2023-07-11T20:08:31.320

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>

edited Jul 11 '23 at 20:08

answered Jul 11 '23 at 13:56

Willem Van Onsem

443,496
30
428
555

Thank you, that solves the tasks. You have some type in your code though: `measurement = set()` should be `measurements = set()` and in the template `table.items()` should be `table.items`. This part `+= stat.value` I reworked to `+= 0 if stat.value is None else stat.value` to capture cases with no value for a certain measurement. – MightyRavendark Jul 11 '23 at 20:03
@MightyRavendark: aarrrgghhh, yes, that the result of some editing and forgetting to adapt the rest. Thanks! – Willem Van Onsem Jul 11 '23 at 20:09
1

@MightyRavendark: as for the `None`, you might want to use `if value is not None`, in that case if all items are `None` you can for example render it blank, which is often something different than `0`. – Willem Van Onsem Jul 11 '23 at 20:09
1

Gonna do it that way, thx for the hint. – MightyRavendark Jul 13 '23 at 14:37

Django Pivot QuerySet of multiple models from long to wide format

1 Answers1