Ignoring "Excluded" rows

[#1]

Hello,

I'm still trying to get over the JSL "hump," so forgive the fundamental question.

The issue:

- I have a sample table that contains data sets for three replicates with sample suffix a1, a2, and a3.

- At times, one of the replicates needs to be set to the "Excluded" state (example below is a2)

- I'm writing a batch script that will open a table and apply calculations only sets that were not excluded.

- However, I'm still not there with my attempt (shown below) since data from all three are still being considered.

- I feel like an "and" statement may be necessary to  identify non-excluded rows, then applying search for suffixes. 

Any input, would be most appreciative.

Thanks Neil

states = Current Data Table() << Get Row States();
	For( i = 1, i <= N Rows( states ), i++,
		rs = As Row State( states[i] ); 
		if (Excluded(rs)==0, 
		
			a1 = dt << Get Rows Where(Contains(:Sample,"a1"));
			a2 = dt << Get Rows Where(Contains(:Sample,"a2"));
			a3 = dt << Get Rows Where(Contains(:Sample,"a3"));		
		);	
		
	)

[#2]

It looks like you are iterating over the list of excluded rows but then doing the same analysis every time, which doesn't actually reference the list of excluded rows anywhere.  Here is one way that might solve your problem, which gets matching rows and excluded rows, and then just removes excluded rows from the list.

Names default to here(1);

//Make a sample dataset with some excluded rows
dt = Open("$Sample_data/iris.jmp");

excluded rows to set = [ 4, 9, 52 ];
dt << Select Rows( excluded rows to set );
dt << Exclude(1);

//Find all matching rows and load in associative array, useful for Remove From function later
species matches = associative array(dt << Get Rows Where(:Species == "setosa"));

//get excluded rows
excluded rows = dt << get excluded rows();

//remove excluded rows from the list of rows matching the species
Remove From (species matches, excluded rows);

//get the matching rows as a list
speciesmatches << get keys;

[#3]

ih,

Many thanks for the input. I'm still a bit of a novice, but took a stab at the Associative Array concept. Code is shown and will work up to "Get Keys". However, I'm still unsure how to grab the information that I need from "speciesmatches" to make the needed calculations (last 6 lines listed). I took an image of a typical table that would contain excluded sets. 

Neil

dt = Open( path );
	
	// Find row that were excluded and load into list
	Exc_row = dt << Get Excluded Rows;	
	
	dt << Select Rows( Exc_row );
	dt << Exclude(1);
	
	
	//Find all matching rows and load in associative array, useful for Remove From function later
	species matches = associative array(dt << Get Rows Where(Contains(:Sample,"a1") |Contains(:Sample,"a2") |Contains(:Sample,"a3")));
	
	//get excluded rows
	Exc_row = dt << get excluded rows();
	
	//remove excluded rows from the list of rows matching the species
	Remove From (species matches, Exc_row);
	
	//get the matching rows as a list
	speciesmatches << get keys;
	
	// What needs to be done to get mean of three (or 2 sets in the case of exclusion see table image)
	a1 = dt << Get Rows Where(Contains(:Sample,"a1"));
	a2 = dt << Get Rows Where(Contains(:Sample,"a2"));
	a3 = dt << Get Rows Where(Contains(:Sample,"a3"));

    c1 = Column("Image A pore area (%)");
   mean1 = Mean(c1[a1[1]], c1[a2[1]], c1[a3[1]]);

[#4]

Here is an updated script that goes through each species in the iris data table and calculates the mean for the rows matching that criteria.  That said, now that I better understand what you are doing, I would likely go about this differently and use tabulate to find those values instead.

Names default to here(1);

//Make a sample dataset with some excluded rows
dt = Open("$Sample_data/iris.jmp");

excluded rows to set = [ 4, 9, 52 ];
dt << Select Rows( excluded rows to set );
dt << Exclude(1);

species list = {"setosa", "versicolor", "virginica"};

//For each species
for(s=1, s<= n items (species list), s++,

	//Find all matching rows and load in associative array, useful for Remove From function later
	species matches = associative array(dt << Get Rows Where(:Species == species list[s]));

	//get excluded rows
	excluded rows = dt << get excluded rows();

	//remove excluded rows from the list of rows matching the species
	Remove From (species matches, excluded rows);

	//get the matching rows as a list, use that to find which rows to average
	mn = mean( :Sepal length[speciesmatches << get keys] );
	
	//output averages to the log
	write(species list[s] || " mean = " || char(mn) || "\!n")
);

Option using tabulate:

Names default to here(1);

//Make a sample dataset with some excluded rows
dt = Open("$Sample_data/iris.jmp");

excluded rows to set = [ 4, 9, 52 ];
dt << Select Rows( excluded rows to set );
dt << Exclude(1);

//New column with the check for each value
New Column("Species_", Character, "Nominal", Formula(If(Contains(:Species, "se"), "se", Contains(:Species, "ve"), "ve", Contains(:Species, "vi"), "vi")));

//tabulate to find averages - script copied from 'source' of data table after selecting
//'make into data table' from tabulate platform, then inserted dt reference
dtTab = (dt << Tabulate(
	Set Format( Mean( :Sepal length( 10, 4 ) ) ),
	Add Table(
		Column Table( Analysis Columns( :Sepal length ), Statistics( Mean ) ),
		Row Table( Grouping Columns( :Species_ ) )
	)
)) << Make Into Data Table;

//Now get values from the table
dtTab:"Mean(Sepal length)"n[dtTab << Get Rows Where(:Species_ == "se")];

[#5]

Many thanks!

I'll give this a shot and see how it goes.

The original intent was just to take the porosity from one row for each set (a1, a2, and a3) and get the mean. The porosity is the same for each replicate, but couldn't find an easier way to extract just 3 distinct points and average. 

Again thanks for the input.

Neil

[#6]

Definitely take a look at both the tabulate platform and the summarize JSL function, I think both can get your answer fairly quickly.