Subscribe Bookmark RSS Feed

Automatic Grouping Script

abdulj

Community Trekker

Joined:

Jul 12, 2011

Hello All

I need some help on what the best way would be to group multiple columns of the same table.

My table would have columns similar to the following:

Z 3 , X 1, Z 2, X 3, X 4, Y 1, X 2, Y 2, Z 1

I would like to group all the Xs, Ys and Zs together and in numeric order if possible. This is a condensed version of what I deal with which is usually on the order of hundreds of columns with varying group sizes required.

The easiest way I think would be to create a list based on the column names, then use the loc() feature to return the position of each similar column name. However, what would be the best way to use the group() command?

Any help on this problem is appreciated.

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

There are probably several ways to do this. Here is one idea that should work for any table where grouping is to be based on the first letter of the column names.

// Example table

names = {Z 3, X 1, Z 2, X 3, X 4, Y 1, X 2, Y 2, Z 1};

dt = New Table( "test" );

For( i = 1, i <= N Items( names ), i++,

  dt << New Column( Char( names[i] ), numeric )

);

// sort table columns and make list of first letter of column names

dt << reorder by name();

collist = dt << get column names( string );

For( i = 1, i <= N Col( dt ), i++,

  collist[i] = Left( collist[i], 1 )

);

// Make list of unique column name 1st letter

col_type = Associative Array( collist ) << get keys;

// Group columns

For( i = 1, i <= N Items( col_type ), i++,

  pos = Loc( collist, col_type[i] );

  dt << group columns( Column( pos[1] ), N Row( pos ) );

);

2 REPLIES
Solution

There are probably several ways to do this. Here is one idea that should work for any table where grouping is to be based on the first letter of the column names.

// Example table

names = {Z 3, X 1, Z 2, X 3, X 4, Y 1, X 2, Y 2, Z 1};

dt = New Table( "test" );

For( i = 1, i <= N Items( names ), i++,

  dt << New Column( Char( names[i] ), numeric )

);

// sort table columns and make list of first letter of column names

dt << reorder by name();

collist = dt << get column names( string );

For( i = 1, i <= N Col( dt ), i++,

  collist[i] = Left( collist[i], 1 )

);

// Make list of unique column name 1st letter

col_type = Associative Array( collist ) << get keys;

// Group columns

For( i = 1, i <= N Items( col_type ), i++,

  pos = Loc( collist, col_type[i] );

  dt << group columns( Column( pos[1] ), N Row( pos ) );

);

abdulj

Community Trekker

Joined:

Jul 12, 2011

Works great, thanks MS!

A follow up question... Is there any easy way to present the user with a list of all the groups, have them select one more groups from the list, then have the script create subset tables containing only the selected groups?