Frekul (フリクル)の楽曲売上をデータベース管理してコンピアーティストへ自動送金する話（1）

前置き

時たまDTM（パソコンで曲作るやつ）をやっているshuyamです。
昨年末に自作曲をリリースしたのですが、その売り上げが先月くらいからぼちぼち入ってきました。

有難いことに2人の方にRemixをお願いして快諾して頂けて、3曲入りのシングルとなったやつです。
個人的には初の共同リリースで、ものすご嬉しくて滅多にない楽しい経験で、
自分もRemixとかやっていきたいなぁって思っています。
売り上げ自体は少額なのですが、感謝の気持ちも込めて是非ともきっちりとお二人に還元したいところ。

ちなみに曲はFrekulというサービスを利用しているのですが、問題はだいたい次の3点。

売り上げは毎月永久に入ってくる
口座にログインできるのは自分のみ
一定期間を過ぎると過去の詳細は見れない

月毎のCSVファイルはダウンロードできるのでこれを利用していい感じに支払いを自動化して、詳細を二人に共有するのが目標です。

対策

データベースを作り、一定額になるとアマゾンギフトカードで二人に送金、詳細はWebページから確認できるようにします。

ツールはシェルスクリプトとJava、MySQLを使うことにしました。実装環境としてはちょうど触ってみたかったGoogle Cloud Platformに弱小CentOS(Linux)サーバーを立てて、そこに全部導入しようと思います（有難いことに初期費用ゼロ）。

で、今回はとりあえずCSVファイルの整形について書くことにします。シェルスクリプトにもちょっとは慣れてきたかな、、、難しい。。

【本題】CSVファイルの整形

FrekulでCSVファイルをダウンロードすると、次のようなフォーマットでデータが入ってきます。

cavデータ

日時,内容,売上,手数料,入出金,残高
"2018/06/04 19:00","各社サービスに配信「アルバム名」iTunes Store アルバム売上 2017年12月分","1,494.72",597.89,896.83,"1,271.64"
"2018/06/04 19:04","各社サービスに配信「曲名 [アーティスト1 Remix]」iTunes Store 単曲ダウンロード売上 2017年12月分",319.68,127.87,191.81,374.81
"2018/06/04 19:04","各社サービスに配信「曲名 [アーティスト2 Remix]」iTunes Store 単曲ダウンロード売上 2017年12月分",159.84,63.94,95.90,183.00

いわゆる、3桁区切りのカンマ付きCSVファイルですね。まず最初の問題は、このCSVをLinuxで見ようとすると文字化けして読めないということです（あるあるw）

文字コード変換

なので文字エンコードをShift-JISからUTF-8に変換して上書きします。これにはnkfコマンドが便利なのですが、CentOS7には標準では入っていないのでインストールします。

nkfコマンドのインストール

sudo yum install epel-release
sudo yum install nkf

Macの人はHomebrewでインストールすると楽です。

nkfコマンドのインストール(Macの人用)

brew install nkf

これで準備OKです。次で変換します。

文字コード変換

# ファイルの場所
datafile=~/ホゲホゲ.csv

# UTF-8に変換
echo $datafile | xargs nkf --overwrite -w;

シェルでの変数は$変数とか、${変数}といった書き方になります。

アーティスト毎の行抜き出し

次は各アーティスト毎に行を抜き出します。今回はアルバム購入の行もあるので、日本語でアルバム売上を指定してgrepする必要があります。lvコマンドをインストールすると付いてくるlgrepコマンドで、マルチ言語の抜き出しを行います。

例によってcentOS7では非標準なので、別途インストールしておきます。アーティスト名やアルバム名が英字なら不要です。普通にgrepを使ってください。

lvのインストール(CentOS7)

# 各行を順に実行してください。
wget http://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/l/lv-4.51-25.el7.x86_64.rpm
sudo rpm -ivh lv-4.51-25.el7.x86_64.rpm
wget http://mirror.centos.org/centos/6.9/os/x86_64/Packages/compat-libtermcap-2.0.8-49.el6.x86_64.rpm
sudo rpm -ivh compat-libtermcap-2.0.8-49.el6.x86_64.rpm

Macの人はまたまたHomebrewでインストールしましょう。

lvのインストール(Macの人用)

brew install lv

それでは日本語にはlgrep、英字にはgrepを使って行を抜き出します。

