[SOLVED] Difference between two row values within the same column based on value in third column in R

Difference between two row values within the same column based on value in third column in R

[Expected Table][1]Thanks for looking into this post. Trying to find difference between the "Record" value based on baseline visit in R(for spotfire) For RecordId(10001), Change in Record from Baseline is 4 (Difference of 20 at baseline, 24 at screening) and so on.

RecordId Record    Visit           Change in Record from Baseline
10001     20       Baseline     
10001     24       Screening     4
10001     20       Visit 4       0
10001     15       Visit 5       5

Solution

I understand you need the change from baseline for each RecordId. I think this is doable using Spotfire calculated columns and expressions, so you don't have to involve an R script if you are ok with it.

First calculate an intermediate column [Baseline_Record] :

ValueForMax(Integer(Trim([Visit])='Baseline'),[Record]) over ([RecordId])

This is calculated for every RecordId.
Trim([Visit]) removes extra spaces, just in case.
Trim([Visit])='Baseline' will be True if the Visit is a Baseline,
False otherwise.
Integer(..) turns a logical True (False) into 1 (0). So the maximum
value corresponds to the Baseline.
ValueForMax(col1,col2) takes the value of col2 (in this case, Record) for the maximum value of col1.

So this will calculate a column that holds the Baseline record for every row.

Then to calculate the change you create another calculated column:

Abs([Record] - [Baseline_Record])

You could collapse everything into a single column, although not quite readable:

Abs([Record] - ValueForMax(Integer(Trim([Visit])='Baseline'),[Record]) over ([RecordId]))