{"cells":[{"cell_type":"code","id":"initial_id","metadata":{"collapsed":true,"id":"initial_id","executionInfo":{"status":"error","timestamp":1748880567369,"user_tz":-480,"elapsed":1256,"user":{"displayName":"Yihan Wang","userId":"09841618405917660021"}},"outputId":"0d8829df-87b3-48a7-d13f-d9a39138c108","colab":{"base_uri":"https://localhost:8080/","height":379}},"source":["from kaggle.api.kaggle_api_extended import KaggleApi\n","import pandas as pd\n","import os\n","\n","# 初始化 Kaggle API\n","api = KaggleApi()\n","api.authenticate()\n","\n","# 设置下载路径\n","download_dir = \"./data\"\n","os.makedirs(download_dir, exist_ok=True)\n","\n","# 下载数据集\n","api.dataset_download_files(\n","    \"vivek468/superstore-dataset-final\",\n","    path=\"./data\",\n","    unzip=True\n",")\n","\n","csv_file = \"Sample - Superstore.csv\"\n","csv_path = os.path.join(download_dir,csv_file)\n","\n","# 读取数据集\n","df = pd.read_csv(csv_path,encoding='ISO-8859-1')"],"outputs":[{"output_type":"error","ename":"OSError","evalue":"Could not find kaggle.json. Make sure it's located in /root/.config/kaggle. Or use the environment method. See setup instructions at https://github.com/Kaggle/kaggle-api/","traceback":["\u001b[0;31m---------------------------------------------------------------------------\u001b[0m","\u001b[0;31mOSError\u001b[0m                                   Traceback (most recent call last)","\u001b[0;32m<ipython-input-1-881c45edc264>\u001b[0m in \u001b[0;36m<cell line: 0>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mkaggle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkaggle_api_extended\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mKaggleApi\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;31m# 初始化 Kaggle API\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.11/dist-packages/kaggle/__init__.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mapi\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mKaggleApi\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mauthenticate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m","\u001b[0;32m/usr/local/lib/python3.11/dist-packages/kaggle/api/kaggle_api_extended.py\u001b[0m in \u001b[0;36mauthenticate\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    432\u001b[0m         \u001b[0;32mreturn\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    433\u001b[0m       \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 434\u001b[0;31m         raise IOError('Could not find {}. Make sure it\\'s located in'\n\u001b[0m\u001b[1;32m    435\u001b[0m                       \u001b[0;34m' {}. Or use the environment method. See setup'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    436\u001b[0m                       \u001b[0;34m' instructions at'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;31mOSError\u001b[0m: Could not find kaggle.json. Make sure it's located in /root/.config/kaggle. Or use the environment method. See setup instructions at https://github.com/Kaggle/kaggle-api/"]}],"execution_count":1},{"metadata":{"id":"184337b1c6725701"},"cell_type":"code","source":["df.head()"],"id":"184337b1c6725701","outputs":[],"execution_count":null},{"metadata":{"id":"8a2cdc69dcb355d9"},"cell_type":"code","source":["def explore_data(df):\n","    print(\"数据基本信息如下：\")\n","    print(\"字段名称:\\n\", df.columns.tolist())\n","    print(\"\\n数据类型:\\n\", df.dtypes)\n","\n","    print(\"\\n数据样本如下：\")\n","    display(df.head(3))\n","    print(\"\\n数据维度:\", df.shape)\n","\n","    print(\"\\n缺失值分析如下：\")\n","    missing = df.isnull().sum()\n","    print(\"缺失值数量:\\n\", missing[missing > 0])\n","    print(\"\\n缺失值百分比:\\n\", round(df.isnull().mean()[missing > 0] * 100, 2))\n","\n","explore_data(df)"],"id":"8a2cdc69dcb355d9","outputs":[],"execution_count":null},{"metadata":{"id":"d0ba6c20960256a2"},"cell_type":"code","source":["# 统计完全重复的行数\n","duplicate_rows = df[df.duplicated(keep=False)]\n","print(f\"完全重复的行数: {len(duplicate_rows)}\")"],"id":"d0ba6c20960256a2","outputs":[],"execution_count":null},{"metadata":{"id":"18fea2509a258a0e"},"cell_type":"code","source":["df['Order Date'] = pd.to_datetime(df['Order Date'], infer_datetime_format=True, errors='coerce')\n","df['Ship Date'] = pd.to_datetime(df['Ship Date'], infer_datetime_format=True, errors='coerce')\n","\n","# 检查转换后的数据类型\n","print(\"字段类型:\\n\", df[['Order Date', 'Ship Date']].dtypes)"],"id":"18fea2509a258a0e","outputs":[],"execution_count":null},{"metadata":{"id":"c2d9c736ba770ea7"},"cell_type":"code","source":["stats = {\n","    'Sales': {\n","        '均值': df['Sales'].mean(),\n","        '中位数': df['Sales'].median(),\n","        '标准差': df['Sales'].std(),\n","        '峰度': df['Sales'].kurtosis(),\n","        '偏度': df['Sales'].skew()\n","    },\n","    'Profit': {\n","        '均值': df['Profit'].mean(),\n","        '中位数': df['Profit'].median(),\n","        '标准差': df['Profit'].std(),\n","        '峰度': df['Profit'].kurtosis(),\n","        '偏度': df['Profit'].skew()\n","    }\n","}\n","print(stats)"],"id":"c2d9c736ba770ea7","outputs":[],"execution_count":null},{"metadata":{"id":"d39d926283dd6eda"},"cell_type":"code","source":["# 按产品和地区分组分析\n","grouped = df.groupby([\"Category\",\"Region\"]).agg({\n","    \"Sales\":[\"sum\",\"mean\",\"median\"],\n","    \"Profit\":[\"sum\",\"mean\",\"median\",lambda x:(x<0).mean()]\n","}).round(2)\n","\n","# 重新命名列名\n","grouped.columns = [\"销售额总和\",\"平均销售额\",\"销售额中位数\",\n","                   \"利润总和\",\"平均利润\",\"利润中位数\",\"亏损比例\"]\n","\n","# 输出结果\n","print(\"按产品和地区分组的销售表现如下：\")\n","print(grouped.sort_values(by=\"利润总和\",ascending=False))\n","\n","# 计算利润率\n","grouped[\"利润率\"] = (grouped[\"利润总和\"]/grouped[\"销售额总和\"]).round(3)\n","print(\"\\n利润率排名为：\")\n","print(grouped[\"利润率\"].sort_values(ascending=False))"],"id":"d39d926283dd6eda","outputs":[],"execution_count":null},{"metadata":{"id":"4b4915a2f6f8488"},"cell_type":"code","source":["# 按年趋势分析\n","yearly_trend = df.groupby(df['Order Date'].dt.year).agg({\n","    \"Sales\":'sum',\n","    \"Profit\":'sum',\n","    \"Order ID\":'count'\n","}).rename(columns={\"Order ID\":\"Order Count\"})\n","\n","# 计算年平均利润率\n","yearly_trend[\"Profit Margin\"] = yearly_trend[\"Profit\"] / yearly_trend[\"Sales\"]\n","\n","# 按月趋势分析\n","monthly_trend = df.groupby([\n","    df['Order Date'].dt.year.rename(\"Year\"),\n","    df[\"Order Date\"].dt.month.rename(\"Month\")\n","]).agg({\n","    'Sales':\"sum\",\n","    \"Profit\":'sum',\n","}).reset_index()\n","\n","# 创建年月标签\n","monthly_trend['Year-Month'] = monthly_trend['Year'].astype(str) + '-' + monthly_trend['Month'].astype(str).str.zfill(2)\n","\n","# 输出结果\n","print(\"年度趋势如下：\")\n","print(yearly_trend[[\"Sales\",\"Profit\",\"Profit Margin\"]].round(2))\n","print(\"\\n月度趋势如下：\")\n","print(monthly_trend.tail(12).set_index(\"Year-Month\")[[\"Sales\",\"Profit\"]].round(2))"],"id":"4b4915a2f6f8488","outputs":[],"execution_count":null},{"metadata":{"id":"e28bb0ac8a555e62"},"cell_type":"code","source":["# 按产品聚合统计利润\n","product_profit = df.groupby(\"Product Name\").agg({\n","    \"Profit\":['sum','mean','count'],\n","    \"Sales\":\"sum\"\n","}).sort_values(('Profit','sum'),ascending=False)\n","\n","product_profit.columns = ['Total Profit', 'Avg Profit', 'Order Count', 'Total Sales']\n","\n","# 选择利润前十的产品\n","top_products = product_profit.head(10).round(2)\n","print(\"利润最高的前十个产品有：\")\n","print(top_products)"],"id":"e28bb0ac8a555e62","outputs":[],"execution_count":null},{"metadata":{"id":"8f1d7ad969562639"},"cell_type":"code","source":["import matplotlib.pyplot as plt\n","\n","plt.figure(figsize=(14, 8))\n","\n","# --- 年度趋势 ---\n","plt.subplot(2, 1, 1)\n","# 销售额柱状图\n","plt.bar(yearly_trend.index,yearly_trend['Sales'],\n","        width=0.6, alpha=0.7, label='Sales')\n","# 利润折线图（右轴）\n","plt.twinx()\n","plt.plot(yearly_trend.index, yearly_trend['Profit'],\n","         'r-o', linewidth=2, markersize=8, label='Profit')\n","plt.title('Annual Sales & Profit Trend')\n","plt.grid(ls=':')\n","plt.legend()\n","\n","# --- 月度趋势 ---\n","plt.subplot(2, 1, 2)\n","# 销售额柱状图\n","plt.bar(monthly_trend['Year-Month'], monthly_trend['Sales'],\n","        width=0.8, alpha=0.7, label='Sales')\n","# 利润折线图（右轴）\n","plt.twinx()\n","plt.plot(monthly_trend['Year-Month'], monthly_trend['Profit'],\n","         'r-o', linewidth=2, markersize=5, label='Profit')\n","\n","# 优化横轴\n","ax = plt.gca()  # 获取当前轴对象\n","ax.set_xticks(monthly_trend['Year-Month'][::3])  # 每3个月显示一个刻度\n","\n","plt.title('Monthly Sales & Profit Trend')\n","plt.xticks(rotation=45, ha='right')\n","plt.grid(ls=':')\n","plt.legend()\n","\n","plt.tight_layout()\n","plt.show()"],"id":"8f1d7ad969562639","outputs":[],"execution_count":null},{"metadata":{"id":"c88cc5dd53dd7219"},"cell_type":"code","source":["import matplotlib.pyplot as plt\n","\n","# 准备数据\n","category_sales = df.groupby('Category')['Sales'].sum().sort_values(ascending=False)\n","\n","# 创建图形\n","plt.figure(figsize=(10, 8))\n","\n","# 设置颜色\n","colors = ['#1F77B4', '#5D9BFF', '#A5C8FF']\n","explode = (0.2, 0, 0)\n","\n","# 绘制饼图\n","plt.pie(category_sales,\n","        labels=category_sales.index,\n","        autopct='%1.1f%%',\n","        startangle=90,\n","        colors=colors,\n","        explode=explode,\n","        textprops={'fontsize': 12})\n","\n","plt.title('Sales Distribution by Product Category', pad=20)\n","\n","plt.legend(title=\"Categories\",\n","           loc=\"center left\",\n","           bbox_to_anchor=(1, 0.5))\n","\n","plt.tight_layout()\n","plt.show()"],"id":"c88cc5dd53dd7219","outputs":[],"execution_count":null},{"metadata":{"id":"52de664b2b1ebc68"},"cell_type":"code","source":["import matplotlib.pyplot as plt\n","import numpy as np\n","\n","# 按地区聚合数据\n","region_data = df.groupby('Region').agg({\n","    'Sales': 'sum',\n","    'Profit': 'sum'\n","}).sort_values('Sales', ascending=False)\n","\n","plt.figure(figsize=(12, 6))\n","\n","# 设置位置和宽度\n","x = np.arange(len(region_data.index))\n","width = 0.35\n","\n","# 销售额柱状图（蓝色）\n","plt.bar(x - width/2, region_data['Sales'], width,\n","        label='Sales', color='#1F77B4', alpha=0.7)\n","\n","# 利润柱状图（红色）\n","plt.bar(x + width/2, region_data['Profit'], width,\n","        label='Profit', color='#FF7F0E', alpha=0.7)\n","\n","plt.xticks(x, region_data.index)\n","plt.title('Sales & Profit by Region', pad=20)\n","plt.xlabel('Region')\n","plt.ylabel('Amount')\n","plt.grid(axis='y', ls=':')\n","plt.legend()\n","\n","for i in x:\n","    plt.text(i - width/2, region_data['Sales'][i] + 1000,\n","             f\"{region_data['Sales'][i]/1000:.1f}K\",\n","             ha='center', fontsize=9)\n","    plt.text(i + width/2, region_data['Profit'][i] + 1000,\n","             f\"{region_data['Profit'][i]/1000:.1f}K\",\n","             ha='center', fontsize=9)\n","\n","plt.tight_layout()\n","plt.show()"],"id":"52de664b2b1ebc68","outputs":[],"execution_count":null},{"metadata":{"id":"22c16468ee933e7d"},"cell_type":"code","source":["# 设置图形样式\n","plt.figure(figsize=(12, 8))\n","plt.style.use('seaborn-v0_8')\n","\n","# 绘制散点图\n","scatter = plt.scatter(\n","    x=df['Sales'],\n","    y=df['Profit'],\n","    c=df['Quantity'],\n","    cmap='viridis',\n","    alpha=0.7,\n","    s=50\n",")\n","\n","z = np.polyfit(df['Sales'], df['Profit'], 1)\n","p = np.poly1d(z)\n","plt.plot(df['Sales'], p(df['Sales']), \"r--\", linewidth=2)\n","\n","plt.title('Sales vs Profit Correlation', fontsize=16, pad=20)\n","plt.xlabel('Sales Amount', fontsize=12)\n","plt.ylabel('Profit', fontsize=12)\n","\n","cbar = plt.colorbar(scatter)\n","cbar.set_label('Quantity Sold', rotation=270, labelpad=15)\n","\n","plt.grid(True, linestyle='--', alpha=0.6)\n","plt.xlim(left=0)\n","plt.ylim(bottom=min(df['Profit'])*1.1, top=max(df['Profit'])*1.1)\n","\n","plt.tight_layout()\n","plt.show()"],"id":"22c16468ee933e7d","outputs":[],"execution_count":null},{"metadata":{"id":"88b26292e971b1d9"},"cell_type":"code","source":["# --- 1. 按子类别划分的销售额和利润 ---\n","# 按子类别分组数据并计算销售额和利润的总和\n","subcategory_performance = df.groupby('Sub-Category').agg(\n","    Sales=('Sales', 'sum'),\n","    Profit=('Profit', 'sum')\n",").sort_values(by='Sales', ascending=False)\n","\n","plt.figure(figsize=(14, 10))\n","\n","# 创建按子类别划分的销售额柱状图\n","plt.subplot(2, 1, 1) # 2 行，1 列，第一个图\n","sns.barplot(x=subcategory_performance.index, y=subcategory_performance['Sales'], palette='Blues_d')\n","plt.title('按子类别划分的总销售额', fontsize=16, pad=20)\n","plt.xlabel('子类别', fontsize=12)\n","plt.ylabel('总销售额', fontsize=12)\n","plt.xticks(rotation=45, ha='right')\n","plt.grid(axis='y', linestyle='--', alpha=0.7)\n","\n","# 创建按子类别划分的利润柱状图\n","plt.subplot(2, 1, 2) # 2 行，1 列，第二个图\n","sns.barplot(x=subcategory_performance.index, y=subcategory_performance['Profit'], palette='Greens_d')\n","plt.title('按子类别划分的总利润', fontsize=16, pad=20)\n","plt.xlabel('子类别', fontsize=12)\n","plt.ylabel('总利润', fontsize=12)\n","plt.xticks(rotation=45, ha='right')\n","plt.grid(axis='y', linestyle='--', alpha=0.7)\n","\n","plt.tight_layout()\n","plt.show()\n","\n","# --- 2. 折扣与利润（按类别着色） ---\n","plt.figure(figsize=(12, 8))\n","sns.scatterplot(\n","    x='Discount',\n","    y='Profit',\n","    hue='Category', # 按类别为点着色\n","    size='Sales', # 按销售额大小调整点的大小以显示量级\n","    sizes=(20, 400), # 点的大小范围\n","    alpha=0.7,\n","    data=df,\n","    palette='viridis'\n",")\n","\n","# 添加回归线\n","sns.regplot(x='Discount', y='Profit', data=df, scatter=False, color='red', line_kws={'linestyle':'--', 'alpha':0.7})\n","\n","plt.title('折扣与利润关系（按类别划分）', fontsize=16, pad=20)\n","plt.xlabel('折扣', fontsize=12)\n","plt.ylabel('利润', fontsize=12)\n","plt.grid(True, linestyle='--', alpha=0.6)\n","plt.legend(title='类别', bbox_to_anchor=(1.05, 1), loc='upper left')\n","plt.tight_layout()\n","plt.show()"],"id":"88b26292e971b1d9","outputs":[],"execution_count":null}],"metadata":{"kernelspec":{"display_name":"Python [conda env:base] *","language":"python","name":"conda-base-py"},"language_info":{"codemirror_mode":{"name":"ipython","version":2},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython2","version":"2.7.6"},"colab":{"provenance":[]}},"nbformat":4,"nbformat_minor":5}