Python+excel

Posted on Edited on Views:

联动Excel

基本库

pandas，pandas提供了一个称为DataFrame的数据结构，可以方便地加载、处理和分析表格数据。对于刚创建的虚拟环境，需要安装：pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas openpyxl两个库。openpyxl 是一个用于处理 Excel 文件（.xlsx 格式）的第三方库，用于 pandas 中的 to_excel 方法，所以确保在运行 df.to_excel 之前，openpyxl 已成功安装

举例

创建一个简单的表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import pandas as pd

# 创建一个字典，包含表格的数据
data = {
    '姓名': ['小明', '小红', '小刚'],
    '年龄': [25, 22, 28],
    '城市': ['北京', '上海', '深圳']
}

# 使用字典创建一个DataFrame
df = pd.DataFrame(data)

# 打印表格数据
print(df)

# 将表格保存为Excel文件
path = '111.xlsx'
df.to_excel(path, index=False)

DataFrame是pandas库中的一个类，用于表示二维数据表格，传递一个字典给它，就创建了一个数据框对象，而这个对象的to_excel方法可以将数据框保存为Excel文件

DataFrame.to_excel(excel_writer, sheet_name='Sheet1', index=True, ...)可见，可以设置工作表的名称，index表示是否包含行索引

读取已存在excel数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import pandas as pd

# 读取Excel文件
excel_file_path = '111.xlsx'
df = pd.read_excel(excel_file_path)

# 打印前几行数据
print(df.head())

# 对数据进行简单处理，例如添加一列
df['新列'] = df['原列'] * 2

# 打印处理后的数据
print("\n处理后的数据:")
print(df.head())

# 将处理后的数据保存到新的Excel文件
path = '222.xlsx'
df.to_excel(path, index=False)

用pd的read_excel方法读取文件，df.head()用于返回数据框的前几行，默认为5，该方法通常用于查看开头部分以了解数据结构和其内容

12.28更新：老师给我发了十几份excel文件，让我算每个同学的总分，这里顺便学习记录一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import pandas as pd
import glob

# 获取所有 Excel 文件的文件名
file_names = glob.glob("D:/Downloads/理论力学作业成绩/*.xlsx")
# print(file_names)

# 创建一个空的 DataFrame 用于存储合并后的数据
merged_data = pd.DataFrame()

# 循环读取每个 Excel 文件并合并数据
for file in file_names:
    df = pd.read_excel(file)
    merged_data = pd.concat([merged_data, df], ignore_index=True)
    # print(merged_data.columns)
    # break

# 使用 Pandas 的 groupby 和 sum 函数计算每个学生的总分
total_scores = merged_data.groupby(['学生姓名', '学号/工号', '院系', '专业', '班级'])['分数'].sum().reset_index()

# 打印结果
print(total_scores)

# 保存结果到新的 Excel 文件
total_scores.to_excel("D:/Downloads/理论力学作业成绩/total_scores.xlsx", index=False)

glob：全称是Global regualar expression print，但在python中，glob模块主要用于文件路径匹配，而不涉及正则表达式，直接理解成global的缩写，用于匹配全局文件路径

pd.concat：用于将多个pandas对象（例如DataFrame）进行纵向连接，一个一个读取，一步一步添加至merged_data（所有内容，在excel中就是复制粘贴的操作） ignore_index=True表示在连接时忽略原来的索引，使用新的连续整数索引，如果不设置，那么连接后的df将保留原来两个df的索引，会导致索引重复

df.columns：用于查看当前df的列名

df.groupby：按照指定的列对df进行分组：.groupby('学生姓名')是一个按照’学生姓名’列进行分组的操作，后续的聚合操作将基于这个分组进行计算；['分数'].sum()表示想要对分数进行聚合操作；.reset_index()：将最终结果重新设置索引

完善：上述的结果只会得到：学生姓名+分数两列数据，想要把班级学号也加进去：.groupby(['学生姓名', '学号/工号', '院系', '专业', '班级'])

得到表格后，就可以进行美化了，居中、算平均值之类的。这些操作就不需要在代码里完成了，显然直接操作Excel软件效率更高

代码有代码擅长的事，软件也有软件的优势，根据需求正确选择生产工具从而提高生产力是很重要的

过程中遇到的报错：

  • PermissionError: [Errno 13] Permission denied: 'D:/Downloads/total_scores1.xlsx'

    访问错误，就是文件被打开占用了，关闭即可

  • KeyError: '学生姓名'

    表明使用groupby操作时，原表格没有名为“学生姓名”的列。实际上是有的，只不过表格第一行是统一的大标题，所以要对每个表格做预处理，把第一行的大标题删了，这样就可以读取到每列的第一行。可以使用print(merged_data.columns)查看列名