はじめに

前回、基本的な機械学習の手順：①分類モデルで、本当に基本的な分類モデルの作成手順を整理しました。
ただ、データを準備するところは、端折ってしまっていました。そこで、今回はBigQueryを使って下記のようなテーブルを作成し、Python環境に取り込むまでの手順を整理していきたいと思います。

id	result	product1	product2	product3	product4	product5
001	1	2500	1200	1890	530	null
002	0	750	3300	null	1250	2000

分析環境

Google BigQuery
Google Colaboratory

対象とするデータ

下記の様なテーブル構造で購買データが格納されています。

|id |店舗名 |購入日 |購入商品 |購入金額 |購入個数 |
|---|---|---|---|---|---|---|
|001|〇〇店|2020-01-10|product1|2500|1|
|002|☓☓店|2020-01-10|product1|750 |3|
|002|☓☓店|2020-01-10|product2|1000 |2|

テーブル名は、BQ上で次のようになっています。（日別でテーブル分け）
myproject.mydataset.transaction_年月日

項目名は、残念ながら日本語ではつけられませんが、わかりやすくするためにここでは日本語にしています。
それぞれの環境に合わせて名称を変えてください。

1.BigQuery上でやること

BigQuery上でやることと書いていますが、実際の操作は全てPython（Google Colaboratory）から行っています。ちゃんと、何の処理をしたかを記録に残す意味でも、Notebookは重宝しますし。

ColaboratoryからBigQueryを操作するには、次の２行で簡単に認証できます。
URLが表示されたら自身のアカウントでアクセスして、ワンタイムパスワードを発行し、Notebookに表示されたセルに貼り付けましょう。


from google.colab import auth
auth.authenticate_user()

1-1.購買データの加工

それでは、実際に元の購買データから人別・商品別に集計する処理を進めていきましょう。

from google.cloud import bigquery

# ここでは2020年1月を対象期間とします
from_day = "20200101"
to_day = "20200131"

query=f"""
SELECT
 id, 購入商品, SUM(購入金額) AS 合計金額
 FROM `myproject.mydataset.transaction_*`
 WHERE _table_suffix between `{from_day}` and `{to_day}`
"""

# 出力するプロジェクト・データセット・テーブル名
project = "myproject"
client = bigquery.Client(project=project)
dataset = "mydataset"
ds = client.dataset(dataset )
table = "tmp"

job_config = bigquery.QueryJobConfig()
job_config.destination = ds.table(table)
job_config.write_disposition="WRITE_TRUNCATE" #テーブルに上書きする場合。追加する場合は"WRITE_APPEND"
job_config.allow_large_results=True #大規模な結果を許可するか。基本はTrue
job_config.use_legacy_sql=False #レガシーSQLを使うか。基本はFalse
job = client.query(query, job_config=job_config)

result = job.result()

これで、何も出ずに実行が完了したら、myproject.mydataset.tmpに実行結果が出力されています。
job_configでの設定が少々面倒ですが、最初はそれぞれが何の設定をしているのか意識しながら書いておきましょう。（慣れたらコピペでOK）

ここまで実行すると、次のようなテーブルになっています。

id	購入商品	合計金額
001	product1	2500
001	product2	1200
002	product1	750
002	product2	3300

1-2.人別テーブルへ加工

次に、機械学習のモデルに読み込むために、id × 購入金額のPivotTableを作成したいと思います。

PythonでPandasを使えば、pivot_tableが使えて楽なのですが、対象の期間等によっては、データサイズが数GBや数十GBあり、データ取り込みや加工に余計な時間がかかるので、PivotTable化までをBigQueryでやっています。


new_columns=[]

# 横持ちする「購入商品」の一覧を作成して、Dataframe化
new_column="購入商品"
query = f'SELECT DISTINCT {new_column} FROM `{project}.{dataset}.{tmp}` ORDER BY {new_column}'
df_product = client.query(query).to_dataframe()

## 購入商品のDataframeをループして、横持ちするSQLを自動生成
for index, row in df_product.iterrows():
	item_frag = []
	condition_frag = []

	for i, v in zip(row.index, row.values):
		formula=f"{i}='{v}'"
		condition_frag.append(formula)
		item_frag.append(v)

	item=''.join(item_frag)
	condition=' AND '.join(condition_frag)
	query_frag = f'SUM(IF(({condition}),"購入金額",0)) AS {new_column}_{item}'
	new_columns.append(query_frag)

# new_columnsというListをquery_partsという文字列に変更
query_parts = ',\n'.join(new_columns)
query = f'SELECT id + query_parts + FROM `{project}.{dataset}.{tmp}` GROUP BY id'

# 出力先のテーブル名
table2="pivoted_table"

# BigQueryの実行は同上
job_config = bigquery.QueryJobConfig()
job_config.destination = ds.table(table2)
job_config.write_disposition="WRITE_TRUNCATE" #テーブルに上書きする場合。追加する場合は"WRITE_APPEND"
job_config.allow_large_results=True #大規模な結果を許可するか。基本はTrue
job_config.use_legacy_sql=False #レガシーSQLを使うか。基本はFalse
job = client.query(query, job_config=job_config)

result = job.result()

Pivot TableのSQLを作るところの処理が、２重でループしていて少しややこしいですが、作りたいのは次のSQLです。

SELECT
 id,
SUM(IF((購入商品='product1'),購入金額,0)) AS 購入商品_product1,
SUM(IF((購入商品='product2'),購入金額,0)) AS 購入商品_product2,
SUM(IF((購入商品='product3'),購入金額,0)) AS 購入商品_product3,
...
FROM `myproject.mydataset.tmp`
GROUP BY id

テーブルをPivot化する定番のSQLですが、これのSUM(IF(～))部分を、商品数に応じて自動で作成するようにしています。
ここまで実行すると、次のテーブルがmyproject.mydataset.pivoted_tableとして保存されています。

id	product1	product2	product3	product4	product5
001	2500	1200	1890	530	null
002	750	3300	null	1250	2000

#2.Pythonの環境への取り込み

最後に、BigQueryで作成したデータをPython環境にPandas Dataframeで取り込みます。
とはいえ、Pivot化の途中で既にDataframeで取り込んでいるので、プログラムを書くのも今更ですね。。。

query = f'SELECT * FROM `{project}.{dataset}.{table2}`'
df = client.query(query).to_dataframe()

おわりに

「基本的な機械学習の手順」と言いながら、今回はBigQueryでの操作に終始しました。
Colaboratoryからは、認証が簡単にできるため、BigQueryの操作へのハードルも低く感じますね。

BigQueryは今でも十分パワフルですが、日々新たな機能がリリースされています。上手く新たな機能を使いこなしていきたいですね。