topic Summary with String_Aggregation in Discussions

Summary with String_Aggregation

TWE — Fri, 09 Jun 2023 22:08:28 GMT

HI,

How can I code a table which:

- grouped based on characters and

- build the corresponding cells with conact_items (e.g. separated with ,).

- like the SQL code "SELECT sex, String_Agg(Name,', ') as ConcatName FROM 'Big Class'" GROUP BY sex

I know this would be possible with looping but I was wondering if there is another and more elegant way.

Based on the "Big Class" table, the result should look like this:

sex ConcatName

f KATIE, LOUISE, ....
m TIM, JAMES, ...

Many thanks

Re: Summary with String_Aggregation

ih — Mon, 15 Mar 2021 18:12:09 GMT

It seems like the tabulate platform needs a concatenate summary function. Here is one way using a single loop on each row of a summary table:

Names default to here(1);

dt = Open("$Sample_data/big class.jmp");

//Table with each unique sex and count
dtSum = (dt << Tabulate(
	Add Table( Row Table( Grouping Columns( :sex ) ) )
)) << Make Into Data Table;

//blank column for concatenated names
dtSum << New Column("Names", Character, "Nominal");

//loop for each row/sex
for each row(dtSum,

	// Use Eval and eval expression to insert the sex of the current 
	// row into the where expression of the other table
	Eval(Eval Expr(
	
		//fill in the names one row at a time, joining all values together
		:Names = char( Concat Items(
			
			//get names from main table matching the sex of the summary table
			dt:name[ dt << Get Rows Where( dt:sex == Expr(:sex) ) ],
			","
		) )
	))
);

Re: Summary with String_Aggregation

ms — Mon, 15 Mar 2021 19:20:55 GMT

Here's an attempt using JSL (JMP 15). It can be done interactively too, using transform and combine columns.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt1 = dt << Transpose( columns( :name ), By( :sex ), Output Table( "Test" ) );
dt1 << Combine Columns(
	delimiter( ", " ),
	Columns( 3 :: N Col( dt1 ) ),
	Selected Columns are Indicator Columns( 0 ),
	Column Name( "ConcatName" )
) << deletecolumns( 2, 4 :: N Col( dt1 ) );

Re: Summary with String_Aggregation

Jeff_Perkinson — Mon, 15 Mar 2021 19:49:22 GMT

@ih and @ms have given you good answers but I was already working on this solution, so I thought I'd post it to show another way to do this, via JSL.

Taking advantage of the Subset By option in Subset we can get a table for each subgroup, and then get the values from the Name column and use Concat Items() to build the string of names.

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

dt_list = dt << Subset(
	By( :sex ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :age, :height, :weight ),
	private
);

nt = New Table();
group_col_name = Word( 1, dt_list[1] << get name, "=" );
nt << New Column( group_col_name, character );
nt << New Column( "concat name" );
nt << add rows( N Items( dt_list ) );
For( i = 1, i <= N Items( dt_list ), i++,
	Column( nt, group_col_name )[i] = Word( 2, dt_list[i] << get name, "= " );
	nt:concat name[i] = Concat Items( dt_list[i]:name << get values, ", " );
);

For( i = 1, i <= N Items( dt_list ), i++,
	Close( dt_list[1], nosave )
);

dt_list = Empty();

Re: Summary with String_Aggregation

ms — Mon, 15 Mar 2021 20:07:57 GMT

I wasn't aware of that Subset( By()...) returns a list. Can be very useful. Thanks Jeff!

/Marcus

Re: Summary with String_Aggregation

TWE — Tue, 16 Mar 2021 10:50:34 GMT

Thanks to all approaches.

All of them are working well!

Re: Summary with String_Aggregation

TWE — Tue, 16 Mar 2021 10:54:09 GMT

Thanks ih,

I tried your approach with some tables and all are working. Sometimes it needed a bit time (depends on the table size).

Re: Summary with String_Aggregation

TWE — Tue, 16 Mar 2021 10:55:15 GMT

Thanks ms,

this is a very fast way. I tried it based an huge LogFile and it works in seconds!

Re: Summary with String_Aggregation

TWE — Tue, 16 Mar 2021 10:56:48 GMT

Thanks Jeff,

your solution is also working fast and I was also not aware of of the Subset(by()) command.