{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport math as math\nimport datetime as dt\nimport sklearn\nimport plotly\nimport plotly.plotly as py\nimport plotly.figure_factory as ff\nimport plotly.graph_objs as go\nfrom plotly import __version__\nprint(__version__)\nfrom plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\ninit_notebook_mode(connected=True)\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\npd.set_option('display.max_rows', 500)\npd.set_option('display.max_columns', 500)\npd.set_option('display.width', 1000)\npd.set_option('precision', 5)\npd.options.display.float_format = '{:20,.2f}'.format\nnp.set_printoptions(suppress =True) \n\nimport os\nprint(os.listdir(\"../input\"))\nimport tqdm\nfrom tqdm import tqdm\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-12-06T12:19:24.182845Z","iopub.execute_input":"2021-12-06T12:19:24.183625Z","iopub.status.idle":"2021-12-06T12:19:26.967145Z","shell.execute_reply.started":"2021-12-06T12:19:24.183248Z","shell.execute_reply":"2021-12-06T12:19:26.966163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/historical_transactions.csv', nrows=50).head(5)","metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","execution":{"iopub.status.busy":"2021-12-06T12:19:26.969427Z","iopub.execute_input":"2021-12-06T12:19:26.969723Z","iopub.status.idle":"2021-12-06T12:19:27.027678Z","shell.execute_reply.started":"2021-12-06T12:19:26.969674Z","shell.execute_reply":"2021-12-06T12:19:27.026755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.029389Z","iopub.execute_input":"2021-12-06T12:19:27.029851Z","iopub.status.idle":"2021-12-06T12:19:27.062741Z","shell.execute_reply.started":"2021-12-06T12:19:27.029656Z","shell.execute_reply":"2021-12-06T12:19:27.061968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/new_merchant_transactions.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.064128Z","iopub.execute_input":"2021-12-06T12:19:27.064675Z","iopub.status.idle":"2021-12-06T12:19:27.094053Z","shell.execute_reply.started":"2021-12-06T12:19:27.064624Z","shell.execute_reply":"2021-12-06T12:19:27.093319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/train.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.097708Z","iopub.execute_input":"2021-12-06T12:19:27.097982Z","iopub.status.idle":"2021-12-06T12:19:27.120718Z","shell.execute_reply.started":"2021-12-06T12:19:27.097932Z","shell.execute_reply":"2021-12-06T12:19:27.120034Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/test.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.123252Z","iopub.execute_input":"2021-12-06T12:19:27.123543Z","iopub.status.idle":"2021-12-06T12:19:27.144275Z","shell.execute_reply.started":"2021-12-06T12:19:27.123497Z","shell.execute_reply":"2021-12-06T12:19:27.143464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/elo-merchant-category-recommendation/sample_submission.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.145662Z","iopub.execute_input":"2021-12-06T12:19:27.146147Z","iopub.status.idle":"2021-12-06T12:19:27.165524Z","shell.execute_reply.started":"2021-12-06T12:19:27.146097Z","shell.execute_reply":"2021-12-06T12:19:27.164734Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_excel('../input/elo-merchant-category-recommendation/Data_Dictionary.xlsx')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.166942Z","iopub.execute_input":"2021-12-06T12:19:27.167525Z","iopub.status.idle":"2021-12-06T12:19:27.398799Z","shell.execute_reply.started":"2021-12-06T12:19:27.167472Z","shell.execute_reply":"2021-12-06T12:19:27.397759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('../input/ecommerce-data/data.csv', nrows=50).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.400492Z","iopub.execute_input":"2021-12-06T12:19:27.400968Z","iopub.status.idle":"2021-12-06T12:19:27.427037Z","shell.execute_reply.started":"2021-12-06T12:19:27.400761Z","shell.execute_reply":"2021-12-06T12:19:27.426292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Reading the Data","metadata":{}},{"cell_type":"code","source":"# df_train = pd.read_csv(\"../input/elo-merchant-category-recommendation/train.csv\", nrows = 10000)\n# df_test = pd.read_csv(\"../input/elo-merchant-category-recommendation/test.csv\", nrows = 10000)\n# df_historical =pd.read_csv(\"../input/elo-merchant-category-recommendation/historical_transactions.csv\",parse_dates=['purchase_date'])\n# df_new =pd.read_csv(\"../input/elo-merchant-category-recommendation/new_merchant_transactions.csv\",parse_dates=['purchase_date'], nrows = 10000)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.428493Z","iopub.execute_input":"2021-12-06T12:19:27.428990Z","iopub.status.idle":"2021-12-06T12:19:27.432810Z","shell.execute_reply.started":"2021-12-06T12:19:27.428938Z","shell.execute_reply":"2021-12-06T12:19:27.431964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = pd.read_csv(\"../input/elo-merchant-category-recommendation/train.csv\")\ndf_test = pd.read_csv(\"../input/elo-merchant-category-recommendation/test.csv\")\ndf_historical =pd.read_csv(\"../input/elo-merchant-category-recommendation/historical_transactions.csv\",parse_dates=['purchase_date'])\ndf_new =pd.read_csv(\"../input/elo-merchant-category-recommendation/new_merchant_transactions.csv\",parse_dates=['purchase_date'])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:19:27.434487Z","iopub.execute_input":"2021-12-06T12:19:27.435179Z","iopub.status.idle":"2021-12-06T12:21:20.464876Z","shell.execute_reply.started":"2021-12-06T12:19:27.435110Z","shell.execute_reply":"2021-12-06T12:21:20.463985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_historical.head(2)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:20.466506Z","iopub.execute_input":"2021-12-06T12:21:20.466821Z","iopub.status.idle":"2021-12-06T12:21:20.484964Z","shell.execute_reply.started":"2021-12-06T12:21:20.466775Z","shell.execute_reply":"2021-12-06T12:21:20.484234Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(df_historical.card_id.unique())","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:20.486282Z","iopub.execute_input":"2021-12-06T12:21:20.486784Z","iopub.status.idle":"2021-12-06T12:21:22.130060Z","shell.execute_reply.started":"2021-12-06T12:21:20.486732Z","shell.execute_reply":"2021-12-06T12:21:22.129107Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_new.head(2)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:22.131559Z","iopub.execute_input":"2021-12-06T12:21:22.131861Z","iopub.status.idle":"2021-12-06T12:21:22.147615Z","shell.execute_reply.started":"2021-12-06T12:21:22.131814Z","shell.execute_reply":"2021-12-06T12:21:22.146805Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_historical=df_historical.loc[df_historical.authorized_flag==\"Y\",]\ndf_historical.purchase_amount += 0.75\ndf_new.purchase_amount += 0.75","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:22.149151Z","iopub.execute_input":"2021-12-06T12:21:22.149721Z","iopub.status.idle":"2021-12-06T12:21:30.786690Z","shell.execute_reply.started":"2021-12-06T12:21:22.149664Z","shell.execute_reply":"2021-12-06T12:21:30.785773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Checking for Purchase, Return and Rebate Transactions (if any)","metadata":{}},{"cell_type":"markdown","source":"## As the purchase_amount variable is normalized it is not possible to check if the transaction if a Purchase/Return/Rebate one","metadata":{}},{"cell_type":"markdown","source":"### Aggregation by card-id (At Customer Level). Can also compute at Item Level RFM and Store Level RFM or even website visit & activity based RFM etc depending on availability of Data","metadata":{}},{"cell_type":"code","source":"def groupby_mean(x):\n    return x.mean()\n\ndef groupby_count(x):\n    return x.count()\n\ndef purchase_duration(x):\n    return (x.max() - x.min()).days\n\ndef avg_frequency(x):\n    return (x.max() - x.min()).days/x.count()\n\ngroupby_mean.__name__ = 'avg'\ngroupby_count.__name__ = 'count'\npurchase_duration.__name__ = 'purchase_duration'\navg_frequency.__name__ = 'purchase_frequency'\n\ndef get_max(cols):\n    return max(cols[0],cols[1])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:30.788100Z","iopub.execute_input":"2021-12-06T12:21:30.788413Z","iopub.status.idle":"2021-12-06T12:21:30.797406Z","shell.execute_reply.started":"2021-12-06T12:21:30.788364Z","shell.execute_reply":"2021-12-06T12:21:30.796561Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_Agg_Monetary = df_historical.groupby('card_id').agg({'purchase_amount':sum})\ndf_Agg_Monetary.columns = ['Monetary']\nprint(df_Agg_Monetary.shape)\ndf_Agg_Monetary.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:30.798768Z","iopub.execute_input":"2021-12-06T12:21:30.799246Z","iopub.status.idle":"2021-12-06T12:21:33.972509Z","shell.execute_reply.started":"2021-12-06T12:21:30.799195Z","shell.execute_reply":"2021-12-06T12:21:33.971547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_Agg_Frequency = df_historical.groupby('card_id').agg({'card_id': groupby_count,'purchase_date': groupby_count})\ndf_Agg_Frequency['Frequency'] = df_Agg_Frequency[['card_id','purchase_date']].apply(get_max,axis = 1)\nprint(df_Agg_Frequency.shape)\ndf_Agg_Frequency.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:21:33.974073Z","iopub.execute_input":"2021-12-06T12:21:33.974645Z","iopub.status.idle":"2021-12-06T12:22:22.454368Z","shell.execute_reply.started":"2021-12-06T12:21:33.974584Z","shell.execute_reply":"2021-12-06T12:22:22.453632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"table_max_date = max(df_historical['purchase_date'])\nprint(table_max_date)\n\n# x = df_historical['purchase_date'][0]\n# (table_max_date-x).days","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:22:22.455841Z","iopub.execute_input":"2021-12-06T12:22:22.456143Z","iopub.status.idle":"2021-12-06T12:25:09.453208Z","shell.execute_reply.started":"2021-12-06T12:22:22.456094Z","shell.execute_reply":"2021-12-06T12:25:09.452321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_Agg_Recency = df_historical.groupby(['card_id']).agg({'purchase_date':max})\ndf_Agg_Recency['Recency'] = df_Agg_Recency['purchase_date'].apply(lambda x:(table_max_date-x).days)\nprint(df_Agg_Recency.shape)\ndf_Agg_Recency.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:09.456433Z","iopub.execute_input":"2021-12-06T12:25:09.456685Z","iopub.status.idle":"2021-12-06T12:25:20.641168Z","shell.execute_reply.started":"2021-12-06T12:25:09.456640Z","shell.execute_reply":"2021-12-06T12:25:20.640253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm = pd.merge(pd.merge(df_Agg_Recency, df_Agg_Frequency, left_index=True, right_index=True), \n                           df_Agg_Monetary, left_index=True, right_index=True)\ndf_rfm.drop(columns=['purchase_date_x', 'card_id', 'purchase_date_y'], inplace=True)\ndf_rfm.head(5)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:20.642643Z","iopub.execute_input":"2021-12-06T12:25:20.643117Z","iopub.status.idle":"2021-12-06T12:25:20.994220Z","shell.execute_reply.started":"2021-12-06T12:25:20.642927Z","shell.execute_reply":"2021-12-06T12:25:20.993457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,8))\ncounts, bin_edges = np.histogram(np.log1p(df_rfm['Recency']), bins=10, density = True)\npdf = counts/(sum(counts))\nprint(pdf);\nprint(bin_edges);\ncdf = np.cumsum(pdf)\nplt.plot(bin_edges[1:],pdf);\nplt.plot(bin_edges[1:], cdf)\nplt.xlabel('log_Recency', fontsize=12)\nplt.ylabel('Percentage', fontsize=12)\nplt.title('Recency Cumulative Frequency Distribution',fontsize=15)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:20.996013Z","iopub.execute_input":"2021-12-06T12:25:20.996515Z","iopub.status.idle":"2021-12-06T12:25:21.329083Z","shell.execute_reply.started":"2021-12-06T12:25:20.996292Z","shell.execute_reply":"2021-12-06T12:25:21.328301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,8))\ncounts, bin_edges = np.histogram(np.log1p(df_rfm['Frequency']), bins=10, density = True)\npdf = counts/(sum(counts))\nprint(pdf);\nprint(bin_edges);\ncdf = np.cumsum(pdf)\nplt.plot(bin_edges[1:],pdf);\nplt.plot(bin_edges[1:], cdf)\nplt.xlabel('log_Frequency', fontsize=12)\nplt.ylabel('Percentage', fontsize=12)\nplt.title('Frequency Cumulative Frequency Distribution',fontsize=15)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:21.330522Z","iopub.execute_input":"2021-12-06T12:25:21.330990Z","iopub.status.idle":"2021-12-06T12:25:21.662661Z","shell.execute_reply.started":"2021-12-06T12:25:21.330936Z","shell.execute_reply":"2021-12-06T12:25:21.661894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,8))\ncounts, bin_edges = np.histogram(np.log1p(df_rfm['Monetary']), bins=10, density = True)\npdf = counts/(sum(counts))\nprint(pdf);\nprint(bin_edges);\ncdf = np.cumsum(pdf)\nplt.plot(bin_edges[1:],pdf);\nplt.plot(bin_edges[1:], cdf)\nplt.xlabel('log_Monetary', fontsize=12)\nplt.ylabel('Percentage', fontsize=12)\nplt.title('Monetary Cumulative Frequency Distribution',fontsize=15)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:21.664009Z","iopub.execute_input":"2021-12-06T12:25:21.664478Z","iopub.status.idle":"2021-12-06T12:25:22.008186Z","shell.execute_reply.started":"2021-12-06T12:25:21.664424Z","shell.execute_reply":"2021-12-06T12:25:22.007249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.close();\nsns.set_style(\"whitegrid\");\nsns.pairplot(df_rfm, size=3);\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:22.013149Z","iopub.execute_input":"2021-12-06T12:25:22.015649Z","iopub.status.idle":"2021-12-06T12:25:50.539611Z","shell.execute_reply.started":"2021-12-06T12:25:22.015588Z","shell.execute_reply":"2021-12-06T12:25:50.538735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# fig = ff.create_scatterplotmatrix(df_rfm, height=800, width=800)\n# iplot(fig, filename='Basic Scatterplot Matrix')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.540787Z","iopub.execute_input":"2021-12-06T12:25:50.541101Z","iopub.status.idle":"2021-12-06T12:25:50.545134Z","shell.execute_reply.started":"2021-12-06T12:25:50.541052Z","shell.execute_reply":"2021-12-06T12:25:50.544423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df_StateRFM['Frequency'] = np.round(df_StateRFM['Frequency'])\n# data = [go.Choropleth(\n#     colorscale = scl,\n#     autocolorscale = False,\n#     locations = df_StateRFM['State'],\n#     z = df_StateRFM['Frequency'].astype(float),\n#     locationmode = 'USA-states',\n#     marker = go.choropleth.Marker(\n#         line = go.choropleth.marker.Line(\n#             color = 'rgb(255,255,255)',\n#             width = 2\n#         )),\n#     colorbar = go.choropleth.ColorBar(\n#         title = \"Frequency Count\")\n# )]\n\n# layout = go.Layout(\n#     title = go.layout.Title(\n#         text = 'State wise Frequency Value '\n#     ),\n#     geo = go.layout.Geo(\n#         scope = 'usa',\n#         projection = go.layout.geo.Projection(type = 'albers usa'),\n#         showlakes = True,\n#         lakecolor = 'rgb(255, 255, 255)'),\n# )\n\n# fig = go.Figure(data = data, layout = layout)\n# iplot(fig, filename = 'd3-cloropleth-map')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.921782Z","iopub.status.idle":"2021-12-06T12:25:50.922626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df_StateRFM['Recency'] = np.round(df_StateRFM['Recency'])\n# data = [go.Choropleth(\n#     colorscale = scl,\n#     autocolorscale = False,\n#     locations = df_StateRFM['State'],\n#     z = df_StateRFM['Recency'].astype(float),\n#     locationmode = 'USA-states',\n#     marker = go.choropleth.Marker(\n#         line = go.choropleth.marker.Line(\n#             color = 'rgb(255,255,255)',\n#             width = 2\n#         )),\n#     colorbar = go.choropleth.ColorBar(\n#         title = \"Recency Count\")\n# )]\n\n# layout = go.Layout(\n#     title = go.layout.Title(\n#         text = 'State wise Recency Value '\n#     ),\n#     geo = go.layout.Geo(\n#         scope = 'usa',\n#         projection = go.layout.geo.Projection(type = 'albers usa'),\n#         showlakes = True,\n#         lakecolor = 'rgb(255, 255, 255)'),\n# )\n\n# fig = go.Figure(data = data, layout = layout)\n# iplot(fig, filename = 'd3-cloropleth-map')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.924120Z","iopub.status.idle":"2021-12-06T12:25:50.925206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 1. Simple Rule Based Approach - The Business Analyst Way","metadata":{}},{"cell_type":"code","source":"df_rfm.quantile(q=[0.1,0.25,0.4,0.5,0.75,0.9])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:48:31.439394Z","iopub.execute_input":"2021-12-06T12:48:31.439728Z","iopub.status.idle":"2021-12-06T12:48:31.483238Z","shell.execute_reply.started":"2021-12-06T12:48:31.439677Z","shell.execute_reply":"2021-12-06T12:48:31.482092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Thresholds for R | F | M","metadata":{"trusted":true}},{"cell_type":"code","source":"threshold_M = int(df_rfm.median()['Monetary'])+1\nprint(threshold_M)\nthreshold_F = df_rfm.Frequency.quantile(0.75)\nprint(threshold_F)\nthreshold_R = df_rfm.Recency.quantile(0.40)\nprint(threshold_R)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:48:35.222622Z","iopub.execute_input":"2021-12-06T12:48:35.223036Z","iopub.status.idle":"2021-12-06T12:48:35.258785Z","shell.execute_reply.started":"2021-12-06T12:48:35.222906Z","shell.execute_reply":"2021-12-06T12:48:35.257940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm['threshold_R'] = df_rfm['Recency'].apply(lambda x: x < threshold_R)\ndf_rfm['threshold_F'] = df_rfm['Frequency'].apply(lambda x: x > threshold_F)\ndf_rfm['threshold_M'] = df_rfm['Monetary'].apply(lambda x: x > threshold_M)\ndf_rfm[['threshold_R', 'threshold_F', 'threshold_M']] = df_rfm[['threshold_R', 'threshold_F', 'threshold_M']].apply(lambda x: x.astype(int), axis=1)\ndf_rfm['IsLoyal'] = 'NA'\ndf_rfm['Segment'] = 'NA'\ndf_rfm.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:48:35.753232Z","iopub.execute_input":"2021-12-06T12:48:35.753746Z","iopub.status.idle":"2021-12-06T12:49:16.770202Z","shell.execute_reply.started":"2021-12-06T12:48:35.753682Z","shell.execute_reply":"2021-12-06T12:49:16.769461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def Loyalty_assign(x):\n    if((x[5]==1) & (x[4]==1)):\n        return 'Loyal'\n       \n    elif((x[3]==1) & (x[4]==0)):\n        return 'Loyal'\n         \n    else:\n        return 'Not Loyal'\n    \ndef Segment_assign(x):\n    if((x[5]==1) & (x[3]==1) & (x[4]==1)):\n        return 'Champions'\n       \n    elif((x[5]==1) & (x[3]==1) & (x[4]==0)):\n        return 'Future Champions'\n         \n    elif((x[5]==1) & (x[3]==0) & (x[4]==1)):\n        return 'Very Valuable'\n         \n    elif((x[5]==1) & (x[3]==0) & (x[4]==0)):\n        return 'Hibernating'\n         \n    elif((x[5]==0) & (x[3]==1) & (x[4]==1)):\n        return 'Active'\n         \n    elif((x[5]==0) & (x[3]==1) & (x[4]==0)):\n        return 'About to Sleep'\n         \n    elif((x[5]==0) & (x[3]==0)):\n        return 'Lost'","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:49:16.771970Z","iopub.execute_input":"2021-12-06T12:49:16.772215Z","iopub.status.idle":"2021-12-06T12:49:16.784483Z","shell.execute_reply.started":"2021-12-06T12:49:16.772171Z","shell.execute_reply":"2021-12-06T12:49:16.783602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm['Segment'] = df_rfm.apply(Segment_assign, axis=1)\ndf_rfm['IsLoyal'] = df_rfm.apply(Loyalty_assign, axis=1)\ndf_rfm.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:49:16.785877Z","iopub.execute_input":"2021-12-06T12:49:16.786448Z","iopub.status.idle":"2021-12-06T12:50:42.133194Z","shell.execute_reply.started":"2021-12-06T12:49:16.786396Z","shell.execute_reply":"2021-12-06T12:50:42.131412Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm['IsLoyal'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.136552Z","iopub.execute_input":"2021-12-06T12:50:42.136812Z","iopub.status.idle":"2021-12-06T12:50:42.226114Z","shell.execute_reply.started":"2021-12-06T12:50:42.136763Z","shell.execute_reply":"2021-12-06T12:50:42.225139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm['Segment'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.229492Z","iopub.execute_input":"2021-12-06T12:50:42.230072Z","iopub.status.idle":"2021-12-06T12:50:42.315421Z","shell.execute_reply.started":"2021-12-06T12:50:42.230020Z","shell.execute_reply":"2021-12-06T12:50:42.314676Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_rfm.reset_index(inplace=True)\ndf_rfm.head(1)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.317451Z","iopub.execute_input":"2021-12-06T12:50:42.318088Z","iopub.status.idle":"2021-12-06T12:50:42.343420Z","shell.execute_reply.started":"2021-12-06T12:50:42.317847Z","shell.execute_reply":"2021-12-06T12:50:42.342671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_Segment = df_rfm.groupby('Segment', as_index=False).agg({'Monetary':sum, 'card_id':groupby_count, 'Frequency':sum})\ndf_Segment.columns = ['Segment','Monetary', 'No_Cards', 'Frequency']\ndf_Segment","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.346982Z","iopub.execute_input":"2021-12-06T12:50:42.347235Z","iopub.status.idle":"2021-12-06T12:50:42.500175Z","shell.execute_reply.started":"2021-12-06T12:50:42.347188Z","shell.execute_reply":"2021-12-06T12:50:42.499396Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_Loyal = df_rfm.groupby('IsLoyal', as_index=False).agg({'Monetary':sum, 'card_id':groupby_count, 'Frequency':sum})\ndf_Loyal.columns = ['Loyality','Monetary', 'No_Cards', 'Frequency']\ndf_Loyal","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.501825Z","iopub.execute_input":"2021-12-06T12:50:42.502401Z","iopub.status.idle":"2021-12-06T12:50:42.711937Z","shell.execute_reply.started":"2021-12-06T12:50:42.502345Z","shell.execute_reply":"2021-12-06T12:50:42.709823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"groups = df_Segment['Segment'].values.tolist()\namount = df_Segment['Monetary'].values.tolist()\n#colors = ['red', 'yellow', 'green', 'orange']\n\ntrace = go.Pie(labels=groups, values=amount, hoverinfo='label+percent', textinfo='value', textfont=dict(size=25),\n       pull=.4,hole=.2,marker=dict(line=dict(color='#000000', width=3)))\n\niplot([trace])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:42.716377Z","iopub.execute_input":"2021-12-06T12:50:42.716909Z","iopub.status.idle":"2021-12-06T12:50:43.148285Z","shell.execute_reply.started":"2021-12-06T12:50:42.716693Z","shell.execute_reply":"2021-12-06T12:50:43.147364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"groups = df_Loyal['Loyality'].values.tolist()\namount = df_Loyal['Monetary'].values.tolist()\n#colors = ['red', 'yellow', 'green', 'orange']\n\ntrace = go.Pie(labels=groups, values=amount, hoverinfo='label+percent', textinfo='value', textfont=dict(size=25),\n       pull=.4,hole=.2,marker=dict(line=dict(color='#000000', width=3)))\n\niplot([trace])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:43.149733Z","iopub.execute_input":"2021-12-06T12:50:43.150052Z","iopub.status.idle":"2021-12-06T12:50:43.273737Z","shell.execute_reply.started":"2021-12-06T12:50:43.150001Z","shell.execute_reply":"2021-12-06T12:50:43.272914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"groups = df_Segment['Segment'].values.tolist()\nNo_Company = df_Segment['No_Cards'].values.tolist()\nDistinct_Frequency = df_Segment['Frequency'].values.tolist()\n#colors = ['blue','red', 'yellow', 'pink','violet','green', 'orange']\n\n#trace2 = go.Bar(x=groups,y=No_Company,name='Companies', marker=dict(color=colors))\n\ntrace1 = go.Bar(x=groups,y=Distinct_Frequency,name='Frequency')\ntrace2 = go.Bar(x=groups,y=No_Company,name='Cards/Customers')\ndata = [trace1, trace2]\n\nlayout = go.Layout(barmode='stack')\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='stacked-bar')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:43.275101Z","iopub.execute_input":"2021-12-06T12:50:43.275597Z","iopub.status.idle":"2021-12-06T12:50:43.484263Z","shell.execute_reply.started":"2021-12-06T12:50:43.275541Z","shell.execute_reply":"2021-12-06T12:50:43.483369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"groups = df_Loyal['Loyality'].values.tolist()\nNo_Company = df_Loyal['No_Cards'].values.tolist()\nDistinct_Frequency = df_Loyal['Frequency'].values.tolist()\n#colors = ['blue','red', 'yellow', 'pink','violet','green', 'orange']\n\n#trace2 = go.Bar(x=groups,y=No_Company,name='Companies', marker=dict(color=colors))\n\ntrace1 = go.Bar(x=groups,y=Distinct_Frequency,name='Frequency')\ntrace2 = go.Bar(x=groups,y=No_Company,name='Cards/Customers')\ndata = [trace1, trace2]\n\nlayout = go.Layout(barmode='stack')\n\nfig = go.Figure(data=data, layout=layout)\niplot(fig, filename='stacked-bar')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:50:43.485685Z","iopub.execute_input":"2021-12-06T12:50:43.485983Z","iopub.status.idle":"2021-12-06T12:50:43.657290Z","shell.execute_reply.started":"2021-12-06T12:50:43.485934Z","shell.execute_reply":"2021-12-06T12:50:43.656498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Clustering Algorithms Based Segmentation","metadata":{"trusted":true}},{"cell_type":"code","source":"# df_rfm.set_index('card_id', inplace=True)\n# df_rfm.head(1)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.957291Z","iopub.status.idle":"2021-12-06T12:25:50.958019Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rank_df = df_rfm[['Recency','Frequency', 'Monetary']].rank(method='first')\nrank_df.head(2)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.732604Z","iopub.execute_input":"2021-12-06T13:23:47.732832Z","iopub.status.idle":"2021-12-06T13:23:47.748182Z","shell.execute_reply.started":"2021-12-06T13:23:47.732786Z","shell.execute_reply":"2021-12-06T13:23:47.747003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"normalized_df = (rank_df - rank_df.mean()) / rank_df.std()\nnormalized_df.head(2)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.749367Z","iopub.status.idle":"2021-12-06T13:23:47.750073Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.cluster import KMeans\ndata = normalized_df[['Recency', 'Frequency', 'Monetary']]\n\nsse = {}\nfor k in range(1, 10):\n    kmeans = KMeans(n_clusters=k, max_iter=1000).fit(data)\n    data[\"clusters\"] = kmeans.labels_\n    sse[k] = kmeans.inertia_ # Inertia: Sum of distances of samples to their closest cluster center\nplt.figure()\nplt.plot(list(sse.keys()), list(sse.values()))\nplt.xlabel(\"Number of cluster\")\nplt.ylabel(\"SSE\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.751332Z","iopub.status.idle":"2021-12-06T13:23:47.752005Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.metrics import silhouette_score","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.753224Z","iopub.status.idle":"2021-12-06T13:23:47.753899Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(normalized_df.shape)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.755214Z","iopub.status.idle":"2021-12-06T13:23:47.755910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# for n_cluster in tqdm([2,3,4,5]):\n#     kmeans = KMeans(n_clusters=n_cluster, max_iter=100).fit(normalized_df[['Recency', 'Frequency', 'Monetary']])\n    \n#     silhouette_avg = silhouette_score(normalized_df[['Recency', 'Frequency', 'Monetary']], kmeans.labels_)\n    \n#     print('Silhouette Score for %i Clusters: %0.4f' % (n_cluster, silhouette_avg))","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.969197Z","iopub.status.idle":"2021-12-06T12:25:50.969914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.cluster import KMeans\n\nkmeans = KMeans(n_clusters=4,max_iter=1000).fit(normalized_df[['Recency', 'Frequency', 'Monetary']])\nprint(kmeans.labels_)\nprint(kmeans.cluster_centers_)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.757148Z","iopub.status.idle":"2021-12-06T13:23:47.757815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from collections import Counter\nz = kmeans.labels_\nCounter(z)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.759019Z","iopub.status.idle":"2021-12-06T13:23:47.759670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"normalized_df['Cluster'] = kmeans.labels_\nnormalized_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.760864Z","iopub.status.idle":"2021-12-06T13:23:47.761518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_kmeans_rfm = df_rfm[['Recency','Frequency', 'Monetary']].copy()\ndf_kmeans_rfm['Cluster'] = kmeans.labels_\nprint(df_kmeans_rfm['Cluster'].value_counts())\ndf_kmeans_rfm.head(5)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.762698Z","iopub.status.idle":"2021-12-06T13:23:47.763367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# trace1 = go.Scatter3d(\n#     x=normalized_df['Recency'],\n#     z=normalized_df['Monetary'],\n#     y=normalized_df['Frequency'],\n#     mode='markers',\n#     marker=dict(size=12,color=df_kmeans_rfm['Cluster'],))\n\n# data = [trace1]\n\n# layout = go.Layout(margin=dict(l=0,r=0,b=0,t=0))\n\n# fig = go.Figure(data=data, layout=layout)\n# iplot(fig, filename='3d-scatter-colorscale')","metadata":{"execution":{"iopub.status.busy":"2021-12-06T12:25:50.979072Z","iopub.status.idle":"2021-12-06T12:25:50.979789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"colors = ['red', 'yellow', 'green', 'orange']\n\nfor i in ['Monetary']:\n    trace = go.Pie(labels=df_kmeans_rfm['Cluster'], values=df_kmeans_rfm[i], \n           hoverinfo='label+percent', textinfo='value', textfont=dict(size=15),\n           marker=dict(colors=colors, line=dict(color='#000000', width=3)))\n    iplot([trace])","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.866227Z","iopub.execute_input":"2021-12-06T13:23:47.866451Z","iopub.status.idle":"2021-12-06T13:23:47.880857Z","shell.execute_reply.started":"2021-12-06T13:23:47.866408Z","shell.execute_reply":"2021-12-06T13:23:47.879398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_kmeans_rfm.head(5)","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:47.882223Z","iopub.status.idle":"2021-12-06T13:23:47.882865Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in [\"Recency\",\"Frequency\",\"Monetary\"]:\n    print()\n    plt.figure(figsize=(14,8))\n    ax = sns.boxplot(x=\"Cluster\", y=col, data=normalized_df)\n    plt.title('Cluster/Segment wise Difference in Purchase '+col,fontsize=15)\n    plt.show()\n    print()","metadata":{"execution":{"iopub.status.busy":"2021-12-06T13:23:48.287675Z","iopub.execute_input":"2021-12-06T13:23:48.287950Z","iopub.status.idle":"2021-12-06T13:23:48.301975Z","shell.execute_reply.started":"2021-12-06T13:23:48.287894Z","shell.execute_reply":"2021-12-06T13:23:48.300803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}