运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

释放双眼,带上耳机,听听看~!

案例01 批量制作数据透视表

  • 代码文件:批量制作数据透视表.py – 数据文件:商品销售表(文件夹)

运维工具 – PYTHON 日常数据分析 – 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
import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'd:\\22\商品销售表'
file_list=os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':
        workbook=app.books.open(file_path+'\'+i)
        for j in workbook.sheets:
            values=j.range('A1').expand().options(pd.DataFrame).value
            pivottable=pd.pivot_table(values,values='销售金额' #汇总字段为销售金额
                                      ,index='销售地区'  #指定行字段为销售地区
                                      ,columns='销售分部' #列字段为销售分部
                                      ,aggfunc='sum' #汇总计算方式为求和
                                      ,fill_value=0  #缺失值填充0
                                      ,margins=True #显示汇总行列
                                      ,margins_name='总计'  #数据行的名称
                                     )
            j.range('J1').value=pivottable
            j.autofit()
        workbook.save()
        workbook.close()
app.quit()

举一反三 为一个工作簿的所有工作表制作数据透视表

  • 代码文件:为一个工作簿的所有工作表制作数据透视表.py – 数据文件:商品销售表.xlsx

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'd:\22\商品销售表.xlsx')
for i in workbook.sheets:
    values=i.range('A1').expand().options(pd.DataFrame).value
    pivottable=pd.pivot_table(values,values='销售金额' #汇总字段为销售金额
                              ,index='销售地区'  #指定行字段为销售地区
                              ,columns='销售分部' #列字段为销售分部
                              ,aggfunc='sum' #汇总计算方式为求和
                              ,fill_value=0  #缺失值填充0
                              ,margins=True #显示汇总行列
                              ,margins_name='总计'  #数据行的名称
                             )
    i.range('J1').value=pivottable
    i.autofit()
workbook.save()
workbook.close()
app.quit()

案例02 使用方差分析对比数据的差异

  • 代码文件:使用方差分析对比数据的差异.py – 数据文件:方差分析.xlsx

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

在Python中做方差分析,要用到与方差分析相关的statsmodels.formula.api模块和statsmodels.stats.anova模块,以及ols()函数和anova_lm()函数。下面一起来看看具体的代码。


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
import pandas as pd
from statsmodels.formula.api import ols  #导入方差分析的模块
from statsmodels.stats.anova import anova_lm
import xlwings as xw
df=pd.read_excel(r'd:\22\方差分析.xlsx')
df=df[['A型号','B型号','C型号','D型号','E型号']]  #选取ABCDE的型号的列作为分析

df_melt=df.melt() #将列名转换成列数据
df_melt.columns=['Treat','Value']  #重命名列名

df_describe=pd.DataFrame()
df_describe['A型号']=df['A型号'].describe()  #计算A型号的平均值、最大值、最小值
df_describe['B型号']=df['B型号'].describe()  #计算A型号的平均值、最大值、最小值
df_describe['C型号']=df['C型号'].describe()  #计算A型号的平均值、最大值、最小值
df_describe['D型号']=df['D型号'].describe()  #计算A型号的平均值、最大值、最小值
df_describe['E型号']=df['E型号'].describe()  #计算A型号的平均值、最大值、最小值

print(df_describe)
model=ols('Value~C(Treat)',data=df_melt).fit()  #对样本数据进行最小二乘现行拟合计算
anova_table=anova_lm(model,typ=3)  #对样本进行方差分析

print(model)
print(anova_table)
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\方差分析.xlsx')
worksheet=workbook.sheets['单因素方差分析']  #选中工作表‘单因素方差分析’

worksheet.range('H2').value=df_describe.T  #将计算出的平均值、最小值、最大值等数据xieru
worksheet.range('H14').value='方差分析'
worksheet.range('H15').value=anova_table #将方差分析的结果写入工作表

workbook.save()
workbook.close()
app.quit()

知识延伸

  • 第7行代码中的melt()是pandas模块中DataFrame对象的函数,用于将列名转换为列数据,效果如下图所示,以满足后续使用的ols()函数对数据结构的要求。- 第10~14行代码中的describe()是pandas模块中DataFrame对象的函数,用于总结数据集分布的集中趋势,生成描述性统计数据。该函数的语法格式和常用参数含义如下。-

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

第15行代码中的ols()是statsmodels.formula.api模块中的函数,用于对数据进行最小二乘线性拟合计算。该函数的语法格式和常用参数含义如下。

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

  • 第16行代码中的anova_lm()是statsmodels.stats.anova模块中的函数,用于对数据进行方差分析并输出结果。该函数的语法格式和常用参数含义如下。

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

举一反三 绘制箱形图识别异常值

  • 代码文件:绘制箱形图识别异常值.py – 数据文件:方差分析.xlsx

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import pandas as pd
import matplotlib.pyplot as plt
from statsmodels.formula.api import ols  #导入方差分析的模块
from statsmodels.stats.anova import anova_lm
import xlwings as xw
df=pd.read_excel(r'd:\\22\方差分析.xlsx')
df=df[['A型号','B型号','C型号','D型号','E型号']]  #选取ABCDE的型号的列作为分析
figure=plt.figure()
plt.rcParams['font.sans-serif']=['SimHei']
df.boxplot(grid=False)
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\方差分析.xlsx')
worksheet=workbook.sheets['单因素方差分析']  #选中工作表‘单因素方差分析’
worksheet.pictures.add(figure,name='图片1',update=True,left=500,top=10)
workbook.save('箱型图.xlsx')
workbook.close()
app.quit()

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

