制作图标，设置简单元素

# 1、批量制作数据透视表
'''
file_path = '商品销售表'
file_list = os.listdir(file_path)
for j in file_list:
    if os.path.splitext(j)[1] == '.xslx':
        workbook = app.books.open('销售表.xlsx')
        worksheet = workbook.sheets['业务表']
        for i in worksheet:
            values = i.range('A1').expand('table').options(pd.DataFrame).value
            # 用读取的数据制作透视表
            pivot_table = pd.pivot_table(values, values = '销售金额', index = '销售地区', columns= '销售分部', aggfunc = 'sum', fill_value = 0, margins = True, margins_name = '总计')
            i.range('J1').value = pivot_table
        workbook.save()
        workbook.close()
app.quit()
'''
# pivot_table(data,value=None,index=None,columns=None,aggfunc='mean',fill_value=None,margins=False,dropna=True,margins_name='All')
# data:必选，指定要制作数据透视表的数据区域
# values：可选，指定汇总计算的字段
# index：必选，用于指定行字段
# columns：必选参数，指定列字段
# aggfunc：指定汇总计算的方式，如'sum','mean()计算平均值'
# fill_value：指定填充缺失值的内容，默认不填充
# margins：设置是否显示行列的总计数据，FALSE时不显示，TRUE时显示
# dropna：设置汇总后的整行数据都为空值时是否丢弃该行，TRUE时丢弃，FALSE时不丢弃
# margins_name：参数margins为TRUE时，设置总计数据行的名称

# 2、为一个工作簿的所有工作表制作数据透视表
'''
workbook = app.books.open('商品销售表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    values = i.range('A1').expand('table').options(pd.DataFrame).value
    pivottable = pd.pivot_table(values,values = '销售金额', index= '销售地区', columns= '销售分部', aggfunc= 'sum', fill_value=0, margins=True,margins_name='总计')
    i.range('J1').value = pivottable
workbook.save()
workbook.close()
app.quit()
'''

# 3、使用相关系数判断数据的相关性
'''
import pandas as pd
df = pd.read.excel('相关性分析.xlsx',index_col = '代理商编号')  # 从指定工作簿中读取要进行相关性分析的数据
result = df.corr() # 计算两个任意变量之间的相关系数
print(result)
'''
# read_excel(io,sheet_name=0,header=0,names=None,index_col=None,usecols=None,squeeze=False,dtype=None)
# io:要读取的工作簿的文件路径
# sheet_name：默认为0，如果为字符串，则代表工作表名称，如果整数，则代表工作表的序号（从0开始），如果为字符串列表或整数列表，表示读取多个工作表，None则为读取所有工作表
# header：指定作为列名的行，默认0，即读取第一行作为列名，列名以下为数据。如果原有内容没有列名，则header=None
# names：指定要使用的列名列表，默认None
# index_col：指定索引的列，默认None，表示用自动生成的整数序列作为索引
# usecols：要读取的列，默认None，读取所有列，整数列表表示按列号从0开始读取指定列，字符串列表表示按列名读取指定列
# squeeze：默认FALSE，如果为TRUE，表示读取只有一列时，返回一个series
# dtype：指定数据或列的数据类型，默认None

# 4、单个变量和其他变量间的相关性
'''
import pandas as pd
df = pd.read_excel('相关性分析.xlsx', index_col = '代理商编号')
result = df.corr()['年销售金额']  # 计算年销售额与其他变量间的皮尔逊相关系数
print(result)
'''

