必學(xué)的 20 個(gè) Excel 表格操作 Python 腳本
作者：小白PythonAI編程
本文介紹了使用 Python 處理 Excel 文件的各種方法，通過這些示例，你可以掌握如何高效地管理和分析 Excel 數(shù)據(jù)。
本文將介紹使用 Python 處理 Excel 文件的多種方法，涵蓋從基本的讀寫操作到高級(jí)的數(shù)據(jù)分析與可視化。通過這些示例，你可以學(xué)習(xí)如何高效地管理和分析 Excel 數(shù)據(jù)。
1. 安裝必要的庫
在開始之前，確保安裝了 pandas 和 openpyxl 這兩個(gè)庫。這兩個(gè)庫是處理 Excel 文件的基礎(chǔ)。
pip install pandas openpyxl2. 讀取 Excel 文件
首先，讓我們看看如何讀取一個(gè) Excel 文件。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx', engine='openpyxl')
# 顯示前五行數(shù)據(jù)
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 50003. 寫入 Excel 文件
接下來，我們將創(chuàng)建一個(gè)新的 DataFrame 并將其寫入新的 Excel 文件。
import pandas as pd
# 創(chuàng)建一個(gè) 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. 選擇特定列
有時(shí)候我們只需要 Excel 文件中的某些列。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx', usecols=['A', 'C'])
# 顯示前五行數(shù)據(jù)
print(df.head())輸出：
A C
0 1 1000
1 2 2000
2 3 3000
3 4 4000
4 5 50005. 過濾數(shù)據(jù)
過濾數(shù)據(jù)可以幫助我們找到特定條件下的記錄。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 過濾出 A 列大于 3 的行
filtered_df = df[df['A'] > 3]
# 顯示過濾后的數(shù)據(jù)
print(filtered_df)輸出：
A B C
3 4 400 4000
4 5 500 50006. 數(shù)據(jù)排序
排序數(shù)據(jù)可以讓我們更容易地查看數(shù)據(jù)的趨勢(shì)。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 按照 A 列降序排列
sorted_df = df.sort_values(by='A', ascending=False)
# 顯示排序后的數(shù)據(jù)
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 10007. 數(shù)據(jù)分組
數(shù)據(jù)分組可以幫助我們分析不同類別的數(shù)據(jù)。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 按照 B 列分組并計(jì)算平均值
grouped_df = df.groupby('B').mean()
# 顯示分組后的數(shù)據(jù)
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.08. 添加新列
我們可以根據(jù)現(xiàn)有列的數(shù)據(jù)輕松添加新列。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 添加新列 D
df['D'] = df['A'] * df['B']
# 顯示更新后的數(shù)據(jù)
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 25009. 更新單元格
有時(shí)候我們需要更新特定單元格的值。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 更新 A 列第 2 行的值
df.at[1, 'A'] = 10
# 顯示更新后的數(shù)據(jù)
print(df)輸出：
A B C
0 1 100 1000
1 10 200 2000
2 3 300 3000
3 4 400 4000
4 5 500 500010. 刪除列
刪除不需要的列可以簡(jiǎn)化數(shù)據(jù)集。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 刪除 C 列
del df['C']
# 顯示更新后的數(shù)據(jù)
print(df)輸出：
A B
0 1 100
1 2 200
2 3 300
3 4 400
4 5 50011. 合并多個(gè) Excel 文件
合并多個(gè) Excel 文件可以方便地將數(shù)據(jù)集中到一起。
import pandas as pd
# 讀取多個(gè) Excel 文件
df1 = pd.read_excel('example1.xlsx')
df2 = pd.read_excel('example2.xlsx')
# 合并兩個(gè) DataFrame
merged_df = pd.concat([df1, df2], ignore_index=True)
# 顯示合并后的數(shù)據(jù)
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 500012. 數(shù)據(jù)透視表
數(shù)據(jù)透視表是一種強(qiáng)大的工具，可以快速匯總和分析數(shù)據(jù)。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 創(chuàng)建數(shù)據(jù)透視表
pivot_table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc=sum)
# 顯示數(shù)據(jù)透視表
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 500013. 數(shù)據(jù)合并
合并多個(gè)數(shù)據(jù)集可以讓你更好地分析數(shù)據(jù)之間的關(guān)系。
import pandas as pd
# 讀取兩個(gè) Excel 文件
df1 = pd.read_excel('example1.xlsx')
df2 = pd.read_excel('example2.xlsx')
# 使用內(nèi)連接合并兩個(gè)數(shù)據(jù)集
merged_df = pd.merge(df1, df2, on='A', how='inner')
# 顯示合并后的數(shù)據(jù)
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 50014. 數(shù)據(jù)清洗
數(shù)據(jù)清洗是數(shù)據(jù)分析的重要步驟，可以去除無效或錯(cuò)誤的數(shù)據(jù)。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 去除空值
df.dropna(inplace=True)
# 去除重復(fù)行
df.drop_duplicates(inplace=True)
# 顯示清洗后的數(shù)據(jù)
print(df)輸出：
A B C
0 1 100 1000
1 2 200 2000
2 3 300 3000
3 4 400 4000
4 5 500 500015. 數(shù)據(jù)類型轉(zhuǎn)換
正確設(shè)置數(shù)據(jù)類型有助于節(jié)省內(nèi)存并提高性能。
import pandas as pd
# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
# 將 A 列轉(zhuǎn)換為整型
df['A'] = df['A'].astype(int)
# 將 B 列轉(zhuǎn)換為浮點(diǎn)型
df['B'] = df['B'].astype(float)
# 顯示轉(zhuǎn)換后的數(shù)據(jù)
print(df.dtypes)輸出：
A int64
B float64
C float64
dtype: object16. 數(shù)據(jù)可視化
使用 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 文件時(shí)，可以使用 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']
118. 條件格式化
條件格式化可以幫助你突出顯示特定數(shù)據(jù)。
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill
# 創(chuàng)建一個(gè)新的 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)
# 設(shè)置條件格式化
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
# 創(chuàng)建一個(gè)新的 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)
# 設(shè)置字體樣式
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")
# 設(shè)置邊框樣式
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
# 設(shè)置居中對(duì)齊
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. 批量處理文件
批量處理多個(gè) Excel 文件可以大大提高效率。
import os
import pandas as pd
# 獲取目錄中的所有 Excel 文件
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]
# 循環(huán)處理每個(gè)文件
for file in files:
# 讀取 Excel 文件
df = pd.read_excel(file)
# 進(jìn)行數(shù)據(jù)處理
df['D'] = df['A'] * df['B']
# 保存處理后的文件
df.to_excel(f'processed_{file}', index=False)實(shí)戰(zhàn)案例：?jiǎn)T工績(jī)效分析
假設(shè)你有一個(gè)包含員工績(jī)效數(shù)據(jù)的 Excel 文件，需要分析每位員工的績(jī)效。
import pandas as pd
# 讀取 Excel 文件
performance_data = pd.read_excel('employee_performance.xlsx')
# 計(jì)算每位員工的總銷售額
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總結(jié)
本文介紹了使用 Python 處理 Excel 文件的各種方法，包括讀取、寫入、篩選、排序、分組、添加新列、更新單元格、刪除列、合并多個(gè)文件、數(shù)據(jù)透視表、數(shù)據(jù)合并、數(shù)據(jù)清洗、數(shù)據(jù)類型轉(zhuǎn)換、數(shù)據(jù)可視化、多頁文件操作、條件格式化、自定義樣式以及批量處理等。通過這些示例，你可以掌握如何高效地管理和分析 Excel 數(shù)據(jù)。
責(zé)任編輯：趙寧寧
來源：
小白PythonAI編程
