0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@cozy_666_tech
in

pandas入門2 — groupby/merge/pivotを実務ユースケースで試した

0
Posted at

はじめに

前の記事でpandasの基本操作を一通り触った。

今回はより実務に近いユースケースでgroupby/merge/pivotを掘り下げる。「SQLで書いていたあの集計がpandasでどう書けるか」を意識しながら整理した。SQLのGROUP BY/JOINとの対応関係で考えると理解が早かった。

使うサンプルデータ

今回はこの3つのDataFrameを使い回す。

import pandas as pd

# 顧客データ
customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "name":        ["田中", "鈴木", "佐藤", "伊藤", "渡辺"],
    "region":      ["東京", "大阪", "東京", "福岡", "大阪"],
    "age":         [28, 35, 42, 25, 31],
})

# 注文データ
orders = pd.DataFrame({
    "order_id":   [101, 102, 103, 104, 105, 106, 107],
    "customer_id":[1,   2,   1,   3,   2,   4,   1  ],
    "product":    ["A", "B", "A", "C", "A", "B", "C"],
    "amount":     [1000, 2000, 1500, 800, 1200, 900, 3000],
    "month":      [1,    1,    2,    2,   3,    3,   3   ],
})

# 商品データ
products = pd.DataFrame({
    "product": ["A", "B", "C"],
    "category":["食品", "日用品", "食品"],
    "price":   [500, 900, 1500],
})

groupby — SQLのGROUP BYに相当

基本

-- SQL
SELECT region, COUNT(*) as count, AVG(age) as avg_age
FROM customers
GROUP BY region;

result = customers.groupby("region").agg(
    count  =("customer_id", "count"),
    avg_age=("age",         "mean"),
)
print(result)
#         count    avg_age
# region
# 大阪        2  33.000000
# 東京        2  35.000000
# 福岡        1  25.000000

.agg()に辞書形式で結果列名=(対象列, 集計関数)を渡す書き方が一番読みやすかった。

複数列でグループ化

-- SQL
SELECT customer_id, month, SUM(amount) as total
FROM orders
GROUP BY customer_id, month;

result = orders.groupby(["customer_id", "month"]).agg(
    total=("amount", "sum")
).reset_index()
print(result)
#    customer_id  month  total
# 0            1      1   1000
# 1            1      2   1500
# 2            1      3   3000
# 3            2      1   2000
# 4            2      3   1200
# 5            3      2    800
# 6            4      3    900

reset_index()を忘れるとグループのキーがインデックスになったままで扱いにくい。最初はよく忘れた。

複数の集計を同時に行う

result = orders.groupby("customer_id").agg(
    注文回数  =("order_id", "count"),
    合計金額  =("amount",   "sum"  ),
    平均金額  =("amount",   "mean" ),
    最大金額  =("amount",   "max"  ),
)
print(result)
#              注文回数   合計金額       平均金額  最大金額
# customer_id
# 1               3      5500  1833.333333      3000
# 2               2      3200  1600.000000      2000
# 3               1       800   800.000000       800
# 4               1       900   900.000000       900

カスタム集計関数

# 標準の集計関数にないものはlambdaで書く
result = orders.groupby("customer_id").agg(
    金額レンジ=("amount", lambda x: x.max() - x.min()),
    注文商品  =("product", lambda x: "/".join(sorted(x.unique()))),
)
print(result)
#              金額レンジ 注文商品
# customer_id
# 1              2000    A/C
# 2               800      A/B
# 3                 0        C
# 4                 0        B

HAVING相当（集計後のフィルタ）

-- SQL
SELECT customer_id, SUM(amount) as total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 2000;

result = (
    orders
    .groupby("customer_id")
    .agg(total=("amount", "sum"))
    .reset_index()
    .query("total >= 2000")  # HAVINGに相当
)
print(result)
#    customer_id  total
# 0            1   5500
# 2            2   3200

.query()はSQLのWHERE/HAVINGに近い書き方ができて可読性が高い。

merge — SQLのJOINに相当

INNER JOIN

-- SQL
SELECT o.order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

result = pd.merge(orders, customers, on="customer_id")
print(result[["order_id", "name", "amount"]])
#    order_id name  amount
# 0       101   田中    1000
# 1       102   鈴木    2000
# ...

LEFT JOIN

-- SQL
SELECT c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

result = (
    pd.merge(customers, orders, on="customer_id", how="left")
    .groupby(["customer_id", "name"])
    .agg(order_count=("order_id", "count"))
    .reset_index()
)
print(result[["name", "order_count"]])
#   name  order_count
# 0   田中            3
# 1   鈴木            2
# 2   佐藤            1
# 3   伊藤            1
# 4   渡辺            0  ← 注文がなくても残る

3テーブルのJOIN

# orders → customers → products を順番にmerge
result = (
    orders
    .merge(customers, on="customer_id")
    .merge(products,  on="product")
)
print(result[["name", "product", "category", "amount"]])
#   name product category  amount
# 0   田中       A       食品    1000
# 1   鈴木       B     日用品    2000
# ...

テーブルを追加するたびに.merge()をチェーンするだけ。SQLのJOINをそのまま順番に書く感覚で書ける。

キー名が違う場合

result = pd.merge(
    orders,
    customers.rename(columns={"customer_id": "cid"}),
    left_on ="customer_id",
    right_on="cid",
)

pivot_table — クロス集計

SQLでは書くのが面倒なクロス集計がpandasだと一発で書ける。

基本

