必学的 20 个 Excel 表格操作 Python 脚本-51CTO.COM

本文将介绍使用 Python 处理 Excel 文件的多种方法，涵盖从基本的读写操作到高级的数据分析与可视化。通过这些示例，你可以学习如何高效地管理和分析 Excel 数据。

1. 安装必要的库

在开始之前，确保安装了 pandas 和 openpyxl 这两个库。这两个库是处理 Excel 文件的基础。

pip install pandas openpyxl

2. 读取 Excel 文件

首先，让我们看看如何读取一个 Excel 文件。

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('example.xlsx', engine='openpyxl')

# 显示前五行数据
print(df.head())

输出：

   A    B     C
0  1  100  1000
1  2  200  2000
2  3  300  3000
3  4  400  4000
4  5  500  5000

3. 写入 Excel 文件

接下来，我们将创建一个新的 DataFrame 并将其写入新的 Excel 文件。

import pandas as pd

# 创建一个 DataFrame
data = {
    'A': [1, 2, 3, 4, 5],
    'B': [100, 200, 300, 400, 500],
    'C': [1000, 2000, 3000, 4000, 5000]
}
df = pd.DataFrame(data)

# 将 DataFrame 写入 Excel 文件
df.to_excel('output.xlsx', index=False)

4. 选择特定列

有时候我们只需要 Excel 文件中的某些列。

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('example.xlsx', usecols=['A', 'C'])

# 显示前五行数据
print(df.head())

输出：

5. 过滤数据

过滤数据可以帮助我们找到特定条件下的记录。

import pandas as pd

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

# 过滤出 A 列大于 3 的行
filtered_df = df[df['A'] > 3]

# 显示过滤后的数据
print(filtered_df)

输出：

   A    B     C
3  4  400  4000
4  5  500  5000

6. 数据排序

排序数据可以让我们更容易地查看数据的趋势。

import pandas as pd

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

# 按照 A 列降序排列
sorted_df = df.sort_values(by='A', ascending=False)

# 显示排序后的数据
print(sorted_df)

输出：

   A    B     C
4  5  500  5000
3  4  400  4000
2  3  300  3000
1  2  200  2000
0  1  100  1000

7. 数据分组

数据分组可以帮助我们分析不同类别的数据。

import pandas as pd

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

# 按照 B 列分组并计算平均值
grouped_df = df.groupby('B').mean()

# 显示分组后的数据
print(grouped_df)

输出：

          A        C
B                    
100  1.000000  1000.0
200  2.000000  2000.0
300  3.000000  3000.0
400  4.000000  4000.0
500  5.000000  5000.0

8. 添加新列

我们可以根据现有列的数据轻松添加新列。

import pandas as pd

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

# 添加新列 D
df['D'] = df['A'] * df['B']

# 显示更新后的数据
print(df)

输出：

   A    B     C     D
0  1  100  1000   100
1  2  200  2000   400
2  3  300  3000   900
3  4  400  4000  1600
4  5  500  5000  2500

9. 更新单元格

有时候我们需要更新特定单元格的值。

import pandas as pd

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

# 更新 A 列第 2 行的值
df.at[1, 'A'] = 10

# 显示更新后的数据
print(df)

输出：

   A    B     C
0  1  100  1000
1 10  200  2000
2  3  300  3000
3  4  400  4000
4  5  500  5000

10. 删除列

删除不需要的列可以简化数据集。

import pandas as pd

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

# 删除 C 列
del df['C']

# 显示更新后的数据
print(df)

输出：

11. 合并多个 Excel 文件

合并多个 Excel 文件可以方便地将数据集中到一起。

import pandas as pd

# 读取多个 Excel 文件
df1 = pd.read_excel('example1.xlsx')
df2 = pd.read_excel('example2.xlsx')

# 合并两个 DataFrame
merged_df = pd.concat([df1, df2], ignore_index=True)

# 显示合并后的数据
print(merged_df)

输出：

   A    B     C
0  1  100  1000
1  2  200  2000
2  3  300  3000
3  4  400  4000
4  5  500  5000

12. 数据透视表

数据透视表是一种强大的工具，可以快速汇总和分析数据。

import pandas as pd

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

# 创建数据透视表
pivot_table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc=sum)

# 显示数据透视表
print(pivot_table)

输出：

B       100   200   300   400   500
A                        
1       1000  NaN   NaN   NaN   NaN
2       NaN   2000  NaN   NaN   NaN
3       NaN   NaN   3000  NaN   NaN
4       NaN   NaN   NaN   4000  NaN
5       NaN   NaN   NaN   NaN   5000

13. 数据合并

合并多个数据集可以让你更好地分析数据之间的关系。

import pandas as pd

# 读取两个 Excel 文件
df1 = pd.read_excel('example1.xlsx')
df2 = pd.read_excel('example2.xlsx')

# 使用内连接合并两个数据集
merged_df = pd.merge(df1, df2, on='A', how='inner')

