-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathforecasting.py
149 lines (119 loc) · 5.99 KB
/
forecasting.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
#there's a possibility of an unused lib here
import numpy as np
import pandas as pd
import random
from sklearn.preprocessing import MinMaxScaler
import os
from flask_restful import Resource,Api
from flask import Flask,request,jsonify
import cx_Oracle
from sqlalchemy import create_engine
from datetime import timedelta
import math
from statsmodels.tsa.holtwinters import ExponentialSmoothing
from sklearn.metrics import mean_squared_error
from flask_cors import CORS
import requests
import json
random.seed(0)
np.random.seed(0)
app=Flask(__name__)
api=Api(app)
here = os.path.dirname(os.path.abspath(__file__))
cors = CORS(app, resources={r"/*": {"origins": "*"}})
cf_port = os.getenv("PORT")
cx_Oracle.init_oracle_client(lib_dir=r"/home/vcap/app/oracle/instantclient")
dsn_tns = cx_Oracle.makedsn('fill urr conn properties here', 'fill urr conn properties here', service_name='fill urr conn properties here')
cstr = 'fill urr conn properties here'.format(
sid=dsn_tns
)
class forecast(Resource):
def get(self):
with cx_Oracle.connect(user='fill urr conn properties here', password='fill urr conn properties here', dsn=dsn_tns) as conn:
curr=conn.cursor()
#seasonal optimizer
def param_opt(df):
date=df.index[-1]- timedelta(days=pred_periods)
train = df.loc[:date]
test = df.loc[date+timedelta(days=1):]
max_param=60
params=[]
rmse=[]
i=2
while i<=max_param:
model = ExponentialSmoothing(train, seasonal='mul', seasonal_periods=i).fit()
pred = model.predict(start=test.index[0], end=test.index[-1])
test["pred"]=pred
testScore = math.sqrt(mean_squared_error(test["TRX_AMOUNT"], test["pred"]))
params.append(i)
rmse.append(testScore)
i=i+1
optimal=pd.DataFrame(columns=["Param","RMSE"])
optimal["Param"]=params
optimal["RMSE"]=rmse
optimal=optimal.sort_values(by=['RMSE'])
s_per=optimal["Param"].head(1).values
rmse=optimal["RMSE"].head(1).values
return s_per,rmse
#listing table and trx_type
table=["forecast_main","forecast_branch"]
trx_type=["DB","CR"]
#start the loop
#do for each_db table
for tab in table:
print()
if tab=="forecast_branch":
q_brid="branch_account_id"
else:
q_brid="main_account_id"
curr=conn.cursor()
sql="select count(*) from "+tab
curr.execute(sql)
rownum = curr.fetchone()
rownum= int(list(rownum)[0])
#if data exist
if rownum>0:
#do per trx_type income and outcome
for flg in trx_type:
sql="select count(distinct("+q_brid+")) from "+tab
curr.execute(sql)
branch= curr.fetchone()
branch= int(list(branch)[0])
#each branch
for br_id in range(1,branch+1):
sql="select * from "+tab+" where "+q_brid+"="+str(br_id)+" and trx_type='"+flg+"'"
df=pd.read_sql(sql,con=conn)
df=df.drop(df.columns[0], axis=1)
df=df.drop(df.columns[2], axis=1)
df=df.drop(df.columns[2], axis=1)
df=df.drop(df.columns[2], axis=1)
df['TRX_DATE']=pd.to_datetime(df['TRX_DATE'])
df = df.set_index(pd.DatetimeIndex(df['TRX_DATE']))
df=df.drop(df.columns[1], axis=1)
df.sort_index(inplace=True)
pred_periods=24
s_per=param_opt(df)
model = ExponentialSmoothing(df, seasonal='mul', seasonal_periods=s_per[0]).fit()
pred = model.forecast(pred_periods)
df_forecast= pd.DataFrame(pred,columns=["forecast_amount"])
df_forecast["branch_id"]=br_id
df_forecast["recommended"]=df_forecast["forecast_amount"]+s_per[1]
df_forecast=df_forecast.reset_index()
df_forecast=df_forecast.rename(columns={"index":"date"})
df_forecast=df_forecast[["branch_id","date","forecast_amount","recommended"]]
df_forecast["trx_type"]=flg
df_forecast=df_forecast.rename(columns={"branch_id":q_brid,"date":"trx_date"})
print(sql)
#for insert
c_alchemy = create_engine(cstr)
df_forecast.to_sql(tab,c_alchemy, if_exists='append',index=False)
else:
print("no data")
# use triple quotes if you want to spread your query across multiple lines
return ("done")
api.add_resource(forecast,'/fore')
if __name__ == '__main__':
if cf_port is None:
app.run( host='0.0.0.0', port=5000, debug=True, threaded=True )
else:
app.run( host='0.0.0.0', port=int(cf_port), debug=True, threaded=True)