初めに

前提

・PythonやSQLの基礎を終えたプログラミング初心者の方。
・SQLを使用し、バスケット分析を行いたい方へ。

VScode内でSQLを使用したため、DuckDBでの記述となっています。
執筆者の復習を兼ねた記事となっています。

ゴール

バスケット分析、支持度の指標をデータフレームにする。
SQLで実装できる。

メリット

数えることが得意なSQL.
WITH句、JOIN句など使用すると、バスケット分析ができるライブラリ内で行っている処理過程を理解しやすい。

使用データ

kaggleのOnline Retail Dataset
オンラインショップの購買データである。

541909行8列のデータセット。

今回主に使用するカラムの説明
InvoiceNoがレシート番号と思われる。
StockCodeとDescriptionは関連しており商品を示している。
なお、以降は欠損値や外れ値処理を済ませたデータを使用している。

VScode内でSQLを実行する方法

duckdb-engineをインストール

pip install duckdb-engine

duckdbをインポート

python

import duckdb

以下の記述でSQLを実行できる。

python

df_name = duckdb.query(
  f"""
--コードを記述
  """
).to_df()

df_name.head()

バスケット分析-支持度について

バスケット分析は支持度、信頼度、期待信頼度、リフト値という指標を使って商品Aと商品Bの関係性を調べるとき参考になる。

今回は支持度を調べていく。

支持度はデータにあるすべての顧客のうち、商品Aと商品Bを同時に購入する顧客の割合である。

つまり計算式は

\displaylines{
支持度 = 
\frac{商品Aと商品Bの同時購入人数}{購入者全体数}
}

となる。

支持度の計算

最終形態

df_basket_support = duckdb.query(
    """
--購入者全体数の集計

WITH total_buyer AS (
    SELECT COUNT(DISTINCT InvoiceNo) AS 購入者全体数
    FROM df
),

--同時購入人数の集計

product_combinations AS (
    SELECT A.Description AS 商品A, B.Description AS 商品B, 
           COUNT(DISTINCT A.InvoiceNo) AS AB同時購入人数
    FROM df AS A
    JOIN df AS B ON A.InvoiceNo = B.InvoiceNo AND A.Description < B.Description
    GROUP BY A.Description, B.Description
)

--支持度 = AB同時購入人数 / 購入者全体数の計算

SELECT 商品A, 商品B,
    AB同時購入人数 * 1.0 / 購入者全体数 AS 支持度 
FROM product_combinations, total_buyer
ORDER BY 支持度 DESC
"""
).to_df()


df_basket_support

解説

購入者全体数の集計

WITH total_buyer AS (
    SELECT COUNT(DISTINCT InvoiceNo) AS 購入者全体数
    FROM df
),

分母である購入者全体数を知りたい。
InvoiceNoを重複を許さず数えることで購入者の全体数とする。
WITH句で購入者全体数をTotal_buyerとして数えておく。
SELECT COUNT(DISTINCT InvoiceNo) AS 購入者全体数
集計関数COUNT、DISTINCTで重複を取り除いたInvoiceNoを数える。これで分母が導けたので、ASで購入者全体数とエイリアスをつけておく。

反映はされないが、上記値が得られている。

同時購入人数の集計

product_combinations AS (
    SELECT A.Description AS 商品A, B.Description AS 商品B, 
           COUNT(DISTINCT A.InvoiceNo) AS AB同時購入人数
    FROM df AS A
    JOIN df AS B ON A.InvoiceNo = B.InvoiceNo AND A.Description < B.Description
    GROUP BY A.Description, B.Description
)

SELECT A.Description AS 商品A, B.Description AS 商品B,
同じデータフレーム内（SQLなのでテーブル内）であるが、'A.'と'B.'に分けることで異なる行の商品説明を比較したり、条件を設定することができる。(任意の変数で良い)
この後のFROM句、JOIN句でA,Bの範囲が説明されている。

COUNT(DISTINCT A.InvoiceNo)
にてInvoiceNo毎に商品Aと商品Bが購入された回数＝AS AB同時購入人数を数えることができる。

FROM df AS AでdfのデータをSQLテーブルAとして指定する。
JOIN df AS B ON以下でテーブルAに、さらにdfのデータをテーブルBとして付け加える。

記述的にはA.DescriptionとB.Descriptionを先に分けているように見えるが、実際はここでdfからSQLテーブルAとテーブルBをそれぞれ作成していることになる。テーブルAのDescriptionとテーブルBのDescriptionとして分けることで、ABの商品を比較したり条件を追加したりする加工がやりやすくなる。

A.InvoiceNo = B.InvoiceNoは、AB2つのエイリアスで参照されるdfの行が同じ請求書番号を持つ場合に結合される。

A.Description < B.Descriptionは、AB2つのエイリアスで参照されるdfの行がA<Bだった場合に結合する。
数学で場合の数を数える時と同じ要領である。
これにより重複がなくなるし軽量化できる。
ちなみに!=でAがBでない場合にすると、すべてを洗い出してから消すという作業になるようで大変重くなる。

GROUP BY句で同じ組み合わせの商品Aと商品Bをグループ化する。

このコードでやったことは以下の通りである。
組み合わせて買った人数を数え上げているのが分かる。

（実行結果には反映されない）

支持度 = AB_同時購入人数 / 購入者全体数の計算

SELECT 商品A, 商品B
    AB_同時購入人数 * 1.0 / 購入者全体数 AS 支持度
FROM product_combinations, total_buyer
ORDER BY 支持度 DESC

AB_同時購入人数 * 1.0 / 購入者全体数 AS 支持度
あとは本懐である支持度を求める。
*1.0は浮動小数点に変換するために必要。
ORDER BYで支持度を降順に並べたりする。

結果の解釈

支持度はデータにあるすべての顧客のうち、商品Aと商品Bを同時に購入する顧客の割合であった。

降順に並べたので、一番上が支持度が最も高い商品の組み合わせである。

商品A：GREEN REGENCY TEACUP AND SAUCER と　商品B：ROSES REGENCY TEACUP AND SAUCERGREEN REGENCY TEACUP AND SAUCERについていえば、

\begin{align*}
支持度 &= \frac{商品Aと商品Bの同時購入人数}{購入者全体数} \\
&= \frac{742}{18734} \\
&≒0.039607
\end{align*}

つまり商品Aを購入したら約3.9%（％表示にした場合）の割合で商品Bを購入する傾向にある。

組み合わせの重複について

記述の仕方によっては、商品Aと商品B、商品Bと商品Aの組み合わせになることがある。同じではないかと思うが、むしろ同じであるため特に問題がないので除去しなくてもよい。

備考

組み合わせの支持度自体は上記の方法で導ける。

ここでいう商品Aはこのアイテムが購入された場合、商品Bが購入される可能性があることを示す。
aprioriなどのライブラリを使用してバスケット分析をすると、商品A:antecedents,商品B:consequentsなどと表記される。

antecedents(先行現象)に対するconsequents(結果)。

参考URL

この記事は以下の情報を参考にして執筆しました。

使用したデータセット

kaggle : Online Retail Dataset