中学校偏差値 (Y偏差値) を Excel にするだけ (Python)

Python で中学校偏差値 (Y偏差値) を Excel にするだけです。
Beautiful Soup 4、Pandas、OpenPyXL がインストールされていることを前提にします。

事前準備

四谷大塚の中学校偏差値一覧ページで、お好きな絞り込み条件で検索して、検索結果の各ページを 01.html, 02.html, ... に保存しておきます。お手元のブラウザの「名前を付けてページを保存」から、「ウェブページ、HTML のみ」で保存すればじゅうぶんです (もし今後仕様変更があったらわかりませんが)。スクレイピングすれば楽ですがしてよいか私は知らないので手作業で保存します。

work_dir/
  ├─ gfg202604/
  │  ├─ 01.html
  │  ├─ 02.html
  │  └─ ...
  ├─ extract.py
  └─ book.xlsx

抽出スクリプト

事前準備してから以下を実行すると、book.xlsx (空でよいので既に存在する前提) に「合不合202604」というシートを作成します (既に同名のシートがあれば置き換えます)。シートの列は以下のようになります。¹

extract.py

import re
from bs4 import BeautifulSoup, NavigableString
from pathlib import Path
import pandas as pd
from openpyxl.styles import Border


def to_df(path_html):
    soup = BeautifulSoup(path_html.read_text(encoding='utf8'), 'html.parser')
    tbody = soup.find('div', id='hensachi-result-table-wrapper').find('tbody')
    rows = []
    for tr in tbody.find_all('tr'):
        hensachi = int(tr.find('td', class_='dev-head').text.strip())
        for container in tr.find_all('div', class_='exam-of-dev-container'):
            right = container.find('div', class_='exam-of-dev-container-right')
            sex = right.find('span', class_=re.compile(r'^exam-of-dev-sex-'))
            date = right.find('span', class_='exam-of-dev-date')
            school = right.find('span', class_='exam-of-dev-school')
            href = school.find('a')['href']
            kubun_parts = [
                s.strip() for s in right.children
                if isinstance(s, NavigableString) and s.strip()
            ] + ['-']
            rows.append({
                '偏差値': hensachi,
                '学校名': school.text.strip(),
                '試験区分': kubun_parts[0],
                '性別': sex.text.strip(),
                '試験日': date.text.strip(),
                'リンク': 'https://www.yotsuyaotsuka.com' + href,
            })
    return pd.DataFrame(rows)


def get_df(target_dir):
    dfs = []
    for i in range(1, 100):
        path_html = Path(target_dir) / f'{i:02}.html'
        if not path_html.is_file():
            break
        dfs.append(to_df(path_html))
    df = pd.concat(dfs, ignore_index=True)
    return df[df['性別'] != '女子'].reset_index(drop=True)


def main():
    book = 'book.xlsx'
    target_dir = 'gfg202604'
    target_name = '合不合202604'
    df = get_df(target_dir)
    print(df.head())
    with pd.ExcelWriter(
        book, engine='openpyxl', mode='a', if_sheet_exists='replace',
    ) as writer:
        df.to_excel(writer, sheet_name=target_name, index=False)
        ws = writer.sheets[target_name]
        for cell in ws[1]:
            cell.border = Border()
        ws.column_dimensions['B'].width = 25
        ws.column_dimensions['C'].width = 20


if __name__ == '__main__':
    main()

備考

• 多くの場合、「男子 or 共学」「女子 or 共学」に絞り込むと思いますが、共学の中学校であって偏差値が男女で異なる場合、目的の性別でない方の偏差値も検索結果に出てくると思うので、適宜 get_df() の出口で排除します。
• エクセル側での処理に便利なら、main() の with 句内で以下のように学校マスタを追加できます。
• 適宜さらにマスタやセル式を追加して、下図のように学校名を C2 に入力したら模試ごとの推移を出すということもできると思います。
  • このときに C2 のデータの入力規則で「学校マスタ」を参照して参照セル範囲を Python から追記すると入力規則が消えるので注意してください (そういうことをしたいなら openpyxl でない Excel 操作パッケージが必要な気がしています)。

学校マスタ

        # 学校マスタ
        kubun_counts = df.groupby('学校名')['試験区分'].nunique()
        df_m = pd.DataFrame({'学校名': df['学校名'].unique()})
        df_m['試験区分数'] = df_m['学校名'].map(kubun_counts)
        df_m.to_excel(writer, sheet_name='学校マスタ', index=False)
        ws = writer.sheets['学校マスタ']
        for cell in ws[1]:
            cell.border = Border()
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 15

1. 関係ないですがエクセルの列をコピーして Qiita 編集画面に貼り付けると PNG 画像になることを私は今知りました。 ↩