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:
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:
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
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.
Extract Column Names, exclude non-baseline columns, string manipulation to get the Filter-Needle
Table Row to Variable Loop Start with the table containing the filter needles
Column Filter. Haystack is the original table. Enable RegEx/Wildcards
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)
Column Append Loop End
rename the non-baseline columns to get the proper names
Sweet Dreams, T
edit: is “Column Group Loop” actually the correct name? not a native english speaker …
@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
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:
Rename them directly after the Loop End
Extract the column names
Get Var number and time from them, then sort by number and time
Reference Column Sorter and Column Append with ID
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.