【シェルスクリプト＆Excel】検索対象が無いときの対処方法〜エラー時の値を#N/Aに置き換える

検索結果が無い行を発見したときに、通常は何も表示されないで無視されることが多いですが、エラーハンドリングを定義し、エラーを発見したときの処置も実装したい場合があります。
たとえば、検索結果で一致しない行には、新しい内容を新規作成してそこに挿入する...などです。
今回はCSVデータ使う上でのAwkとJqを使ったシェルスクリプトのエラーの捌き方の基礎を行っていきます。

【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍＆教材特集

はじめに

当サイトではオフィス業務のComputer-Aidedなハイブリッドな方法を模索し、より効率的なExcel業務を実現したい多忙なオフィスワーカー向けの主にAwkとSedを使うシェル講座です。
シェルスクリプトはどこでもどんなOSでも基本的に使えて、しかも一度使い方を覚えると、Excelと組み合わせて最高に効率の良いオフィスワークツールが作れることでしょう。

Excelの値入力エラー時の挙動を定義する

Excelなどで関数をつかった集計計算などで、例えば数字が半角と全角を気付かずに四則演算してしまうことです。

開く/閉じる

            $ awk -F"," '{print $1*$2;}' << EOF
3,６
4,19
8,3
５,22
EOF
#👇出力
0
76
24
0

例えばAwkでは通常演算が正しく行われない場合でも、エラーではなくゼロが返さえる仕組みになっています。
これだとどのセルに欠陥データがあるかは分かりにくいので、Excel風に

#N/A

が出力させたいのが今回の試みです。

【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍＆教材特集

Awkの場合

単純に正規表現で先程の例を修正してみると、

開く/閉じる

            $ awk -F"," '{
    if ($1 ~ /[^0-9.]/ || $2 ~ /[^0-9.]/) {
        print "#N/A";
    } else {
        print $1*$2;
    }
}' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
#N/A
76
24
#N/A
41.085
0
#N/A

となりいい感じに数値以外の演算結果を#N/Aで出力することができます。
またgawk4.2以降の新しいバーションのAwkでは型判定の関数

typeof

を利用することで、より丁寧な演算前の処置も可能です。

開く/閉じる

            $ awk -F"," '{
    if ( typeof($1) != "strnum" || typeof($2) != "strnum" ) {
        print "#N/A";
    } else {
        print $1*$2;
    }
}' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
#N/A
76
24
#N/A
41.085
0
#N/A

なお、Awkでの型は

array

number

regexp

string

strnum

undefined

のいずれかに区別されます。

よもやま講座〜条件分岐のショートハンド

Awkには従来の

if~else if~else

での条件分岐も使えますが、モダンな

条件 ? 真の場合の返値 : 偽の場合の返値

でも先程と同様の操作が可能です。

開く/閉じる

            $ awk -F"," '{
    print ($1 ~ /[^0-9.]/ || $2 ~ /[^0-9.]/) ? "#N/A" : $1*$2;
}' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
#N/A
76
24
#N/A
41.085
0
#N/A

さらに判定に真偽(1か0)を返すカスタマイズ関数を利用し、以下のようなスクリプトの例に示すように

||

か

&&

オペレーターを利用すると、より複雑な判定が処理ブロックでワンライナーで実現できます。

開く/閉じる

            $ echo '1,2,3' | awk -F"," '
function func1(arg1_) {
    print arg1_;
    return 0;
}
function func2(arg2_) {
    print arg2_;
    return 0;
}
function func3(arg3_) {
    print arg3_;
    return 1;
}
function func4(arg4_) {
    print arg4_;
    return 1;
}
{
    func1($1) || func2($2) || func3($3) && func4($0);
}'
#👇出力
1
2
3
1,2,3

このテクニックで先程のスクリプトを置き換えると、

開く/閉じる

            $ awk -F"," '
