Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Tabulate all numeric columns in a table

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 24, 2020 10:41 AM
(1488 views)

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 = {}
)
)
)
)
```

3 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 ) ) )
)
);
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

8 REPLIES 8

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 ) ) )
)
);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Tabulate all numeric columns in a table

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" ) ) ),
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Tabulate all numeric columns in a table

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

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Tabulate all numeric columns in a table

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Tabulate all numeric columns in a table

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 ) ) )
)
);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Tabulate all numeric columns in a table

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

Article Labels

There are no labels assigned to this post.