Additional functions for summary table via JSL

[#1]

I would like to know how I can obtain summary tables but with additional statistics.

For example, Mode() is a function that one can use in a formula, but this does not work:

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

dt << Summary( Group( :Age ),
             subgroup( :sex ),
             Mode( :Height ));  // Mode isntead of mean

I would also like to summarize the table by getting the last or first number of the group.

[#2]

FWIW (and if you know a little JSL), you can do this kind of thing:

Names Default To Here( 1 );

// Sample data
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// Invisible Distribution with 'By' group(s)
dist = dt << Distribution(Continuous Distribution(Column( :height ),Customize Summary Statistics(Mode(1))), By(:age, :sex), Invisible);

// Get the Summary Stats
dt2 = Report(dist[1])[TableBox(2)] << makeCombinedDataTable;
Report(dist[1]) << closeWindow;

// Retain only the Mode
dt3 = dt2 << Subset(Rows(dt2 << getRowsWhere(:Column 1 == "Mode")));
dt3 << setName("Mode Values");
Column(dt3, "Column 1") << setName("Statistic");
Column(dt3, "Column 2") << setName("Value");
Close(dt2, NoSave);

I would also consider the JMP Wish List.

[#3]

Nice trick using the distributions platform as a way to obtain these.

I have added the request here. Extending summary table functions to any defined function (e.g. mode, kurt, skew, etc.) 

[#4]

I was testing this approach and it is not feasible, I am afraid. 

JMP takes too long to use show distributions when regrouping many items in a big table (half a million rows and hundreds of columns).

Contrarily, default functions in the summary table are calculated very fast.

[#5]

You can make summary tables (or any tables function ) much faster using by settingtheir visibility  - private, invisible, visible. when i worked with very large files and only needed to extract the modes i used private.

[#6]

hi @FN ,

there must be many ways to get what you are asking for even though Mode is not an option in the table summary. You can ask for the Mode in the additional summary statistics table in the distribution platform manually or by clicking as @ian_jmp suggests. Otherwise, try using the  add in by brady_brady :

Extended-Summary-Statistics-Add-in-for-JMP which refferes the mode from the menu.

alternatively, try the following script:

Names Default To Here( 1 );

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


// get frequencies for modes 
dt2 = dt << Summary(
	Group( :Age, :sex, :height ),
	Link to original data table( 0 )

);

// delete all rows that are not modes
dt2 << select where( Or( :N Rows != Col Max( :N Rows, :age, :sex ), :N Rows == 1 ) );
dt2 << delete rows;

// indicate first and last values per group - Not min and max unless table is first sorted!!!
dt << New Column( "id age", Numeric, "Continuous", Format( "Best", 12 ), Formula( Sum( :age[Index( 1, Row() )] == :age ) ), eval formula, );
dt:id age << delete formula;

// remove all rows that are not first or last
dt << select where( And( :id age != Col Max( :id age, :age ), :id age != 1 ) );
dt << delete rows;

// create one table with all data and sort it
dt3 = dt << concatenate( dt2, Create source column );
dt3 << New Column( "Value",
	Character,
	"Ordinal",
	Formula( If( :id age == 1, "First", :id age > 1, "Last", :N Rows > 1, "Mode" ) ),
	Set Property( "Value Order", {Custom Order( {"First", "Mode", "Last"} ), Common Order( 0 )} ),

);

dt3 << sort( By( :age, :sex, :Value ), Order( Ascending ), replace table );

please notice that in the case of Big class, not all groups have a clear mode. my approach was to remove it the highest frequency was equal to 1.

please let us know if it works or what needs improvement.

Ron