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
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.