# %% [python] cell-025b1bc6d0ab
import pyodide, os, requests, js, json, micropip, xlrd, pandas as pd
await micropip.install('et_xmlfile-2.0.0-py3-none-any.whl')
await micropip.install('openpyxl-3.1.5-py2.py3-none-any.whl')
await micropip.install('shuyouqi-0.0.0-py3-none-any.whl')
from shuyouqi import profiling
# %% [prompt] cell-08c16d096eb4
Excel文件中的“销量”字段包含多种格式的数据，诸如“100”、“100+”、“200”、“1300+”以及“5万”等。请编写Python代码，执行以下任务：
    将“销量”字段中的所有数据转换为整数类型：
        移除末尾的“+”字符；
        将“5万”这样的格式转换为实际的数值（例如将“5万”转化为50000）。
    将“宝贝ID”字段的类型转换为字符串。
    将转换后的结果保存并覆盖原Excel文件。
请确保在处理过程中正确地识别并处理各种格式的数据。
# %% [python] cell-a0c85df413a1
# 读取原始Excel文件
input_file = '/RPA_20250426_161959.xlsx'  # 输入文件路径
import pandas as pd
import re

def convert_value(value):
    # 将输入转换为字符串，以便处理
    value_str = str(value)
    
    # 使用正则表达式匹配模式
    match = re.match(r"(\d+)([万])?", value_str)
    
    if match:
        number = int(match.group(1))
        unit = match.group(2)
        
        # 根据单位进行相应的转换
        if unit == '万':
            return number * 10000
        else:
            # 如果没有单位或者有其他情况（比如末尾带+），直接处理
            return int(re.sub(r"[^\d]", "", value_str))
    else:
        # 如果不匹配任何已知模式，尝试直接转换为整数
        return int(value)

# 读取原始Excel文件
output_file = input_file  # 输出文件路径

# 假设目标列名为 'YourColumnName'
df = pd.read_excel(input_file)

# 处理目标列，应用convert_value函数
df['销量'] = df['销量'].apply(convert_value)

df['宝贝ID'] = df['宝贝ID'].astype(str)

# 将结果写入新的Excel文件
df.to_excel(output_file, index=False)
# %% [plaintext] cell-c59b25cab165
《淘宝天猫店铺内宝贝列表》
rpa_20250428_140934
rpa_20250427_152444
rpa_20250426_161959
# %% [prompt] cell-071e5fb2e392
你是精通DuckDB SQL的分析师，你的任务是编写SQL语句，将结构相同的若干张表（表名参见《淘宝天猫店铺内宝贝列表》）按照相同的字段“宝贝ID”连接在一起。只返回那些在两个表中都有匹配的行，并且保留“标题”字段。对于每个表的“销量”字段，分别命名为“销量1”、“销量2”、“销量3”等等。最终，将连接的结果保存到一个临时表“itemlist”中（如果该表已存在，则先删除该表）。
请确保在编写查询时，遵循以下要求：
    表格的结构相同，且可以通过字段“宝贝ID”进行连接。
    在返回结果中，每一张表的“销量”字段分别重命名为“销量1”、“销量2”、“销量3”等等。
    在连接的结果中，确保只包含那些在两个表中都有匹配的行。
    如果临时表“itemlist”已存在，首先删除该表，然后重新创建并插入数据。
    请确保SQL语句在执行时，不会引发性能瓶颈，且能够处理较大数据量。
# %% [sql] cell-bcb4eb3031f4
-- 删除已存在的临时表
DROP TABLE IF EXISTS itemlist;

-- 创建包含三表交集且保留销量别名的临时表
CREATE TEMP TABLE itemlist AS
SELECT
    t1.宝贝ID,
    t1.标题,
    t1.销量 AS 销量1,
    t2.销量 AS 销量2,
    t3.销量 AS 销量3
FROM rpa_20250426_161959 t1
INNER JOIN rpa_20250427_152444 t2 
    ON t1.宝贝ID = t2.宝贝ID
INNER JOIN rpa_20250428_140934 t3 
    ON t1.宝贝ID = t3.宝贝ID;