# 显示合并后的数据
print(merged_df)

输出：

   A    B_x    C_x    B_y    C_y
0  1   100  1000    10    100
1  2   200  2000    20    200
2  3   300  3000    30    300
3  4   400  4000    40    400
4  5   500  5000    50    500

14. 数据清洗

数据清洗是数据分析的重要步骤，可以去除无效或错误的数据。

import pandas as pd

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

# 去除空值
df.dropna(inplace=True)

# 去除重复行
df.drop_duplicates(inplace=True)

# 显示清洗后的数据
print(df)

输出：

   A    B     C
0  1  100  1000
1  2  200  2000
2  3  300  3000
3  4  400  4000
4  5  500  5000

15. 数据类型转换

正确设置数据类型有助于节省内存并提高性能。

import pandas as pd

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

# 将 A 列转换为整型
df['A'] = df['A'].astype(int)

# 将 B 列转换为浮点型
df['B'] = df['B'].astype(float)

# 显示转换后的数据
print(df.dtypes)

输出：

A         int64
B        float64
C        float64
dtype: object

16. 数据可视化

使用 matplotlib 库可以方便地绘制图表。

import pandas as pd
import matplotlib.pyplot as plt

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

# 绘制柱状图
plt.bar(df['A'], df['B'])
plt.xlabel('A')
plt.ylabel('B')
plt.title('A vs B')
plt.show()

17. 多页 Excel 文件操作

处理多页 Excel 文件时，可以使用 openpyxl 库。

from openpyxl import load_workbook

# 加载 Excel 文件
wb = load_workbook('example.xlsx')

# 获取所有工作表名称
sheet_names = wb.sheetnames
print(sheet_names)

# 选择特定工作表
sheet = wb['Sheet1']

# 读取特定单元格的值
cell_value = sheet.cell(row=1, column=1).value
print(cell_value)

输出：

['Sheet1', 'Sheet2', 'Sheet3']
1

18. 条件格式化

条件格式化可以帮助你突出显示特定数据。

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill

# 创建一个新的 DataFrame
data = {
    'A': [1, 2, 3, 4, 5],
    'B': [100, 200, 300, 400, 500],
    'C': [1000, 2000, 3000, 4000, 5000]
}
df = pd.DataFrame(data)

# 将 DataFrame 写入 Excel 文件
wb = Workbook()
ws = wb.active
df.to_excel(ws, index=False)

# 设置条件格式化
for row in ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=2):
    for cell in row:
        if cell.value > 300:
            cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

# 保存 Excel 文件
wb.save('condition.xlsx')

19. 自定义样式

自定义样式可以让你的 Excel 文件更加美观。

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment

# 创建一个新的 DataFrame
data = {
    'A': [1, 2, 3, 4, 5],
    'B': [100, 200, 300, 400, 500],
    'C': [1000, 2000, 3000, 4000, 5000]
}
df = pd.DataFrame(data)

# 将 DataFrame 写入 Excel 文件
wb = Workbook()
ws = wb.active
df.to_excel(ws, index=False)

# 设置字体样式
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=3):
    for cell in row:
        cell.font = Font(bold=True, color="FFFFFF")

# 设置边框样式
thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))

for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3):
    for cell in row:
        cell.border = thin_border

# 设置居中对齐
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3):
    for cell in row:
        cell.alignment = Alignment(horizontal='center', vertical='center')

# 保存 Excel 文件
wb.save('styled.xlsx')

20. 批量处理文件

批量处理多个 Excel 文件可以大大提高效率。

import os
import pandas as pd

# 获取目录中的所有 Excel 文件
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]

# 循环处理每个文件
for file in files:
    # 读取 Excel 文件
    df = pd.read_excel(file)
    
    # 进行数据处理
    df['D'] = df['A'] * df['B']
    
    # 保存处理后的文件
    df.to_excel(f'processed_{file}', index=False)

实战案例：员工绩效分析

假设你有一个包含员工绩效数据的 Excel 文件，需要分析每位员工的绩效。

import pandas as pd

# 读取 Excel 文件
performance_data = pd.read_excel('employee_performance.xlsx')

# 计算每位员工的总销售额
performance_data['Total Sales'] = performance_data['Quantity'] * performance_data['Price']

# 分析每位员工的平均销售额
average_sales = performance_data.groupby('Employee')['Total Sales'].mean()

# 显示平均销售额
print(average_sales)

输出：

Employee
Alice    5000.0
Bob      6000.0
Charlie  7000.0
Dave     8000.0
Eve      9000.0
dtype: float64

总结

本文介绍了使用 Python 处理 Excel 文件的各种方法，包括读取、写入、筛选、排序、分组、添加新列、更新单元格、删除列、合并多个文件、数据透视表、数据合并、数据清洗、数据类型转换、数据可视化、多页文件操作、条件格式化、自定义样式以及批量处理等。通过这些示例，你可以掌握如何高效地管理和分析 Excel 数据。