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

WhiteCow2000

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;
);

 

 

mmarchandFSLR

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<SPAN>&nbsp;& Lag(:Point No.) != 1</SPAN>,
				Lag( :Experiment No ) + 1, // Increment experiment number if "Point No." resets to 1
				Lag( :Experiment No, 1 ) // Otherwise, keep the same experiment number
			)
		)
	)
);