Notes

main_pta.py

# author : makcin

# Importing Libraries
import numpy as np
import pandas as pd
from sklearn.linear_model import LinearRegression
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.ensemble import RandomForestRegressor
import copy
from sklearn.metrics import mean_absolute_error
import openpyxl
from datetime import date, timedelta
from sklearn.linear_model import HuberRegressor, LinearRegression, TheilSenRegressor, RANSACRegressor
from sklearn.linear_model import Ridge, Lasso
from sklearn.model_selection import train_test_split, GridSearchCV
from sklearn.metrics import mean_squared_error, r2_score
from sklearn.linear_model import ElasticNetCV
from sklearn.linear_model import ElasticNet
from sklearn.utils.validation import check_consistent_length, check_array
pd.set_option('display.max_rows', 6000)
from scripts import _check_reg_targets,mean_absolute_percentage_error,best_model_pipeline,taking_average
import warnings
warnings.filterwarnings("ignore")
import re
from openpyxl import load_workbook
from datetime import datetime


# ------- PTA Models --------------------
# 1- PTA HS Prediction Model
# 2- PTA HHS Prediction Model (İş birimi modellenmesi zor olabilir dedi, çeşitli sorular sorduk fakat average ile ilerleme kararı alındı.) (Average alınacak.)
# 3- PTA LS Import Prediction Model (Average)
# 4- PTA LS Export Prediction Model (Average)

# Data Çekme kodu ile değişecek

# 1- PTA HS Prediction Model

df_org = pd.read_excel("PTA_Tags_06082022.xlsx",index_col = 0,engine='openpyxl')


df_org.rename(columns = {'2401.FIC1201':'PTA_Pxylene_Capacity',
'2401.FIC1201 - Average - Confidencee':'PTA_Pxylene_Capacity_Confidence',
'2401.FI3000':'PTA_HHS_Import',
'2401.FI3000 - Average - Confidence':'PTA_HHS_Import_Confidence',
'2401.FI3010':'PTA_HS_Import',
'2401.FI3010 - Average - Confidence':'PTA_HS_Import_Confidence',
'2401.FI3008':'PTA_LS_Import/Export',
'2401.FI3008 - Average - Confidence':'PTA_LS_Import/Export_Confidence',
'5801.11MBL01CT001_XQ60':'Ambient_Temperature',
'5801.11MBL01CT001_XQ60 - Average - Confidence':'Ambient_Temperature_Confidence',
'2401.FI3020':'Kompresor_HS_Cekis',
'2401.FI3020 - Average - Confidence': 'Kompresor_HS_Cekis_Confidence'}, inplace = True)

df_pta = copy.deepcopy(df_org)

# Confidence 0 olan dataları veri setinden düşürmek.
df_pta_manipulated = copy.deepcopy(df_pta)

confidence_cols = list([col for col in df_pta_manipulated.columns if 'Confidence' in col])

for i in confidence_cols:
df_pta_manipulated.drop(
df_pta_manipulated.loc[(df_pta_manipulated[i] == 0)].index,
inplace=True)

df_pta_manipulated.drop(confidence_cols,axis=1,inplace=True)

# Sıfırdan küçük değerlerin sıfıra eşitlenmesi

main_features = list(df_pta_manipulated.columns)

main_features.remove('Ambient_Temperature')
main_features.remove('PTA_LS_Import/Export')


for i in main_features:
df_pta_manipulated.loc[df_pta_manipulated[i] < 0, i] = 0

df_pta_manipulated.loc[df_pta_manipulated['Ambient_Temperature']<=-10,'Ambient_Temperature'] = -10

# Kompresör açık/kapalı durumunun veri ile incelenmesi

df_pta_manipulated['Kompresor_on'] = 1
df_pta_manipulated.loc[df_pta_manipulated['Kompresor_HS_Cekis']<10000,'Kompresor_on'] = 0

target_pta_hs_import_model = 'PTA_HS_Import'


# Model datasının hazırlanması (kullanılmayacak feature'lar çıkarılmalı)


