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.
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 Variable to Table Column — NodePit
The output should give you a list of variables row0 … row11 with the old names
Now add a Column Rename node Column Rename — NodePit 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).
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