# 5、使用方差分析数据之间差异
'''
import pandas as pd
from statsmodels.formula.api import ols
from statsmodels.stats.anova import anova_1m
import xlwings as xw
df = pd.read_excel('方差分析.xlsx')  # 读取指定工作簿中的数据
df = df[['A型号','B型号','C型号','D型号','E型号']]  # 选取“A型号”“ B
型号”“ C型号”“ D型号”“ E型号”列的数据用于分析
df_melt = df.melt()  # 将列名转换为列数据，重构DataFrame
df_melt.columns = ['Treat', 'Value']  # 重命名列
df_describe = pd.DataFrame()  # 创建一个空DataFrame用于汇总数据
df_describe['A型号'] = df['A型号'].describe()  # 计算“A型号”轮胎刹车距离的平均值、最大值和最小值等
df_describe['B型号'] = df['B型号'].describe()  # 计算“B型号”轮胎刹车距离的平均值、最大值和最小值等
df_describe['C型号'] = df['C型号'].describe()  # 计算“C型号”轮胎刹车距离的平均值、最大值和最小值等
df_describe['D型号'] = df['D型号'].describe()  # 计算“D型号”轮胎刹车距离的平均值、最大值和最小值等
df_describe['E型号'] = df['E型号'].describe()  # 计算“E型号”轮胎刹车距离的平均值、最大值和最小值等
model = ols('Value~C(Treat)', data = df_melt).fit()  # 对样本数据进行最小二乘线性拟合计算
anova_table = anova_lm(model, typ = 3)  # 对样本数据进行方差分析
# app = xw.App(visible = False)  # 启动Excel程序
workbook = app.books.open('方差分析.xlsx')  # 打开要写入分析结果的工作簿
worksheet = workbook.sheets['单因素方差分析']  # 选中工作表“单因素方差分析”
worksheet.range('H2').value = df_describe.T  # 将计算出的平均值、最大值和最小值等数据转置行列并写入工作表
worksheet.range('H14').value = '方差分析'  # 在工作表中写入文本“方差分析”
worksheet.range('H15').value = anova_table  # 将方差分析的结果写入工作表
workbook.save()  # 保存工作簿
workbook.close()  # 关闭工作簿
app.quit()  # 退出Excel程序
'''
# melt(id_vars=None,value_vars=None,var_name=None,value_name='value',col_level=None)
# id_vars:不需要转换的列的列名
# value_vars：需要转换列的列名，如果为指明，则出id_vars之外所有
# var_name：value_vars的值转换后的列名
# value_name：数值列的列名
# col_level：可选参数，如果不止一个索引项，则使用该参数

# DataFrame.describe(percentiles=None,include=None,exclude=None)
# percentiles：可选参数，数据类型为列表，用于设定数值型特征的统计量，默认None，表示返回25%，50%，75%数据量时的数字
# include：可选，设定运行结果包含哪些数据类型的列，默认None，表示运行结果将包含所有数据类型为数字的列
# exclude：可选，设定要忽略哪些数据类型的列，默认None，表示不忽略

# ols(formula,data)
# formula：指定模型的公式的字符串
# data：搭建模型的数据

# anova_lm(args,scale,test,typ,robust)
# args：一个或多个拟合线性模型
# scale：方差估计，None则为将从最大的模型估计
# test：提供测试统计数据
# typ：要进行方差分析的类型
# robust：使用异方差校正系数协方差矩阵

# 6、绘制箱型图识别异常值
'''
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
df = pd.read_excel('方差分析.xlsx')
df = df[['A型号', 'B型号', 'C型号', 'D型号', 'E型号']]
figure = plt.figure()   # 创建绘图窗口
plt.rcParams['font.sans-serif'] = ['SimHei']  # 解决中文乱码问题
df.boxplot(grid = False)  # 绘制箱型图并删除网格线
app = xw.App(viseble = False)
workbook = app.books.open('方差分析.xlsx')
worksheet = workbook.sheets['单因素方差分析']
worksheet.pictures.add(figure,name = '图片1', udpate = True, left = 500, top = 10)  # 将绘制的箱型图插入工作表
workbook.save('箱型图.xlsx')
workbook.close()
app.quit()
'''

