使用Pandas读取excel中的多个行标题

Question

我正在尝试使用pandas阅读包含学生考试详细信息的excel

import pandas as pd

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

还有3行我要跳过。但问题是我为每个学生重复了相同的标题

  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

使用上面的代码，我可以读取所有的数据作为一个单一的帧，但我需要多个帧。我如何阅读每个学生的考试细节，这对我很有帮助。有没有办法阅读学生的名字和详细信息 请查找附件Test.xlsx

Answer 1

下面是一个示例代码，它迭代并创建具有嵌套结构的字典。 您可以按照以下方式创建所需的结构：-

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)

输出如下内容：

{
  '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': [{},.....]
      }
    }
  }
}