This article introduces the Python language, based on the realization of a number of different Excel files for data reading and average value calculation method~.
I recommend learning python books
First, let's look at the specific requirements: there is currently a folder that holds a large number of Excel files; the file names are the names of each student, i.e. there is no pattern to the file names.
And while each file is a student's scoring of each of the students in the class other than themselves, let's look at one of the Excel files as an example:
It can be seen that the number of students in the class (i.e., the number of rows of the table) is large, the number of items to be scored (i.e., the number of columns of the table) is11(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 there will be dozens of students in a class. It would be very cumbersome to calculate them individually in Excel.
And with the help of Python, it's a lot easier. 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 scored 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 scoring item.
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.
Supplementary: python batch read Excel file
Reads an xlsx file from the same folder:
import os
import pandas as pd
path = r'./path of file'
for i in (path):
df = pd.read_excel((path,i))This article on 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 related Python to read a large number of Excel files, please search for my previous posts or continue to browse the following articles I hope that you will support me in the future more!