How to find the difference between rows by a group with missing data?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!
Automation and ScriptingBasic Data Analysis and ModelingContent OrganizationData AccessData Blending and CleanupRe: How to find the difference between rows by a group with missing data?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=""></SKIP>
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?This is exactly what I was looking for! Thank you so much I've been trying for hours to figure this out.
Solution accepted.
