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


2021/04/16

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

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

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

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


はじめに

当サイトではオフィス業務の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
        
なお一行目の日付はエクセル時間形式になっています。この時間形式に関しては以下の記事で取り上げています。

ツールスクリプトの実装

では先程の生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コマンドを利用した期間ごとの集計を、月間・週間・曜日の3つのパターンに分けて説明していきます。

月ごと

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

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

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
        
のように1月と判断できます。

週ごと

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

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

            
            + 年またぎを考慮しないもの:
    %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]
                }
            }
        }
    '
}
        
今回は条件分岐により、集計の方法で2通り、集計の期間で3通りの組み合わせで計6通りの処理が一括して行えるようにしておりますので、多少分岐が多くなっています。

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

動作確認

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

月間来店客数

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

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

週別来店客数

質問に1、2を入力すると...

            
            $./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
        

曜日ごとの来店客数

質問に1、3を入力すると...

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

月間総売上

質問に2、1を入力すると...

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

週別総売上

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

            
            $./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
        

曜日ごとの総売上

質問に2、3を入力すると...

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


まとめ

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

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

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

記事の担当:taconocat

ナンデモ系エンジニア

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