Problem with automatic merge column levels in pandas pivot-CodePudding

This is my base dataframe:

Course      Subject      Score Teacher     Student 
course_1    subject_1    18    teacher1    student_1
course_1    subject_1    16    teacher1    student_3
course_1    subject_1    18    teacher1    student_2
course_1    subject_2    19    teacher1    student_1
course_1    subject_2    20    teacher1    student_3
course_1    subject_3    17    teacher1    student_2
course_1    subject_3    18    teacher1    student_1
course_1    subject_3    13    teacher1    student_3

I used this method to create pivot table:

pivot_df = pd.pivot_table(df, values="Score", index=["Course", "Subject"], columns=["Teacher", "Student"])

And I want something like this:

                        teacher1
                        student_1  student_2   student_3
course_1  subject_1       18           18           16
          subject_2       19           Nan          20
          subject_3       18           17           13

But pandas give me this:

                        teacher1\student_1  teacher1\student_2   teacher1\student_3
course_1\subject_1                  18           18                    16
course_1\subject_2                  19           Nan                   20
course_1\subject_3                  18           17                    13

How to fix this problem?

CodePudding user response：

I don't reproduce your issue (tested with pandas versions 1.3.5 and 1.4.1).

I am getting a MultiIndex as expected:

Teacher             teacher1                    
Student            student_1 student_2 student_3
Course   Subject                                
course_1 subject_1      18.0      18.0      16.0
         subject_2      19.0       NaN      20.0
         subject_3      18.0      17.0      13.0

As image:

used input:

df = pd.DataFrame({'Course': ['course_1', 'course_1', 'course_1', 'course_1', 'course_1', 'course_1', 'course_1', 'course_1'],
                   'Subject': ['subject_1', 'subject_1', 'subject_1', 'subject_2', 'subject_2', 'subject_3', 'subject_3', 'subject_3'],
                   'Score': [18, 16, 18, 19, 20, 17, 18, 13],
                   'Teacher': ['teacher1', 'teacher1', 'teacher1', 'teacher1', 'teacher1', 'teacher1', 'teacher1', 'teacher1'],
                   'Student': ['student_1', 'student_3', 'student_2', 'student_1', 'student_3', 'student_2', 'student_1', 'student_3']})