Reading multiple row headers in a excel using Pandas

Question

I am trying to read the excel which contains the student test details using pandas.

import pandas as pd

frame = pd.read_excel('Test.xlsx',skiprows=3)
print(frame)

there are 3 extra rows those I am skipping. But the problem is I have the same headers repeated for each student.

  A        B          C         D           E

  Class   First
        StudentID   0001    StudentName   Name
                  DateOfTest  TestTitle   TestMarks  TestDuration
                  12/02/2019    Eng           97        12:30:00
                  13/02/2019    Mat           91        12:30:00
                  14/02/2019    Phy           81        12:30:00
  Class   Second
        StudentID   0002    StudentName   Name
                  DateOfTest  TestTitle   TestMarks  TestDuration
                  12/02/2019    Eng           67        12:30:00
                  13/02/2019    Mat           41        12:30:00
                  14/02/2019    Phy           81        12:30:00

using the above code I can read all the data as a single frame but I need multiple frames. How Can I read each student's test details and it's helpful is there any way to read the class name and student details Please find the attached file Test.xlsx

The example that you have shared, is it the excel or the dataframe that you get after importing the excel? In either case, can you also share the other? — Vishakha Lall
– Vishakha Lall, Commented Jan 29, 2020 at 4:44
it's the content from excel I added column names also at the top — goodman
– goodman, Commented Jan 29, 2020 at 4:55
Post the Test.xlsx as well. It is difficult to say if there is improper reading of the file. — SKPS
– SKPS, Commented Jan 29, 2020 at 5:06
No there isn't. Write a separate script to split your file into smaller ones by looking for the column name "Class" in the line. You can read each file in a loop with pandas. — cs95
– cs95, Commented Jan 29, 2020 at 5:11
@SwarajSomala I see. I really doubt that you can create two dataframes out of a single xlsx file. It should be possible to by doing some "tricks" but why not keep them in separate files? There are several posts about combining multiple dataframes into a single file but not vice versa. — SKPS
– SKPS, Commented Jan 29, 2020 at 6:39

Santhy K · Accepted Answer · 2020-01-29 08:18:06Z

here is a sample code which iterate and create a dictionary with nested structure. You can create a needed structure according to you, in this way:-

import pandas as pd

df = pd.read_excel('Test.xlsx',  skiprows=2,names=['A', 'B', 'C','D','E'])
print(df)
finalDict={}
currentClass = ''
currentStudent = ''
for row in df.iterrows():
   if row[1]['A'] == 'Class':
       currentClass = row[1]['B']
       finalDict[currentClass] = {'students' : {}}
   elif row[1]['B'] == 'StudentID':
       currentStudent = row[1]['C']
       currentStudentName = row[1]['E']
       finalDict[currentClass]['students'][currentStudent]={'StudentID': currentStudent, 'StudentName' : currentStudentName,'marks': []}
   elif row[1]['C'] == 'Eng' or row[1]['C'] == 'Mat' or row[1]['C'] == 'Phy':
       DateOfTest = row[1]['B'].strftime("%Y-%m-%d")
       TestDuration = row[1]['E'].strftime("%H:%M:%S")
       finalDict[currentClass]['students'][currentStudent]['marks'].append(
           {'DateOfTest':DateOfTest,
            'TestTitle':row[1]['C'],
            'TestMarks':row[1]['D'],
            'TestDuration':TestDuration}
       )
print("\nOUTPUT:\n")
print(finalDict)

which outputs something like:-

{
  'First': {
    'students': {
      1: {
        'StudentID': 1,
        'StudentName': 'Name1',
        'marks': [{'DateOfTest': '2019-02-12','TestTitle': 'Eng','TestMarks': 90,'TestDuration': '12:30:00'},....]
      }
    }
  },
  'Second': {
    'students': {
      2: {
        'StudentID': 2,
        'StudentName': 'Name2',
        'marks': [{},.....]
      }
    }
  }
}

Collectives™ on Stack Overflow

Reading multiple row headers in a excel using Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related