Calculations between rows based on conditions

Question

Hey, I'm new to JMP scripting, but somewhat used to python language. I'm trying to achieve the following with my dummy test table below:

1) Cycle through all rows (both before and after the row) and do calculations only on those rows where Cuvette == 11, remaining rows where Cuvette != 11 the "Output" can be set to NA or whatever.

2) The calcuation itself is between two separate rows. In rows with Cuvette == 11, the 850 nm cell of the row has to be divided with the 250 nm cell from the row that meets two conditions: 
                   a) Cuvette == 12
                   b) The Pass value has to match up between the rows, e.g. Pass value for cuvette 11 == Pass value for Cuvette 12

Pass850 nm250 nmCuvetteOutput110020011100/500 = 0,2240050011400/400 = 1330010011300 / 900 = 0.33170050012NA220040012NA340090012NA140080013NA250070013NA330070013NA

jthi · Answer

Here is one matrix option, but using JMP features is generally easier to understand Names Default To Here(1); dt = Current Data Table(); cuvette_start = 11; cuvette_end = 12; cuvettes = dt[0, "Cuvette"]; // Data is sorted -> we can just take values and make comparison start_idx = Loc(cuvettes, cuvette_start); end_idx = Loc(cuvettes, cuvette_end); // differences output = J(1, N Rows(dt), .); output[start_idx] = dt[start_idx, "850 nm"] :\ dt[end_idx, "250 nm"]; dt << new column("R", Numeric, Continuous, Values(output)); If you can rely on the order heavily, you can use formula like this If(:Cuvette == 11, :"850 nm"n / :"250 nm"n[Col Min(If(:Cuvette == 12, Row(), .)) + Row() - 1]; ); The type of final formula will heavily depend on your real use case (is data sorted "properly", are you looking for more than one value comparison and so on).

txnelson · Answer

Welcome to the Community.

Here is an example of how I would approach your problem

Names Default To Here( 1 );
dt = Current Data Table();

// Separate out the Cuvette==12 rows into a separate data table
// and then join the Pass==Pass rows to the Cuvette==11 rows
// Then calculate the Output column

// Select the Cuvette==12 rows
dt << Select Where( :Cuvette == 12 );

// Create a new subsetted data table with those rows
dt12 = dt << Subset( Selected Rows( 1 ), columns( :Pass, :"250 nm"n ) );

// Change 250 nm column name so when joined back to original table
// it will have a unique column name
dt12:"250 nm"n << Set Name( "Divisor" );

// For alignment reasons, add a new column called Cuvette and set
// it equal to 11
dt12 << New Column( "Cuvette", Set Each Value( 11 ) );

// Join the tables using the Update Platform
dt << Update(
	With( dt12 ),
	Match Columns( :Pass = :Pass, :Cuvette = :Cuvette )
);

// Create the new Output column
dt << New Column( "Output", Set Each Value( :"850 nm"n / :Divisor ) );

// Remove Divisor column since it is no longer needed
dt << delete columns( Divisor );

// Close the subsetted data table since it is no longer needed
Close( dt12, nosave );

// Remove the Selected RowStates from the data table
dt << clear rowstates;

Below is a more direct approach, but a far more inefficient method, which yields the same results.  On a large data table this method will be quite slow

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Output",
	Formula(
		foundRow = [];
		If( :Cuvette == 11,
			curPass = :Pass;
			foundRow = Current Data Table() <<
			get rows where( :Cuvette == 12 & :Pass == curPass );
			If( N Items( foundRow ) > 0,
				:"850 nm"n / :"250 nm"n[foundRow[1]]
			);
		);
	)
);

jthi · Answer

To do this in JMP like fashion, you generally would split your data and then perform the calculation (JMP is Not a Spreadsheet), then join it back or continue with the split data.

But you can use also JSL or Formula to do this calculation, but first consider the splitting option.