カテゴリー
【実践！CSVデータ集計スクリプト作成編】日付範囲で細かい条件指定した集計を行うスクリプト
※ 当ページには【広告/PR】を含む場合があります。
2021/04/16
CSVデータの自由でより高機能な集計操作を行うスクリプトツールを作成してみる特集の第１回目です。
今回は初回ということで、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コマンドを利用した期間ごとの集計を、
月間・週間・曜日月ごと
まずは月ごとの集計の自動計算の方法を検討してみましょう。
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を組み合わせることで、期間の伴うデータの解析に非常に応用が効くことが理解いただけたかと思います。
まだ休日・祝祭日の取扱なども場合によっては必要かと思います。 これも時間があれば後日記事として取り上げていければ良いなと考えているところです。
記事を書いた人
ナンデモ系エンジニア
主にAngularでフロントエンド開発することが多いです。 開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。 コツコツとプログラミングするのが好きな人間です。
カテゴリー