Column that Displays Longest String in Row

Question

I have a table that contains seven columns, all with string values in them. How would I create a column that displays the longest (or second or third longest etc.) string from each row of these seven select columns? I would like to do this using JSL.

txnelson · Answer

Most of the time in JSL, there is more than one way to solve a problem.  The following method is probably a pretty fast method to use if there are a lot of data.  using @jules  sample data as input to the below script, you will get the results you want

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "theRow", formula( Row() ) );

dtStack = dt << Stack(
	columns( :Column 1, :Column 2, :Column 3, :Column 4, :Column 5, :Column 6, :Column 7 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dtStack << New Column( "The Length", formula( Length( :Data ) ) );
dtStack << New Column( "The Rank", formula( Col Rank( :The Length, :theRow ) ) );
For( i = 1, i <= N Rows( dtStack ), i++,
	:Label[i] = "The Rank " || Word( 2, :Label[i], " " )
);
dtSplit = dtStack << Split(
	Split By( :Label ),
	Split( :The Rank ),
	Group( :theRow ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);
Close( dtStack, nosave );
dt << Update( With( dtSplit ), Match Columns( :theRow = :theRow ) );
Close( dtSplit, nosave );
dt << delete column( "theRow" );

jules · Answer

Hi @twillkickers,

Here's a (potentially inelegant) method of doing that:

listOfCols = {:Column 1, :Column 2, :Column 3, :Column 4, :Column 5, :Column 6, :Column 7};

destinationCol = New Column( "Column 8", Character, "Nominal" );

For Each Row(//step through each row

//get all the text for the columns in this row
	colText = Eval List( listOfCols );

colTextLength = {};

//generate a list of expressions to get lengths of text for this row
	For( i = 1, i <= Length( listOfCols ), i++,
		colTextLength[i] = Parse( "length(colText[" || Char( i ) || "])" )
	);

//if you wish to have something other than the longest, you can knock out the n max items
	//setting this to 1 then gives second longest, 2 = third longest, etc.
	//setting to 0 gives the longest (no knock out)

nKnockOut = 0;
	matLengths = Matrix( Eval List( colTextLength ) );

//knock out top as many times... 
	If( nKnockOut > 0,
		For( k = 1, k <= nKnockOut, k++,
			matLengths[Loc Max( matLengths )] = [0]
		)
	);

destinationCol[] = colText[Loc Max( matLengths )];

);

A table is attached for you to try this out on. You'll just need to update the list of columns and destination column at the top if you use this elsewhere. I left details in the script for how to select the longest, second longest, third longest, etc. It works by iteratively zeroing out the lengths of the longest, second longest, third longest, etc, so that you can still use LocMax() to find the longest (remaining) string location. I expect there is a method more clever than this, but I'm unaware of a Loc2ndMax() type function (though I'm sure you could write yourself something working with ranks).

I hope this helps get you started!

@jules

example.jmp

lengths.jsl