Search code examples
rdifferencespotfire

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]))