Forecasting in Tableau using Tabpy

Hello Curious Person, If you want to create a dynamic forecasting dashboard in Tableau using the power of Python, You have come to the right place. So without any further ado, let’s get started.

To unleash the power of Python in Tableau, first, we have to set up and run the Tabpy server. To do that install the tabpy library using the magic command

pip install tabpy

and then start the server using the below command

tabpy

you will see a message in the terminal stating that “Web service listening on port 9004”. Great Work, Your Tabpy server is up and running. Now let’s open up Tableau and create the connection between Tableau and Tabpy. Sadly, you will require a Professional Edition of Tableau to do this. I guess Tableau requires money to feed that Python 😉. You can also use Trial version of Tableau.

With that being handled, open up Tableau Then Click on Help → Settings and Performance → Manage Analytics Extension Connections → TabPy. Now you will see a dialog box asking you few information. Just provide it two, that is give “localhost” as the Hostname and “9004” as the Port. Then Click on “Test Connection”, if you see a dialog box stating “Successfully connected to the analytics extension”. Well done, your Tableau client is connected to the Tabpy server. Now Click OK → Save → Close.

Now we need the crude oil for our project that is our data, Download it from here. Once it is done, Open the dataset using Tableau and go to the Worksheet. Your blank dashboard should look like below

Now create a calculated field named “Month date” as below.

Then create a parameter named “Months Forecast” as below. Once this parameter is created, click on this created parameter and click the Show parameter. Now you can change the value of this parameter to determine how many months of forecast you want to perform.

Then create a calculated field named “Forecast date” as below

Then create a calculated field named “Forecast” as below

The code for this field is big so I will paste it below

SCRIPT_REAL(
'
import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothing
dates = _arg1
passeng = _arg2
months_forecast = min(_arg3)
df = pd.DataFrame({"Month":dates, "Passengers":passeng})
df["Month"]= pd.to_datetime(df["Month"], format="%Y-%m")
df.set_index("Month", inplace=True)
model = ExponentialSmoothing(df, trend= "additive", seasonal= "additive", seasonal_periods=12,
                            initialization_method="estimated"
                            ).fit()
actual = pd.Series(list(df["Passengers"].values))
if(months_forecast > 0):
    forecast = pd.Series(list(model.predict(start= len(df), end=len(df)+months_forecast-1).values))
    forecast = forecast.apply(lambda x: 0 if x < 0 else x)
    return list(pd.concat([actual, forecast]))[-1*len(df):]
else:
    return list(actual)
',
ATTR([Month]), ATTR([#Passengers]), MIN([Months Forecast])
)

Now create a calculated field named “Actual” as below

Now drag the “Forecast date” to “Columns” (Make sure it is MONTH(Forecast date)), drag “Measure Names” to “Filters”, Select Only “Actual” and “Forecast” as below

drag “Measure Names” to “Color” in “Marks”, drag “Measure Values” to “Rows”.

After all this work your dashboard should look like below

At last, we are done with our project. Congratulations on creating your dynamic forecasting dashboard in Tableau. Play with it and see the future 😎

I will be back with more interesting articles like this, till then Enjoy your day 😇.

References-

1. http://www.clearpeaks.com/tableau-python/