How to make a new column with experiment number in a data table with stacked repeating continuous data?

[#1]

Hi,

I have a data table with repeating continuous experiments. So the data is already stacked. I have a column where each new experiment starts with 1 then 2 and so on until last measurement. I want to create a new column with experiment number, so every time the other column starts with 1 that is the start of another experiment. 

I have tried writing this script with help from AI but it does not work. Hope somebody can help me on this.

dt = Current Data Table();

// Column used to identify new experiments
experimentIdentifier = Column(dt, "Column1"); // It is Column1 that contains numbers from 1 to 100 repeating over and over. 

// Create a new column for sample numbers
dt << New Column("SampleNumber", Numeric);

// Initialize variables
currentExperiment = experimentIdentifier[1];
sampleNumber = 1;

// Iterate over each row using explicit row indexing
For(i = 1, i <= N Row(dt), i++,
If(experimentIdentifier[i] != currentExperiment,
// New experiment starts, reset sample number
currentExperiment = experimentIdentifier[i];
sampleNumber = 1;
,
// Otherwise, increment sample number
sampleNumber++;
);

// Assign sample number to the new column
dt:SampleNumber[i] = sampleNumber;
);

[#2]

Create a new column and set this as the formula:

Col Cumulative Sum( 1, :Column 1 )

Column 1 being the column that contains the measurement numbers.

Brain fart.  

[#3]

Length( Loc( :Column 1[1 :: Row()], 1 ) )

That formula works.

[#4]

You can use Col Cumulative Sum(), but it's not as pretty.

Col Cumulative Sum( If( :Column 1 == 1, :Column 1, 0 ) )

[#5]

I don't understand how to use this in a script. 

I have also tried this, but so far all rows in my new column have a value of 1. Can you see what is not working?

dt = current data Table () << New Column("Experiment No",
Numeric,
Formula(
If( Row() == 1, 1, // Initialize experiment number as 1 for the first row
If( :Point No. == 1 & Lag(:Point No.) != 1, Lag(:Experiment No) + 1, // Increment experiment number if "Point No." resets to 1
Lag(:Experiment No, 1) // Otherwise, keep the same experiment number
)
)
)
);

[#6]

Hi @WhiteCow2000 ,

I had a crack at this by taking the position where '1' appears, marking each of them with an increasing number, then copying the Fill > Replace Missing with Previous Value option that can be done manually in JMP. I added a window for you to select your column that is to be measured to make it more flexible.

Names Default To Here( 1 );
dt = current data Table ();
dt << Clear Column Selection();
//This will work to find the instance where the lowest number (1 in this case) appears in the row order, if the rows are sorted in any way then it will adapt to that so be careful.
//Window to select the column with repeats
nw = New Window( "Launch Dialog",
    <<Modal,
    V List Box( Align( "right" ),
        H List Box(
            Panel Box( "Select Columns",
            //Filter for continuous data only
                clb = Filter Col Selector( dt, All,<<continuous(1), <<ordinal(0), <<nominal(0))
            ),
            Panel Box( "Pick Experiment Repeat Column",
                Lineup Box( N Col( 2 ), Spacing( 5 ),
                    Button Box( "Columns",
                        clbY << Append( clb << Get Selected )
                    ),
                    clbY = Col List Box(
                        "Numeric",
                        MinItems( 1 ),
                        MaxItems( 1 ),
                        nlines( 5 )
                    ),
                    Button Box( "Remove",
                        clbY << Remove Selected
                    )
                )
            )
        ),
        H List Box(
            Button Box( "OK",
            (
                cols = clby << Get Items( "Column Reference" );
                 run;);
            ),
            Button Box( "Cancel" )
        )
    )
);

//Expression that runs the steps to mark out the rows
run=expr(
//Add a column to track experiment number
dt<<new column("Experiment No","Character");

//Find the points where "1" appears in the selected column
val=dt<<Get rows where (cols == 1);


//Mark each instance where 1 appears with an iteration from 1, 2, 3....
For(i=1, i<(N items(val)+1), i++,
dt:Experiment No[(val[i])] = i;);

//JSL Version of 'Fill Replace missing values...' Example taken from https://community.jmp.com/t5/Discussions/Fill-empty-cells-with-last-valid-value/m-p/30782#U30782
For( i = 2, i <= N Rows( dt ), i++,
	If( Is Missing( :Experiment No[i] ),
		:Experiment No[i] = :Experiment No[i - 1]
	)
);
);

The simple version of the script where you would type the exact column names in JSL is here:

Names Default To Here( 1 );
dt = current data Table ();

//This will work to find the instance where the lowest number (1 in this case) appears in the row order, if the rows are sorted in any way
//Window to select the column with repeats

//Add a column to track experiment number
dt<<new column("Experiment No","Character");

//Find the points where "1" appears
val=dt<<Get rows where (:Column 1 == 1);
For(i=1, i<(N items(val)+1), i++,
dt:Column 2[(val[i])] = i;);

//JSL Version of 'Fill Replace missing values...' Example taken from https://community.jmp.com/t5/Discussions/Fill-empty-cells-with-last-valid-value/m-p/30782#U30782
For( i = 2, i <= N Rows( dt ), i++,
	If( Is Missing( :Experiment No[i] ),
		:Experiment No[i] = :Experiment No[i - 1]
	)
);

[#7 · Accepted solution]

AI is pretty bad with JSL.  Applying my solution is straightforward.  Also, I tried your last formula, and it worked fine.

dt = New Table( New Column( "Point No.", Set Values( Repeat( 1 :: 100, 4 ) ) ) );
dt << New Column( "Experiment No", Numeric, Formula( Length( Loc( :Point No.[1 :: Row()], 1 ) ) ) );
dt << New Column( "Experiment No", Numeric, Formula( Col Cumulative Sum( If( :Point No. == 1, :Point No., 1, 0 ) ) ) );
dt << New Column( "Experiment No",
	Numeric,
	Formula(
		If( Row() == 1,
			1, // Initialize experiment number as 1 for the first row
			If( :Point No. == 1 & Lag(:Point No.) != 1,
				Lag( :Experiment No ) + 1, // Increment experiment number if "Point No." resets to 1
				Lag( :Experiment No, 1 ) // Otherwise, keep the same experiment number
			)
		)
	)
);

[#8]

Thank you so much for all your help! It works now with both If formula and the Length formula!

[#9]

Returning to this thread a few weeks later to share a nice solution to a similar problem, which I think might work better with a simpler formula: 

Solved: How to get new column to populate cycles in large data set for multiple cycles o... - JMP Us...