Previous and next non-missing values in a column

[#1]

I’m fairly new to JMP, and I’ve come across a problem with which I’m really struggling. I use JMP Pro 17. I have a data sheet with one column with distance from a starting point (“Distance”) and one column with a signal intensity value at that distance (“Signal”). I’ve found signal local maxima (peaks) by making a formula column which yields a 1 if the signal value at that distance is the largest value within a given range (Signal[row] == max(Signal[row-n, row+n]), else missing. My problem, however, is the following; I wish to make another formula column which gives the distance at each peak to the closest peak.

In other words, I wish to, if “Peak?” == 1, find the distance value at the previous and the next rows where “Peak?” == 1, find the absolute value of the difference between the distance at the current row and the distance at those rows, and yield the minimum of those two values.

So far, I’ve made a “helper column” which if the row contains a local signal maximum gives the distance at that value. In other words, I have a column with the distance values at peak signal values, and missing values in all the rows which aren’t peaks. However, I cannot find a way to define by formula “previous non-missing value” or “next non-missing value”. I would be extremely grateful for any advice, either on how to make a formula for this, or if there are better ways to solve the actual problem of finding minimum distance between peaks. 

[#2 · Accepted solution]

First idea that came to my mind so it does look fairly complicated

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("Peaks", Numeric, "Continuous", 
		Set Values([., 1, ., ., 2.5, ., ., ., ., 3.1, ., ., 2])
	)
);


dt << New Column("DistanceToPeak", Numeric, Continuous, Formula(
	As Constant(m = :Peaks << get values;
		peak_rows = Loc(m);
		peak_values = m[peak_rows];
	);
	
	If(!IsMissing(:Peaks),
		peak_distances = peak_rows - Row();
		
		previous_peak = Loc(peak_distances < 0);
		If(N Items(previous_peak) == 0,
			previous_distance = .;
		,
			previous_distance = Abs(m[Row()] - peak_values[Reverse(previous_peak)[1]]);
		);
		
		next_peak = Loc(peak_distances > 0);
		If(N Items(next_peak) == 0,
			next_distance = .;
		,
			next_distance = Abs(m[Row()] - peak_values[next_peak[1]]);
		);
		Min(previous_distance, next_distance);
	,
		.
	);
));

[#3]

Thank you so much!! It works beautifully. I'll do my best to go through and try to understand your formula.

If I may, I'd like to ask an additional, and rather related, question to you or anyone else reading here.

Next, I'm finding the minimum distance between peaks in two channels. I.e., I have Distance, Signal 1 and Signal 2, I've found the peaks in each, and thanks to the fantastic help of jthi I can now find the minimum inter-peak distances of Signal 1 and Signal 2. Is there a formula I could use to at the peaks of Signal 1 find the distance to the nearest peak of Signal 2? In other words, if using the attached image in jthi's reply above, add a column "Peaks2", and use a formula to if "Peaks" is non-missing, find the minimum of the absolute values of the difference between "Peaks" and the previous and next non-missing values of "Peaks2"?

[#4]

Similar idea should still work

View more...

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("Peak1", Numeric, "Continuous", 
		Set Values([., 1, ., ., 2.5, ., ., ., ., 3.1, ., ., 2])
	),
	New Column("Peak2", Numeric, "Continuous", 
		Set Values([0.5, 0.9, ., 2, ., 4, ., ., ., ., ., 5, .])
	)	
);


dt << New Column("Peak1ToPeak1", Numeric, Continuous, Formula(
	As Constant(m = :Peak1 << get values;
		peak_rows = Loc(m);
		peak_values = m[peak_rows];
	);
	
	If(!IsMissing(:Peak1),
		peak_distances = peak_rows - Row();
		
		previous_peak = Loc(peak_distances < 0);
		If(N Items(previous_peak) == 0,
			previous_distance = .;
		,
			previous_distance = Abs(m[Row()] - peak_values[Reverse(previous_peak)[1]]);
		);
		
		next_peak = Loc(peak_distances > 0);
		If(N Items(next_peak) == 0,
			next_distance = .;
		,
			next_distance = Abs(m[Row()] - peak_values[next_peak[1]]);
		);
		Min(previous_distance, next_distance);
	,
		.
	);
));

dt << New Column("Peak1ToPeak2", Numeric, Continuous, Formula(
	As Constant(m = :Peak2 << get values;
		peak_rows = Loc(m);
		peak_values = m[peak_rows];
	);
	
	If(!IsMissing(:Peak1),
		peak_distances = peak_rows - Row();
		
		previous_peak = Loc(peak_distances < 0);
		If(N Items(previous_peak) == 0,
			previous_distance = .;
		,
			previous_distance = Abs(:Peak1[Row()] - peak_values[Reverse(previous_peak)[1]]);
		);
		
		next_peak = Loc(peak_distances > 0);
		If(N Items(next_peak) == 0,
			next_distance = .;
		,
			next_distance = Abs(:Peak1[Row()] - peak_values[next_peak[1]]);
		);
		Min(previous_distance, next_distance);
	,
		.
	);
));

Depending how you will be using this data + how comfortable you are with programming, I would maybe consider creating few functions as those can make the code easier to understand (such as "find earlier row", "find next row", "find nearest row",...).

[#5]

Thank you so much, again! Works just as beautifully. And I'm starting to get an understanding of your code.

Thanks for the advice. Like I mentioned, I'm quite new to JMP, and also to programming in general. But I'll certainly keep it in mind, especially as I get more experience. Thanks again, wishing you all the best!