This paper describes a system based onPythonlanguage, realizing theMultiple different Excel filescarry outData reading and average value calculationThe methodology.
First, let's look at a specific requirement: there is currently a folder that holds a large number ofExcelfile; the file name is the name of each student, i.e., there is no pattern to the file name.
And every file isA classmate.treat (sb a certain way)of the whole class except yourself.For each scoring, we take one of theExcelDocumentation as an example to look at:
It can be seen that the class size (i.e., tablerow) are numerous and need to be scored (i.e., formscolumns) There are11(not counting the total number of points); also, the inability to rate yourself results in a row of no data in each table.
And all we need to do is to ask for each student's,11The average scores for each of the individual scoring items are stored in a new, total file with table headers (row and column headers) that match everyone's scoring file, as shown below. Each of these cells represents the average calculated for this student, for this scoring item, after it has been scored by every student in the class except him or her.
As you can see, one would need to count11times average, not to mention that a class will have dozens of students. If the class is averaged individually withExcelCalculations that are very cumbersome.
with the help ofPythonThe code will be much simpler. Specific code is as follows. Here, it is no longer like the usual machine learning, deep learning code blog, the code to be paragraph by paragraph, sub-part of the specific explanation, listed directly in all the code, you can refer to the comments to understand.
# -*- coding: utf-8 -*-
"""
Created on Thu Apr 8 16:24:41 2021
@author: fkxxgis
"""
import os
import numpy as np
from openpyxl import load_workbook
file_path='F:/Class President/2020-2021 Comprehensive Assessment and Evaluation of Awards and Merits/01_Comprehensive Assessment/Geoscience XXXX Class Mutual Evaluation Score Sheet/' # Here is the path to the Excel file where each student's scores are stored.
output_path='F:/Class President/2020-2021 Comprehensive Assessment and Evaluation of Prizes and Awards/01_Comprehensive Assessment/Geoscience XXXX Class Comprehensive Quality Assessment Mutual Evaluation Scoring Sheet.xlsx' # Here is the path to the final result, please don't match the above path.
first_row=5 # of rows where the first score is
first_column=3 # of columns in which the first fraction is located
all_row=32 # Total number of classmates
all_column=11 # of score items to be calculated
all_excel=(file_path) # Get all Excel files under the path of the scoring file
file_row=first_row+all_row-1
file_column=first_column+all_column-1
all_mean_score=((file_row,file_column),dtype=float) # Create a new two-dimensional array that holds the average of the scores for each student, for each event
for now_row in range(first_row,file_row+1):
for now_column in range (first_column,file_column+1):
all_score=[]
for excel_num in range(0,len(all_excel)):
now_excel=load_workbook(file_path+all_excel[excel_num]) # Open the first scoring Excel file
all_sheet=now_excel.get_sheet_names() # Get all the Sheet names of the scoring file
now_sheet=now_excel.get_sheet_by_name(all_sheet[0]) # In this article the scores are all stored in the first Sheet, so the subscript is 0
single_score=now_sheet.cell(now_row,now_column).value # Get the corresponding cell data
if single_score==None: # If this cell is empty (i.e., the row where you don't rate yourself)
pass
else:
all_score.append(single_score)
all_mean_score[now_row-1,now_column-1]=(all_score) # Calculate the average of the scores given by all the students for this one student, for this one marking scheme.
output_excel=load_workbook(output_path) #Read the results to store in Excel
output_all_sheet=output_excel.get_sheet_names() # The code here means the same as above
output_sheet=output_excel.get_sheet_by_name(output_all_sheet[0])
output_sheet=output_excel.active
for output_now_row in range(first_row,file_row+1):
for output_now_column in range (first_column,file_column+1):
exec("output_sheet.cell(output_now_row,output_now_column).value=all_mean_score[output_now_row-1,output_now_column-1]") # Write the final average scores for each student and each scored item in the two-dimensional array to the corresponding location in the results file
output_excel.save(output_path)
At this point, the job is done.
This article on the implementation of Python to read a large number of Excel files and cross-file batch calculation of the average value of the article is introduced to this, more relevant Python to read Excel content please search my previous posts or continue to browse the following related articles I hope that you will support me in the future more!