Python让Excel飞起来—批量进行数据分析

案例01 批量升序排序一个工作簿中的所有工作表

• 代码文件：批量升序排序一个工作簿中的所有工作表.py - 数据文件：产品销售统计表.xlsx  每个表批量对销售利润进行升序排列：

import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\MLoong\Desktop\22\产品销售统计表.xlsx')
for i in workbook.sheets:
    values=i.range('A1').expand().options(pd.DataFrame).value  #读取当前工作表的数据并装换成DataFrame类型
    result=values.sort_values(by='销售利润')   #对销售利润进行排序
    i.range('A1').value=result
workbook.save()
workbook.close()
app.quit()

知识延伸

举一反三 批量排序多个工作簿中的数据

• 代码文件：批量排序多个工作簿中的数据.py - 数据文件：产品销售统计表（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\MLoong\Desktop\22\产品销售统计表'
file_list=os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':
        file_paths=os.path.join(file_path,i)
        workbook=app.books.open(file_paths)
        for j in workbook.sheets:
            values= j.range('A1').expand().options(pd.DataFrame).value
            result=values.sort_values(by='销售利润')
            j.range('A1').value=result
        workbook.save()
        workbook.close()
app.quit()

案例02 筛选一个工作簿中的所有工作表数据

• 代码文件：筛选一个工作簿中的所有工作表数据.py - 数据文件：采购表.xlsx 下图所示是按月份存放在不同工作表中的物品采购明细数据，如果要更改为按物品名称存放在不同工作表中，你会怎么做呢？

 思路：先合并所有表，再按采购物品名称进行分表：

import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\MLoong\Desktop\22\采购表.xlsx')

#合并原工作簿中各工作表的数据
table=pd.DataFrame()   #创建一个空的DataFrame
for i,j in enumerate(workbook.sheets) :
    values=j.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
    data=values.reindex(columns=['采购物品','采购日期','采购数量','采购金额'])  #调整列的顺序
    table=table.append(data,ignore_index=True)

#新建表，并写入数据    
table=table.groupby('采购物品')
new_workbook=app.books.add()
for idx,group in table:  #遍历筛选好的数据,其中idx对应物品名称，group对应物品的明细数据
    new_worksheet=new_workbook.sheets.add(idx)
    new_worksheet['A1'].options(index=False).value=group
    
    #对分表进行求和，放在右下角最后一个位置
    last_cell= new_worksheet['A1'].expand().last_cell  #获取当前工作表数据区域右下角单元格
    last_row=last_cell.row  #获取当前工作表数据区域最后一行
    last_column=last_cell.column  #获取当前工作表数据区域最后一列
    last_column_letter=chr(64+last_column)  #根据最后一列，装换成字母列标
    sum_cell_name='{}{}'.format(last_column_letter,last_row+1)
    sum_last_row_name='{}{}'.format(last_column_letter,last_row) 
    formula='=sum({}2:{})'.format(last_column_letter,sum_last_row_name)
    new_worksheet[sum_cell_name]. formula= formula
    new_worksheet.autofit()

new_workbook.save(r'C:\Users\MLoong\Desktop\22\采购分类表.xlsx')
new_workbook.close()
workbook.close()
app.quit()

举一反三 在一个工作簿中筛选单一类别数据

• 代码文件：在一个工作簿中筛选单一类别数据.py - 数据文件：采购表.xlsx

import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
table=pd.DataFrame() #创建一个新的DataFrame
for i,j in enumerate(workbook.sheets):
    values=j.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
    data=values.reindex(columns=['采购物品','采购日期','采购数量','采购金额'])
    table=table.append(data,ignore_index=True)  #ignore_index=True是序号进行累加的意思
product=table[table['采购物品']=='保险箱']  #筛选"采购物品"是"保险箱"的数据

new_workbook=xw.books.add()
new_worksheet=new_workbook.sheets.add('保险箱')
new_worksheet['A1'].options(index=False).value=product
new_worksheet.autofit()
new_workbook.save(r'C:\Users\Administrator\Desktop\22\保险箱.xlsx')
new_workbook.close()
workbook.close()
app.quit()

案例03 对多个工作簿中的工作表分别进行分类汇总

• 代码文件：对多个工作簿中的工作表分别进行分类汇总.py - 数据文件：销售表（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表'
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
            values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
            result=values.groupby('销售区域').sum()
            j.range('J1').value=result['销售利润']
        workbook.save()
        workbook.close()
app.quit()  

• 第13行代码中的astype()是pandas模块中DataFrame对象的函数，用于转换指定列的数据类型。该函数的语法格式和常用参数含义如下。

• 第14行代码中groupby()函数后接的sum()函数用于进行求和汇总，还可以使用其他函数完成其他类型的汇总运算。常用的有：用mean()函数求平均值，用count()函数统计个数，用max()函数求最大值，用min()函数求最小值。

举一反三 批量分类汇总多个工作簿中的指定工作表