# 7、使用描述统计和直方图指定目标
'''
import pandas as pd  # 导入pandas模块
import matplotlib.pyplot as plt  # 导入Matplotlib模块
import xlwings as xw  # 导入xlwings模块
df = pd.read_excel('描述统计.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()  # 创建一个空DataFrame用于汇总数据
df_all['计数'] = cut_count  # 将月销售额的区间及区间的人数写入前面创建的DataFrame中
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 = False)  # 启动Excel程序
workbook = app.books.open('描述统计.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('描述统计1.xlsx')  # 另存工作簿
workbook.close()
app.quit()
'''

# cut(x,bins,right=True,labels=None,retbins=False,precision=3,include_lowest=False)
# x：要进行离散化的一堆数组
# bins：为整数，则将x划分为多少等间距的区间；为序列，则将x划分在指定的序列中
# right：区间是否包含右端点
# labels：划分出的区间指定名称标签
# retbins：设置是否返回每个区间的端点值
# precision：区间端点值的经度
# include_lowest：区间是否包含左端点
#
# DataFrame.reset_index(level=None,drop=False,inplace=False,col_level=0,col_fill='')
# lever：控制要重置哪个等级的索引
# drop：默认False，表示索引列会被还原为普通列，否则丢失
# inplace：默认False，不修改原有的DataFrame，而是创建新的
# col_level：有多个级别时，用于确定将标签插入哪个级别。默认为0，插入第一个层级
# col_fill：确定如何命名其他级别，默认‘’，如果为None，重复使用索引名
#
# figure(num=None,figsize=None,dpi=None,facecolor=None,edgecolor=None,frameon=True,clear=False)
# num：设置窗口的名称，默认None，可选参数
# figsize：窗口大小，默认None，可选
# dpi：窗口分辨率，默认None，可选
# facecolor：窗口背景色，可选
# edgecolor：窗口边框颜色，可选
# frameon：是否绘制窗口的图框，False则绘制，可选
# clear：True并且窗口中已经有图形，则清除图形，可选
#
# hist(x,bins=None,range=None,density=False,color=None,edgecolor=None,linewidth=None)
# x：绘制直方图的数据
# bins：证书则分位相应数量的区间，默认10；如为序列，用序列的元素作为区间端点值
# range：指定分组统计数据范围，不在此范围则忽略，如果bins取值为序列，则此参数无效
# density：如为TRUE，绘制频率直方图；False则绘制频数直方图
# color、edgecolor、linewidth：设置柱子的填充颜色，边框颜色，边框粗细，可选

# 8、使用自定义区间绘制直方图
'''
import pandas as pd
import xlwings as xw
import matplotlib.pyplot as plt
df = pd.read_excel('描述统计.xlsx')
df.columns = ['序号', '员工姓名', '月销售额']
df = df.drop(columns=['序号', '员工姓名'])
df_describe = df.astype(float).describe()
df_cut = pd.cut(df['月销售额']), bins = range(8, 36, 4)  # 按照指定的端点划分区间
cut_count = df['月销售额'].groupby(df_cut).count()
df_all = pd.DataFrame()
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.reParams['font.sans-serif'] = ['SimHei']
n, bins, patches = plt.hist(df['月销售额'], bins= range(8, 36, 4), edgecolor = 'black', linewidth = 0.5)
plt.xticks(bins)
plt.title('月销售额频率分析')
plt.xlabel('月销售额')
plt.ylable('频数')
app = xw.App(visible = False)
workbook = app.books.open('描述统计.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('描述统计.xlsx')
workbook.close()
app.quit()
'''

# 9、使用回归分析预测未来值
'''
import pandas as pd
from sklearn import linear_model
df = pd.read_excel('回归分析.xlsx', header = None)
df = df[2:]
df.columns = ['月份', '电视台广告费', '视频门户广告费', '汽车当月销售额']  # 重命名数据列
x = df['视频门户广告费', '电视台广告费']   # 获取“视频门户广告费”列和“电视台广告费”列的数据作为自变量
y = df['汽车当月销售额']  # 获取“汽车当月销售额”列的数据作为因变量
model = linear_model.LinearRegression()  # 创建一个线型回归模型
model.fit(x,y)  # 用自变量和因变量数据对线型回归模型进行训练，拟合出线型回归方程
R2 = model.score(x,y)
print(R2)
'''
# LinearRegression(fit_intercept=True,normalize=False,copy_X=True,n_jobs=1)
# fit_intercept:是否需要计算截距，默认True，可选
# normalize：是否对数据进行标准化处理，默认False，可选
# copy_X:默认True，表示复制X值，如果False，表示该值会被覆盖，可选
# n_jobs：计算时使用的CPU数量，默认1，可选

