import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = 'SimHei'
plt.rcParams['axes.unicode_minus'] = False
import pandas as pd
import numpy as np

df = pd.read_excel("q4result.xlsx",index_col='样本编号')
df_pic = df.loc[[2,3,6,25,28,52,67,93,94,313],:]
# df_pic.sort_values('损失降幅',inplace=True)
plt.scatter(range(len(df_pic)),df_pic['损失降幅'].values,marker='p',s=50)
plt.ylim(0.3,0.6)
plt.xticks(range(len(df_pic)),df_pic.index.astype('str'))
plt.xlabel('样本编号')
plt.ylabel('RON损失降幅')
# plt.savefig('q4降幅散点图.jpg')
# plt.show()

sample_code = list(df_pic.index)

df_s = pd.read_excel('q4data2.xlsx',index_col='样本编号')

# dict = {'自变量':["x1'","x2'","x3'","x4'","x5'","x6'","x7'","x8'","x9'","x10'","x11'","x12'"],
#         '2':[169.0, 52.3, 49.955209000895486, 250.00000001305563, 13.949885724606805, 236.7317999993248, 0.578173076848835, 8.885450549995653, 2.2781382499999627, 237.3548000000003, 2277.3611, 6.8793749999992935],
#         '3':[177.0, 52.3, 50.21517652590782, 250.0000000291667, 13.85723768283674, 236.73179996531474, -0.4868591845438578, 8.84611384985329, 2.2776433999853842, 239.75333000000808, 2117.8924999999967, 6.87937499996829],
#         '6':[163.0, 52.10000000000001, 50.10215428387455, 250.0000051990987, 13.888465911454654, 236.7318000584507, -1.3239579486401116, 9.21258149853323, 2.258351999872742, 241.00297500008, 1940.6933999999912, 6.879374999731523],
#         "25":[222.0, 55.12857142857143, 50.06695202437646, 255.31249559668257, 11.169282524657485, 236.73179995646387, -2.955682305603234, 10.119935799855089, 2.324869099992706, 169.93940499999846, 2476.1814999999933, 6.8793749999748455],
#         "28":[170.0, 56.00000000000001, 49.43898730519431, 250.00002568107945, 11.24451098924199, 236.73179953735988, -1.290458413779933, 10.42254099838372, 2.3260479499250213, 173.16873500002637, 2459.591849999956, 6.879374999691545],
#         "52":[126.0, 58.52857142857142, 50.06921550369621, 250.00000009428516, 13.97818633589903, 236.73179999774518, -2.745286150620401, 8.995656099982314, 2.290238349999911, 164.86446000000117, 1536.48345, 6.879374999997179],
#         "67":[137.0, 58.0, 50.25314760739547, 250.0000383027744, 11.954401750190042, 236.73179544949144, -1.2195432547655338, 10.124934988373445, 2.2956041491400305, 150.4927750005985, 2236.941399999651, 6.8793749972739455],
#         "93":[287.0, 52.5, 50.053332061039924, 250.00000040656371, 14.71777140060802, 236.73179974119483, 1.1014849969257603, 1.6031870499306364, 0.00670048201706412, 171.67890999991758, 2228.8012000000012, 6.87937499986514],
#         "94":[279.0, 50.45, 50.03312400087336, 250.00000000503817, 14.08309121741429, 236.73179999839184, 1.8650957498873706, 1.6021669499969664, 0.0070135524500092895, 168.28891499999966, 2483.0369499999997, 6.879374999999144],
#         "313":[392.0, 55.05, 610.8467100039672, 250.00000006016242, 12.195198088500401, 706.6461424973039, 1.6824734922913802, -0.03327292325470828, 2.2197373999986776, 271.5438175000004, 1788.629575000003, 135.7755343125013]
#         }
# np.transpose(pd.DataFrame(dict)).to_excel("q4对比表.xlsx")

df_s_new = df_s.loc[sample_code,['辛烷值RON.1','S-ZORB.FT_9202.PV','S-ZORB.FC_1203.PV']]
df_p_new = pd.DataFrame({'样本编号':sample_code,
                         "S-ZORB.FT_9202.PV":[250,250,250,255.312,250,250,250,250,250,250],
                         "S-ZORB.FC_1203.PV":[13.94988,13.857237,13.88846,11.16928,11.24451,13.978186,11.95440175,14.7177714006,14.08309121,12.1951980]
                         }).set_index('样本编号')
plt.figure(1)
plt.bar(np.arange(len(df_s_new)),df_s_new['S-ZORB.FT_9202.PV'],alpha=0.5,width=0.3,label='原始数据', lw=3)
plt.bar(np.arange(len(df_s_new))+0.3, df_p_new['S-ZORB.FT_9202.PV'],alpha=0.5,width=0.3,label='优化数据', lw=3)
plt.xticks(np.arange(len(df_s_new))+0.2,sample_code)
plt.legend(loc='upper left')
plt.xlabel('样本编号')
# plt.savefig("q4pic1.jpg")
plt.show()

plt.figure(2)
plt.bar(np.arange(len(df_s_new)),df_s_new['S-ZORB.FC_1203.PV'],alpha=0.5,width=0.3,label='原始数据', lw=3)
plt.bar(np.arange(len(df_s_new))+0.3, df_p_new['S-ZORB.FC_1203.PV'],alpha=0.5,width=0.3,label='优化数据', lw=3)
plt.xticks(np.arange(len(df_s_new))+0.2,sample_code)
plt.legend(loc='upper left')
plt.xlabel('样本编号')
# plt.savefig("q4pic2.jpg")
plt.show()

plt.figure(3)
plt.scatter(range(len(df_s_new)),list(df_pic['辛烷值(产品)']),marker='v',s=50,label='辛烷值(产品)')
plt.scatter(range(len(df_s_new)),list(df_pic['辛烷值(优化)']),marker='*',s=50,label='辛烷值(优化)')
for i in range(len(df_s_new)):
    plt.axvline(x=i,linestyle=':',alpha=0.2)
plt.xticks(range(len(df_s_new)),sample_code)
plt.xlabel('样本编号')
plt.ylabel('辛烷值')
plt.legend()
plt.savefig("产品与优化后的辛烷值对比图.jpg")
plt.show()