Incrementing column names after Cell Splitter node

[davidbaker]

Hi folks,
I’ve used a Cell Splitter node to split a column that contained a comma delimited list. The output was as follows:

I need the output columns to ultimately be named:
Lith_Min1, Lith_Min2, Lith_Min3, etc.

I can get rid of the _Arr[ & ] parts using a Column Rename (Regex) node. That works fine. But how do I increment the numeric portion of the column name, such that it starts with 1 and not 0?

I’m trying to do this dynamically, to work for a variety of different column names, so I don’t want to just hardwire a Column Renamer node.

Alternatively, is it possible to set the Cell Splitter to start it’s column numbering at 1 instead of 0? That would be even better.

[Stephane73]

Hi @davidbaker

To achieve your desired output dynamically, here’s an approach you can try, which is also implemented in the attached workflow:

1. Pivot the Columns to Rows: Use the Unpivoting node to transform your columns (e.g., Lith_Min[0], Lith_Min[1], ...) into rows. This makes it easier to manipulate the column names as data.

2. Adjust the Column Index Dynamically:

• Use a Column Expression node to extract and modify the numeric portion of the column name.
• Apply a regular expression to split the text and numeric parts of the name.
• Increment the numeric part by 1 to start numbering from 1 instead of 0.

join(regexReplace(column("ColumnNames"),"(.*)\\[(.*)\\]","$1")," ",toInt(regexReplace(column("ColumnNames"),"(.*)\\[(.*)\\]","$2"))+1)

3. Pivot Rows Back to Columns: Use the Pivoting node to transform the rows back into columns, now with the adjusted column names.

This approach works dynamically for various column sets, and you don’t need to hardwire specific names.

The attached workflow demonstrates these steps in action. I hope this helps resolve your issue! Let me know if you have further questions.
Columns_Named.knwf (93.9 KB)

[davidbaker]

Great, thank you @Stephane73 ! Unpivoting the columns to data makes a lot of sense.