python处理excel数据

python处理excel数据

最近老师交给我一个小任务，就是教务处导出了全校同学的期末成绩，但是老师只想要他带的班级同学的期末成绩，并计算出总成绩。老师给我了全校总表和他的班级表（带有平时成绩的）。
对于一个对Python完全不太会的人，开始了一脸懵的学习。

PS:该过程必须要很简单，不会电脑的也能操作。

第1步：安装python

官网下载安装包：https://www.python.org/
安装教程：
https://blog.youkuaiyun.com/m0_65379736/article/details/124630197

1、安装库：
打开cmd
Pip源配置：

pip config set global.index-url https://mirrors.aliyun.com/pypi/simple/
pip config set install.trusted-host mirrors.aliyun.com

注意：openpyxl不支持打开xls，pandas后续不再支持打开xls，xlrd 2.0.1 仅支持 .xls 文件。建议将xls转换为xlsx，openpyxl处理更方便。

pip install openpyxl
pip install pandas
pip install matplotlib
pip install xlwt
pip install xlrd==1.2.0
pip install xlutils

在安装库的时候出现黄色字体的警告，可以执行python -m pip install --upgrade pip解决

2、打开idle

第2步：新建py文件写代码

在idle新建py文件，保存，运行。

1、读取文件，并从2022-6期考成绩(6.10)表 读取期末成绩批量写入待处理的表格

代码：

import pandas as pd
import os
import xlwt
import xlrd
from xlutils.copy import copy

inputdir_path = r'D:/learning/pywork/source/'
outputdir_path = 'D:/learning/pywork/result/'
def main():
    filelist = os.listdir(inputdir_path)
    df_all = pd.read_excel('D:/learning/pywork/alldata/testscore.xlsx')
    df_select = df_all[['学号','成绩']]
    for filename in filelist:
        excelPath = os.path.join(inputdir_path,filename)
        df = pd.read_excel(excelPath,header=1,dtype={'学号':str})
        data = df.merge(df_select,on='学号', right_index=False,left_index=False, sort=False)
        print(data)
        data[['期末成绩']] = data[['成绩']]
        data2 = data.drop(labels='成绩', axis=1)
        data2['总成绩'] = data2['期末成绩']*0.5 + data2['平时成绩']*0.5
        print(data.columns)
        data2.to_excel('D:/learning/pywork/result/'+filename, encoding='gbk',index=False)   
        print(filename+'********文件处理成功')

def addMsg():
    filelist = os.listdir(outputdir_path)
    for filename in filelist:
        excelPath = os.path.join(outputdir_path,filename)
        oldwb = xlrd.open_workbook(excelPath)
        oldWbS = oldwb.sheet_by_index(0)
        newwb = copy(oldwb)
        newWs = newwb.get_sheet(0)
        newWs.write_merge(0, 0, 0, 7, '当前成绩录入方式：平时成绩:50%,期末成绩:50%')
        inserRowNo=0
        for rowIndex in range(inserRowNo, oldWbS.nrows):
            for colIndex in range(oldWbS.ncols):
                newWs.write(rowIndex + 1, colIndex, oldWbS.cell(rowIndex, colIndex).value);

        newwb.save('D:/learning/pywork/result/'+filename)
        print(filename+'成功')
 
        
if __name__ == "__main__":
    main()
    addMsg()

2、计算总成绩统计画图，自行命名保存

代码：

import pandas as pd
import numpy as np
import os 
import matplotlib.pyplot as plt
inputdir_path = r'D:/learning/pywork/result/'
def main():
    filelist = os.listdir(inputdir_path)
    # 设置分段
    bins=[0,60,70,80,90,100]
    # 设置标签
    labels=['不及格','及格','中等','良好','优秀']
    for filename in filelist:
        excelPath = os.path.join(inputdir_path, filename)
        df = pd.read_excel(excelPath)
        print(df.head)
        x = df['总成绩']
        print(df.describe())       # 查看最大值与最小值
        segments = pd.cut(x, bins,labels=labels,right=True)    # 对数据进行离散化处理
        print(segments)
        plt.rcParams['font.family']='simhei'
        # 统计各分段人数
        counts=pd.value_counts(segments,sort=False)
        # 绘制柱状图
        b=plt.bar(counts.index,counts)
        print(b)
        # 添加数据标签
        plt.bar_label(b,counts)
        #折线图
        plt.plot(counts.index, counts, "r", marker='*')
        plt.show()
if __name__ == "__main__":
    main()

小结

这个方案应该是最简单的吧，最开始还装了Anaconda。琢磨了半天虚拟环境的配置，发现真的是很方便的啊。
excel的批处理，我也是很迷惑，可能这就是菜鸟的日常艰难生活。