Splitting data from column containing multiple pipe-separated values

Hi!

I have a file with user preferences that are stored in a column separated with pipes. So for each user in the table there is a column that contains a string that could look lite any of these examples (depending on what they have chosen):
apple | banana | lemon
apple
banana
banana | lemon
mango
lemon
apple | lemon
lemon | mango | watermelon

Now, I want to use the data in this column to determine the number of users that have flagged interest in a specific fruit.

First I tried to use Cell Splitter to get the value into separate columns but that didn’t work because I didn’t get unique values in each columns (since the order of the source data is not fixed - i.e. the first value in the pipe separated list could be apples but it could also be bananas as illustrated in the example above).

My goal is to be able to filter out all rows that matches a specific interest and plot the count of each one of them in a single visual bar chart. There is probably an easy solution to this but I can’t really see it at the moment. Any help or pointers are greatly appreciated!

Thanks!

Hi! In the Cell Splitter, you can select “output as List” to avoid the result being split into separate columns. You can then filter the fruits with a Row Filter. Select “Filter based on collection elements” in the Row Filter config window.

Edit: if you want to count all the fruits, you probably don’t want the Row Filter. Instead, after using the Cell Splitter to create a List as above, apply the Ungroup node to the fruit column and use a Value Counter on the result.

5 Likes

Hi Aswin!
Thank you very much! The Cell Splitter (“output as List”) in combination with the Ungroup node put me on the right track!

Thanks a million!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.