{"metadata":{"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.8.18"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":7391,"databundleVersionId":44328,"sourceType":"competition"}],"dockerImageVersionId":30732,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# 1.数据准备","metadata":{}},{"cell_type":"code","source":"#basic\nimport numpy as np \nimport pandas as pd\n#viz\nimport seaborn as sns\nimport matplotlib.pyplot as plt\ncolor = sns.color_palette()\n#others\nimport subprocess\nfrom subprocess import check_output\nimport gc\n\n%matplotlib inline","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"查看每个文件的记录条数","metadata":{}},{"cell_type":"code","source":"# 获取 input 目录下的所有文件\nfiles = check_output([\"ls\", \"input\"]).decode(\"utf8\")\n\n# 检查每个文件的行数\nfor file in files.split(\"\\n\"):\n    if file.strip():\n        path = 'input/' + file\n        # 使用 wc -l 命令来统计行数\n        popenobj = subprocess.Popen(['wc', '-l', path], stdout=subprocess.PIPE, stderr=subprocess.PIPE)\n        result, error = popenobj.communicate()\n        # 打印文件名和行数\n        print(\"The file :\", file, \"has :\", result.strip().split()[0], \"rows\")\n    else:\n        # 打印空文件名的提示\n        print(f\"Found an empty or invalid file name: '{file}'\")\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.read_csv(\"input/train.csv\")\ntest = pd.read_csv(\"input/test.csv\")\nstores = pd.read_csv(\"input/stores.csv\")\nitems = pd.read_csv(\"input/items.csv\")\ntrans = pd.read_csv(\"input/transactions.csv\")\noil = pd.read_csv(\"input/oil.csv\")\nholiday = pd.read_csv(\"input/holidays_events.csv\")\nprint(\"done\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"这个警告提醒我们，在处理大文件时，pandas 不会一次性读取整个文件，而是将文件分块读取。每一块数据都会独立进行数据类型的推断。\\\n因为 pandas 不知道其他块中的数据，它只能根据当前块的数据来推断数据类型，这可能导致某些列的类型被错误地推断。\\\n所以可以指定数据类型，可以避免数据类型自动推断错误，并能加快读取速度。","metadata":{}},{"cell_type":"code","source":"# 计算最大的两个文件的内存使用情况\nmem_train = train.memory_usage(index=True).sum()\nmem_test = test.memory_usage(index=True).sum()\n\n# 打印内存使用情况，单位是MB\nprint(\"train dataset uses \",mem_train/ 1024**2,\" MB\")\nprint(\"test dataset uses\", mem_test / 1024**2, \"MB\")\n\n# checking contents in train\ntest.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2.内存优化","metadata":{}},{"cell_type":"markdown","source":"### 步骤概述：减少内存消耗的方法\n\n为了有效地减少数据处理中的内存消耗，采用了以下几个步骤：\n\n1. **检查列中的值范围**：\n   在优化数据类型之前，首先检查每一列中的值的范围。这有助于确定使用哪种数据类型可以更有效地存储这些值。\n\n2. **查找合适的数据类型**：\n   参考以下链接中提供的Numpy数据类型指南，来选择合适的数据类型：\n   [Numpy数据类型指南](https://docs.scipy.org/doc/numpy-1.13.0/user/basics.types.html)\n\n3. **更改数据类型**：\n   根据前一步确定的合适数据类型，将列的数据类型进行更改，以减少内存占用。\n\n4. **将日期列拆分为三个独立的列**：\n   这个操作有两个主要原因：\n   - 在Pandas中，对类型为 \"datetime\" 的列进行操作时，这些操作不是矢量化的，因此处理这些列会消耗更多的时间。\n   - 将日期拆分为三个独立的列（年、月、日），能够更好地利用内存。例如，在测试数据集中，日期列大约使用了25 MB的内存，而店铺编号（uint8类型）仅使用了大约3 MB的内存。\n\n5. **填充缺失的促销列**：\n   对于“促销”列，填充缺失值，以避免在分析过程中出现空值问题。\n\n6. **合并所有处理过的数据**：\n   最后，将处理过的各个数据部分重新合并成一个完整的数据框。","metadata":{}},{"cell_type":"markdown","source":"## 2.1测试数据内存优化","metadata":{}},{"cell_type":"code","source":"# 打印每列的最大值\nprint(\"Max values in test dataset:\")\nprint(test.max())\n\n# 打印每列的最小值\nprint(\"Min values in test dataset:\")\nprint(test.min())\n\n# 检查每列的数据类型\nprint(\"Data types in test dataset:\")\nprint(test.dtypes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 只有54家商店 转换 store_nbr 列为 uint8 类型 无符号整数（0 至 255）\ntest['store_nbr'] = test['store_nbr'].astype(np.uint8)\n\n# ID在train中是从1到128867502，在test中是从128867503到125497040 转换 id 列为 uint32 类型（0 至 4294967295）\ntest['id'] = test['id'].astype(np.uint32)\n\n# 商品编号 转换 item_nbr 列为 uint32 类型\ntest['item_nbr'] = test['item_nbr'].astype(np.uint32)\n\n# 将 date 列转换为日期格式\ntest['date'] = pd.to_datetime(test['date'], format=\"%Y-%m-%d\")\n\n# 打印每列的内存使用情况\nprint(\"Memory usage per column after changes:\")\nprint(test.memory_usage(index=True))\n\n# 计算优化后的总内存使用情况\nnew_mem_test = test.memory_usage(index=True).sum()\nprint(\"Test dataset uses\", new_mem_test / 1024**2, \"MB after changes\")\n\n# 计算节省的内存\nmemory_saved = (mem_test - new_mem_test) / 1024**2\nprint(\"Memory saved =\", memory_saved, \"MB\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2.2训练数据内存优化","metadata":{}},{"cell_type":"code","source":"# 打印每列的最大值\nprint(\"Max values in train dataset:\")\nprint(train.max())\n\n# 打印每列的最小值\nprint(\"Min values in train dataset:\")\nprint(train.min())\n\n# 检查每列的数据类型\nprint(\"Data types in train dataset:\")\nprint(train.dtypes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"id、store_nbr、item_nbr、unit_sales这些只需要转换类型，所以这一部分一起处理。\\\ndate需要转换为3列、onpromotion需要处理缺失值，所以这两个列一起处理。","metadata":{}},{"cell_type":"code","source":"# 定义数据类型转换的字典\ndtype_dict = {\n    \"id\": np.uint32,\n    \"store_nbr\": np.uint8,\n    \"item_nbr\": np.uint32,\n    \"unit_sales\": np.float32  # 使用 float32 而非 float16 以避免精度损失\n}\n\n# 读取 train.csv 文件的主部分，指定使用的列和数据类型\ntrain_part1 = pd.read_csv(\"input/train.csv\", dtype=dtype_dict, usecols=[0, 2, 3, 4])\n\n# 输出数据类型以验证转换是否成功\nprint(train_part1.dtypes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 读取整个 train.csv 数据集\ntrain_part2 = pd.read_csv(\"input/train.csv\", dtype=dtype_dict, usecols=[1, 5], parse_dates=[0])\n\n# 提取日期信息（年、月、日）\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\n\n# 删除原始的日期列\ndel train_part2['date']\n\n# 转换年、月、日的类型\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# 处理 onpromotion 列中的缺失值，填充为 0\ntrain_part2[\"onpromotion\"].fillna(0, inplace=True)\ntrain_part2[\"onpromotion\"] = train_part2[\"onpromotion\"].astype(np.int8)\n\n# 输出前几行数据和数据类型信息\nprint(train_part2.head())\nprint(train_part2.dtypes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 将 train_part1 和 train_part2 按列合并\ntrain = pd.concat([train_part1.reset_index(drop=True), train_part2], axis=1)\n\n# 删除临时变量以释放内存\ndel(train_part1)\ndel(train_part2)\n\n# id 列只是一个标识符，对分析没有实际意义，故删除\nid = train['id']\ndel(train['id'])\n\n# 检查内存使用情况\nprint(train.memory_usage())\n\nnew_mem_train = train.memory_usage().sum()\n\n# 显示优化后的内存使用情况\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_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.探索数据分析（EDA）","metadata":{}},{"cell_type":"markdown","source":"## 3.1数据预处理","metadata":{}},{"cell_type":"code","source":"# 显示 'unit_sales' 列的描述性统计\nsummary_stats = train['unit_sales'].describe()\nprint(summary_stats)\n\n# 检查 'unit_sales' 列中是否存在空值\nmissing_values_count = train['unit_sales'].isnull().sum()\nprint(\"Number of missing values in 'unit_sales':\", missing_values_count)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"按年份分割大数据集，便于分步处理。","metadata":{}},{"cell_type":"code","source":"train_2013=train.loc[train['Year']==2013]\ntrain_2014=train.loc[train['Year'] ==2014]\ntrain_2015=train.loc[train['Year'] ==2015]\ntrain_2016=train.loc[train['Year'] ==2016]\ntrain_2017=train.loc[train['Year'] ==2017]","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def aggregate_level1(df):\n    #计算每天每个店铺的总销售量和销售的商品种类数。\n    sale_day_store_level = df.groupby(['Year', 'Month', 'Day', 'store_nbr'], as_index=False)['unit_sales'].agg(['sum', 'count'])\n    sale_day_store_level = sale_day_store_level.reset_index().rename(columns={'sum': 'store_sales', 'count': 'item_variety'})\n    \n    #计算每天每个商品的总销售量和销售的店铺数量。\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    #计算每个店铺中每个商品的总销售量和销售记录数。\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_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 提供店铺每天的销售情况，帮助了解每个店铺的日销售表现和商品种类的多样性。\n- 提供商品每天的销售情况，帮助分析每个商品的受欢迎程度及其在不同店铺的销售分布。\n- 提供每个店铺和商品组合的销售情况，帮助理解每个店铺中的特定商品销售表现以及销售记录的频次。","metadata":{}},{"cell_type":"code","source":"#run for 2013\nsale_day_store_level_2013,sale_day_item_level_2013,sale_store_item_level_2013=aggregate_level1(train_2013)\nprint(sale_day_store_level_2013.head())\nsale_day_item_level_2013.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import time\nstart_time = time.time()\n#run for 2014\nsale_day_store_level_2014,sale_day_item_level_2014,sale_store_item_level_2014=aggregate_level1(train_2014)\n#run for 2015\nsale_day_store_level_2015,sale_day_item_level_2015,sale_store_item_level_2015=aggregate_level1(train_2015)\n#run for 2016\nsale_day_store_level_2016,sale_day_item_level_2016,sale_store_item_level_2016=aggregate_level1(train_2016)\n#run for 2017\nsale_day_store_level_2017,sale_day_item_level_2017,sale_store_item_level_2017=aggregate_level1(train_2017)\n\nend_time=time.time()\ntime_taken=end_time-start_time\nprint(\"This block took \",time_taken,\"seconds\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 将各年的日-店级别数据拼接在一起\nsale_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# 将各年的日-商品级别数据拼接在一起\nsale_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\n# 将各年的店-商品级别数据拼接在一起\nsale_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])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 删除不再需要的变量以释放内存\ndel(sale_day_store_level_2013)\ndel(sale_day_store_level_2014)\ndel(sale_day_store_level_2015)\ndel(sale_day_store_level_2016)\ndel(sale_day_store_level_2017)\ndel(sale_day_item_level_2013)\ndel(sale_day_item_level_2014)\ndel(sale_day_item_level_2015)\ndel(sale_day_item_level_2016)\ndel(sale_day_item_level_2017)\ndel(sale_store_item_level_2013)\ndel(sale_store_item_level_2014)\ndel(sale_store_item_level_2015)\ndel(sale_store_item_level_2016)\ndel(sale_store_item_level_2017)\n\n# 手动调用垃圾收集器\ngc.collect()","metadata":{},"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_count":null,"outputs":[]},{"cell_type":"code","source":"# 使用列表来选择多列，并进行 groupby 和聚合操作\nsale_store_level = sale_day_store_level.groupby(['store_nbr'], as_index=False)[['store_sales', 'item_variety']].agg(['sum'])\n# groupby 操作生成的结果有多级索引，去掉其中的一层，即去除 sum 这一层级。\nsale_store_level.columns = sale_store_level.columns.droplevel(1)\n#重置索引以将 DataFrame 的索引恢复为默认整数索引。\nsale_store_level=sale_store_level.reset_index()\nsale_store_level.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 按商品编号进行分组，并对每个商品的销售量进行求和\nsale_item_level=sale_day_item_level.groupby(['item_nbr'],as_index=False)['item_sales'].agg(['sum'])\n# groupby 操作后需要重置索引，以便得到一个标准的 DataFrame\nsale_item_level=sale_item_level.reset_index()\nsale_item_level.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3.2数据可视化分析","metadata":{}},{"cell_type":"code","source":"# 按照销售额（store_sales）降序排序，并重置索引\ntemp = sale_store_level.sort_values('store_sales', ascending=False).reset_index(drop=True)\ntemp = temp.set_index('store_nbr').head(10)\n\n# 绘制条形图\nplt.figure(figsize=(12, 6))\nsns.barplot(x=temp.index, y=temp.store_sales, palette='viridis',order=temp.index)\n\n# 设置标题和标签\nplt.title('Top 10 Stores by Sales Volume', fontsize=16)\nplt.xlabel('Store Number', fontsize=12)\nplt.ylabel('Total Sales', fontsize=12)\n\n# 显示图表\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 假设 sale_item_level 已经定义并包含数据\n# 示例数据的排序和选择前 10 个项\ntemp1 = sale_item_level.sort_values('sum', ascending=False).reset_index(drop=True)\ntemp1 = temp1.set_index('item_nbr').head(10)\n\nplt.figure(figsize=(12, 8))\nx = temp1.index.values\ny = temp1['sum'].values\n\n# 正确的传递 x 和 y 作为关键字参数\nsns.barplot(x=x, y=y, alpha=0.8, palette='viridis',order=x)  # 使用 color_list 中的第 8 个颜色\n\nplt.ylabel('Overall Sales', fontsize=12)\nplt.xlabel('Item Number', fontsize=12)  # 应该是 \"Item Number\" 而不是 \"Store Number\"\nplt.title('Top Items by Overall Sale', fontsize=15)\n\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Overall sales\n#年度销售变化（Sale YOY）\n#按年份对销售数据进行分组，并计算每年的总销售额。\ntemp = sale_day_store_level.groupby('Year')['store_sales'].sum().reset_index()\n\nplt.figure(figsize=(13, 4))\nsns.pointplot(x='Year', y='store_sales', data=temp, color=color[1])\nplt.ylabel('Overall Sales', fontsize=12)\nplt.xlabel('Year', fontsize=12)\nplt.title('Sale YOY', fontsize=15)\nplt.xticks(rotation='vertical')\n\n#月度销售变化Month over month sales\ntemp = sale_day_store_level.groupby(['Year', 'Month'])['store_sales'].sum().reset_index()\ntemp['Month'] = temp['Year'].astype(str) + '-' + temp['Month'].astype(str)\n\nplt.figure(figsize=(13, 4))\nsns.pointplot(x=temp['Month'], y=temp['store_sales'], color=color[2])\nplt.ylabel('Overall Sales', fontsize=12)\nplt.xlabel('Month', fontsize=12)\nplt.title('Monthly sales variation', fontsize=15)\nplt.xticks(rotation='vertical')\n\n# Also checking the oil price change\noil['date'] = pd.to_datetime(oil['date'])\noil['Year'] = oil['date'].dt.year\noil['Month'] = oil['date'].dt.month\n\n#月度石油价格变化 Oil price variation over month\ntemp = oil.groupby(['Year', 'Month']).agg({'dcoilwtico': ['sum', 'count']})\ntemp.columns = temp.columns.droplevel(0)\ntemp['avg'] = temp['sum'] / temp['count']\ntemp = temp.reset_index()  # Resetting index for proper plotting\ntemp['Month'] = temp['Year'].astype(str) + '-' + temp['Month'].astype(str)\n\n# Plot\nplt.figure(figsize=(13, 4))\nsns.pointplot(x=temp['Month'], y=temp['avg'], color=color[4])\nplt.ylabel('Oil price', fontsize=12)\nplt.xlabel('Month', fontsize=12)\nplt.title('Monthly variation in oil price', fontsize=15)\nplt.xticks(rotation='vertical')\n\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"经济学和商业研究发现，油价下降与零售销售额的上升之间存在显著的正相关关系。《Journal of Consumer Research》的一项研究表明，油价变化对低收入家庭的消费模式有显著影响，油价下降时，这些家庭在非必需品上的支出显著增加 。","metadata":{}},{"cell_type":"code","source":"#月度销售变化 month over month sales\ntemp=sale_day_store_level.groupby(['Year','Month']).aggregate({'store_sales':np.sum,'Year':np.min,'Month':np.min})\ntemp=temp.reset_index(drop=True)\nsns.set(style=\"whitegrid\", color_codes=True)\n# temp\nplt.figure(figsize=(15,8))\nplt.plot(range(1,13),temp.iloc[0:12,0],label=\"2013\")\nplt.plot(range(1,13),temp.iloc[12:24,0],label=\"2014\")\nplt.plot(range(1,13),temp.iloc[24:36,0],label=\"2015\")\nplt.plot(range(1,13),temp.iloc[36:48,0],label=\"2015\")\nplt.ylabel('Overall Sales', fontsize=12)\nplt.xlabel('Month', fontsize=12)\nplt.title('Monthly sales variation', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(['2013', '2014', '2015', '2016'], loc='upper left')\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 创建绘图\nplt.figure(figsize=(15,12))\n\n#商店在不同集群中的分布\nplt.subplot(221)\ntemp = stores['cluster'].value_counts().reset_index()\ntemp.columns = ['cluster', 'count']\nsns.barplot(x='cluster', y='count', data=temp, color=color[5])\nplt.ylabel('Count of stores', fontsize=12)\nplt.xlabel('Cluster', fontsize=12)\nplt.title('Store distribution across clusters', fontsize=15)\n\n#商店在不同类型中的分布\nplt.subplot(222)\ntemp = stores['type'].value_counts().reset_index()\ntemp.columns = ['type', 'count']\nsns.barplot(x='type', y='count', data=temp, color=color[7])\nplt.ylabel('Count of stores', fontsize=12)\nplt.xlabel('Type of store', fontsize=12)\nplt.title('Store distribution across store types', fontsize=15)\n\n#商店在不同州中的分布\nplt.subplot(223)\ntemp = stores['state'].value_counts().reset_index()\ntemp.columns = ['state', 'count']\nsns.barplot(x='state', y='count', data=temp, color=color[8])\nplt.ylabel('Count of stores', fontsize=12)\nplt.xlabel('State', fontsize=12)\nplt.title('Store distribution across states', fontsize=15)\nplt.xticks(rotation='vertical')\n\n#商店在不同城市中的分布\nplt.subplot(224)\ntemp = stores['city'].value_counts().reset_index()\ntemp.columns = ['city', 'count']\nsns.barplot(x='city', y='count', data=temp, color=color[9])\nplt.ylabel('Count of stores', fontsize=12)\nplt.xlabel('City', fontsize=12)\nplt.title('Store distribution across cities', fontsize=15)\nplt.xticks(rotation='vertical')\n\nplt.tight_layout()\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sale_store_level=sale_store_level.iloc[:,0:2]\n#print(sale_store_level)\nmerge=pd.merge(sale_store_level,stores,how='left',on='store_nbr')\n#temp\n# 创建绘图\nplt.figure(figsize=(15,12))\n\n# 1. 跨门店集群的累计销售\nplt.subplot(221)\ntemp = merge.groupby(['cluster'])['store_sales'].sum().reset_index()\nsns.barplot(x='cluster', y='store_sales', data=temp, color=color[5])\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Cluster', fontsize=12)\nplt.title('Cumulative sales across store clusters', fontsize=15)\n\n# 2. 不同商店类型的累计销售额\nplt.subplot(222)\ntemp = merge.groupby(['type'])['store_sales'].sum().reset_index()\nsns.barplot(x='type', y='store_sales', data=temp, color=color[7])\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Type of store', fontsize=12)\nplt.title('Cumulative sales across store types', fontsize=15)\n\n# 3. 各州累计销量\nplt.subplot(223)\ntemp = merge.groupby(['state'])['store_sales'].sum().reset_index()\nsns.barplot(x='state', y='store_sales', data=temp, color=color[8])\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('State', fontsize=12)\nplt.title('Cumulative sales across states', fontsize=15)\nplt.xticks(rotation='vertical')\n\n# 4. 城市累计销量\nplt.subplot(224)\ntemp = merge.groupby(['city'])['store_sales'].sum().reset_index()\nsns.barplot(x='city', y='store_sales', data=temp, color=color[9])\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('City', fontsize=12)\nplt.title('Cumulative sales across cities', fontsize=15)\nplt.xticks(rotation='vertical')\n\nplt.tight_layout()\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"有趣的是，只有4家商店的14号商店集群的销售额最高。","metadata":{}},{"cell_type":"code","source":"sale_store_level=sale_store_level.iloc[:,0:2]\nmerge=pd.merge(sale_store_level,stores,how='left',on='store_nbr')\n\nplt.figure(figsize=(15,12))\n#row col plotnumber - 121\nplt.subplot(221)\n#plot\nsns.boxplot(x='cluster', y=\"store_sales\", data=merge)\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Cluster', fontsize=12)\nplt.title('Variation across store clusters', fontsize=15)\n\nplt.subplot(222)\n# sale of stores for each type \nsns.boxplot(x='type', y=\"store_sales\", data=merge)\nplt.ylabel('sales', fontsize=12)\nplt.xlabel('Type of store', fontsize=12)\nplt.title('Variation across store types', fontsize=15)\n\nplt.subplot(223)\n# sale of stores for each type \nsns.boxplot(x='state', y=\"store_sales\", data=merge)\nplt.ylabel('sales', fontsize=12)\nplt.xlabel('state', fontsize=12)\nplt.title('Variation across states', fontsize=15)\nplt.xticks(rotation='vertical')\n\nplt.subplot(224)\n# sale of stores for city\nsns.boxplot(x='city', y=\"store_sales\", data=merge)\nplt.ylabel('sales', fontsize=12)\nplt.xlabel('City', fontsize=12)\nplt.title('Variation across cities', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#transactions\n# month over month sales\ntrans['date']=pd.to_datetime(trans['date'])\n#print(trans.dtypes)\ntemp=trans.groupby(['date']).aggregate({'store_nbr':'count','transactions':np.sum})\ntemp=temp.reset_index()\ntemp_2013=temp[temp['date'].dt.year==2013].reset_index(drop=True)\ntemp_2014=temp[temp['date'].dt.year==2014].reset_index(drop=True)\ntemp_2015=temp[temp['date'].dt.year==2015].reset_index(drop=True)\ntemp_2016=temp[temp['date'].dt.year==2016].reset_index(drop=True)\ntemp_2017=temp[temp['date'].dt.year==2017].reset_index(drop=True)\n\n#print(temp)\nsns.set(style=\"whitegrid\", color_codes=True)\n# temp\nplt.figure(figsize=(15,14))\nplt.subplot(211)\nplt.plot(temp_2013['date'],temp_2013.iloc[:,1],label=\"2013\")\nplt.plot(temp_2014['date'],temp_2014.iloc[:,1],label=\"2014\")\nplt.plot(temp_2015['date'],temp_2015.iloc[:,1],label=\"2015\")\nplt.plot(temp_2016['date'],temp_2016.iloc[:,1],label=\"2016\")\nplt.plot(temp_2017['date'],temp_2017.iloc[:,1],label=\"2017\")\nplt.ylabel('Number of stores open', fontsize=12)\nplt.xlabel('Time', fontsize=12)\nplt.title('Number of stores open', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(['2013', '2014', '2015', '2016'], loc='lower right')\n\nplt.subplot(212)\nplt.plot(temp_2013.index,temp_2013.iloc[:,1],label=\"2013\")\nplt.plot(temp_2014.index,temp_2014.iloc[:,1],label=\"2014\")\nplt.plot(temp_2015.index,temp_2015.iloc[:,1],label=\"2015\")\nplt.plot(temp_2016.index,temp_2016.iloc[:,1],label=\"2016\")\nplt.plot(temp_2017.index,temp_2017.iloc[:,1],label=\"2017\")\n\n\nplt.ylabel('Number of stores open', fontsize=12)\nplt.xlabel('Day of year', fontsize=12)\nplt.title('Number of stores open', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(['2013', '2014', '2015', '2016'], loc='lower right')\nplt.show()\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp=trans.groupby(['store_nbr']).agg({'date':[np.min,np.max]}).reset_index()\ntemp['store_age']=temp['date']['amax']-temp['date']['amin']\ntemp['open_year']=temp['date']['amin'].dt.year\ndata=temp['open_year'].value_counts()\n#print(data)\nplt.figure(figsize=(12,4))\nsns.barplot(x=data.index,y=data.values, alpha=0.8, color=color[0])\nplt.ylabel('Stores', fontsize=12)\nplt.xlabel('Store opening Year', fontsize=12)\nplt.title('When were the stores started?', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"store_items=pd.merge(sale_store_item_level,items,on='item_nbr')\nstore_items=pd.merge(store_items,stores,on='store_nbr')\nstore_items['item_sales']=store_items['item_sales']\n\n#item\n# top selling items by store type 商店类型的最畅销商品编号\ntop_items_by_type=store_items.groupby(['type','item_nbr'])['item_sales'].sum()\ntop_items_by_type=top_items_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n\n#get top 5\ntop_items_by_type=top_items_by_type.groupby(['type']).head(5)\n\n\n#class\n# top selling item class by store type 按商店类型划分的最畅销商品分类代码\ntop_class_by_type=store_items.groupby(['type','class'])['item_sales'].sum()\ntop_class_by_type=top_class_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n\n#get top 5\ntop_class_by_type=top_class_by_type.groupby(['type']).head(5)\n\n\n#family\n# top selling item family by store type 商店类型的最畅销商品所属类别\ntop_family_by_type=store_items.groupby(['type','family'])['item_sales'].sum()\ntop_family_by_type=top_family_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\n\n#get top 5\ntop_family_by_type=top_family_by_type.groupby(['type']).head(5)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#不同类型的商店最畅销的5种商品类别\nplt.figure(figsize=(12,5))\n\nx=top_family_by_type.pivot(index='type',columns='family')\nx.plot.bar(stacked=True,figsize=(12,5))\ny=x.columns.droplevel(0).values\n#print(y)\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Top 5 item families', fontsize=12)\nplt.title('Top 5 item families across different store types', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(y)\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#不同类型的商店最畅销的5种商品分类代码\nplt.figure(figsize=(12,5))\nx=top_class_by_type.pivot(index='type',columns='class')\nx.plot.bar(stacked=True,figsize=(12,5))\ny=x.columns.droplevel(0).values\n#print(y)\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Top 5 item classes', fontsize=12)\nplt.title('Top 5 item classes across different store types', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(y)\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##不同类型的商店最畅销的5种商品编号\nplt.figure(figsize=(12,5))\nx=top_items_by_type.pivot(index='type',columns='item_nbr')\nx.plot.bar(stacked=True,figsize=(12,5))\ny=x.columns.droplevel(0).values\n#print(y)\nplt.ylabel('Sales', fontsize=12)\nplt.xlabel('Top 5 items ', fontsize=12)\nplt.title('Top 5 items across different store types', fontsize=15)\nplt.xticks(rotation='vertical')\nplt.legend(y)\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#可视化不同类型type和所属类别之间的销售额情况\ntop_family_by_type=store_items.groupby(['type','family'])['item_sales'].sum()\ntop_family_by_type=top_family_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\nx=top_family_by_type.pivot(index='family',columns='type')\ncm = sns.light_palette(\"orange\", as_cmap=True)\nx = x.style.background_gradient(cmap=cm)\nx","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#可视化不同类型（type）和分类代码（class）之间的商品销售总\ntop_class_by_type=store_items.groupby(['type','class'])['item_sales'].sum()\ntop_class_by_type=top_class_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\ntop_class_by_type=top_class_by_type.groupby(['class']).head(20)\nx=top_class_by_type.pivot(index='class',columns='type')\nx['total']=x.sum(axis=1)\nx=x.sort_values('total',ascending=False)\ndel(x['total'])\nx=x.head(20)\ncm = sns.light_palette(\"gray\", as_cmap=True)\nx = x.style.background_gradient(cmap=cm,axis=1)\nx","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_items_by_type=store_items.groupby(['type','item_nbr'])['item_sales'].sum()\ntop_items_by_type=top_items_by_type.reset_index().sort_values(['type','item_sales'],ascending=[True,False])\ntop_items_by_type=top_items_by_type.groupby(['item_nbr']).head(20)\n#print(top_items_by_type)\nx=top_items_by_type.pivot(index='item_nbr',columns='type')\nx['total']=x.sum(axis=1)\nx=x.sort_values('total',ascending=False)\ndel(x['total'])\nx=x.head(30)\ncm = sns.light_palette(\"green\", as_cmap=True)\nx = x.style.background_gradient(cmap=cm,axis=1)\nx","metadata":{},"execution_count":null,"outputs":[]}]}