Rank within row

KNIME Analytics Platform
#1

Hi all,

I am trying to create a ranking, but within a row.

I have following table

Col1 Col2 Col3 Col4 Col5 Col6
Hans 10 EUR 12 EUR 15 EUR 22 EUR 9 EUR
Bert 7 EUR 5 EUR 11 EUR 4 EUR 2 EUR

And basically looking for following output

Col1 Col2 Col3 Col4 Col5 Col6
Hans 4 3 2 1 5
Bert 2 3 1 4 5

 

#2

How do you derive the data in the second table from the first ?

#3

A bit tricky since the Rank node is designed to work by columns. Without reverting to any R code snippet or code based solutions, your best option is to Transpose, Rank, Transpose again and clean up.

See the attached workflow for an example on how to achieve this.

#4

These are the ranks of each row. Highest EUR amount --> Rank 1, second highest --> Rank 2 and so on.

#5

unpivot (retain Col1 as constant), rank, pivot

#6

unpivoting, then rank and re-pivot is right, there are few things to be wary when doing it. You need to convert the "10 EUR" etc strings into numbers, and pivoting will change the names of the columns, Also to guarantee you end up back in the same row order, you may need to resort. 

The attached workflow does what you want for your sample.

 

#7

Hi all,

thanks for your replies and suggestions, much appreciated!

I ended up using the Java Snippet node with following code, which works fine.

Possibly not the best code, but I want to share it anyway:

// system imports
import org.knime.base.node.jsnippet.expression.AbstractJSnippet;
import org.knime.base.node.jsnippet.expression.Abort;
import org.knime.base.node.jsnippet.expression.Cell;
import org.knime.base.node.jsnippet.expression.ColumnException;
import org.knime.base.node.jsnippet.expression.TypeException;
import static org.knime.base.node.jsnippet.expression.Type.*;
import java.util.Date;
import java.util.Calendar;
import org.w3c.dom.Document;

// Your custom imports:
import java.util.Arrays;
import java.util.Comparator;

// system variables
public class JSnippet extends AbstractJSnippet {

  // Fields for output columns
/** Output column: "BL Rank" */
  public Double out_BLRank;
/** Output column: "GR Rank" */
  public Double out_GRRank;
/** Output column: "LG Rank" */
  public Double out_LGRank;
/** Output column: "AC Rank" */
  public Double out_ACRank;
/** Output column: "DR Rank" */
  public Double out_DRRank;
/** Output column: "SK Rank" */
  public Double out_SKRank;
/** Output column: "MT Rank" */
  public Double out_MTRank;

// Your custom variables:
int i;
// expression start
    public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
final double[][] mySalesArray = {
	new double[] {71.0,c_BL,.0},
	new double[] {72.0,c_GR,.0},
	new double[] {73.0,c_LG,.0},
	new double[] {74.0,c_AC,.0},
	new double[] {76.0,c_DR,.0},
	new double[] {78.0,c_SK,.0},
	new double[] {82.0,c_MT,.0}
};

Arrays.sort(mySalesArray, new Comparator<double[]>() {
        @Override
        public int compare(final double[] o1, final double[] o2) {
            final double d1 = o1[1];
            final double d2 = o2[1];
            return Double.compare(d1, d2);
        }
    });
    
i=7;

for(final double[] d:mySalesArray) {
	d[2] = i;
	i--;
}

i=7;

for(final double[] d: mySalesArray) {
	if(d[0] == 71.0 ) {
		out_BLRank = d[2];
		if(d[1]==0){
			out_BLRank = .0;
		}
	}
	
	if(d[0] == 72.0  ) {
		out_GRRank = d[2];
		if(d[1]==0){
			out_GRRank = .0;
		}		
	}
	
	if(d[0] == 73.0 ) {
		out_LGRank = d[2];
		if(d[1]==0){
			out_LGRank = .0;
		}		
	}
	
	if(d[0] == 74.0 ) {
		out_ACRank = d[2];
		if(d[1]==0){
			out_ACRank = .0;
		}		
	}
	
	if(d[0] == 76.0 ) {
		out_DRRank = d[2];
		if(d[1]==0){
			out_DRRank = .0;
		}		
	}
	
	if(d[0] == 78.0 ) {
		out_SKRank = d[2];
		if(d[1]==0){
			out_SKRank = .0;
		}		
	}
	
	if(d[0] == 82.0 ) {
		out_MTRank = d[2];
		if(d[1]==0){
			out_MTRank = .0;
		}		
	}
}


// expression end
    }
}