{"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":"# Relationship of Article columns\n\nOne of the datasets provided in the [H&M Personalized Fashion Recommendations competition](https://www.kaggle.com/c/h-and-m-personalized-fashion-recommendations) consists of article information in tabular form. \n\nThe `articles` dataset combines information of the products, such as color or the product name, combined with general information, like department number. The data is denormalized (flattened) so it can easily be integrated into ML models. However, due to the denormalization the hierarchies of the underlying data model are not easily recognizable. It's also hard to spot which columns are redundant. \nFor instance we will see that there is no 1:1 relationship between `product_type_no` and `product_type_name`.\n\nThe information of the relationship and hierarchies can later on be used to create enbeddings for articles or models with a certain focus.","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport seaborn as sns\nfrom matplotlib import pyplot as plt\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\nimport graphviz\n\nimport os\n#for dirname, _, filenames in os.walk('/kaggle/input'):\n#    for filename in filenames:\n#        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-02-22T07:00:51.246795Z","iopub.execute_input":"2022-02-22T07:00:51.247120Z","iopub.status.idle":"2022-02-22T07:00:52.306647Z","shell.execute_reply.started":"2022-02-22T07:00:51.247036Z","shell.execute_reply":"2022-02-22T07:00:52.305739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Load articles:","metadata":{}},{"cell_type":"code","source":"articles = pd.read_csv('../input/h-and-m-personalized-fashion-recommendations/articles.csv')","metadata":{"execution":{"iopub.status.busy":"2022-02-22T07:00:52.308627Z","iopub.execute_input":"2022-02-22T07:00:52.311475Z","iopub.status.idle":"2022-02-22T07:00:53.516951Z","shell.execute_reply.started":"2022-02-22T07:00:52.311421Z","shell.execute_reply":"2022-02-22T07:00:53.516087Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check whether there is a *1:1*, *1:n* or *m:n* relationship between the columns. The hierarchical composition is done by comparing the distinct number of values with the distinct number of values of pairs.","metadata":{}},{"cell_type":"code","source":"a_unq = articles.nunique()\nart_cols = articles.columns","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-02-22T07:02:45.275458Z","iopub.execute_input":"2022-02-22T07:02:45.275811Z","iopub.status.idle":"2022-02-22T07:02:45.456148Z","shell.execute_reply.started":"2022-02-22T07:02:45.275781Z","shell.execute_reply":"2022-02-22T07:02:45.454992Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# decompose hierarchie\ndef column_dependencies(articles, verbose = False):\n    a_unq = articles.nunique()\n    art_cols = articles.columns\n    \n    mx = np.zeros((len(art_cols),len(art_cols))) \n    \n    if verbose:\n        print('# List relationships. All others are n:m')\n            \n    for i1 in range(len(art_cols)):\n        for i2 in range(i1+1,len(art_cols)):\n            col1 = articles.columns[i1]\n            col2 = articles.columns[i2]\n            \n            if a_unq[col1] == a_unq[col2]:\n                mx[i1,i2]=2\n                rel = '1:1'\n            else:\n                pair_nunique = articles.loc[:,[col1,col2]].drop_duplicates().shape[0]\n                if a_unq[col1] == pair_nunique:\n                    rel = 'n:1'\n                    mx[i1,i2]=1\n                elif a_unq[col2] == pair_nunique:\n                    rel = '1:n'\n                    mx[i2,i1]=1\n                        \n                else: \n                    rel = 'm:n'\n            \n            if verbose:\n                if (rel!='m:n') & (col1!='article_id'):\n                    print(col1,rel,col2)\n    return mx\n    ","metadata":{"execution":{"iopub.status.busy":"2022-02-22T08:05:02.928924Z","iopub.execute_input":"2022-02-22T08:05:02.929905Z","iopub.status.idle":"2022-02-22T08:05:02.940357Z","shell.execute_reply.started":"2022-02-22T08:05:02.929864Z","shell.execute_reply":"2022-02-22T08:05:02.939444Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"col_deps = column_dependencies(articles, verbose = False)","metadata":{"execution":{"iopub.status.busy":"2022-02-22T07:02:47.180212Z","iopub.execute_input":"2022-02-22T07:02:47.180493Z","iopub.status.idle":"2022-02-22T07:02:53.118900Z","shell.execute_reply.started":"2022-02-22T07:02:47.180463Z","shell.execute_reply":"2022-02-22T07:02:53.117613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's plot the relationships. \n- Red marks a 1:1 relationship (`index_name` : `index_code`).\n- Black marks 1:n relationship. It can be read like `article_id` is a child of `department_no` and a grant-child of `section_no`.\n- Blue marks n:m relationships, hence no technical hierarchie.","metadata":{}},{"cell_type":"code","source":"mask = np.ones_like(col_deps)\nmask[np.triu_indices_from(mask,1)] = 0\n\nsns.set(rc={'figure.figsize':(12,10)})\nsns.color_palette(\"tab10\")\n\n#with sns.axes_style(\"white\"):\nax = sns.heatmap(col_deps, \n                 xticklabels = articles.columns, \n                 yticklabels = articles.columns, \n                 cmap= sns.color_palette(\"icefire\",3),\n                 linewidths = 1,\n                 mask = mask\n                 #cbar=False\n                )\nax.set(xlabel='(grand-)parent', ylabel='(grand-)child')\ncolorbar = ax.collections[0].colorbar\ncolorbar.set_ticks([1/3, 1, 5/3])\ncolorbar.set_ticklabels(['n:m', ' n:1 ', '1:1'])\nax.set_title('Relationship between columns')\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-02-22T07:00:59.499711Z","iopub.execute_input":"2022-02-22T07:00:59.499944Z","iopub.status.idle":"2022-02-22T07:01:00.412449Z","shell.execute_reply.started":"2022-02-22T07:00:59.499915Z","shell.execute_reply":"2022-02-22T07:01:00.411281Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If we now travel the map from bottom right to upper left and we look to the next black box right above, we see the next direct child.","metadata":{}},{"cell_type":"code","source":"children = (np.sign(col_deps)*(np.arange(1,26).reshape(-1,1))).argmax(axis=0)","metadata":{"execution":{"iopub.status.busy":"2022-02-22T07:01:00.413931Z","iopub.execute_input":"2022-02-22T07:01:00.414878Z","iopub.status.idle":"2022-02-22T07:01:00.420470Z","shell.execute_reply.started":"2022-02-22T07:01:00.414831Z","shell.execute_reply":"2022-02-22T07:01:00.419512Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So now we can draw a graph of the hierarchie along with the number of unique values of each column.","metadata":{}},{"cell_type":"code","source":"g = graphviz.Graph('col_dep')#, graph_attr={'rankdir':'LR', 'size':'15,10'})\ng.attr('node', shape='box')\n\nfor col, child in zip(articles.columns, children):\n    g.node(col, label = f'{col} ({a_unq[col]})')\n    if col != articles.columns[child]:\n        g.edge(articles.columns[child],col)\n\ng","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-02-22T07:07:55.616144Z","iopub.execute_input":"2022-02-22T07:07:55.616464Z","iopub.status.idle":"2022-02-22T07:07:55.660435Z","shell.execute_reply.started":"2022-02-22T07:07:55.616434Z","shell.execute_reply":"2022-02-22T07:07:55.659517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The 1:1 relationships are between the `*_no` and `*_name` columns. Expect of the `departmant_name` which includes many `department_no`. That's not a surprice. It's probably hard to come up with 299 meaning full names for departments. \nThe hierarchies of `*_no/*_name` pairs can also be interpreted as an entities. E.g. entity `department` with primary key `department_no` and attribute `department_name`.\n\nSo there are two areas that don't match the hierarchical pattern as expected. \n\nThe first one is `product_type_no`, `product_type_name` and `product_group_name`. Let's inspect what is causing the ambiguous hierarchie. Which `product_type_name` occure in more than one group?","metadata":{"execution":{"iopub.status.busy":"2022-02-22T07:29:42.705612Z","iopub.execute_input":"2022-02-22T07:29:42.706406Z","iopub.status.idle":"2022-02-22T07:29:42.712648Z","shell.execute_reply.started":"2022-02-22T07:29:42.706363Z","shell.execute_reply":"2022-02-22T07:29:42.711472Z"}}},{"cell_type":"code","source":"articles[['product_type_name', 'product_group_name']].drop_duplicates().groupby('product_type_name').count().sort_values(by='product_group_name', ascending = False).head(n=3)","metadata":{"execution":{"iopub.status.busy":"2022-02-22T07:45:40.955408Z","iopub.execute_input":"2022-02-22T07:45:40.955859Z","iopub.status.idle":"2022-02-22T07:45:40.999544Z","shell.execute_reply.started":"2022-02-22T07:45:40.955828Z","shell.execute_reply":"2022-02-22T07:45:40.998484Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"articles[articles.product_type_name=='Umbrella'][['product_type_name', 'product_type_no', 'product_group_name']].drop_duplicates()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-02-22T07:48:46.841239Z","iopub.execute_input":"2022-02-22T07:48:46.841608Z","iopub.status.idle":"2022-02-22T07:48:46.876890Z","shell.execute_reply.started":"2022-02-22T07:48:46.841573Z","shell.execute_reply":"2022-02-22T07:48:46.875836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Ok, *Umbrella* is ambiguous it is part of the group *Items* and *Accessories*.\n\nThe other area that is ambiguous from a hierarchical point of view includes `product_code`, `prod_name` and `detail_desc`. The number of unique values here is huge. So it's not surprising that `product_no` and `prod_name` don't form a 1:1 relationship. So we leave that part as it is and assume `prod_name` is an attribute of `product_no`.\n\nFor a better overview, we redraw the graph without the name columns:","metadata":{}},{"cell_type":"code","source":"no_name_cols = [col for col in articles.columns if col[-4:].strip()!='name']\ncol_deps_no_name = column_dependencies(articles[no_name_cols], verbose = False)\nchildren_no_name = (np.sign(col_deps_no_name)*(np.arange(1,len(no_name_cols)+1).reshape(-1,1))).argmax(axis=0)\n\ng_no_name = graphviz.Graph('col_dep_no_name')#, graph_attr={'rankdir':'LR', 'size':'15,10'})\ng_no_name.attr('node', shape='box')\n\nfor col, child in zip(no_name_cols, children_no_name):\n    g_no_name.node(col, label = f'{col} ({a_unq[col]})')\n    if col != no_name_cols[child]:\n        g_no_name.edge(no_name_cols[child],col)\n\ng_no_name","metadata":{"execution":{"iopub.status.busy":"2022-02-22T08:09:16.826563Z","iopub.execute_input":"2022-02-22T08:09:16.827319Z","iopub.status.idle":"2022-02-22T08:09:18.004469Z","shell.execute_reply.started":"2022-02-22T08:09:16.827265Z","shell.execute_reply":"2022-02-22T08:09:18.002821Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"... to be continued ...","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}