import pandas as pd

import numpy as np

import matplotlib.pyplot as plt



path=r'D:\1.Python\全量明细.xlsx'

a=pd.read_excel(path,sheet_name='全量明细1')

b=pd.read_excel(path,sheet_name='全量明细2')

c=pd.read_excel(path,sheet_name='全量明细3')



a1=a.groupby(['业务线'])['处理天数'].agg( 'mean' )

b1=b.groupby(['业务线'])['处理天数'].agg( 'mean' )

c1=c.groupby(['业务线'])['处理天数'].agg( 'mean' )



ct=pd.DataFrame([a1,b1,c1],index=['一期','二期','三期'])

ct=ct.T

ct1=ct.sort_values(by='三期',ascending=False)

d=ct1.iloc[0:8,0:3]



#提取行
d1=d.iloc[[6]]
#删除指定行
d.drop(index=['管理'],inplace=True)
#新增指定行
d2=d.append(d1)

#提取特定两行
d3=d.iloc[[2,3]]

#增加行
d.loc['基础']=d3.apply(lambda x : x.sum(),axis=0)

# 提取数据

name = list(d.index)

x=range(len(name))



y1 = list(d.iloc[:,0])

y2 = list(d.iloc[:,1])

y5 = list(d.iloc[:,2])



# 设置正常显示中文标签

plt.rcParams['font.sans-serif'] = ['SimHei']

# 正常显示负号

plt.rcParams['axes.unicode_minus'] = False

# 设置字体大小

plt.rcParams.update({'font.size':20})

plt.figure(figsize=(10,6))

plt.subplot(111)


bar_width=0.2
#在主坐标轴绘制柱形图

plt.bar(x,y1,label='一期',color='royalblue')

plt.bar(x,y2,bottom=(d.iloc[:,0]),label='二期',color='orange')

plt.bar(x,y5,bottom=(d.iloc[:,0]+d.iloc[:,1]),label='三期',color='darkorchid')

#设置坐标轴的取值范围，避免柱子过高而与图例重叠

plt.ylim(0,60)

#设置图例

plt.legend(loc='upper center',ncol=3)

#设置横坐标的标签

plt.xticks(x,name,rotation=35,weight='bold')

#横坐标字体颜色

label_colors = ['black','black','black','black','red','black','black','black']

plt.gca().tick_params(axis='x', labelcolor='black')

#横坐标单个字体颜色
plt.gca().get_xticklabels()[7].set_color('red')

# 设置标题
plt.title('\n业务数量分布\n',fontsize=20,loc='center',color = 'k',weight='bold')


for a,b in enumerate(d.iloc[:,0]):
  plt.text(a, b/2, '%.0f'%b, ha='center', va= 'bottom',fontsize=13)
for a,b,c in zip(x,d.iloc[:,0]+d.iloc[:,1],d.iloc[:,0]+(d.iloc[:,1])/2):
  plt.text(a,c, '%.0f'%(round(b,0)), ha='center', va= 'bottom',fontsize=13)
for a,b,c in zip(x,d.iloc[:,0]+d.iloc[:,1]+d.iloc[:,2],d.iloc[:,0]+d.iloc[:,1]+(d.iloc[:,2])/2) :
  plt.text(a,c, '%.0f'%(round(b,0)), ha='center', va= 'bottom',fontsize=13)

plt.show()