Summary of categorical levels

klk

I want to get a summary of the values of a column, using another as a "By" group.  It's like what Summary() does with N Categories, but I want a list of the actual values, not just the number of them.  For example take this table:

I want something like:

 

I'm thinking I could do something with using the 'Category' as a subgroup in Summary(), then Recode and Combine Columns()?  Any better or more clever ideas?  I poked around the community a bit and couldn't find anything analgous (suprisingly?)  Maybe a place for use of Associative Arrays?  This example table is tiny, the table I will be running this on could be a few million rows with hundreds of "levels" for the Group column.

txnelson

I think the way to start is to do a split on the data table, and then to work from there.  Below is a simple script that I wrote to get you the results you want.  If your need is to do this interactively, just follow the steps in the script

Names Default To Here( 1 );

// Create the beginning table
dt = New Table( "Example",
	Add Rows( 11 ),
	New Column( "Group",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 1, 2, 3, 4, 5, 5, 5, 6, 7, 8] )
	),
	New Column( "Category",
		Character,
		"Nominal",
		Set Values( {"A", "A", "B", "A", "D", "X", "Y", "Z", "A", "B", "C"} )
	)
);

// Change the group column to character so
// split columns are blanks and not missing values
dt:Group << data type( character );

// Split the columns
dtSplit = dt << Split( Split By( :Category ), Split( :Group ), Group( :Group ), Sort by Column Property );

// Change the data type back
dt:Group << data type( numeric );
dtSplit:Group << data type( numeric );

// Convert all of the non blank entries to the value of the column name
For( i = 2, i <= N Cols( dtSplit ), i++,
	theRows = dtSplit << get rows where( As Column( dtsplit, i ) != "" );
	If( N Rows( theRows ) > 0,
		Column( dtSplit, i )[theRows] = Column( dtsplit, i ) << get name
	);
);

// get all of the character column names
namesList = dtSplit << get column names( character, string );

// Create the combined column
dtSplit << Combine Columns(
	delimiter( "," ),
	Columns( :a, :b, :c, :d, :X, :y, :z ),
	Column Name( "Values (Category)" )
);

// Count the number of combined values
dtSplit << New Column( "N Categories (Category)",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		count = 1;
		While( Word( count, :Name( "Values (Category)" ), "," ) != "", count++ );
		count = count - 1;
	)
);

// get rid of the formula
dtSplit:Name("N Categories (Category)") << delete property(formula);

// delete the unnecessary columns
dtSplit << delete columns( namesList );