topic Re: Conditional formatting by row state of different columns. in Discussions

Conditional formatting by row state of different columns.

Frogger — Wed, 28 Jun 2023 18:55:55 GMT

Good Day,

I have 1.7mil rows of data that are linked with common identifiers. I can format smaller portions of it it in Excel but with the size of the file (greater than the excel limit) and the need to reproduce similar data sets I would like to figure out how to code the data in JMP.

---

Column A (FID) is a series of fish identification numbers (1,2,3…) with a variable number of reads which populate Column B (Time). Every read for Column B is an interval of 20 seconds.

Column B start at various reads >28000 as a result of the previous time stamp they were associated with. I want the first read of each FID to start at 0 then increase by 20 for every row associated with that FID. So C2 will equal 0 sec., C3 will be 20 sec., C4 will be 40 sec., and so on until we get to FID =2 then it will reset and start counting it at 0, 20, 40, 60.

In Excel, as seen below, I can set the first row of a new column to "0" then apply the formula: =IF(A4=A3,C3+20,0)

When I attempt to apply similar logic to the conditional formula in JMP I cannot figure out how to have it recognize the value of each row and adjust. Likely a fundamental difference between data handling packages like Excel and statistical packages like JMP, but JMP is my preferred software for analysis and the size of the data files requires it. I am using Version 16, JMP.

Any thoughts are appreciated. An excerpt of the data are attached for practice purposes.

Re: Conditional formatting by row state of different columns.

txnelson — Wed, 28 Jun 2023 19:23:34 GMT

The formula to use is

Col Cumulative Sum( 20, :FID ) - 20

The subtraction of 20 is because the Col Cumulative Sum will start at 20

Re: Conditional formatting by row state of different columns.

GregF_JMP — Wed, 28 Jun 2023 19:26:51 GMT

Hello,

It appears that you are starting with a sorted table, so this should go swimmingly. You will be able to do this with a column formula for a column we will call "New_Time", but realize that unlike excel, where the first row could manually be set to zero, JMP requires all formulas within a column to be the same. The Lag function will be useful in this case and will behave like an Excel relative reference to the row above.

The graphic image from the formula editor for a column called "New_Time" is shown below.

This is described:

If the current row is == 1, OR if the current value of FID is different than the previous lag(FID,1), then the "time" of the current row is zero (reset). Else, add 20 to the prior value of the column New_Time (the cell above).

I have also included the JSL, that can be copied into the formula editor with a double click to JSL mode.

If( Row() == 1 | :FID != Lag( :FID, 1 ),
	0,
	Lag( :New_Time, 1 ) + 20
)

Notice that JMP comparison operators are double equals (vs excel single), and not equals in JMP is != (vs excel <>).

Good luck with this data preparation step, hope that the column formula enables all of the downstream analysis.

-G

Re: Conditional formatting by row state of different columns.

Frogger — Wed, 28 Jun 2023 20:41:48 GMT

@GregF_JMP & @txnelson Thank you both for the prompt support and insight.

I have it working now and are progressing with the analysis. I was stuck on this for four hours. Really appreciate your help.

Greg, your pun of "downstream analysis" goes noted and appreciated since these are fish data... :)