案例03 使用描述统计和直方图制定目标

  • 代码文件:使用描述统计和直方图制定目标.py – 数据文件:描述统计.xlsx

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
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
#构造月销售额数据列
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\描述统计.xlsx')
df.columns=['序号','员工姓名','月销售额'] #重命名数据列
df=df.drop(columns=['序号','员工姓名'])  #删除序号和员工姓名列

df_describe=df.astype('float').describe()  #对月销售额数据进行描述性统计
df_cut=pd.cut(df['月销售额'],bins=7,precision=2)  #将月销售额分成7个区间
cut_count=df['月销售额'].groupby(df_cut).count()  #统计各区间的个数

df_all=pd.DataFrame()  #创建一个空的DateFrame用于汇总数据
df_all['计数']=cut_count
df_all_new=df_all.reset_index()  #将索引重置
df_all_new['月销售额']=df_all_new['月销售额'].apply(lambda x:str(x)) #将月销售额转换成字符串类型


#绘图
fig=plt.figure()  #创建绘图窗口
plt.rcParams['font.sans-serif']=['SimHei'] #解决中文乱码问题
n,bins,patches=plt.hist(df['月销售额'],bins=7,edgecolor='black',linewidth=0.5)
plt.xticks(bins)  #将直方图x轴的刻度标签设置为各区间的端点值
plt.title('月度销售额频率分析')  #标题
plt.xlabel('月销售额') #x轴标题
plt.ylabel('频数') #y轴标题

#将图放进表里
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\描述统计.xlsx')
worksheet=workbook.sheets['业务员销售额统计表']  #选中工作表‘单因素方差分析’

worksheet.range('E2').value=df_describe  #将描述性统计数据写入表中
worksheet.range('H2').value=df_all_new  #将分类后的表写入表中

worksheet.pictures.add(fig,name='图片1',update=True,left=400,top=200)
worksheet.autofit()
workbook.save(r'C:\Users\Administrator\Desktop\22\描述统计-直方图.xlsx')
workbook.close()
app.quit()

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

运维工具 – PYTHON 日常数据分析 – EXCEL常用高级函数处理技巧

描述统计数据中几个比较重要的值分别为平均值(mean)19.194、标准差(std)5.46、中位数(50%)18.09、最小值8.54、最大值32.56。在工作簿中还可以看到如下图所示的直方图,根据直方图可以看出,月销售额基本上以18为基数向两边递减,即18最普遍。

知识延伸

  • 第8行代码中的cut()是pandas模块中的函数,用于对数据进行离散化处理,也就是将数据从最大值到最小值进行等距划分。该函数的语法格式和常用参数含义如下。

1
df_cut=pd.cut(df['月销售额'],bins=7,precision=2)
  • 第12行代码中的reset_index()是pandas模块中DataFrame对象的函数,用于重置DataFrame对象的索引。在3.5.1节中曾简单介绍过reset_index()函数的用法,这里再详细介绍一下该函数的语法格式和常用参数含义。

1
df_all_new=df_all.reset_index()  #将索引重置
  • 第14行代码中的figure()是matplotlib.pyplot模块中的函数,用于创建一个绘图窗口。在3.7.2节中曾使用过figure()函数,这里再详细介绍一下该函数的语法格式和常用参数含义。- 第16行代码中的hist()是Matplotlib模块中的函数,用于绘制直方图。该函数的语法格式和常用参数含义如下。

1
n,bins,patches=plt.hist(df['月销售额'],bins=7,edgecolor='black',linewidth=0.5)

举一反三 使用自定义区间绘制直方图

  • 代码文件:使用自定义区间绘制直方图.py – 数据文件:描述统计.xlsx

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
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
#构造月销售额数据列
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\描述统计.xlsx')
df.columns=['序号','员工姓名','月销售额'] #重命名数据列
df=df.drop(columns=['序号','员工姓名'])  #删除序号和员工姓名列

df_describe=df.astype('float').describe()  #对月销售额数据进行描述性统计
df_cut=pd.cut(df['月销售额'],bins=range(8,37,4))  #将月销售额分成7个区间
cut_count=df['月销售额'].groupby(df_cut).count()  #统计各区间的个数

df_all=pd.DataFrame()  #创建一个空的DateFrame用于汇总数据
df_all['计数']=cut_count
df_all_new=df_all.reset_index()  #将索引重置
df_all_new['月销售额']=df_all_new['月销售额'].apply(lambda x:str(x)) #将月销售额转换成字符串类型


#绘图
fig=plt.figure()  #创建绘图窗口
plt.rcParams['font.sans-serif']=['SimHei'] #解决中文乱码问题
n,bins,patches=plt.hist(df['月销售额'],bins=range(8,37,4),edgecolor='black',linewidth=0.5)
plt.xticks(bins)  #将直方图x轴的刻度标签设置为各区间的端点值
plt.title('月度销售额频率分析')  #标题
plt.xlabel('月销售额') #x轴标题
plt.ylabel('频数') #y轴标题

#将图放进表里
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\描述统计.xlsx')
worksheet=workbook.sheets['业务员销售额统计表']  #选中工作表‘单因素方差分析’

worksheet.range('E2').value=df_describe  #将描述性统计数据写入表中
worksheet.range('H2').value=df_all_new  #将分类后的表写入表中

worksheet.pictures.add(fig,name='图片1',update=True,left=400,top=200)
worksheet.autofit()
workbook.save(r'C:\Users\Administrator\Desktop\22\描述统计-直方图2.xlsx')
workbook.close()
app.quit()

给TA打赏
共{{data.count}}人
人已打赏
安全运维

安全运维之道:发现、解决问题的有效闭环

2024-4-14 20:59:36

安全运维

稳定性建设 – 架构优化的关键策略

2025-2-11 17:15:56

个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索