{"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":"# **Detailed EDA - Understanding H&M data**  \n\n<img src=\"https://upload.wikimedia.org/wikipedia/commons/e/e5/HM-Logo.png\" alt=\"drawing\" width=\"400\"/>\n\n\nUnderstanding data is the most important part of any analysis. It's the basis of all decisions related to data preprocessing and later the ML machine model creation and finally the interpretation of the results.\n\nIn this notebook, you will find a detailed description of this dataset. I hope you find it useful.\n\nAs an introduction let's see who our client/stakeholder actually is:\n\n**H&M** is a very popular Swedish clothing company with a headquarter in Stockholm. The company was established in 1947 and was originally named Hennes. In 1968 they acquired the hunting and fishing store named Mauritz Widfoss. From this point in time it operated under name Hennes& Mauritz or simply H&M. Six years later the had a debut on the Stockholm Stock Exchange which allowed them soon after in 1976 to open their first shop outside Sweden - in UK, London. In 2000 they entered the US market. As of 2022 it operates shops in 74 countries what you can see on the map below:  \n\n<img src=\"https://upload.wikimedia.org/wikipedia/commons/f/f0/H%26M_Global_map_%281%29.png\" alt=\"drawing\" width=\"450\"/>\n\n\nMore about history of H&M you can read on their article [here](https://about.hm.com/content/dam/hmgroup/groupsite/documents/en/Digital%20Annual%20Report/2017/Annual%20Report%202017%20Our%20history.pdf).\n\n**Recommender Systems** are powerful, successful and widespread applications for almost every business selling products or services. It's especially useful for companies with a wide offer and diverse clients. Ideal examples are retail companies, like H&M, Zalando, etc. as well as these selling services or digital products - the best examples are Netflix or Spotify. If you visit websites of any of these firms you'll notice that after creating an account the service will start to recommend you other products, movies or songs that the algorithm thinks will suit you the best. It's their way to personalise the offer and who doesn't like to get such care. That's why these systems are precious for business owners. The more you buy, watch and listen the better it gets. Also, the more users the better it gets.  \n\nHowever, because this is a dynamic environment - both clients and product change it is quite a hassle to tune, manage and maintain these systems. That's why companies using this tool keep a big group of machine learning engineers and backend SE in a dedicated department or they outsource this work to other companies.\n\nMore about **Netflix Recommender System (NRS)** you can read [here](https://research.netflix.com/research-area/recommendations).  \n\nTo learn more about the **Zalando** recommender system check their [Engineering Blog](https://engineering.zalando.com/posts/2016/12/recommendations-galore-how-zalando-tech-makes-it-happen.html).\n\nRecommender Systems usually are classified into two groups:\n1. *Collaborative-filtering* which is based on users behaviours. However, it needs a lot of traffic data per customer to be fully successfull.\n2. *Content-based filtering* which is based on similarity and complementariness (what fits what) of products. This is good if you just started to collect traffic data but you have a good described and labeled products.\nMany companies create their custom-made, hybrid systems. In this competition we're free to decide what methodology we're are going to use.\n\nNow let's go to the data itself.\nThe first step: loading necessary libraries and 3 databases (articles, transactions and customers).","metadata":{}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns # nice visualisations\nimport matplotlib.pyplot as plt # basic visualisation library\nimport datetime as dt # library to opearate on dates\n\nprint(\"pandas version: {}\".format(pd.__version__))\nprint(\"numpy version: {}\".format(np.__version__))\nprint(\"seaborn version: {}\".format(sns.__version__))","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:23:23.454429Z","iopub.execute_input":"2022-03-12T17:23:23.454808Z","iopub.status.idle":"2022-03-12T17:23:23.463048Z","shell.execute_reply.started":"2022-03-12T17:23:23.454775Z","shell.execute_reply":"2022-03-12T17:23:23.462122Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"art = pd.read_csv(\"../input/h-and-m-personalized-fashion-recommendations/articles.csv\")\ncust = pd.read_csv(\"../input/h-and-m-personalized-fashion-recommendations/customers.csv\")\ntrans = pd.read_csv(\"../input/h-and-m-personalized-fashion-recommendations/transactions_train.csv\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:23:23.464808Z","iopub.execute_input":"2022-03-12T17:23:23.465596Z","iopub.status.idle":"2022-03-12T17:24:22.605310Z","shell.execute_reply.started":"2022-03-12T17:23:23.465558Z","shell.execute_reply":"2022-03-12T17:24:22.604303Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. Articles database  \n\nThis databasecontains information about the assortiment of H&M shops. It's important not to confuse it with the number of transactions for each article what is given in a different database.","metadata":{}},{"cell_type":"code","source":"import matplotlib as mpl\nmpl.rcParams.update(mpl.rcParamsDefault)\n\ndata = art\nart_dtypes = art.dtypes.value_counts()\n\nfig = plt.figure(figsize=(5,2),facecolor='white')\n\nax0 = fig.add_subplot(1,1,1)\nfont = 'monospace'\nax0.text(1, 0.8, \"Key figures\",color='black',fontsize=28, fontweight='bold', fontfamily=font, ha='center')\n\nax0.text(0, 0.4, \"{:,d}\".format(data.shape[0]), color='#fcba03', fontsize=24, fontweight='bold', fontfamily=font, ha='center')\nax0.text(0, 0.001, \"# of rows \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily=font,ha='center')\n\nax0.text(0.6, 0.4, \"{}\".format(data.shape[1]), color='#fcba03', fontsize=24, fontweight='bold', fontfamily=font, ha='center')\nax0.text(0.6, 0.001, \"# of features \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily=font,ha='center')\n\nax0.text(1.2, 0.4, \"{}\".format(art_dtypes[0]), color='#fcba03', fontsize=24, fontweight='bold', fontfamily=font, ha='center')\nax0.text(1.2, 0.001, \"# of text columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily=font, ha='center')\n\nax0.text(1.9, 0.4,\"{}\".format(art_dtypes[1]), color='#fcba03', fontsize=24, fontweight='bold', fontfamily=font, ha='center')\nax0.text(1.9, 0.001,\"# of numeric columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily=font,ha='center')\n\nax0.set_yticklabels('')\nax0.tick_params(axis='y',length=0)\nax0.tick_params(axis='x',length=0)\nax0.set_xticklabels('')\n\nfor direction in ['top','right','left','bottom']:\n    ax0.spines[direction].set_visible(False)\n\nfig.subplots_adjust(top=0.9, bottom=0.2, left=0, hspace=1)\n\nfig.patch.set_linewidth(5)\nfig.patch.set_edgecolor('#8c8c8c')\nfig.patch.set_facecolor('#f6f6f6')\nax0.set_facecolor('#f6f6f6')\n    \nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:22.606815Z","iopub.execute_input":"2022-03-12T17:24:22.607152Z","iopub.status.idle":"2022-03-12T17:24:22.802568Z","shell.execute_reply.started":"2022-03-12T17:24:22.607109Z","shell.execute_reply":"2022-03-12T17:24:22.801752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unique indentifier of an article:\n* ```article_id``` (int64) - an unique 9-digit identifier of the article, 105 542 unique values (as the length of the database)\n\n5 product related columns:\n* ```product_code``` (int64) - 6-digit product code (the first 6 digits of ```article_id```, 47 224 unique values\n* ```prod_name``` (object) - name of a product, 45 875 unique values\n* ```product_type_no``` (int64) - product type number, 131 unique values\n* ```product_type_name``` (object) - name of a product type, equivalent of ```product_type_no```\n* ```product_group_name``` (object) - name of a product group, in total 19 groups\n\n2 columns related to the pattern:\n* ```graphical_appearance_no``` (int64) - code of a pattern, 30 unique values\n* ```graphical_appearance_name``` (object) - name of a pattern, 30 unique values\n\n2 columns related to the color:\n* ```colour_group_code``` (int64) - code of a color, 50 unique values\n* ```colour_group_name``` (object) - name of a color, 50 unique values\n\n4 columns related to perceived colour (general tone):\n* ```perceived_colour_value_id``` - perceived color id, 8 unique values\n* ```perceived_colour_value_name``` - perceived color name, 8 unique values\n* ```perceived_colour_master_id``` - perceived master color id, 20 unique values\n* ```perceived_colour_master_name``` - perceived master color name, 20 unique values\n\n2 columns related to the department:\n* ```department_no``` - department number, 299 unique values\n* ```department_name``` - department name, 299 unique values\n\n4 columns related to the index, which is actually a top-level category:\n* ```index_code``` - index code, 10 unique values\n* ```index_name``` - index name, 10 unique values\n* ```index_group_no``` - index group code, 5 unique values\n* ```index_group_name``` - index group code, 5 unique values\n\n2 columns related to the section:\n* ```section_no``` - section number, 56 unique values\n* ```section_name``` - section name, 56 unique values\n\n2 columns related to the garment group:\n* ```garment_group_n``` - section number, 56 unique values\n* ```garment_group_name``` - section name, 56 unique values\n\n1 column with a detailed description of the article:\n* ```detail_desc``` - 43 404 unique values","metadata":{}},{"cell_type":"markdown","source":"Let's check how many missing values we have (in pct).","metadata":{}},{"cell_type":"code","source":"art.isna().sum()/len(art)*100","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:22.804286Z","iopub.execute_input":"2022-03-12T17:24:22.804648Z","iopub.status.idle":"2022-03-12T17:24:22.974961Z","shell.execute_reply.started":"2022-03-12T17:24:22.804618Z","shell.execute_reply":"2022-03-12T17:24:22.974029Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Only one column - ```detail desc``` - has missing values but this is a very small fraction of the dataset - about 0.4%.\n\nLet's visualise some of the articles. To do so I will create a helper function below. As mentioned in the competition description not all articles have an image. Therefore, this function will show selected amount of images from a given folder. It will also write an article name. What I've found is that a leading zero in the name of the folder does not correspond to the first digit of the ```article_id``` and it has to be stripped when looking for a product in the articles database. E.g.: *0108775015.jpg* is ```article_id``` *108775015*.","metadata":{}},{"cell_type":"code","source":"from os import walk\n\ndef show_articles(folder, no_images=3):\n    folder_path = '../input/h-and-m-personalized-fashion-recommendations/images/{}/'.format(folder)\n    # extracting all image names from a folder\n    files = []\n    for _, _, filenames in walk(folder_path):\n        files.extend(filenames)\n    no_files = len(files)\n    if no_images > no_files:\n        no_images = no_files\n        print(\"Warning! In the folder there are less images than requested.\")\n        \n    # plotting selected number of pictures\n    images = files[:no_images]\n    fig, ax = plt.subplots(1,no_images, figsize=(12,4))\n    for i, img in enumerate(images):\n        art_id = img.split('.')[0]\n        img = plt.imread(folder_path+img)\n        ax[i].imshow(img, aspect='equal')\n        ax[i].grid(False)\n        ax[i].set_xticks([], [])\n        ax[i].set_yticks([], [])\n        ax[i].set_xlabel(art[art['article_id']==int(art_id[1:])]['prod_name'].iloc[0])\n    plt.show()\n    \nshow_articles('020',4)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:22.976098Z","iopub.execute_input":"2022-03-12T17:24:22.976328Z","iopub.status.idle":"2022-03-12T17:24:24.674484Z","shell.execute_reply.started":"2022-03-12T17:24:22.976301Z","shell.execute_reply":"2022-03-12T17:24:24.673612Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In a hidden cell below there's a helper function for plotting sorted horizontal barplots.","metadata":{}},{"cell_type":"code","source":"import matplotlib.ticker as mtick\n\ndef plot_bar(database, col, figsize=(13,5), pct=False, label='articles'):\n    fig, ax = plt.subplots(figsize=figsize, facecolor='#f6f6f6')\n    for loc in ['bottom', 'left']:\n        ax.spines[loc].set_visible(True)\n        ax.spines[loc].set_linewidth(2)\n        ax.spines[loc].set_color('black')\n    ax.spines['right'].set_visible(False)\n    ax.spines['top'].set_visible(False)\n    \n    if pct:\n        data = database[col].value_counts()\n        data = data.div(data.sum()).mul(100)\n        data = data.reset_index()\n        ax = sns.barplot(data=data, x=col, y='index', color='#2693d7', lw=1.5, ec='black', zorder=2)\n        ax.set_xlabel('% of ' + label, fontsize=10, weight='bold')\n        ax.xaxis.set_major_formatter(mtick.PercentFormatter())\n    else:\n        data = database[col].value_counts().reset_index()\n        ax = sns.barplot(data=data, x=col, y='index', color='#2693d7', lw=1.5, ec='black', zorder=2)        \n        ax.set_xlabel('# of articles' + label)\n        \n    ax.grid(zorder=0)\n    ax.text(0, -0.75, col, color='black', fontsize=10, ha='left', va='bottom', weight='bold', style='italic')\n    ax.set_ylabel('')\n        \n    plt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:29:50.848272Z","iopub.execute_input":"2022-03-12T17:29:50.849062Z","iopub.status.idle":"2022-03-12T17:29:50.862302Z","shell.execute_reply.started":"2022-03-12T17:29:50.849021Z","shell.execute_reply":"2022-03-12T17:29:50.861165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.set_style(\"darkgrid\", {\"axes.facecolor\": \".9\"})\nplot_bar(art, 'index_group_name', pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:30:40.144029Z","iopub.execute_input":"2022-03-12T17:30:40.144393Z","iopub.status.idle":"2022-03-12T17:30:40.436974Z","shell.execute_reply.started":"2022-03-12T17:30:40.144355Z","shell.execute_reply":"2022-03-12T17:30:40.436129Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most of the articles are in categories (index) of *Ladieswear* and *Baby/Children*. The smallest amount of articles is in *Sport* group.","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'index_name', pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:30:41.427738Z","iopub.execute_input":"2022-03-12T17:30:41.428019Z","iopub.status.idle":"2022-03-12T17:30:41.760635Z","shell.execute_reply.started":"2022-03-12T17:30:41.427991Z","shell.execute_reply":"2022-03-12T17:30:41.759656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Once again the dominant group is *Ladieswear*. However, this time the second group is *Divided*. Let's check what this group actually is.  \n\nAfter asking a subject matter expert, I know that this is a category for teenagers.","metadata":{}},{"cell_type":"code","source":"art_divided = art[art['index_name']=='Divided']\nart_divided[['prod_name','product_type_name','detail_desc','index_name','section_name','garment_group_name']].drop_duplicates().head(12)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:25.354448Z","iopub.execute_input":"2022-03-12T17:24:25.354679Z","iopub.status.idle":"2022-03-12T17:24:25.417200Z","shell.execute_reply.started":"2022-03-12T17:24:25.354652Z","shell.execute_reply":"2022-03-12T17:24:25.416135Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's visualise some of articles from this category. To do so I will create a helper function that can be reused later.","metadata":{}},{"cell_type":"code","source":"def show_items_in_category(column, value, no_imgs=4, title=None):\n    data = art[art[column]==value]\n    cat_ids = data['article_id'].iloc[:no_imgs].to_list()\n    \n    fig, ax = plt.subplots(1, no_imgs, figsize=(12,4))\n\n    for i, prod_id in enumerate(cat_ids):\n        folder = str(prod_id)[:2]\n        file_path = '../input/h-and-m-personalized-fashion-recommendations/images/0{}/0{}.jpg'.format(folder, prod_id)\n\n        img = plt.imread(file_path)       \n        ax[i].imshow(img, aspect='equal')\n        ax[i].grid(False)\n        ax[i].set_xticks([], [])\n        ax[i].set_yticks([], [])\n        ax[i].set_xlabel(art[art['article_id']==int(prod_id)]['prod_name'].iloc[0])\n    \n    fig.suptitle(title)\n    plt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:25.421049Z","iopub.execute_input":"2022-03-12T17:24:25.421619Z","iopub.status.idle":"2022-03-12T17:24:25.432008Z","shell.execute_reply.started":"2022-03-12T17:24:25.421568Z","shell.execute_reply":"2022-03-12T17:24:25.431110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"show_items_in_category('index_name', 'Divided', 5, 'Articles from a \"Divided\" category')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:25.433145Z","iopub.execute_input":"2022-03-12T17:24:25.433865Z","iopub.status.idle":"2022-03-12T17:24:27.536832Z","shell.execute_reply.started":"2022-03-12T17:24:25.433832Z","shell.execute_reply":"2022-03-12T17:24:27.536026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We see now also that index is a sub-category of ```index_group```. We can create now a multi-index fram with groupings to see the counts:","metadata":{}},{"cell_type":"code","source":"art.groupby(['index_group_name', 'index_name']).size()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:27.538502Z","iopub.execute_input":"2022-03-12T17:24:27.539063Z","iopub.status.idle":"2022-03-12T17:24:27.574338Z","shell.execute_reply.started":"2022-03-12T17:24:27.539019Z","shell.execute_reply":"2022-03-12T17:24:27.573390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Even finer category is ```product_group_name```. Let's see what is it's structure and articles counts.","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'product_group_name', pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:27.575790Z","iopub.execute_input":"2022-03-12T17:24:27.576329Z","iopub.status.idle":"2022-03-12T17:24:28.004263Z","shell.execute_reply.started":"2022-03-12T17:24:27.576284Z","shell.execute_reply":"2022-03-12T17:24:28.003308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The barchart above shows that most of articles lays in only few groups. Let's see the cumulative sum and a Pareto graph.","metadata":{}},{"cell_type":"code","source":"data = art['product_group_name'].value_counts()\ndata = data.div(data.sum()).mul(100)\npareto = data.cumsum().rename('cumulative_pct')\npareto","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:28.005654Z","iopub.execute_input":"2022-03-12T17:24:28.005903Z","iopub.status.idle":"2022-03-12T17:24:28.033007Z","shell.execute_reply.started":"2022-03-12T17:24:28.005872Z","shell.execute_reply":"2022-03-12T17:24:28.032035Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = pareto.reset_index()\ndata.columns = ['group', 'cumulative_pct']\ndata.index += 1\ndata['cumulative_pct'][6]/100","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:24:28.034793Z","iopub.execute_input":"2022-03-12T17:24:28.035061Z","iopub.status.idle":"2022-03-12T17:24:28.048124Z","shell.execute_reply.started":"2022-03-12T17:24:28.035028Z","shell.execute_reply":"2022-03-12T17:24:28.047245Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(15,5))\n\ndata = pareto.reset_index()\ndata.columns = ['features', 'cumulative_pct']\ndata.index += 1\n\nsns.lineplot(data=data, x=data.index, y='cumulative_pct')\n\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\n    \nax.set_xticks(pareto.reset_index().index)\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nax.set_ylabel('cumulative percentage')\nax.set_xlabel('number of product groups')\nax.set_xlim(1)\nax.set_ylim(data['cumulative_pct'][1])\n\nax.vlines(7, data['cumulative_pct'][1], data['cumulative_pct'][7], color='orange', ls='--')\nax.hlines(data['cumulative_pct'][7], 1, 7, color='orange', ls='--')\nax.text(0, 1.05, 'Pareto graph of the product groups', color='black', fontsize=10, ha='left', va='bottom', weight='bold', style='italic', transform=ax.transAxes)\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:28.049488Z","iopub.execute_input":"2022-03-12T17:24:28.049733Z","iopub.status.idle":"2022-03-12T17:24:28.436139Z","shell.execute_reply.started":"2022-03-12T17:24:28.049705Z","shell.execute_reply":"2022-03-12T17:24:28.435247Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Subcategories of ```product_group``` is ```product_type```. Taking into consideration there are 131 types plotting a barchar may be useless. So let's check how many product types are in each group.","metadata":{}},{"cell_type":"code","source":"for group in art['product_group_name'].unique():\n    print('Number of subcategories in \"{}\"\" is {}.'.format(group, len(art.groupby(['product_group_name', 'product_type_name']).size()[group])))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:28.437495Z","iopub.execute_input":"2022-03-12T17:24:28.437826Z","iopub.status.idle":"2022-03-12T17:24:28.905326Z","shell.execute_reply.started":"2022-03-12T17:24:28.437789Z","shell.execute_reply":"2022-03-12T17:24:28.904262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We see that the accessories and shoes have the biggest number of subcategories.\n\nThe key takeaways here are:\n* The hierarhy of categories is: index_group --> index --> group --> type\n* Over 80% of the products lays in 4 product groups (out of 19)\n\n\nLet's investigate now some other feature of the H&M products.  \nFirst in what colours are H&M products:","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'colour_group_name', figsize=(15,12), pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:28.906867Z","iopub.execute_input":"2022-03-12T17:24:28.907095Z","iopub.status.idle":"2022-03-12T17:24:29.875333Z","shell.execute_reply.started":"2022-03-12T17:24:28.907056Z","shell.execute_reply":"2022-03-12T17:24:29.874242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Next let's see the perceived color, which is general color range like: dark, light, bright, etc.","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'perceived_colour_value_name', pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:29.876902Z","iopub.execute_input":"2022-03-12T17:24:29.877233Z","iopub.status.idle":"2022-03-12T17:24:30.206539Z","shell.execute_reply.started":"2022-03-12T17:24:29.877178Z","shell.execute_reply":"2022-03-12T17:24:30.205579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"I'm wondering what ```Dusty Light``` actually is. Let's visualise.","metadata":{}},{"cell_type":"code","source":"show_items_in_category('perceived_colour_value_name', 'Dusty Light', 5, 'Dusty Light articles')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:30.207788Z","iopub.execute_input":"2022-03-12T17:24:30.208023Z","iopub.status.idle":"2022-03-12T17:24:32.184949Z","shell.execute_reply.started":"2022-03-12T17:24:30.207996Z","shell.execute_reply":"2022-03-12T17:24:32.184290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"After looking at the pictures above we see what does it mean. Interestingly the last picture shows two items where one is grey-ish while the other is black.","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'perceived_colour_master_name', pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:32.186115Z","iopub.execute_input":"2022-03-12T17:24:32.186684Z","iopub.status.idle":"2022-03-12T17:24:32.620353Z","shell.execute_reply.started":"2022-03-12T17:24:32.186646Z","shell.execute_reply":"2022-03-12T17:24:32.619685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Perceived colour is more or less in line with the colour itself but it's more generic. \n\nNow it's time for patterns.","metadata":{}},{"cell_type":"code","source":"plot_bar(art, 'graphical_appearance_name', figsize=(14,7), pct=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:32.621303Z","iopub.execute_input":"2022-03-12T17:24:32.622017Z","iopub.status.idle":"2022-03-12T17:24:33.119522Z","shell.execute_reply.started":"2022-03-12T17:24:32.621980Z","shell.execute_reply":"2022-03-12T17:24:33.118681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most of H&M articles are without any pattern - they fall into a category ```solid```. Let's visualise next two categories.","metadata":{}},{"cell_type":"code","source":"show_items_in_category('graphical_appearance_name', 'All over pattern', 5,  'All over pattern')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:33.120720Z","iopub.execute_input":"2022-03-12T17:24:33.120938Z","iopub.status.idle":"2022-03-12T17:24:35.339057Z","shell.execute_reply.started":"2022-03-12T17:24:33.120912Z","shell.execute_reply":"2022-03-12T17:24:35.338169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"show_items_in_category('graphical_appearance_name', 'Melange', 5,  'Melange')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:35.340861Z","iopub.execute_input":"2022-03-12T17:24:35.341485Z","iopub.status.idle":"2022-03-12T17:24:36.686706Z","shell.execute_reply.started":"2022-03-12T17:24:35.341441Z","shell.execute_reply":"2022-03-12T17:24:36.685822Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As I'm a total layman in fashion from pure curiosity I'll check what are some other patterns.","metadata":{}},{"cell_type":"code","source":"show_items_in_category('graphical_appearance_name', 'Lace', 5,  'Lace')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:36.687798Z","iopub.execute_input":"2022-03-12T17:24:36.688022Z","iopub.status.idle":"2022-03-12T17:24:39.088187Z","shell.execute_reply.started":"2022-03-12T17:24:36.687994Z","shell.execute_reply":"2022-03-12T17:24:39.087359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"show_items_in_category('graphical_appearance_name', 'Embroidery', 5,  'Embroidery')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:39.089816Z","iopub.execute_input":"2022-03-12T17:24:39.090346Z","iopub.status.idle":"2022-03-12T17:24:40.888869Z","shell.execute_reply.started":"2022-03-12T17:24:39.090298Z","shell.execute_reply":"2022-03-12T17:24:40.887404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's see some exemplary descriptions.","metadata":{}},{"cell_type":"code","source":"art['detail_desc'].drop_duplicates().to_list()[:10]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:40.890378Z","iopub.execute_input":"2022-03-12T17:24:40.890678Z","iopub.status.idle":"2022-03-12T17:24:40.920380Z","shell.execute_reply.started":"2022-03-12T17:24:40.890642Z","shell.execute_reply":"2022-03-12T17:24:40.919582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Nice way to visualise descriptions is to create a word cloud. To do this we have to tokenize the descriptions and later join all tokens into a bag of words or like in this case into a one long text.","metadata":{}},{"cell_type":"code","source":"import nltk\nfrom nltk.corpus import stopwords\nfrom wordcloud import WordCloud, STOPWORDS # library to create a wordcloud\nfrom PIL import Image\n\n# creating cloud of words\nwords_raw = art['detail_desc'].dropna().apply(nltk.word_tokenize)\nbag_of_words = \" \".join(words_raw.explode())\nstopwords = set(STOPWORDS)\n\n# creating cloud of words\nfig, ax1 = plt.subplots(figsize=(8,6))\nwordcloud = WordCloud(stopwords=stopwords, background_color=\"white\", height=300, contour_width=3).generate(bag_of_words)\nplt.imshow(wordcloud, interpolation='bilinear')\nplt.axis(\"off\")\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:24:40.925514Z","iopub.execute_input":"2022-03-12T17:24:40.925767Z","iopub.status.idle":"2022-03-12T17:25:42.563711Z","shell.execute_reply.started":"2022-03-12T17:24:40.925740Z","shell.execute_reply":"2022-03-12T17:25:42.562489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2. Customers database\n\nThis database contains data about customers. Collecting this type of data allows company to tune their recommender system. This data contains data which can be treated as 'static' or slowly-changing, usually these are features like sex, age, address, hight, etc. Let's look what H&M gave us.","metadata":{}},{"cell_type":"code","source":"mpl.rcParams.update(mpl.rcParamsDefault)\n\ncust_dtypes = cust.dtypes.value_counts()\ndata = cust\n\nfig = plt.figure(figsize=(5,2),facecolor='white')\n\nax0 = fig.add_subplot(1,1,1)\nax0.text(1.0, 1, \"Key figures\",color='black',fontsize=28, fontweight='bold', fontfamily='monospace',ha='center')\n\nax0.text(0, 0.4, \"{:,d}\".format(data.shape[0]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(0, 0.001, \"# of rows \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(0.6, 0.4, \"{}\".format(data.shape[1]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(0.6, 0.001, \"# of features \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(1.2, 0.4, \"{}\".format(cust_dtypes[0]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(1.2, 0.001, \"# of text columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(1.9, 0.4,\"{}\".format(cust_dtypes[1]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(1.9, 0.001,\"# of numeric columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.set_yticklabels('')\nax0.tick_params(axis='y',length=0)\nax0.tick_params(axis='x',length=0)\nax0.set_xticklabels('')\n\nfor direction in ['top','right','left','bottom']:\n    ax0.spines[direction].set_visible(False)\n    \nfig.patch.set_linewidth(5)\nfig.patch.set_edgecolor('#8c8c8c')\nfig.patch.set_facecolor('#f6f6f6')\nax0.set_facecolor('#f6f6f6')\n\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:42.565579Z","iopub.execute_input":"2022-03-12T17:25:42.566670Z","iopub.status.idle":"2022-03-12T17:25:42.800408Z","shell.execute_reply.started":"2022-03-12T17:25:42.566575Z","shell.execute_reply":"2022-03-12T17:25:42.799027Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unique indentifier of a customer:\n* ```customer_id``` - an unique identifier of the customer\n\n5 product related columns:\n* ```FN``` - binary feature (1 or NaN)\n* ```Active``` - binary feature (1 or NaN)\n* ```club_member_status``` - status in a club, 3 unique values\n* ```fashion_news_frequency``` - frequency of sending communication to the customer, 4 unique values\n* ```age```  - age of the customer\n* ```postal_code``` - postal code (anonimized), 352 899 unique values","metadata":{}},{"cell_type":"markdown","source":"Let's check the missing values:","metadata":{}},{"cell_type":"code","source":"cust.isna().sum()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:42.802186Z","iopub.execute_input":"2022-03-12T17:25:42.802751Z","iopub.status.idle":"2022-03-12T17:25:43.620473Z","shell.execute_reply.started":"2022-03-12T17:25:42.802718Z","shell.execute_reply":"2022-03-12T17:25:43.619318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Here I'll replace NaN for columns ```FN``` and ```Active``` with 0.","metadata":{}},{"cell_type":"code","source":"cust_backup = cust.copy()\ncust[['FN','Active']] = cust[['FN','Active']].fillna(0)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:43.622617Z","iopub.execute_input":"2022-03-12T17:25:43.622864Z","iopub.status.idle":"2022-03-12T17:25:43.752039Z","shell.execute_reply.started":"2022-03-12T17:25:43.622834Z","shell.execute_reply":"2022-03-12T17:25:43.751104Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(5,5))\nexplode = (0, 0.1)\ncolors = sns.color_palette('Paired')\nax.pie(cust['FN'].value_counts(), explode=explode, labels=['Not-FN','FN'],\n       autopct='%1.1f%%',shadow=True, startangle=90, colors=colors)\nax.axis('equal')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:43.754094Z","iopub.execute_input":"2022-03-12T17:25:43.754379Z","iopub.status.idle":"2022-03-12T17:25:43.986689Z","shell.execute_reply.started":"2022-03-12T17:25:43.754346Z","shell.execute_reply":"2022-03-12T17:25:43.985277Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(5,5))\nexplode = (0, 0.1)\ncolors = sns.color_palette('Paired')\nax.pie(cust['Active'].value_counts(), explode=explode, labels=['Not-active','Active'],\n       autopct='%1.1f%%',shadow=True, startangle=90, colors=colors)\nax.axis('equal')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:43.988752Z","iopub.execute_input":"2022-03-12T17:25:43.989915Z","iopub.status.idle":"2022-03-12T17:25:44.199645Z","shell.execute_reply.started":"2022-03-12T17:25:43.989853Z","shell.execute_reply":"2022-03-12T17:25:44.198331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"FN_Active = len(cust[(cust['FN']==1) & (cust['Active']==1)])/cust.shape[0]*100\nprint('Percentage of customers that have both FN and Active status: {}%'.format(round(FN_Active,2)))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:44.201869Z","iopub.execute_input":"2022-03-12T17:25:44.202676Z","iopub.status.idle":"2022-03-12T17:25:44.318378Z","shell.execute_reply.started":"2022-03-12T17:25:44.202620Z","shell.execute_reply":"2022-03-12T17:25:44.317035Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It look that all custmoers that are Active have also FN status. But reverse is not true - not all users with FN status are active. Let's check this by substaraction of these two sets. If it's correct we would expect to see a percentage difference in order of 0.9%.","metadata":{}},{"cell_type":"code","source":"FN_not_active = len(cust[(cust['FN']==1) & (cust['Active']!=1)])/cust.shape[0]*100\nprint('Percentage of customers that have FN status but are not Active: {}%'.format(round(FN_not_active,2)))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:44.320879Z","iopub.execute_input":"2022-03-12T17:25:44.322348Z","iopub.status.idle":"2022-03-12T17:25:44.354280Z","shell.execute_reply.started":"2022-03-12T17:25:44.322178Z","shell.execute_reply":"2022-03-12T17:25:44.352611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As we remember from the missing values analysis there is also a group of people where we do not have any data. Perhaps, these people are the ones without membership at all - to add them to the visialisation I'll fill NaN with 'N/A'.","metadata":{}},{"cell_type":"code","source":"cust['club_member_status'] = cust['club_member_status'].fillna('N/A')\nsns.set_style(\"darkgrid\", {\"axes.facecolor\": \".9\"})\nplot_bar(cust, 'club_member_status', pct=True, label='customers')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:44.355895Z","iopub.execute_input":"2022-03-12T17:25:44.356173Z","iopub.status.idle":"2022-03-12T17:25:45.086358Z","shell.execute_reply.started":"2022-03-12T17:25:44.356124Z","shell.execute_reply":"2022-03-12T17:25:45.085435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most of customers have an ```active``` membership status, others are with the ```pre-create``` status. Interestingly, there is nobody with ```left club``` status.","metadata":{}},{"cell_type":"code","source":"cust['fashion_news_frequency'] = cust['fashion_news_frequency'].fillna('N/A')\nplot_bar(cust, 'fashion_news_frequency', pct=True, label='customers')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:45.087820Z","iopub.execute_input":"2022-03-12T17:25:45.088118Z","iopub.status.idle":"2022-03-12T17:25:45.835154Z","shell.execute_reply.started":"2022-03-12T17:25:45.088087Z","shell.execute_reply":"2022-03-12T17:25:45.833991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We see here that there are two statuses that can be merged: *NONE* and *None* to improve data quality and to reduce one dimension. Most of customers do not receive any communication from H&M.\n\nLet's see now what is the age distribution.","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(10,5))\nax = sns.histplot(data=cust, x='age', bins=cust['age'].nunique(), color='orange', stat=\"percent\")\nax.set_xlabel('Distribution of the customers age')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nmedian = cust['age'].median()\nax.axvline(x=median, color=\"green\", ls=\"--\")\nax.text(median, 3.5, 'median: {}'.format(round(median,1)), rotation='vertical', ha='right')\nax.text(12, 5.5, 'Distribution of customers age', color='black', fontsize=10, ha='left', va='bottom', weight='bold', style='italic')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:32:50.441717Z","iopub.execute_input":"2022-03-12T17:32:50.443101Z","iopub.status.idle":"2022-03-12T17:32:51.158809Z","shell.execute_reply.started":"2022-03-12T17:32:50.443055Z","shell.execute_reply":"2022-03-12T17:32:51.157928Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The distribution shows that there are two main age-groups of customers: around 20-30 years old and 45-55 years old. Let's check how old is the oldest customer.","metadata":{}},{"cell_type":"code","source":"print('The olders customer is {} years old.'.format(cust['age'].max()))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:46.591360Z","iopub.execute_input":"2022-03-12T17:25:46.591709Z","iopub.status.idle":"2022-03-12T17:25:46.599745Z","shell.execute_reply.started":"2022-03-12T17:25:46.591672Z","shell.execute_reply":"2022-03-12T17:25:46.598710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(10,5))\nax = sns.histplot(data=cust, x='age', bins=cust['age'].nunique(), hue='Active', stat=\"percent\")\nax.set_xlabel('Distribution of the customers age')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:33:00.078112Z","iopub.execute_input":"2022-03-12T17:33:00.078901Z","iopub.status.idle":"2022-03-12T17:33:01.403872Z","shell.execute_reply.started":"2022-03-12T17:33:00.078857Z","shell.execute_reply":"2022-03-12T17:33:01.402985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's check what is the share of the active customers per age.","metadata":{}},{"cell_type":"code","source":"active_age_ratio = cust.groupby('age')['Active'].value_counts(normalize=True).mul(100)\nactive_age_ratio = active_age_ratio.rename('Active_ratio', inplace=True).reset_index()\nactive_age_ratio = active_age_ratio[active_age_ratio['Active']==1]\nactive_age_ratio['Active'] = active_age_ratio['Active'].astype(int)\nactive_age_ratio['age'] = active_age_ratio['age'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:25:47.956125Z","iopub.execute_input":"2022-03-12T17:25:47.957032Z","iopub.status.idle":"2022-03-12T17:25:48.245508Z","shell.execute_reply.started":"2022-03-12T17:25:47.956946Z","shell.execute_reply":"2022-03-12T17:25:48.244491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16,8))\nsns.barplot(x='age', y='Active_ratio', data=active_age_ratio)\nfor label in ax.xaxis.get_ticklabels()[::2]:\n    label.set_visible(False)\n    \nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\n    \nax.set_title(\"Share of active users per age\", color='black', fontsize=12, weight='bold')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:33:10.711361Z","iopub.execute_input":"2022-03-12T17:33:10.711674Z","iopub.status.idle":"2022-03-12T17:33:11.656362Z","shell.execute_reply.started":"2022-03-12T17:33:10.711636Z","shell.execute_reply":"2022-03-12T17:33:11.655346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Having these data it may be worth considering during data engineering part to perform a customer segmentation. It can be performed in many ways - some techinques I describe in my other [Kaggle notebook](https://www.kaggle.com/datark1/customers-clustering-k-means-dbscan-and-ap).","metadata":{}},{"cell_type":"markdown","source":"# 3. Transactions\n\nThis is the biggest database containing all transactions every day.","metadata":{}},{"cell_type":"code","source":"mpl.rcParams.update(mpl.rcParamsDefault)\n\ntrans_dtypes = trans.dtypes.value_counts()\ndata = trans\n\nfig = plt.figure(figsize=(5,2),facecolor='white')\n\nax0 = fig.add_subplot(1,1,1)\nax0.text(1.0, 1, \"Key figures\",color='black',fontsize=28, fontweight='bold', fontfamily='monospace',ha='center')\n\nax0.text(0, 0.4, \"{:,d}\".format(data.shape[0]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(0, 0.001, \"# of rows \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(0.6, 0.4, \"{}\".format(data.shape[1]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(0.6, 0.001, \"# of features \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(1.2, 0.4, \"{}\".format(trans_dtypes[0]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(1.2, 0.001, \"# of text columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.text(1.9, 0.4,\"{}\".format(trans_dtypes[1]), color='gold', fontsize=24, fontweight='bold', fontfamily='monospace', ha='center')\nax0.text(1.9, 0.001,\"# of numeric columns \\nin the dataset\",color='dimgrey',fontsize=15, fontweight='light', fontfamily='monospace',ha='center')\n\nax0.set_yticklabels('')\nax0.tick_params(axis='y',length=0)\nax0.tick_params(axis='x',length=0)\nax0.set_xticklabels('')\n\nfor direction in ['top','right','left','bottom']:\n    ax0.spines[direction].set_visible(False)\n\nfig.patch.set_linewidth(5)\nfig.patch.set_edgecolor('#8c8c8c')\nfig.patch.set_facecolor('#f6f6f6')\nax0.set_facecolor('#f6f6f6')\n    \nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:49.398390Z","iopub.execute_input":"2022-03-12T17:25:49.398807Z","iopub.status.idle":"2022-03-12T17:25:49.628141Z","shell.execute_reply.started":"2022-03-12T17:25:49.398775Z","shell.execute_reply":"2022-03-12T17:25:49.627275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Columns description:\n* ```t_dat``` - date of a transaction in format YYYY-MM-DD but provided as a string\n* ```customer_id``` - identifier of the customer which can be mapped to the ```customer_id```  column in the ```customers``` table\n* ```article_id``` - identifier of the product which can be mapped to the ```article_id```  column in the ```articles``` table\n* ```price``` - price paid\n* ```sales_channel_id``` - sales channel, 2 unique values","metadata":{}},{"cell_type":"code","source":"trans.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:25:49.629394Z","iopub.execute_input":"2022-03-12T17:25:49.629637Z","iopub.status.idle":"2022-03-12T17:25:49.644405Z","shell.execute_reply.started":"2022-03-12T17:25:49.629610Z","shell.execute_reply":"2022-03-12T17:25:49.643425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trans.isna().sum()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:25:49.646367Z","iopub.execute_input":"2022-03-12T17:25:49.646677Z","iopub.status.idle":"2022-03-12T17:25:58.230174Z","shell.execute_reply.started":"2022-03-12T17:25:49.646630Z","shell.execute_reply":"2022-03-12T17:25:58.229274Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"No missing data!\n\nLet's investigate the price column.","metadata":{}},{"cell_type":"code","source":"sns.set_style(\"darkgrid\", {\"axes.facecolor\": \".9\"})\nfig, ax = plt.subplots(figsize=(10,5), facecolor='#f6f5f5')\nax = sns.histplot(data=trans, x='price', bins=50, stat=\"percent\")\nax.set_xlabel('Distribution of the price')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:33:18.014154Z","iopub.execute_input":"2022-03-12T17:33:18.014439Z","iopub.status.idle":"2022-03-12T17:33:31.039980Z","shell.execute_reply.started":"2022-03-12T17:33:18.014410Z","shell.execute_reply":"2022-03-12T17:33:31.039040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It's clear from the above graph that we have a lot o outliers. Let's look at the price after cutting the values above 0.1.","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(10,5), facecolor='#f6f5f5')\ndata = trans[trans['price']<0.1]\nax = sns.histplot(data=data, x='price', bins=20, stat=\"percent\")\nax.set_xlabel('Distribution of the price')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:33:31.041897Z","iopub.execute_input":"2022-03-12T17:33:31.042475Z","iopub.status.idle":"2022-03-12T17:33:43.122321Z","shell.execute_reply.started":"2022-03-12T17:33:31.042432Z","shell.execute_reply":"2022-03-12T17:33:43.121224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now let's see the data distribution over time. First what dates range is provided. It will be usefull to change the datatype of ","metadata":{}},{"cell_type":"code","source":"trans['t_dat'] = pd.to_datetime(trans['t_dat'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:23.664566Z","iopub.execute_input":"2022-03-12T17:26:23.664812Z","iopub.status.idle":"2022-03-12T17:26:30.574945Z","shell.execute_reply.started":"2022-03-12T17:26:23.664783Z","shell.execute_reply":"2022-03-12T17:26:30.574030Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"begin = trans['t_dat'].min()\nend = trans['t_dat'].max()\nprint('Date range is from {} to {}.'.format(begin.date(), end.date()))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:30.576405Z","iopub.execute_input":"2022-03-12T17:26:30.576676Z","iopub.status.idle":"2022-03-12T17:26:30.788070Z","shell.execute_reply.started":"2022-03-12T17:26:30.576643Z","shell.execute_reply":"2022-03-12T17:26:30.786945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We have full 2 years of data. Let's plot now number of transactions per day over the full period of time.","metadata":{}},{"cell_type":"code","source":"t_per_day = trans.groupby('t_dat',as_index=False).count()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:30.789498Z","iopub.execute_input":"2022-03-12T17:26:30.789719Z","iopub.status.idle":"2022-03-12T17:26:35.941168Z","shell.execute_reply.started":"2022-03-12T17:26:30.789691Z","shell.execute_reply":"2022-03-12T17:26:35.940261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16,8))\n\nsns.lineplot(data=t_per_day, x='t_dat',y='customer_id')\n\nax.set_xlabel('date')\nax.set_ylabel('number of transactions')\n\nax.axvline(x=dt.datetime(2019,1,1), c='green')\nax.axvline(x=dt.datetime(2020,1,1), c='green')\n\nmax_t = t_per_day['customer_id'].max()\nmax_t_date = t_per_day[t_per_day['customer_id']==max_t]['t_dat']\nax.scatter(max_t_date, max_t, c='red')\nax.text(max_t_date+pd.DateOffset(days=5), max_t-4000, '{}\\n{:,d}'.format(max_t_date.iloc[0].date(), max_t))\n\nmin_t = t_per_day['customer_id'].min()\nmin_t_date = t_per_day[t_per_day['customer_id']==min_t]['t_dat']\nax.scatter(min_t_date, min_t, c='red')\nax.text(min_t_date+pd.DateOffset(days=5), min_t-4000, '{}\\n{:,d}'.format(min_t_date.iloc[0].date(), min_t))\nax.set_xlim(trans['t_dat'].min(),trans['t_dat'].max())\n\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\n\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:35.942825Z","iopub.execute_input":"2022-03-12T17:26:35.943152Z","iopub.status.idle":"2022-03-12T17:26:36.695158Z","shell.execute_reply.started":"2022-03-12T17:26:35.943108Z","shell.execute_reply":"2022-03-12T17:26:36.694263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From the graph above we see that there are distinct variations and spikes in the number of transactions per day. It would be clearer to visualise this by using box plots with monthly aggregations.","metadata":{}},{"cell_type":"code","source":"trans_gr_month = trans.groupby('t_dat').size().rename(\"no_transactions\")\ntrans_gr_month = trans_gr_month.reset_index()\ntrans_gr_month['month_year'] = trans_gr_month['t_dat'].dt.to_period('M')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:36.696664Z","iopub.execute_input":"2022-03-12T17:26:36.696876Z","iopub.status.idle":"2022-03-12T17:26:37.391641Z","shell.execute_reply.started":"2022-03-12T17:26:36.696849Z","shell.execute_reply":"2022-03-12T17:26:37.390613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16,8))\nax = sns.boxplot(x=\"month_year\", y='no_transactions', data=trans_gr_month)\nplt.xticks(rotation=90)\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.set_xlabel('Month-Year')\nax.set_ylabel('Number of transactions')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:37.393313Z","iopub.execute_input":"2022-03-12T17:26:37.393625Z","iopub.status.idle":"2022-03-12T17:26:38.161940Z","shell.execute_reply.started":"2022-03-12T17:26:37.393585Z","shell.execute_reply":"2022-03-12T17:26:38.161141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The bar chart above show us that per day usuall number of transactions lays in range about between 25 000 and 80 000 transactions per day. We see also that sales spikes during summertime and drops during winter.\n\nNow, let's see how many transactions, on average, customers do.","metadata":{}},{"cell_type":"code","source":"t_by_customer = trans.groupby('customer_id', as_index=False).size()\n\nfig, ax = plt.subplots(figsize=(10,5))\nax = sns.histplot(data=t_by_customer, x='size', bins=50, stat=\"percent\")\nax.set_xlabel('Distribution of total transactions per customer')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:33:43.124066Z","iopub.execute_input":"2022-03-12T17:33:43.124336Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Clearly there's a lot of outliers. Let's look at the distribution after cutting everything above 50 trasactions per customer.","metadata":{}},{"cell_type":"code","source":"t_by_customer_50tr = t_by_customer[t_by_customer['size'] < 50]\n\nfig, ax = plt.subplots(figsize=(10,5))\nax = sns.histplot(data=t_by_customer_50tr, x='size', bins=50, stat=\"percent\")\nax.set_xlabel('Distribution of total transactions per customer')\nfor loc in ['bottom', 'left']:\n    ax.spines[loc].set_visible(True)\n    ax.spines[loc].set_linewidth(2)\n    ax.spines[loc].set_color('black')\nax.yaxis.set_major_formatter(mtick.PercentFormatter())\nplt.show()","metadata":{"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The graph above shows us that most of customers, on average, bought only few items during these 2 years.\n\nLet's see now the popularity of sale channels.","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(5,5))\nexplode = (0, 0.1)\ncolors = sns.color_palette('Paired')\nax.pie(trans['sales_channel_id'].value_counts(), explode=explode, labels=['1','2'],\n       autopct='%1.1f%%',shadow=True, startangle=90, colors=colors)\nax.axis('equal')\nax.set_title('Sale channel')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:53.487442Z","iopub.execute_input":"2022-03-12T17:26:53.487781Z","iopub.status.idle":"2022-03-12T17:26:53.798683Z","shell.execute_reply.started":"2022-03-12T17:26:53.487749Z","shell.execute_reply":"2022-03-12T17:26:53.797438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4. Combined databases EDA","metadata":{}},{"cell_type":"markdown","source":"All 3 databases can be joined together. However, if you look closely it's not possible to connect directly databases ```art``` (with ```article_id``` as the primary key) and  ```cust``` (with ```customer_id``` as the primary key). They can be joined with a bridge table ```trans``` which contains both keys ```article_id``` and ```customer_id```.  \n\nWe can join them separately or all at once - it depends what your approach will be. Note that joining all tables will create one mega-table with a significant size which can slow-down your calculations or event it may not fit into memory allocated to you.\n\nBelow I'll join pre-filtered ```trans``` with ```art``` to visualise amount of transactions per article top-level group each month. I'll merge dataframes using ```pd.merge()``` with SQL-like logic.","metadata":{}},{"cell_type":"code","source":"#trans['month'] = trans['t_dat'].dt.month\n#trans['year'] = trans['t_dat'].dt.year\ntrans['year_month'] = trans['t_dat'].dt.to_period('M')\ntrans.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-12T17:26:53.800615Z","iopub.execute_input":"2022-03-12T17:26:53.801445Z","iopub.status.idle":"2022-03-12T17:26:57.108055Z","shell.execute_reply.started":"2022-03-12T17:26:53.801368Z","shell.execute_reply":"2022-03-12T17:26:57.107257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trans_grouped = trans.groupby(['year_month', 'article_id']).size().rename('total_per_article').to_frame()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:26:57.109226Z","iopub.execute_input":"2022-03-12T17:26:57.109432Z","iopub.status.idle":"2022-03-12T17:27:00.029228Z","shell.execute_reply.started":"2022-03-12T17:26:57.109406Z","shell.execute_reply":"2022-03-12T17:27:00.028446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trans_grouped.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:27:00.030571Z","iopub.execute_input":"2022-03-12T17:27:00.032158Z","iopub.status.idle":"2022-03-12T17:27:00.045337Z","shell.execute_reply.started":"2022-03-12T17:27:00.032102Z","shell.execute_reply":"2022-03-12T17:27:00.044498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trans_grouped.reset_index(inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:27:00.046694Z","iopub.execute_input":"2022-03-12T17:27:00.047530Z","iopub.status.idle":"2022-03-12T17:27:00.074629Z","shell.execute_reply.started":"2022-03-12T17:27:00.047488Z","shell.execute_reply":"2022-03-12T17:27:00.073707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"art_trans = pd.merge(art[['article_id', 'index_name']], trans_grouped, on='article_id')","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:27:00.076090Z","iopub.execute_input":"2022-03-12T17:27:00.076580Z","iopub.status.idle":"2022-03-12T17:27:00.208806Z","shell.execute_reply.started":"2022-03-12T17:27:00.076544Z","shell.execute_reply":"2022-03-12T17:27:00.207851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"art_trans = art_trans.groupby(['year_month','index_name'])['total_per_article'].sum().to_frame()\nart_trans.head()","metadata":{"execution":{"iopub.status.busy":"2022-03-12T17:27:00.210032Z","iopub.execute_input":"2022-03-12T17:27:00.210283Z","iopub.status.idle":"2022-03-12T17:27:00.322533Z","shell.execute_reply.started":"2022-03-12T17:27:00.210253Z","shell.execute_reply":"2022-03-12T17:27:00.321601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# UNDER CONSTRUCTION - TO BE CONTINUED","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}