I am working on a monthly update on my dataset that only shows data values for past 12 months. I currently manually rename the columns to NOVEMBER2020 to month 1, DECEMBER2020 to month 2, JANUARY2021 to month 3 & so on, but I would like to automate this based on the position of the column on the table, where I will rename the column headers from columns position 1-12 to Month1-Month12 at one go so that I do not have to go DECEMBER2020 as month 1, JANUARY2021 as month 2, FEBRUARY2021 as month 3, next month when I refresh my excel sheet.
Any idea how I can do this?
Welcome to the world of KNIME.
Here is a draft of a potential solution for your purpose.
- Create a table e.g. using the Table Creator Node https://nodepit.com/node/org.knime.base.node.io.tablecreator.TableCreator2NodeFactory
- Add both the old names in column 1 and the new names in column 2
NOVEMBER2020 | month 1
DECEMBER2020 | month 2
- Add a Variable to Table Column node https://nodepit.com/node/org.knime.base.node.flowvariable.appendvariabletotable2.AppendVariableToTable2NodeFactory
The output should give you a list of variables row0 … row11 with the old names
- Now add a Column Rename node https://nodepit.com/node/org.knime.base.node.preproc.rename.RenameNodeFactory and configure first manually the rename of the columns. Keep track of the sequence, you will need this in the next step
If you run this node it will give you a new table with the new names
- Now the last step keep it dynamic: In the config of the Column Rename node go to the flow variables tab. Here you should find 12 entries. Now assign the flow variables to each of the new column names.
That will allow you have the requested rename process automatically. In case of changes of the old column names you only need to change the entry table (or you calculate that table automatically).
and welcome to KNIME Community!
One way to do it is to utilize Extract Column Header node which can output same table with default column headers (Column 0, Column 1, Column 2… for example). Then all you have to do is create mapping table and connect it to Insert Column Header node to get your desired column headers. Check this example on KNIME Hub:
Also Extract Column Header node allows you to specify column prefix ("Column " is default) so if you change it to "Month " you’ll get Month 0, Month 1, Month 2 and so on. If months can start with zero that might be good enough as well
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.