DESCRIBE itemlist;
# %% [plaintext] cell-bf6f1d23e028
[
  {"column_name": "宝贝ID", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "标题", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量1", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量2", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量3", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null}
]
# %% [prompt] cell-52cbb153fa2
你是精通DuckDB SQL的分析师，你的任务是编写SQL语句，将表格 itemlist 包含多个“销量”字段（如“销量1”、“销量2”、“销量3”等，描述参看《资料》）筛选，从中去掉几个“销量”字段没有增长的记录,去掉销量大于等于1000的记录，结果另存为临时表itemlistgood中（如果该表已存在，则先删除该表）。
# %% [sql] cell-af5b37a78962
DROP TABLE IF EXISTS itemlistgood;

-- 创建包含三表交集且保留销量别名的临时表
CREATE TEMP TABLE itemlistgood AS
SELECT 宝贝ID, 标题, 销量1, 销量2, 销量3
    FROM itemlist
    WHERE 销量1 >= 销量2 AND 销量2 >= 销量3 AND 销量1 < 1000 AND 销量2 < 1000 AND 销量3 < 1000 ORDER BY 销量1 - 销量2 + 销量2 - 销量3 DESC  LIMIT 30;

DESCRIBE itemlistgood;
# %% [plaintext] cell-d52d98b3f2ed
[
  {"column_name": "宝贝ID", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "标题", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量1", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量2", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量3", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null}
]
# %% [prompt] cell-f3e49c808934
你是精通DuckDB SQL的分析师，你的任务是编写SQL语句，将表格 itemlistgood 从宽结构转换为长结构。表格 itemlistgood 包含多个“销量”字段（如“销量1”、“销量2”、“销量3”等），描述参看《资料》，然后转化为包含以下字段的长结构表，结果保存在一个临时表itemsold中（如果该表已存在，则先删除该表）。
    宝贝ID：保留原表中的“宝贝ID”字段。
    标题：保留原表中的“标题”字段。
    几天前：根据不同“销量”字段的顺序，为每个“销量”分配相应的值：
        对于“销量1”，值为0。
        对于“销量2”，值为-1。
        对于“销量3”，值为-2。
    销量：将每个“销量”字段的值放入新的“销量”字段中。
确保转换后的结果符合以下要求：
    如果临时表“itemsold”已存在，首先删除该表，然后重新创建并插入数据。
    每个“销量”字段都对应一个独立的记录，且“几天前”字段反映销量的时间差。
    生成的长结构表应包含所有转换后的记录，并保留原始“宝贝ID”和“标题”信息。
    请确保SQL语句在执行时，不会引发性能瓶颈，且能够处理较大数据量。
# %% [sql] cell-a17bd6a17d71
-- 删除已存在的临时表
DROP TABLE IF EXISTS itemsold;

-- 创建临时表并通过UNPIVOT转换结构
CREATE TEMP TABLE itemsold AS
SELECT 
    宝贝ID,
    标题,
    CASE 
        WHEN sales_column = '销量1' THEN 0
        WHEN sales_column = '销量2' THEN -1
        WHEN sales_column = '销量3' THEN -2
    END AS 几天前,
    sales_value AS 销量
FROM itemlistgood
UNPIVOT (
    sales_value 
    FOR sales_column IN (销量1, 销量2, 销量3)
);

DESCRIBE itemsold;
# %% [plaintext] cell-baecde1493f2
[
  {"column_name": "宝贝ID", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "标题", "column_type": "VARCHAR", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "几天前", "column_type": "INTEGER", "null": "YES", "key": null, "default": null, "extra": null},
  {"column_name": "销量", "column_type": "DOUBLE", "null": "YES", "key": null, "default": null, "extra": null}
]
# %% [prompt] cell-04ac192dfbce
你是精通Vega-Lite的可视化专家，你的任务是生成Vega-Lite JSON代码，用于绘制折线图。具体要求如下：
    X轴：使用“几天前”字段。
    Y轴：使用“销量”字段。
    颜色：不同颜色的折线表示不同的“宝贝ID”。
    数据源：表格名为“itemsold”，包含“宝贝ID”、“几天前”和“销量”字段，描述参看《资料》。
生成的Vega-Lite JSON代码应该能够清晰地展示每个宝贝ID随时间变化的销量趋势，使用不同颜色区分不同的宝贝ID。在工具提示中，显示一个由“https://item.taobao.com/item.htm?id=”加上“宝贝ID”两部分合并的宝贝链接。
# %% [vegalite] cell-caee721bda41
{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "多商品销量趋势折线图",
  "data": {"name": "itemsold"},
  "width": 800,
  "height": 500,
  "mark": {
    "type": "line",
    "interpolate": "monotone",
    "strokeWidth": 4
  },
  "transform": [{
    "calculate": "'https://item.taobao.com/item.htm?id=' + datum.宝贝ID",
    "as": "宝贝链接"
  }],
  "encoding": {
    "x": {
      "field": "几天前",
      "type": "quantitative",
      "title": "相对天数（0=当天）",
      "scale": {"domain": [-2.5, 0.5]},  // 扩展显示范围
      "axis": {
        "tickCount": 3,
        "values": [-2, -1, 0],
        "labelExpr": "datum.value + '天前'"
      }
    },
    "y": {
      "field": "销量",
      "type": "quantitative",
      "title": "销量（单位）",
      "axis": {"grid": false}
    },
    "color": {
      "field": "宝贝ID",
      "type": "nominal",
      "title": "商品ID",
      "scale": {"scheme": "category20"},  // 20色分类方案
      "legend": {
        "titleFontSize": 14,
        "labelFontSize": 12,
        "orient": "right",
        "columns": 2
      }
    },
    "tooltip": [
      {"field": "宝贝ID", "type": "nominal", "title": "商品"},
      {"field": "标题", "type": "nominal", "title": "名称"},
      {"field": "几天前", "type": "quantitative", "title": "天数", "format": "+,d"},
      {"field": "销量", "type": "quantitative", "title": "销量", "format": ",.0f"},
      {
       "field": "宝贝链接", 
       "type": "nominal",        
       "title": "宝贝链接"
      }
    ]
  },
  "config": {
    "view": {"stroke": "transparent"},
    "axis": {"domainWidth": 1},
    "font": "Microsoft YaHei"  // 中文字体适配
  }
}