Pandas Dataframe Pivot - how to display multiple values in separate rows grouped by index

Question

I don't know how exactly to phrase the title, but I will explain my issue below. I'm using pandas dataframe in django. I'm very new to pandas and I'm having a hard time with showing all the intended information in my dataframe.

This is the model I'm working with -

class UnderG(models.Model):

    semester = models.CharField(max_length = 100)
    course_code = models.CharField(max_length = 10)
    course_title = models.CharField(max_length = 100)
    course_credit = models.FloatField()
    course_grade = models.CharField(max_length = 10)
    course_no = models.CharField(max_length = 5)
    class Meta: 
        db_table = "under_g" 

This is my view.py file

from django.shortcuts import render
from .models import UnderG
import pandas as pd
import numpy as np
import json

 def btechstudent1(request):

    sem3 =  UnderG.objects.all().values()
    df = pd.DataFrame(sem3)
    df3 = df.reset_index().pivot(index='semester', columns='course_no', values='course_code')
    print(df3)

Right now, the result I get from printing df3 is -

course_no           1        2           3       4       5       6       7       8       9
semester
1              ABC100   BCD100      CDE100  DEF100  EFG100  FGH101  GHI100  HIJ100  IJK100
2              JKL100   KLM100      LMN100  MNOL101 NOP100  OPR100  PRQ100     NaN     NaN
3              ABC202   ABC215      ABC106  ABC102  CBD106  ABC101     NaN     NaN     NaN
4              ABC226   ABC216      ABC205  ABC100  ABC2XX  ABC290     NaN     NaN     NaN
5          ABC333/XX1   ABC334         NaN  ABC351  ABC100  ABC2XX  ABC3XX     NaN     NaN
6          ABC362/XX1   BCD331      CBD352  BCLXXX  CBL2XX  BDL380     NaN     NaN     NaN
7             UE 2(3)  YC 1(3)  CTY400/402     NaN     NaN     NaN     NaN     NaN     NaN
8             BC 2(4)  KC 3(3)     NE 3(4)   JK3XX     NaN     NaN     NaN     NaN     NaN

The issue is that I want the course grade of each course to display under it i.e. I want each semester group to span 3 rows(when it is later shown in the template as an html table). The first row will have the course codes, the second should have the course credits and the third the course grades.

This is my intended result -

course_no           1        2           3       4       5       6       7       8       9
semester
1              ABC100   BCD100      CDE100  DEF100  EFG100  FGH101  GHI100  HIJ100  IJK100
credits             4        4           3      4        5       2       1       1       3
grades              A        B          B-      C        D       A       F       B       B
2              JKL100   KLM100      LMN100  MNOL101 NOP100  OPR100  PRQ100     NaN     NaN
credits             4        3           3      4        5       2       1              
grades              A        E          B-      C        D       A       F              
3              ABC202   ABC215      ABC106  ABC102  CBD106  ABC101     NaN     NaN     NaN
credits             2        4           3      4        5       2                     
grades              C        B          B-      C        D       A                    
4              ABC226   ABC216      ABC205  ABC100  ABC2XX  ABC290     NaN     NaN     NaN
credits             4        4           3      4        5       2                   
grades              D        B          B-      C        D       A                     
5          ABC333/XX1   ABC334         NaN  ABC351  ABC100  ABC2XX  ABC3XX     NaN     NaN
credits             2        4                 3        5       2       1              
grades              A        B                 C        D       A       F              
6          ABC362/XX1   BCD331      CBD352  BCLXXX  CBL2XX  BDL380     NaN     NaN     NaN
credits             4        4           3      5        5       2                     
grades              A        D          B-      C        A       A                     
7             UE 2(3)  YC 1(3)  CTY400/402     NaN     NaN     NaN     NaN     NaN     NaN
credits             4        4           3                                          
grades              A        B          B-                                            
8             BC 2(4)  KC 3(3)     NE 3(4)   JK3XX     NaN     NaN     NaN     NaN     NaN
credits             1        4           3      4                                    
grades              A        F          B-      C-                                  

I tried passing course_grades in the pivot filter under values -

df3 = df.reset_index().pivot(index='semester', columns='course_no', values=['course_code','course_grade'])

but that gives me two side by side tables, one with course codes in the cells and one with grades. What command should I be using in place of the pivot ? Any help appreciated.

question from:https://stackoverflow.com/questions/65642561/pandas-dataframe-pivot-how-to-display-multiple-values-in-separate-rows-grouped

Answer 1

Waitting for answers