BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
aserino

Community Trekker

Joined:

Jan 31, 2018

Color cells for values that do not appear the most in each row

Hi all,

 

I've been struggling to put together something that works for this.

 

I'd like to iterate over each row and color the cells whose value appears NOT the most. For example, using the following table:

 

 RowNamesStore1Store2Store3Store4Store5
1AnimalCatDogCatCatBird
2Cost10128106

 

Row 1: highlight Dog and Bird

Row 2: highlight 12, 8, and 6

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Color cells for values that do not appear the most in each row

Here is a script that works for your example data table, and with a little work, should work for whatever tables you need it to work with

Names Default To Here( 1 );
dt = New Table( "example",
	Add Rows( 2 ),
	New Column( "RowNames", Character( 16 ), "Nominal", Set Values( {"Animal", "Cost"} ) ),
	New Column( "Store1", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store2", Character( 16 ), "Nominal", Set Values( {"Dog", "12"} ) ),
	New Column( "Store3", Character( 16 ), "Nominal", Set Values( {"Cat", "8"} ) ),
	New Column( "Store4", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store5", Character( 16 ), "Nominal", Set Values( {"Bird", "6"} ) )
);

// Add a new column to use as a pointer to the row 
dt << New Column( "TheRow", formula( Row() ) );
// Convert to static values for TheRow
dt:TheRow << delete formula;

// Stack the columns in question, so the processing can be done all at once
dtStack = dt << Stack(
	columns( :Store1, :Store2, :Store3, :Store4, :Store5 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Name( "Non-stacked columns" )(Keep( :TheRow ))
);

// Summarize the data to find the max count for each row values
dtSum = dtStack << Summary(
	Group( :TheRow, :Data ),
	Freq( "None" ),
	Weight( "None" ),
	Link to Original Data Table( 0 )
);

Close( dtStack, nosave );

// Add a new column to represent the maximums
dtSum << New Column( "TheMax",
	formula( If( :N Rows == Col Maximum( :N Rows, :TheRow ), 1, 0 ) )
);

// Delete the non max rows in the table
dtSum << select where( :TheMax == 0 );
dtSum << delete rows;

// Create a new table that has only one row for each row in the original data table
// and has a column for each maximum value found
dtSplit = dtSum << Split(
	Split By( :Data ),
	Split( :Data ),
	Group( :TheRow ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

Close( dtSum, nosave );

// Add a column that concatenates all values into one
theColumns = dtSplit << get column names;
theColumns = Remove( theColumns, 1, 1 );
dtSplit << Combine Columns(
	delimiter( "," ),
	Columns( Eval( theColumns ) ),
	Column Name( "The Maximums" )
);

// Delete the nonessential columns
dtSplit << delete columns( theColumns );

// attach the found maximumns to the original data table
dt = dt << Update( With( dtSplit ), Match Columns( :TheRow = :TheRow ) );

Close( dtSplit, nosave );

// Color the cells
For( i = 1, i <= N Rows( dt ), i++,
	theList = As List( Words( dt:The Maximums[i], "," ) );
	If( N Rows( Loc( theList, dt:Store1[i] ) ) == 0,
		dt:Store1 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store2[i] ) ) == 0,
		dt:Store2 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store3[i] ) ) == 0,
		dt:Store3 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store4[i] ) ) == 0,
		dt:Store4 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store5[i] ) ) == 0,
		dt:Store5 << Color Cells( "Red", i )
	);
);

// Cleanup
dt << delete columns( {"TheRow", "The Maximums"} );
Jim
4 REPLIES 4
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Color cells for values that do not appear the most in each row

Here is a script that works for your example data table, and with a little work, should work for whatever tables you need it to work with

