topic Re: JMP script to create sequence column based on group from another source column in Discussions

JMP script to create sequence column based on group from another source column

thedellette — Fri, 09 Jun 2023 19:51:28 GMT

I have a large data set where the cycle changes over time. I would like to create a column that has an index by 1 at the start of each cycle and continues to count up until the next cycle starts then the counts starts back at 0. I have tried using the count and sequence functions shown below but I run into problems when my second cycle has more values that my first cycle (see diagram below where the cycle observation is the value I want the formula to produce and Column 109 is what is produced by the script.)

dt << New Column("Cycle Observation", Numeric, Formula (If(
:Cycle == 1, Sequence( 0, 1080, 1 ),
:Cycle == 2, Sequence( 0, 1439, 1 ),
:Cycle == 3, Sequence( 0, 360, 1 ),
:Cycle == 4, Sequence( 0, 360, 1 ),
:Cycle == 5, Sequence( 0, 360, 1 ),
:Cycle == 6, Sequence( 0, 180, 1 ),
:Cycle == 7, Sequence( 0, 180, 1 ),
:Cycle == 8, Sequence( 0, 180, 1 ),
:Cycle == 9, Sequence( 0, 180, 1 ),
:Cycle == 10, Sequence( 0, 180, 1 ),
Sequence( 0, 180, 1 )
)));

Re: JMP script to create sequence column based on group from another source column

txnelson — Fri, 02 Jul 2021 15:40:53 GMT

There are several previous Discussion entries concerning this topic, but since it is an easy solution, I have created another example for your specific situation.

Attached is a data table with a formula column that calculates the values desired

The formula is

If( Row() == 1,
	value = 1,
	If( :Cycle != Lag( :Cycle ),
		value = 1,
		value
		++)
);
value;

While my solution works fine, @jthi solution below is a better solution.

Re: JMP script to create sequence column based on group from another source column

jthi — Fri, 02 Jul 2021 07:32:16 GMT

As @txnelson said there are lots of solutions in the community for similar problem.

You can use Lag(), but also Col formulas can be helpful in cases like these:

Col Cumulative Sum(1, :Cycle)

If you need to shift it by 1 you can add -1 to the formula after Col Cumulative Sum().

Not necessarily helpful in this particular case, but very helpful video by @brady_brady which will most likely be useful at some point:

Special Formula Columns

Re: JMP script to create sequence column based on group from another source column

thedellette — Fri, 02 Jul 2021 15:19:06 GMT

Thank you very much this is very helpful information and the Col Cumulative Sum() is a very elegant way to do it.