Django how to make a pivot table?

Question

I'm having trouble making a pivot table of my data. The data I'm taking from the user is : Driver name, Date, The commission, The branch, and some others but those are the ones that i want to Make an pivot table of. so Here's the model that's gonna take the important fields.

models.py

class InvoiceRegistering(models.Model):
    driver_name = models.ForeignKey(Drivers, on_delete=models.SET(""), verbose_name="السائق")
    pub_date = models.DateField('التاريخ')  # default=timezone.now
    the_car = models.ForeignKey(Cars, on_delete=models.SET(""), verbose_name="السيارة")
    city = models.ForeignKey(Cities, on_delete=models.SET(""), verbose_name="المدينة")
    branch = models.ForeignKey(Branches, on_delete=models.SET(""), verbose_name=" الفرع")
    the_representer = models.ForeignKey(Representers, on_delete=models.SET(""), verbose_name="المندوب")
    location = models.ForeignKey(Locations, on_delete=models.SET(""), verbose_name="اختر الموقع")  # blank = True == required = False
    sort_ofBox = models.ForeignKey(SortOfBoxes, on_delete=models.SET(""), verbose_name="اختر الصنف")
    number_of_boxes = models.IntegerField(verbose_name="عدد الطبالي", default="")
    sar_4box = models.IntegerField(verbose_name="ريال \ طبلية", default="")
    receipt_num = models.IntegerField(verbose_name="رقم الايصال", default="")
    def commision(self):
        myvalue = ''.join(str(self.number_of_boxes * self.sar_4box))
        return str(myvalue)
    commision.short_description = "العمولة"

admin.py

class InvoiceAdmin(ImportExportModelAdmin):
    def render_change_form(self, request, context, add=False, change=False, form_url='', obj=None):
    response = super(InvoiceAdmin, self).render_change_form(request, context, add, change, form_url, obj)
    response.context_data['title'] = "تعديل" if response.context_data['object_id'] else "إضافة رد جديد"
    return response

    list_display = (
    'id', 'driver_name', 'pub_date', 'the_car', 'the_representer', 'branch', 'location', 'number_of_boxes',
    'sar_4box',
    'commision', 'sort_ofBox', 'receipt_num')
    list_filter = ('id', 'pub_date', 'driver_name', 'branch', 'the_car')
    search_fields = (
    'id', 'driver_name', 'pub_date', 'the_car', 'the_representer', 'branch', 'location', 'number_of_boxes',
    'sar_4box',
    'sort_ofBox', 'receipt_num')

I tried to pull the data from the model then parse it, but it turns out it's too complicated. I tried that by this code inside the model :

def full_values(self):
    fullValue = str(self.driver_name), str(self.pub_date), str(self.the_representer), str(self.the_car), str(self.city), str(self.location), str(self.sort_ofBox), self.number_of_boxes, self.sar_4box, self.receipt_num
    return str(fullValue)

And it returns the needed data, the problem is i don't know how to take it to another model or how to analyze it inside a table.

What I need is : 1- How to make a pivot table that calculate all commissions ? Columns are the branches and the rows are the drivers. it'll be something like this:

            ---+-----Branches
       Drivers |  D1 | R1 | T1
       Noah    |  62 | 23 | 52
       Ahmad   |  16 | 61 | 72
       Saleh   |  32 | 22 | 33

I was thinking of pandas pivot table then parse it in context and send it to the page but i didn't know how to do it.

If anyone know the solution please help.

user_13324508 · Answer

Solved! I used pandas to make DataFrame then into pivot table, then send it through context to my template. Also Taking the year of the report through the url that it's written by user. here's my views.py :

from django.http import HttpResponse
import pandas as pd
from .models import InvoiceRegistering, Branches, gettingBranchesList
from django.shortcuts import render, redirect, get_object_or_404
import datetime
import django as dj
def YearlyReports(request, year): # month
    qs = InvoiceRegistering.objects.filter(pub_date__year=year).values("driver_name", "amount", "branch")
    data = pd.DataFrame(qs)
    data = data.fillna(0)
    data = data.rename(columns={'driver_name': 'السائق', 'branch': 'الفرع', 'amount': 'العمولة'})
    pivot = pd.pivot_table(data, index='السائق', columns='الفرع', values='العمولة', aggfunc='sum',
                           margins=True, margins_name='الاجمالي', fill_value=0)
    Date = "عمولة الطبالي للسائقين على حسب الفرع عن سنة {}".format(year)
    context = {'YearsTable': pivot.to_html,
               'description': Date,
               }
    return render(request, 'YearlyReports.html', context)

urls.py :

path("reports/yearlyreports//", views.YearlyReports, name="YearlyReports"),

Django how to make a pivot table?

1 Answers