TSV文字列, 数値合計のクロス集計

// (c) 2019, Questetra, Inc. (the MIT License)
// Notes:
// A timeout may occur depending on the number of records or aggregate labels.
// Aggregate labels are sorted in ascending or descending order.
// 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 ",".

// Notes(ja):
// 履歴TSVのレコード数（行数）や集約ラベルの数によってはタイムアウトとなります。
// 集約ラベルは昇順もしくは降順でソートされます。
// 数値判定は JavaScript parseFloat() に依存します
// 数値の前に "接頭辞" があると数値として判定されません ( 例 "$100" )
// ピリオド "." は小数点として認識されます
// 桁区切り文字を想定し、全てのカンマ "," を除去したうえで数値判定します


//////// 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: Crosstab Target Columns: " + targetColIds );
engine.log( " AutomatedTask Config: Sort Order: " + sortOrder );

const arrTargetColIds = targetColIds.split(",");
if( arrTargetColIds.length !== 3 ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Target Col-IDs} length must be three \n" );
}
const intCalcTarget = parseInt( arrTargetColIds[0] );
const intXFieldCol  = parseInt( arrTargetColIds[1] );
const intYFieldCol  = parseInt( arrTargetColIds[2] );

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 / 演算 ==
// Extract X-flds Y-flds
let arrXFields = [];
let arrYFields = [];
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine = arrInputString[i].split("\t");
  if( arrXFields.indexOf( arrTsvLine[ intXFieldCol ] ) == -1){
      arrXFields.push(    arrTsvLine[ intXFieldCol ] );
  }
  if( arrYFields.indexOf( arrTsvLine[ intYFieldCol ] ) == -1){
      arrYFields.push(    arrTsvLine[ intYFieldCol ] );
  }
}
arrXFields.sort( function( strA, strB ){
  if( sortOrder === "DESC" ){ // from Z to A
    if( strA < strB ){ return 1;}
    if( strA > strB ){ return -1;}
  }else{ // ASC: alphabetical from A to Z
    if( strA > strB ){ return 1;}
    if( strA < strB ){ return -1;}
  }
});
arrYFields.sort( function( strA, strB ){
  if( sortOrder === "DESC" ){ // from Z to A
    if( strA < strB ){ return 1;}
    if( strA > strB ){ return -1;}
    return 0;
  }else{ // ASC: alphabetical from A to Z
    if( strA > strB ){ return 1;}
    if( strA < strB ){ return -1;}
    return 0;
  }
});

// Calculate outputTable[y][x]
let grandTotal = 0;
let outputTable = new Array( arrYFields.length );
for( let i = 0; i < arrYFields.length; i++ ){
  outputTable[i] = new Array( arrXFields.length );
  for( let j = 0; j < arrXFields.length; j++ ){
    outputTable[i][j] = 0; // initialize
  }
}
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine = arrInputString[i].split("\t");
  outputTable[
    arrYFields.indexOf( arrTsvLine[ intYFieldCol ] )
  ][
    arrXFields.indexOf( arrTsvLine[ intXFieldCol ] )
  ] += parseFloat( arrTsvLine[ intCalcTarget ].replace( /,/g,"") );
  grandTotal += parseFloat( arrTsvLine[ intCalcTarget ].replace( /,/g,"") );
}

// Convert to TSV String
let outputTsv = "Crosstab\t";
for( let i = 0; i < arrXFields.length; i++ ){
  outputTsv += arrXFields[i] + "\t";
}
outputTsv += "total\n"; // header ends
for( let i = 0; i < outputTable.length; i++ ){
  outputTsv += arrYFields[i] + "\t";
  for( let j = 0; j < outputTable[i].length; j++ ){
    outputTsv += outputTable[i][j] + "\t";
  }
  outputTsv += sum( outputTable[i] ) + "\n";
}
outputTsv += "total\t";
for( let i = 0; i < arrXFields.length; i++ ){
  let vsum = 0;
  for( let j = 0; j < arrYFields.length; j++ ){
    vsum += outputTable[j][i];
  }
  outputTsv += vsum + "\t";
}
outputTsv += grandTotal + "";


//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdC, outputTsv );

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


function sum(arrF){
  let tmp = 0;
  for( let i = 0; i < arrF.length; i++){ tmp += arrF[i]; }
  return tmp;
}