7-7. 【データ分析・業務自動化】選挙出口調査のクロス集計＆スプレッドシート動的レポート自動生成

第3章（標準ライブラリ・データ処理）および第5章（データ分析・可視化）で学んだ pandas によるデータ加工テクニックと openpyxl によるExcel操作技術を応用し、「選挙出口調査のクロス集計＆スプレッドシート動的レポート自動生成ツール」を構築します。

大量の出口調査アンケート結果（CSVデータ）を元に、年代別・支持政党別の投票動向を自動解析し、Excel上で再集計・条件変更が可能な「動的関数（COUNTIFS）埋め込みレポート」を出力します。

1. アプリケーションの全体設計と要件

本ツールの主な要件と実務的なポイントは以下の通りです。

  1. 生データの自動クレンジング: pandas を用いて、欠損値の除去や年代区分の正規化処理を行う。
  2. クロス集計処理: pd.crosstab による集計結果の算出。
  3. Excelへの動的関数（COUNTIFS）埋め込み: 単に計算結果の静的数値を貼り付けるのではなく、openpyxl を使ってExcel数式（COUNTIFS）を動的に埋め込み、後からExcel上で条件を調整できるようにする。
  4. デザイン整形: 見出しの装飾、罫線、パーセンテージ表記のフォーマット適用。

2. 実装コード（exit_poll_analyzer.py）

以下のコードを exit_poll_analyzer.py というファイル名で保存し、手元で実行してみましょう（事前準備: pip install pandas openpyxl）。

Python

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# --- 1. サンプルデータ生成 (出口調査結果のシミュレーション) ---
def generate_sample_data(filename="exit_poll_raw.csv"):
    np.random.seed(42)
    n_samples = 500
    
    ages = np.random.choice(["20代", "30代", "40代", "50代", "60代以上"], size=n_samples, p=[0.15, 0.20, 0.25, 0.20, 0.20])
    parties = np.random.choice(["A党", "B党", "C党", "無党派"], size=n_samples, p=[0.35, 0.25, 0.15, 0.25])
    voted_candidates = np.random.choice(["候補者Alpha", "候補者Beta", "候補者Gamma"], size=n_samples, p=[0.45, 0.40, 0.15])
    
    df = pd.DataFrame({
        "respondent_id": range(1, n_samples + 1),
        "age_group": ages,
        "support_party": parties,
        "voted_candidate": voted_candidates
    })
    df.to_csv(filename, index=False, encoding="utf-8-sig")
    print(f"📁 サンプル生データを作成しました: {filename}")

# --- 2. openpyxl による動的レポート作成 ---
def create_dynamic_report(csv_filename="exit_poll_raw.csv", output_excel="exit_poll_report.xlsx"):
    # データ読み込み
    df = pd.read_csv(csv_filename)
    
    wb = Workbook()
    
    # -------------------------------------------------------------
    # シート1: 生データ (Raw_Data)
    # -------------------------------------------------------------
    ws_raw = wb.active
    ws_raw.title = "Raw_Data"
    
    # ヘッダー書き込み
    headers = ["回答ID", "年代", "支持政党", "投票候補者"]
    ws_raw.append(headers)
    
    # データ行書き込み
    for row in df.itertuples(index=False):
        ws_raw.append(list(row))

    # -------------------------------------------------------------
    # シート2: 年代別×投票候補者 クロス集計レポート (Cross_Summary)
    # -------------------------------------------------------------
    ws_summary = wb.create_sheet(title="Cross_Summary")
    
    # タイトル
    ws_summary["A1"] = "【出口調査】年代別 投票候補者クロス集計表 (COUNTIFS動的埋め込み)"
    ws_summary["A1"].font = Font(size=14, bold=True)
    
    age_groups = ["20代", "30代", "40代", "50代", "60代以上"]
    candidates = ["候補者Alpha", "候補者Beta", "候補者Gamma"]
    
    # テーブルヘッダーの配置
    ws_summary["A3"] = "年代 / 候補者"
    for col_idx, cand in enumerate(candidates, start=2):
        cell = ws_summary.cell(row=3, column=col_idx, value=cand)
    ws_summary.cell(row=3, column=len(candidates) + 2, value="合計")
    
    # 動的数式（COUNTIFS）の埋め込み
    # Raw_Dataシートの B列: 年代, D列: 投票候補者
    start_row = 4
    for r_idx, age in enumerate(age_groups, start=start_row):
        ws_summary.cell(row=r_idx, column=1, value=age)
        
        for c_idx, cand in enumerate(candidates, start=2):
            # COUNTIFS(Raw_Data!$B:$B, 年代セル, Raw_Data!$D:$D, 候補者セル)
            formula = f'=COUNTIFS(Raw_Data!$B:$B, $A{r_idx}, Raw_Data!$D:$D, {get_column_letter(c_idx)}$3)'
            ws_summary.cell(row=r_idx, column=c_idx, value=formula)
            
        # 行合計数式 (SUM)
        last_cand_col = get_column_letter(len(candidates) + 1)
        ws_summary.cell(row=r_idx, column=len(candidates) + 2, value=f'=SUM(B{r_idx}:{last_cand_col}{r_idx})')
    
    # 列合計行の追加
    total_row = start_row + len(age_groups)
    ws_summary.cell(row=total_row, column=1, value="合計")
    for c_idx in range(2, len(candidates) + 3):
        col_letter = get_column_letter(c_idx)
        ws_summary.cell(row=total_row, column=c_idx, value=f'=SUM({col_letter}4:{col_letter}{total_row-1})')

    # -------------------------------------------------------------
    # デザイン修飾（スタイリング）
    # -------------------------------------------------------------
    header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
    header_font = Font(color="FFFFFF", bold=True)
    thin_border = Border(
        left=Side(style='thin', color='D9D9D9'),
        right=Side(style='thin', color='D9D9D9'),
        top=Side(style='thin', color='D9D9D9'),
        bottom=Side(style='thin', color='D9D9D9')
    )
    
    for row in ws_summary.iter_rows(min_row=3, max_row=total_row, min_col=1, max_col=len(candidates)+2):
        for cell in row:
            cell.border = thin_border
            cell.alignment = Alignment(horizontal="center", vertical="center")
            if cell.row == 3 or cell.column == 1 or cell.row == total_row:
                if cell.row == 3:
                    cell.fill = header_fill
                    cell.font = header_font
                else:
                    cell.font = Font(bold=True)
    
    wb.save(output_excel)
    print(f"📊 動的数式付きExcelレポートを生成しました: {output_excel}")

if __name__ == "__main__":
    generate_sample_data()
    create_dynamic_report()

3. コードのポイント解説（第3章・第5章の応用）

  1. pandas によるデータ受け渡し
    • CSVからパースした大量データをオブジェクトとして保持し、そのまま openpyxl のセルへ転送します。
  2. openpyxl を用いたExcel数式の動的設定
    • 単に計算済みの数値を書き込むのではなく、=COUNTIFS(Raw_Data!$B:$B, $A4, ...) のような数式文字列をセルに割り当てています。
    • これにより、ユーザーがExcel側で Raw_Data シートの数値を変更したり行を追加した際、集計結果が自動的にリアルタイム再計算されます。
  3. スタイリングと自動整形
    • PatternFill や Font、Border を指定して、見やすい業務クオリティのレポートデザインを自動構築します。

まとめ

本記事では、pandas でのデータ集計と openpyxl による動的関数埋め込みを組み合わせた「選挙出口調査の動的集計レポート自動生成ツール」を構築しました。

コメント

タイトルとURLをコピーしました