JFLランキング作成（GoogleDrive）

pip3 install pandas
pip3 install beautifulsoup4
pip3 install html5lib
pip3 install lxml
pip3 install gspread
pip3 install oauth2clinet
pip3 install tqdm

# -*- coding: utf-8 -*-

import gspread
import pandas as pd
import requests
from bs4 import BeautifulSoup
from oauth2client.service_account import ServiceAccountCredentials

url = "http://www.jfl.or.jp/jfl-pc/view/s.php?a=1411&f=2019A001_spc.html"

dfs = pd.read_html(url, na_values="-")

print(len(dfs))


dfs_h = []

for i in dfs:
    i.columns = ["日にち", "時間", "ホーム", "スコア", "アウェイ", "スタジアム", "備考"]
    dfs_h.append(i)


df = pd.concat(dfs_h, keys=[i for i in range(1, len(dfs_h) + 1)], names=["節", "番号"])


# 備考を除去
df.drop("備考", axis=1, inplace=True)

# スコアがないものを除去
df.dropna(subset=["スコア"], inplace=True)

# スコアを分割
df2 = df["スコア"].str.split("-", expand=True)
df2 = df2.astype(int)
df2.columns = ["ホーム得点", "アウェイ得点"]

# スコアを削除
df1 = df.drop("スコア", axis=1)

# スコアを分割、スコアを削除、結合
df = pd.concat([df, df2], axis=1)

# ホーム
df_home = df.loc[:, ["ホーム", "アウェイ", "ホーム得点", "アウェイ得点"]].reindex()
df_home.columns = ["チーム名", "対戦相手", "得点", "失点"]
df_home["戦"] = "H"
df_home.head()

# アウェイ
df_away = df.loc[:, ["アウェイ", "ホーム", "アウェイ得点", "ホーム得点"]]
df_away.columns = ["チーム名", "対戦相手", "得点", "失点"]
df_away["戦"] = "A"
df_away.head()

df_total = pd.concat([df_home, df_away])


# 得失点を計算
df_total["得失点"] = df_total["得点"] - df_total["失点"]
df_total.head()


# 勝敗を追加
def win_or_loss(x):
    if x["得点"] > x["失点"]:
        return "勝利"
    elif x["得点"] < x["失点"]:
        return "敗戦"
    else:
        return "引分"


df_total["勝敗"] = df_total.apply(lambda x: win_or_loss(x), axis=1)


# 勝点を追加
def win_point(x):
    if x["得点"] > x["失点"]:
        return 3
    elif x["得点"] < x["失点"]:
        return 0
    else:
        return 1


df_total["勝点"] = df_total.apply(lambda x: win_point(x), axis=1)

# 得点・失点・得失点・勝点　集計
pv_score = df_total.pivot_table(
    values=["得点", "失点", "得失点", "勝点"], index="チーム名", aggfunc=sum
)


# 集計用にカウント追加
df_total["カウント"] = 1

# 得点・失点・得失点・勝点　集計
pv_wlcnt = df_total.pivot_table(
    values="カウント", index="チーム名", columns=["戦", "勝敗"], aggfunc=sum, fill_value=0
)


# 列名変更
pv_wlcnt.columns = ["勝利A", "引分A", "敗戦A", "勝利H", "引分H", "敗戦H"]

# 合計追加
pv_wlcnt["勝利"] = pv_wlcnt["勝利H"] + pv_wlcnt["勝利A"]
pv_wlcnt["引分"] = pv_wlcnt["引分H"] + pv_wlcnt["引分A"]
pv_wlcnt["敗戦"] = pv_wlcnt["敗戦H"] + pv_wlcnt["敗戦A"]

# 試合数追加
pv_wlcnt["試合数"] = pv_wlcnt["勝利"] + pv_wlcnt["引分"] + pv_wlcnt["敗戦"]


df3 = df_total.copy()

# 評価値を作成
df3["評価値"] = (df3["勝点"] * 10000) + (df3["得失点"] * 100) + df3["得点"]


# 評価値集計
pv_eval = df3.pivot_table(
    values="評価値", index="チーム名", columns="節", aggfunc=sum, fill_value=0
)


# 累計評価値
pvc_eval = pv_eval.apply(lambda d: d.cumsum(), axis=1)


# 累計評価値をランキングに変換
df_rank = pvc_eval.rank(ascending=False, method="min").astype(int)
df_rank

# 前ランキングとの差分
df4 = df_rank.copy()
df_diff = df4.diff(axis=1).fillna(0)


# 差分を三角に変換
def arrow_up(x):
    if x > 0:
        return "▼"
    elif x < 0:
        return "▲"
    else:
        return "－"


s1 = df_diff.iloc[:, -1].apply(lambda x: arrow_up(x))
s1.name = "前節"


df5 = pd.concat([pv_score, pv_wlcnt], axis=1).join(s1)


