【実践！CSVデータ集計スクリプト作成編】日付範囲で細かい条件指定した集計を行うスクリプト

※ 当ページには【広告/PR】を含む場合があります。

2021/04/16

【シェルスクリプト＆Excel】より複雑な条件を満たすのときのみに集計するスクリプト

CSVデータの自由でより高機能な集計操作を行うスクリプトツールを作成してみる特集の第１回目です。

今回は初回ということで、CSVデータの集計操作の基礎的な内容も掘り下げつつ、後半で少し応用的なツールスクリプトに仕上げる方法をじっくり解説していきます。

なお、先行して以下の記事で、コマンド直打ちよりも修正作業が楽になる方法として、対話型スクリプトに仕上げる方法を紹介しています。

【シェルスクリプト＆Excel】集計を対話的にするスクリプト作成の基礎

集計モードを対話に選択できるようなツールスクリプトに仕立てる前段階のユーザーインタラクティブなシェルスクリプトの実装方法を考えてみます。

今回の内容は対話型のスクリプトをベースに機能を盛り込んでいくスタイルで実装していきます。

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

はじめに

当サイトではオフィス業務のComputer-Aidedなハイブリッドな方法を模索し、より効率的なExcel業務を実現したい多忙なオフィスワーカー向けの主にAwkとSedを使うシェル講座です。

シェルスクリプトはどこでもどんなOSでも基本的に使えて、しかも一度使い方を覚えると、Excelと組み合わせて最高に効率の良いオフィスワークツールが作れることでしょう。

データの準備

今回もとある飲食店の売上高をまとめたものを集計する題材に利用しましょう。

今回のデータ列の並びは以下のようにしておきます。

開く/閉じる

            
            日付(エクセル時間),来客数,一日売上高

今回のデータは少し長めですので、以下のレポジトリからcsvファイルをCurlから入手できるようにしてあります。

開く/閉じる

            
            $ curl -O https://raw.githubusercontent.com/tacoskingdom/commonBlogMaterial/main/deep-tacopots/2021_sales.csv
$ cat 2021_sales.csv
44197,51,111417
44198,91,182127
#...中略
44304,44,91798

なお一行目の日付はエクセル時間形式になっています。この時間形式に関しては以下の記事で取り上げています。

Excelでの時間の計算をシェルスクリプトでどう取り扱うのか〜DATEの計算方法

エクセルでの日付時間とシェルスクリプトの扱うUNIX時間との日付のフォーマット形式を考慮して、シェルスクリプトでCSVインポート・エクスポートを行う方法を考察してみます。

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

ツールスクリプトの実装

では先程の生Csvデータから対話的に集計するために、

前回の記事で作成していた簡単なツールスクリプトの雛形

を以下のように拡張工事して、

interactive_add_up.sh

として作成しておきましょう。

開く/閉じる

            
            #!/bin/bash

#👇☆未実装
function TotalCustomerOrSales() {
    local option=$(( $1 + 1 ))
    local mode="$2"
    local filepath="$3"
    #...実装は後ほど
}

function Addup() {
    local option
    local mode

    printf "Q1. 集計したい項目を 1 か 2 で選択してください\n"
    printf "\n  0) データの確認  1) 総来客数  2) 総売上\n\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read OPT
    if [ "$OPT" == '0' ] ; then
        cat $1 | awk -F"," '
            function parse_excel_date(rawtime) {
                #👇1900/01/00とのEpoch時間差を取得
                parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
                excel_date = strftime("%Y年%m月%d日", parsed_time);
                return excel_date;
            }
            BEGIN{ OFS="," }
            {
                print parse_excel_date($1),$2,$3;
            }
        '
        exit 1
    elif [ "$OPT" == '1' ] ; then
        printf '\n  来店客数  が選択されました\n\n'
        option=$OPT
    elif [ "$OPT" == '2' ] ; then
        printf '\n  総売上  が選択されました\n\n'
        option=$OPT
    elif [ "$OPT" == 'q' ] || [ "$OPT" == 'c' ] ; then
        echo "終了"
        exit 1
    else
        printf "ヒント...1 か 2 で入力!\n\n"
        Addup $1
    fi

    printf "Q2. 集計期間を 1 か 2 か 3 で選択してください\n"
    printf "\n  1) 月間  2)  週間  3) 曜日\n\n"
    printf "※ 終了は q か c を入力！\n>>>"
    read MODE
    if [ "$MODE" == '1' ] || [ "$MODE" == '2' ] || [ "$MODE" == '3' ]; then
        if [ "$option" == '1' ] || [ "$option" == '2' ]; then
            TotalCustomerOrSales "$option" "$MODE" "$1"
            mode=$MODE
        fi
    elif [ "$MODE" == 'q' ] || [ "$MODE" == 'c' ]; then
        echo "終了"
        exit 1
    else
        printf "ヒント...1 か 2 か 3 で入力!\n"
        printf "最初からやり直してください\n\n"
        Addup $1
    fi

    if [ -n "$option" ] || [ -n "$mode"]; then
        exit 1
    fi
}

