【GAS】クロス集計された横持ちデータを縦持ちに変換する

2022年6月22日GAS

二次元配列にされているデータを軸をずらして分析したい場合、縦持ちにしたいなって思うことが多かったのですが、GASは横持⇒縦持ちに変換できる関数がないようなのでGASでコードを書いてみました。

1. 元データと最終目標となる配列
2. 元データセットをスプレッドシートで用意
3. データの構成を整理
4. コードを記述する

元データと最終目標となる配列

店舗ごとの月の実績データが3か月分持っていることにします。

※月ごとに分かれている売上シートを統合するにはこちら。（参考：共通列を持つ複数のシートの統合）

//元データ：横持ち
年月	店舗名	店舗ID	従業員数	売上	来店客数
2020/06/01	A	332	6	265372	33
2020/06/01	B	383	33	265427	33
2020/06/01	C	488	27	331522	21
2020/06/01	D	979	1	110193	2
2020/07/01	A	878	33	268141	7
2020/07/01	B	670	23	396982	1
2020/07/01	C	889	33	312252	13
2020/07/01	D	893	13	298151	32
2020/08/01	A	481	36	195515	15
2020/08/01	B	470	6	208665	11
2020/08/01	C	175	40	154143	13
2020/08/01	D	293	38	19102	19

これを以下の縦持ちデータに統合するのがゴールです。

//アウトプットデータ：縦持ち
年月	店舗名	店舗ID	属性	値
2020年6月	A	332	従業員数	6
2020年6月	B	383	従業員数	33
2020年6月	C	488	従業員数	27
2020年6月	D	979	従業員数	1
2020年7月	A	878	従業員数	33
2020年7月	B	670	従業員数	23
2020年7月	C	889	従業員数	33
2020年7月	D	893	従業員数	13
2020年8月	A	481	従業員数	36
2020年8月	B	470	従業員数	6
2020年8月	C	175	従業員数	40
2020年8月	D	293	従業員数	38
2020年6月	A	332	売上	265,372
2020年6月	B	383	売上	265,427
2020年6月	C	488	売上	331,522
2020年6月	D	979	売上	110,193
2020年7月	A	878	売上	268,141
2020年7月	B	670	売上	396,982
2020年7月	C	889	売上	312,252
2020年7月	D	893	売上	298,151
2020年8月	A	481	売上	195,515
2020年8月	B	470	売上	208,665
2020年8月	C	175	売上	154,143
2020年8月	D	293	売上	19,102
2020年6月	A	332	来店客数	33
2020年6月	B	383	来店客数	33
2020年6月	C	488	来店客数	21
2020年6月	D	979	来店客数	2
2020年7月	A	878	来店客数	7
2020年7月	B	670	来店客数	1
2020年7月	C	889	来店客数	13
2020年7月	D	893	来店客数	32
2020年8月	A	481	来店客数	15
2020年8月	B	470	来店客数	11
2020年8月	C	175	来店客数	13
2020年8月	D	293	来店客数	19

元データセットをスプレッドシートで用意

スプレッドシートに用意しておきましょう。同じGoogle Driveフォルダの中（同じじゃなくても大丈夫ですがわかりやすく）に、「元データ：横持ち」（上のデータをコピペ）と「アウトプットデータ：縦持ち」（空白）というスプレッドシートを用意し、後で利用するそれぞれのシートのIDを記録しておきます。

データの構成を整理

元データと変換後データの値の位置関係を確かめます。

形式を変えないカラム（年月、店舗名、店舗ID）のブロックは変換したい属性（従業員数、売り上げ、来店客数）の回数（ここでいうと3回）分繰り返す。
属性にはカラム名を挿入。
値には対応する属性のデータを挿入。

これを繰り返し表記でどう表現できるか整理します。

コードを記述する

//元データの変換しないカラム数
const t = 3;
//元データの変換する属性数
const v = 3;

//データ元のシート
const im_id = "***「元データ：横持ち」のID***";
//貼り付け先シート
const ex_id = "***「アウトプットデータ：縦持ち」のID***";

function MyScripts(){
  
  //データ元取得
  const data = GetSheetData(im_id);

  var values = data[1];
  var col_name = data[2];

  //
  var n = values.length;

  //貼り付け先
    var to_spredsheet = SpreadsheetApp.openById(ex_id);
    to_spredsheet.getDataRange().clear();
    var to_sheet = to_spredsheet.getSheetByName("シート1");

  //カラムを先に追加 
    to_sheet.getRange(1,1,1,5).setValues([['年月','店舗名','店舗ID','指標','値']]); //data[2] = ["年月","店舗名","店舗ID","指標","値"]

  var x=0;
    //変換する属性カラム個数分の繰り返し
    for(var s = 0; s < v; s++){

      //元データのレコード数分繰り返し
      for(var i = 0; i < n; i++){

        //列数分繰り返し（変換しない列＋2回）
        for(var j = 0; j < (t+2); j++){
          var input_data;

          //変換しないカラムは元データを格納
          if(j < t){

            input_data = values[i][j];

          //属性名を格納
          }else if(j == t){

            input_data = col_name[j];

          //属性ごとの値
          }else{

            input_data = values[i][t+s];

          }

          // console.log(input_data); // 出力確認
          to_sheet.getRange(x+2,j+1).setValue(input_data);
        }

        x++;
      }
  }
  //for文ここまで

}


function GetSheetData(id) {
  var spredsheet = SpreadsheetApp.openById(id);
  var sheet = spredsheet.getSheetByName("シート1");
  var value = sheet.getDataRange().getValues();
  var value_nocolumn = sheet.getDataRange().getValues();
  const column = value_nocolumn.shift();
  
  //行列数取得
  var num_row = value_nocolumn.length;
  var num_col = value_nocolumn[0].length;

  var results = [value, value_nocolumn, column, num_row, num_col];
  return results;
}

なんとかできました。もう少しスマートな記述ができたらアップデートします。

もっとこんな良い方法もあるよという方がいらっしゃいましたらぜひコメントください！

配列の操作方法の詳しい解説はこちら。