In the previous blog, I shared how to install the TabPy server and run Python code in Tableau with 4 SCRIPT functions. If you have to work with many calculations but those calculations repeat many times, it could cause a bad performance. Finding a way to avoid repeating calculations is my priority. In that case, running the saved Python functions would be a good choice because you only need to call the function without writing calculations again. It will help to increase the performance in Tableau when you use fewer calculations.
In this blog, I will share:
1/ Some notes when writing functions/ returning results from Python script
* Data Structure
* Data Type
* IDE to write Python script
* Write Python function
2/ Store the Python file and set the Python Path
3/ Call function from saved Python file and pass arguments into functions
1/ Some notes when writing functions/ returning results from Python script
In this part, I will share some important points you need to consider before writing the Python code and running it in Tableau.
- Data structure
First, let's analyze the structure of the data source in Tableau. As you know, the data source structure in Tableau contains columns and rows. For each column, there are many rows.
For example: In image 1 below, I have a table with 4 columns and N rows. The structure of that data table is the same as the structure we have in the Data Source in Tableau.
If I split each column separately, it would look like Image 2 below. Each cell has its own identification. For example: If I want to know which ID is on the third row, I will call ID[2]. Then, the system will understand that ID[2] = USA-769-2020. Or Sales[1] =250.
In Python, each of those columns is represented by a list. For example: the ID list contains elements from ID[0], ID[1], ..., to ID[N]. To let the Python console know a variable is a list, we use the square brackets. If we want to access a specific value from the list, we only need to fill in a number inside the square bracket. ID[2] is the value on the third row of column ID. You can read more functions with the list in Python here.
The structure of the data source in Tableau is in rows and columns. The data type for input and output from the Python function is a list (array). To learn more about the list, I recommend you read the Python documentation at: https://docs.python.org/3/library/stdtypes.html#sequence-types-list-tuple-range.
- Data type
As you already know there are 4 SCRIPT functions (BOOL, INT, REAL, and STR) in Tableau that I introduced in Part 1. Therefore, when we write a Python function, we should make sure that the result will return in one of 4 data types: boolean, integer, real, or string. If we don't know the result in one of those 4 data types, we could use the wrong SCRIPT function leading to an error.
- Use an IDE to write Python script
After understanding the data type for the input and output from the Python functions, I will share about writing Python functions. Any IDE (Integrated Development Environment) can write and save Python functions. Many IDEs that I used: PyCharm, Jupyter Notebook from Anaconda, Command Prompt, and Visual Studio Code (Image 3). Each IDE has pros and cons. For me, I usually use Jupyter Notebook from Anaconda.
Now, If you choose for yourself an IDE, let's discover some built-in functions in Python.
- Write Python functions
As I mentioned above, we need to import the list and output the list from the Python function. In this part, I will share how to create a list and print out the list from functions.
In Image 4 below, I created a list called my_example_list containing values: Pen, Pencil, Table, Chair, and Laptop. Each element is in a single quotation mark (or double quotation mark) and is separated by a comma. To let Python know that is a list, we should have a square bracket[] (On the line In[13]).
In the cell In[14], I wrote a for loop to iterate each element inside the my_example_list and printed out each element in that list in a new line. In cell In[18], I also wrote a for-loop to iterate each element inside the my_example_list. However, at this time, for each element [i] in the list, I want to uppercase all characters of each element. In this case, I used the upper() function.
Next, I would like to write a Python function. Whenever I call the name of the function and pass the list in, it will return the result to me. There are 2 ways:
Basic way:
To start writing a function, I need the keyword "def" to let Python know that we are defining a function. For example, In cell In[23], I wrote a function upper_char:
def upper_char(list1):
result = []
for i in range(len(list1)):
result.append(list1[i].upper())
return result
Note that, indentation is very important in Python. If the code is inside the function, that code should have a space before to let Python know that those lines are inside the function. In 5 lines of code above, 4 lines of code inside the upper_char function, so should have space. It is also applied when you write the for loop, or if...else...
-In the code above, I defined a function called upper_char and passed the list1 argument in (you can name whatever you like but avoid putting the name the same as the Python keyword in green color).
- I created an empty list called result equals an empty square bracket. Python understands that I just initialized an empty result list.
- Same as the Image 4 above, I used the for loop to iterate to each element in my list1. Inside the for loop, I used the append() function to add each uppercase element in the list1 into my empty result list which I created before. You can use some functions with lists in Python from W3Schools here.
- Finally, I returned the result list.
That way is easy to understand but there are many lines of code.
There is a shorter way:
def upper_char2(list1):
return [x.upper() for x in list1]
I also defined a function called upper_char2 and passed the list1 in. Then, I only need to return 1 line. To express that line by word, I would say: "Return a list where each element x in list 1 is uppercase". It is shorter than the previous way very much. The result (a list) of both ways is the same (Image 5). One important thing that I mentioned above is the data type. In this case, I returned a String list. In Tableau, I will call SCRIPT_STR in the Calculated Field.
Now, my function satisfies the condition that the input and output of the function are a list and I also know the data type of the returned list. I will define more functions to test in Tableau later (Image 6). After defining a function, I always test with sample data to make sure the function works as I expected.
2/ Store the Python file and set the Python Path
In this part, I will share how to save the Python file (.py) from Jupyter Notebook and set the Python Path to run in Tableau.
- Save Python file (.py)
In Jupyter Notebook, I edited the name of the file. In Image 7, I named my file le_list_all_functions. Then, I go to File > Download as > Python (.py).
After downloading the Python file (.py), I right-click on the file and choose Open With. You can choose any Text Editor to open. On my computer, I open it by Sublime Text. After you open the file, edit the file to make sure only the function is in the Python file. I deleted all sample data (Image 8). Then, Ctrl+S to save the file.
- Set Python Path
In this step, we need to let TabPy know where our Python file is saved. Then, TabPy can use the function from that file. The author Oleksandr Golovatyi wrote a blog to show how to set Python Path here.
Note that, we need to set Python Path before accessing TabPy server in the Command Prompt.
Let's open the Command Prompt first. Then type the same as this format:
set PYTHONPATH=%PYTHONPATH%;<my-python-functions-folder>
In my case, <my-python-functions-folder> is C:\Users\LeLuu\Documents\tabpy. Then press Enter. In the new command line, I type tabpy and enter (Image 9); then press y.
Now, you are ready to open Tableau Desktop. However, if you don't want to set Python Path every time like that, there is another way.
- Deploy Functions
In case you don't want to Set Python Path, you can deploy the function directly from the IDE. I recommend reading the documentation page from TabPy first. But I can't call the function in the Calculated Field in Tableau. If you know how it works, please let me know.
3/ Call function from saved Python file and pass arguments into functions
If your Command Prompt looks like Image 10 below, then open Tableau Desktop.
I used Sample SuperStore in Tableau to demo. Before creating a new Calculated Field, let's check the connection from Tableau to TabPy first. In Tableau Desktop, go to Help > Setting and Performance > Manage Analytics Extension Connection... Then, make sure the Hostname and Port are correct. Click on Test Connection. If you see the Action Completed Pop-up and 200 GET code in the Command Prompt, then you successfully connected to TabPy from Tableau (Image 11).
- SCRIPT_STR: to demo this function, I will use the upper_char2(list1) function in my le_list_all_functions. I created a new Calculated Field called Upper Case SCRIPT_STR. Then, I type the same as Image 12. Note that, Tableau required the argument in the Aggregation function, so I put Sub-Category in Min function. Also, make sure the indentation when typing Python code and make sure the Python file and function name are correct.
- SCRIPT_BOOL: I want to check which sub-categories have positive profit and return Boolean type, so I called the pos_profit function (Image 13).
- SCRIPT_REAL: I want to calculate the final price of each sub-category after the discount and return a Real data type result, so I called the final_price function with SCRIPT_REAL (Image 14). You should be careful to put the argument in the order same as the order in the Python file.
Thank you so much for reading until now. I know this blog is very long, but I would like to show as many details as I can. In this blog, I introduced the list in Python, data types for input and output, IDE, and how to write Python functions. I also shared how to save the Python file and set the Python Path. In the final part, I demo how to call Python functions in Tableau Desktop. I only use some simple Python script to explain. You can look up the pandas and numpy library in Python for the advanced.
The advantage of using TabPy:
- Can reuse the function whenever you connect with the TabPy server.
- Save time when there are many built-in functions in Tableau. Don't need to make a complex workflow to prepare data.
- Increase the performance. If you work with a big dataset, then need many calculations in Tableau to prepare data. Now, only need to define functions in Python and use TabPy to call the function to return the result.
- Easy to share the Python file with other team members.
- Can work with many analysis types Principle Component Analysis (PCA), Sentiment Analysis, T-Test, ANOVA (Analysis of Variance).
The disadvantages of using TabPy:
- Take a long time to set up the environment.
- Need to have programming skills in Python.
- Hard to debug if there are errors in Tableau when using TabPy.
This is all information that I would like to share in this blog. I hope you enjoy my blog and follow me until this time.
See you soon in the next blog with more interesting things :)