Mathematical operation between rows based on conditions

I have a dataframe with four columns:
(This is a toy example, the real dataset has thousands of rows)

ID | Test | Value | Timepoint
1    A      0.5     Day4Hour-2
1    A      1       Day4Hour-1
1    A      234     Day4Hour2
1    A      22      Day4Hour4
1    A      672     Day4Hour8
1    A      345     Day4Hour16
1    B      2       Day4Hour-2
1    B      3       Day4Hour-1
1    B      252     Day4Hour2
1    B      234     Day4Hour4
1    B      13      Day4Hour8

I want to compute the mean for value between Day4Hour-2 and Day4Hour-1.

The trick is that there is only one couple of Day4Hour-2 and Day4Hour-1 for each combination of conditions in ID and Test.

That is, ID1 and TestA has only one Day4Hour-2 and one Day4Hour-1, etc (you can see it in the previos table)

In the end I would like to get something like this:

ID | Test | Value | Timepoint
1    A      0.75    Baseline
1    A      234     Day4Hour2
1    A      22      Day4Hour4
1    A      672     Day4Hour8
1    A      345     Day4Hour16
1    B      2.5     Baseline
1    B      252     Day4Hour2
1    B      234     Day4Hour4
1    B      13      Day4Hour8

Hi @RoyBatty296

See this wf: math_operation.knwf (58.2 KB)


gr. Hans

1 Like