Addup $1

さて、この対話型スクリプトでは、集計項目と集計期間を2つに分けて選択できるようにしています。

なお最初の集計項目で0を選択すると、データの確認ができるように修正しております。

開く/閉じる

            
            $ chmod +x interactive_add_up.sh
$ ./interactive_add_up.sh 2021_sales.csv
Q1. 集計したい項目を 1 か 2 で選択してください

  0) データの確認  1) 総来客数  2) 総売上

※ 終了は q か c を入力！
>>>0
2021年01月01日,51,111417
2021年01月02日,91,182127
#...中略
2021年04月18日,44,91798

データの確認のためにはエクセル時間だと日付が具体的に分からないため、一時的に日付形式表示できるようにしています。

エクセル時間から通常の日付に変換するテクニックは前の回で特集しています

ので、併せてお読みください。

なお日付が歯抜けになっているのは毎週水曜が定休日を想定しています。

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

期間で集計するためのDateコマンドの使い方

では具体的にdateコマンドを利用した期間ごとの集計を、

月間・週間・曜日

の３つのパターンに分けて説明していきます。

月ごと

まずは月ごとの集計の自動計算の方法を検討してみましょう。

前の回

では、エクセル時間を通常の日時に変換するテクニックとして紹介していたものを、月だけを判定できるように修正してみます。

dateコマンドには、日付から様々な形式で時間の情報を取り出せるような出力フォーマットが備わっています。

例えば、月だけを判定するためのフォーマット表記には以下が利用できます。

開く/閉じる

            
            %m : 月を2桁で表現 (01～12)
%B : 月をロケール表示 (例: January)
%b : %Bの略記版 (例: Jan)

これを例えばもっとも簡潔な

%m

を利用すると、

開く/閉じる

            
            echo '44197' | awk -F"," '
    function excel_month(rawtime) {
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
        cmd="date -d @"parsed_time " +\"%m\"";
        cmd | getline month_; close(cmd);
        return month_;
    }
    {
        print excel_month($1);
    }
'
#👇
01

のように１月と判断できます。

週ごと

次に週ごとの集計の自動計算の方法を先程と同様に検討してみましょう。

週番号を表示するフォーマットオプションとしては以下があります。

開く/閉じる

            
            + 年またぎを考慮しないもの:
    %W : 月曜日を週初めとした週番号 (00..53)
    %U : 日曜日を週初めとした週番号 (00..53)

+ 年またぎも考慮するもの(ISO週番号):
    %V : 月曜日を週初めとするISO週番号 (01..53)
    %G : ISO週番号の西暦年部分。
        通常は%Vとセットで利用する
    %g : %Gの最後の2桁だけ表示
        (例: 2021 > 21)

今回は利用しませんが、規格として決まっているISO週番号を利用すると、より厳密な週を考慮する集計が可能になります。

開く/閉じる

            
            echo '44197' | awk -F"," '
    function excel_week(rawtime) {
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
        cmd="date -d @"parsed_time " +\"%W\"";
        cmd | getline week_; close(cmd);
        return week_;
    }
    {
        print excel_week($1);
    }
'
#👇
00

%Wフォーマットでは月曜が週の基点になり、かつ年始の1月1日からを

00

とするカウント方法になるようです。

曜日ごと

dateの出力フォーマットを利用すると、曜日ごとの集計も簡単に実現できます。

曜日に関するフォーマット表記は、

開く/閉じる

            
            %u : 月曜日を1とした曜日サイクル番号 (1..7)
%w : 日曜日を0とした曜日サイクル番号 (0..6)
%A : 曜日のロケール表示 (例: Sunday)
%a : %Aの略記 (例: Sun)

先程の月の判定は月曜を基点にしたので、ここでは

%u

を採用すると以下のようにできます。

開く/閉じる

            
            $ echo '44197' | awk -F"," '
    function excel_day(rawtime) {
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
        cmd="date -d @"parsed_time " +\"%u\"";
        cmd | getline day_; close(cmd);
        return day_;
    }
    {
        print excel_day($1);
    }
'
#👇5は金曜日
5

ツールスクリプトの実装の続き

では先程のdateコマンドの利用法を前置きを踏まえて、未実装だった関数(TotalCustomerOrSales)の部分の中身を作成していきます。

早速一気に実装完成品を以下のように与えます。

開く/閉じる

            
            #!/bin/bash

