topic Subsetting a data table into several data tables by column value in Discussions

Subsetting a data table into several data tables by column value

Feli — Sat, 10 Jun 2023 23:27:55 GMT

Dear Community,

I want to split my data table into several smaller data tables that contain only the rows where a column has a certain value. This I want to do for all unique values of the column.

With the example of the Big Class data: I want to generate two data tables in which one has only the rows where sex= F and the other the rows where sex = M. They have to be two separate data tables.

With only a few value categories I could do it by hand by selecting/data filtering the rows and only subsetting the selected rows, but with over 50+ categories I am probably looking at a scripting option since point and click will take too long.

Any help? I guess I would need to find a way to loop over the unique values of my separation column and try to reproduce the subsetting?

Re: Subsetting a data table into several data tables by column value

txnelson — Mon, 29 Mar 2021 14:10:03 GMT

Tables=>Subset has a Subset By checkbox that when checked, allows for the creation of a new subset for each level of the column(s) selected in the subset selection box

Re: Subsetting a data table into several data tables by column value

Georg — Mon, 29 Mar 2021 17:15:04 GMT

In addition to @txnelson ,

when having so many tables, you may need to organize and change them in lists, see example.

Names Default To Here( 1 );

cdt = Open( "$SAMPLE_DATA\Big Class.jmp" );

tables = cdt << Subset(
	By( :age ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :age, :height, :weight )
);
Wait( 3 );

For( i = 1, i <= N Items( tables ), i++,
	write( "closing: ", tables[i] << get name , "\!n");
	wait(1);
	Close( tables[i], NoSave );
);

Re: Subsetting a data table into several data tables by column value

Feli — Tue, 30 Mar 2021 06:41:40 GMT

Wow. How did I not know that?? That's exactly what I wanted. Thanks, @txnelson!