ワールドカップ 2018の勝敗を BigQuery ML で予測する

サッカーワールドカップ 2018 ロシア大会は、フランスの20年ぶり2回目の優勝で幕を閉じましたが、今月、新たに幕が開いたものがあります。それが BigQuery ML です(詳細は以下の公式サイトでご確認ください)。

Introduction to BigQuery ML
( https://cloud.google.com/bigquery/docs/bigqueryml-intro )

現在はベータ版で、使える機械学習のモデルは線形回帰とロジスティック回帰の2つですが、これらのモデルが BigQuery 上のデータをそのまま利用して SQLの知識だけで手軽に実行できるのは画期的ではないかと思います。
使用する場面としては、

  • シンプルな回帰を実行して結果を確認する
  • Python などのプログラミング言語を使用したモデル構築の前に、BigQuery に保管されているデータが機械学習に適用可能なデータなのか確認する

などが想定されます。

本記事では、サッカーワールドカップ 2018 ロシア大会の試合データを学習データとして、BigQuery ML のロジスティック回帰を使用して日本代表の試合結果を予測してみます。ロシア大会の試合データだけしか使用せずデータ件数が少ないため、精度についての議論はできませんが、BigQuery ML を使うとロジスティック回帰がこれだけ簡単に実行できる、ということを実感していただけると思います。

1. 線形回帰では連続値を分析し、ロジスティック回帰では分類をする

そもそも線形回帰、ロジスティック回帰とは何でしょうか? 線形回帰とは、ある連続値の数学的なパターン(関数) を見つけ、その数学的なパターンに基づいて分析(予測) を行う手法です。例えば株価や飛行機乗客数、電力需要などの分析に適用できます。一方、ロジスティック回帰では、名前に “回帰” とはついていますが、目的は連続値ではなく2値分類、つまり真か偽かを表す数学的なパターンを見つけ、分析(予測) する手法です。例えば、受信したメールがスパムメールかそうでないか、ある治療を施した患者の病気が治るかどうか、などです。

2. BigQuery の権限を持つアカウントを用意する

早速、BigQuery ML を使う準備に入りますが、BiqQuery を扱うので本記事での手順を実行するためには BigQuery を有効にし、BigQuery のユーザー権限、及びデータセットへの編集権限を持つアカウントを用意する必要があります。BigQuery を有効にする手順は公式サイトで確認できますので、まだ実施していない方は手順に従い有効にしてください。

ウェブ UI を使用したクイックスタート (https://cloud.google.com/bigquery/quickstart-web-ui?hl=ja)

また、BigQuery のユーザー権限は IAM にて、データセットへの編集権限は GCP Cloud Console BigQuery の画面で設定できます。ただし、BigQuery の管理者権限など上位の権限を持っていればこれらの設定は不要です。BigQuery を使用できるようになったら、今回使用するデータセットを “test_worldcup2018” の名前で作成しておいてください。BigQuery の画面を開き、左のリソース一覧のプロジェクトID を選択すればデータセットを作成できます。
以降の手順にてデータの前処理で Python を使いますが、その実行環境として Colaboratory を使用します。Colaboratory には一定時間アイドル状態になると実行している仮想マシンがリセットされるなどの制約はありますが、Google アカウントがあれば無料で使用できます。

Colaboratory よくある質問
(https://research.google.com/colaboratory/faq.html)

3. Python でデータを前処理する

それでは Colaboratory へアクセスします。以下のリンクへアクセスしてみてください。
https://colab.research.google.com/

すると、以下のような画面が表示されると思います。

メニューバーの [ファイル] – [Python 3 の新しいノートブック] を選択し、BigQuery への権限を持つアカウントでログインしてください。すると、通常の Jupyter Notebook のような画面が表示されます(実際、Notebook なのですが)。

この Colaboratory を使用してデータの前処理を行います。
まず、以下のコマンド(Colaboratory では頭に “!” をつけると OS のコマンドを実行できます)でワールドカップの試合データをダウンロードします。

コード
!wget http://worldcup.sfg.io/matches

(この API から取得できるデータはアメリカの Software for Good という会社のエンジニア(https://softwareforgood.com/soccer-good-2014/) の方が無償で提供しているデータのようです。感謝します!)

ダウンロードした試合データは json 形式なので json ファイルとして読み取ります。

コード
import json
matches_data = json.load(open("matches"))

ただ、この json の形式は BigQuery で読み取れる改行区切りではないため、Python で編集します。

コード
import os
with open('matches_lines', mode='w') as f:
    f.write(os.linesep.join([str(line).replace('True', 'true').replace('False', 'false').replace('None', 'null') for line in matches_data]))

上記のコードでは、改行区切りにする他、BigQuery では認識しないため、”True”、”False”、”None” のキーワードも変換しています。そして変換後のデータを “matches_lines” というファイル名で保存します。
続いて BigQuery へ load するための認証を行います。Colaboratory では以下のように認証します。

コード
from google.colab import auth
auth.authenticate_user()

上記のコードを実行すると、認証コードを取得するための画面へのリンクが表示されるので、そのリンク先へアクセスし、BigQuery への権限を持つアカウントで認証します。その後の画面で表示される認証コードをコピーして Colaboratory のテキストエリアへ入力します。

認証できたら gcloud コマンドでデフォルトのプロジェクトID を設定し(”あなたのプロジェクトID” の部分は適切に設定してください)、

コード
!gcloud config set project あなたのプロジェクトID

以下の bq コマンドで前処理した json ファイルを BigQuery へ load します。

コード
!bq --location=US load --autodetect --source_format=NEWLINE_DELIMITED_JSON test_worldcup2018.matches_lines matches_lines

上記のコマンドが正常に実行されると BigQuery にワールドカップ 2018 ロシア大会の試合データが load されているはずです。

4. BigQuery でデータを前処理する

ようやく BigQuery へデータを load できました。ただ、この状態だと RECORD型、ARRAY型のカラムがあり、更に機械学習で推定するのに適したデータ構成でもありません。引き続き、学習データとするための前処理を BigQuery で実行します。
まずは RECORD型、ARRAY型のカラムをフラットにし、更に1試合ごとのデータ(Record) となっている構成をチームごとのデータに変換します。それには BigQuery のクエリエディタから以下の SQL を実行します(本記事での SQL は StandardSQL を使用します)。

コード
SELECT
    fifa_id,
    datetime,
    last_score_update_at,
    last_event_update_at,
    stage_name,
    status,
    home_team_country AS team_country,
    away_team_country AS opponent_team_country,
    venue,
    location,
    attendance,
    time,
    officials,
    weather,
    winner_code,
    winner,
    IF(home_team_country = winner, 1, 0) AS is_winner,
    1 AS is_home,
    home_team AS team,
    home_team_statistics AS team_statistics,
    home_team_events AS team_events,
    away_team AS opponent_team,
    away_team_statistics AS opponent_team_statistics,
    away_team_events AS opponent_team_events
FROM
    `test_worldcup2018.matches_lines`
UNION ALL
SELECT
    fifa_id,
    datetime,
    last_score_update_at,
    last_event_update_at,
    stage_name,
    status,
    away_team_country AS team_country,
    home_team_country AS opponent_team_country,
    venue,
    location,
    attendance,
    time,
    officials,
    weather,
    winner_code,
    winner,
    IF(away_team_country = winner, 1, 0) AS is_winner,
    0 AS is_home,
    away_team AS team,
    away_team_statistics AS team_statistics,
    away_team_events AS team_events,
    home_team AS opponent_team,
    home_team_statistics AS opponent_team_statistics,
    home_team_events AS opponent_team_events
FROM
    `test_worldcup2018.matches_lines`
ORDER BY fifa_id, is_home DESC
;

この SQL では、学習時の正解ラベルとなる “is_winner” を追加し、チームごとのデータとなるため、”is_home” を追加しています(away のチームは 0 となります)。上記 SQL の実行結果を “matches_teams” のテーブル名で保存してください。
続いて、学習に必要なデータに絞るため、BigQuery のクエリエディタから以下の SQL を実行します。以下の SQL では確認しやすくするため、使用しないカラムもコメントアウトして残しています。

コード
SELECT
--     fifa_id,
--     datetime,
--     last_score_update_at,
--     last_event_update_at,
--     stage_name,
--     status,
    team_country,
--     opponent_team_country,
--     venue,
--     location,
--     attendance,
--     time,
--     officials,
--     weather,
--     weather.description AS weather__description,
--     weather.wind_speed AS weather__wind_speed,
--     weather.temp_farenheit AS weather__temp_farenheit,
--     weather.temp_celsius AS weather__temp_celsius,
--     weather.humidity AS weather__humidity,
--     winner_code,
--    winner,
    is_winner,
--     is_home,
--     team,
    team.penalties AS team__penalties,
--     team.goals AS team__goals,
--     team.code AS team__code,
--     team.country AS team__country,
--     team_statistics,
    team_statistics.tactics AS team_statistics__tactics,
    team_statistics.clearances AS team_statistics__clearances,
    team_statistics.tackles AS team_statistics__tackles,
    team_statistics.balls_recovered AS team_statistics__balls_recovered,
    team_statistics.red_cards AS team_statistics__red_cards,
--     team_statistics.country AS team_statistics__country,
    team_statistics.off_target AS team_statistics__off_target,
    team_statistics.attempts_on_goal AS team_statistics__attempts_on_goal,
    team_statistics.yellow_cards AS team_statistics__yellow_cards,
    team_statistics.on_target AS team_statistics__on_target,
    team_statistics.blocked AS team_statistics__blocked,
    team_statistics.corners AS team_statistics__corners,
    team_statistics.fouls_committed AS team_statistics__fouls_committed,
    team_statistics.distance_covered AS team_statistics__distance_covered,
    team_statistics.woodwork AS team_statistics__woodwork,
    team_statistics.offsides AS team_statistics__offsides,
    team_statistics.passes_completed AS team_statistics__passes_completed,
    team_statistics.ball_possession AS team_statistics__ball_possession,
    team_statistics.num_passes AS team_statistics__num_passes,
    team_statistics.pass_accuracy AS team_statistics__pass_accuracy,
--     team_statistics.starting_eleven,
--     team_statistics.starting_eleven.shirt_number AS team_statistics__starting_eleven__shirt_number,
--     team_statistics.starting_eleven.position AS team_statistics__starting_eleven__position,
--     team_statistics.starting_eleven.captain AS team_statistics__starting_eleven__captain,
--     team_statistics.starting_eleven.name AS team_statistics__starting_eleven__name,
--     team_statistics.substitutes,
--     team_statistics.substitutes.shirt_number AS team_statistics__substitutes__shirt_number,
--     team_statistics.substitutes.position AS team_statistics__substitutes__position,
--     team_statistics.substitutes.captain AS team_statistics__substitutes__captain,
--     team_statistics.substitutes.name AS team_statistics__substitutes__name,
--     team_events,
--     team_events.time AS team_events__time,
--     team_events.player AS team_events__player,
--     team_events.type_of_event AS team_events__type_of_event,
--     team_events.id AS team_events__id,
--     opponent_team,
    opponent_team.penalties AS opponent_team__penalties,
--     opponent_team.goals AS opponent_team__goals,
--     opponent_team.code AS opponent_team__code,
--     opponent_team.country AS opponent_team__country,
--     opponent_team_statistics,
    opponent_team_statistics.tactics AS opponent_team_statistics__tactics,
    opponent_team_statistics.clearances AS opponent_team_statistics__clearances,
    opponent_team_statistics.tackles AS opponent_team_statistics__tackles,
    opponent_team_statistics.balls_recovered AS opponent_team_statistics__balls_recovered,
    opponent_team_statistics.red_cards AS opponent_team_statistics__red_cards,
--     opponent_team_statistics.country AS opponent_team_statistics__country,
    opponent_team_statistics.off_target AS opponent_team_statistics__off_target,
    opponent_team_statistics.attempts_on_goal AS opponent_team_statistics__attempts_on_goal,
    opponent_team_statistics.yellow_cards AS opponent_team_statistics__yellow_cards,
    opponent_team_statistics.on_target AS opponent_team_statistics__on_target,
    opponent_team_statistics.blocked AS opponent_team_statistics__blocked,
    opponent_team_statistics.corners AS opponent_team_statistics__corners,
    opponent_team_statistics.fouls_committed AS opponent_team_statistics__fouls_committed,
    opponent_team_statistics.distance_covered AS opponent_team_statistics__distance_covered,
    opponent_team_statistics.woodwork AS opponent_team_statistics__woodwork,
    opponent_team_statistics.offsides AS opponent_team_statistics__offsides,
    opponent_team_statistics.passes_completed AS opponent_team_statistics__passes_completed,
    opponent_team_statistics.ball_possession AS opponent_team_statistics__ball_possession,
    opponent_team_statistics.num_passes AS opponent_team_statistics__num_passes,
    opponent_team_statistics.pass_accuracy AS opponent_team_statistics__pass_accuracy
--     opponent_team_statistics.starting_eleven,
--     opponent_team_statistics.starting_eleven.shirt_number AS opponent_team_statistics__starting_eleven__shirt_number,
--     opponent_team_statistics.starting_eleven.position AS opponent_team_statistics__starting_eleven__position,
--     opponent_team_statistics.starting_eleven.captain AS opponent_team_statistics__starting_eleven__captain,
--     opponent_team_statistics.starting_eleven.name AS opponent_team_statistics__starting_eleven__name,
--     opponent_team_statistics.substitutes,
--     opponent_team_statistics.substitutes.shirt_number AS opponent_team_statistics__substitutes__shirt_number,
--     opponent_team_statistics.substitutes.position AS opponent_team_statistics__substitutes__position,
--     opponent_team_statistics.substitutes.captain AS opponent_team_statistics__substitutes__captain,
--     opponent_team_statistics.substitutes.name AS opponent_team_statistics__substitutes__name,
--     opponent_team_events,
--     opponent_team_events.time AS opponent_team_events__time,
--     opponent_team_events.player AS opponent_team_events__player,
--     opponent_team_events.type_of_event AS opponent_team_events__type_of_event,
--     opponent_team_events.id AS opponent_team_events__id
FROM
    `test_worldcup2018.matches_teams`
ORDER BY fifa_id, is_home DESC
;

上記 SQL の実行結果を “matches_data” のテーブル名で保存します。
学習で使用するために残しているカラムは、ペナルティの数やフォーメーション、ボール支配率などで、これらのデータからその試合の勝敗を予測することになります(これらのデータがわかっている上で勝敗を予測するのは、実際の状況として想定しにくいですが、今回は BigQuery ML を試すことが目的のため、ご了承いただければと思います。。)。
ちなみにフォーメーションは “4-2-3-1” などの String 型なのですが、このようなデータでもそのまま(one hot ベクトルに変換せずに) 学習データとして使用することができます。

5. BigQuery ML でモデルを作成する

これでようやく BigQuery ML でモデルを作成する準備ができました。
BigQuery ML では SQL を実行するのと同じように、クエリエディタからコマンドを実行することによって、モデルの作成、及び作成したモデルでの予測を行うことができます。今回は、ワールドカップ 2018 ロシア大会の試合データから日本代表の試合結果を予測することが目的でした。従って、学習データには日本代表以外の試合データを利用します。また、予測するのは試合の勝敗なので、前章までで作成したデータの “is_winner” を正解ラベルとします。SQL の内容は以下のようになります。

コード
CREATE MODEL `test_worldcup2018.matches_model`
OPTIONS(model_type='logistic_reg') AS
SELECT
    is_winner AS label,
    * EXCEPT (is_winner, team_country)
FROM
    `test_worldcup2018.matches_data`
WHERE
    team_country != "Japan"
;

上記の SQL では test_worldcup2018 のデータセットに matches_model の名前でモデルを作成しています。そして “OPTIONS” の “model_type” に “logistic_reg” を指定しているのでロジスティック回帰を実行します。その際、“SELECT” 句で “label” のカラムに “is_winner” を指定しています。また、”is_winner” は正解ラベルであること、“team_country” は学習データとして不要であるとして “EXCEPT” 句で除外しています。更に、日本代表の試合データを含めないように ”WHERE” 句で日本代表以外のデータを指定しています。
上記の SQL が正常に実行されると、「このステートメントで新しいモデル プロジェクトID:test_worldcup2018.matches_model が作成されました。」というメッセージが表示されると思います。ちなみに実行時間は 2分3秒ほどかかりました。
作成後、BigQuery 画面の左のテーブル一覧から作成したモデルが選択できるので、以下のようなモデルの統計情報なども確認できます。

6. BigQuery ML で予測する

それでは作成したモデルを使用して日本代表の試合の勝敗を予測してみます。BigQuery ML で予測するには以下のような SQL を実行します。

コード
SELECT
    *
FROM
  ML.PREDICT(MODEL `test_worldcup2018.matches_model`, (
SELECT
    * EXCEPT (is_winner, team_country)
FROM
    `test_worldcup2018.matches_data`
WHERE
    team_country = "Japan"))
;

この結果の一部は以下のようになります(opponent_team_country は学習データから削除していますが、以下の表では確認しやすくするために右端に追加しています)。

predicted_label predicted_label_probs.label predicted_label_probs.prob opponent_team_statistics__tactics opponent_team_statistics__clearances opponent_team_country
1 1 0.727 4-3-3 27 Senegal
0 0.272
0 1 0.435 3-4-3 18 Poland
0 0.565
0 1 0.171 3-4-3 12 Belgium
0 0.829
1 1 0.965 4-2-3-1 22 Colombia
0 0.035

“predicted_label” の列が予測結果で、1 が勝つ、0 が負けか引き分けです。今回の予測結果としては、セネガルには勝つ、ポーランドには負けか引き分け、ベルギーには負けか引き分け、コロンビアには勝つ、となりました。実際の結果と比べるとポーランド戦、ベルギー戦、コロンビア戦は正解し、セネガル戦は間違いとなります。

7. BigQuery ML でモデルを評価する

最後に、作成したモデルを評価してみます。評価するには以下の SQL を実行します。

コード
SELECT
    *
FROM
    ML.EVALUATE(MODEL `test_worldcup2018.matches_model`, (
SELECT
      is_winner AS label,
    * EXCEPT (is_winner)
FROM
    `test_worldcup2018.matches_data`
WHERE
    team_country = "Japan"))
;

上記の SQL を実行すると以下のような結果が出力されます。

precision recall accuracy f1_score log_loss roc_auc
0.5 1.0 0.75 0.667 0.523 1.002

上記の結果を検証するには混同行列を考慮する必要があります。混同行列とは、カテゴリ分類において予測結果をまとめたものになります。つまり、

  • 真陽性:正しく(真)、陽と予測した -> 正しく勝利と予測した(実際には勝利)
  • 真陰性:正しく(真)、陰と予測した -> 正しく敗北と予測した(実際には敗北)
  • 偽陽性:間違えて(偽)、陽と予測した -> 間違えて勝利と予測した(実際には敗北)
  • 偽陰性:間違えて(偽)、陰と予測した -> 間違えて敗北と予測した(実際には勝利)

の結果の数をまとめて、以下のような表にします。

予測(ML.PREDICT の出力結果)
勝利 敗北
実際の結果 勝利 1 (真陽性) 0 (偽陰性)
敗北 1 (偽陽性) 2 (真陰性)

そして確認のため、上表の混同行列から ML.EVALUATE が出力する値を求めると以下のようになります。

precision(適合率) = (真陽性) / (真陽性 + 偽陽性) = 1 / 2 = 0.5
recall(再現率) = (真陽性) / (真陽性 + 偽陰性) = 1 / 1 = 1.0
accuracy(正解率) = (真陽性 + 真陰性) / (真陽性 + 偽陽性 + 偽陽性 + 真陰性) = 3 / 4 = 0.75
f1_score(F値) = 2 * (適合率 * 再現率) / (適合率 + 再現率) = 2 * (0.5 * 1.0) / (0.5 + 1.0) = 0.667

ML.EVALUATE の結果として出力される値は、log_loss 以外は高いほど(1に近いほど) 精度が高いことを示します。今回は4件しか検証していないため信頼性は低いですが、実際にモデルをチューニングする場合にはこの結果を利用することになります。

8.まとめ

いかがだったでしょうか? 今回はワールドカップ 2018 ロシア大会の試合データを使い、BigQuery ML で日本代表の勝敗を予測してみました。前処理の煩雑さは使用するデータによりますが、前処理を行い、BigQuery に学習用データを入れてしまえば、とても簡単にロジスティック回帰が実行できました。現状として使用できるモデルが線形回帰とロジスティック回帰の2つと限られているとは言え、これだけ簡単に実行できるのは大きなメリットと言えます。今後もデータ分析の手法として機械学習を利用する場面はますます増えていくと思いますが、BigQuery ML もその選択肢の1つとして検討し、活用していただければと思います!

次の記事を読み込んでいます
次の記事を読み込んでいます
次の記事を読み込んでいます
次の記事を読み込んでいます
次の記事を読み込んでいます