nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Subset scripting

viralnerd2013

Hello JMP users,

I am able to generate scripts to subset large data files, but then have to manually name these and run them all individually through a second and third subset script. Is there a way to iteratively run the subset command and save the files to include the subset definitions?

For example: Subset file "Colors" by column1 (red, blue, green) to produce and save files: Colors.red.jmp, Colors.blue.jmp, Colors.green.jmp and then subset these by column8 (bright, light, dark) to create and save files

Colors.red.bright.jmp, Colors.red.light.jmp, Colors.red.dark.jmp, Colors.blue.bright.jmp, Colors.blue.light.jmp, Colors.blue.dark.jmp, Colors.green.bright.jmp, Colors.green.light.jmp, Colors.green.dark.jmp.

The columns positions would vary.

Thank you !!!

Accepted answers

At least In JMP 10 there is a "Subset by" checkbox. If selected, subsets for every level of selected column(s) are automatically generated. It is easy to implement that function in JSL if the the number of by-columns is known. However, to generalize this was more tricky than I imagined.

This approach, based on inserting the sequentially generated subsets in lists (within lists) seems to work though:

//Example Table

dt = New Table( "Color",

Add Rows( 12 ),

New Column( "Color",

Character,

Nominal,

Set Values( {"red", "red", "red", "red", "blue", "blue", "blue", "blue", "blue", "green", "green", "green"} )

New Column( "Intensity",

Character,

Nominal,

Set Values(

{"bright", "bright", "dark", "light", "light", "light", "light", "dark", "dark", "dark", "light", "bright"}

)

New Column( "Finish",

Character,

Nominal,

Set Values(

{"Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank"}

)

New Column( "Colensity", Character, Nominal )

);

For Each Row( :Colensity = Color || Intensity || Finish ); // Just for checking that it's working...

// End example table

// Script starts here

cols = {Color, Intensity, Finish}; // Any nr of subset-by-columns (in hierarchical order)

nt = N Table(); //number of currently open tables

subList = Eval List( {dt << subset( by( cols[1] ) )} ); //First-level subsets

//Make lower-level subsets recursively

For( i = 2, i <= N Items( cols ), i++,

Insert Into( subList, Eval List( {Eval( subList[i - 1] ) << subset( by( cols[i] ) )} ) )

);

// name and save the all subsets

myPath = "path"; //change to desired path

For( i = 1, i <= N Table() - nt, i++,

tv = Data Table( i ) << get table variable names; // The levels are stored as table variables

subname = {};

For( j = 1, j <= N Items( tv ), j++,

Insert Into( subname, Data Table( i ) << get table variable( tv[j] ) )

);

Insert Into( subname, dt << get name );

Reverse Into( subname );

Data Table( i ) << save( myPath || Concat Items( subname, "." ) );

);

// End Script //close all(data tables);

All comments

pmroz

I have an application where I need to subset a large dataset in several different ways, and run the same analysis on each subset. Rather than create separate datasets, I use a combination of SELECT and EXCLUDE to make the data in the large dataset ready for analysis. For example:

dt = New Table( "Color Example",

Add Rows( 12 ),

New Column( "Color", Character, Nominal,

Set Values(

{"red", "red", "red", "red", "blue", "blue", "blue", "blue", "blue",

"green", "green", "green"} ) ),

New Column( "Intensity", Character, Nominal,

Set Values(

{"bright", "bright", "dark", "light", "light", "light", "light", "dark",

"dark", "dark", "light", "bright"} ) ) );

nr = nrows(dt);

color_list = {"red", "blue", "green"};

intensity_list = {"bright", "light", "dark"};

for (i = 1, i <= nitems(color_list), i++,

one_color = color_list[i];

for (k = 1, k <= nitems(intensity_list), k++,

one_intensity = intensity_list[i];

// Exclude all rows not matching this color and intensity

dt << select where(:Color == one_color & :Intensity == one_intensity)

<< invert row selection << Exclude;

nselected = nr - nrows(dt << Get Selected rows);

dt << clear select;

if (nselected > 0,

// Do your analysis on dt here. Only the non-excluded rows will be used.

// The only command I've found that ignores excluded rows is Tables >> Summary

print("# rows: " || char(nselected) || " color: " || one_color ||

" intensity: " || one_intensity);

);

// Clean up for the next selection

dt << clear row states;

);

viralnerd2013

Thank you PMroz,

The problem is that I do need to keep the intermediate files and the example I gave was made up, the column names would change each time.

Thanks,

Eric

This approach, based on inserting the sequentially generated subsets in lists (within lists) seems to work though:

//Example Table

dt = New Table( "Color",

Add Rows( 12 ),

New Column( "Color",

Character,

Nominal,

Set Values( {"red", "red", "red", "red", "blue", "blue", "blue", "blue", "blue", "green", "green", "green"} )

New Column( "Intensity",

Character,

Nominal,

Set Values(

{"bright", "bright", "dark", "light", "light", "light", "light", "dark", "dark", "dark", "light", "bright"}

)

New Column( "Finish",

Character,

Nominal,

Set Values(

{"Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank", "Matte", "Blank"}

)

New Column( "Colensity", Character, Nominal )

);

For Each Row( :Colensity = Color || Intensity || Finish ); // Just for checking that it's working...

// End example table

// Script starts here

cols = {Color, Intensity, Finish}; // Any nr of subset-by-columns (in hierarchical order)

nt = N Table(); //number of currently open tables

subList = Eval List( {dt << subset( by( cols[1] ) )} ); //First-level subsets

//Make lower-level subsets recursively

For( i = 2, i <= N Items( cols ), i++,

Insert Into( subList, Eval List( {Eval( subList[i - 1] ) << subset( by( cols[i] ) )} ) )

);

// name and save the all subsets

myPath = "path"; //change to desired path

For( i = 1, i <= N Table() - nt, i++,

tv = Data Table( i ) << get table variable names; // The levels are stored as table variables

subname = {};

For( j = 1, j <= N Items( tv ), j++,

Insert Into( subname, Data Table( i ) << get table variable( tv[j] ) )

);

Insert Into( subname, dt << get name );

Reverse Into( subname );

Data Table( i ) << save( myPath || Concat Items( subname, "." ) );

);

// End Script //close all(data tables);

viralnerd2013

Hello MS,

That was very close! It created all of the subsets, but did not save any of the tables. I got this message: Unable to save file. Unknown data target.

I am using JMP 9 so I am wondering if there is a difference in a command for creating the file or am I doing something incorrectly? I put the correct path into the script.

Thanks,

Eric