topic Re: Filter by row, stack columns? in Discussions

Filter by row, stack columns?

student — Fri, 09 Jun 2023 19:45:26 GMT

I know how to make a subset table based on some criteria:

dt << Row Selection( Select where( (:Stat == "mean"), Current Selection( Extend )) );
dt << Select Columns( [10, 20] );
dt2 << Subset( Output Table( "test"), Selected Rows( 1 ), Selected columns( 1 ) );

But now I need to repeat the above for multiple criteria and stack the selection, i.e. the new table will have Column 10 as a fixed column, and then I keep adding column 20 with only the rows matching the criteria above. The number of rows will always be the same.

The "Stack" command does not work with selection, so am not sure what other commands can do that?

Re: Filter by row, stack columns?

txnelson — Tue, 27 Apr 2021 14:59:50 GMT

Here is an example of an easy way to delete the selected rows when using the Stack platform

names default to here(1);

// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << Row Selection( Select where( (:Sex=="F"), Current Selection( "Extend" )) );

// Stack data table
dtStack = dt << Stack(
	columns( :height, :weight ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dtStack << delete rows;

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 15:09:43 GMT

(Hit accept as solution by mistake)

Thanks but I don't want to delete selected rows. I can take care of that, but how would I append the selection when using Stack?

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 15:21:23 GMT

Here is how I thought it can be done (but it is not working):

my_list = {"F0","F1"};
stackcols = {};
For( i = 1, i <= N Items( my_list ), i++,
	dt << Row Selection( Select where( (:Freq==my_list[i]), Current Selection( "Extend" )) );
	dt << Select Columns( [19] );
	Insert Into( stackcols, dt )
);
dt2 << Subset( Output Table( "aa"), Selected Rows( 1 ), Selected columns( 1 ) );

Basically, I filter the active table according to column "Freq", first by the value "F0" and then by "F1". This is located in Column 19. I add that selection to the selection list called 'stackcols'. So after the loop I am expecting this to be a two columns-wide list. Then I want to make a table out of it (Called "aa"). But it is not working.

Re: Filter by row, stack columns?

txnelson — Tue, 27 Apr 2021 15:24:09 GMT

Ah.....you are correct about deleting the selected rows. But to delete the non selected, just use

dtStack << Invert Row Selection;

and then delete the rows.

To append multiple independent Stacked data tables, simply:

Before starting the repeating Stack instances, create an empty Base data table that will end up having all of the stacked tables concatenated to it
```
dtBase = New Table("Base");
```
Then for each of the stacked tables you create,
1. Delete the rows you do not want from the stacked data table
2. Concatenate the Stacked table to the Base table
```
dtBase << Concatenate( dtStacked );
```
3. Delete the Stacked table
```
Close( dtStacked, nosave );
```

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 15:36:11 GMT

Thanks, but it didn't work:

dt = current data table();
my_list = {"F0","F1"};
dtBase = New Table("Base");
dtStacked = {};
For( i = 1, i <= N Items( my_list ), i++,
	dt << Row Selection( Select where( (:Freq==my_list[i]), Current Selection( "Extend" )) );
	dt << Select Columns( [19] );
	Insert Into( dtStacked, dt );
	dtBase << Concatenate( dtStacked );
	Close( dtStacked, nosave );
);

dtBase is empty.

Re: Filter by row, stack columns?

Jeff_Perkinson — Tue, 27 Apr 2021 16:23:04 GMT

Insert Into() doesn't do what it appears you think it does.

In your code it would insert the data table reference dt into the dtstacked list repeatedly. It doesn't have anything to do with the selected rows or columns.

You'll need to create a new data table of the selected rows and columns using Subset() and insert that reference into dtstacked and then concatenate them.

Though, that doesn't seem necessary. Since you're just extending the selection each time through the loop at the very end you could do a subset of the selected rows and column and get the same result as your concatenated data table.

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 16:31:36 GMT

Thanks, that is almost what I want, except I need them stacked (column-wise) instead of concatenated (row-wise). That is, if selection column is L long, I want my new table to have same number of rows, but two columns (not one column of twice the length).

Re: Filter by row, stack columns?

txnelson — Tue, 27 Apr 2021 16:36:08 GMT

To not concatenate, but rather to add new columns then simply use the Update platform.

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 16:36:09 GMT

Here is a code that seems to work, manually. Now I will try to do it in a loop:

dt << Row Selection( Select where( (:Stat == "selection1"), Current Selection( Extend )) );
dt << Select Columns( [15, 19] );

dt << Row Selection( Select where( (:Stat == "selection2"), Current Selection( Extend )) );
dt << Select Columns( [15, 19] );
dt << Subset( ( Selected Columns ), Output Table Name( "Subset_1_2" ) );

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 16:43:13 GMT

No, the above code still concatenate. I'll try "update" command.

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 17:10:34 GMT

I apologize for the many posts. I am still stuck. Right now all what I have is this script that results in two tables, each having one column that I am interested in. I am able to manually copy and paste one column from table 1 to table 2, but can't do it automatically.

dt << Row Selection( Select where( (:Freq == "F0"), Current Selection( Extend )) );
dt << Select Columns( [15,19] );
dt << Subset( ( Selected Columns ), Output Table Name( "F0" ) );

dt << clear select;
dt << Row Selection( Select where( (:Freq == "F1"), Current Selection( Extend )) );
dt << Select Columns( [15,19] );
dt << Subset( ( Selected Columns ), Output Table Name( "F1" ) );

Re: Filter by row, stack columns?

txnelson — Tue, 27 Apr 2021 18:02:19 GMT

I suspect the issue is that the columns in both tables have the same names. Therefore, JMP will overwrite the values. Here is a simple example on how I would handle this. Now, given this type of Update, the two data tables will be lined up, row 1 with row 1.

That may result in the issue that you can not compare across columns, since the relationship between them does not exist.

dt << Row Selection( Select where( (:Freq == "F0"), Current Selection( Extend )) );
dt << Select Columns( [15,19] );
dt2 = dt << Subset( ( Selected Columns ), Output Table Name( "F0" ) );

dt << clear select;
dt << Row Selection( Select where( (:Freq == "F1"), Current Selection( Extend )) );
dt << Select Columns( [15,19] );
dt3 = dt << Subset( ( Selected Columns ), Output Table Name( "F1" ) );

// Rename the columns
column(dt3,1) <<set name((column(dt3,1) << get name) || " 2");
column(dt3,2) <<set name((column(dt3,2) << get name) || " 2");

// Merge the data tables
dt2 << update(with(dt3));

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 19:09:50 GMT

Thanks, that was correct! Only thing remaining is how to put it in a for loop. Here is my trial:

For(i=1, i<=NItems(freq_list), i++,
	dt << clear select;
	dt << Row Selection( Select where( (:Freq == (freq_list[i])), Current Selection( Extend )) );
	dt << Select Columns( [15,19] );
	dt << Subset( ( Selected Columns ), Output Table Name( freq_list[i] ) );
);

But this produces two identical tables even though I clear the selection at the start of each loop.

Re: Filter by row, stack columns?

txnelson — Tue, 27 Apr 2021 21:47:34 GMT

Rethinking of what I am guessing is your desired final table output, I suggest that you take a look at

Tables=>Split

The example below splits by the :Sex column, it would be your :Freq column, and the Split columns are Height and Weight, which would be your 15 and 19 columns.

The output creates pairs of columns for each of the Split By column values

Names Default To Here( 1 );
dt = open("$SAMPLE_DATA\big class.jmp");

// Split data table
// → Data Table( "Untitled 66" )
Data Table( "big class" ) << Split(
	Split By( :Sex ),
	Split( :height, :weight ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

Re: Filter by row, stack columns?

student — Tue, 27 Apr 2021 23:03:41 GMT

That is actually what I wanted, thanks again!