# 評価値を作成
df5["評価値"] = (df5["勝点"] * 10000) + (df5["得失点"] * 100) + df5["得点"]

# ランキング
df5["順位"] = df5["評価値"].rank(ascending=False, method="min").astype(int)

# 順位で昇順
df5.sort_values(["順位"], inplace=True)


# チーム名をインデックスから解除
df6 = df5.reset_index()
jfl_rank = df6.loc[
    :,
    [
        "前節",
        "順位",
        "チーム名",
        "勝点",
        "試合数",
        "勝利",
        "勝利H",
        "勝利A",
        "引分",
        "引分H",
        "引分A",
        "敗戦",
        "敗戦H",
        "敗戦A",
        "得失点",
        "得点",
        "失点",
    ],
]


# 結果を追加
def match_result(x):
    if x["得点"] > x["失点"]:
        return "{}○{}".format(x["得点"], x["失点"])
    elif x["得点"] < x["失点"]:
        return "{}●{}".format(x["得点"], x["失点"])
    else:
        return "{}△{}".format(x["得点"], x["失点"])


df_total["結果"] = df_total.apply(lambda x: match_result(x), axis=1)


# 戦績表　集計
pv_senseki = df_total.pivot_table(
    values="結果", index=["チーム名", "戦"], columns="対戦相手", aggfunc=sum, fill_value=""
)


jfl_team = [
    "Ｈｏｎｄａ ＦＣ",
    "ＦＣ大阪",
    "ソニー仙台ＦＣ",
    "ＦＣ今治",
    "東京武蔵野シティＦＣ",
    "ＭＩＯびわこ滋賀",
    "奈良クラブ",
    "ヴェルスパ大分",
    "ラインメール青森",
    "ヴィアティン三重",
    "テゲバジャーロ宮崎",
    "ＦＣマルヤス岡崎",
    "ホンダロックＳＣ",
    "流経大ドラゴンズ龍ケ崎",
    "松江シティＦＣ",
    "鈴鹿アンリミテッド",
]

new_idx = pd.MultiIndex.from_product(
    [jfl_team, ["H", "A"]], names=pv_senseki.index.names
)

jfl_senseki = pv_senseki.reindex(new_idx, columns=jfl_team)
jfl_senseki.fillna("", inplace=True)


# スプレッドシート

scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/drive",
]

credentials = ServiceAccountCredentials.from_json_keyfile_name(
    "jfl-ranking.json", scope
)
gc = gspread.authorize(credentials)

workbook = gc.open_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxxxx")

# ランキング更新

rank_data = jfl_rank.values.flatten().tolist()

worksheet = workbook.worksheet("ランキング")

cell_list = worksheet.range("A2:Q17")

for cell, v in zip(cell_list, rank_data):
    cell.value = v

worksheet.update_cells(cell_list)

# 戦績表更新

senseki_data = jfl_senseki.values.flatten().tolist()

worksheet = workbook.worksheet("戦績表")

cell_list = worksheet.range("C2:R33")

for cell, v in zip(cell_list, senseki_data):
    cell.value = v

worksheet.update_cells(cell_list)

# 順位を取得

imabari = df5.at['ＦＣ今治', '順位']
imabari


# url = 'http://www.jfl.or.jp/jfl-pc/view/s.php?a=1436&f=2019A00106_spc.html'
url = 'http://www.jfl.or.jp/jfl-pc/view/s.php?a=871&f=top_spc.html'

headers = {
    'User-Agent':
    'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'
}

r = requests.get(url, headers=headers)
soup = BeautifulSoup(r.content, 'html5lib')

for i in soup.select('table.score-table'):

    team = [
        j.get_text(strip=True)
        for j in i.select('tr:nth-of-type(2) > th[class^="score-team"]')
    ]

    score = [j.text for j in i.select('tr:nth-of-type(2) > td.score-finish')]

    if 'ＦＣ今治' in team:

        # スコアを数字に変換
        temp = list(map(int, score))

        # ホーム
        if 'ＦＣ今治' in team[0]:

            place = 'ホーム'
            opponent = team[1]
            home = temp[0]
            away = temp[1]

        # アウェイ
        else:

            place = 'アウェイ'
            opponent = team[0]
            home = temp[1]
            away = temp[0]

        # 勝利
        if home > away:

            res = '勝利しました'

        # 敗北
        elif home < away:

            res = '負けました'

        # 引分
        else:

            res = '引分ました'

        print(
            '今日の{0}との{1}戦は{2}－{3}で{4}\n現在の順位は{5}位です\n#FC今治\nhttps://docs.google.com/spreadsheets/d/e/2PACX-1vRHxFkvm1VLkykuCINoEmRHIfPE5gJB_Jb6uUsrS0lXdlg354HC_AEwnNQD_TAlgC3v1yFfFNW4ETpD/pubhtml'
            .format(opponent, place, home, away, res, imabari))
        break