用python对excel进行数据处理与分析操作_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)
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209