2021セリーグ優勝争いを比較してみた

#本記事に関して
自身の練習用のページになります！
アドバイスがありましたら、よろしくお願いいたします！

# はじめに
今回は、SQLとGoogl BigQueryの練習をかねて、
大混戦だったセリーグの優勝争いの勝敗を比較してみました！

始めてGoogl BigQueryを扱ってみました。
個人的ルールとして6時間という時間制限を設けてみました。

##簡単なあらすじ
1.Pythonにて試合結果をスクレイピング
2.Googl BigQueryにてデータを抽出・加工
3.データポータルにて可視化

# 1.Pythonにて試合結果をスクレイピング
データ収集のために、試合結果の一覧サイトから以下のデータを取得！
中止試合などデータの加工もできましたが、最低限に抑えSQLで実行しております。
セリーグ6チーム対象
[取得元のサイトはこちら！](https://baseball-freak.com/game/carp.html)

<details><summary><font color="Red">__コードはこちら__</font></summary><div>

```python

# インストール
!pip install requests
!pip install beautifulsoup4

import requests
from bs4 import BeautifulSoup
import pandas as pd
from sklearn.preprocessing import OneHotEncoder
import datetime
import re
from google.colab import drive 
drive.mount('/content/drive')


url = 'https://baseball-freak.com/game/giants.html'
dfs = pd.read_html(url,
                   header=0,
                   index_col= 0
                  )
dfs = dfs[0]
dfs = dfs.drop(["日付.1","先発投手","責任投手"],axis = 1)
dfs = dfs.reset_index()

dfs["勝敗"] = dfs["勝敗"].map({"-":3,"△":2,"○":1,"●":0})
dfs["日付"] = dfs["日付"].map(lambda x: "21-{}-{}".format(re.findall(r"\d+", x)[0], re.findall(r"\d+", x)[1]))

dfs["split"] = dfs["スコア"].str.split("-")
dfs["get"] = dfs["split"].str.get(0)
dfs["lost"] = dfs["split"].str.get(1)
dfs = dfs.drop("split",axis = 1)
dfs["team"] = "巨人"

dfs = dfs.rename(columns={'日付': 'date',
                          '勝敗':'game',
                          'スコア':'score',
                          '対戦相手':'vs',
                          '球場':'location',
                          '開始':'start'})

dfs.head()
dfs.to_csv("21_giatns.csv",encoding="utf_8_sig")

```
</div></details>

__取得データ一覧__

|取得データ       |説明        |   
|:-----------------|:------------------|
|date       |試合日         |2-385      |
|game    |試合結果を数値で表記<br>0 = 敗戦 <br> 1 = 勝利 <br> 2 = 引分 <br> 3 = 中止 <br>  |
|score |結果スコア   |
|vs   |対戦相手   |
|location |試合球場 → home ＆ away の判別     |
|start   |試合開始時間     |
|get|teamの得点 |
|lost|teamの失点 (vsの得点)     |
|team|~~チームのデータ|

__↓サンプル↓__
![6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/38d22a4b-7678-4b67-8d7e-bf21d5b2cba9.png)

得点・失点からデータを確認するときのために、分けました。
また、この後データを1つにまとめるため、どのチームのデータなのか「team」にて表記。


#2.Googl BigQueryにてデータを抽出・加工
Googl BigQueryの使い方にまず苦戦しました。
そのため、書籍[『 集中演習 SQL入門 Google BigQueryではじめるビジネス データ分析 』](https://prtimes.jp/main/html/rd/p/000000018.000057738.html)を参考にさせて頂きました!!

抽出の前に先ほど6チーム分のデータをわざと1つのテーブルにまとめました！
そして、全試合のデータが含まれた`21`テーブルを作成

```
# すべてのテーブルを縦に結合
SELECT * FROM `baseball-331309.2021.carp` AS `21`
UNION ALL
SELECT * FROM `baseball-331309.2021.dragons`
UNION ALL
SELECT * FROM `baseball-331309.2021.giants`
UNION ALL
SELECT * FROM `baseball-331309.2021.swallows`
UNION ALL
SELECT * FROM `baseball-331309.2021.tigers`
UNION ALL
SELECT * FROM `baseball-331309.2021.yokomaha`
```

__様子__
![3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/012d7978-d285-c840-5d4c-6e1ffafa3906.png)



そして、メインとして4つのデータを抽出しました！（全て1チーム毎のデータ）

1.シーズン勝敗データ (勝利・敗北・引き分け) / 中止を除く
2.locationごとの勝敗
3.ホームゲームの勝敗
4.アウェイゲームの勝敗

####1.シーズン勝敗データ (勝利・敗北・引き分け) / 中止を除く
必要なデータのみ抽出
中止ゲームは振替日にてゲームを行うために除く
0,1,2のグループで試合結果が表記されるが、そのカウントを`count`として表記。

```
SELECT
game,
COUNT(*) AS `count`
FROM `baseball-331309.2021.21`
WHERE 
team = "広島" #広島の場合
AND
game != 3
GROUP BY
game;
```
![1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/2a713f8c-c734-e631-45aa-a5ea319f5bd7.png)



####2.locationごとの勝敗
試合を行った球場ごと、そしてhome＆awayについて確認するため。
ある記事ではスポーツにおけるホームゲームでの勝率はアウェイより高いらしい！


```
SELECT
team,
game,
location,
COUNT(*) AS `count`
FROM `baseball-331309.2021.21`
WHERE 
team = "広島" #広島の場合
AND
game != 3
GROUP BY
team,
game,
location
ORDER BY game ASC;
```
![5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/bfe01150-bfae-9002-73f5-22b81e200bd2.png)



####3.ホームゲームの勝敗
ホームゲームでの勝敗の差を調べる。
そのため、条件にホーム球場の名前を記載

```
SELECT
game,
location,
COUNT(*) AS `count`
FROM `baseball-331309.2021.21`
WHERE 
team = "広島" #広島の場合
AND 
location = "マツダスタジアム" #広島の場合
AND
game != 3
GROUP BY
game,
location
ORDER BY game ASC;
```
![3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/17933aa6-76f5-d4be-928e-af46eef8baa6.png)


####4.アウェイゲームの勝敗
最後にアウェイの試合を調査。
先ほどとは反対に`!=`にてホーム球場以外のデータを調査

```
SELECT
game,
COUNT(*) AS `count`
FROM `baseball-331309.2021.21`
WHERE 
team = "広島" #広島の場合
AND 
location != "マツダスタジアム" #広島の場合
AND
game != 3
GROUP BY
game
```
![4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/b48b1cd6-9e3f-20b0-6a6c-455fb5226dbd.png)


#3.データポータルにて可視化
結果を示す！
今回は、1位のヤクルト2位のタイガース、そして応援しているカープの3データ
取得したデータをそれぞれテーブルに保存
保存データをもとにデータポータルにて可視化してみた！

#####ヤクルト
![swallows-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/4c1be971-7466-daa3-e391-c1f17cfa62fc.png)

#####タイガース
![tigers-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/96dc3243-b006-adfe-2236-5882628dbd6c.png)

#####カープ
![carp-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1971455/99dfec25-e96a-3d8f-e7e7-8affd2a0446a.png)


1位のヤクルト2位のタイガースを比較してみて
特出すべきなのは、引き分けと負けの数だろうか、
2位タイガースの方が勝利数が多いのだ！
ヤクルトは負けそうな試合を引き分けに持ち込み、勝ち点を伸ばしたようだ。
また、ヤクルトは下位チームである、「カープ」「横浜」「中日」から勝ち点を多く上げている。
これは、偶然か戦力か判断は難しいが、勝てるチームいは勝つという年間的な戦略なのか！？

また、2位のタイガースがホームゲームで勝率が5割を切っているように
応援が禁止されていた今シーズン
ホーム球場がホームチームにとって戦いやすいことには変わりないが、
ホームで力が出る(アドバンテージ)のようなものは少なかったのかもしれない。

Python、SQLを比較して
Pythonには自由度が高いと感じた！自由にデータを書き替えられ調整も容易い一方コードが複雑。
SQLはデータの誤操作が少なく、社内で人のコードを見る分には便利そうだ！