2022年2月26日土曜日

15分毎の温度・湿度データを1時間平均に変換する、そして欠損値の探索・修復をする（Google Apps Script）

気象庁より提供されている過去の気象データとRaspberry Pi製データロガーで取得したビニールハウス内の温度・湿度の比較のため、スプレッドシートに記録された15分毎のデータを1時間平均に変換するGAS（Google Apps Script）を作りました。

・Raspberry Pi Zero WHを使って、格安で温湿度データロガーを作る。（2/2）【Pythonスクリプトの作成】


今回はとりあえず10月から2月途中までのデータですが、1万行を超えているので手作業ではしんどいですね。あと、データが飛んでいたり重複したりというところが多少あったので、その部分の修復も自動化してみました。

備忘録がてら、以下作り方です。


＊＊＊

まずは、15分毎の温度・湿度データを1時間平均に変換します。

スプレッドシートIDは適宜。

シートは元データが入ったものが「raw_data」、1時間平均に直したものを入れるのが「data」になります。

それぞれ、A列が「日時」、B列が「温度」、C列が「湿度」、1行目が見出しという構成です。

//15分毎のデータを1時間平均に変換
function setHourAve() {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートID");
  const rdSheet = spreadsheet.getSheetByName("raw_data");
  const dSheet = spreadsheet.getSheetByName("data");
  
  let lastRow = rdSheet.getLastRow();
  
  let dt_raw = rdSheet.getRange(2, 1, lastRow, 1).getValues();
  let tm_raw = rdSheet.getRange(2, 2, lastRow, 1).getValues();
  let hu_raw = rdSheet.getRange(2, 3, lastRow, 1).getValues();

  let data = [];

  let dt_h = "";
  let tm_h = "";
  let hu_h = "";
  let count_h = 1; 
  let count_row = 0;
  
  for(let x = 0; x < lastRow - 1; x++) {
    dt_h = Utilities.formatDate(new Date(dt_raw[x]), "JST", "MM/dd HH");
    tm_h = Number(tm_raw[x]);
    hu_h = Number(hu_raw[x]);
    for(let y = 1; y < 5; y++) {
      if(dt_h == Utilities.formatDate(new Date(dt_raw[x + y]), "JST", "MM/dd HH")) {
        tm_h = tm_h + Number(tm_raw[x + y]);
        hu_h = hu_h + Number(hu_raw[x + y]);
        count_h++;
      }
    }
    data.push([Utilities.formatDate(new Date(dt_raw[x]), "JST", "yyyy/MM/dd HH:00:00"), tm_h / count_h, hu_h / count_h]);
    x = x + count_h - 1;
    count_h = 1;
    count_row++;
  } 
  dSheet.getRange(2, 1, count_row, 3).setValues(data);
}
※ コードを一部修正（2022-3-5）

15分毎にデータを取得しているので、通常は1時間あたり4回ほど温度・湿度が記録されるのですが、たまーに5回取れてしまうことがあったようです。そうすると、同じ日時の行が重複してしまいます。その対策として、23行目のfor文は5回ループさせています。


次に、1時間平均に変換したデータに欠損値等がないかをチェックします。あった場合には修復します。

//欠損値を探索・修復
function srchMissValu() {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートID");
  const dSheet = spreadsheet.getSheetByName("data");

  let lastRow = dSheet.getLastRow();
  let dt_data = dSheet.getRange(3, 1, lastRow, 1).getValues();
  let dt = "";
  let dt_next = "";
  let diff = 0;
  let x = 0;
  let y = 0;

  for(let i = 0; i < lastRow - 2 + x; i++) {
    dt = new Date(dt_data[i]);
    dt_next = new Date(dt_data[i + 1]);
    if((dt_next - dt) / 1000 > 3600) {
      //欠損データ（日時）の追加
      diff = (dt_next - dt) / 1000 / 3600;
      for(let j = 0; j < diff - 1; j++) {
        dSheet.insertRowAfter(2 + i + x);
        dSheet.getRange(3 + i + x, 1).setValue(Utilities.formatDate(new Date(dt.setHours(dt.getHours() + 1)), "JST", "yyyy/MM/dd HH:00:00"));
        Logger.log("追加　" + (3 + i + j) + "行目：" + Utilities.formatDate(new Date(dt), "JST", "yyyy/MM/dd HH:00:00"));
        x++;
      } 
    } else if (dt_next - dt == 0) {
      //重複データの削除
      dSheet.deleteRow(3 + i + x);
      Logger.log("削除　" + (3 + i + x) + "行目：" + Utilities.formatDate(dt, "JST", "yyyy/MM/dd HH:00:00"));
      x--;
    }
    y = i + 3;
  }
  Logger.log("データを" + y + "行目までチェックしました。");
}
※ コードを一部修正（2022-3-5）

今回、欠損値というは、1時間平均が「2021/10/01 16:00:00」から「2021/10/01 18:00:00」に飛んでしまっているようなパターンです。時間が飛んでいると気象庁の過去の気象データと比較する際に行がずれてしまうため、「2021/10/01 17:00:00」を追記する必要があります。

また、元データの方で1時間に6回以上値が入っていると「2021/10/01 16:00:00」の次の行に再度「2021/10/01 16:00:00」が入ってしまうので、その場合は重複したデータの行を削除します。（5回ループのfor文のおかげで、今回は該当ありませんでした。）

-----
・追記（2022-9-24）

-----


＊＊＊

ということで、無事に処理完了。こういうのは分かりやすく便利ですね。

今後、いろいろ使いまわせそうな気がします。


ちなみに、ちょっと混乱したのですが、Utilities.formatDateで年を取得するときは小文字（yyyy）だそうです。

大文字（YYYY）ですと、翌年の1月1日と同じ週が翌年として表示されてしまうようです。例えば2021年の場合、12月26日〜31日が2022年12月26日〜31日と表示されてしまいます。（参考 ：「SimpleDateFormatで年を表すときはyyyy」）

過去に作ったスクリプト内でもYYYYになっているものがあったので修正しました。


GAS、日付や配列の操作にだいぶ慣れてきました。Pythonの方も頑張っていきましょう。


詳解! Google Apps Script完全入門 [第3版]
詳解! Google Apps Script完全入門 [第3版]
by SimpleImageLink


雪だるま　猫
雪だるま　きな粉


Twitter（@nkkmd）日々更新中です。
By 時刻:
ラベル: ,