Reformat Department Table

アルゴリズム概要

縦持ち（EAV形式）で格納された Department テーブルを、部門ID × 月 の2次元テーブル（横持ち）にピボット変換する問題です。 SQLでは GROUP BY id と 条件付き集計 MAX … FILTER を組み合わせて12列を一度に展開します。

ステップバイステップ解説

SQL実装（PostgreSQL 16.6+）

WITH pivoted AS (
  SELECT
    id,
    MAX(revenue) FILTER (WHERE month = 'Jan') AS "Jan_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Feb') AS "Feb_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Mar') AS "Mar_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Apr') AS "Apr_Revenue",
    MAX(revenue) FILTER (WHERE month = 'May') AS "May_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Jun') AS "Jun_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Jul') AS "Jul_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Aug') AS "Aug_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Sep') AS "Sep_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Oct') AS "Oct_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Nov') AS "Nov_Revenue",
    MAX(revenue) FILTER (WHERE month = 'Dec') AS "Dec_Revenue"
  FROM Department
  GROUP BY id
)
SELECT *
FROM pivoted
ORDER BY id;

代替：CASE WHEN（互換性重視）

SELECT
  id,
  MAX(CASE WHEN month = 'Jan' THEN revenue END) AS "Jan_Revenue",
  MAX(CASE WHEN month = 'Feb' THEN revenue END) AS "Feb_Revenue",
  MAX(CASE WHEN month = 'Mar' THEN revenue END) AS "Mar_Revenue",
  -- ... 残り9ヶ月分
  MAX(CASE WHEN month = 'Dec' THEN revenue END) AS "Dec_Revenue"
FROM Department
GROUP BY id
ORDER BY id;

Pandas実装（Python 3.10 / pandas 2.2.2）

import pandas as pd

_MONTHS = ["Jan","Feb","Mar","Apr","May","Jun",
           "Jul","Aug","Sep","Oct","Nov","Dec"]

def reformat_department(department: pd.DataFrame) -> pd.DataFrame:
    """
    縦持ちの Department テーブルを横持ちにピボットする。

    Args:
        department (pd.DataFrame): columns = [id, revenue, month]

    Returns:
        pd.DataFrame: [id, Jan_Revenue, Feb_Revenue, ..., Dec_Revenue]
                      売上なし月は NaN。
    """
    # ① (id, month) が主キー → 集計不要、直接ピボット軸を構築
    #    unstack は Cython レベルで動作し Python 集計呼び出しなし
    out = (
        department
        .set_index(["id", "month"])["revenue"]   # MultiIndex Series: O(N)
        .unstack("month")                         # Series → 2D DataFrame: O(N)
        .reindex(columns=_MONTHS)                 # 欠損月補完 + カレンダー順: O(12)
    )

    # ② 列名を一括リネーム（リスト代入はコピーなし）
    out.columns = [f"{m}_Revenue" for m in out.columns]

    # ③ id を通常列に戻す
    out = out.reset_index()
    out.columns.name = None

    return out

フローチャート

計算量分析