topic Re: How to find the difference between rows by a group with missing data? in Discussions

How to find the difference between rows by a group with missing data?

PruningTheBird9 — Tue, 30 Dec 2025 16:17:13 GMT

I'm running into this issue where I have two sets of datapoints across my timeline. One is collected at every point in the interval, the other is collected sporadically. When placed together in a data table, one of the columns is missing data every in the interval. I attached a data table as an example but essentially looks like this:

What I need now is to get the difference between rows that contain data in the Glucose (g/L) column, grouped by Bioreactor column. For e.g., at 36 hrs for Bioreactor 1, my value would be "-1" (3 - 4). For bioreactor 2 at 120 hrs, my value would be "1" (2 - 1).

Lag hasn't been working since it requires that the values are spaced apart similarly, which they are not. I would appreciate any help and/or guidance!

Re: How to find the difference between rows by a group with missing data?

jthi — Tue, 30 Dec 2025 17:27:44 GMT

As you seem to be using JMP19 Col At() might do what you are looking for (but I'm not 100% sure). You could try with formula like

:"Glucose (g/L)"n - Col At(:"Glucose (g/L)"n, -1, :Bioreactor, << relative(1), <<Skip Missing(:"Glucose (g/L)"n))

If this isn't what you are looking for, could you provide full "correct result" column? For example for Bioreactor 1 what would be the values at 0, 84, 132 and 180 hours? Or are you just interested in the first difference which can be calculated?

Re: How to find the difference between rows by a group with missing data?

PruningTheBird9 — Tue, 30 Dec 2025 17:55:31 GMT

This is exactly what I was looking for! Thank you so much I've been trying for hours to figure this out.

Solution accepted.