用python对excel进行数据处理与分析操作

本文代码及数据集来自《超简单：用Python让Excel飞起来（实战150例）》

# 排序一个工作表中的数据（方法一）
import pandas as pd
data = pd.read_excel('销售表.xlsx', sheet_name='总表')
data = data.sort_values(by='利润', ascending=False) # 按照“利润”列进行降序排序
data.to_excel('销售表1.xlsx', sheet_name='总表', index=False)

# 排序一个工作表中的数据（方法二）
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('销售表.xlsx')
worksheet = workbook.sheets['总表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value # 读取工作表中的数据，并将其转换为DataFrame格式
result = data.sort_values(by='利润', ascending=False) # 对DataFrame中的“利润”列进行降序排序
worksheet.range('A1').value = result # 将排序后的数据写入工作表，这里是从单元格A1开始写入
workbook.save('销售表1.xlsx')
workbook.close()
app.quit()

# 排序一个工作簿中所有工作表的数据
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('各月销售数量表.xlsx')
worksheet = workbook.sheets
for i in worksheet: # 遍历工作表
    data = i.range('A1').expand('table').options(pd.DataFrame).value
    result = data.sort_values(by='销售数量', ascending=False)
    i.range('A1').value = result
workbook.save('各月销售数量表1.xlsx')
workbook.close()
app.quit()

# 排序多个工作簿中同名工作表的数据
from pathlib import Path
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
folder_path = Path('各地区销售数量')
file_list = folder_path.glob('*.xls*')
for i in file_list:
    workbook = app.books.open(i)
    worksheet = workbook.sheets['销售数量'] # 指定要排序的工作表
    data = worksheet.range('A1').expand('table').options(pd.DataFrame).value
    result = data.sort_values(by='销售数量', ascending=False)
    worksheet.range('A1').value = result
    workbook.save()
    workbook.close()
app.quit()

# 根据单个条件筛选一个工作表中的数据
import pandas as pd
data = pd.read_excel('销售表.xlsx', sheet_name='总表')
pro_data = data[data['产品名称'] == '离合器'] # 在“产品名称”列中筛选“离合器”数据
num_data = data[data['销售数量'] >= 100] # 在“销售数量”列中筛选大于等于100的数据
pro_data.to_excel('离合器.xlsx', sheet_name='离合器', index=False)
num_data.to_excel('销售数量大于等于100的记录.xlsx', sheet_name='销售数量大于等于100的记录', index=False)

# 根据多个条件筛选一个工作表中的数据
import pandas as pd
data = pd.read_excel('销售表.xlsx', sheet_name='总表')
condition1 = (data['产品名称'] == '转速表') & (data['销售数量'] >= 50)
condition2 = (data['产品名称'] == '转速表') | (data['销售数量'] >= 50)
data1 = data[condition1]
data2 = data[condition2]
data1.to_excel('销售表1.xlsx', sheet_name='与条件筛选', index=False)
data2.to_excel('销售表2.xlsx', sheet_name='或条件筛选', index=False)

# 筛选一个工作簿中所有工作表的数据
import pandas as pd
all_data = pd.read_excel('办公用品采购表.xlsx', sheet_name=None)
with pd.ExcelWriter('筛选表.xlsx') as workbook:
    for i in all_data:
        data = all_data[i]
        filter_data = data[data['采购物品'] == '办公桌'] # 筛选“采购物品”为“办公桌”的数据
        filter_data.to_excel(workbook, sheet_name=i, index=False) # 存放在不同工作表中
        
# 筛选一个工作簿中所有工作表的数据并汇总
import pandas as pd
all_data = pd.read_excel('办公用品采购表.xlsx', sheet_name=None)
datas = pd.DataFrame()
for i in all_data:
    data = all_data[i]
    filter_data = data[data['采购物品'] == '办公桌']
    datas = pd.concat([datas, filter_data], axis=0) # 将筛选出的数据合并到第3行代码创建的DataFrame中
datas.to_excel('办公桌.xlsx', sheet_name='办公桌', index=False)

# 分类汇总一个工作表
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('销售表.xlsx')
worksheet = workbook.sheets['总表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame, dtype=float).value
result = data.groupby('产品名称').sum() # 汇总依据为“产品名称”列
worksheet1 = workbook.sheets.add(name='分类汇总') # 新增一个名为“分类汇总”的工作表
worksheet1.range('A1').value = result[['销售数量', '销售金额']] # 将指定列的汇总结果写入工作表“分类汇总”
workbook.save('分类汇总表.xlsx')
workbook.close()
app.quit()

# 对一个工作表求和
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('办公用品采购表.xlsx')
worksheet = workbook.sheets['1月']
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value
result = data['采购金额'].sum()
worksheet.range('B15').value = '合计' # 将文本“合计”写入单元格
worksheet.range('C15').value = result # 将求和结果写入单元格
workbook.save('求和表.xlsx')
workbook.close()
app.quit()

# 对一个工作簿的所有工作表分别求和
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('办公用品采购表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    data = i.range('A1').expand('table').options(pd.DataFrame).value
    result = data['采购金额'].sum()
    column = i.range('A1').expand('table').value[0].index('采购金额') + 1 # 获取采购金额列的列号
    row = i.range('A1').expand('table').shape[0] # 获取最后一行的行号
    i.range(row + 1, column - 1).value = '合计' # 将文本“合计”写入“采购金额”列前一列最后一个单元格下方的单元格
    i.range(row + 1, column).value = result # 将求和结果写入“采购金额”列最后一个单元格下方的单元格
workbook.save('求和表.xlsx')
workbook.close()
app.quit()

# 在一个工作表中制作数据透视表
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('销售表.xlsx')
worksheet = workbook.sheets['总表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame, dtype=float).value
pivot = pd.pivot_table(data, values=['销售数量', '销售金额'], index=['产品名称'], aggfunc={'销售数量': 'sum', '销售金额': 'sum'}, fill_value=0, margins=True, margins_name='合计')
worksheet1 = workbook.sheets.add(name='数据透视表')
worksheet1.range('A1').value = pivot
workbook.save('数据透视表.xlsx')
workbook.close()
app.quit()

# 使用相关系数判断数据的相关性
import pandas as pd
data = pd.read_excel('销售额统计表.xlsx', sheet_name=0, index_col='序号')
result = data.corr()
print(result)

# 使用描述统计和直方图制定目标
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
data = pd.read_excel('员工销售业绩表.xlsx', sheet_name=0)
data_describe = data['销售额（万元）'].astype(float).describe() # 计算数据的个数、平均值、最大值和最小值等描述统计数据
data_cut = pd.cut(data['销售额（万元）'], 6) # 将“销售额（万元）”列的数据分为6个均等的区间
data1 = pd.DataFrame()
data1['计数'] = data['销售额（万元）'].groupby(data_cut).count() # 统计各个区间的人数
data2 = data1.reset_index() # 将行索引重置为数字序号
data2['销售额（万元）'] = data2['销售额（万元）'].apply(lambda x:str(x)) # 将“销售额”列的数据转换为字符串类型
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解决中文乱码问题
plt.rcParams['axes.unicode_minus'] = False # 解决负号问题
n, bins, patches = plt.hist(data['销售额（万元）'], bins=6, edgecolor='black', linewidth=1)
plt.xticks(bins)
plt.title('员工销售业绩频率分析')
plt.xlabel('销售额（万元）')
plt.ylabel('频数')
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('员工销售业绩表.xlsx')
worksheet = workbook.sheets[0]
worksheet.range('E1').value = data_describe # 将计算出的描述统计数据写入指定工作表
worksheet.range('H1').value = data2 # 将销售额的区间以及区间的人数写入指定工作表
worksheet.pictures.add(figure, name='图片1', update=True, left=400, top=200) # 将直方图的图片插入指定工作表
worksheet.autofit() # 自动调整行高列宽
workbook.save('描述统计.xlsx')
workbook.close()
app.quit()

# 拟合回归方程并判断拟合程度
import pandas as pd
from sklearn import linear_model
df = pd.read_excel('各月销售额与广告费支出表.xlsx', sheet_name=0)
x = df[['视频门户广告费（万元）', '电视台广告费（万元）']] # 选取作为自变量的列数据
y = df['销售额（万元）']
model = linear_model.LinearRegression()
model.fit(x, y)
R2 = model.score(x, y)
print(R2)

# 使用回归方程预测未来值
import pandas as pd
from sklearn import linear_model
df = pd.read_excel('各月销售额与广告费支出表.xlsx', sheet_name=0)
x = df[['视频门户广告费（万元）', '电视台广告费（万元）']]
y = df['销售额（万元）']
model = linear_model.LinearRegression()
model.fit(x, y)
coef = model.coef_ # 获取方程中各变量的系数
model_intercept = model.intercept_ # 获取方程截距
equation = f'y={coef[0]}*x1+{coef[1]}*x2{model_intercept:+}'
print(equation) # 输出线性回归方程
x1 = 40
x2 = 30
y = coef[0] * x1 + coef[1] * x2 + model_intercept
print(y)