TSV String, Sort by Numeric
TSV String, Sort by Numeric
Sorts TSV text in the numerical order of the specified column. If each column values are “123 jpy”, “1,234 jpy”, and “12 jpy”, they are sorted in the order “line including 12 jpy”, “line including 123 jpy”, “line including 1,234 jpy”.
Configs
  • A: Select STRING DATA for TSV String *
  • B-key: Set Column ID(s) for Sort Target (eg “0” “3,1” ) *#{EL}
  • B-order: Set DESC or ASC ( default “DESC” )#{EL}
  • C: Select STRING DATA for Sorted TSV (update) *
Script (click to open)
// GraalJS Script (engine type: 2)
// (c) 2021, Questetra, Inc. (the MIT License)
// Notes:
// Numeric parsing depends on JavaScript parseFloat().
// If a "prefix" precedes a number, it will not be determined as a number. (eg "$ 100")
// The period "." is recognized as a decimal point.
// Assumes a digit separator and evaluates all numbers after removing all commas ",".
// If you want to specify multiple sort keys, use {Column ID} in CSV notation (eg "3,1,0")
// Notes(ja):
// 数値判定は JavaScript parseFloat() に依存します
// 数値の前に "接頭辞" があると数値として判定されません ( 例 "$100" )
// ピリオド "." は小数点として認識されます
// 桁区切り文字を想定し、全てのカンマ "," を除去したうえで数値判定します
// ソートキーを複数指定したい場合、{Column ID} をCSV表記で行います ( 例 "3,1,0" )



//////// START "main()" ////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA      = configs.get( "conf_DataIdA" ) + "";      // config required
const targetColIds = configs.get( "conf_TargetColIds" ) + ""; // config required
let   sortOrder    = configs.get( "conf_SortOrder" ) + "";    // config not required
const dataIdC      = configs.get( "conf_DataIdC" ) + "";      // config required
engine.log( " AutomatedTask Config: Sort Key Column(s): " + targetColIds );
engine.log( " AutomatedTask Config: Sort Order: " + sortOrder );

if( targetColIds === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Sort Column} is empty \n" );
}
const arrTargetColIds = targetColIds.split(",");

if( sortOrder !== "ASC" ){
  sortOrder = "DESC";
}


//// == Data Retrieving / ワークフローデータの参照 ==
const inputString = engine.findDataByNumber( dataIdA ) + "";
let   arrInputString = inputString.split("\n");
engine.log( " AutomatedTask MultilineString:" + 
            " TSV {A}, number of lines: " + arrInputString.length );


//// == Calculating / 演算 ==
arrInputString.sort( function( tsvlineA, tsvlineB ){
  let arrTsvlineA = tsvlineA.split("\t");
  let arrTsvlineB = tsvlineB.split("\t");

  if( sortOrder === "DESC" ){
    for( let i = 0; i < arrTargetColIds.length; i++ ){
      if( arrTsvlineA.length <= arrTargetColIds[i] ||
          arrTsvlineB.length <= arrTargetColIds[i] ){
        throw new Error( "\n AutomatedTask UnexpectedNumberError:" +
                         " Number {Sort Key ID} is larger than TSV length \n" );
      }
      if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) <
          parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) ){ return 1; }
      if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) >
          parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) ){ return -1; }
    }
  }else{ // ASC: alphabetical from A to Z
    for( let i = 0; i < arrTargetColIds.length; i++ ){
      if( arrTsvlineA.length <= arrTargetColIds[i] ||
          arrTsvlineB.length <= arrTargetColIds[i] ){
        throw new Error( "\n AutomatedTask UnexpectedNumberError:" +
                         " Number {Sort Key ID} is larger than TSV length \n" );
      }
      if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) >
          parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) ){ return 1; }
      if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) <
          parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( /,/g,"")) ){ return -1; }
    }
  }
  return 0;
});


//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdC, arrInputString.join("\n") );

} //////// END "main()" ////////////////////////////////////////////////////////////////

Download

2021-07-09 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-sort-by-numeric-2021/
The Add-on import feature is available with Professional edition.

Notes

  1. Numeric parsing depends on JavaScript parseFloat().
  2. If a prefix precedes a number, it will not be determined as a number. (e.g. “$100”)
  3. The period “.” is recognized as a decimal point.
  4. Assumes a digit separator and evaluates all numbers after removing all commas.
  5. If you want to specify multiple sort keys, use {Column ID} in CSV notation (eg “3,1,0”)

Capture

Sorts TSV text in the numerical order of the specified column. If each column values are "123 jpy", "1,234 jpy", and "12 jpy", they are sorted in the order of "line including 12 jpy", "line including 123 jpy", "line including 1,234 jpy".

See also

%d bloggers like this: