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 of “line including 12 jpy“, “line including 123 jpy“, “line including 1,234 jpy“.

2019-12-03 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-sort-by-numeric/

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
// (c) 2019, 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

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".

Notes

  1. Numeric parsing depends on JavaScript parseFloat().
  2. If a “prefix” precedes a number, it will not be determined as a number. (eg “$ 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”)

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: