CODEWITHSUNDEEP
pythonsqldjango
acer123
acer123

Reputation: 326

Django: pivot data

I am building a simple school-registration app in Django and am struggling to figure out how to create a report. I see it as pivoting the data but that may not really be what I'm trying to do. Extensive research and playing with pandas, chartit, etc has yielded no answers so I'm hoping you can help.

class SemesterPeriod(models.Model):
    name = models.CharField(max_length=50)

class Schedule(models.Model):
    semester = text
    course = Foreign Key
    semester_period = models.ForeignKey(SemesterPeriod)

class Student(models.Model):
    first_name = models.CharField(max_length=50)

class StudentEnroll(models.Model):
    schedule = ForeignKey('Schedule')
    student = ForeignKey('Student')

The data might look like this for two students

SemesterPeriod
    1st_Period
    2nd_Period
    3rd_Period

Schedule
    Fall, Math, 1st_Period
    Fall, History, 2nd_Period
    Fall, Science, 3rd_Period

Student
    Chris
    Kim

StudentEnroll
    Math, Chris
    Science, Kim
    Science, Chris
    History, Kim

Now, what I want to display is something like this:

Student             1st_Period           2nd_Period          3rd_Period
Chris                Math                                      Science
Kim                                        History             Science

OR......

Student             1st_Period           2nd_Period          3rd_Period
Chris                X                                          X
Kim                                        X                    X

You can see, I hope, how I'm "pivoting" the data. Since I'm not summarizing or aggregating it I don't know if that is the right term to use. In any case, I can't figure out how to do this efficiently. I could make tons of database queries but there has to be an easier way.

Upvotes: 2

Views: 2189

Answers (1)

dr jimbob
dr jimbob

Reputation: 17751

You should be able to do two DB queries, (1) where you get all the student enrolls (fetched with appropriate other data), and (2) where you get a sorted list of your periods. You could probably get away with optimizing the second query away (but personally I wouldn't bother).

In your view process this StudentEnroll list using a defaultdict:

from collections import defaultdict

enrolls = StudentEnroll.objects.filter(semester=semester).values_list(
            'student__name', 'schedule__course', 'schedule__semester_period__name')
# Fetch all the enrolled data; this puts them in a list of tuples like 
# [("Chris", "Math", "1st_Period"), ("Kim", "Science", "3rd_Period"), ... ]

periods = SemesterPeriod.objects.values_list('name', flat=True).order_by('name')
# get names of all periods in a flat list like: ['1st_Period', '2nd_Period', '3rd_Period'] 

period_dict = dict([(period,i) for i,period in enumerate(periods)])
# enumerate(periods) is a generator that has data in the form 
# [(0, '1st_Period'), (1, '2nd_Period'), (2, '3rd_Period')] -- it enumerates the list.
# I then use a list comprehension to reverse it:
# [(period,i) for i,period in enumerate(periods)] is
# [('1st_Period', 0), ('2nd_Period', 1), ...]
# and then convert that list of paired tuples into a dictionary:
# {'1st_Period': 0, '2nd_Period': 1, '3rd_Period': 2}
# period_dict['3rd_Period'] returns 2; indicating that classes that are third period
# should fall into a students schedule in the third slot of the list 
# (first element of list is 0)

students_dict = defaultdict(lambda: [""]*len(periods))
# a defaultdict that when a new student (not previously stored) is seen
# initializes that student to have an empty schedule.
# the empty schedule is an empty list that is as long as the number of periods,
# with each period an empty string ''
# student_dict once fully populated will be of form
# {'Chris': ['Math', '', 'Science'], 'Kim': ['', 'History', 'Science']}
# Note student_dict['Chris'] = ['Math', '', 'Science'] and 
# student_dict['Chris'][0] = 'Math'.
for (student_name, course, period) in enrolls:
    students_dict[student_name][period_dict[period]] = course
    # go through the list of enrolls and assign courses to the schedule in the appropriate spots.

student_list = list(student_dict.items())    
# [['Chris', ['Math', '', 'Science']], ['Kim', ['', 'History', 'Science']],]

Then return student_list and periods to your template through the context variables, and use something like

<table>
<thead>
  <tr> 
    <th>Student</th>
  {% for per in periods %}
    <th>{{ per }}</th>
  {% endfor %}
  </tr>
</thead>
<tbody>
  {% for student_row in student_list %}
     <tr><th>{{ student_row.0 }}</th>
     {% for class in student_row.1 %}
        <td>{{ class }}</td>
     {% endfor %}
     </tr>
  {% endfor %}
</tbody>
</table>

Upvotes: 4

Related Questions