Transform the below data as required using python

Question

I had input in below format:

Expected output is in below format:

I tried using pandas data frame to format the data & used the normalize techniques but it didn't work as per expectation.

So posting here with my input & output images

if need a copy then please copy from below

Answer 1

If you have an excel file, you can use the code below:

df = pd.read_excel('exam.xlsx', index_col=[0, 1, 2, 3, 4], header=[0, 1])
df.index.names = ['STUDENT_ID', 'NAME', 'CLASS', 'SECTION', 'SCHOOL']
df = df.stack('EXAM').rename_axis(columns=None).reset_index()

Output:

>>> df
   STUDENT_ID NAME  CLASS SECTION    SCHOOL EXAM  LANGUAGE1  LANGUAGE2  MATHS
0           1  JOE      8       A  X-SCHOOL   Q1         80         80     80
1           1  JOE      8       A  X-SCHOOL   Q2         90         90     90
2           2  MAX      8       A  Y-SCHOOL   Q1         81         81     81
3           2  MAX      8       A  Y-SCHOOL   Q2         91         91     91

Note: you should provide a link to your data or any way to reproduce your input.