{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np \nimport pandas as pd\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-01-07T06:28:40.398019Z","iopub.execute_input":"2022-01-07T06:28:40.398302Z","iopub.status.idle":"2022-01-07T06:28:40.405643Z","shell.execute_reply.started":"2022-01-07T06:28:40.398275Z","shell.execute_reply":"2022-01-07T06:28:40.404672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install py7zr","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:27:16.162606Z","iopub.execute_input":"2022-01-07T06:27:16.162892Z","iopub.status.idle":"2022-01-07T06:27:30.624836Z","shell.execute_reply.started":"2022-01-07T06:27:16.162860Z","shell.execute_reply":"2022-01-07T06:27:30.623969Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import py7zr\nfrom subprocess import check_output\n\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        archive = py7zr.SevenZipFile(os.path.join(dirname, filename), mode='r')\n        archive.extractall(path=\"/kaggle/working\")\n        archive.close()\n\nprint(check_output([\"ls\", \"../working\"]).decode(\"utf8\"))","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:33:25.591684Z","iopub.execute_input":"2022-01-07T06:33:25.592634Z","iopub.status.idle":"2022-01-07T06:34:47.521599Z","shell.execute_reply.started":"2022-01-07T06:33:25.592588Z","shell.execute_reply":"2022-01-07T06:34:47.519532Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../working/test.csv\")\nstores = pd.read_csv(\"../working/stores.csv\")\nitems = pd.read_csv(\"../working/items.csv\")\ntrans = pd.read_csv(\"../working/transactions.csv\")\noil = pd.read_csv(\"../working/oil.csv\")\nholiday = pd.read_csv(\"../working/holidays_events.csv\")\nprint(\"done\")","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:35:55.395492Z","iopub.execute_input":"2022-01-07T06:35:55.397498Z","iopub.status.idle":"2022-01-07T06:35:56.868685Z","shell.execute_reply.started":"2022-01-07T06:35:55.397442Z","shell.execute_reply":"2022-01-07T06:35:56.867821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Since train.csv has 125 mil records, it is best to consider performing some data engineering before starting any analysis.","metadata":{}},{"cell_type":"code","source":"#check memory use for the two biggest files - train and test\n#mem_train = train.memory_usage(index=True).sum()\nmem_test=test.memory_usage(index=True).sum()\n#print(\"train dataset uses \",mem_train/ 1024**2,\" MB\")\nprint(\"test dataset uses \",mem_test/ 1024**2,\" MB\")\n\ntest.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:36:15.048370Z","iopub.execute_input":"2022-01-07T06:36:15.048627Z","iopub.status.idle":"2022-01-07T06:36:15.087773Z","shell.execute_reply.started":"2022-01-07T06:36:15.048601Z","shell.execute_reply":"2022-01-07T06:36:15.086425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# optimize test.csv\n# First check the contents of train.csv\nprint(test.max())\nprint(test.min())\n#check datatypes\nprint(test.dtypes)","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:36:27.439000Z","iopub.execute_input":"2022-01-07T06:36:27.439536Z","iopub.status.idle":"2022-01-07T06:36:27.942168Z","shell.execute_reply.started":"2022-01-07T06:36:27.439494Z","shell.execute_reply":"2022-01-07T06:36:27.941197Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#There are only 54 stores\ntest['store_nbr'] = test['store_nbr'].astype(np.uint8)\n\n# The ID column is a continuous number from 1 to 128867502 in train and 128867503 to 125497040 in test\ntest['id'] = test['id'].astype(np.uint32)\n\n# item number is unsigned \ntest['item_nbr'] = test['item_nbr'].astype(np.uint32)\n\n#Converting the date column to date format\ntest['date']=pd.to_datetime(test['date'],format=\"%Y-%m-%d\")\n\n#check memory\nprint(test.memory_usage(index=True))\nnew_mem_test=test.memory_usage(index=True).sum()\nprint(\"test dataset uses \",new_mem_test/ 1024**2,\" MB after changes\")\nprint(\"memory saved =\",(mem_test-new_mem_test)/ 1024**2,\" MB\")","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:36:44.508037Z","iopub.execute_input":"2022-01-07T06:36:44.508729Z","iopub.status.idle":"2022-01-07T06:36:44.973703Z","shell.execute_reply.started":"2022-01-07T06:36:44.508681Z","shell.execute_reply":"2022-01-07T06:36:44.972754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Around 50% save in memory utilization","metadata":{}},{"cell_type":"code","source":"print(test.memory_usage())\n\n#check range of float 16\nmin_value = np.finfo(np.float16).min\nmax_value = np.finfo(np.float16).max\nprint(\"range of float16 is\",min_value,max_value)","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:37:37.925554Z","iopub.execute_input":"2022-01-07T06:37:37.926692Z","iopub.status.idle":"2022-01-07T06:37:37.935247Z","shell.execute_reply.started":"2022-01-07T06:37:37.926632Z","shell.execute_reply":"2022-01-07T06:37:37.934498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dtype_dict={\"id\":np.uint32,\n            \"store_nbr\":np.uint8,\n            \"item_nbr\":np.uint32,\n            \"unit_sales\":np.float32\n           }\n\ntrain_part1 = pd.read_csv(\"../working/train.csv\",dtype=dtype_dict,usecols=[0,2,3,4])\nprint(train_part1.dtypes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_part2=pd.read_csv(\"../working/train.csv\",dtype=dtype_dict,usecols=[1,5],parse_dates=[0])\ntrain_part2['Year'] = pd.DatetimeIndex(train_part2['date']).year\ntrain_part2['Month'] = pd.DatetimeIndex(train_part2['date']).month\ntrain_part2['Day'] =pd.DatetimeIndex(train_part2['date']).day.astype(np.uint8)\ndel(train_part2['date'])\ntrain_part2['Day']=train_part2['Day'].astype(np.uint8)\ntrain_part2['Month']=train_part2['Month'].astype(np.uint8)\ntrain_part2['Year']=train_part2['Year'].astype(np.uint16)\n\n#impute the missing values to be -1\ntrain_part2[\"onpromotion\"].fillna(0, inplace=True)\ntrain_part2[\"onpromotion\"]=train_part2[\"onpromotion\"].astype(np.int8)\nprint(train_part2.head())\nprint(train_part2.dtypes)","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:39:53.765870Z","iopub.execute_input":"2022-01-07T06:39:53.766393Z","iopub.status.idle":"2022-01-07T06:41:54.102858Z","shell.execute_reply.started":"2022-01-07T06:39:53.766343Z","shell.execute_reply":"2022-01-07T06:41:54.101800Z"},"trusted":true},"execution_count":null,"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\ntrain = pd.concat([train_part1.reset_index(drop=True), train_part2], axis=1)\n#drop temp files\ndel(train_part1)\ndel(train_part2)\n#Further Id is just an indicator column, hence not required for analysis\nid=train['id']\ndel(train['id'])\n# check memory\nprint(train.memory_usage())\n#The extracted train.csv file is approx 5 GB\nmem_train=5*1024**3\nnew_mem_train=train.memory_usage().sum()\nprint(\"Train dataset uses \",new_mem_train/ 1024**2,\" MB after changes\")\nprint(\"memory saved is approx\",(mem_train-new_mem_train)/ 1024**2,\" MB\")","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:43:18.656704Z","iopub.execute_input":"2022-01-07T06:43:18.657463Z","iopub.status.idle":"2022-01-07T06:43:21.854080Z","shell.execute_reply.started":"2022-01-07T06:43:18.657416Z","shell.execute_reply":"2022-01-07T06:43:21.853206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"1.6GB is a managable size","metadata":{}},{"cell_type":"markdown","source":"# Now lets look into the dataset","metadata":{}},{"cell_type":"markdown","source":"\n\n## Further to make EDA easier, rolling up the sales to different levels\n\n - Day-Store level\n - Day-Item level\n - Store level\n - Item level\n - Day level\n\n","metadata":{}},{"cell_type":"markdown","source":"\n Store-day level sale -- This variable indicates the sale of a particular store over time\n\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 \n Item-day level count -- This gives an indication of the popularity of the item across the supermarket chain.\n","metadata":{}},{"cell_type":"code","source":"sale_day_store_level=train.groupby(['Year','Month','Day','store_nbr'])['unit_sales'].sum()\nsale_day_item_level=train.groupby(['Year','Month','Day','item_nbr'])['unit_sales'].sum()","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:43:51.861521Z","iopub.execute_input":"2022-01-07T06:43:51.862153Z","iopub.status.idle":"2022-01-07T06:44:27.908685Z","shell.execute_reply.started":"2022-01-07T06:43:51.862093Z","shell.execute_reply":"2022-01-07T06:44:27.907422Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def aggregate_level1(df):\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    \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    sale_day_item_level=sale_day_item_level.reset_index().rename(columns={'sum':'item_sales','count':'store_spread'})\n    \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    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","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:53:27.629862Z","iopub.execute_input":"2022-01-07T06:53:27.630290Z","iopub.status.idle":"2022-01-07T06:53:27.638926Z","shell.execute_reply.started":"2022-01-07T06:53:27.630245Z","shell.execute_reply":"2022-01-07T06:53:27.638218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import time\nstart_time = time.time()\nsale_day_store_level,sale_day_item_level,sale_store_item_level=aggregate_level1(train)\n\nend_time=time.time()\ntime_taken=end_time-start_time\nprint(\"This block took \",time_taken,\"seconds\")","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:56:57.446847Z","iopub.execute_input":"2022-01-07T06:56:57.447155Z","iopub.status.idle":"2022-01-07T06:58:02.469172Z","shell.execute_reply.started":"2022-01-07T06:56:57.447101Z","shell.execute_reply":"2022-01-07T06:58:02.468175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sale_day_store_level.to_csv(\"sale_day_store_level.csv\")\nsale_day_item_level.to_csv(\"sale_day_item_level.csv\")\nsale_store_item_level.to_csv(\"sale_store_item_level.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-01-07T06:59:17.464549Z","iopub.execute_input":"2022-01-07T06:59:17.465577Z","iopub.status.idle":"2022-01-07T06:59:32.062562Z","shell.execute_reply.started":"2022-01-07T06:59:17.465516Z","shell.execute_reply":"2022-01-07T06:59:32.061923Z"},"trusted":true},"execution_count":null,"outputs":[]}]}