topic Re: How to apply a script on subsets of a datatable in Discussions

How to apply a script on subsets of a datatable

cbaril — Sat, 10 Jun 2023 23:59:23 GMT

Hello,

In my datatable I have 6 data subsets (distinguished one another by Column X where each subset has a different letter "a", "b", "c", "d", "e" and "f"). How could I apply a script to all 6 subsets independently and get the 6 results of my script in one column?

Thank you in advance for your support!

Best

Claire

------

Script currently applied to entire datatable:

dt = Current Data Table();

dt << New Column("sumtempj",
	Numeric,
	Continuous,
	<<Set Each Value(
		As Constant(
			ts = :"Rounded TempJ" << get values;
			f_row = Min(Loc(ts, 121));
			t_idx = Index(f_row, f_row + 359);
			sumtempj = Sum(ts[t_idx]) - (360 * 121);
		);
		If(
			Row() == f_row, sumtempj,
			, .
		);
	)
);

Re: How to apply a script on subsets of a datatable

jthi — Wed, 22 Mar 2023 09:33:23 GMT

Just based on the script you have here, I would add index column, create subsets based on the unique letters, run formula on each of those and then update data back to original table

Re: How to apply a script on subsets of a datatable

cbaril — Wed, 22 Mar 2023 13:21:54 GMT

Hi Jarmo,

Thanks for the tip!

I'm only a beginner in JSL and now have this script (see below). The result column "sumtempj" appears in each sub datatable produced but there is no result in the column. Can you see the mistake?

Best

Claire

Script:

dt = Current Data Table();

// Perform the subsets
listDT = dt << Subset(
	By( :BBI and SteriRun ),
	All rows,
	Selected columns only( 0 ),
	columns( :RoundedTempJ, :BBI and SteriRun, :Sterilisation duration )
);

// Loop across all new data tables and run sumtempj
For( i = 1, i <= N Items( listDT ), i++,
	listDT[i] << New Column( "sumtempj",
		Numeric,
		Continuous,
		<<Set Each Value(

			As Constant(
				ts = :Rounded TempJ << get values;
				f_row = Min( Loc( ts, 121 ) );
				t_idx = Index( f_row, f_row + 359 );
				sumtempj = Sum( ts[t_idx] ) - (360 * 121);
			);
			If(
				Row() == f_row, sumtempj,
				, .
			);
		)
	)
);

Re: How to apply a script on subsets of a datatable

jthi — Wed, 22 Mar 2023 14:07:58 GMT

There might be other ways of doing this, but as I don't know your data I cannot really suggest anything. Here is one solution which uses subsets which could work

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

// add indexing to original data
dt << New Column("IDX", Numeric, Continuous, << Set Each Value(Row()));
// add data column to table as empty
new_col = dt << New Column("sumtempj", Numeric, Continuous);
col_name = new_col << get name;
// get unique groups to uniq_vals
Summarize(dt, uniq_vals_str = by(:age));

// in this case we have to convert uniq_vals back to numeric values BUT it isn't always necessary
// (summarize changes them to strings)
uniq_vals = Transform Each({uniq_val_str}, uniq_vals_str, num(uniq_val_str));

// loop over those unique values 
For Each({uniq_val}, uniq_vals,
	// create subset
	uniq_val_rows = dt << Get Rows Where(:age == uniq_val);
	dt_temp = dt << Subset(rows(uniq_val_rows), output table(char(uniq_val)), invisible);
	
	// perform calculation
	Column(dt_temp, col_name) << Set Each Value(
		Col Max(:height);
	);
	// update values
	dt << Update(
		With(dt_temp),
		Match Columns(:IDX = :IDX)
	);
	// wait is here for demo purposes
	wait(1);
	close(dt_temp, no save);
);

dt << Delete Columns("idx");

Re: How to apply a script on subsets of a datatable

cbaril — Wed, 22 Mar 2023 14:58:50 GMT

This is a bit too advanced for me, but thank you for the inputs! I managed to find a way to get the result without too much manual handling and for now it's good enough.

Best

Claire