📊

オープンデータを使ったBigQuery × Python 折れ線グラフ・スクリプト例

に公開
Python
pandas
BigQuery
Google Colaboratory
Matplotlib
tech

はじめに

本記事では、e-Stat（政府統計の総合窓口）が公開している国勢調査のデータを、分析・可視化を目的として列名の整理や型変換などを行った上で、BigQueryで集計し、Python(Colab)で折れ線グラフを作成しています。
前処理、集計はBigQueryで行い、可視化はPythonで行っています。

設計上のポイント

  • 集計処理はBigQueryに寄せ、Pythonは可視化に専念することで、
    大規模データでもスケールする構成にした
  • Nullの処理、IDの末尾に空白が入ってしまっているときの処理をすることで、
    その後の集計、可視化、分析がスムーズになるようにした
  • Query Parameters を使用し、SQLインジェクションを防ぎつつ
    可読性と再利用性を両立した
  • 可視化処理は関数化し、対象年期間やカテゴリを差し替えても
    流用できる構成にした

実装（BigQuery / Python / Google Colab）

BigQueryとの接続

# Colabでは不要な場合もあるが、ローカル実行を想定して記載
# !pip install google-cloud-bigquery
from google.colab import auth
auth.authenticate_user()

定数を定義

  • TARGET_LABOR_STATUSやSTART_YEAR、END_YEARなどを変更すれば、カテゴリや対象年期間の調整をできるように定数にしました。
# === 分析条件 ===
TARGET_LABOR_STATUS = '就業者'

START_YEAR = 1965
END_YEAR = 2010

PROJECT_ID = 'census-483003'
DATASET_ID = 'census'

BQ_PREFIX = f'{PROJECT_ID}.{DATASET_ID}'

BigQueryに指示して集計

  • Query Parameters を使うことで、SQL の安全性を保ちつつ、Python 側から柔軟に分析条件を切り替えられます。
  • テーブル名やデータセット名は Query Parameters では渡せないため、Python 側で定数として管理し、SQL を組み立てています。これにより、プロジェクト切り替えや再利用が容易になります。
  • Nullの処理やIDの末尾に空白があった場合の処理を入れていますが、扱っているデータにNullやIDの末尾に空白があったわけではありません。
sql = f'''
SELECT
  y.year,
  SUM(
    CASE
      WHEN g.gender = '男' THEN COALESCE(f.value, 0)
      ELSE 0
    END
  ) AS male,
  SUM(
    CASE
      WHEN g.gender = '女' THEN COALESCE(f.value, 0)
      ELSE 0
    END
  ) AS female
FROM `{BQ_PREFIX}.fact_Population_Census_of_Japan` f
JOIN `{BQ_PREFIX}.master_labor_status` l
  ON RTRIM(f.cat01_code) = RTRIM(l.cat01_code)
JOIN `{BQ_PREFIX}.master_gender` g
  ON RTRIM(f.cat02_code) = RTRIM(g.cat02_code)
JOIN `{BQ_PREFIX}.master_year` y
  ON RTRIM(f.time_code) = RTRIM(y.time_code)
WHERE
  y.year BETWEEN @START_YEAR AND @END_YEAR
  AND MOD(y.year, 5) = 0
  AND l.labor_status = @TARGET_LABOR_STATUS
GROUP BY y.year
ORDER BY y.year
'''

初回のみ：日本語フォント対応（Colab）

# 初回のみ実行（Colab）
!pip install japanize-matplotlib
!pip install adjustText

ライブラリのインポートと日本語フォント設定

  • japanize_matplotlib.japanize() を実行しておくことで、matplotlib で日本語ラベルが文字化けせずに表示されます。
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
from matplotlib.ticker import MaxNLocator
from adjustText import adjust_text
from matplotlib.ticker import FuncFormatter

# --- japanize_matplotlib のインストールとインポート ---
import japanize_matplotlib  # 日本語フォント対応

# --- 日本語フォント設定 ---
japanize_matplotlib.japanize()

BigQueryの集計データをdfに格納

