Unable to run the python script correctly

[klehar]

Hi,

I'm trying to run a python script that will essentially perform time series forecasting on my input data.

What I want is the next 12 predicted values.

I did this in jupyter but not able to do it in power bi as I'm getting the same dataset instead of predictions in power bi

What is wrong?
Dataset has 110 records. 2 records pasted below:

date	tcv
1/1/2013	2011131
2/1/2013	2053142

My code:

import numpy as np
import matplotlib.pyplot as plt
import pypyodbc  as pyodbc
import pandas as pd 
from statsmodels.tsa.arima_model import ARMA,ARMAResults,ARIMA,ARIMAResults
from statsmodels.graphics.tsaplots import plot_acf,plot_pacf # for determining (p,q) orders
from statsmodels.tsa.holtwinters import ExponentialSmoothing

SERVER_NAME = 'servername.its.corp.net'
DATABASE_NAME = 'GDB'


cnxn = pyodbc.connect(Driver='{SQL Server}', Server=SERVER_NAME , database=DATABASE_NAME,
                      trusted_connection='Yes')

cursor = cnxn.cursor()



sql_query = """
select  * from my_table
"""


# With Headers
df = pd.read_sql(sql_query, cnxn,index_col='date',parse_dates=True)
#index col is required to make sure stasmodel on this dataset we need to set index frequency
df.index.freq = 'MS'

method_TESmul12 = ExponentialSmoothing(df['tcv'],trend='mul',seasonal='mul',seasonal_periods=12).fit()
dataset = method_TESmul12.forecast(12)

[Icey]

Hi @klehar ,

I have tested with Python 3.10.2 and the results in Power BI and Python are the same.

Here is the table I used to test.

date	tcv
2013-01-01	2011131
2013-02-01	2053142
2013-03-01	2033123
2013-04-01	2040222
2013-05-01	2044113
2013-06-01	2056333
2013-07-01	2060245
2013-08-01	2058756
2013-09-01	2068524
2013-10-01	2070555
2013-11-01	2056700
2013-12-01	2069322
2014-01-01	2080246
2014-02-01	2076623
2014-03-01	2075456
2014-04-01	2068456
2014-05-01	2078123
2014-06-01	2074235
2014-07-01	2080654
2014-08-01	2081456
2014-09-01	2090231
2014-10-01	2085165
2014-11-01	2064897
2014-12-01	2070542
2015-01-01	2065894
2015-02-01	2075698

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @klehar ,

I have posted my test result. Please check if any differences.

Best Regards,

Icey

[klehar]

So I made some modifications to the code and Im getting the output now.

However, my power bi and python show different predictions

Wonder where I have to set the seed, if that is the case

NewCode

import numpy as np
import matplotlib.pyplot as plt
import pypyodbc  as pyodbc
import pandas as pd 
from statsmodels.tsa.arima_model import ARMA,ARMAResults,ARIMA,ARIMAResults
from statsmodels.graphics.tsaplots import plot_acf,plot_pacf # for determining (p,q) orders
from statsmodels.tsa.holtwinters import ExponentialSmoothing

SERVER_NAME = 'db.its.corp.net'
DATABASE_NAME = 'GDB'


cnxn = pyodbc.connect(Driver='{SQL Server}', Server=SERVER_NAME , database=DATABASE_NAME,
                      trusted_connection='Yes')

cursor = cnxn.cursor()



sql_query = """
select * from my_table
"""


# With Headers
df = pd.read_sql(sql_query, cnxn,index_col='date',parse_dates=True)
#index col is required to make sure stasmodel on this dataset we need to set index frequency
df.index.freq = 'MS'

method_TESmul12 = ExponentialSmoothing(df['tcv'],trend='mul',seasonal='mul',seasonal_periods=12).fit()
range = pd.date_range('01-02-2022',periods=12,freq='MS')
predictions = method_TESmul12.forecast(12).astype(int)
predictions_range = pd.DataFrame({'Date':range, 'TCV':predictions})
predictions_range