行抜き出し

# ファイルの場所
datafile=~/ホゲホゲ.csv
# 中間ファイルの生成場所
place=~/適当なディレクトリ

lgrep "アルバム売上" $datafile | lgrep "アルバム名" > $place/album.csv;
grep -i アーティスト名1 $datafile > $place/アーティスト1.csv;
grep -i アーティスト名2 $datafile > $place/アーティスト名2.csv;

一行目で|で渡して二度検索しているのは、アルバム名と曲名に同じ文字列があったためです。

これで、各アーティスト・項目毎に抜き出せました。
次は各要素の処理したいところですが、このままでは数字にもカンマがあってうまくいきそうにありません。ダブルクオート""で要素のまとまりがつくられているので、""内のカンマのみを取り除くことで3桁区切りなしのデータに置き換えます。

3桁区切りカンマの除去

メイン作業です。CSVを見ると、1,000を超える数字は必ず"1,000"となっており、1,000未満の数字には""がついていません。そのため、""で囲まれた領域を指定して、カンマが含まれていれば除去することで対応出来そうです。

cavデータの再確認

"2018/06/04 19:00","各社サービスに配信「アルバム名」iTunes Store アルバム売上 2017年12月分","1,494.72",597.89,896.83,"1,271.64"

偶数番目のダブルクオートを指定してcutコマンドで要素を切り出すと、カンマで囲まれた要素を取り出せます。
その要素から置き換えコマンドsedでカンマの除去をします。この除去した結果で切り出した要素を上書きしたいので、再度sedコマンドで対象の要素を置き換えます。

これを行内の各要素について反復し、さらに各行について反復します。

3桁区切りカンマの除去

# 中間ファイルの生成場所
place=~/適当なディレクトリ


n=1 # 行数指定用カウンター
IFS=$'\n' #改行文字を行の区切りに指定

for line in `cat $place/album.csv` # 各行についてループ
do
	for i in {3..6} # ""で囲まれた項目についてのループ。i=1, 2は日付と曲名なのでスキップ。
	do
		moto=`echo $line | cut -d '"' -f $(( i*2 ))` # 変更前の文字列
		ato=`echo ${moto} | sed -e 's/,//'` # カンマ除去後の文字列
		if [ -n "${moto}" ]; then # 変更前の文字列がnonzeroならば実行
			sed -i -e ''$n','$n's/'${moto}'/'${ato}'/' $place/album.csv # カンマ除去して上書き(-iオプション)
		fi
	done
	n=$(( n+1 )) # 行数カウントアップ
done

これでカンマが区切り文字として機能するようになったので、不要な""は削除しておきます。

ダブルクオーテーションの除去

# 中間ファイルの生成場所
place=~/適当なディレクトリ

sed -i -e 's/"//g' $place/album.csv

ようやく自由にデータを扱えるようになりました。

【補足】sedコマンドの使い方

sedは文字を置き換えるためのコマンドです。基本的な使い方は次のようにします。

sedの使い方

sed -e 's/置き換え前の文字列/置き換え後の文字列' ファイル名

