Mastering Tableau 2021: How to use Scripting (Python/R) in Tableau Prep

This article is a modified excerpt of Mastering Tableau 2021, Chapter Three¹ and requires Tableau Prep knowledge in order to follow along the exercises.

The script functionality in Tableau Prep is one of the more recently added features to Tableau. I chose the topic for this (second) excerpt because adding a programming language like Python or R to your ETL processes will give you endless flexibility. If you ever reach the boundaries of Prep’s offerings you can fall back on scripting and write your own code to do exactly what you want.

Photo by Lounidesign

Content:

1. Loading the data
2. Connecting Tableau Prep with Python (or R)
3. Creating a script in Tableau Prep
4. Running the script
5. Extra Tip

Loading the data

For this exercise we will be using the Boston Airbnb dataset, more specifically the calendar table. Download this dataset from https://www.kaggle.com/airbnb/boston and connect to the calendar table in Tableau Prep Builder:

Figure 1: Calendar dataset

Connecting Tableau Prep with Python (or R)

In order to make use of scripting, you have to connect to Python (or R), outside of Tableau first. The following steps are based on the instructions from here for Python and here for R.

Connect Python to Tableau Prep by following these steps:

• Download and install Python from python.org (or download and install R from https://www.r-project.org/).
• Download and install tabpy by executing the following command in your terminal (Mac) or on the command line (Windows):

pip install tabpy

Alternatively, if using R, open R and execute:

install.packages(“Rserve”, , “http://rforge.net")

• Open tabpy on the command line/terminal by entering the following command:

tabpy

• Or, in the R GUI, type:

library(Rserve)
Rserve()

• Back in Tableau Prep, click on the + on the right-hand side of the calendar table and add a Clean Step.
• Add a calculated field called PythonTest (use RTest instead if you’ve chosen to experiment with R) with a string value “Test”:

Figure 2: Python test

Creating a script in Tableau Prep

Now, click on the + on the right-hand side of the Clean 1 step and add Script.

By selecting the Script step, the Settings pane at the bottom (see Figure 3) will give you an option to connect to Rserve or TabPy. Rserve is the Tableau server for R and TabPy is the Tableau server for Python.

• Choose your connection type by selecting Rserve or Tableau Python (TabPy) Server. Connect Tableau Prep to tabpy by using localhost and Port 9004 (if using R, connect to Rserve by selecting it under Connection type and using localhost and Port 6311 in the same popup):

If using an SSL-encrypted Rserve server, Port 4912 is the default port.

Figure 3: TabPy Server

Next, if experimenting with Python, create a .py file containing the following code:

def ChangeString(df):
  df[‘PythonTest’] = ‘Python’
  return df

Alternatively, create a .R file containing the following code:

Get ChangeString<- add_column(
.data,
.before = ‘Test’
.after = ‘R’) {Return (data.frame ())
}

• The script we just created is written to change an existing column in Prep and rename every row from Test to Python or R, depending on which language you’ve chosen.
• We’ll continue with the Python script. Back in Tableau Prep Builder, browse for the .py file we just created and add the function name to the Tableau Prep interface. I called the file PrepTableau.py and the function name is ChangeString, as defined in the preceding step:

Figure 4: Adding a function name

Running the script

Run the flow and observe that the PythonTest column has changed from Test to Python:

Figure 5: PythonTest

Our little experiment worked! And even though we used a very simplistic example, it shows that the scripting feature works, and you can just follow your creativity with what else you might be able to do by using scripting. Examples include everything from calculating an inverse normal distribution to machine learning with sentiment analysis or clustering.

Extra Tip

You might have noticed that we did not import pandas in the script itself, but still used a pandas DataFrame. This is due to the fact that the pandas import comes with tabpy. You can see this in your command line/terminal after opening tabpy:

Figure 6: Importing pandas on the command line

If you want to use other libraries in your script, you can install them by using pip install on your command line/terminal. You only have to restart tabpy afterward and you will be able to use them too. Remember, always open tabpy on the terminal/command line first or else Tableau Prep Builder can’t execute the script.

At the time of writing, Tableau Prep does not support a script as data input yet — you could however use a dummy .csv file as a workaround and add a script to the flow immediately after.

I hope you enjoyed this article on scripting. More publication will be coming soon, including hands-on exercises. Please let me know in the comment section if any particular topics are especially of interest for you! Have a great rest of your day!

Sources

[1] M. Meier and D. Baldwin: Mastering Tableau 2021 (May 2021), Packt Publishing, ISBN: 9781800561649