{"nbformat_minor": 1, "metadata": {"kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}, "language_info": {"file_extension": ".py", "mimetype": "text/x-python", "name": "python", "version": "3.6.3", "pygments_lexer": "ipython3", "codemirror_mode": {"name": "ipython", "version": 3}, "nbconvert_exporter": "python"}}, "cells": [{"metadata": {"_uuid": "c184aec2e8f7cb1d4d4c8263901b4fe906f8bff2", "_cell_guid": "588f022c-c594-440c-9cb0-681500b94b39", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#basic\n", "import numpy as np \n", "import pandas as pd\n", "#viz\n", "import seaborn as sns\n", "import matplotlib.pyplot as plt\n", "color = sns.color_palette()\n", "\n", "#others\n", "import subprocess\n", "from subprocess import check_output\n", "import gc\n", "\n", "%matplotlib inline\n"], "execution_count": null}, {"metadata": {"_kg_hide-output": false, "scrolled": false, "_uuid": "d5273b1aef3f7f4bf28d68bf6e759e0dd274d21f", "_cell_guid": "421736ba-228a-4941-a081-6de4bd9d6f0e", "collapsed": true, "_kg_hide-input": false}, "outputs": [], "cell_type": "code", "source": ["\n", "files=check_output([\"ls\", \"../input\"]).decode(\"utf8\")\n", "#Check the number of row of each file\n", "for file in files.split(\"\\n\"):\n", "    path='../input/'+file\n", "    popenobj=subprocess.Popen(['wc', '-l', path], stdout=subprocess.PIPE, \n", "                                              stderr=subprocess.PIPE)\n", "    result,error= popenobj.communicate()\n", "    print(\"The file :\",file,\"has :\",result.strip().split()[0],\"rows\")"], "execution_count": null}, {"metadata": {"_uuid": "286eae513cd4419904f3be8ad5b3b925c99ee169", "_cell_guid": "946fe5c4-f099-4048-8825-e9c56057a9a9", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#train = pd.read_csv(\"../input/train.csv\")\n", "test = pd.read_csv(\"../input/test.csv\")\n", "stores = pd.read_csv(\"../input/stores.csv\")\n", "items = pd.read_csv(\"../input/items.csv\")\n", "trans = pd.read_csv(\"../input/transactions.csv\")\n", "oil = pd.read_csv(\"../input/oil.csv\")\n", "holiday = pd.read_csv(\"../input/holidays_events.csv\")\n", "print(\"done\")\n"], "execution_count": null}, {"metadata": {"_uuid": "453c538b2226bcc60756d9e35c8fa38b38acd77d", "_cell_guid": "56eee2d6-f849-4d68-92a3-11c0d09f1523"}, "cell_type": "markdown", "source": ["## Memory optimization\n", "Since train.csv has 125 mil records, it is best to consider performing some data engineering before starting any analysis.\n", "\n", "Note: This kernal was inspired by Jeru666's kernel for the KKBox churn challenge. \n", "If you like this kernel, do checkout his work using the link here ->\n", "https://www.kaggle.com/jeru666/memory-reduction-and-data-insights\n", "\n", "Also a similar kernal that I've written for KKBox churn challenge.\n", "https://www.kaggle.com/jagangupta/processing-huge-datasets-user-log\n"]}, {"metadata": {"_uuid": "1d2dc08e7c72d4e40d6871b36fb7a0e338ed37aa", "_cell_guid": "3c7b1d52-f73c-4001-9f1e-6c5e3bad2599", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#check memory use for the two biggest files - train and test\n", "#mem_train = train.memory_usage(index=True).sum()\n", "mem_test=test.memory_usage(index=True).sum()\n", "#print(\"train dataset uses \",mem_train/ 1024**2,\" MB\")\n", "print(\"test dataset uses \",mem_test/ 1024**2,\" MB\")\n", "# checking contents in train\n", "test.head()"], "execution_count": null}, {"metadata": {"_uuid": "6be3b34308c9450e4419ffca537b8bc5cb02c8db", "_cell_guid": "dfc161a7-cb51-4517-99b3-655b06ab7592", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# optimize test.csv\n", "# First check the contents of train.csv\n", "print(test.max())\n", "print(test.min())\n", "#check datatypes\n", "print(test.dtypes)"], "execution_count": null}, {"metadata": {"_uuid": "6b9bdad721c8d2b0df0aadbc93973d401511754e", "_cell_guid": "0ed7ec7a-4a47-4119-9f4d-78b3867f9b0f"}, "cell_type": "markdown", "source": ["TLDR: following are the steps I've used to reduce memory consumption \n", "- Check the range of values stored in the column\n", "- Check the suitable datatype from the following link\n", "    https://docs.scipy.org/doc/numpy-1.13.0/user/basics.types.html\n", "- Change datatype\n", "- split date col into three columns\n", "    - There are two reasons to do this\n", "        - In pandas any operation on column of type \"datetime\" is not vectorized.Hence any operations on it will take more time\n", "        - Splitting it into three columns will provide better memory utilization. Eg: in the test dataset date col uses approx. 25 mb while storenbr(uint8) uses approx. 3 mb\n", "- Impute on promo col \n", "- join everything\n", "\n"]}, {"metadata": {"_uuid": "caada3db98df06bcd21d00e2716ae1f3eda0f66a", "_cell_guid": "8fe159a1-ce6c-4644-8add-09b3bf39fe7a", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#There are only 54 stores\n", "test['store_nbr'] = test['store_nbr'].astype(np.uint8)\n", "# The ID column is a continuous number from 1 to 128867502 in train and 128867503 to 125497040 in test\n", "test['id'] = test['id'].astype(np.uint32)\n", "# item number is unsigned \n", "test['item_nbr'] = test['item_nbr'].astype(np.uint32)\n", "#Converting the date column to date format\n", "test['date']=pd.to_datetime(test['date'],format=\"%Y-%m-%d\")\n", "#check memory\n", "print(test.memory_usage(index=True))\n", "new_mem_test=test.memory_usage(index=True).sum()\n", "print(\"test dataset uses \",new_mem_test/ 1024**2,\" MB after changes\")\n", "print(\"memory saved =\",(mem_test-new_mem_test)/ 1024**2,\" MB\")"], "execution_count": null}, {"metadata": {"_uuid": "347ded3c8e80918b5f0e08958c6a6c683752a6e3", "_cell_guid": "2f4c703e-056f-49d9-92aa-cd6d7fc05f51"}, "cell_type": "markdown", "source": ["## Around 50% save in memory utilization"]}, {"metadata": {"_uuid": "318e484031d076107d9fa28df1656ce60985902e", "_kg_hide-output": true, "_cell_guid": "5bfc7081-552a-4fe5-ac3b-35c872a210be", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["print(test.memory_usage())\n", "\n", "#check range of float 16\n", "min_value = np.finfo(np.float16).min\n", "max_value = np.finfo(np.float16).max\n", "print(\"range of float16 is\",min_value,max_value)"], "execution_count": null}, {"metadata": {"_uuid": "13a88d562a45c4a95e573189b109a2d478a4f980", "_cell_guid": "991dc07c-28a4-42cd-b906-1c8a570e0cfb"}, "cell_type": "markdown", "source": ["But the unit sales is not present in test.\n", "Based on the top EDA kernal till now by @\"head or tails\" the max and min of unit sales from a random sample is 20748.000 and -15372.000 respectively, and it has a \"double\" datatype meaning it some items can have unit sales in decimals.\n", "link:https://www.kaggle.com/headsortails/shopping-for-insights-favorita-eda\n", "\n", "Hence \"float 16\" would be a good choice.\n", "\n", "Any value which is out of that range is anyways an outlier which should be imputed before analysis.\n"]}, {"metadata": {"_uuid": "04fb1e7cae225d83f2d89dc7ca6ef7a3adcd0446", "_cell_guid": "3aff604c-dde9-4926-aef4-aeab422555b1"}, "cell_type": "markdown", "source": ["## Import train.csv with the correct datatypes\n", "We can specify the datatypes as a dictionary while importing a csv\n"]}, {"metadata": {"_uuid": "f58c9185159e8015b50084eaa6cea5c4b6a63812", "_kg_hide-output": true, "_cell_guid": "7e18e43b-d361-4d3e-a6e3-349503394024", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["# taking a peak\n", "#train = pd.read_csv(\"../input/train.csv\",nrows=10000)\n", "#print(train.dtypes)\n", "\n", "dtype_dict={\"id\":np.uint32,\n", "            \"store_nbr\":np.uint8,\n", "            \"item_nbr\":np.uint32,\n", "            \"unit_sales\":np.float16\n", "           }\n", "\n", "#test for 10000 rows\n", "train_part1 = pd.read_csv(\"../input/train.csv\",nrows=100,dtype=dtype_dict,usecols=[0,2,3,4])\n", "print(train_part1.describe())\n", "print(train_part1.dtypes)\n", "\n", "\n", "\n", "\n", "# read in the date col,on promo col\n", "#specify the column number which has the date in the parse_dates as a list\n", "train_part2=pd.read_csv(\"../input/train.csv\",nrows=10000,dtype=dtype_dict,usecols=[1,5],parse_dates=[0])\n", "train_part2['Year'] = pd.DatetimeIndex(train_part2['date']).year\n", "train_part2['Month'] = pd.DatetimeIndex(train_part2['date']).month\n", "train_part2['Day'] =pd.DatetimeIndex(train_part2['date']).day.astype(np.uint8)\n", "del(train_part2['date'])\n", "train_part2['Day']=train_part2['Day'].astype(np.uint8)\n", "train_part2['Month']=train_part2['Month'].astype(np.uint8)\n", "train_part2['Year']=train_part2['Year'].astype(np.uint16)\n", "\n", "#impute the missing values to be -1\n", "train_part2[\"onpromotion\"].fillna(0, inplace=True)\n", "train_part2[\"onpromotion\"]=train_part2[\"onpromotion\"].astype(np.int8)\n", "print(train_part2.head())\n", "print(train_part2.dtypes)"], "execution_count": null}, {"metadata": {"_uuid": "3b88cefb6fe430f99a7c4ab8ecfcac11a60d9214", "_kg_hide-output": true, "_cell_guid": "5abc864b-4373-4d7b-a12a-9a3147d66c46", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# now scaling it to the entire dataset of train\n", "\n", "train_part2=pd.read_csv(\"../input/train.csv\",dtype=dtype_dict,usecols=[1,5],parse_dates=[0])\n", "train_part2['Year'] = pd.DatetimeIndex(train_part2['date']).year\n", "train_part2['Month'] = pd.DatetimeIndex(train_part2['date']).month\n", "train_part2['Day'] =pd.DatetimeIndex(train_part2['date']).day.astype(np.uint8)\n", "del(train_part2['date'])\n", "train_part2['Day']=train_part2['Day'].astype(np.uint8)\n", "train_part2['Month']=train_part2['Month'].astype(np.uint8)\n", "train_part2['Year']=train_part2['Year'].astype(np.uint16)\n", "\n", "#impute the missing values to be -1\n", "train_part2[\"onpromotion\"].fillna(0, inplace=True)\n", "train_part2[\"onpromotion\"]=train_part2[\"onpromotion\"].astype(np.int8)\n", "print(train_part2.head())\n", "print(train_part2.dtypes)\n"], "execution_count": null}, {"metadata": {"_uuid": "f749f3a20ecdfbb89c4df4e90550ca0ee824647d", "_kg_hide-output": true, "_cell_guid": "b8005bf5-c4e1-4fb8-8648-6d1cffd39500", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# scaling part 1 to the entire dataset\n", "dtype_dict={\"id\":np.uint32,\n", "            \"store_nbr\":np.uint8,\n", "            \"item_nbr\":np.uint32,\n", "            \"unit_sales\":np.float32\n", "           }\n", "\n", "train_part1 = pd.read_csv(\"../input/train.csv\",dtype=dtype_dict,usecols=[0,2,3,4])\n", "print(train_part1.dtypes)"], "execution_count": null}, {"metadata": {"_uuid": "bf187da5b855aff18c54bb409a1df63e1711c3e5", "_cell_guid": "e994e153-4cd4-4ca6-a7be-b61929ea6ded", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# joining part one and two\n", "# For people familiar with R , the equivalent of cbind in pandas is the following command\n", "train = pd.concat([train_part1.reset_index(drop=True), train_part2], axis=1)\n", "#drop temp files\n", "del(train_part1)\n", "del(train_part2)\n", "#Further Id is just an indicator column, hence not required for analysis\n", "id=train['id']\n", "del(train['id'])\n", "# check memory\n", "print(train.memory_usage())\n", "#The extracted train.csv file is approx 5 GB\n", "mem_train=5*1024**3\n", "new_mem_train=train.memory_usage().sum()\n", "print(\"Train dataset uses \",new_mem_train/ 1024**2,\" MB after changes\")\n", "print(\"memory saved is approx\",(mem_train-new_mem_train)/ 1024**2,\" MB\")"], "execution_count": null}, {"metadata": {"_uuid": "9f3c146ba604009b59d5381890d6fb11ff7d876b", "_cell_guid": "41074023-f1af-458e-b583-dd6f401cc810"}, "cell_type": "markdown", "source": ["# Finally, We have the entire train dataset loaded into memory.\n", "## ~1.6GB is a managable size for basic computations in the kaggle kernal\n", "\n", "Further if there is some heavy computation that need to be made,and it can be applied to the data as chunks, then we can split the data into blocks and aggregate and combine them.\n", "(ie) Use map reduce concepts to further optimize if required.\n", "\n", "# Now lets look into the dataset and perform some basic EDAs"]}, {"metadata": {"_uuid": "4f661e663380966010579b816dcafa260ff37f91", "_cell_guid": "f3294824-e92b-4c9c-99c3-480ee1064efd", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# summary stats\n", "train['unit_sales'].describe()\n", "#check\n", "train['unit_sales'].isnull().sum()"], "execution_count": null}, {"metadata": {"_uuid": "46c66a1cf10272d4b029bdee86d23373a1a2b184", "_cell_guid": "c791ac72-2ef9-4ab3-a99b-a56bfb1b1d28"}, "cell_type": "markdown", "source": ["Further to make EDA easier, rolling up the sales to different levels\n", "     - Day-Store level\n", "     - Day-Item level\n", "     - Store level\n", "     - Item level\n", "     - Day level\n", "     "]}, {"metadata": {"_uuid": "89a73ed46480068ed8828556defa9bdbddbc8c5a", "_cell_guid": "3f0b7914-8f2c-401c-9aa7-3493145ea5ce", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# Using pandas group by and aggregate\n", "#sale_day_store_level=train.groupby(['Year','Month','Day','store_nbr'])['unit_sales'].sum()\n", "#sale_day_item_level=train.groupby(['Year','Month','Day','item_nbr'])['unit_sales'].sum()\n", "\n", "#kernal got stuck when trying this piece of code, hence splitting into chunks(chunks of one year) and appending\n"], "execution_count": null}, {"metadata": {"_uuid": "0be017a10d621717ab59c34e0e228fd3e0430b38", "_cell_guid": "0f229e8a-4817-413d-816a-587d7b8346ba", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["train_2013=train.loc[train['Year']==2013]\n", "train_2014=train.loc[train['Year'] ==2014]\n", "train_2015=train.loc[train['Year'] ==2015]\n", "train_2016=train.loc[train['Year'] ==2016]\n", "train_2017=train.loc[train['Year'] ==2017]"], "execution_count": null}, {"metadata": {"_uuid": "c642504ac8f838c75e624b5b508b6b4355b8ba41", "_cell_guid": "0a782c97-1097-41c8-9699-521472cefdfc"}, "cell_type": "markdown", "source": ["## Short description of the metrics that I'm pulling here\n", "- Store-day level sale -- This variable indicates the sale of a particular store over time\n", "- Store-day level count -- This variable gives an indication of the variaty/spread of the items sold\n", "\n", "- Item-day level sale -- Sale of an item over time\n", "- Item-day level count -- This gives an indication of the popularity of the item across the supermarket chain. \n", "    - This variable can lead to information like region specific items,etc"]}, {"metadata": {"_uuid": "03ce9755f8772a1c4f560ebe6e083c2c8f151a93", "_cell_guid": "18e307ed-443e-4aa6-9b6a-2f6065087311", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["def aggregate_level1(df):\n", "    '''writing a function to get item and store level summary metrics for a specific year'''\n", "#day-store level\n", "    sale_day_store_level=df.groupby(['Year','Month','Day','store_nbr'],as_index=False)['unit_sales'].agg(['sum','count'])\n", "    #drop index and rename\n", "    sale_day_store_level=sale_day_store_level.reset_index().rename(columns={'sum':'store_sales','count':'item_variety'})\n", "#day-item level  \n", "    sale_day_item_level=df.groupby(['Year','Month','Day','item_nbr'],as_index=False)['unit_sales'].agg(['sum','count'])\n", "    #drop index and rename\n", "    sale_day_item_level=sale_day_item_level.reset_index().rename(columns={'sum':'item_sales','count':'store_spread'})\n", "#store item level   \n", "    sale_store_item_level=df.groupby(['Year','store_nbr','item_nbr'],as_index=False)['unit_sales'].agg(['sum','count'])\n", "    #drop index and rename\n", "    sale_store_item_level=sale_store_item_level.reset_index().rename(columns={'sum':'item_sales','count':'entries'})\n", "\n", "    return sale_day_store_level,sale_day_item_level,sale_store_item_level"], "execution_count": null}, {"metadata": {"_uuid": "a9f6383d8e8455dd7d528bb0044759d981b68624", "_cell_guid": "a4a0ab09-0dcc-48f3-b915-13d689557701", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#run for 2013\n", "sale_day_store_level_2013,sale_day_item_level_2013,sale_store_item_level_2013=aggregate_level1(train_2013)\n", "print(sale_day_store_level_2013.head())\n", "sale_day_item_level_2013.head()\n"], "execution_count": null}, {"metadata": {"_uuid": "3a7d644d006ff978f217752dc1353b9575546b70", "_cell_guid": "1e822da4-56c2-4af8-ae6e-bec65d853de6"}, "cell_type": "markdown", "source": ["### Now apply the function to other years and append together"]}, {"metadata": {"_uuid": "d09240b5bd455ebff63a1e218f36ed0f8a7d3851", "_cell_guid": "d1e7f5af-9e63-436b-b2cb-72e05942b870", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["import time\n", "start_time = time.time()\n", "#run for 2014\n", "sale_day_store_level_2014,sale_day_item_level_2014,sale_store_item_level_2014=aggregate_level1(train_2014)\n", "#run for 2015\n", "sale_day_store_level_2015,sale_day_item_level_2015,sale_store_item_level_2015=aggregate_level1(train_2015)\n", "#run for 2016\n", "sale_day_store_level_2016,sale_day_item_level_2016,sale_store_item_level_2016=aggregate_level1(train_2016)\n", "#run for 2017\n", "sale_day_store_level_2017,sale_day_item_level_2017,sale_store_item_level_2017=aggregate_level1(train_2017)\n", "\n", "end_time=time.time()\n", "time_taken=end_time-start_time\n", "print(\"This block took \",time_taken,\"seconds\")"], "execution_count": null}, {"metadata": {"_uuid": "06ba3e2e911fc7e32187316dc9be2d8e8efb602a", "_cell_guid": "1b58e6f2-710d-473b-b697-218742fe783b"}, "cell_type": "markdown", "source": ["## A wierd trivia here is that only store number 25 is open 1st Jan of every year :p"]}, {"metadata": {"_uuid": "b8fd11d5e3c714acd7a0dceb558aae444fbc40b7", "_cell_guid": "8285068c-698c-40fb-b00f-d7ba6d9af7b4", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# appending together\n", "#note: concat expects a list of dfs and not a list of strings\n", "sale_day_store_level=pd.concat([sale_day_store_level_2013,sale_day_store_level_2014,\n", "                                sale_day_store_level_2015,sale_day_store_level_2016,\n", "                                sale_day_store_level_2017])\n", "\n", "sale_day_item_level=pd.concat([sale_day_item_level_2013,sale_day_item_level_2014,\n", "                                sale_day_item_level_2015,sale_day_item_level_2016,\n", "                                sale_day_item_level_2017])\n", "sale_store_item_level=pd.concat([sale_store_item_level_2013,sale_store_item_level_2014,\n", "                                sale_store_item_level_2015,sale_store_item_level_2016,\n", "                                sale_store_item_level_2017])"], "execution_count": null}, {"metadata": {"_uuid": "202db7b48ca9a04690bfc289e496a37ff8198431", "_cell_guid": "510cf173-b94b-4d23-af09-35e30a390d7b", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["# freeup memory\n", "del(sale_day_store_level_2013)\n", "del(sale_day_store_level_2014)\n", "del(sale_day_store_level_2015)\n", "del(sale_day_store_level_2016)\n", "del(sale_day_store_level_2017)\n", "del(sale_day_item_level_2013)\n", "del(sale_day_item_level_2014)\n", "del(sale_day_item_level_2015)\n", "del(sale_day_item_level_2016)\n", "del(sale_day_item_level_2017)\n", "del(sale_store_item_level_2013)\n", "del(sale_store_item_level_2014)\n", "del(sale_store_item_level_2015)\n", "del(sale_store_item_level_2016)\n", "del(sale_store_item_level_2017)\n", "gc.collect()"], "execution_count": null}, {"metadata": {"_uuid": "8476a1b074df3163f70e932eeda04513340ba4ac", "_cell_guid": "9a24a15d-1a8e-453e-a21b-1387e09b7507"}, "cell_type": "markdown", "source": ["# Exporting the two datasets for others to use.\n", "\n", "Please leave a reference to this kernal in your script if you decide to use them :) "]}, {"metadata": {"_uuid": "2fa9c4d47f2a35e0ca72aadbe570f42756e250a5", "_cell_guid": "7deb837e-549e-48c6-bad4-db8ba6909891", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["sale_day_store_level.to_csv(\"sale_day_store_level.csv\")\n", "sale_day_item_level.to_csv(\"sale_day_item_level.csv\")\n", "sale_store_item_level.to_csv(\"sale_store_item_level.csv\")"], "execution_count": null}, {"metadata": {"_uuid": "75fa84f279ee7778c4e77a4d8185f3bf7dadd8e6", "_cell_guid": "ddfe19c9-ab1f-4702-aa56-d2ffeb94a8af"}, "cell_type": "markdown", "source": ["## Further aggregations \n", "\n", " - Store level aggregation\n", " - Item level aggregation\n", " - Day level aggregation\n", " "]}, {"metadata": {"_uuid": "b28acd5ae590b1108c453cd4eb193dc18134c00a", "_cell_guid": "baa37053-b35f-4405-aaf1-1dfe4df2c084", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#Creating store level metrics\n", "sale_store_level=sale_day_store_level.groupby(['store_nbr'],as_index=False)['store_sales','item_variety'].agg(['sum'])\n", "\n", "# Here the group by gives a multiindex , removing that\n", "sale_store_level.columns = sale_store_level.columns.droplevel(1)\n", "sale_store_level=sale_store_level.reset_index()\n", "sale_store_level.head()\n"], "execution_count": null}, {"metadata": {"_uuid": "1e7cf91a2d48c6fcf1c931db814595307b9025ae", "_cell_guid": "deaf35bc-ec7a-493a-a3f0-7de243a5de2b", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["#Creating item level metrics\n", "sale_item_level=sale_day_item_level.groupby(['item_nbr'],as_index=False)['item_sales'].agg(['sum'])\n", "\n", "sale_item_level=sale_item_level.reset_index()\n", "sale_item_level.head()"], "execution_count": null}, {"metadata": {"_uuid": "9e13f9c41ade1a6f86155c19c3b90352e224de57", "_cell_guid": "b5f04f02-77cc-4321-9660-cf553e01151e"}, "cell_type": "markdown", "source": ["# Section 2 : Plots \n", "1. Store\n", "2. Item\n"]}, {"metadata": {"_uuid": "dd8cde3c1309efe44e93f44f36cd4e1a668e7333", "_cell_guid": "145c6038-ba32-4a97-8dbb-d87100eed55d", "collapsed": true, "_kg_hide-input": false}, "outputs": [], "cell_type": "code", "source": ["# Sorting by sales\n", "temp=sale_store_level.sort_values('store_sales',ascending=False).reset_index(drop=True)\n", "temp=temp.set_index('store_nbr').head(10)\n", "\n", "plt.figure(figsize=(12,8))\n", "sns.barplot(temp.index,temp.store_sales, alpha=0.8, color=color[2],)\n", "plt.ylabel('Overall Sales', fontsize=12)\n", "plt.xlabel('Store Number', fontsize=12)\n", "plt.title('Top Stores by Overall sale', fontsize=15)\n", "# plt.xticks(rotation='vertical')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "d305cc32e98ed150c3ec6b82313cc6fdaa57b0d7", "_kg_hide-output": false, "_cell_guid": "67350b73-cc21-4d41-86e7-1464d25aea5a", "collapsed": true, "_kg_hide-input": false}, "outputs": [], "cell_type": "code", "source": ["# Sorting by sales\n", "temp1=sale_item_level.sort_values('sum',ascending=False).reset_index(drop=True)\n", "temp1=temp1.set_index('item_nbr').head(10)\n", "plt.figure(figsize=(12,8))\n", "x=temp1.index.values\n", "y=temp1['sum'].values\n", "sns.barplot(x,y, alpha=0.8, color=color[8])\n", "plt.ylabel('Overall Sales', fontsize=12)\n", "plt.xlabel('Store Number', fontsize=12)\n", "plt.title('Top Items by Overall sale', fontsize=15)\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "3551d7090bf9d1a8d5f8cc3efa1644c09614ba9b", "_cell_guid": "6c43809d-fcdc-44ac-b93d-63e1c357a00d", "collapsed": true}, "outputs": [], "cell_type": "code", "source": ["print(\"PLot I wanted to show :(\")\n", "print(\"top 10 items\")\n", "temp.iloc[:,0].plot.bar()\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "5b61b7f4a2e00f68afa961e7b2898be134a108fb", "_cell_guid": "8decd480-5e8f-48d9-ad7d-2c2aac646375"}, "cell_type": "markdown", "source": ["## There seems to be an issue with Seaborn automatically sorting the plots by the x variable. \n", "### If someone from the community could help me figure out the right way to do this it would be great \n", "\n", "Anyways,moving on..\n"]}, {"metadata": {"_uuid": "21a80b6e083fe9b241278d15a4bcbd9fafaddd7f", "_cell_guid": "e81c3bde-733a-4048-ad23-8d3ac7c56ce0", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["#Overall sales\n", "#YOY sales\n", "temp=sale_day_store_level.groupby('Year')['store_sales'].sum()\n", "plt.figure(figsize=(13,4))\n", "sns.pointplot(temp.index,temp.values, alpha=0.8, color=color[1],)\n", "plt.ylabel('Overall Sales', fontsize=12)\n", "plt.xlabel('Year', fontsize=12)\n", "plt.title('Sale YOY', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "# month over month sales\n", "temp=sale_day_store_level.groupby(['Year','Month'])['store_sales'].sum()\n", "plt.figure(figsize=(13,4))\n", "sns.pointplot(temp.index,temp.values, alpha=0.8, color=color[2],)\n", "plt.ylabel('Overall Sales', fontsize=12)\n", "plt.xlabel('Month', fontsize=12)\n", "plt.title('Monthly sales variation', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "\n", "\n", "# also checking the oil price change\n", "oil['date']=pd.to_datetime(oil['date'])\n", "oil['Year']=oil['date'].dt.year\n", "oil['Month']=oil['date'].dt.month \n", "\n", "# Oil price variation over month\n", "temp=oil.groupby(['Year','Month']).agg(['sum','count'])\n", "temp.columns = temp.columns.droplevel(0)\n", "temp['avg']=temp['sum']/temp['count']\n", "#plot\n", "plt.figure(figsize=(13,4))\n", "sns.pointplot(temp.index,temp.avg, alpha=0.8, color=color[4],)\n", "plt.ylabel('Oil price', fontsize=12)\n", "plt.xlabel('Month', fontsize=12)\n", "plt.title('Monthly variation in oil price', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "plt.show()\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "dd0d8493e4a27c5c092c054978e2829cc8d0f7e3", "_cell_guid": "c8cf7e5d-c21f-4edf-8319-fe0fa581e0a9"}, "cell_type": "markdown", "source": ["\n"]}, {"metadata": {"_uuid": "17c2164f91fcbfd21d864727a023bddce192af5e", "_cell_guid": "5e506421-1386-4871-b8c4-a4df92f451d0", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["# month over month sales\n", "temp=sale_day_store_level.groupby(['Year','Month']).aggregate({'store_sales':np.sum,'Year':np.min,'Month':np.min})\n", "temp=temp.reset_index(drop=True)\n", "sns.set(style=\"whitegrid\", color_codes=True)\n", "# temp\n", "plt.figure(figsize=(15,8))\n", "plt.plot(range(1,13),temp.iloc[0:12,0],label=\"2013\")\n", "plt.plot(range(1,13),temp.iloc[12:24,0],label=\"2014\")\n", "plt.plot(range(1,13),temp.iloc[24:36,0],label=\"2015\")\n", "plt.plot(range(1,13),temp.iloc[36:48,0],label=\"2015\")\n", "plt.ylabel('Overall Sales', fontsize=12)\n", "plt.xlabel('Month', fontsize=12)\n", "plt.title('Monthly sales variation', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(['2013', '2014', '2015', '2016'], loc='upper left')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "29d3bae8b1b8321887ca427fc53fc96f45d6f584", "_cell_guid": "0c677ba8-d73c-42bf-8592-113f59d686bc"}, "cell_type": "markdown", "source": ["# Store\n", "- First lets check the distribution of stores across different store dimention fields\n", "- Cumilative sales across different store dimentions\n", "- Box plots for the same to understand variations\n", "\n", "## Store distribution"]}, {"metadata": {"_uuid": "964eb5606d3f0bbd58b13c52fdc31db95afdbb78", "_cell_guid": "ab04beab-584a-49de-8269-3efe25f53e73", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["#Count of stores in different types and clusters\n", "plt.figure(figsize=(15,12))\n", "#row col plotnumber - 121\n", "plt.subplot(221)\n", "# Count of stores for each type \n", "temp = stores['cluster'].value_counts()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[5])\n", "plt.ylabel('Count of stores', fontsize=12)\n", "plt.xlabel('Cluster', fontsize=12)\n", "plt.title('Store distribution across cluster', fontsize=15)\n", "\n", "plt.subplot(222)\n", "# Count of stores for each type \n", "temp = stores['type'].value_counts()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[7])\n", "plt.ylabel('Count of stores', fontsize=12)\n", "plt.xlabel('Type of store', fontsize=12)\n", "plt.title('Store distribution across store types', fontsize=15)\n", "\n", "plt.subplot(223)\n", "# Count of stores for each type \n", "temp = stores['state'].value_counts()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[8])\n", "plt.ylabel('Count of stores', fontsize=12)\n", "plt.xlabel('state', fontsize=12)\n", "plt.title('Store distribution across states', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "plt.subplot(224)\n", "# Count of stores for each type \n", "temp = stores['city'].value_counts()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[9])\n", "plt.ylabel('Count of stores', fontsize=12)\n", "plt.xlabel('City', fontsize=12)\n", "plt.title('Store distribution across cities', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "b51676678bc44aa66d6b1e72b2c91f4cd8d70cab", "_cell_guid": "c936f112-3d81-40ce-8533-4b6d8af6b909"}, "cell_type": "markdown", "source": ["## Sale distribution "]}, {"metadata": {"_uuid": "04f485355888ddd1f9b146e3bfe51ee213fc8d20", "_cell_guid": "e1eadded-4c0c-4c94-9677-3244fed78d6c", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["sale_store_level=sale_store_level.iloc[:,0:2]\n", "#print(sale_store_level)\n", "merge=pd.merge(sale_store_level,stores,how='left',on='store_nbr')\n", "#temp\n", "\n", "#Sale of stores in different types and clusters\n", "plt.figure(figsize=(15,12))\n", "#row col plotnumber - 121\n", "plt.subplot(221)\n", "# Sale of stores for each type \n", "temp = merge.groupby(['cluster'])['store_sales'].sum()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[5])\n", "plt.ylabel('Sales', fontsize=12)\n", "plt.xlabel('Cluster', fontsize=12)\n", "plt.title('Cumulative sales across store clusters', fontsize=15)\n", "\n", "plt.subplot(222)\n", "# sale of stores for each type \n", "temp = merge.groupby(['type'])['store_sales'].sum()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[7])\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('Type of store', fontsize=12)\n", "plt.title('Cumulative sales across store types', fontsize=15)\n", "\n", "plt.subplot(223)\n", "# sale of stores for each type \n", "temp = merge.groupby(['state'])['store_sales'].sum()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[8])\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('state', fontsize=12)\n", "plt.title('Cumulative sales across states', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "plt.subplot(224)\n", "# sale of stores for city\n", "temp = merge.groupby(['city'])['store_sales'].sum()\n", "#plot\n", "sns.barplot(temp.index,temp.values,color=color[9])\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('City', fontsize=12)\n", "plt.title('Cumulative sales across cities', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "ef83165fcadc556ab06d366740106f0a53c8f995", "_cell_guid": "297d94d7-fb4c-4e85-b9fc-a17c41bc84e6", "collapsed": true}, "cell_type": "markdown", "source": ["- Interesting fact here is that store cluster number 14 which has only 4 stores has the most sales.\n", "\n", "# Sale variation"]}, {"metadata": {"_uuid": "73c18d142f3847c2ade5aa9c802d425f34a681d4", "_cell_guid": "9b85cd84-f761-494a-8cbe-a45f916e61f8", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["sale_store_level=sale_store_level.iloc[:,0:2]\n", "merge=pd.merge(sale_store_level,stores,how='left',on='store_nbr')\n", "\n", "plt.figure(figsize=(15,12))\n", "#row col plotnumber - 121\n", "plt.subplot(221)\n", "#plot\n", "sns.boxplot(x='cluster', y=\"store_sales\", data=merge)\n", "plt.ylabel('Sales', fontsize=12)\n", "plt.xlabel('Cluster', fontsize=12)\n", "plt.title('Variation across store clusters', fontsize=15)\n", "\n", "plt.subplot(222)\n", "# sale of stores for each type \n", "sns.boxplot(x='type', y=\"store_sales\", data=merge)\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('Type of store', fontsize=12)\n", "plt.title('Variation across store types', fontsize=15)\n", "\n", "plt.subplot(223)\n", "# sale of stores for each type \n", "sns.boxplot(x='state', y=\"store_sales\", data=merge)\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('state', fontsize=12)\n", "plt.title('Variation across states', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "\n", "plt.subplot(224)\n", "# sale of stores for city\n", "sns.boxplot(x='city', y=\"store_sales\", data=merge)\n", "plt.ylabel('sales', fontsize=12)\n", "plt.xlabel('City', fontsize=12)\n", "plt.title('Variation across cities', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "aa2d9e76e706dc8dc60dfad7720ca8f2f2860e64", "_cell_guid": "62cf719b-ae12-4f95-a3d0-8ad281a866b4", "collapsed": true}, "outputs": [], "cell_type": "code", "source": [], "execution_count": null}, {"metadata": {"_uuid": "4dc1798155bf6eb7e84ddf16f207ae1dd9fd92a3", "_cell_guid": "8a5b26b6-2611-4f38-b08f-a1c5ebb44983"}, "cell_type": "markdown", "source": ["- There are a lot of cases where there is only one store that is present in that specific grouping. Hence the single lines in the boxplot above\n", "    - Clusters 5,16,17,12 have only one store\n", "    - 8 states have only one store\n", "    - 15 cities have only one store\n", "    "]}, {"metadata": {"_uuid": "a4dd87270fcd53da4186c40c95d35c61dd8973b5", "_cell_guid": "2a08e73a-d476-4cc4-9cbe-94335ed185d9", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["#transactions\n", "# month over month sales\n", "trans['date']=pd.to_datetime(trans['date'])\n", "#print(trans.dtypes)\n", "temp=trans.groupby(['date']).aggregate({'store_nbr':'count','transactions':np.sum})\n", "temp=temp.reset_index()\n", "temp_2013=temp[temp['date'].dt.year==2013].reset_index(drop=True)\n", "temp_2014=temp[temp['date'].dt.year==2014].reset_index(drop=True)\n", "temp_2015=temp[temp['date'].dt.year==2015].reset_index(drop=True)\n", "temp_2016=temp[temp['date'].dt.year==2016].reset_index(drop=True)\n", "temp_2017=temp[temp['date'].dt.year==2017].reset_index(drop=True)\n", "\n", "#print(temp)\n", "sns.set(style=\"whitegrid\", color_codes=True)\n", "# temp\n", "plt.figure(figsize=(15,14))\n", "plt.subplot(211)\n", "plt.plot(temp_2013['date'],temp_2013.iloc[:,1],label=\"2013\")\n", "plt.plot(temp_2014['date'],temp_2014.iloc[:,1],label=\"2014\")\n", "plt.plot(temp_2015['date'],temp_2015.iloc[:,1],label=\"2015\")\n", "plt.plot(temp_2016['date'],temp_2016.iloc[:,1],label=\"2016\")\n", "plt.plot(temp_2017['date'],temp_2017.iloc[:,1],label=\"2017\")\n", "plt.ylabel('Number of stores open', fontsize=12)\n", "plt.xlabel('Time', fontsize=12)\n", "plt.title('Number of stores open', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(['2013', '2014', '2015', '2016'], loc='lower right')\n", "\n", "plt.subplot(212)\n", "plt.plot(temp_2013.index,temp_2013.iloc[:,1],label=\"2013\")\n", "plt.plot(temp_2014.index,temp_2014.iloc[:,1],label=\"2014\")\n", "plt.plot(temp_2015.index,temp_2015.iloc[:,1],label=\"2015\")\n", "plt.plot(temp_2016.index,temp_2016.iloc[:,1],label=\"2016\")\n", "plt.plot(temp_2017.index,temp_2017.iloc[:,1],label=\"2017\")\n", "\n", "\n", "plt.ylabel('Number of stores open', fontsize=12)\n", "plt.xlabel('Day of year', fontsize=12)\n", "plt.title('Number of stores open', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(['2013', '2014', '2015', '2016'], loc='lower right')\n", "plt.show()\n", "\n"], "execution_count": null}, {"metadata": {"_uuid": "c85cb156b1030ba98156cf54225c63a2477f7075", "_cell_guid": "7b60aff5-1718-4354-a04c-d3fed2e8019f", "collapsed": true}, "outputs": [], "cell_type": "code", "source": [], "execution_count": null}, {"metadata": {"_uuid": "abc095a6016920ed5c1c1692364f493e97c9bd6a", "_cell_guid": "14a8f290-268b-441b-91ed-654563557928"}, "cell_type": "markdown", "source": [" - New year seems to be the only time when most of the stores are closed.(Store number 25 being the exception)\n", "     - But in 2016 new year all the stores were closed. There was only one store opened on 2nd of Jan which is an oddity\n", " - There seems to be certain local holidays where some of the stores are closed. But there is no consistent pattern of holidays where stores are closed\n", " \n", " ## Store age"]}, {"metadata": {"_uuid": "c51e422c6ffbdf0915df22ea9bdf8eda4d9e34b8", "_cell_guid": "716db4e6-2765-4cfb-999d-554dc5270c3e", "collapsed": true, "_kg_hide-input": false}, "outputs": [], "cell_type": "code", "source": ["temp=trans.groupby(['store_nbr']).agg({'date':[np.min,np.max]}).reset_index()\n", "temp['store_age']=temp['date']['amax']-temp['date']['amin']\n", "temp['open_year']=temp['date']['amin'].dt.year\n", "data=temp['open_year'].value_counts()\n", "#print(data)\n", "plt.figure(figsize=(12,4))\n", "sns.barplot(data.index,data.values, alpha=0.8, color=color[0])\n", "plt.ylabel('Stores', fontsize=12)\n", "plt.xlabel('Store opening Year', fontsize=12)\n", "plt.title('When were the stores started?', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.show()\n"], "execution_count": null}, {"metadata": {"_uuid": "8086c77776cfadd8179d8eb8b09c7f4de6742785", "_cell_guid": "a7eb9cac-2343-4bf0-9bc1-df9d5b9e25f7"}, "cell_type": "markdown", "source": ["- 5 Stores were opened in 2015 and 1 each in 2014 and 2017\n", "\n", "# Item preference across store types\n", "\n", "Lets try out new plotly library for treemaps"]}, {"metadata": {"_uuid": "1452cfad93c5bd0b6af2da1b30ad0e56d5bc4b33", "_cell_guid": "2a6118b3-817e-4d24-b8ef-f3865f2fa8b4", "collapsed": true, "_kg_hide-input": false}, "outputs": [], "cell_type": "code", "source": ["store_items=pd.merge(sale_store_item_level,items,on='item_nbr')\n", "store_items=pd.merge(store_items,stores,on='store_nbr')\n", "store_items['item_sales']=store_items['item_sales']\n", "\n", "#item\n", "# top selling items by store type\n", "top_items_by_type=store_items.groupby(['type','item_nbr'])['item_sales'].sum()\n", "top_items_by_type=top_items_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "\n", "#get top 5\n", "top_items_by_type=top_items_by_type.groupby(['type']).head(5)\n", "\n", "\n", "#class\n", "# top selling item class by store type\n", "top_class_by_type=store_items.groupby(['type','class'])['item_sales'].sum()\n", "top_class_by_type=top_class_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "\n", "#get top 5\n", "top_class_by_type=top_class_by_type.groupby(['type']).head(5)\n", "\n", "\n", "#family\n", "# top selling item family by store type\n", "top_family_by_type=store_items.groupby(['type','family'])['item_sales'].sum()\n", "top_family_by_type=top_family_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "\n", "#get top 5\n", "top_family_by_type=top_family_by_type.groupby(['type']).head(5)\n"], "execution_count": null}, {"metadata": {"_uuid": "6d74ce479b50d3951d483c87385fc93b79a1e7f0", "_cell_guid": "fcc202ae-65bd-4ced-b6d9-05b7fdc4b44c", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["plt.figure(figsize=(12,5))\n", "\n", "x=top_family_by_type.pivot(index='type',columns='family')\n", "x.plot.bar(stacked=True,figsize=(12,5))\n", "y=x.columns.droplevel(0).values\n", "#print(y)\n", "plt.ylabel('Sales', fontsize=12)\n", "plt.xlabel('Top 5 item families', fontsize=12)\n", "plt.title('Top 5 item families across different store types', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(y)\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "4c66e2fabcde8960cf946736d98914d86d3a7dd6", "_cell_guid": "08560f15-54cd-4d81-a770-780d637ed245", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["plt.figure(figsize=(12,5))\n", "x=top_class_by_type.pivot(index='type',columns='class')\n", "x.plot.bar(stacked=True,figsize=(12,5))\n", "y=x.columns.droplevel(0).values\n", "#print(y)\n", "plt.ylabel('Sales', fontsize=12)\n", "plt.xlabel('Top 5 item classes', fontsize=12)\n", "plt.title('Top 5 item classes across different store types', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(y)\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "3fd91b22c36f09f44e1362f749c132d76075be6a", "_cell_guid": "3e5fd295-ac4f-4ff0-8fae-a0864fd4fd41", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["plt.figure(figsize=(12,5))\n", "x=top_items_by_type.pivot(index='type',columns='item_nbr')\n", "x.plot.bar(stacked=True,figsize=(12,5))\n", "y=x.columns.droplevel(0).values\n", "#print(y)\n", "plt.ylabel('Sales', fontsize=12)\n", "plt.xlabel('Top 5 items ', fontsize=12)\n", "plt.title('Top 5 items across different store types', fontsize=15)\n", "plt.xticks(rotation='vertical')\n", "plt.legend(y)\n", "plt.show()"], "execution_count": null}, {"metadata": {"_uuid": "de8cf307eafccaed2c4602a2955af760fb1793c0", "_cell_guid": "1f0b17fe-619c-4677-bb5e-62463f924310"}, "cell_type": "markdown", "source": ["## Performance of Item families across stores of different type"]}, {"metadata": {"_uuid": "0c126f904ac3edb55e0edf765a5a71884a68c9cf", "_cell_guid": "a3cc37c1-cb8c-4a38-882b-f9ce109e1d38", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["top_family_by_type=store_items.groupby(['type','family'])['item_sales'].sum()\n", "top_family_by_type=top_family_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "x=top_family_by_type.pivot(index='family',columns='type')\n", "cm = sns.light_palette(\"orange\", as_cmap=True)\n", "x = x.style.background_gradient(cmap=cm)\n", "x"], "execution_count": null}, {"metadata": {"_uuid": "e930b56521237a18e1a57e13a4817d0e73890865", "_cell_guid": "2bd74692-b079-4e7c-b024-85bc8362c01a"}, "cell_type": "markdown", "source": ["## Top 20 Item classes(by overall sales)\n", "- The distribution of sale across the store types for the top 20 item classes have been shown below\n", "- The darker the color gradient the more the store type has contributed to the sale of items in that class"]}, {"metadata": {"_uuid": "89a19eaf10c78166ca2c148b63cc0401a53bd807", "_cell_guid": "11f9fd7c-287f-479b-a018-47fff61cd601", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["top_class_by_type=store_items.groupby(['type','class'])['item_sales'].sum()\n", "top_class_by_type=top_class_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "top_class_by_type=top_class_by_type.groupby(['class']).head(20)\n", "x=top_class_by_type.pivot(index='class',columns='type')\n", "x['total']=x.sum(axis=1)\n", "x=x.sort_values('total',ascending=False)\n", "del(x['total'])\n", "x=x.head(20)\n", "cm = sns.light_palette(\"gray\", as_cmap=True)\n", "x = x.style.background_gradient(cmap=cm,axis=1)\n", "x"], "execution_count": null}, {"metadata": {"_uuid": "476441ec5ed87cae06f6563ee2678e2315bf2fa9", "_cell_guid": "f4476db4-4d96-4e03-ae60-b0790232bcd7"}, "cell_type": "markdown", "source": ["## Top 30 Items(by overall sales)\n", "- The distribution of sale across the store types for the top 30 items have been shown below\n", "- The darker the color gradient the more the store type has contributed to the sale of items in that class"]}, {"metadata": {"_uuid": "5eaac1ecdd680c1f44001622a3497f67841b02fc", "_cell_guid": "56529664-00b0-42ce-a143-3a03797637ed", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": ["top_items_by_type=store_items.groupby(['type','item_nbr'])['item_sales'].sum()\n", "top_items_by_type=top_items_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n", "top_items_by_type=top_items_by_type.groupby(['item_nbr']).head(20)\n", "#print(top_items_by_type)\n", "x=top_items_by_type.pivot(index='item_nbr',columns='type')\n", "x['total']=x.sum(axis=1)\n", "x=x.sort_values('total',ascending=False)\n", "del(x['total'])\n", "x=x.head(30)\n", "cm = sns.light_palette(\"green\", as_cmap=True)\n", "x = x.style.background_gradient(cmap=cm,axis=1)\n", "x"], "execution_count": null}, {"metadata": {"_uuid": "343fa61a2e2c4f7410fa3161e9734497e0f9a22b", "_cell_guid": "ae96637f-d7c5-4f8e-9e91-b0ca68fd66f4"}, "cell_type": "markdown", "source": ["\n", "    "]}, {"metadata": {"_uuid": "24047879ce540b4cbc2d2f360d3401ec366e05fd", "_cell_guid": "ef09dfe2-3eb0-43d2-8f6f-35b0a2d58ddc"}, "cell_type": "markdown", "source": ["# To be continued"]}, {"metadata": {"_uuid": "4ff1b48ea39b48e1954e34b53207fae4f0789c04", "_kg_hide-output": true, "_cell_guid": "24e42959-aea1-447b-974c-4e4d08bbf23f", "collapsed": true, "_kg_hide-input": true}, "outputs": [], "cell_type": "code", "source": [], "execution_count": null}], "nbformat": 4}