Names Default To Here( 1 );
dt = New Table( "example",
	Add Rows( 2 ),
	New Column( "RowNames", Character( 16 ), "Nominal", Set Values( {"Animal", "Cost"} ) ),
	New Column( "Store1", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store2", Character( 16 ), "Nominal", Set Values( {"Dog", "12"} ) ),
	New Column( "Store3", Character( 16 ), "Nominal", Set Values( {"Cat", "8"} ) ),
	New Column( "Store4", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store5", Character( 16 ), "Nominal", Set Values( {"Bird", "6"} ) )
);

// Add a new column to use as a pointer to the row 
dt << New Column( "TheRow", formula( Row() ) );
// Convert to static values for TheRow
dt:TheRow << delete formula;

// Stack the columns in question, so the processing can be done all at once
dtStack = dt << Stack(
	columns( :Store1, :Store2, :Store3, :Store4, :Store5 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Name( "Non-stacked columns" )(Keep( :TheRow ))
);

// Summarize the data to find the max count for each row values
dtSum = dtStack << Summary(
	Group( :TheRow, :Data ),
	Freq( "None" ),
	Weight( "None" ),
	Link to Original Data Table( 0 )
);

Close( dtStack, nosave );

// Add a new column to represent the maximums
dtSum << New Column( "TheMax",
	formula( If( :N Rows == Col Maximum( :N Rows, :TheRow ), 1, 0 ) )
);

// Delete the non max rows in the table
dtSum << select where( :TheMax == 0 );
dtSum << delete rows;

// Create a new table that has only one row for each row in the original data table
// and has a column for each maximum value found
dtSplit = dtSum << Split(
	Split By( :Data ),
	Split( :Data ),
	Group( :TheRow ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

Close( dtSum, nosave );

// Add a column that concatenates all values into one
theColumns = dtSplit << get column names;
theColumns = Remove( theColumns, 1, 1 );
dtSplit << Combine Columns(
	delimiter( "," ),
	Columns( Eval( theColumns ) ),
	Column Name( "The Maximums" )
);

// Delete the nonessential columns
dtSplit << delete columns( theColumns );

// attach the found maximumns to the original data table
dt = dt << Update( With( dtSplit ), Match Columns( :TheRow = :TheRow ) );

Close( dtSplit, nosave );

// Color the cells
For( i = 1, i <= N Rows( dt ), i++,
	theList = As List( Words( dt:The Maximums[i], "," ) );
	If( N Rows( Loc( theList, dt:Store1[i] ) ) == 0,
		dt:Store1 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store2[i] ) ) == 0,
		dt:Store2 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store3[i] ) ) == 0,
		dt:Store3 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store4[i] ) ) == 0,
		dt:Store4 << Color Cells( "Red", i )
	);
	If( N Rows( Loc( theList, dt:Store5[i] ) ) == 0,
		dt:Store5 << Color Cells( "Red", i )
	);
);

// Cleanup
dt << delete columns( {"TheRow", "The Maximums"} );
Jim
pmroz

Super User

Joined:

Jun 23, 2011

Re: Color cells for values that do not appear the most in each row

Interesting problem.  I tried a different approach using transpose, which allows me to use column operations.

dt = New Table( "example",
	Add Rows( 2 ),
	New Column( "RowNames", Character( 16 ), "Nominal", Set Values( {"Animal", "Cost"} ) ),
	New Column( "Store1", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store2", Character( 16 ), "Nominal", Set Values( {"Dog", "12"} ) ),
	New Column( "Store3", Character( 16 ), "Nominal", Set Values( {"Cat", "8"} ) ),
	New Column( "Store4", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
	New Column( "Store5", Character( 16 ), "Nominal", Set Values( {"Bird", "6"} ) )
);

dtt = dt << Transpose(
	columns( :Store1, :Store2, :Store3, :Store4, :Store5 ),
	Label( :RowNames ),
	Output Table( "Transpose of example" ),
	invisible
);

for (i = 1, i <= nrows(dt), i++,
	one_col = column(dt, "RowNames")[i];
	tab = dtt << Tabulate(
		Show Control Panel( 0 ),
		Add Table( Row Table( Grouping Columns( column(dtt, one_col) ) ) ),
		invisible
	);	
	dtb = tab << Make Into Data Table;
	dtb << sort(by(:N), replace table(1));
	nr = nrows(dtb);
// Get the maximum counted value
	max_value = column(dtb, one_col)[nr];
// Now back to the original table - highlight everything but the max value
	for (k = 2, k <= ncols(dt), k++,
		one_value = column(dt, k)[i];
		if (one_value != max_value,
			column(dt, k) << Color Cells( "Light Yellow", i );
		);
	);
	close(dtb, nosave);
);
close(dtt, nosave);
txnelson

Super User

Joined:

Jun 22, 2012

Re: Color cells for values that do not appear the most in each row

Nice approach!

 

I do see one issue, what if more than one selection....let's say, both Cat and Dog have the same number of occurrances......

 

That is why I did the splitting and the combining of columns......

Jim
aserino

Community Trekker

Joined:

Jan 31, 2018

Re: Color cells for values that do not appear the most in each row

Thanks! This works great!