Django Table - Single Row if Item is unique

Question

Is there a way to get all the amounts on a single row in HTML if "category name" is unique? According to my code, "category name" is no longer unique because it is being annotated by month. Thank you

Data:

HTML Output: I want them to be all in one row since they are "Car Payment"

Views.py

class CashFlow(LoginRequiredMixin, AccountContextMixin, TemplateView):
    model = Transaction
    template_name = 'transaction_app/cf.html'
    context_object_name = 'transaction'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        user = self.request.user

        #-------Dropdown Begins---------
        #Get unique years for dropdown
        distinct_years = Transaction.objects.filter(user=user).dates('transaction_date', 'year')
        year_choices = [(date.year, date.year) for date in distinct_years]
        unique_years = list(set(year_choices))
        unique_years.sort()
        context['unique_years'] = unique_years
        #-------Dropdown Ends-----------

        #---Get Data From Dropdown Begins---------       
        selected_year = self.request.GET.get('selected_year')
        selected_year = int(selected_year) if selected_year else datetime.now().year
    
        selected_status = self.request.GET.get('selected_status')
        #---Get Data From Dropdown Ends-----------

        #----Get Data for Table Begins------------
        
        # Obtain distinct category names
        category_names = Transaction.objects.filter(
            user=user,
            transaction_date__year=selected_year,
            status=selected_status
        ).values_list('category__category_name', flat=True).distinct()

        # Aggregate data for each distinct category
        category_sums = []
        for category_name in category_names:
        # Aggregate total amount for each month within the specified year for the current category
            category_sum_by_month = Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
                category__category_name=category_name
            ).annotate(
                month=ExtractMonth('transaction_date')
            ).values('month').annotate(
                total_amount=Sum('amount')
            )

            # Append category name and its monthly sums to category_sums list
            for item in category_sum_by_month:
                item['category__category_name'] = category_name
                category_sums.append(item)

            # Aggregate total amount for the current category
            category_sum = Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
                category__category_name=category_name
            ).aggregate(
                total_amount=Sum('amount')
            )

            category_sum['category__category_name'] = category_name
            category_sums.append(category_sum)
            category_sums.append(category_sum_by_month)

            context['category_sums'] = category_sums
        #----Get Data for Table Begins------------
        return context

Html template

<table class="tbl-big" border="1">
    <thead>
        <tr>
            <th>Category Name</th>
            <th>Jan</th>
            <th>Feb</th>
            <th>Mar</th>
            <th>Total</th>
        </tr>
    </thead>
    <tbody>
        {% for item in category_sums %}
        <tr>
            <td>{{item.category__category_name}}</td>
            
            <td>
            {% if item.month == 1%}
            {{item.total_amount}}
            {% endif %}
            </td>
            
            <td>
            {% if item.month == 2%}
            {{item.total_amount}}
            {% endif %}
            </td>

            <td>
            {% if item.month == 3%}
            {{item.total_amount}}
            {% endif %}
            </td>

            <td>{{item.total_amount}}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

Data Output:

{'month': 1, 'total_amount': Decimal('-1400.03000000000'),
'category__category_name': 'Car Payment'} {'month': 2, 'total_amount':
Decimal('-1400.03000000000'), 'category__category_name': 'Car
Payment'} {'total_amount': Decimal('-2800.06000000000'),
'category__category_name': 'Car Payment'} <QuerySet [{'month': 1,
'total_amount': Decimal('-1400.03000000000'),
'category__category_name': 'Car Payment'}, {'month': 2,
'total_amount': Decimal('-1400.03000000000'),
'category__category_name': 'Car Payment'}]>

Solution: thanks to @willeM_ Van Onsem

CategoryOverview = namedtuple('CategoryOverview', ['name', 'entries', 'total'])

class CashFlow(LoginRequiredMixin, AccountContextMixin, TemplateView):
    model = Transaction
    template_name = 'transaction_app/cf.html'
    context_object_name = 'transaction'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        user = self.request.user

...

        items = defaultdict(dict)
        sum_per_month = (
            Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
            )
            .values(
                month=ExtractMonth('transaction_date'),
                category_name=F('category__category_name'),
            )
            .annotate(total_amount=Sum('amount'))
            .order_by('category_name', 'month')
        )

        for record in sum_per_month:
            items[record['category_name']][record['month']] = record['total_amount']
        
        context['summary'] = [
            CategoryOverview(key, [entries.get(month) for month in range(1, 13)], sum(entries)
            )
            for key, entries in items.items()
        ]

        return context

Answer 1

I think the main problem here is the view that should provide data in an accessible manner. We can work with:

from collections import Counter, defaultdict, namedtuple

CategoryOverview = namedtuple('CategoryOverview', ['name', 'entries', 'total'])


class CashFlow(LoginRequiredMixin, AccountContextMixin, TemplateView):
    model = Transaction
    template_name = 'transaction_app/cf.html'
    context_object_name = 'transaction'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        selected_year = self.request.GET.get('selected_year')
        selected_year = (
            int(selected_year) if selected_year else datetime.now().year
        )
        items = defaultdict(dict)
        sum_per_month = (
            Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
            )
            .values(
                month=ExtractMonth('transaction_date'),
                category_name=F('category__category_name'),
            )
            .annotate(total_amount=Sum('amount'))
            .order_by('category_name', 'month')
        )
        for record in sum_per_month:
            items[record['category_name']][record['month']] = record['total']
        context['summary'] = [
            CategorySummary(
                key, [entries.get(month) for month in range(1, 12)], sum(entries)
            )
            for key, entries in items.items()
        ]
        return context

then we can render this quite easily with:

<table class="tbl-big" border="1">
    <thead>
        <tr>
            <th>Category Name</th>
            <th>Jan</th>
            <th>Feb</th>
            <th>Mar</th>
            <th>Apr</th>
            <th>May</th>
            <th>Jun</th>
            <th>Jul</th>
            <th>Aug</th>
            <th>Sep</th>
            <th>Oct</th>
            <th>Nov</th>
            <th>Dec</th>
            <th>Sep</th>
            <th>Total</th>
        </tr>
    </thead>
    <tbody>
        {% for category in summary %}
        <tr>
            <td>{{category.name }}</td>
            
            {% for entry in category.entries %}<td>{{ entry }}</td>{% endfor %}
            <td>{{ item.total }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

This will also boost efficiency, since we perform one to fetch all transactions, and then simply sort these out per category.