function TotalCustomerOrSales() {
    #👇option : 1) 来店客数[2列目] 2) 売上高[3列目]
    local option=$(( $1 + 1 ))
    #👇mode : 1) 月間 2) 週間 3) 曜日
    local mode="$2"
    local filepath="$3"
    cat "$filepath" | awk -v mode=$mode -F"," '
        function excel_month(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%m\"";
            cmd | getline month_; close(cmd);
            return month_;
        }
        function excel_week(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%W\"";
            cmd | getline week_; close(cmd);
            return week_;
        }
        function excel_day(rawtime) {
            parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
            cmd="date -d @"parsed_time " +\"%u\"";
            cmd | getline day_; close(cmd);
            return day_;
        }
        BEGIN {
            # Array of months
            for (i=1;i<=12;i++) {
                m[i-1] = i > 9 ? i "" : "0" i;
            }
            # Array of weeks
            for (i=0;i<=53;i++) {
                w[i] = i > 9 ? i "" : "0" i;
            }
            # Array of days of the week
            for (i=1;i<=7;i++) {
                d[i-1] = i "";
            }
        }
        {
            if (mode == 1) {
                for (i in m) {
                    if ( excel_month($1) == m[i] ) {
                        if (mSummary[i] == "" ) {
                            mSummary[i] = $'$option'
                        } else {
                            mSummary[i] += $'$option'
                        }
                    }
                }
            } else if (mode == 2) {
                for (i in w) {
                    if ( excel_week($1) == w[i] ) {
                        if (wSummary[i] == "" ) {
                            wSummary[i] = $'$option'
                        } else {
                            wSummary[i] += $'$option'
                        }
                    }
                }
            } else if (mode == 3) {
                for (i in d) {
                    if ( excel_day($1) == d[i] ) {
                        if (dSummary[i] == "" ) {
                            dSummary[i] = $'$option'
                        } else {
                            dSummary[i] += $'$option'
                        }
                    }
                }
            }
        }
        END {
            if (mode == 1) {
                for (i in mSummary) {
                    print m[i] "月:" mSummary[i]
                }
            } else if (mode == 2) {
                for (i=0 ; i < length(w) ; i++ ) {
                    if (wSummary[i] != "") { print w[i] "週:" wSummary[i] }
                }
            } else if (mode == 3) {
                for (i in dSummary) {
                    print d[i] "曜日:" dSummary[i]
                }
            }
        }
    '
}

今回は条件分岐により、集計の方法で２通り、集計の期間で３通りの組み合わせで計６通りの処理が一括して行えるようにしておりますので、多少分岐が多くなっています。

各集計のポイントとして、データの一列目($1)をexcel_monthなどの期間の判定を行う関数を使って、期間を表すキーを格納している配列(m[*]など)と比較して、キーが同じであったら集計計算を行うような処理をしています。

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

動作確認

折角ですので、このスクリプトを動作させて６通りの集計結果をみてみます。

月間来店客数

質問に１、１を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
01月:1994
02月:1698
03月:2239
04月:1208

週別来店客数

質問に１、２を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
00週:219
01週:436
02週:418
03週:443
04週:478
05週:511
06週:341
07週:455
08週:391
09週:475
10週:540
11週:501
12週:524
13週:458
14週:536
15週:413

曜日ごとの来店客数

質問に１、３を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
1曜日:1229
2曜日:1096
4曜日:1125
5曜日:1055
6曜日:1331
7曜日:1303

月間総売上

質問に２、１を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
01月:4108513
02月:3501668
03月:4576208
04月:2497273

週別総売上

質問に２、２を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
00週:451084
01週:898587
02週:857494
03週:911447
04週:989901
05週:1045881
06週:698865
07週:938857
08週:818065
09週:962564
10週:1103552
11週:1032966
12週:1074030
13週:944144
14週:1105164
15週:851061

曜日ごとの総売上

質問に２、３を入力すると...

開く/閉じる

            
            $./interactive_add_up.sh 2021_sales.csv
#...中略
1曜日:2506109
2曜日:2251597
4曜日:2314700
5曜日:2190471
6曜日:2737769
7曜日:2683016

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

まとめ

今回は期間集計を行うポイントを詳しく見ていきました。

Awkとdateを組み合わせることで、期間の伴うデータの解析に非常に応用が効くことが理解いただけたかと思います。

まだ休日・祝祭日の取扱なども場合によっては必要かと思います。これも時間があれば後日記事として取り上げていければ良いなと考えているところです。

記事を書いた人

記事の担当:taconocat

ナンデモ系エンジニア

主にAngularでフロントエンド開発することが多いです。開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。コツコツとプログラミングするのが好きな人間です。

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

はじめに

データの準備

ツールスクリプトの実装

期間で集計するためのDateコマンドの使い方

月ごと

週ごと

曜日ごと

ツールスクリプトの実装の続き

動作確認

月間来店客数

週別来店客数

曜日ごとの来店客数

月間総売上

週別総売上

曜日ごとの総売上

まとめ

その他のリンク記事

【シェルスクリプト＆Excel】集計を対話的にするスクリプト作成の基礎

【シェルスクリプト＆Excel】集計を対話的にするスクリプト作成の基礎

【シェルスクリプト＆Excel】より複雑な条件を満たすのときのみに集計するスクリプト

【シェルスクリプト＆Excel】より複雑な条件を満たすのときのみに集計するスクリプト

Excelでの時間の計算をシェルスクリプトでどう取り扱うのか〜DATEの計算方法

Excelでの時間の計算をシェルスクリプトでどう取り扱うのか〜DATEの計算方法