{"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":"markdown","source":"\n\n    Transactions analysis:\n        Q1 - Which are the TOP 100 articles in terms of sold quantity?\n        Q2 - Are there articles that have been sold only once?\n        Q3 - Which are the TOP 100 articles that generated most earnings for the company?\n        Q4 - Which are articles that generated lower earnings for the company?\n\n    Customer Analysis:\n        Q5 - Which age group purchase more articles?\n        Q6 - Which age group generates more earnings for the company?\n        Q7 - Do active customers on the fashion news purchase more articles?\n        Q8 - Does the club member status influence the purchased quantity of articles?\n","metadata":{}},{"cell_type":"markdown","source":"\nImport libraries\n","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nfrom pandasql import sqldf\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nplt.style.use('seaborn-white')\nsns.set_style(\"whitegrid\")\nsns.despine()\nplt.rc(\"figure\", autolayout=True)\nplt.rc(\"axes\", labelweight=\"bold\", labelsize=\"large\", titleweight=\"bold\", titlesize=14, titlepad=10)\n\nimport matplotlib as mpl\n\nmpl.rcParams['axes.spines.left'] = False\nmpl.rcParams['axes.spines.right'] = False\nmpl.rcParams['axes.spines.top'] = False\nmpl.rcParams['axes.spines.bottom'] = False\nplt.rcParams[\"font.weight\"] = \"bold\"\nplt.rcParams[\"axes.labelweight\"] = \"bold\"","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:39:05.735386Z","iopub.execute_input":"2022-05-26T12:39:05.736007Z","iopub.status.idle":"2022-05-26T12:39:07.014732Z","shell.execute_reply.started":"2022-05-26T12:39:05.735890Z","shell.execute_reply":"2022-05-26T12:39:07.013483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nData import\n","metadata":{}},{"cell_type":"code","source":"df_a = pd.read_csv(\"/kaggle/input/h-and-m-personalized-fashion-recommendations/articles.csv\")\ndf_t = pd.read_csv(\"/kaggle/input/h-and-m-personalized-fashion-recommendations/transactions_train.csv\")\ndf_c = pd.read_csv(\"/kaggle/input/h-and-m-personalized-fashion-recommendations/customers.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:39:07.017341Z","iopub.execute_input":"2022-05-26T12:39:07.017881Z","iopub.status.idle":"2022-05-26T12:40:27.569580Z","shell.execute_reply.started":"2022-05-26T12:39:07.017834Z","shell.execute_reply":"2022-05-26T12:40:27.567936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nArticles dataframe analysis\n","metadata":{}},{"cell_type":"code","source":"df_a.head(1)","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:27.571925Z","iopub.execute_input":"2022-05-26T12:40:27.572950Z","iopub.status.idle":"2022-05-26T12:40:27.617282Z","shell.execute_reply.started":"2022-05-26T12:40:27.572902Z","shell.execute_reply":"2022-05-26T12:40:27.616316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\ndf_a.columns\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:27.621193Z","iopub.execute_input":"2022-05-26T12:40:27.621627Z","iopub.status.idle":"2022-05-26T12:40:27.627840Z","shell.execute_reply.started":"2022-05-26T12:40:27.621594Z","shell.execute_reply":"2022-05-26T12:40:27.626795Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"The dataframe articles has {len(df_a)} rows\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:27.629043Z","iopub.execute_input":"2022-05-26T12:40:27.629383Z","iopub.status.idle":"2022-05-26T12:40:27.643452Z","shell.execute_reply.started":"2022-05-26T12:40:27.629356Z","shell.execute_reply":"2022-05-26T12:40:27.642404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nTo filter the columns, we will use SQL like code through SQL-DF library.\n","metadata":{}},{"cell_type":"code","source":"df_a = sqldf(\"\"\"SELECT article_id, prod_name, product_type_name, product_group_name, colour_group_name, index_name\n            FROM df_a\n            \"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:27.645067Z","iopub.execute_input":"2022-05-26T12:40:27.645603Z","iopub.status.idle":"2022-05-26T12:40:35.236768Z","shell.execute_reply.started":"2022-05-26T12:40:27.645560Z","shell.execute_reply":"2022-05-26T12:40:35.235620Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_a.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:35.238189Z","iopub.execute_input":"2022-05-26T12:40:35.238569Z","iopub.status.idle":"2022-05-26T12:40:35.250950Z","shell.execute_reply.started":"2022-05-26T12:40:35.238538Z","shell.execute_reply":"2022-05-26T12:40:35.249953Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nTransactions dataframe analysis\n","metadata":{}},{"cell_type":"code","source":"\n\ndf_t.head(1)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:35.252449Z","iopub.execute_input":"2022-05-26T12:40:35.252904Z","iopub.status.idle":"2022-05-26T12:40:35.271327Z","shell.execute_reply.started":"2022-05-26T12:40:35.252873Z","shell.execute_reply":"2022-05-26T12:40:35.270301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_t.columns","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:35.272881Z","iopub.execute_input":"2022-05-26T12:40:35.273313Z","iopub.status.idle":"2022-05-26T12:40:35.285360Z","shell.execute_reply.started":"2022-05-26T12:40:35.273269Z","shell.execute_reply":"2022-05-26T12:40:35.284342Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"The dataframe Transactions has {len(df_t)} rows\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:35.288387Z","iopub.execute_input":"2022-05-26T12:40:35.289517Z","iopub.status.idle":"2022-05-26T12:40:35.298082Z","shell.execute_reply.started":"2022-05-26T12:40:35.289467Z","shell.execute_reply":"2022-05-26T12:40:35.296833Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nThe Transactions dataframe has more than 31 million rows: in order to save memory, we decide to drop some columns and keep only \"customer_id\", \"article_id\", \"price\".\n","metadata":{}},{"cell_type":"code","source":"df_t = df_t[[\"customer_id\", \"article_id\", \"price\"]]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:35.299436Z","iopub.execute_input":"2022-05-26T12:40:35.299965Z","iopub.status.idle":"2022-05-26T12:40:36.028307Z","shell.execute_reply.started":"2022-05-26T12:40:35.299922Z","shell.execute_reply":"2022-05-26T12:40:36.027332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\nTransactions analysis\n","metadata":{}},{"cell_type":"code","source":"df_sold_qty = df_t[\"article_id\"].value_counts()\ndf_sold_qty","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:36.029747Z","iopub.execute_input":"2022-05-26T12:40:36.030249Z","iopub.status.idle":"2022-05-26T12:40:37.638915Z","shell.execute_reply.started":"2022-05-26T12:40:36.030204Z","shell.execute_reply":"2022-05-26T12:40:37.637763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\ndf_sold_qty=df_sold_qty.reset_index()\ndf_sold_qty.rename(columns = {\"article_id\":\"sold_qty\",\"index\":\"article_id\"}, inplace=True)\ndf_sold_qty.head()\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:37.640419Z","iopub.execute_input":"2022-05-26T12:40:37.640809Z","iopub.status.idle":"2022-05-26T12:40:37.656664Z","shell.execute_reply.started":"2022-05-26T12:40:37.640778Z","shell.execute_reply":"2022-05-26T12:40:37.655851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_sold_qty","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:37.658099Z","iopub.execute_input":"2022-05-26T12:40:37.659100Z","iopub.status.idle":"2022-05-26T12:40:37.670699Z","shell.execute_reply.started":"2022-05-26T12:40:37.659058Z","shell.execute_reply":"2022-05-26T12:40:37.669638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_sold_qty[\"sold_qty\"].describe()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:37.671966Z","iopub.execute_input":"2022-05-26T12:40:37.672544Z","iopub.status.idle":"2022-05-26T12:40:37.702930Z","shell.execute_reply.started":"2022-05-26T12:40:37.672507Z","shell.execute_reply":"2022-05-26T12:40:37.701960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nSummary statistics on the sold quantities:\n\n    there are 105000 different articles in the transactions.\n    There are items which have been sold only once\n    25% of sold products, have been sold 14 or less times\n    50% were sold 65 or less times\n    75% were sold 286 or less times,\n    The most sold item have been sold 50287 times.\n\n","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(12,4))\nplt.title(\"Sold Quantity KDE plot\")\nsns.kdeplot(df_sold_qty[\"sold_qty\"])\nplt.xlabel(\"Sold Quantity\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:37.703965Z","iopub.execute_input":"2022-05-26T12:40:37.704844Z","iopub.status.idle":"2022-05-26T12:40:38.643170Z","shell.execute_reply.started":"2022-05-26T12:40:37.704806Z","shell.execute_reply":"2022-05-26T12:40:38.642329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"distribution is heavily right skewed.","metadata":{}},{"cell_type":"code","source":"\n\ndf_sold_qty[\"sold_qty\"].quantile([0.90,0.95,0.99,0.999])\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:38.644269Z","iopub.execute_input":"2022-05-26T12:40:38.644727Z","iopub.status.idle":"2022-05-26T12:40:38.658876Z","shell.execute_reply.started":"2022-05-26T12:40:38.644697Z","shell.execute_reply":"2022-05-26T12:40:38.657780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"very small minority of items that sold more than 10k times (just the 0.001%)","metadata":{}},{"cell_type":"markdown","source":"Q1 - Which are the TOP 100 articles in terms of sold quantity?","metadata":{}},{"cell_type":"code","source":"top_100_sold = df_sold_qty.iloc[:100]\ntop_100_sold.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:38.660018Z","iopub.execute_input":"2022-05-26T12:40:38.660706Z","iopub.status.idle":"2022-05-26T12:40:38.670934Z","shell.execute_reply.started":"2022-05-26T12:40:38.660671Z","shell.execute_reply":"2022-05-26T12:40:38.669369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_details = sqldf(\"\"\"SELECT *\n        FROM top_100_sold t\n        INNER JOIN df_a a\n        on t.article_id = a.article_id\n    \"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:38.672474Z","iopub.execute_input":"2022-05-26T12:40:38.673016Z","iopub.status.idle":"2022-05-26T12:40:40.881878Z","shell.execute_reply.started":"2022-05-26T12:40:38.672980Z","shell.execute_reply":"2022-05-26T12:40:40.881018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\ntop_100_details.head()\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:40.883048Z","iopub.execute_input":"2022-05-26T12:40:40.883915Z","iopub.status.idle":"2022-05-26T12:40:40.897608Z","shell.execute_reply.started":"2022-05-26T12:40:40.883873Z","shell.execute_reply":"2022-05-26T12:40:40.896558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_details.iloc[:30]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:40.898965Z","iopub.execute_input":"2022-05-26T12:40:40.899430Z","iopub.status.idle":"2022-05-26T12:40:40.931897Z","shell.execute_reply.started":"2022-05-26T12:40:40.899399Z","shell.execute_reply":"2022-05-26T12:40:40.931128Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_details.iloc[:30].groupby(\"prod_name\")[\"sold_qty\"].sum()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:40.933676Z","iopub.execute_input":"2022-05-26T12:40:40.934384Z","iopub.status.idle":"2022-05-26T12:40:40.953027Z","shell.execute_reply.started":"2022-05-26T12:40:40.934337Z","shell.execute_reply":"2022-05-26T12:40:40.952320Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(10,8))\nplt.title(\"TOP 30 most sold products\", fontsize=33, fontweight=\"bold\")\nno=30\ng = sns.barplot(y=\"prod_name\", x=\"sold_qty(%)\", data=top_100_details.iloc[:no].groupby(\"prod_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=12)\nplt.xlabel(\"Sold Quantity (%)\", size=25, fontweight=\"bold\")\nplt.ylabel(\"\")\nplt.grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:40.955988Z","iopub.execute_input":"2022-05-26T12:40:40.956358Z","iopub.status.idle":"2022-05-26T12:40:41.722526Z","shell.execute_reply.started":"2022-05-26T12:40:40.956328Z","shell.execute_reply":"2022-05-26T12:40:41.721551Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n    The trousers \"Jade HW Skinny Denim TRS \" is responsible for almost 19% of all sold products.\n    the TOP 4 of most sold items, is responsible for almost 40% of the TOP 100 sold products.\n","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(2,2, figsize=(13,9.5))\nplt.suptitle(\"TOP 100 most sold products characteristics\", fontweight=\"bold\",fontsize=30)\n\nno=100\n\ng = sns.barplot(y=\"product_type_name\", x=\"sold_qty(%)\", data=top_100_details.iloc[:no].groupby(\"product_type_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[0,0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=12, color=\"black\")\nax[0,0].set_ylabel(\"\")\nax[0,0].set_xlabel(\"Sold Quantity (%)\", size=22, fontweight=\"bold\")\nax[0,0].set_title(\"Product Type\",fontweight=\"bold\",fontsize=28)\nax[0,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"index_name\", x=\"sold_qty(%)\", data=top_100_details.iloc[:no].groupby(\"index_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[0,1],palette=\"viridis\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15, color=\"black\")\nax[0,1].set_ylabel(\"\")\nax[0,1].set_xlabel(\"Sold Quantity (%)\", size=22, fontweight=\"bold\")\nax[0,1].set_title(\"Index\",fontweight=\"bold\",fontsize=28)\nax[0,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"colour_group_name\", x=\"sold_qty(%)\", data=top_100_details.iloc[:no].groupby(\"colour_group_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[1,0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15, color=\"black\")\nax[1,0].set_ylabel(\"\")\nax[1,0].set_xlabel(\"Sold Quantity (%)\", size=22, fontweight=\"bold\")\nax[1,0].set_title(\"Colour Group\",fontweight=\"bold\",fontsize=28)\nax[1,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"product_group_name\", x=\"sold_qty(%)\", data=top_100_details.iloc[:no].groupby(\"product_group_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[1,1],palette=\"Reds_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15, color=\"black\")\nax[1,1].set_ylabel(\"\")\nax[1,1].set_xlabel(\"Sold Quantity (%)\", size=22, fontweight=\"bold\")\nax[1,1].set_title(\"Product Group\",fontweight=\"bold\",fontsize=28)\nax[1,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\nfig.tight_layout()\n\nplt.show() ","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:41.724338Z","iopub.execute_input":"2022-05-26T12:40:41.724973Z","iopub.status.idle":"2022-05-26T12:40:43.508152Z","shell.execute_reply.started":"2022-05-26T12:40:41.724931Z","shell.execute_reply":"2022-05-26T12:40:43.507129Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nAmong the TOP 100 of solds products:\n\n    Almost 30% of sold products are trousers\n    38% is Ladieswear\n    30% is Lingeries/Tights\n    Over 70% are black colored\n    Almost 40% are related to lower body\n\n","metadata":{}},{"cell_type":"markdown","source":"\nQ2 - Are there articles that have been sold only once?\n","metadata":{}},{"cell_type":"code","source":"df_sold_qty[\"sold_qty\"].where(lambda x: x==1).dropna() #top 15% products","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:43.509508Z","iopub.execute_input":"2022-05-26T12:40:43.509886Z","iopub.status.idle":"2022-05-26T12:40:43.530410Z","shell.execute_reply.started":"2022-05-26T12:40:43.509854Z","shell.execute_reply":"2022-05-26T12:40:43.529564Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_sold_qty[\"sold_qty\"].where(lambda x: x==1).dropna().to_frame()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:43.531499Z","iopub.execute_input":"2022-05-26T12:40:43.532371Z","iopub.status.idle":"2022-05-26T12:40:43.559160Z","shell.execute_reply.started":"2022-05-26T12:40:43.532319Z","shell.execute_reply":"2022-05-26T12:40:43.557988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Almost 4500 different items have been sold just once.\nSince in the \"Transactions\" dataframe there are around 100000 different items, this means that among the transactions, almost 4.5% of the products have only been sold once.","metadata":{}},{"cell_type":"markdown","source":"items that sold only once","metadata":{}},{"cell_type":"code","source":"\n\nworst_sold = df_sold_qty.tail(4491)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:43.560645Z","iopub.execute_input":"2022-05-26T12:40:43.561526Z","iopub.status.idle":"2022-05-26T12:40:43.566242Z","shell.execute_reply.started":"2022-05-26T12:40:43.561480Z","shell.execute_reply":"2022-05-26T12:40:43.565359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"worst_sold","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:43.570828Z","iopub.execute_input":"2022-05-26T12:40:43.571868Z","iopub.status.idle":"2022-05-26T12:40:43.584803Z","shell.execute_reply.started":"2022-05-26T12:40:43.571814Z","shell.execute_reply":"2022-05-26T12:40:43.583844Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"worst_details = sqldf(\"\"\"SELECT *\n        FROM worst_sold t\n        INNER JOIN df_a a\n        on t.article_id = a.article_id\n    \"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:43.586146Z","iopub.execute_input":"2022-05-26T12:40:43.587231Z","iopub.status.idle":"2022-05-26T12:40:46.032856Z","shell.execute_reply.started":"2022-05-26T12:40:43.587182Z","shell.execute_reply":"2022-05-26T12:40:46.031853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(2,2, figsize=(19,14))\nplt.suptitle(\"Characteristic of products sold only once\", size=38, fontweight=\"bold\")\n\nno=100\n\ng = sns.barplot(y=\"product_type_name\", x=\"sold_qty(%)\", data=worst_details.iloc[:no].groupby(\"product_type_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[0,0],palette=\"viridis_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=12, color=\"black\")\nax[0,0].set_ylabel(\"\")\nax[0,0].set_xlabel(\"Sold Quantity (%)\", size=20, fontweight=\"bold\")\nax[0,0].set_title(\"Product Type\", size=25, fontweight=\"bold\")\nax[0,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"index_name\", x=\"sold_qty(%)\", data=worst_details.iloc[:no].groupby(\"index_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[0,1],palette=\"Reds_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15, color=\"black\")\nax[0,1].set_ylabel(\"\")\nax[0,1].set_xlabel(\"Sold Quantity (%)\", size=20, fontweight=\"bold\")\nax[0,1].set_title(\"Index\", size=25, fontweight=\"bold\")\nax[0,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"colour_group_name\", x=\"sold_qty(%)\", data=worst_details.iloc[:no].groupby(\"colour_group_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[1,0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=12, color=\"black\")\nax[1,0].set_ylabel(\"\")\nax[1,0].set_xlabel(\"Sold Quantity (%)\", size=20, fontweight=\"bold\")\nax[1,0].set_title(\"Colour Group\", size=25, fontweight=\"bold\")\nax[1,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"product_group_name\", x=\"sold_qty(%)\", data=worst_details.iloc[:no].groupby(\"product_group_name\")[\"sold_qty\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('sold_qty(%)').reset_index().sort_values(by=\"sold_qty(%)\", ascending=False), \\\n            ax=ax[1,1],palette=\"Blues_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15, color=\"black\")\nax[1,1].set_ylabel(\"\")\nax[1,1].set_xlabel(\"Sold Quantity (%)\", size=20, fontweight=\"bold\")\nax[1,1].set_title(\"Product Group\", size=25, fontweight=\"bold\")\nax[1,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\nfig.tight_layout()\n\nplt.show() ","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:46.034152Z","iopub.execute_input":"2022-05-26T12:40:46.034540Z","iopub.status.idle":"2022-05-26T12:40:48.405655Z","shell.execute_reply.started":"2022-05-26T12:40:46.034509Z","shell.execute_reply":"2022-05-26T12:40:48.404555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"almost 60% of only sold once items are for children and baby","metadata":{}},{"cell_type":"markdown","source":"Q3 - Which are the TOP 100 articles that generated most earnings for the company?","metadata":{}},{"cell_type":"markdown","source":"The earnings can be calculated by multiplying the price of each product by its total sold quanity. ","metadata":{}},{"cell_type":"code","source":"df_t_p_a = df_t[[\"price\",\"article_id\"]]\ndf_t_p_a","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:46:28.786164Z","iopub.execute_input":"2022-05-26T12:46:28.787966Z","iopub.status.idle":"2022-05-26T12:46:29.108920Z","shell.execute_reply.started":"2022-05-26T12:46:28.787908Z","shell.execute_reply":"2022-05-26T12:46:29.107852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_t_p_a[df_t_p_a['article_id']==706016001]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:46:46.058875Z","iopub.execute_input":"2022-05-26T12:46:46.059336Z","iopub.status.idle":"2022-05-26T12:46:46.108389Z","shell.execute_reply.started":"2022-05-26T12:46:46.059297Z","shell.execute_reply":"2022-05-26T12:46:46.107323Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_prices = df_t[[\"price\",\"article_id\"]].groupby(\"article_id\").sum().sort_values(by=\"price\", ascending=False)","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:48.705176Z","iopub.execute_input":"2022-05-26T12:40:48.705760Z","iopub.status.idle":"2022-05-26T12:40:50.445658Z","shell.execute_reply.started":"2022-05-26T12:40:48.705715Z","shell.execute_reply":"2022-05-26T12:40:50.444541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_prices","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:50.448174Z","iopub.execute_input":"2022-05-26T12:40:50.448682Z","iopub.status.idle":"2022-05-26T12:40:50.461211Z","shell.execute_reply.started":"2022-05-26T12:40:50.448635Z","shell.execute_reply":"2022-05-26T12:40:50.460351Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_prices.rename(columns={\"price\":\"earning\"}, inplace=True)\ndf_prices = df_prices.reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:40:58.043977Z","iopub.execute_input":"2022-05-26T12:40:58.045234Z","iopub.status.idle":"2022-05-26T12:40:58.051834Z","shell.execute_reply.started":"2022-05-26T12:40:58.045187Z","shell.execute_reply":"2022-05-26T12:40:58.050943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_prices","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:41:00.588127Z","iopub.execute_input":"2022-05-26T12:41:00.588554Z","iopub.status.idle":"2022-05-26T12:41:00.601873Z","shell.execute_reply.started":"2022-05-26T12:41:00.588519Z","shell.execute_reply":"2022-05-26T12:41:00.600871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"most earnings generated by a product is 1631","metadata":{}},{"cell_type":"code","source":"print(\"Number of different sold articles:\",len(df_prices[\"earning\"]))\nprint(\"Total Earnings:\",df_prices[\"earning\"].sum())","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:47:45.343448Z","iopub.execute_input":"2022-05-26T12:47:45.344365Z","iopub.status.idle":"2022-05-26T12:47:45.350896Z","shell.execute_reply.started":"2022-05-26T12:47:45.344325Z","shell.execute_reply":"2022-05-26T12:47:45.350166Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\nfor i in [10,50,100,200,300,400,1000]:\n    print(\"The TOP {} of products that generate most earnings, account for the {:.2f} % of total earnings\".format(i, df_prices[\"earning\"].iloc[:i].sum() / df_prices[\"earning\"].iloc[:].sum() * 100) ) \n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:48:14.995055Z","iopub.execute_input":"2022-05-26T12:48:14.995952Z","iopub.status.idle":"2022-05-26T12:48:15.007291Z","shell.execute_reply.started":"2022-05-26T12:48:14.995913Z","shell.execute_reply":"2022-05-26T12:48:15.006217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"TOP 100 of over 100000 products, generates around 5% of the total earnings. It can be interesting to check these products names and characteristics","metadata":{}},{"cell_type":"code","source":"top_100_prices=df_prices.iloc[:100]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:49:17.165976Z","iopub.execute_input":"2022-05-26T12:49:17.166476Z","iopub.status.idle":"2022-05-26T12:49:17.171437Z","shell.execute_reply.started":"2022-05-26T12:49:17.166439Z","shell.execute_reply":"2022-05-26T12:49:17.170610Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_price_details = sqldf(\"\"\"SELECT *\n        FROM top_100_prices t\n        INNER JOIN df_a a\n        on t.article_id = a.article_id\"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:49:31.846088Z","iopub.execute_input":"2022-05-26T12:49:31.847124Z","iopub.status.idle":"2022-05-26T12:49:34.110426Z","shell.execute_reply.started":"2022-05-26T12:49:31.847073Z","shell.execute_reply":"2022-05-26T12:49:34.109491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_100_price_details.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:49:39.047612Z","iopub.execute_input":"2022-05-26T12:49:39.048283Z","iopub.status.idle":"2022-05-26T12:49:39.062824Z","shell.execute_reply.started":"2022-05-26T12:49:39.048235Z","shell.execute_reply":"2022-05-26T12:49:39.061871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(10,11))\nplt.title(\"TOP 50 most profitable products\", size=40, fontweight=\"bold\")\nno=50\ng = sns.barplot(y=\"prod_name\", x=\"earning(%)\", data=top_100_price_details.iloc[:no].groupby(\"prod_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=15)\nplt.xlabel(\"Earnings (%)\", size=25, fontweight=\"bold\")\nplt.ylabel(\"\")\nplt.grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:49:49.645997Z","iopub.execute_input":"2022-05-26T12:49:49.646466Z","iopub.status.idle":"2022-05-26T12:49:50.566557Z","shell.execute_reply.started":"2022-05-26T12:49:49.646435Z","shell.execute_reply":"2022-05-26T12:49:50.565320Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(2,2, figsize=(13,9))\nplt.suptitle(\"TOP 100 most profitable products characteristics\", fontweight=\"bold\", fontsize=30)\n\nno=100\n\ng = sns.barplot(y=\"product_type_name\", x=\"earning(%)\", data=top_100_price_details.iloc[:no].groupby(\"product_type_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[0,0],palette=\"Blues_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=14, color=\"black\")\nax[0,0].set_ylabel(\"\")\nax[0,0].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[0,0].set_title(\"Product Type\", size=25,fontweight=\"bold\")\nax[0,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\n\ng = sns.barplot(y=\"index_name\", x=\"earning(%)\", data=top_100_price_details.iloc[:no].groupby(\"index_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[0,1],palette=\"viridis\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', padding = 5, fontsize=18, color=\"black\")\nax[0,1].set_ylabel(\"\")\nax[0,1].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[0,1].set_title(\"Index\", size=25,fontweight=\"bold\")\nax[0,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\n\ng = sns.barplot(y=\"colour_group_name\", x=\"earning(%)\", data=top_100_price_details.iloc[:no].groupby(\"colour_group_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[1,0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=18, color=\"black\")\nax[1,0].set_ylabel(\"\")\nax[1,0].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[1,0].set_title(\"Colour Group\", size=25,fontweight=\"bold\")\nax[1,0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\ng = sns.barplot(y=\"product_group_name\", x=\"earning(%)\", data=top_100_price_details.iloc[:no].groupby(\"product_group_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[1,1],palette=\"Reds_r\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', padding=5, fontsize=18, color=\"black\")\nax[1,1].set_ylabel(\"\")\nax[1,1].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[1,1].set_title(\"Product Group\", size=25,fontweight=\"bold\")\nax[1,1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\nfig.tight_layout()\n\nplt.show() \n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T12:56:27.160731Z","iopub.execute_input":"2022-05-26T12:56:27.161196Z","iopub.status.idle":"2022-05-26T12:56:28.796381Z","shell.execute_reply.started":"2022-05-26T12:56:27.161162Z","shell.execute_reply":"2022-05-26T12:56:28.795343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nInsights:\n\n    Over 60% of the TOP 100 products in terms of earnings are generated by selling trousers\n    Around 50% of these products are divided (a H&M teenage collection)\n    37% of the products are from the Ladieswear line\n    55% of the products are black\n    66.2% of the products are related to lower body\n","metadata":{}},{"cell_type":"markdown","source":"It is also important to notice that the TOP 100 most profitable products list do not exactly match the TOP 100 most sold products one, since lots of products that sells a lot in quantity are cheap, and so generate less earnings","metadata":{}},{"cell_type":"markdown","source":"Q4 - Which are articles that generated lower earnings for the company?","metadata":{}},{"cell_type":"code","source":"worst_100_prices=df_prices.iloc[-100:]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:02:05.416909Z","iopub.execute_input":"2022-05-26T13:02:05.417420Z","iopub.status.idle":"2022-05-26T13:02:05.422982Z","shell.execute_reply.started":"2022-05-26T13:02:05.417360Z","shell.execute_reply":"2022-05-26T13:02:05.421863Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"worst_100_price_details = sqldf(\"\"\"SELECT *\n        FROM worst_100_prices t\n        INNER JOIN df_a a\n        on t.article_id = a.article_id\"\"\")","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:02:10.252354Z","iopub.execute_input":"2022-05-26T13:02:10.252814Z","iopub.status.idle":"2022-05-26T13:02:12.688942Z","shell.execute_reply.started":"2022-05-26T13:02:10.252778Z","shell.execute_reply":"2022-05-26T13:02:12.687674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"worst_100_price_details.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:02:15.018094Z","iopub.execute_input":"2022-05-26T13:02:15.018897Z","iopub.status.idle":"2022-05-26T13:02:15.032959Z","shell.execute_reply.started":"2022-05-26T13:02:15.018859Z","shell.execute_reply":"2022-05-26T13:02:15.032114Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(2,1, figsize=(25,15))\nplt.suptitle(\"FLOP 100 Worst profitable products characteristics (1)\", fontsize=40 ,fontweight=\"bold\")\n\nno=100\n\ng = sns.barplot(x=\"product_type_name\", y=\"earning(%)\", data=worst_100_price_details.iloc[:no].groupby(\"product_type_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', fontsize=18)\nax[0].grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nax[0].set_xlabel(\"\")\nax[0].set_ylabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[0].set_xticklabels(g.get_xticklabels(), rotation=80)\nax[0].set_title(\"Product Type\", size=35,fontweight=\"bold\")\n\ng = sns.barplot(x=\"colour_group_name\", y=\"earning(%)\", data=worst_100_price_details.iloc[:no].groupby(\"colour_group_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[1],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', fontsize=18)\nax[1].set_ylabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[1].set_xlabel(\"\")\nax[1].set_xticklabels(g.get_xticklabels(), rotation=80)\nax[1].set_title(\"Colour Group\", size=35,fontweight=\"bold\")\nax[1].grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\n\n\nfig.tight_layout()\n\nplt.show() ","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:02:27.472351Z","iopub.execute_input":"2022-05-26T13:02:27.473870Z","iopub.status.idle":"2022-05-26T13:02:29.596736Z","shell.execute_reply.started":"2022-05-26T13:02:27.473810Z","shell.execute_reply":"2022-05-26T13:02:29.595071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nInsights:\n\n    17.4% of them are t shirts\n    12.5% are socks\n    There are quite a lot of accessories like hair bands, clips etc..\n\n","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(2,1, figsize=(16,9))\nplt.suptitle(\"FLOP 100 Worst profitable products characteristics (2)\", fontsize=33 ,fontweight=\"bold\")\n\nno=100\n\ng = sns.barplot(y=\"index_name\", x=\"earning(%)\", data=worst_100_price_details.iloc[:no].groupby(\"index_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[0],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', fontsize=18)\nax[0].set_ylabel(\"\")\nax[0].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[0].set_title(\"Index\",size=25,fontweight=\"bold\")\nax[0].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.2)\n             \ng = sns.barplot(y=\"product_group_name\", x=\"earning(%)\", data=worst_100_price_details.iloc[:no].groupby(\"product_group_name\")[\"earning\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index().sort_values(by=\"earning(%)\", ascending=False), \\\n            ax=ax[1],palette=\"mako\", ci=False)\nfor container in g.containers:\n    g.bar_label(container, fmt='%.1f', fontsize=18)\nax[1].set_ylabel(\"\")\nax[1].set_xlabel(\"Earnings (%)\", size=20,fontweight=\"bold\")\nax[1].set_title(\"Product Group\", size=25,fontweight=\"bold\")\nax[1].grid(axis=\"x\",color = 'grey', linestyle = '--', linewidth = 1.5)\n             \nplt.tight_layout()\n             \nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:03:05.683631Z","iopub.execute_input":"2022-05-26T13:03:05.684044Z","iopub.status.idle":"2022-05-26T13:03:06.552195Z","shell.execute_reply.started":"2022-05-26T13:03:05.684013Z","shell.execute_reply":"2022-05-26T13:03:06.551156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Customer Analayis","metadata":{}},{"cell_type":"markdown","source":"which customers are responsible for most purchases.","metadata":{}},{"cell_type":"code","source":"\n\ndf_t.head()\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:03:54.914712Z","iopub.execute_input":"2022-05-26T13:03:54.915241Z","iopub.status.idle":"2022-05-26T13:03:54.929119Z","shell.execute_reply.started":"2022-05-26T13:03:54.915200Z","shell.execute_reply":"2022-05-26T13:03:54.927737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_cust_prices = df_t[[\"customer_id\", \"price\"]].groupby(\"customer_id\").sum()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:17:04.020201Z","iopub.execute_input":"2022-05-26T13:17:04.020661Z","iopub.status.idle":"2022-05-26T13:17:21.817757Z","shell.execute_reply.started":"2022-05-26T13:17:04.020630Z","shell.execute_reply":"2022-05-26T13:17:21.816642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\ndf_cust_prices.head()\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:17:30.817522Z","iopub.execute_input":"2022-05-26T13:17:30.817924Z","iopub.status.idle":"2022-05-26T13:17:30.827127Z","shell.execute_reply.started":"2022-05-26T13:17:30.817894Z","shell.execute_reply":"2022-05-26T13:17:30.826361Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_cust_qty = df_t[[\"customer_id\", \"article_id\"]].groupby(\"customer_id\").count()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:32:01.947850Z","iopub.execute_input":"2022-05-26T13:32:01.948853Z","iopub.status.idle":"2022-05-26T13:32:17.695300Z","shell.execute_reply.started":"2022-05-26T13:32:01.948814Z","shell.execute_reply":"2022-05-26T13:32:17.694377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_cust_qty.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:32:17.696613Z","iopub.execute_input":"2022-05-26T13:32:17.697340Z","iopub.status.idle":"2022-05-26T13:32:17.705961Z","shell.execute_reply.started":"2022-05-26T13:32:17.697308Z","shell.execute_reply":"2022-05-26T13:32:17.705001Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_qty_price = pd.merge(df_cust_prices, df_cust_qty, on='customer_id', how='inner')","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:37:00.180592Z","iopub.execute_input":"2022-05-26T13:37:00.181116Z","iopub.status.idle":"2022-05-26T13:37:02.052972Z","shell.execute_reply.started":"2022-05-26T13:37:00.181082Z","shell.execute_reply":"2022-05-26T13:37:02.052275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_qty_price.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:37:06.040920Z","iopub.execute_input":"2022-05-26T13:37:06.041840Z","iopub.status.idle":"2022-05-26T13:37:06.052246Z","shell.execute_reply.started":"2022-05-26T13:37:06.041798Z","shell.execute_reply":"2022-05-26T13:37:06.051545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_c.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:37:22.323472Z","iopub.execute_input":"2022-05-26T13:37:22.323862Z","iopub.status.idle":"2022-05-26T13:37:22.339941Z","shell.execute_reply.started":"2022-05-26T13:37:22.323833Z","shell.execute_reply":"2022-05-26T13:37:22.338703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_details = pd.merge(cust_qty_price, df_c.drop(\"postal_code\", axis=1), on='customer_id', how='inner')","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:41:51.971434Z","iopub.execute_input":"2022-05-26T13:41:51.972401Z","iopub.status.idle":"2022-05-26T13:41:55.116583Z","shell.execute_reply.started":"2022-05-26T13:41:51.972354Z","shell.execute_reply":"2022-05-26T13:41:55.115411Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_details.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:42:14.611141Z","iopub.execute_input":"2022-05-26T13:42:14.611556Z","iopub.status.idle":"2022-05-26T13:42:14.627578Z","shell.execute_reply.started":"2022-05-26T13:42:14.611527Z","shell.execute_reply":"2022-05-26T13:42:14.626604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\nprint(f\"In total there are {len(cust_details)} different customers\")\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:42:40.768969Z","iopub.execute_input":"2022-05-26T13:42:40.769409Z","iopub.status.idle":"2022-05-26T13:42:40.775422Z","shell.execute_reply.started":"2022-05-26T13:42:40.769375Z","shell.execute_reply":"2022-05-26T13:42:40.774093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Purchased Quantity by Customer Analysis","metadata":{}},{"cell_type":"code","source":"cust_details.article_id.describe()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:42:57.351768Z","iopub.execute_input":"2022-05-26T13:42:57.353222Z","iopub.status.idle":"2022-05-26T13:42:57.413642Z","shell.execute_reply.started":"2022-05-26T13:42:57.353160Z","shell.execute_reply":"2022-05-26T13:42:57.412930Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nBy calling the \"describe\" method on the \"article_id\" column, we can observe that:\n\n    The minimum purchased quantity by a single customer is 1\n    25% of customers Purchased 3 or less items\n    50% of customers Purchased 9 or less items\n    75% of customers Purchased 27 or less items\n    The maximum purchased quantity by a single customer is 1895 products\n\n","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(10,4))\nplt.title(\"Distribution of purchased quantity by customer\", fontweight=\"bold\", size=20)\nsns.kdeplot(cust_details[\"article_id\"])\nplt.xlabel(\"purchased quantity\",fontweight=\"bold\", size=20)\nplt.ylabel(\"Count\",fontweight=\"bold\", size=20)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:43:32.913959Z","iopub.execute_input":"2022-05-26T13:43:32.914375Z","iopub.status.idle":"2022-05-26T13:43:38.201357Z","shell.execute_reply.started":"2022-05-26T13:43:32.914343Z","shell.execute_reply":"2022-05-26T13:43:38.200145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nanalyze the age and other provided features of the customer to better find insights on the customers and their purchase behaviour.","metadata":{}},{"cell_type":"code","source":"\n\nplt.figure(figsize=(10,5))\nplt.title(\"Customers age distribution\", fontweight=\"bold\", size=30)\nplt.hist(cust_details[\"age\"], bins=70, edgecolor=\"black\", color=\"#1ABC9C\")\nplt.xlabel(\"Age\",fontweight=\"bold\", size=20)\nplt.ylabel(\"Count\",fontweight=\"bold\", size=20)\nplt.show()\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:47:17.767008Z","iopub.execute_input":"2022-05-26T13:47:17.767500Z","iopub.status.idle":"2022-05-26T13:47:18.228291Z","shell.execute_reply.started":"2022-05-26T13:47:17.767466Z","shell.execute_reply":"2022-05-26T13:47:18.227281Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"distribution of the age feature is bivariate. In order to create more effective plots, we will create a categorical column for age which divides the ages in age groups","metadata":{}},{"cell_type":"markdown","source":"\nQ5 - Which age group purchase more articles?\n","metadata":{}},{"cell_type":"code","source":"\n\ncust_details['age_groups'] = pd.cut(cust_details['age'], bins=[16, 20, 30, 40,50, 60, 70, float('Inf')], labels=['16-20', '20-30','30-40','40-50','50-60','60-70' , '70+'])\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:51:28.695705Z","iopub.execute_input":"2022-05-26T13:51:28.696735Z","iopub.status.idle":"2022-05-26T13:51:28.758738Z","shell.execute_reply.started":"2022-05-26T13:51:28.696664Z","shell.execute_reply":"2022-05-26T13:51:28.757843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_details","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:51:31.468746Z","iopub.execute_input":"2022-05-26T13:51:31.470031Z","iopub.status.idle":"2022-05-26T13:51:31.504526Z","shell.execute_reply.started":"2022-05-26T13:51:31.469909Z","shell.execute_reply":"2022-05-26T13:51:31.503225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(8,5))\nplt.title(\"Purchased quantity by age group\\n\", fontweight=\"bold\", size=28)\ng = sns.barplot(x=\"age_groups\", y=\"Purchased Quantity(%)\", data=cust_details.groupby(\"age_groups\")[\"article_id\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('Purchased Quantity(%)').reset_index(), palette=\"icefire\", edgecolor=\"black\")\nplt.xlabel(\"Age Group\",fontweight=\"bold\", size=22)\nplt.ylabel(\"Purchased Quantity (%)\",fontweight=\"bold\", size=19)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=18, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:52:06.674756Z","iopub.execute_input":"2022-05-26T13:52:06.675578Z","iopub.status.idle":"2022-05-26T13:52:07.052929Z","shell.execute_reply.started":"2022-05-26T13:52:06.675537Z","shell.execute_reply":"2022-05-26T13:52:07.051744Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nInsights:\n\n    Customers in the range 20-30 are responsible for more than 42% of the total purchased products.\n    Customers in the range 16-20. 60-70 and 70+ are responsible for the 8% of the total purchased products\n    Customers in the range 30-40, 40-50 and 50-60 are responsible for 16% of purchased quantity each.\n\n","metadata":{}},{"cell_type":"markdown","source":"Q6 - Which age group generates more earnings for the company?","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(8,5))\nplt.title(\"Company Earnings by age group\\n\", fontweight=\"bold\", size=28)\ng = sns.barplot(x=\"age_groups\", y=\"earning(%)\", data=cust_details.groupby(\"age_groups\")[\"price\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('earning(%)').reset_index(), palette=\"icefire\",edgecolor=\"black\")\nplt.xlabel(\"Age Group\",fontweight=\"bold\", size=22)\nplt.ylabel(\"Earnings (%)\",fontweight=\"bold\", size=25)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=18, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:56:58.150161Z","iopub.execute_input":"2022-05-26T13:56:58.150633Z","iopub.status.idle":"2022-05-26T13:56:58.525900Z","shell.execute_reply.started":"2022-05-26T13:56:58.150602Z","shell.execute_reply":"2022-05-26T13:56:58.524291Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Q7 - Do active customers on the fashion news purchase more articles?","metadata":{}},{"cell_type":"code","source":"cust_details","metadata":{"execution":{"iopub.status.busy":"2022-05-26T13:59:53.156434Z","iopub.execute_input":"2022-05-26T13:59:53.157413Z","iopub.status.idle":"2022-05-26T13:59:53.182844Z","shell.execute_reply.started":"2022-05-26T13:59:53.157355Z","shell.execute_reply":"2022-05-26T13:59:53.181824Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(9,5))\nplt.title(\"Purchased quantity by Fashion News Frequency\\n\", fontweight=\"bold\", size=20)\ng = sns.barplot(x=\"fashion_news_frequency\", y=\"Purchased Quantity(%)\", data=cust_details.groupby(\"fashion_news_frequency\")[\"article_id\"].sum() \\\n            .transform(lambda x: (x / x.sum() * 100)).rename('Purchased Quantity(%)').reset_index(), palette=\"Spectral\", edgecolor=\"black\")\nplt.xlabel(\"Fashion News Frequency\",fontweight=\"bold\", size=22)\nplt.ylabel(\"Purchased Quantity (%)\",fontweight=\"bold\", size=25)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.3f', fontsize=18, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:00:26.192631Z","iopub.execute_input":"2022-05-26T14:00:26.194057Z","iopub.status.idle":"2022-05-26T14:00:26.709453Z","shell.execute_reply.started":"2022-05-26T14:00:26.193992Z","shell.execute_reply":"2022-05-26T14:00:26.708044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"interesting to check the fashion news frequency by age group, to find more useful insights","metadata":{}},{"cell_type":"code","source":"x, y = 'age_groups', 'fashion_news_frequency'\ndf_age_news = cust_details.groupby(x)[y].value_counts(normalize=True)\ndf_age_news = df_age_news.mul(100)\ndf_age_news = df_age_news.rename('percent(%)').reset_index()\ndf_age_news = df_age_news[df_age_news[\"fashion_news_frequency\"].isin([\"Regularly\",\"NONE\"])]","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:01:33.380455Z","iopub.execute_input":"2022-05-26T14:01:33.380874Z","iopub.status.idle":"2022-05-26T14:01:33.770211Z","shell.execute_reply.started":"2022-05-26T14:01:33.380832Z","shell.execute_reply":"2022-05-26T14:01:33.769015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"palette1 = {\"Regularly\":'#46C646', \"NONE\":'#FF0000'}\n\nplt.figure(figsize=(13,6))\nplt.title(\"Fashion News Frequency by age group\\n\",fontweight=\"bold\", size=33)\ng=sns.barplot(x=\"age_groups\", y=\"percent(%)\",data=df_age_news, hue=\"fashion_news_frequency\", palette=palette1)\nplt.xlabel(\"Age group\",fontweight=\"bold\", size=22)\nplt.ylabel(\"Percentage (%)\",fontweight=\"bold\", size=25)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.1f', fontsize=16, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.legend(title='News\\nFrequency',bbox_to_anchor=(1.0, 1.0), ncol=1, fancybox=True, shadow=True, fontsize=17,title_fontsize=22)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:01:48.129586Z","iopub.execute_input":"2022-05-26T14:01:48.130031Z","iopub.status.idle":"2022-05-26T14:01:48.610808Z","shell.execute_reply.started":"2022-05-26T14:01:48.129999Z","shell.execute_reply":"2022-05-26T14:01:48.610083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nWe can see that customers in the range 20-30 and 30-40 have the lowest percentage of fashion news frequency, while being the groups which buy the most.\nMoreover, the frequency of customer that regulary check fashion news starts increasing from the range 40-50, with a peak value of 43.7% of regular/active users for customers in the range 70+ years old. This means that checking fashion news seems to be more effective for older customers, who still represent a small percentage of total sold products, while younger customers do not need to check the news to buy new products.\nIt could be effective for the company to invite younger customers (range 20-40) to check the news more frequently in order to increase the sold items.\n","metadata":{}},{"cell_type":"markdown","source":"\nQ8 - Does the club member status influence the purchased quantity?","metadata":{}},{"cell_type":"code","source":"\n\ncust_details[\"club_member_status\"].value_counts(normalize=True)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:03:01.955913Z","iopub.execute_input":"2022-05-26T14:03:01.956952Z","iopub.status.idle":"2022-05-26T14:03:02.179613Z","shell.execute_reply.started":"2022-05-26T14:03:01.956901Z","shell.execute_reply":"2022-05-26T14:03:02.178631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n    More than 93% of the customers belong to the ACTIVE category\n    6.8% of the customers belong to the PRE-CREATE cateory\n    0.3% of the customers belong to the LEFT CLUB category\n","metadata":{}},{"cell_type":"code","source":"\n\nprint(\"The average quantity of purchased products by the customers is {:.0f} products \".format(cust_details[\"article_id\"].mean()))\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:03:48.859363Z","iopub.execute_input":"2022-05-26T14:03:48.859819Z","iopub.status.idle":"2022-05-26T14:03:48.867656Z","shell.execute_reply.started":"2022-05-26T14:03:48.859787Z","shell.execute_reply":"2022-05-26T14:03:48.866316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\nprint(\"The average quantity of purchased products by the ACTIVE customers is {:.0f} products \".format(cust_details.groupby(\"club_member_status\")[\"article_id\"].mean()[\"ACTIVE\"]))\nprint(\"The average quantity of purchased products by the LEFT-CLUB customers is {:.0f} products \".format(cust_details.groupby(\"club_member_status\")[\"article_id\"].mean()[\"LEFT CLUB\"]))\nprint(\"The average quantity of purchased products by the PRE-CREATE customers is {:.0f} products \".format(cust_details.groupby(\"club_member_status\")[\"article_id\"].mean()[\"PRE-CREATE\"]))\n\n","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:03:53.516589Z","iopub.execute_input":"2022-05-26T14:03:53.517001Z","iopub.status.idle":"2022-05-26T14:03:54.128766Z","shell.execute_reply.started":"2022-05-26T14:03:53.516969Z","shell.execute_reply":"2022-05-26T14:03:54.127643Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(9,5))\nplt.title(\"Average Purchased Quantity by Club Member Status\\n\", fontweight=\"bold\", size=22)\ng = sns.barplot(x=\"club_member_status\", y=\"article_id\", data=cust_details.groupby(\"club_member_status\")[\"article_id\"].mean().astype(int).reset_index(), palette=\"viridis\", edgecolor=\"black\")\nplt.axhline(y = cust_details[\"article_id\"].mean(), color = 'r', linestyle = '--')\nplt.text(0.76, 23.7, 'Mean Purchased Quantity: {:.0f}'.format(cust_details[\"article_id\"].mean()), size=16, color=\"red\",fontweight=\"bold\")\nplt.xlabel(\"Club Member Status\",fontweight=\"bold\", size=20)\nplt.ylabel(\"Average Purchased Quantity\",fontweight=\"bold\", size=16)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.0f', fontsize=23, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:04:05.183451Z","iopub.execute_input":"2022-05-26T14:04:05.184440Z","iopub.status.idle":"2022-05-26T14:04:05.679428Z","shell.execute_reply.started":"2022-05-26T14:04:05.184369Z","shell.execute_reply":"2022-05-26T14:04:05.678208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"customers belonging to the ACTIVE clubs, purchase more products than other categories","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(9,5))\nplt.title(\"Median Purchased Quantity by Club Member Status\\n\", fontweight=\"bold\", size=22)\ng = sns.barplot(x=\"club_member_status\", y=\"article_id\", data=cust_details.groupby(\"club_member_status\")[\"article_id\"].median().reset_index(), palette=\"viridis\", edgecolor=\"black\")\nplt.axhline(y = cust_details[\"article_id\"].median(), color = 'r', linestyle = '--')\nplt.text(0.76, 9.3, 'Median Purchased Quantity: {:.2f}'.format(cust_details[\"article_id\"].median()), size=16, color=\"red\",fontweight=\"bold\")\nplt.xlabel(\"Club Member Status\",fontweight=\"bold\", size=20)\nplt.ylabel(\"Median Purchaed Quantity\",fontweight=\"bold\", size=16)\nfor container in g.containers:\n    g.bar_label(container, padding = 5, fmt='%.0f', fontsize=23, color=\"black\")\nplt.grid(axis=\"y\",color = 'grey', linestyle = '--', linewidth = 1.5)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-05-26T14:04:39.866821Z","iopub.execute_input":"2022-05-26T14:04:39.867320Z","iopub.status.idle":"2022-05-26T14:04:40.454602Z","shell.execute_reply.started":"2022-05-26T14:04:39.867286Z","shell.execute_reply":"2022-05-26T14:04:40.453461Z"},"trusted":true},"execution_count":null,"outputs":[]}]}