How to use Python to deal with students' grade sheets? In practical problems, we often use excel to complete this work. For example, calculate the average score, total score, number of qualified people, etc. How do you do this with Python?
preparation in advance
- Python3.9
- Required module pandas openpyxl
- Grade sheet xlsx
win10 installation pyton environment
Python installation package download address: https://www.python.org/downloads/ Open the link, click the version number or Download button in the figure below to enter the Download page of the corresponding version, and scroll to the end to see the Python installation package of each platform.
Description of prefix: The 64 bit Python installer starts with Windows Installer (64 bit); Starting with Windows installer (32-bit) is a 32-bit Python installer. Description of suffix: embeddable zip file indicates The green installation free version in ZIP format can be directly embedded (integrated) into other applications; executable installer indicates exe format executable program, which is a complete offline installation package. Generally, this can be selected; Web based installer means that it is installed through the network, that is, it is downloaded to an empty shell. During the installation process, it is also necessary to download the real Python installation package online.
*Note that check Add Python 3.9 to PATH to add the directory where the python command tool is located to the system Path environment variable. It will be very convenient to develop programs or run Python commands in the future.
Continue to click next, and you will soon complete the python installation. verification Enter python in cmd to see the echo, indicating that the installation is successful. a master hand 's first small display
print ("kali's blog https://blog.bbskali.cn")
This completes the Python installation
Python processing excel
Install the corresponding module, because the operation of excel is inseparable from the corresponding library of python. Here we use pandas openpyxl. Execute the following command in cmd to install!
python pip install pandas python pip install openpyxl
It should be noted that the speed of pip installation is relatively slow. We can change the source of Python to improve the download speed. Please refer to the following article for details.
Change the Pip download source to make the download speed fly
Open Excel with Python
import pandas as pd
import openpyxl
df=pd.read_excel('H:\chengji.xlsx', sheet_name='Sheet1') #Read the specified table
print(df)sheet_name reads the table in Excel for us
Find the total and average scores of students
Functions used sum: sum mean: average score Note that axis 0 is the column and 1 is the row
import pandas as pd
import openpyxl
df=pd.read_excel('H:\chengji.xlsx', sheet_name='Sheet1') #Read the specified table
temp = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]]
df["Total score"] = temp.sum(axis=1) #axis 0 is the column and 1 is the row
df["average"] = temp.mean(axis=1)Find the average and highest score of each subject
import pandas as pd
import openpyxl
df=pd.read_excel('H:\chengji.xlsx', sheet_name='Sheet1') #Read the specified table
temp = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]]
Total = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]].mean()
Tota2 = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]].max()Judge the number of pass in the score array
df1 = df[df[['language','mathematics','English']] >= 90]
print('Number of qualified persons:',df1[['language','mathematics','English']].count())Judge the number of people taking the exam
df2 = df[['language','mathematics','English']]
print('Number of examinees:',df2[['language','mathematics','English']].count())Save data
writer = pd.ExcelWriter('H:\ 2.xlsx')
writer.save()#file save
writer.close()#File closeEffect of processing completion
Complete code
# -*- coding: UTF-8 -*-
import pandas as pd
import openpyxl
df=pd.read_excel('H:\chengji.xlsx', sheet_name='date1') #Read the specified table
temp = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Raw land"]]
df["Total score"] = temp.sum(axis=1)#axis 0 is the column and 1 is the row
df["average"] = temp.mean(axis=1)
Total = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]].mean()
Tota2 = df[["language","mathematics","English","Physics","Chemistry","Morality and rule of law","history","Birthplace"]].max()
writer = pd.ExcelWriter('H:\ 2.xlsx')
df.to_excel(writer,sheet_name='Sheet1')
Total.to_excel(writer,sheet_name='Sheet2')
Tota2.to_excel(writer,sheet_name='Sheet3')
# Judge the number of pass in the score array
df1 = df[df[['language','mathematics','English']] >= 90]
print('Number of qualified persons:',df1[['language','mathematics','English']].count())
df2 = df[['language','mathematics','English']]
print('Number of examinees:',df2[['language','mathematics','English']].count())
writer.save()#file save
writer.close()#File close
templ= r"H:\ 2.xlsx"
wb = openpyxl.load_workbook(templ)
#Specify cell save
ws = wb['Sheet1']
ws['c189'].value = 'Average score'
ws['d189'].value = Total['language']
ws['e189'].value = Total['mathematics']
ws['f189'].value = Total['English']
ws['g189'].value = Total['Physics']
ws['h189'].value = Total['Chemistry']
ws['i189'].value = Total['Morality and rule of law']
ws['j189'].value = Total['history']
ws['k189'].value = Total['Birthplace']
ws['c190'].value = 'Number of pass'
ws['d190'].value = df1['language'].count()
ws['e190'].value = df1['mathematics'].count()
ws['f190'].value = df1['English'].count()
#pass rate
ws['d190'].value = df1['language'].count() / df2['language'].count()
wb.save(r"H:\ 2.xlsx") The appeal code can be modified according to your actual situation. For example, among the passing number, I calculate by > = 90 points.
Copyright: big cousin xiaoyaozi
Link to this article: https://blog.bbskali.cn/2505.html
Licensed under the Creative Commons Attribution - non-commercial use 4.0 international agreement, reprinting of cited articles shall follow the same agreement.