Tabulate all numeric columns in a table

[#1]

Hi,

I'm trying to creat a tabulate table that cill contain all numeric columns (and then another one with the categorical ones).

I tryed running this but it doesn't work.

Any suggestion?

TIA

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
	NumericCols = {};
	for(i = 1, i <= N Cols(dt), i++,
		Col = Column(dt,i);
		ColDataType = Col << Get Data Type; 
		If( ColDataType == "Numeric",
			Insert Into(NumericCols,Col << Get Name);
			);
		);


Tabulate(	
	Set Format( Uniform Format( 10, 2 ) ),
	Add Table(
		Column Table( Statistics( N ) ),
		Column Table( Statistics( Mean ) ),
		Column Table( Statistics( Std Dev ) ),
		Column Table( Statistics( Min ) ),
		Column Table( Statistics( Max ) ),
		Column Table( Statistics( Median ) ),
		Row Table(
			Analysis Columns(
				NumericCols = {}
			)
		)
	)
)

[#2 · Accepted solution]

Here is a rewrite of your code that uses a simpler way to get the numeric column names, and then adds the Eval() function around the list of numeric columns, to make it work

.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );
NumericCols = dt << get column names( numeric );

dt << Tabulate(
	Set Format( Uniform Format( 10, 2 ) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N ) ),
		Column Table( Statistics( Mean, Std Dev ) ),
		Column Table( Statistics( Min, Max ) ),
		Column Table( Statistics( Median ) ),
		Row Table( Analysis Columns( Eval( Numericcols ) ) )
	)
);

[#3]

Hi,

Thanks a lot!

I see now that the syntax of a categorical column is totaly different.

How would you do this if you want a tabulate table with the following: 

Set Format( Name( "% of Total" )(9, 0) ),
	Add Table(
		Column Table( Statistics( N, Name( "% of Total" ) ) ),

[#4]

The Tabulate syntax is NOT totally different, in fact it is identical except for the  words "Analysis Columns" changed to "Grouping Columns".   

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );
CharacterCols = dt << get column names( Character );

dt << Tabulate(
	Set Format( Name( "% of Total" )(9, 0) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N, Name( "% of Total" ) ) ),
		Row Table( Grouping Columns( eval( CharacterCols ) ) )
	)
);

I would like to know, what made you think the syntax was drastically different?  I am assuming that you are interactively running a sample Tabulate for the Analysis Columns, and then having JMP provide you with the JSL for the sample you ran, and then repeating the same for the Character Columns.  That would have shown you almost identical JSL.  I would like to help you figure out, what brought you to the conclusion you made, so I can help you make creating scripts as easy as possible.

Oh....and you need to read the Scripting Guide.....there is no substitute for reading it.

     Help==>JMP Documentation Library...……..Scripting Guide

[#5]

Thanks for the reply, but the code you posted does not provide me with the table i need.

here is the code, for the data file examole you used, which would give me the table i need.

You will note the output is different.

Tabulate(
	Add Table(
		Column Table( Statistics( N, Name( "% of Total" ) ) ),
		Row Table( Grouping Columns( :lot_id ) ),
		Row Table( Grouping Columns( :wafer ) ),
		Row Table( Grouping Columns( :Wafer ID in lot ID ) ),
		Row Table( Grouping Columns( :SITE ) )
	)
);

The reason I assumed there is a difference is since here each row table is stated seperately, but for in the numerical data they were all under the same

"Row Table(Analysis Columns("

and then a list of the column names seperated by a comma.

This led me to think that for the numbers I could use a simple list but not for the charecter.

Thanks for the reference to the Scripting Guide I defenetly should find the time to study it!

[#6]

The problem is not with the Tabulate code.  The Tabulate code I provided you will work just fine.  The issue is with the setting of the columns to be in the tabulate.  In the last code that I sent you, the statement

CharacterCols = dt << get column names( character );

put into the CharacterCols list, all of the columns from the data table that were of Data Type Character.  And the Tabulate ran correctly with the columns Lot_id, Wafer ID in Lot ID.  Those are the only 2 columns in the data table that are of Character Data Type.  It was my misunderstanding that your request for Classification variables being included in the second Tabulate, meant, Character columns.  But with your correction that Classification needs to include Wafer and Site in the Tabulate, requires that the CharacterCols list of columns needs to be generated differently.  What will cover all of the columns that you want, is to load that list with all of the columns with the modeling type of "Nominal".  Therefore, a simple change of the statement to

CharacterCols = dt << get column names( nominal );

Will place into the CharacterCols list,  {lot_id, wafer, Wafer ID in lot ID, SITE};

The modified JSL is below

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );
CharacterCols = dt << get column names( nominal );


dt << Tabulate(
	Set Format( Name( "% of Total" )(9, 0) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N, Name( "% of Total" ) ) ),
		Row Table( Grouping Columns( eval( CharacterCols ) ) )
	)
);

[#7]

Thanks again for bearing in with me. The second code you sent still does not give the results I need.

Please see below the results I am trying to get which is the outcome of the code i posted in previous post, followed by the results I get from your code.

This is what I would like to get:

This is what I get using the code posted:

TIA

[#8 · Accepted solution]

Here is one method to do what you want

dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );
CharacterCols = dt << get column names( nominal );

// Build a string variable that contains the required JSL
theExpr = 
"dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N, Name( \!"% of Total\!" ) ) ),";
		For( i = 1, i <= N Items( CharacterCols ), i++,
		theExpr = theExpr ||
			"Row Table( Grouping Columns( " || char(CharacterCols[i]) || ") ),";
		);
	theExpr = theExpr || "));";
// Execute the JSL
eval(parse(theExpr));

Other Community members may have a better method

[#9 · Accepted solution]

PS.

I rewrote this code to work on continuous columns instead of numeric. This suits my needs better, since I have several columns which are in fact nominal but have numerical data and Value labels.

Here is the final code I used.

Names Default To Here( 1 );
dt = currentdatatable();
continuousCols /*NumericCols */ = dt << get column names( continuous /*numeric*/  );

dt << Tabulate(
	Set Format( Uniform Format( 10, 2 ) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N ) ),
		Column Table( Statistics( Mean, Std Dev ) ),
		Column Table( Statistics( Min, Max ) ),
		Column Table( Statistics( Median ) ),
		Row Table( Analysis Columns( Eval( continuousCols /*NumericCols */ ) ) )
	)
);

Thanks a lot for your help!

[#10]

// Here is how I would do it

Names Default To Here( 1 );
dt = Current Data Table();
continuousCols/*NumericCols */ = dt << get column names( continuous/*numeric*/  );
exCols = dt << Get Excluded Columns(string);

For( i = Length( continuousCols ), i >= 1, i--,
	If( Contains( exCols, char(continuousCols[i]) ),
		Remove From( continuousCols, i, 1 )
	)
);

dt << Tabulate(
	Set Format( Uniform Format( 10, 2 ) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( N ) ),
		Column Table( Statistics( Mean, Std Dev ) ),
		Column Table( Statistics( Min, Max ) ),
		Column Table( Statistics( Median ) ),
		Row Table( Analysis Columns( Eval( continuousCols/*NumericCols */ ) ) )
	)
);