function isStrnum(str_) {
    if (str_ ~ /[^0-9.]/) {
        print "#N/A";
        return 1;
    }
    return 0;
}
function calc(arg1_, arg2_) {print arg1_ * arg2_}
{
    isStrnum($1) || isStrnum($2) || calc($1, $2);
}' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
#N/A
76
24
#N/A
41.085
0
#N/A

のように使えます。
複雑な判定シークエンスなどをもつプログラムがAwkで書きたい場合にはこちらのテクニックは有効です。

【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍＆教材特集

Jqの場合

次はJqでも上の節と同様の操作を行ってみます。
最初に断っておきますが、Jqでのストリーム処理では今回のお題としてAwkよりも難解になります。個人的にはAwkが使えるならそちらで処理しても良い気はします。
まず

Jqの正規表現

を組み合わせて利用します。
少しJqスクリプトの働きが分かりにくいかもしれないので、段階的に説明していきます。
まずJqでは読み込んだCsvデータは全てstring型として扱われるので、

tonumber

関数を介してnumber型に変換しないといけません。このとき注意が必要なのが、デフォルトで、tonumber関数は数値以外の文字列を入力してしまうと、パースが失敗のエラーでプロセス自体が終了します。
ということで、

if ~ then ~ else ~ end

構文でtonumber手前でnumber型にパースできるかどうかを判定しないといけません。

開く/閉じる

            $ jq -s -R '
[
    split("\n")[] | select(length > 0) |
    [
        split(",") | .[] |
            if test("[^0-9.]") then "#N/A" else tonumber end
    ]
]
' << EOF
4,19
3,６
EOF
#👇出力
[
  [
    4,
    19
  ],
  [
    3,
    "#N/A"
  ]
]

上は例のようにCsvデータを二次元配列化しているのですが、非数値のセルには

#N/A

でマスクしておくことができます。
ここまで出来ると後は同様の考え方で、セル同士の要素を演算する場合にもif構文を使って処理を分岐させることが可能になります。

開く/閉じる

            $ jq -s -R '
[split("\n")[] | select(length > 0) | [ split(",") | .[] | if test("[^0-9.]") then "#N/A" else tonumber end ]] |
[
    .[] | if .[0] == "#N/A" or .[1] == "#N/A" then "#N/A" else .[0] * .[1] end
]
' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
[
  "#N/A",
  76,
  24,
  "#N/A",
  41.085,
  0,
  "#N/A"
]

上のスクリプトでも、計算できなかった行では演算結果が

#N/A

になっていますが、処理の説明の都合上、同じ処理を２回施してしまっているので、もう少し丸括弧で処理のスコープ範囲を見直して最適化してみると、

開く/閉じる

            $ jq -s -R '
[split("\n")[] | select(length > 0) | split(",")] |
[
    .[] | if (.[0] | test("[^0-9.]")) or (.[1] | test("[^0-9.]")) then "#N/A" else (.[0] | tonumber) * (.[1] | tonumber) end
]
' << EOF
3,６
4,19
8,3
５,22
4.5,9.13
526,0.0
９.1,0.0
EOF
#👇出力
[
  "#N/A",
  76,
  24,
  "#N/A",
  41.085,
  0,
  "#N/A"
]

Jqではこのくらいのスクリプトが落とし所かもしれません。

【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍＆教材特集

まとめ

以上、今回はCsvデータから集計を行う際に生じる数値演算出来ない場合のエラーをどのように出力として安全に取り扱うかを検討してきました。
プログラマーの気持ちの問題かもしれませんが、個人的にはJqよりもAwkの方がショートハンドテクニックを使えることもあり、一日の長といった感じでAwkに軍配が上がるように思いました。
なお、AwkもJqとも明確な静的型付けの言語ではないですが、かと言って動的な型付けもそこまで面倒を見てくれているような印象も受けません。実際にはC++で実装されいるということもあり、裏ではオブジェクト型に対して使う側がデリケートに取り扱う必要があります。

はじめに

Excelの値入力エラー時の挙動を定義する

Awkの場合

よもやま講座 〜 条件分岐のショートハンド

Jqの場合

まとめ

よもやま講座〜条件分岐のショートハンド