topic Re: JSL select rows with certain string and make subset in Discussions

JSL select rows with certain string and make subset

achid03 — Fri, 09 Jun 2023 19:51:36 GMT

Hi All,

I want to select certain rows that have string ended with "452" in column "Sample_ID" and make a subset containing these rows. I try to do like below but it didn't work

Names Default To Here( 1 );
Clear Log();
Clear Symbols();
Close All( Data Tables, NoSave );
Close All( Journals, NoSave );

// pick data table 1
dt1_dir = Pick Directory( "Select directory for data table 1" );
dt1_Name = Pick File( "Select data file 1", dt1_dir, {"JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, 0 );
Show( dt1_Name ); // show dt1_Name string in log
dt1 = Open( dt1_Name ); // reassign dt1 to data table from string dt1_Name
Show( dt1 ); // show data table dt1 in log to check

// pick data table 2
dt2_dir = Pick Directory( "Select directory for data table 2" );
dt2_Name = Pick File( "Select data file 2", dt2_dir, {"JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, 0 );
Show( dt2_Name );
dt2 = Open( dt2_Name );
Show( dt2 );

//concatenate two tables and concat_dt is the output table
concat_dt = dt1 << Concatenate( dt2, Output Table( "concat data table" ), Create Source Column );
Show( concat_dt ); //show concat_dt data table in log
Close( dt1, NoSave );
Close( dt2, NoSave ); //close both dt1 and dt2


//select specific sample_ID from concat_dt and make subset out of it
prep_dt = concat_dt << Subset(
	Row( concat_dt << get rows where( Regex( :Sample_ID, "71MT052800452" ) == :Sample_ID ) ),
	selected columns only( 0 ),

);

Close( concat_dt ); // close concat_dt

Re: JSL select rows with certain string and make subset

txnelson — Sun, 04 Jul 2021 12:50:34 GMT

Try this for your row selection

Row( concat_dt << get rows where( Right( :Sample_ID, 3 ) == "452" ) ),

Re: JSL select rows with certain string and make subset

achid03 — Mon, 05 Jul 2021 00:09:21 GMT

I tried it but it didn't work. I got this message in log:

Re: JSL select rows with certain string and make subset

txnelson — Mon, 05 Jul 2021 03:13:24 GMT

It appears to not to be able to find the data table called "concat data table".
Make sure that table has been created before you specify the data table function.

It would also be useful if you provided examples of your input data tables.

Re: JSL select rows with certain string and make subset

achid03 — Mon, 05 Jul 2021 07:25:44 GMT

the data table "concat data table" looks like this.

the table is already open after concatenating

Re: JSL select rows with certain string and make subset

txnelson — Mon, 05 Jul 2021 07:42:55 GMT

Here is a working example

names default to here(1);
// Create the concat data table
concat_dt = New Table( "concat data table",
	Add Rows( 4 ),
	New Column( "Source Table",
		Character,
		"Nominal",
		Set Values( {"A", "A", "B", "B"} )
	),
	New Column( "Sample_ID",
		Character,
		"Nominal",
		Set Values(
			{"71MT052800452", "71MT052800193", "71MT052800452", "71MT052800120"}
		),
		Set Display Width( 120 )
	),
	New Column( "X_loc",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [-34005, -32288, -30571, -21986] )
	),
	New Column( "Y_Loc",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [-26500, 26500, -26500, 26500] )
	),
	New Column( "Z_Loc",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [83.2, -20.3, 14.5, -78.3] )
	)
);

// Create the subset data

prep_dt = concat_dt << Subset(
	Rows( concat_dt << get rows where( Right( :Sample_ID, 3 ) == "452" ) ),
	selected columns only( 0 ), 
);

Close( concat_dt ); // close concat_dt