from google.cloud import bigquery

client = bigquery.Client(project = 'census-483003')

params = {
    'START_YEAR': ('INT64', START_YEAR),
    'END_YEAR': ('INT64', END_YEAR),
    'TARGET_LABOR_STATUS': ('STRING', TARGET_LABOR_STATUS),
}

job_config = bigquery.QueryJobConfig(
    query_parameters = [
        bigquery.ScalarQueryParameter(k, t, v)
        for k, (t, v) in params.items()
    ]
)

df = client.query(sql, job_config = job_config).to_dataframe()

この SQL の JOIN 構造を図で表すと以下のようになります

可視化用の汎用関数を定義

  • 折れ線グラフを作成する関数です。
  • 国勢調査の人数は値が大きいため、可視化時には可読性を考慮して「万人」単位に変換しています。
from google.colab import files

def plot_year_gender_line(
    df,
    year_col = 'year',
    male_col = 'male',
    female_col = "female",
    title = '就業者数の推移（男女別）',
    highlight_color = '#9DB7F9',
    base_color = '#FFA66D',
    text_color = '#595959',
    y_unit = '万人',
    figsize = (10, 4),
    dpi = 150,
    linewidth = 3,
    save_path = None,
):
    df = df.copy()
    df = df.sort_values(year_col)

    # year → datetime（年単位）
    df[year_col] = pd.to_datetime(df[year_col], format = "%Y")

    fig, ax = plt.subplots(figsize = figsize, dpi = dpi)

    # 折れ線
    ax.plot(
        df[year_col],
        df[male_col],
        label = '男',
        color = highlight_color,
        linewidth = linewidth,
    )

    ax.plot(
        df[year_col],
        df[female_col],
        label = '女',
        color = base_color,
        linewidth = linewidth,
    )

    texts = []

    # 右端 x 座標
    x_last = df[year_col].iloc[-1]

    # ラベルの初期位置
    texts.append(
        ax.text(x_last, df[male_col].iloc[-1], '男', color = highlight_color)
    )
    texts.append(
        ax.text(x_last, df[female_col].iloc[-1], '女', color = base_color)
    )

    # adjustText で重なり回避
    adjust_text(
        texts,
        only_move = {'points':'y', 'text':'y'},
        arrowprops = dict(arrowstyle = '-', color = 'lightgray', lw = 0.5)
    )

    # デザイン
    for spine in ax.spines.values():
        spine.set_visible(False)

    ax.yaxis.set_major_locator(MaxNLocator(nbins = 4))

    # 目盛り（x・y）
    ax.tick_params(
        axis = 'both',
        length = 0,
        colors = text_color,
    )

    # グリッド
    ax.yaxis.grid(True, color = 'lightgray', linewidth = 0.5)
    ax.xaxis.grid(False)

    # y軸：万人表示
    ax.yaxis.set_major_formatter(
        FuncFormatter(lambda x, pos: f'{int(x/10000):,}')
    )
    # 単位
    # ax.yaxis.set_major_formatter(mtick.StrMethodFormatter('{x:,.0f}'))

    # タイトル
    ax.set_title(
        title,
        fontsize = 12,
        fontweight = 'bold',
        pad = 8,
        color = text_color,
    )
    
    # 単位表示（y軸上・横書き）
    ax.text(
        -0.05, 1.02, f'({y_unit})',
        transform = ax.transAxes,
        ha = 'left',
        va = 'bottom',
        fontsize = 10,
        color = text_color,
    )

    # 凡例
    # ax.legend(frameon = False, labelcolor = text_color)

    plt.tight_layout()

    if save_path:
        fig.savefig(save_path, bbox_inches = 'tight')
        files.download(save_path)

    plt.show()

    return fig, ax

グラフ描画

_ = plot_year_gender_line(
    df,
    title = '1965–2010年 就業者数の推移（男女別）',
    save_path = None, # 'employment_trend_1965_2010.png'
)

コードの出力

以下は、1965年から2010年の間、男女別、就業者の折れ線グラフです。

Discussion