# 月×顧客ごとの合計金額
pivot = pd.pivot_table(
    orders,
    values ="amount",
    index  ="month",
    columns="customer_id",
    aggfunc="sum",
    fill_value=0,  # NaNを0で埋める
)
print(pivot)
# customer_id     1     2    3    4
# month
# 1            1000  2000    0    0
# 2            1500     0  800    0
# 3            3000  1200    0  900

fill_value=0を忘れると注文がない組み合わせがNaNになる。

月次×カテゴリのクロス集計

# ordersとproductsをmergeしてからpivot
merged = pd.merge(orders, products, on="product")

pivot = pd.pivot_table(
    merged,
    values ="amount",
    index  ="month",
    columns="category",
    aggfunc="sum",
    fill_value=0,
)
print(pivot)
# category  日用品   食品
# month
# 1           2000   1000
# 2              0   2300
# 3            900   4200

SQLでこれを書くとCASE WHEN地獄になるところが、pivot_table一発で書ける。

小計・合計行を追加する

pivot = pd.pivot_table(
    orders,
    values  ="amount",
    index   ="month",
    columns ="customer_id",
    aggfunc ="sum",
    fill_value=0,
    margins =True,   # 合計行・列を追加
    margins_name="合計",
)
print(pivot)
# customer_id     1     2    3    4    合計
# month
# 1            1000  2000    0    0   3000
# 2            1500     0  800    0   2300
# 3            3000  1200    0  900   5100
# 合計          5500  3200  800  900  10400

margins=Trueで行列両方の合計が自動で追加される。Excelで手動でやっていた作業がパラメータ一つで済む。

melt — pivotの逆操作（ワイドからロングへ）

横持ちデータを縦持ちに変換する操作。SQLでは書くのが難しい。

# 横持ちデータ（ワイド形式）
wide_df = pd.DataFrame({
    "name": ["田中", "鈴木"],
    "1月":  [1000, 2000],
    "2月":  [1500,    0],
    "3月":  [3000, 1200],
})

# 縦持ちに変換（ロング形式）
long_df = pd.melt(
    wide_df,
    id_vars   =["name"],   # そのまま残す列
    var_name  ="month",    # 列名を値にする列名
    value_name="amount",   # 値を入れる列名
)
print(long_df)
#   name month  amount
# 0   田中    1月    1000
# 1   鈴木    1月    2000
# 2   田中    2月    1500
# 3   鈴木    2月       0
# 4   田中    3月    3000
# 5   鈴木    3月    1200

Excelから持ってきたデータは横持ちが多く、pandasで集計するには縦持ちに変換する必要がある。melt()はそのときに使う。

実務でよく使うパターンまとめ

月次売上レポートを作る

# 月×カテゴリ別の売上集計
monthly_report = (
    orders
    .merge(products, on="product")
    .merge(customers, on="customer_id")
    .groupby(["month", "category", "region"])
    .agg(
        件数  =("order_id", "count"),
        売上合計=("amount",   "sum"  ),
        平均単価=("amount",   "mean" ),
    )
    .reset_index()
    .sort_values(["month", "売上合計"], ascending=[True, False])
)
print(monthly_report)

顧客ランキング

customer_rank = (
    orders
    .groupby("customer_id")
    .agg(total=("amount", "sum"))
    .reset_index()
    .merge(customers[["customer_id", "name"]], on="customer_id")
    .sort_values("total", ascending=False)
    .reset_index(drop=True)
)
customer_rank.index += 1  # 1始まりにする
print(customer_rank[["name", "total"]])
#    name  total
# 1    田中   5500
# 2    鈴木   3200
# 3    伊藤    900
# 4    佐藤    800

前月比の計算

monthly = (
    orders
    .groupby("month")
    .agg(total=("amount", "sum"))
    .reset_index()
)

# 前月比（shift(1)で1つ前の行をずらす）
monthly["前月"] = monthly["total"].shift(1)
monthly["前月比"] = (monthly["total"] / monthly["前月"]).round(2)
print(monthly)
#    month  total    前月  前月比
# 0      1   3000     NaN    NaN
# 1      2   2300  3000.0   0.77
# 2      3   5100  2300.0   2.22

shift()はSQLのLAG関数に相当する。時系列データの前後比較によく使う。

よくやるミスと対処

# ① reset_index()忘れ
result = df.groupby("region").agg(count=("id", "count"))
# regionがインデックスになっているので.query()などで扱いにくい
# → .reset_index()をチェーンする

# ② to_csv()でindex列が余計に出る
df.to_csv("output.csv")
# → index=Falseをつける

# ③ mergeでカラムが重複する
result = pd.merge(orders, customers, on="customer_id")
# 同名の列がある場合 _x, _y のサフィックスがつく
# → merge前に不要な列をdropするか、suffixesで名前を指定

# ④ groupbyした結果の列名が意図しない形になる
result = df.groupby("region")["amount"].sum()
# この場合はSeriesが返るのでDataFrameとして扱えない
# → .agg()形式で書くか、.to_frame().reset_index()で変換

まとめ

SQL pandas
GROUP BY .groupby().agg()
HAVING .query()
INNER JOIN pd.merge(how="inner")
LEFT JOIN pd.merge(how="left")
クロス集計 pd.pivot_table()
LAG() .shift()
縦横変換 pd.melt() / pd.pivot_table()

SQLのGROUP BY/JOINとの対応で考えるとpandasの操作が頭に入りやすかった。

実務で出てくるデータ集計の大半はこの記事の操作の組み合わせで書けそうという感触がつかめてきた。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?