If you read some of my last blog post you may notice that R got a new companion called Tableau. Tableau is an easy to use and mighty BI toolbox for visualizing all kinds of data and I suggest everybody to give it a trial. One of the things that I like very much is that it gives you all the options to create simple graphics instantly and on the other side assemble multifunctional and ”customized” dashboards by using table calculation, R scripts and other more advanced features. But nobody is perfect. Especially one thing about Tableaus handling of user specific color schemas/palettes bothers me a little bit. Right now, there is no easy way to save individual color palettes out of Tableau for reusing them also in other workbooks. The current blog post will show you, how this can be achieved, if you don’t fear executing a little python code:
Imagine that you have a discrete attribute, where you want to color the different labels regarding some specific schema. This is easy to do in Tableau Desktop. Just click right on the dimension, click “Default Properties” and “Color…” and assign the colors to the responding labels. Double-clicking on a labels gives you the option to enter the RGB code.
If the same attribute is used in different workbooks, you don’t want to repeat the process every time – especially if there are a lot of labels or the colors are not part of one of the build-in color palettes. For this Tableau offers the possibility to save color schemas in a user specific preference file (Preferences.tps). There is a well written KB article about how to store your palette as xml in the preference file. If you bring the preference file in place, you can select your individual palette when editing the color of an attribute.
For me this solution stops somewhere half the way. Imagine the common situation that you have assigned the colors manually in Tableau. Now you want to save your work, but there is no way to save it out of Tableau! Instead you have to click on every single label, memorize the color code and assemble a xml preference file piece by piece. I experienced saving manual created color schemas as a standard feature in other types of visualization systems for example geographical information systems (GIS).
Remark: Additionally Tableau requires you to transform all of your decimal RGB codes from Tableau Desktop to hexadecimal RCB codes if you want to put them into the preference file. Of course, this is no rocket science (using for example excel with its DEC2HEX function), but you may ask yourself if there shouldn’t be some better user experience. Please Tableau, can you just agree on one common format?
But here is help in form of a small python code that can extract the desired information. Here is, how it works:
You may have noticed that if you view a workbook *.twb file in your text editor, you see XML code that defines the whole workbook. If you used a customized coloring inside a workbook the information about that has to be part of this XML code. Therefore, it should be possible to extract the information using a script that traverses the workbook.
Because accessing the *.twb XML code directly is not supported by Tableau, there is no overall documentation. So any information about how things are stored need to be figured out in a trial-and-error manner. I recommend using versioning system like Git together with a text editor to track any change. Modify the workbook in tableau, save and inspect what’s changed with Git.
Going this way, you will find out how the color information is saved: As long as you stick with the default coloring there will be no info in the XML code, but when you change the default coloring an “encoding” tag will appear as part of the data source under which you find the list of colors.
Now here is the python script to extract these informations:
The structure is very simple – it receives parameter from the command line, opens the workbook, extract the color palette for every requested attribute and prints out the information so that it can be easily saved as preference file. Dependent on the given parameters the script will write the preference file automatically at the end.
Every color palette will be saved as categorical one. If you like to change it (for example to use it as sequential palette), you have to edit the “type” attribute with a text editor. Additionally behind every color the script will print printed the corresponding color as xml comment. This serves as a help to edit the file afterwards or create new palettes outside Tableau.
To run it, just type
python Extract_Color_Schema_TWB.py --s 'Color_and_Sort_Test.twb' 'Region' 'Region (Copy)'
to extract the color palette for the attributes “Region” and “Region (Copy)” from the workbook “Color_and_Sort_Test.twb”. The parameter “–s” will tell the script to save the results as “Preference.tps” in your current directory. You can copy this file directly to your Tableau repository and after restarting Tableau the new color palettes should be available.
I hope this will help other users sharing the same problem (if you want to help, you may vote for this feature idea/proposal in the Tableau community).
Please send me some feedback if the script isn’t working for you (together with the problematic workbook). As explained, I assembled the knowledge about how color palettes are stored by testing different cases. It is not so unlikely that I missed something ;-).
This time you can find the script here on github.