pip3 install pandas
pip3 install beautifulsoup4
pip3 install html5lib
pip3 install lxml
pip3 install gspread
pip3 install oauth2clinet
pip3 install tqdm
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=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
