TSV String, Switch Rows and Columns
TSV String, Switch Rows and Columns
Switches the row and column indices of the rectangle TSV. The output matrix (transposed matrix) is obtained by changing cell (x,y) to (y,x). Used for data arrangement for frequency distribution tables and histograms.
Configs
  • A1: Set Input TSV *#{EL}
  • B1: Select STRING DATA that stores Output TSV (update) *
  • B2: Select NUMERIC that stores Num of Rows in Output (update)
  • B3: Select NUMERIC that stores Num of Columns in Output (update)
Script (click to open)
// GraalJS Script (engine type: 2)

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

//// == Config Retrieving / 工程コンフィグの参照 ==
const strTsv          = configs.get( "StrConfA1" );           /// REQUIRED /////////////
  if( strTsv        === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: Tsv} is empty \n" );
  }
  const arr2dTsv      = parseAsRectangular( strTsv );
const strPocketOutput       = configs.getObject( "SelectConfB1" );  /// REQUIRED ///////
const numPocketNumOfRows    = configs.getObject( "SelectConfB2" );  // NotRequired /////
const numPocketNumOfColumns = configs.getObject( "SelectConfB3" );  // NotRequired /////


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==

// output TSV
let strOutput = "";
for( let x = 0; x < arr2dTsv[0].length; x++ ){ // Will be Y in the new TSV
  for( let y = 0; y < arr2dTsv.length; y++ ){  // Concatenate vertical data horizontally
    strOutput += arr2dTsv[y][x];
    if( y !== arr2dTsv.length - 1 ){
      strOutput += "\t";
    }
  }
  if( x !== arr2dTsv[0].length - 1 ){
    strOutput += "\n";
  }
}


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData( strPocketOutput, strOutput );
if( numPocketNumOfRows !== null ){
  engine.setData( numPocketNumOfRows, new java.math.BigDecimal( arr2dTsv[0].length ) );
}
if( numPocketNumOfColumns !== null ){
  engine.setData( numPocketNumOfColumns, new java.math.BigDecimal( arr2dTsv.length ) );
}


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


// Parses TSV string as two-dimensional rectangular data matrix and creates a 2D array.
function parseAsRectangular( strTsv ){
  const arrTsv = strTsv.split("\n");

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let numMinWidth   = Infinity; // cf. String-Type Max: 1 million
  let numMaxWidth   = 0;
  let numBlanklines = 0;
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      numBlanklines += 1;
      continue;
    }
    let arrCells = arrTsv[i].split("\t");
    if( numMinWidth > arrCells.length ){ numMinWidth = arrCells.length; }
    if( numMaxWidth < arrCells.length ){ numMaxWidth = arrCells.length; }
  }
  engine.log( " AutomatedTask TsvDataCheck:" + 
              " MinWidth:" + numMinWidth +
              " MaxWidth:" + numMaxWidth +
              " Lines:" + arrTsv.length +
              " (BlankLines:" + numBlanklines + ")" );

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let arr2dTsv      = [];
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      continue;
    }
    let arrTmp = [];
    let arrCells = arrTsv[i].split("\t");
    for( let j = 0; j < numMaxWidth; j++ ){
      if( j < arrCells.length ){
        arrTmp[j] = arrCells[j];
      }else{
        arrTmp[j] = "";
      }
    }
    arr2dTsv.push( arrTmp );
  }

  return arr2dTsv;
}

/*
TSV Example:
2021-08	1,400
2021-09	2,500
2021-10	1,900

Notes:
- When the process reaches this automated task, TSV is automatically read.
    - TSV: Sales Records, Survey Records, etc.
- If there is a blank line in the input TSV text, it will be skipped.
    - The line feed code for the last line is not added either.
- The output TSV text (total table TSV, etc.) is rectangular data.
    - The number of rows in TSV is the number of types of "aggregate Key".
    - The number of columns of TSV is 3 of "key", "count" and "percentage"

APPENDIX:
- TSV (Tab Separated Values) text assumes rectangular data.
    - The number of rows in the output TSV is the number of columns in the input TSV.
    - The number of columns in the output TSV is the number of rows in the input TSV.


Notes-ja:
- 案件が自動処理工程に到達した際、文字列型データに保存されているTSVが自動的に読み込まれます。
    - TSV: 売上レコード、アンケート回答ログ、など
- 入力TSVテキストに空行がある場合、スキップされます。
    - 出力TSVの最終行に改行コードは付与されません。
- 出力されるTSVテキスト(合計値テーブルTSV等)は、矩形データです。
    - 出力TSVの行数は、入力TSVの列数です。
    - 出力TSVの列数は、入力TSVの行数です。

APPENDIX-ja:
- TSV(Tab Separated Values)テキストは、矩形データを前提とします。
    - 矩形でないデータは、空文字によって自動整形されます。
    - 空行(末尾改行を含む)は無視されます。
*/

Download

2021-09-10 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-switch-rows-and-columns-2021/
The Add-on import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • When the process reaches this automated task, the TSV is read automatically.
    • TSV: Sales Records, Survey Records, etc.
  • If there is a blank line in the input TSV text, it will be skipped.
    • The line feed code for the last line is not added either.
      - The output TSV text (total table TSV, etc.) is rectangular data.
    • The number of rows in TSV is the number of types of “aggregate Key”.
    • The number of columns of TSV is 3; key, count and percentage

Capture

Switches the row and column indices of the rectangle TSV. The output matrix (Transposed Matrix) is obtained by changing cell(x,y) to (y,x). Used for data arrangement for frequency distribution tables and histograms.
Switches the row and column indices of the rectangle TSV. The output matrix (Transposed Matrix) is obtained by changing cell(x,y) to (y,x). Used for data arrangement for frequency distribution tables and histograms.

Appendix

  • TSV (Tab Separated Values) text assumes rectangular data.
    • The number of rows in the output TSV is the number of columns in the input TSV.
    • The number of columns in the output TSV is the number of rows in the input TSV.

See also

1 thought on “TSV String, Switch Rows and Columns”

  1. Pingback: TSV String, Create Summary Table of Count – Questetra Support

Leave a Reply

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

%d bloggers like this: