【EXCEL】バスケット分析のためのちょっとしたデータ加工

バスケット分析はどの商品とどの商品が一緒に買われたか?の連関規則を発見する分析手法です。

分析にあたって、アイテム×アイテムの共起性を計算する必要がありますが、それをやるためには下右図のようなデータの持ち方をする必要があります。

エクセルで以下のようにデータを集計するやり方をメモしておこうと思います。(Before:左 After:右)

 ⇒ 

 

流れとしては以下の通り

①先頭フラグ、末尾フラグを立て、商品名を連結する

②商品名を重複削除し、縦横並び替える

③末尾フラグでフィルタをかけ、UniqueなUserID行をコピペ

④商品名ごとに出現回数をカウント

それではやっていきます。

 

①先頭フラグ、末尾フラグを立て、商品名を連結する

このステップでのゴールは以下のような表を作成することです。

・先頭フラグは、UserIDがはじめて出現した行に1が立つ

・末尾フラグは、同じUserIDが最後に出現した行に1が立つ

以下のように関数を組みます。

<先頭フラグ> その行のUserIDと1つの行が異なっていたら1、そうでなければ0

<末尾フラグ> その行のUserIDと1つの行が異なっていたら1、そうでなければ0

<商品名(連結)> 先頭フラグが立っている行だったら、その行の商品名を、そうでなければ、1つ上の行の商品名(連結)とその行の商品名をくっつける

 

②商品名を重複削除し、縦横並び替える

商品名の列をすべてコピーして、重複削除を行いユニークな商品名を取得します。

そいつをコピペするのですが、張り付ける際に「行列を入れ替える」オプションを選択します。

 

③末尾フラグでフィルタをかけ、UniqueなUserID行をコピペ

①で作成した表で、末尾フラグで1が立っている列だけフィルタかけて表示します。

このUserIDと商品名(連結)の行だけをコピーして、②と合わせて以下のようにします。

 

④商品名ごとに出現回数をカウント

最後に、COUNTIFで商品名の出現回数をカウントします。

この時注意点は、検索条件は部分一致にするため

*商品名*

とするのですが、商品名をセル参照にするとうまくいかないので、

“*”&セル&”*”

と&で連結してあげます。

 

これで完了です、おつかれさまでした!

おわり