864605_en-US

liliconcom

Calculations between rows based on conditions

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    

 

Pass	850 nm	250 nm	Cuvette	Output
1	100	200	11	100/500 = 0,2
2	400	500	11	400/400 = 1
3	300	100	11	300 / 900 = 0.33
1	700	500	12	NA
2	200	400	12	NA
3	400	900	12	NA
1	400	800	13	NA
2	500	700	13	NA
3	300	700	13	NA

Automation and ScriptingBasic Data Analysis and ModelingRe: Calculations between rows based on conditions

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.

Re: Calculations between rows based on conditions

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

Re: Calculations between rows based on conditions

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"] :\</img> 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).