SPSS Modelerの再構成ノードをPythonで書き換える。購入商品カテゴリごとの集計

SPSS Modelerで縦持ちデータを横持ちに変換する再構成ノードをPythonのpandasで書き換えてみます。

１．加工のイメージ

以下のID付POSデータから各顧客毎に①商品カテゴリごとの購入額合計と②商品カテゴリごとの購入割合を集計してみます。

■加工前
誰（CUSTID）がいつ（SDATE）何（PRODUCTID、L_CLASS商品大分類、M_CLASS商品中分類）をいくら（SUBTOTAL）購入したかが記録されたID付POSデータを使います。

■加工後
顧客毎(CUSTID)に商品大分類（L_CLASS）の①商品カテゴリごとの購入額合計と②商品カテゴリごとの購入割合を集計します。

商品大分類にはBAG、COMETICS、SHOESの3つがあり、100001番の顧客はBAGで7243円、COSMETICSで10273円、SHOESで26870円の購入があります。それを金額の割合で計算するとBAG16.3%、COSMETICS23.1%、SHOES60.5%となります。このような集計を行うと顧客の特徴が浮かび上がってきます。

2.Modeler再構成ノードでの設定①商品カテゴリごとの購入額合計

まず、①商品カテゴリごとの購入額合計までを求めてみます。
再構成ノードは、データ型ノード、レコード集計、また置換ノードまでの組合せで通常使われます。
さらに今回は総購入金額まで計算するために、レコード集計とレコード結合ノードも組み合わせます。

まずデータ型ノードで商品大分類（L_CLASS）にどんなカテゴリ値があるかを認識します。データ型ノードで「値の読み込み」を行うと、自動で大分類のすべてのカテゴリが認識されます。

次に再構成ノードで横持ちさせたいフィールドの値を選びます。以下の例では商品大分類（L_CLASS）にはBAG、COMETICS、SHOES、これらを列として展開して値には小計（SUBSTOTAL）をセットしています。

この設定の結果をプレビューすると下のようになります。小計（SUBSTOTAL）が各商品大分類（L_CLASS）の列に振り分けられたことがわかります。

次に「レコード集計」ノードをつかってこのBAG、COMETICS、SHOESの小計値を顧客で1レコードに集計します（レコード度数は不要なのでチェックを外します）。

これで各顧客毎のBAG、COMETICS、SHOESの商品大分類ごとの購入総額が集計されました。100001番の顧客はBAGで7243円、COSMETICSで10273円、SHOESで26870円の購入があることが計算できました。

これでほぼ完成なのですが、いくつかNULLの値があります。これはこの顧客はこの商品大分類での購入が一度もなかったことを意味しています。このままだと計算に使いづらいので、NULLを0に置き換えます。

それを行うのが置換ノードです。
BAG、COMETICS、SHOESの合計値のノードを選択し、ヌル値の場合に0に設定します。

以下のようにヌルが0に変換されました。

次に顧客毎の総購入額を、レコード集計とレコード結合のノードを追加して算出します。

まず、レコード集計ノードで各顧客（CUSTID）の小計（SUBTOTAL）の合計、つまり総購入金額を算出します（やはりレコード度数は不要です）。

100001番の顧客は全部で44,386円の購入しています。

この集計結果と先ほどのBAG、COMETICS、SHOESの合計額の集計をレコード結合ノードで結合します。

そうすると各顧客毎のBAG、COMETICS、SHOESの合計額の集計の後ろに総購入額が結合できました。

最後はフィルターノードで長くなった列名を短くしておきました（この作業は必須ではありません）。

最終的なデータは以下のようになりました。

2.Modeler再構成ノードでの設定②商品カテゴリごとの購入割合

ここからは、再構成ノードとしては少し応用的な使い方ですが、他の顧客と比較しやすい「割合」を計算したいことはよくありますのでご紹介します。

先ほど作ったデータを見てみましょう。

100001番の顧客はBAGは7243円÷44,386円、COSMETICSは10273円÷44,386円、SHOESは26870円÷44,386円の各割り算を行うことで購入額の割合が計算できます。
この計算をフィールド作成ノードで行います。

モードを複数とし、フィールドリストにBAG_SUM、COSMETICS_SUM、SHOES_SUMを選択します。こうするとCLEM式の中で、@FIELDをつかって、BAG_SUM、COSMETICS_SUM、SHOES_SUMの各列を参照できます。
ここでは
@FIELD/ALL_SUM
を指定することで、
BAG_SUM/ALL_SUM、COSMETICS_SUM/ALL_SUM、SHOES_SUM/ALL_SUMの３つの割合が求められます。

これで各顧客毎のBAG、COMETICS、SHOESの商品大分類ごとの購入割合が集計されました。100001番の顧客の購入割合はBAG16.3%、COSMETICS23.1%、SHOES60.5%となります。

3.pandasでの設定①商品カテゴリごとの購入額合計

