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

!wget http://worldcup.sfg.io/matches

import json
matches_data = json.load(open("matches"))

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]))

from google.colab import auth
auth.authenticate_user()

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

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

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
;

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
;

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"
;

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

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"))
;

SELECT
    *
FROM ML.TRAINING_INFO(MODEL `test_worldcup2018.matches_model`)
;

SELECT
    *
FROM ML.FEATURE_INFO(MODEL `test_worldcup2018.matches_model`)
;

SELECT
    * 
FROM
    ML.WEIGHTS(MODEL `test_worldcup2018.matches_model`)
ORDER BY
    ABS(weight) DESC
;