Compute operations between columns

[RoyBatty296]

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:

[Thyme]

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:

Compute for column values based on conditional substrings in the column names

It’s funny that you ask, after posting I had this idea on how to generalise my solution and I went to the “kitchen” right away. Here it is: split into mean and kind columns unpivot everything into a single column, no extras calc mean for each group transpose reunite columns (resulting table has empty rows) This is not what I had in mind, however. Story thyme: While reading your question, I had this spark in my head: Group Loop Start. Except that it groups rows, not columns. I would have to e…

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 …

[RoyBatty296]

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

[Thyme]

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

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)