Find column in which one row is greatest or lowest compared to other rows.

D_T_M

This seems like an easy question to solve but I'm kinda stuck.

 

I have a dataset with 250 rows and roughly 20K columns. The rows are split into two categories : Good ( 249 rows)  and Bad (1 row). I need to find all the columns in which the 'BAD' row has the higher/lower value than compared to rest of the 'GOOD' rows.

Can you suggest any efficient way of doing this?

 

Thank you! 

txnelson

Here is how I would do it, assuming you have a column that identifies the 1 Bad row, and all of the 249 Good rows.

1. Stack all of your 20k rows, keeping the column that identifies the Bad row and all of the Good rows

     Tables==>Stack

2. Summarize the resulting Stacked table

     a. Group by the new column "Label"

     b. Subgroup by the column that identifies the Bad and Good data

     c. Specifiy to calculate the Min and Max for the column "Data"

3. In the resulting Summary table

     a. Add a new column called "Min Max" using the formula

If(
			:Name( "Max(Data,Bad)" ) > :Name( "Max(Data,Good)" ), "Max",
			:Name( "Min(Data,Bad)" ) < :Name( "Min(Data,Good)" ), "Min",
			"Neither"
		)

Below is an example script that will create the above results.  It uses an example data table with only 4 columns, however, it will work for your 20k table by changing the name in the script that points to the column with the Bad and Good rows(Called "GoodBad" in the Example data table), to the name of the column in your data table that identifies the Bad and Good data.

The script also assumes that all numeric columns are to be compared.

Names Default To Here( 1 );

// Create a simple data table for illustration
dt = New Table( "simple",
	Add Rows( 10 ),
	New Column( "A", Set Values( [59, 61, 55, 66, 52, 60, 61, 51, 60, 61] ) ),
	New Column( "B",
		Format( "Fixed Dec", 5, 0 ),
		Set Values( [22, 123, 74, 145, 64, 84, 128, 79, 112, 107] )
	),
	New Column( "C",
		Set Values( [110, 96, 100, 92, 92, 97, 98, 94, 91, 94] ),
	),
	New Column( "GoodBad",
		Character,
		Set Values(
			{"Bad", "Good", "Good", "Good", "Good", "Good", "Good", "Good", "Good", "Good"}
		)
	)
);

// Get All Numeric Columns
numericCols = dt << get column names(numeric);

// Stack all of the numeric
dtStack = dt << Stack(invisible,
	columns( numericCols ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

// Summarize the data
dtSum = dtStack << Summary(
	Group( :Label ),
	Min( :Data ),
	Max( :Data ),
	Subgroup( :GoodBad ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);<BR /><BR />// Delete the no longer needed Stacked data table<BR />

<SPAN style="display: inline !important; float: none; background-color: #ffffff; color: #008000; font-family: Consolas,Monaco,'Andale Mono','Ubuntu Mono',monospace; font-size: 14px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: pre; word-spacing: 0px;">close( dtStack, nosave );</SPAN><BR />// Add the column that determines if Bad column is < or > Good
dtSum << New Column(
	"Max or Min",
	formula(
		If(
			:Name( "Max(Data,Bad)" ) > :Name( "Max(Data,Good)" ), "Max",
			:Name( "Min(Data,Bad)" ) < :Name( "Min(Data,Good)" ), "Min",
			"Neither"
		)
	)
);