文字列の部分に変数を使いたい場合は、変数をシングルクオート(')で囲みます。

sedの使い方2

sed -e 's/'${変数1}'/'${変数2}'' ファイル名

置き換えたい行を指定するには、sオプションの前に行番号をつけます。

sedの使い方3

sed -e '開始行番号,終了行番号s/置き換え前の文字列/置き換え後の文字列' ファイル名

# 例えば2行目から3行目まで置き換える場合
sed -e '2,3s/置き換え前の文字列/置き換え後の文字列' ファイル名

sedコマンドは最初に見つけた文字列を一度置き換えるだけなので、全ての文字を置換したい場合はgをつけます。

sedの使い方4

sed -e 's/置き換え前の文字列/置き換え後の文字列/g' ファイル名

その他の使い方はココが詳しいので参照してください。

残高列の削除

最後に残高列(一番右端)は共有する必要がないので削除しておきます。列を削除するコマンドはないので、逆に残高列以外の列を取ってきて上書きすることにしました。cutコマンドで1-5（1列目から5列目）を指定します。

残高列の削除

# 中間ファイルの生成場所
place=~/適当なディレクトリ

(rm -f $place/album.csv && cut -d ',' -f 1-5 > $place/album.csv) < $place/album.csv

上で少しトリッキーな書き方をしているのは、読み込みファイルと書き出しファイルが同じためです。
例えば次のように書いてしまうと、出力は空白ファイルになってしまいます。

ダメな例

cut -d ',' -f 1-5 $place/album.csv > $place/album.csv # 空白ファイル

そこで面倒ですが最初のコードのように、読み込みファイルをrmコマンドとcutコマンドの両方に渡して、ファイル削除後に元ファイル名で出力指定してcutコマンドを実行します。これだと空白ファイルにならずに置き換えることができます。

まとめ

以上をまとめてスクリプトファイルに書いて実行します。
アルバム売上と二人分の単曲売上を取り出すために実際に作ったスクリプトを載せておきます。

# !/bin/bash

# ファイルの場所
datafile=~/ホゲホゲ.csv
# 中間ファイルの生成場所
place=/適当なディレクトリ

# UTF-8に変換
echo $datafile | xargs nkf --overwrite -w;


# 各種データの抜き出し
lgrep "アルバム売上" $datafile | lgrep "アルバム名" > $place/album.csv;
grep -i アーティスト名1 $datafile > $place/アーティスト名1.csv;
grep -i アーティスト名2 $datafile > $place/アーティスト名2.csv;


## 数字の3桁区切りカンマを除去
# for アルバム売上
n=1
IFS=$'\n'
for line in `cat $place/album.csv` # 各行についてループ
do
	for i in {3..6} # “”で囲まれた数字項目についてループ
	do
		moto=`echo $line | cut -d '"' -f $(( i*2 ))` # 変更前の文字列
		ato=`echo ${moto} | sed -e 's/,//'` # カンマ除去後の文字列
		if [ -n "${moto}" ]; then # 変更前の文字列がnonzeroならば実行
			sed -i -e ''$n','$n's/'${moto}'/'${ato}'/' $place/album.csv # カンマ除去で置き換えして上書き(-iオプション)
		fi
	done
	n=$(( n+1 ))
done
sed -i -e 's/"//g' $place/album.csv # "の除去
(rm -f $place/album.csv && cut -d ',' -f 1-5 > $place/album.csv) < $place/album.csv # 残高行の削除(残高行以外で上書き)

# for アーティスト1
n=1
IFS=$'\n'
for line in `cat $place/アーティスト名1.csv` # 各行についてループ
do
	for i in {3..6} # “”で囲まれた数字項目についてループ
	do
		moto=`echo $line | cut -d '"' -f $((i*2))` # 変更前の文字列
		ato=`echo ${moto} | sed -e 's/,//'` # カンマ除去後の文字列
		if [ -n "${moto}" ]; then # 変更前の文字列がnonzeroならば実行
			sed -i -e ''$n','$n's/'${moto}'/'${ato}'/' $place/アーティスト名1.csv # カンマ除去で置き換えして上書き(-iオプション)
		fi
	done
	n=$(( n+1 ))
done
sed -i -e 's/"//g' $place/アーティスト名1.csv
(rm -f $place/アーティスト名1.csv && cut -d ',' -f 1-5 > $place/アーティスト名1.csv) < $place/アーティスト名1.csv

# for アーティスト2
n=1
IFS=$'\n'
for line in `cat $place/アーティスト名2.csv` # 各行についてループ
do
	for i in {3..6} # “”で囲まれた数字項目についてループ
	do
		moto=`echo $line | cut -d '"' -f $((i*2))` # 変更前の文字列
		ato=`echo ${moto} | sed -e 's/,//'` # カンマ除去後の文字列
		if [ -n "${moto}" ]; then # 変更前の文字列がnonzeroならば実行
			sed -i -e ''$n','$n' s/'${moto}'/'${ato}'/' $place/アーティスト名2.csv # カンマ除去で置き換えして上書き(-iオプション)
		fi
	done
	n=$(( n+1 ))
done
sed -i -e 's/"//g' $place/アーティスト名2.csv
(rm -f $place/アーティスト名2.csv && cut -d ',' -f 1-5 > $place/アーティスト名2.csv) < $place/アーティスト名2.csv

今後の展望

次回では、この結果をCentOS上のmySQLに渡してデータベース登録を行います。そこで軽くGoogle cloud platform でのサーバーの立て方、各環境の構築についても触れるつもりです（巷に溢れている情報ですが）。

では。