• 代码文件：批量分类汇总多个工作簿中的指定工作表.py - 数据文件：销售表1（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表1'
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)
        worksheet=workbook.sheets['销售记录表']
        values=worksheet.range('A1').expand().options(pd.DataFrame).value
        values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
        result=values.groupby('销售区域').sum()
        worksheet.range('J1').value=result['销售利润']
        workbook.save()
        workbook.close()
app.quit()   

举一反三 将多个工作簿数据分类汇总到一个工作簿

• 代码文件：将多个工作簿数据分类汇总到一个工作簿.py - 数据文件：销售表（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表'
file_list=os.listdir(file_path)
collection=[]
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':
        workbook=app.books.open(file_path+'//'+i)
        worksheet=workbook.sheets['销售记录表']
        values=worksheet.range('A1').expand().options(pd.DataFrame).value
        filtered=values[['销售区域','销售利润']]
        collection.append(filtered)        
        workbook.close()
new_values=pd.concat(collection,ignore_index=False).set_index('销售区域') 
values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
result=new_values.groupby('销售区域').sum()

new_workbook=app.books.add()
new_worksheet=new_workbook.sheets.add('汇总表')
new_worksheet.range('A1').value=result
new_worksheet.autofit()
new_workbook.save(r'C:\Users\Administrator\Desktop\22\03\销售汇总表.xlsx')
new_workbook.close()
app.quit()  

案例04 对一个工作簿中的所有工作表分别求和

• 代码文件：对一个工作簿中的所有工作表分别求和.py - 数据文件：采购表.xlsx

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
for i in workbook.sheets:
    values=i.range('A1').expand()
    data=values.options(pd.DataFrame).value
    sums=data['采购金额'].sum()
    
    column=values.value[0].index('采购金额')+1
    row=values.shape[0]
    i.range(row+1,column).value=sums
workbook.save()
workbook.close()
app.quit()

• 第10行代码中的index()是Python中列表对象的函数，常用于在列表中查找某个元素的索引位置。该函数的语法格式和常用参数含义如下。- 第11行代码中的shape是pandas模块中DataFrame对象的一个属性，它返回的是一个元组，其中有两个元素，分别代表DataFrame的行数和列数。

举一反三 对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格

• 代码文件：对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格.py - 数据文件：采购表.xlsx

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
for i in workbook.sheets:
    values=i.range('A1').expand().options(pd.DataFrame).value
    sums=values['采购金额'].sum()
    i.range('F1').value=sums   #将求和后的内容写到F1单元格中
workbook.save()
workbook.close()
app.quit()

案例05 批量统计工作簿的最大值和最小值

• 代码文件：批量统计工作簿的最大值和最小值.py - 数据文件：产品销售统计表（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\产品销售统计表'
file_list=os.listdir(file_path)
for j in file_list:
    if os.path.splitext(j)[1]=='.xlsx':
        file_paths=os.path.join(file_path,j)
        workbook=app.books.open(file_paths)
        for i in workbook.sheets:
            values=i.range('A1').expand().options(pd.DataFrame).value
            max0=values['销售利润'].max()
            min0=values['销售利润'].min()
            i.range('I1').value='最大销售利润'
            i.range('J1').value=max0
            i.range('I2').value='最小销售利润'
            i.range('J2').value=min0
            i.autofit()
        workbook.save()
        workbook.close()
app.quit()    

举一反三 批量统计一个工作簿中所有工作表的最大值和最小值

• 代码文件：批量统计一个工作簿中所有工作表的最大值和最小值.py- 数据文件：产品销售统计表.xlsx

import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\产品销售统计表.xlsx')
for i in workbook.sheets:
    values=i.range('A1').expand().options(pd.DataFrame).value
    max0=values['销售利润'].max()
    min0=values['销售利润'].min()
    i.range('I3').value='最大销售利润'
    i.range('J3').value=max0
    i.range('I4').value='最小销售利润'
    i.range('J4').value=min0
    i.autofit()
workbook.save()
workbook.close()
app.quit()   

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

• 代码文件：批量制作数据透视表.py - 数据文件：商品销售表（文件夹）

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\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

import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\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() 

案例07 使用相关系数判断数据的相关性

• 代码文件：使用相关系数判断数据的相关性.py - 数据文件：相关性分析.xlsx

import pandas as pd
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\相关性分析.xlsx',index_col='代理商编号')
result=df.corr()
print(result)

运行结果

• corr()函数默认计算的是两个变量之间的皮尔逊相关系数。该系数用于描述两个变量间线性相关性的强弱，取值范围为[-1,1]。系数为正值表示存在正相关性，为负值表示存在负相关性，为0表示不存在线性相关性。系数的绝对值越大，说明相关性越强。- 上表中第1行第2列的数值0.982321，表示的就是年销售额与年广告费投入额的皮尔逊相关系数，其余单元格中数值的含义依此类推。需要说明的是，上表中从左上角至右下角的对角线上的数值都为1，这个1其实没有什么实际意义，因为它表示的是变量自身与自身的皮尔逊相关系数,自然是1。- 从上表可以看到，年销售额与年广告费投入额、成本费用之间的皮尔逊相关系数均接近1，而与管理费用之间的皮尔逊相关系数接近0，说明年销售额与年广告费投入额、成本费用之间均存在较强的线性正相关性，而与管理费用之间基本不存在线性相关性。前面通过直接观察法得出的结论是比较准确的。- 第2行代码中的read_excel()是pandas模块中的函数，用于读取工作簿数据。3.5.2节曾简单介绍过这个函数，这里再详细介绍一下它的语法格式和常用参数的含义。- read_excel(io,sheet_name=0,header=0,names=None,index_col=None,usecols=None,squeeze=False,dtype=None)
• 第3行代码中的corr()是pandas模块中DataFrame对象自带的一个函数，用于计算列与列之间的相关系数。该函数的语法格式和常用参数含义如下。

