Icon

Find variable column and row position

Lower part of flow is used to append the file name to theresultant data using Cross Joiner. This is purely fordocumentary/identification purposes and not strictlynecessary, depending on use case Find the ROW containing the "plates" using regex, and create two variables. The first isthe row number with the "plates" title in it. The second is that row number plus 13so that we can filter the file for just the title row and the following 12 data rows Having filtered out all but the required 13 rows from the table, turn the single data itemfor each of those rows into columns (cell splitter is set to split on tab "\t" but this mayvary if actualy data is different to this). Once we have the columns, use the first row asthe column names so that we can then locate the "plates" data NB... The ROW FILTER node, if configured manually to"filter by number", starts its row numbering at 1. It is notpossible to enter the value "0" as first row, so clearly thefirst row is number 1 in this context.However (strangely!) when passing the values asvariables, I found that passing 1 as the first row causes itto return the second row onwards. It seems that we have topass it a value starting as 0 for first row but only if passedas a flow variable! This is odd, and makes flow variablepassing inconsistent here with manual configurationwhich is not what I would expect. I intend to raise thisbehaviour on a separate post. See note below re rownumbering Read files from datafolderLoop through filesRead current fileFind row containing "plates"Return info from all filesReturn only the rows containing "plates" infoRow number +13(so we include title rowand 12 data rows)Get row number as variable "counter"Row numbers starting from 0to allow for "inconsistency" in "Row Filter"Turn those rowsinto individual columnsApply column headersTranspose to use as column name dictionarySplit out row 1(column headers)get file name as stringappend original file namefor identification purposesApply file name to all rowsInclude only "plates" columnStandardiserowid which may vary fromfile to fileand needs to be consistent forInsert Column Header List Files/Folders Table Row ToVariable Loop Start Line Reader Rule-basedRow Filter Loop End Row Filter Math Formula(Variable) Table Rowto Variable Counter Generation Cell Splitter Insert ColumnHeader Transpose Row Splitter Path to String(Variable) Variable toTable Row Cross Joiner Column Filter RowID Lower part of flow is used to append the file name to theresultant data using Cross Joiner. This is purely fordocumentary/identification purposes and not strictlynecessary, depending on use case Find the ROW containing the "plates" using regex, and create two variables. The first isthe row number with the "plates" title in it. The second is that row number plus 13so that we can filter the file for just the title row and the following 12 data rows Having filtered out all but the required 13 rows from the table, turn the single data itemfor each of those rows into columns (cell splitter is set to split on tab "\t" but this mayvary if actualy data is different to this). Once we have the columns, use the first row asthe column names so that we can then locate the "plates" data NB... The ROW FILTER node, if configured manually to"filter by number", starts its row numbering at 1. It is notpossible to enter the value "0" as first row, so clearly thefirst row is number 1 in this context.However (strangely!) when passing the values asvariables, I found that passing 1 as the first row causes itto return the second row onwards. It seems that we have topass it a value starting as 0 for first row but only if passedas a flow variable! This is odd, and makes flow variablepassing inconsistent here with manual configurationwhich is not what I would expect. I intend to raise thisbehaviour on a separate post. See note below re rownumbering Read files from datafolderLoop through filesRead current fileFind row containing "plates"Return info from all filesReturn only the rows containing "plates" infoRow number +13(so we include title rowand 12 data rows)Get row number as variable "counter"Row numbers starting from 0to allow for "inconsistency" in "Row Filter"Turn those rowsinto individual columnsApply column headersTranspose to use as column name dictionarySplit out row 1(column headers)get file name as stringappend original file namefor identification purposesApply file name to all rowsInclude only "plates" columnStandardiserowid which may vary fromfile to fileand needs to be consistent forInsert Column HeaderList Files/Folders Table Row ToVariable Loop Start Line Reader Rule-basedRow Filter Loop End Row Filter Math Formula(Variable) Table Rowto Variable Counter Generation Cell Splitter Insert ColumnHeader Transpose Row Splitter Path to String(Variable) Variable toTable Row Cross Joiner Column Filter RowID

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: brian
Created at: 2021-09-09
On NodePit since: 2022-02-06
Last update: 2024-04-27
Created with KNIME version: v4.4.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!