# 10、使用回归方程计算预测值
'''
import pandas as pd
from  sklearn import linear_model
df = pd.read_excel('回归分析.xlsx', header = None)
df = df[2:]
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_inter-cept)  # 获取线性回归方程
print('线性回归方程为：', '\n', result)  # 输出线性回归方程
a = 30  # 设置视频门户广告费
b = 20  # 设置电视台广告费
y = coef[0] * a + coef[1] * b + model_intercept  # 根据线性回归方程计算汽车销售额
print(y)  # 输出计算出的汽车销售额
'''

# 11、python中制作图表
# plot(x,y,color,linewidth,linestyle)
'''
import matplotlib.pyplot as plt
x = [1, 2, 3, 4, 5, 6]
y = [2, 4, 6, 8, 10, 12]
plt.plot(x, y, color = 'red', linewidth = 3, linestyle = 'solid')  # 绘制折线图
plt.show()
'''


# 12、制作柱形图
# bar(x,height,width=0.8,bottom=None,align='center',color,edgecolor,linewidth)
'''
import matplotlib.pyplot as plt
x = [1, 2, 3, 4, 5, 6]
y = [50, 60, 80, 78, 95, 70]
plt.bar(x, y, width = 0.8, align = 'center', color = 'blue')
plt.show()
'''

# 13、制作条形图
# barh(y,width,height=0.8,left=None,align='center',color,edgecolor,linewidth)
'''
import matplotlib.pyplot as plt
x = [1, 2, 3, 4]
y = [60, 25, 78, 50]
plt.bath(x, y, align = 'center', color = 'blue')
plt.shou()
'''

# 14、饼图
'''
import matplotlib.pyplot as plt
x = [25, 45, 69, 30, 80, 12]
plt.pie(x)
plt.show()
'''

# 15、python中导入Excel数据制作图表
'''
import pandas as pd
import xlwings as xw
import matplotlib.pyplot as plt
df = pd.read_excel('业绩表.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']  #为中文文本设置默认字体，避免中文显示乱码
plt.rcParams['axes.unicode_minus'] = False  # 解决坐标为负数时，无法正常显示负号的问题
x = df['月份']
y = df['销售额']
plt.bar(x, y, color = 'black')
app = xw.App(visible = False)
workbook = app.books.open('销售业绩表.xlsx')
worksheet = workbook.sheets['销售业绩']
worksheet.pictures.add(figure, left = 500)
workbook.save()
workbook.close()
app.quit()
'''
# 制作散点图
# plt.scatter(x, y, s = 500, color = 'red', marker = '*')
# 制作面积图
# plt.stackplot(x, y, labels = '图标名', colors = 'red')

# 16、python中制作组合图表
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售业绩表.xlsx')
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['月份']
y1 = df['销售额']
y2 = df['利润']
plt.plot(x, y1, color = 'black', linewidth = 4)
plt.bar(x, y2, color = 'blue')
plt.show()
'''
# 双折线图
'''
plt.plot(x1, y1, color = 'red', linewidth = '4', linestyle = 'solid')
plt.plot(x1, y2, color = 'black', linewidth = '3', linestyle = 'solid')
plt.show()
'''

# 17、添加并设置图表标题和坐标轴标题
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售表.xlsx')
plt.rcParam['font.sans-serif'] = ['SimHei']
plt.rcParam['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
plt.bar(x, y, color = 'black')
# 添加并设置图表标题
plt.title(label = '各月销售额对比图', fontdict = {'family': 'KaiTi', 'color': 'red', 'size': 30},loc = 'left')
# 添加并设置x轴标题
plt.xlabel('月份', fontdict = {'family': 'SimSun', 'color': 'black', 'size': 20}, labelpad = 20)
# 添加并设置y轴标题
plt.ylabel('销售额', fontdict = {'family': 'SimSun', 'color': 'black', 'size': 20}, labelpad = 20)
plt.show()
'''
# title(label,fontdict=None,loc='center',pad=None)
# label:图表标题的文本内容
# fontdict：图表标题的字体、字号和颜色
# loc：显示位置，默认center
# pad：标题到图表坐标系顶端的距离