df_pta_hs_import_model = df_pta_manipulated[['Kompresor_HS_Cekis', 'Ambient_Temperature','PTA_HS_Import']]

df_pta_hs_import_model = df_pta_hs_import_model.dropna()


# Model kodları

# Model Pipeline with GridSearchCV

result_pta_hs_import_prediction = best_model_pipeline(n=1000,df=df_pta_hs_import_model,target=target_pta_hs_import_model,name_of_model='PTA_HS_Import_Model')

# Model sonuçları Excel'e yazılıyor.

n = (len(result_pta_hs_import_prediction) - 2)/2

model_pred_pta_hs_import_model = round(result_pta_hs_import_prediction[0], 2)

model_intercept_pta_hs_import_model = round(result_pta_hs_import_prediction[1], 2)

values_used_in_pta_hs_import_model = [round(num, 2) for num in result_pta_hs_import_prediction[int(-n):]]

model_coefs_of_pta_hs_import_model = [round(num, 2) for num in result_pta_hs_import_prediction[2:int(2+n)]]

# Arom FG Prediction Model Sonuçları Excel'e yazılıyor.

wb = load_workbook('Draft_Final_Output.xlsx')

main_summary_sheet = wb['Main_Summary_Sheet']

main_summary_sheet['D12'] = values_used_in_pta_hs_import_model[0]

pta_sheet = wb['PTA-TA']

pta_sheet['B3'] = values_used_in_pta_hs_import_model[0]

pta_sheet['B4'] = values_used_in_pta_hs_import_model[1]

pta_sheet['D3'] = model_coefs_of_pta_hs_import_model[0]

pta_sheet['D4'] = model_coefs_of_pta_hs_import_model[1]

pta_sheet['B5'] = model_intercept_pta_hs_import_model

pta_sheet['L2'] = model_pred_pta_hs_import_model

pta_sheet['R1'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

# 2- PTA HHS Import Model (Average)

pta_hhs_import_value = taking_average(df_pta_manipulated,'PTA_HHS_Import')

pta_sheet['L3'] = pta_hhs_import_value

# 3- PTA LS Import Model (Average)

df_pta_ls_import_export_df = copy.deepcopy(df_pta_manipulated)

df_pta_ls_import_export_df['Pxylene_usage'] = 0

df_pta_ls_import_export_df.loc[df_pta_ls_import_export_df['PTA_Pxylene_Capacity']>=1,'Pxylene_usage'] = 1

df_pta_ls_import_export_df['LS_Import_new'] = df_pta_ls_import_export_df[df_pta_ls_import_export_df['Pxylene_usage']==0][['PTA_LS_Import/Export']]

df_pta_ls_import_export_df['LS_Import_new'] = df_pta_ls_import_export_df['LS_Import_new'].fillna(0)

df_pta_ls_import_export_df['LS_Export_new'] = df_pta_ls_import_export_df[df_pta_ls_import_export_df['Pxylene_usage']==1][['PTA_LS_Import/Export']]

df_pta_ls_import_export_df['LS_Export_new'] = df_pta_ls_import_export_df['LS_Export_new'].fillna(0)

df_pta_ls_import_export_df['LS_Import_new_zeros_excluded'] = df_pta_ls_import_export_df['LS_Import_new'].replace(0, np.NaN)

df_pta_ls_import_export_df['LS_Export_new_zeros_excluded'] = df_pta_ls_import_export_df['LS_Export_new'].replace(0, np.NaN)

pta_ls_import_value = taking_average(df_pta_ls_import_export_df,'LS_Import_new')

pta_ls_export_value = taking_average(df_pta_ls_import_export_df,'LS_Export_new')

pta_sheet['L4'] = pta_ls_import_value

pta_sheet['L5'] = pta_ls_export_value

wb.save(r'C:Usersmert.akcinDesktopDaily NG DemandNG_Demand_NewNg_Demand_Prod_CodeDraft_Final_Output.xlsx')
wb.close()