同じデータ加工をpandasでもやってみます。実は①商品カテゴリごとの購入額合計の算出は、pandasではpivot_tableというメソッド一つで非常に簡単にできます。

pivot_tran_df= df.pivot_table(
    index=['CUSTID'],columns=['L_CLASS'],values=['SUBTOTAL'],
    aggfunc='sum',
    fill_value=0,
    margins=True, margins_name='ALL')

• index=['CUSTID'],columns=['L_CLASS'],values=['SUBTOTAL']でCUSTIDをキーにしてL_CLASSに含まれるBAG、COMETICS、SHOESの商品大分類ごとにSUBTOTALを展開します。ここまでが再構成ノードの設定のイメージです。
• aggfunc='sum'でSUBTOTALの合計値を算出することを示します。ここが集計ノードのイメージです。
• fill_value=0はNULLになった箇所を0埋めすることを示します。ここが置換ノードのイメージです。
• margins=True, margins_name='ALL'は列合計と行合計を追加することを示します。ここが集計ノードとレコード結合ノードの役割を果たしています。

以下のようにCUSTID毎にBAG、COMETICS、SHOESの購入額と総購入金額が計算できています。

• 参考
  • pandas.pivot_table — pandas 1.0.5 documentation https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html
  • pandasのピボットテーブルでカテゴリ毎の統計量などを算出 | note.nkmk.me https://note.nkmk.me/python-pandas-pivot-table/

列の合計列は必要ですが、行の合計行は今回は不要なので以下で削除します。

pivot_tran_df=pivot_tran_df[:-1]

• 参考
  • python — pandasデータフレームのデータの最後の行を削除する方法 https://www.it-swarm.dev/ja/python/pandas%E3%83%87%E3%83%BC%E3%82%BF%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E6%9C%80%E5%BE%8C%E3%81%AE%E8%A1%8C%E3%82%92%E5%89%8A%E9%99%A4%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/1050572731/

pivot_tableをつかうと列が階層構造になりマルチカラムになります。

マルチカラムのままだと結合などがしづらく扱いづらいので、フラットなスネーク形式（アンダースコアで連結）の列名に変換します。Modelerの命名規則に合わせると以下のようになります。

pivot_tran_df.columns = [
    pivot_tran_df.columns.names[1]+"_"+levels[1]+"_"+levels[0] 
    for levels in pivot_tran_df.columns]

ここではさらに正規表現を使って、BAG_SUMのような短い列名に変更しました。

import re
pivot_tran_df=pivot_tran_df.rename(
    columns= lambda str:
    re.sub('L_CLASS_(.+)_SUBTOTAL',r'\1_SUM',str))

3.pandasでの設定②商品カテゴリごとの購入割合

②商品カテゴリごとの購入割合をModelerでは@FIELD/ALL_SUMで計算しました。

pandasではdivメソッドを使います。Modelerの置換ノードのイメージに近くて、すべての列がALL_SUMで除算されて置き換えられます。ALL_SUM自身も割られて1.0になっています。

pivot_tran_ratio_df=pivot_tran_df.div(pivot_tran_df["ALL_SUM"], axis=0)

• 参考
  • pandas.DataFrame.div — pandas 0.24.2 documentation https://pandas.pydata.org/pandas-docs/version/0.24.2/reference/api/pandas.DataFrame.div.html
  • python - データフレームの複数列を一つの列で割る - スタック・オーバーフロー https://ja.stackoverflow.com/questions/52834/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%81%AE%E8%A4%87%E6%95%B0%E5%88%97%E3%82%92%E4%B8%80%E3%81%A4%E3%81%AE%E5%88%97%E3%81%A7%E5%89%B2%E3%82%8B

ALL_SUMを削除(drop)し、SUM(合計)ではなくRATIO(割合)なので列名を変更します(rename)。

import re
pivot_tran_ratio_df=pivot_tran_ratio_df\
    .drop(columns=['ALL_SUM'])\
    .rename(columns= lambda str:re.sub('_SUM','_RATIO',str))

最後に①商品カテゴリごとの購入額合計のDataFrameに結合しなおして完成です。

pivot_tran_df=pivot_tran_df.join(pivot_tran_ratio_df)

4. サンプル

サンプルは以下に置きました。

ストリーム
https://github.com/hkwd/200611Modeler2Python/blob/master/Restructure/Restructure.str?raw=true
notebook
https://github.com/hkwd/200611Modeler2Python/blob/master/Restructure/restructure.ipynb
データ
https://raw.githubusercontent.com/hkwd/200611Modeler2Python/master/data/sampletranDEPT4en2019S.csv

■テスト環境
Modeler 18.2.1
Windows 10 64bit
Python 3.6.9
pandas 0.24.1

5. 参考情報

【リレー連載】わたしの推しノード - ID付POSやIoT時系列データから特徴量を生成するスゴ技職人「再構成ノード」 | IBM ソリューション ブログ https://www.ibm.com/blogs/solutions/jp-ja/spssmodeler-push-node-4/