I have a csv that I have imported that has a column that is a semi-colon separated list (e.g. "foo ; bar")
I’m doing some cleanup on it, like removing content in parentheses. My problem is that I can’t figure out how to expand this into a choice list.
Here’s my current code:
import re
analytics = $Analytics
analytics_clean = re.sub(r'\(.*?\)', '', analytics)
analytics_list = analytics_clean.split(' ; ')
analytics_list = [item.strip() for item in analytics_list]
analytics_list
But when I convert this to a choice list, it doesn’t know how to interpret the values.
Hi @Jared_Scheel.
Try replacing the semi-colons with commas, and then converting the column to a Choice list.
George
hi @georgegevoian, thanks for the reply. I tried that, but it still doesn’t appear to pick up the values.
Had to break this up, because I couldn’t put multiple images in one message (new user).
Thanks for the screenshots.
Just to confirm: was the formula splitting the choices before, but they all appeared as invalid choices? I tried your formula with some sample data containing semi-colons and it expanded it correctly, but since no choices were configured, they all appeared as invalid.
One way to fix that is to round-trip the column from formula to data, and then back to formula. You can convert it to a data column by clicking the Formula Column dropdown under Column Behavior, and then clicking “Convert column to data”. You’ll then need to convert it from a Choice List to Text, and then back to a Choice List, which should cause it to auto-detect the individual choices in the data. Finally, you can set the column type back to being a formula (or keep it as a data column if it doesn’t need to be a formula). It’s a bit cumbersome, but a simpler way doesn’t come to mind.
George
Ah ok, that makes sense I think. So, I just fixed it by switching the type back to text, then applying my transformation to the columns directly, then switching to choice list. Thank you!
1 Like