举一反三 求单个变量和其他变量间的相关性

• 代码文件：求单个变量和其他变量间的相关性.py - 数据文件：相关性分析.xlsx

import pandas as pd
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\相关性分析.xlsx',index_col='代理商编号')
result=df.corr()['年销售额（万元）']
print(result)

得出以下结果：

年销售额（万元）       1.000000
年广告费投入额（万元）    0.982321
成本费用（万元）       0.953981
管理费用（万元）       0.012364
Name: 年销售额（万元）, dtype: float64

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

• 代码文件：使用方差分析对比数据的差异.py - 数据文件：方差分析.xlsx  在Python中做方差分析，要用到与方差分析相关的statsmodels.formula.api模块和statsmodels.stats.anova模块，以及ols()函数和anova_lm()函数。下面一起来看看具体的代码。

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'C:\Users\Administrator\Desktop\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对象的函数，用于总结数据集分布的集中趋势，生成描述性统计数据。该函数的语法格式和常用参数含义如下。- 第15行代码中的ols()是statsmodels.formula.api模块中的函数，用于对数据进行最小二乘线性拟合计算。该函数的语法格式和常用参数含义如下。
• 第16行代码中的anova_lm()是statsmodels.stats.anova模块中的函数，用于对数据进行方差分析并输出结果。该函数的语法格式和常用参数含义如下。

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

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

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'C:\Users\Administrator\Desktop\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()

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

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

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()

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

知识延伸

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

df_cut=pd.cut(df['月销售额'],bins=7,precision=2)  

• 第12行代码中的reset_index()是pandas模块中DataFrame对象的函数，用于重置DataFrame对象的索引。在3.5.1节中曾简单介绍过reset_index()函数的用法，这里再详细介绍一下该函数的语法格式和常用参数含义。

df_all_new=df_all.reset_index()  #将索引重置

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

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

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

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

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()

案例10 使用回归分析预测未来值

• 代码文件：使用回归分析预测未来值.py - 数据文件：回归分析.xlsx

import pandas as pd
from sklearn import linear_model
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\回归分析.xlsx')
df=df[1:]  #删除第一行
df.columns=['月份','电视台广告费','视频门户广告费','汽车当月销售额'] #重命名

#获取'电视台广告费','视频门户广告费'最为自变量
x=df[['电视台广告费','视频门户广告费']]
y=df['汽车当月销售额']

model=linear_model.LinearRegression()
model.fit(x,y)
R2=model.score(x,y)
R2

举一反三 使用回归方程计算预测值

• 代码文件：使用回归方程计算预测值.py - 数据文件：回归分析.xlsx

import pandas as pd
from sklearn import linear_model
df=pd.read_excel(r'C:\Users\Administrator\Desktop\22\回归分析.xlsx')
df=df[1:]  #删除第一行
df.columns=['月份','电视台广告费','视频门户广告费','汽车当月销售额'] #重命名

#获取'电视台广告费','视频门户广告费'最为自变量
x=df[['电视台广告费','视频门户广告费']]
y=df['汽车当月销售额']

model=linear_model.LinearRegression()
model.fit(x,y)

coef=model.coef_  #获取自变量系数
model_intercept=model.intercept_  #获取截距
result='y={}+({})x1+({})x2'.format(coef[0],coef[1],model_intercept)
print('线性回归的方程为：','\n',result)

a=20 #设置电视广告费用
b=30 #设置视频广告费

y=model_intercept+a*coef[0]+b*coef[1]
print('电视广告投放20万，视频门户投放30万，预测汽车的销售额为：','\n',y)

预测结果为：

线性回归的方程为： 
 y=9.133786669280706+(51.06148377665357)x1+(-316.28885036504175)x2
电视广告投放20万，视频门户投放30万，预测汽车的销售额为： 
 1398.2313963201796

参考文献 《超简单：用Python让Excel飞起来》

数据下载06：

链接：https://pan.baidu.com/s/1KdI7u72sZIcG_C5Y9AtCJw  提取码：8888

• Python从入门到大师
• Python爬虫专栏
• 数据分析
• 机器学习
• AI基础

日常收集整理了一批不错的 Python 学习资料，有需要的小伙可以自行免费领取。

获取方式如下：公众号回复资料。领取Python等系列笔记，项目，书籍，直接套上模板就可以用了。资料包含算法、python、算法小抄、力扣刷题手册和 C++ 等学习资料！