Create a record table from JFL match results

Introduction

Create standings from JFL match results continued

Scraping

Same as the scraping of the previous Create standings from JFL match results

import requests
from bs4 import BeautifulSoup

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

r = requests.get(url)
r.raise_for_status()

soup = BeautifulSoup(r.content, "html.parser")

data = []

for table in soup.find_all("table", class_="table-data"):

    trs = table.find_all("tr")

    th = int(trs[0].th.get_text(strip=True).strip("Section"))

    for i, tr in enumerate(trs[1:], 1):

        tds = [td.get_text(strip=True) for td in tr.find_all("td")]

        data.append([th, i] + tds)

Data wrangling

import pandas as pd

df = pd.DataFrame(
    data, columns=["section", "number", "date", "time", "home", "Score", "Away", "Stadium", "Remarks"]
)

df.set_index(["section", "number"], inplace=True)

df

df_score = (
    df["Score"].str.split("-", expand=True).rename(columns={0: "Home score", 1: "Away score"})
)

df_score["Home score"] = pd.to_numeric(df_score["Home score"], errors="coerce").astype("Int64")
df_score["Away score"] = pd.to_numeric(df_score["Away score"], errors="coerce").astype("Int64")

df1 = pd.concat([df, df_score], axis=1).dropna(subset=["Home score", "Away score"])

#Home results only
df_home = df1.loc[:, ["home", "Away", "home得点", "Away得点"]].copy()
df_home.rename(
    columns={"home": "Team name", "Away": "Opponent", "homescore": "score", "Awayscore": "Conceded"}, inplace=True
)
df_home["War"] = "H"
df_home.head()

#Away results only
df_away = df1.loc[:, ["Away", "home", "Away得点", "home得点"]].copy()
df_away.rename(
    columns={"Away": "Team name", "home": "Opponent", "Awayscore": "score", "homescore": "Conceded"}, inplace=True
)
df_away["War"] = "A"
df_away.head()

#Combine home and away
df_total = pd.concat([df_home, df_away])

df_total

jfl_2020 = [
    "Honda FC",
    "Sony Sendai FC",
    "Tokyo Musashino City FC",
    "Tegevajaro Miyazaki",
    "Honda Lock SC",
    "Verspah Oita",
    "FC Osaka",
    "MIO Biwako Shiga",
    "Veertien Mie",
    "FC Maruyasu Okazaki",
    "Suzuka Point Getters",
    "Line mail Aomori",
    "Nara club",
    "Matsue City FC",
    "Iwaki FC",
    "Kochi United SC",
]

df_total

df_total["result"] = df_total.apply(
    lambda x: f'{x["score"]}△{x["Conceded"]}'
    if x["score"] == x["Conceded"]
    else f'{x["score"]}○{x["Conceded"]}'
    if x["score"] > x["Conceded"]
    else f'{x["score"]}●{x["Conceded"]}',
    axis=1,
)

df_total

#Battle record table total
pv_senseki = df_total.pivot(values="result", index=["Team name", "War"], columns="対War相手").fillna("")

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

jfl_senseki = pv_senseki.reindex(new_idx, columns=jfl_2020)

jfl_senseki

print(jfl_senseki.to_markdown())
Honda FC Sony Sendai FC Tokyo Musashino City FC Tegevajaro Miyazaki Honda Lock SC Verspah Oita FC Osaka MIO Biwako Shiga Veertien Mie FC Maruyasu Okazaki Suzuka Point Getters Line mail Aomori Nara club Matsue City FC Iwaki FC Kochi United SC
('Honda FC', 'H') 1△1 3○1 1△1 1○0
('Honda FC', 'A') 4○0 3○0 1△1 1○0
('Sony Sendai FC', 'H') 0●4 1△1 3○1
('Sony Sendai FC', 'A') 0●2 4○2 2○1 2○0 1●2
('Tokyo Musashino City FC', 'H') 2○0 1●2 2○1
('Tokyo Musashino City FC', 'A') 1△1 0●1 1△1
('Tegevajaro Miyazaki', 'H') 1△1 1●2 2○0
('Tegevajaro Miyazaki', 'A') 2○1 4○1 0△0 1○0
('Honda Lock SC', 'H') 0●3 1●2 0●4 0●3
('Honda Lock SC', 'A') 1△1 3○1 2○1 1○0
('Verspah Oita', 'H') 2●4 1○0 1●2 4○1
('Verspah Oita', 'A') 2○1 2○0 2○1
('FC Osaka', 'H') 1△1 1○0 1●2 0△0 2○1
('FC Osaka', 'A') 1●2 4○0 2●3
('MIO Biwako Shiga', 'H') 1●4 3△3 6○0 1○0
('MIO Biwako Shiga', 'A') 1●3 0●1 2○1 3○1
('Veertien Mie', 'H') 0△0 0●2 1●2 3○2
('Veertien Mie', 'A') 1△1 2○1 0●1 1●2
('FC Maruyasu Okazaki', 'H') 1●2 1●3 1△1 0●2
('FC Maruyasu Okazaki', 'A') 0●1 0△0 2●3 2○0
('Suzuka Point Getters', 'H') 0●1 1○0 0●1 1●2 1○0
('Suzuka Point Getters', 'A') 3△3 1○0 2△2
('Line mail Aomori', 'H') 0●2 3○0 1△1
('Line mail Aomori', 'A') 0●1 2○1 0●6 1△1
('Nara club', 'H') 1●2 1○0 0●1 1△1
('Nara club', 'A') 1△1 2○1 3○0 1●2
('Matsue City FC', 'H') 2○1 1●3 2○1 2○1
('Matsue City FC', 'A') 0●2 1●2 0●1 2○1 0●3
('Iwaki FC', 'H') 1●2 3○2 2○1 4○3
('Iwaki FC', 'A') 1●4 2○0 0●1 1△1
('Kochi United SC', 'H') 1△1 0●1 0●2 2△2
('Kochi United SC', 'A') 1●3 1△1 1●2 3●4

Recommended Posts

Create a record table from JFL match results
Create a standings from JFL match results
Create a score ranking from JFL match results
Try to create a battle record table with matplotlib from the data of "Schedule-kun"
Create a pandas Dataframe from a string.
How to create a clone from Github
Create a deb file from a python package
How to create a repository from media
Create a dataframe from excel using pandas
Create a correspondence table between EC number and Uniprot entry from enzyme.dat
Edit Excel from Python to create a PivotTable
How to create a function object from a string
Create a C array from a Python> Excel sheet
Create a game UI from scratch with pygame2!
Create a New Todoist Task from Python Script
[python] Create table from pandas DataFrame to postgres
Create a phylogenetic tree from Biopyton using ClustalW2
Create a decision tree from 0 with Python (1. Overview)
Create a datetime object from a string in Python (Python 3.3)
Create a Word Cloud from an academic program
Create a table of contents with IPython notebook
I wrote Python code to create a table (view) dependency diagram (PlantUML) from SQL
I tried to create a table only with Django
Why not create a stylish table easily with Python?
Python script to create a JSON file from a CSV file
How to create a kubernetes pod from python code
Create a dictionary by searching the table using sqlalchemy
Create a machine learning environment from scratch with Winsows 10
I made a tool to automatically generate a simple ER diagram from the CREATE TABLE statement
[Python] How to create a table from list (basic operation of table creation / change of matrix name)