# xlabel/ylabel(label,fontdict=None,labelpad=None)
# label：坐标轴标题的文本内容
# fontdict：坐标轴标题的字体、字号和颜色
# labelpad：坐标轴标题到坐标轴的距离

# 18、添加图例
'''
x = df['月份']
y = df['销售额']
plt.bar(x, y, label = '销售额')  # 制作柱形图并设置图例名
plt.legend(loc = 'upper left', fontsize = 20)  # 添加并设置图例
plt.show()
'''
# legend(loc,fontsize,facecolor,edgecolor,shadow=False)
# loc:显示位置，常用的upper left,upper right,lower left,lower right,表示左上角，右上角、左下角，右下角
# fontsize：图例名的字号
# facecolor：图例框的背景色
# edgecolor：图例框的边框色
# shadow：给图例添加阴影，默认False不添加

# 19、添加设置数据标签
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售表.xlsx')
plt.rcParam['font.sans-serif'] = ['SimHei']
plt.rcParam['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
plt.plot(x, y, color = 'red', linewidth = 3, linestyle = 'solid')  # 制作折线图
for a,b in zip(x,y):
    # 添加并设置数据标签
    plt.text(a, b, b, fontdict = {'family': 'KaiTi', 'color': 'red', 'size': 20})
plt.show()
'''
# text(x,y,s,fontdict=None)
# x/y:坐标
# s：标签文本内容
# fontdict：可选，设置数据标签的字体、字号、颜色

# 20、设置y轴的取值范围
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售表.xlsx')
plt.rcParam['font.sans-serif'] = ['SimHei']
plt.rcParam['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
plt.plot(x, y, color = 'red', linewidth = 3, linestyle = 'solid')
plt.ylim(0,1200000)  # 设置y轴的取值范围,最小最大值
for a,b in zip(x,y):
    plt.text(a, b, b, fontdect = {'family' : 'KaiTi', 'color' : 'red', 'size': 20})
plt.show()
'''

# 21、为组合图表添加并设置次坐标轴
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售表.xlsx')
plt.rcParam['font.sans-serif'] = ['SimHei']
plt.rcParam['axes.unicode_minus'] = False
x = df['月份']
y1 = df['销售额']
y2 = df['同比增长']
plt.bar(x, y1, color = 'grey', label = '销售额')
plt.legend(loc = 'upper left', fontsize = 20)
plt.twinx()  # 为图表设置双坐标轴
plt.plot(x, y2, color = 'black', linewidth = 3, label = '同比增长')  # 制作折线图
plt.legend(loc = 'upper right', fontsize = 20)
plt.show()
'''

# 22、 设置网格线
'''
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel('销售表.xlsx')
plt.rcParam['font.sans-serif'] = ['SimHei']
plt.rcParam['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
plt.plot(x, y, color = 'black', linewidth = 3, linestyle = 'solid')
plt.grid(b = True, axis = 'y', color = 'red', linestyle = 'dashed', linewidth = 1)  # 为y轴添加设置网格线
plt.show()
'''
# grid(b,which,axis,color,linestyle,linewidth)
# b:True则显示网格线，False不显示
# which：那种类型的网格线，major，minor，both，只设置主要网格线，设置次要，两者都设置
# axis：哪个轴的网格线，x，y，both
# color、linestyle、linewidth：网格线的颜色、线型、粗细