KNOWLEDGE BASE

Creating a Word Cloud When Data is Formatted as Text Lists


Published: 23 May 2018
Last Modified Date: 05 Jun 2018

Question

How to create a word cloud when the data is structured as text, (or string), lists rather than a row for every possible value.

For example, making a word cloud of pet names, but each row in the underlying data lists all of the pets in one household as a comma separated list. Similar to the table below: 

                                         
Household IDPet Names
1Rocky, Doug, Spot
2Spot, Whiskers
3Rocky, Spot, Mittens
4Minnie, Mickey
5Spot
6Bella, Rosa
7Whiskers, Mittens, Snowball
8Snowball, Cloudy, Spot
9Spot
10Spot, Rocky
11Spot
12Spot, Rocky


 

Environment

  • Tableau Desktop
  • Tableau Prep (optional)

Answer

Option 1: Using Tableau Prep and Tableau Desktop

The attached "split then pivot prep flow.tfl" and "word cloud.twbx" demonstrate the following directions:
Step 1 - Clean the data in Tableau Prep
  1. Open Tableau Prep and connect to the original data source
  2. Hover over Original Data, click the plus, and select Add Step
  3. In Clean 1, right-click Pet Names and select Split Values > Custom Split…
  4. In the Custom Split dialog, do the following and click Split
    • Enter the separator, which is ,
    • For Split off, select All
  5. Hover over Clean 1, click the plus, and select Add Pivot
  6. Drag [Pet Name - Split 1], [Pet Name - Split 2], and [Pet Name - Split 3] to the Pivot1 Values column
  7. Hover over Pivot 1, click the plus, and select Add Output
  8. In Output, run the flow
Step 2 - Build the view in Tableau Desktop
  1. Connect to Output.tde in Tableau Desktop
  2. Drag [Pet Names Split] to Text on the Marks card
  3. Right-click and drag a second copy of [Pet Names Split] to Size on the Marks card
  4. In the Drop Field dialog, select CNT(Pet Names Split) and click OK
  5. In the dropdown on the Marks card, select Text

Option 2: Only using Tableau Desktop

Use the Tableau Desktop built-in custom split function to split the list of pet names, and create a duplicate of the data source for each split field. Then create a master pet name list, and join the master pet list to each duplicate data source on a different split field. The attached workbook uses sample data to demonstrate these instructions.
Step 1 - Split the strings
  1. Connect to the "Original Data" sheet of the attached Excel file
  2. In the Data Source tab, right-click [Pet Names] and select Custom Split…
  3. In the Custom Split dialog, do the following and click OK:
    1. For Use the separator, enter "," without quotes
    2. For Split off, select All
Step 2 - Create a duplicate of the data source for each split field
  1. Navigate to a worksheet
  2. Navigate to Data > Original Data (string to split) > Duplicate
  3. Repeat step 5
Step 3 - Join master pet list
  1. Connect to the "Master List" sheet of the attached Excel file
  2. Navigate to Data > Edit Relationships…
  3. In the Relationships dialog, so the following
    1. For Primary data source, select Master List (string to split)
    2. For Secondary data source, select Original Data (string to split)
    3. Select the Custom radio button
    4. Click Add…
    5. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 1, and click OK
    6. For Secondary data source, select Original Data (string to split) (copy)
    7. Select the Custom radio button
    8. Click Add…
    9. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 2, and click OK
    10. For Secondary data source, select Original Data (string to split) (copy 2)
    11. Select the Custom radio button
    12. Click Add…
    13. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 3, and click OK
Step 4 - Create a calculation and build the view
  1. Create a calculated field with a name like "Word Size" with a calculation similar to the following:
    	ZN(SUM([Original Data (string to split)].[Number of Records]) )
    	ZN(SUM([Original Data (string to split) (copy 2)].[Number of Records]))
    	ZN(SUM([Original Data (string to split) (copy)].[Number of Records]))
  2. Drag [Pet Names Master] to Text on the Marks card
  3. Drag [Word Size] to Size on the Marks card
  4. In the dropdown on the Marks card, select Text

Additional Information

Option 2 may cause performance issues due to having to duplicate the original data source. To reduce the impact, consider making a data source just for the word cloud view before duplicating the data source, and make that data source as small as possible.

The size of a data source can be reduced by removing any tables whose fields are not being used, and adding data source filters. If using an extract, click the Hide All Unused Fields button in the Extract Data dialog.
Did this article resolve the issue?