Help us understand the problem. What is going on with this article?

基本的な機械学習の手順:②データを準備しよう

はじめに

前回、基本的な機械学習の手順:①分類モデルで、本当に基本的な分類モデルの作成手順を整理しました。
ただ、データを準備するところは、端折ってしまっていました。そこで、今回は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を操作するには、次の2行で簡単に認証できます。
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を作るところの処理が、2重でループしていて少しややこしいですが、作りたいのは次の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は今でも十分パワフルですが、日々新たな機能がリリースされています。上手く新たな機能を使いこなしていきたいですね。

CraveOwl
小売業のPOSデータを分析して、経営やマーケティングに役立てる仕事をしています。 主に、Google Cloud Platformを使っています。 機械学習、データサイエンスや、データエンジニアなどを中心に、新しいテクノロジーをトライしていきたいと思います。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした