rfmAggregation/rfm_aggregation.ipynb

{"cells":[{"cell_type":"markdown","metadata":{},"source":["# RFM 集計ノード"]},{"cell_type":"code","execution_count":1,"metadata":{},"outputs":[],"source":["import pandas as pd\n","import datetime"]},{"cell_type":"code","execution_count":2,"metadata":{},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>CUSTID</th>\n","      <th>SDATE</th>\n","      <th>PRODUCTID</th>\n","      <th>L_CLASS</th>\n","      <th>M_CLASS</th>\n","      <th>AMOUNT</th>\n","      <th>U_PRICE</th>\n","      <th>SUBTOTAL</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>100001</td>\n","      <td>2019-02-26</td>\n","      <td>9900200</td>\n","      <td>UNDERWEAR</td>\n","      <td>UNDERWEAR06</td>\n","      <td>2</td>\n","      <td>700</td>\n","      <td>1400</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>100001</td>\n","      <td>2019-02-26</td>\n","      <td>9902142</td>\n","      <td>FOOD</td>\n","      <td>FOOD12</td>\n","      <td>2</td>\n","      <td>840</td>\n","      <td>1680</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>100001</td>\n","      <td>2019-07-02</td>\n","      <td>9937845</td>\n","      <td>BAG</td>\n","      <td>BAG04</td>\n","      <td>5</td>\n","      <td>348</td>\n","      <td>1740</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>100001</td>\n","      <td>2019-07-02</td>\n","      <td>9902517</td>\n","      <td>FOOD</td>\n","      <td>FOOD12</td>\n","      <td>5</td>\n","      <td>550</td>\n","      <td>2750</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>100001</td>\n","      <td>2019-07-09</td>\n","      <td>9903878</td>\n","      <td>BAG</td>\n","      <td>BAG04</td>\n","      <td>7</td>\n","      <td>444</td>\n","      <td>3108</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   CUSTID      SDATE  PRODUCTID    L_CLASS      M_CLASS  AMOUNT  U_PRICE  \\\n","0  100001 2019-02-26    9900200  UNDERWEAR  UNDERWEAR06       2      700   \n","1  100001 2019-02-26    9902142       FOOD       FOOD12       2      840   \n","2  100001 2019-07-02    9937845        BAG        BAG04       5      348   \n","3  100001 2019-07-02    9902517       FOOD       FOOD12       5      550   \n","4  100001 2019-07-09    9903878        BAG        BAG04       7      444   \n","\n","   SUBTOTAL  \n","0      1400  \n","1      1680  \n","2      1740  \n","3      2750  \n","4      3108  "]},"execution_count":2,"metadata":{},"output_type":"execute_result"}],"source":["df_tran = pd.read_csv(\"../data/sampletranDEPT4en2019.csv\", parse_dates=['SDATE'])\n","df_tran.head()\n"]},{"cell_type":"code","execution_count":3,"metadata":{},"outputs":[],"source":["# 購買履歴データ内の最新日時を取得し、Recencyの基準日とする\n","from itertools import count\n","\n","\n","NOW=df_tran[\"SDATE\"].max()\n","\n","# 指定日以降のトランザクションデータを集計の対象とする\n","df_tran=df_tran[df_tran[\"SDATE\"]>=\"2019-09-01\"]\n","\n","# 指定の値以下のレコードを破棄\n","df_tran=df_tran[df_tran[\"SUBTOTAL\"]>2000]\n","\n","# Recency,Frequency,Monetary,R2,R3を集計する\n","ID=\"CUSTID\"\n","df_rfm = (\n","    df_tran.groupby(ID)\n","    .agg(\n","        {\n","            \"SDATE\": [\n","                lambda date: (NOW - date.max()).days,\n","                \"count\",\n","                lambda date: (NOW - sorted(date)[-2]).days\n","                if len(date) > 1\n","                else pd.NA,\n","                lambda date: (NOW - sorted(date)[-3]).days\n","                if len(date) > 2\n","                else pd.NA,\n","            ],\n","            \"SUBTOTAL\": \"sum\",\n","        }\n","    )\n","    .reset_index()\n",")\n","# カラム名の変更と順番の並び替え\n","df_rfm.columns=[ID,\"Recency\",\"Frequency\",\"R2\",\"R3\",\"Monetary\"]\n","df_rfm=df_rfm.reindex(columns=[ID,\"Recency\",\"Frequency\",\"Monetary\",\"R2\",\"R3\"])\n","\n"]},{"cell_type":"code","execution_count":4,"metadata":{},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>CUSTID</th>\n","      <th>Recency</th>\n","      <th>Frequency</th>\n","      <th>Monetary</th>\n","      <th>R2</th>\n","      <th>R3</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>100001</td>\n","      <td>9</td>\n","      <td>10</td>\n","      <td>57397</td>\n","      <td>59</td>\n","      <td>59</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>100004</td>\n","      <td>20</td>\n","      <td>8</td>\n","      <td>34711</td>\n","      <td>29</td>\n","      <td>29</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>100005</td>\n","      <td>77</td>\n","      <td>2</td>\n","      <td>10700</td>\n","      <td>108</td>\n","      <td>&lt;NA&gt;</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>100006</td>\n","      <td>41</td>\n","      <td>2</td>\n","      <td>6700</td>\n","      <td>104</td>\n","      <td>&lt;NA&gt;</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>100008</td>\n","      <td>3</td>\n","      <td>3</td>\n","      <td>8789</td>\n","      <td>3</td>\n","      <td>120</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>2788</th>\n","      <td>105967</td>\n","      <td>2</td>\n","      <td>16</td>\n","      <td>84595</td>\n","      <td>64</td>\n","      <td>65</td>\n","    </tr>\n","    <tr>\n","      <th>2789</th>\n","      <td>105970</td>\n","      <td>68</td>\n","      <td>8</td>\n","      <td>62506</td>\n","      <td>79</td>\n","      <td>85</td>\n","    </tr>\n","    <tr>\n","      <th>2790</th>\n","      <td>105972</td>\n","      <td>34</td>\n","      <td>8</td>\n","      <td>28978</td>\n","      <td>65</td>\n","      <td>83</td>\n","    </tr>\n","    <tr>\n","      <th>2791</th>\n","      <td>105974</td>\n","      <td>19</td>\n","      <td>21</td>\n","      <td>60742</td>\n","      <td>19</td>\n","      <td>22</td>\n","    </tr>\n","    <tr>\n","      <th>2792</th>\n","      <td>105978</td>\n","      <td>59</td>\n","      <td>12</td>\n","      <td>60340</td>\n","      <td>59</td>\n","      <td>118</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>2793 rows × 6 columns</p>\n","</div>"],"text/plain":["      CUSTID  Recency  Frequency  Monetary   R2    R3\n","0     100001        9         10     57397   59    59\n","1     100004       20          8     34711   29    29\n","2     100005       77          2     10700  108  <NA>\n","3     100006       41          2      6700  104  <NA>\n","4     100008        3          3      8789    3   120\n","...      ...      ...        ...       ...  ...   ...\n","2788  105967        2         16     84595   64    65\n","2789  105970       68          8     62506   79    85\n","2790  105972       34          8     28978   65    83\n","2791  105974       19         21     60742   19    22\n","2792  105978       59         12     60340   59   118\n","\n","[2793 rows x 6 columns]"]},"execution_count":4,"metadata":{},"output_type":"execute_result"}],"source":["df_rfm"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":[]}],"metadata":{"kernelspec":{"display_name":"Python 3.10.6 ('Modeler2Python-IC4X63eQ')","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.10.6"},"vscode":{"interpreter":{"hash":"324a1c4512f36307372f79437dc33f81d1e1d7036c074d00944b65bc6d9ee766"}}},"nbformat":4,"nbformat_minor":1}