Compute operations between columns

I have a dataframe with hundreds of columns.
Just for example purposes I’m going to present a toy dataframe.

   ID   |  Var1Baseline  |  Var2Baseline   |  Var1_1h  |   Var2_1h
   1    |       100      |   150           |   150     |   100
   2    |       100      |   50            |   125     |   100
   3    |       80       |   80            |   100     |   100

As you can see, I have multiple variables (var1, var2…) at multiple timepoints (baseline, 1h, 2h)
What I want to do is to obtain the difference between each of the timepoints and the baseline (delta) for all the variables.
So I would have something like this.

   ID   |  Var1Baseline  |  Var2Baseline   |  Var1_1h_delta  |   Var2_1h_delta
   1    |       100      |   150           |   50            |   -50
   2    |       100      |   50            |   25            |    50
   3    |       80       |   120           |   20            |   -20

I thought about unpivoting for baseline and then applying math formula like in the image attached, but it doesn`t work, as the columns remain with their value:

Hi again,

quick question, is your 3rd line in the result table correct? I think you want the Baseline to be unchanged, but the last entry of Var2Baseline is different.

Now for the solution, do you remember what I initially had in mind for your previous question? I think you’ll need the “Group Column Loop Mechanism” here. Link:

Since it’s late, I’ll just describe how it would be done. I can do a workflow tomorrow.

  1. Extract Column Names, exclude non-baseline columns, string manipulation to get the Filter-Needle
  2. Table Row to Variable Loop Start with the table containing the filter needles
  3. Column Filter. Haystack is the original table. Enable RegEx/Wildcards
  4. Math Formula (Multi Column): include all non-baseline columns and do “CurrentCol - Baseline” via RegEx (is that possible? not sure); don’t forget to replace the columns, don’t append (keeps the names)
  5. Column Append Loop End
  6. rename the non-baseline columns to get the proper names
  7. Column Resorter

Sweet Dreams, T

edit: is “Column Group Loop” actually the correct name? not a native english speaker …

Thank you @Thyme , yes, there is an error, the var2baseline in the third row is supposed to be 120, so the delta is -20, but this just an example, but I think the idea was clear.

However I’m not sure I’m following you, if you could give post the workflow that would be great :slight_smile:

Yeah, the idea was clear. Two days ago we had a question were what looked like a mistake was actually intended because the algorithm was more complex, so I was making sure :slight_smile:

Here’s the workflow with the Column Group Loop mechanism . I changed the end a little bit to keep the ID column. If the column renaming and resorting doesn’t work with the actual table, you could also:

  1. Rename them directly after the Loop End
  2. Extract the column names
  3. Get Var number and time from them, then sort by number and time
  4. Reference Column Sorter and Column Append with ID

group_column_loop.knwf (76.5 KB)

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.