{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用于分析市场指令相关的数据\n",
    "\n",
    "原始数据源: rpa_app_scene_info 表, 连表查询出**编辑**应用指令的数据\n",
    "\n",
    "分析目标:\n",
    "- 应用使用自定义指令的占比数量, 应用使用市场指令的占比数据\n",
    "- 分析高频使用的市场指令, 指令对应用的占比, 指令在总使用条数的占比\n",
    "- 针对 TOP 指令, 找到对应指令的使用热区"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 同步数据至本地\n",
    "- 同步数据库数据\n",
    "- 同步市场指令数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最近编辑应用数据的总条数为：48969\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector\n",
    "import warnings\n",
    "\n",
    "# 忽略特定警告\n",
    "warnings.filterwarnings(\n",
    "    \"ignore\",\n",
    "    category=UserWarning,\n",
    "    message=\"pandas only supports SQLAlchemy connectable\",\n",
    ")\n",
    "\n",
    "\n",
    "db_host = \"sh-cdb-qlnh5ecg.sql.tencentcdb.com\"\n",
    "db_port = \"25944\"\n",
    "db_user = \"root\"\n",
    "db_password = \"VaDS4mrgKoJi\"\n",
    "db_name = \"xbot_market_info\"\n",
    "\n",
    "conn = mysql.connector.connect(\n",
    "    host=db_host, port=db_port, user=db_user, password=db_password, database=db_name\n",
    ")\n",
    "\n",
    "query = \"SELECT * FROM rpa_app_scene_info_dev\"\n",
    "df = pd.read_sql(query, conn)\n",
    "conn.close()\n",
    "print(f\"最近编辑应用数据的总条数为：{df.shape[0]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "官方指令的总条数为：147\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "from typing import List\n",
    "from urllib import parse\n",
    "\n",
    "\n",
    "def get_token(username, password):\n",
    "    \"\"\"\n",
    "    利用网页端获取 token\n",
    "    :param username: 用户名\n",
    "    :param password: 密码\n",
    "    :return: token\n",
    "    \"\"\"\n",
    "    url = \"https://api.winrobot360.com/oauth/token\"\n",
    "    data = {\n",
    "        \"username\": username,\n",
    "        \"password\": password,\n",
    "        \"grant_type\": \"password\",\n",
    "        \"scope\": \"all\",\n",
    "    }\n",
    "\n",
    "    payload = parse.urlencode(data)\n",
    "    headers = {\n",
    "        \"accept\": \"application/json\",\n",
    "        \"authorization\": \"Basic Y29uc29sZTpzVVdpeTZzTmlYZDBlNUxo\",\n",
    "        \"content-type\": \"application/x-www-form-urlencoded;charset=UTF-8\",\n",
    "    }\n",
    "    response = requests.request(\"POST\", url, headers=headers, data=payload).json()\n",
    "\n",
    "    if response.get(\"success\") == \"false\":\n",
    "        raise ValueError(response.get(\"msg\"))\n",
    "    return response.get(\"access_token\")\n",
    "\n",
    "\n",
    "def get_activity_info(token, page=1, size=200):\n",
    "    \"\"\"\n",
    "    获取活动信息列表\n",
    "    :param token: 认证用的 token\n",
    "    :param page: 分页页码，默认为 1\n",
    "    :param size: 分页大小，默认为 10\n",
    "    :return: 所有页的数据列表\n",
    "    \"\"\"\n",
    "    url = \"https://api.winrobot360.com/api/v1/robot/activity/package/list\"\n",
    "    all_data = []\n",
    "    while True:\n",
    "        payload = {\n",
    "            \"source\": \"market\",\n",
    "            \"page\": page,\n",
    "            \"size\": size,\n",
    "            \"groupInfoUuid\": \"22222222-2222-2222-2222-222222222222\",\n",
    "            \"marketUiTag\": \"\",\n",
    "            \"uiaType\": \"pc\",\n",
    "            \"order\": \"desc\",\n",
    "        }\n",
    "        headers = {\n",
    "            \"Authorization\": f\"bearer {token}\",\n",
    "            \"Accept\": \"application/json\",\n",
    "            \"Accept-Encoding\": \"json\",\n",
    "        }\n",
    "        response = requests.get(url, headers=headers, params=payload)\n",
    "        response_json = response.json()\n",
    "        if not response.ok or not response_json.get(\"success\"):\n",
    "            raise ValueError(response_json.get(\"msg\"))\n",
    "        data = response_json.get(\"data\")\n",
    "        all_data.extend(data)\n",
    "        if not data or len(data) < size:\n",
    "            break\n",
    "        page += 1\n",
    "    return all_data\n",
    "\n",
    "\n",
    "username = \"剑心@fckj\"\n",
    "password = \"320753691love\"\n",
    "token = get_token(username=username, password=password)\n",
    "activity_infos = get_activity_info(token)\n",
    "# print(activity_infos)\n",
    "market_activity_detail_data = {}\n",
    "for item in activity_infos:\n",
    "    # print(item.get(\"packageName\"), item.get(\"packageViewName\"))\n",
    "    market_activity_detail_data[item.get(\"packageName\")] = item.get(\"packageViewName\")\n",
    "\n",
    "print(f\"官方指令的总条数为：{len(market_activity_detail_data)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 自定义指令占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最近编辑应用总数:           48969\n",
      "应用未使用自定义指令总数:       28800\n",
      "应用使用自定义指令总数:        20169\n",
      "应用使用自定义指令占比:        41.19%\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "df[\"scene_info\"]\n",
    "df[\"extension_names\"] = df[\"scene_info\"].apply(\n",
    "    lambda x: json.loads(x).get(\"extension_names\")\n",
    ")\n",
    "df[\"extension_names\"]\n",
    "empty_count = df['extension_names'].apply(lambda x: len(x) == 0).sum()\n",
    "non_empty_count = df['extension_names'].apply(lambda x: len(x) > 0).sum()\n",
    "\n",
    "\n",
    "print(f\"{'最近编辑应用总数:':<20}{len(df)}\")\n",
    "print(f\"{'应用未使用自定义指令总数:':<20}{empty_count}\")\n",
    "print(f\"{'应用使用自定义指令总数:':<20}{non_empty_count}\")\n",
    "print(f\"{'应用使用自定义指令占比:':<20}{non_empty_count / len(df):.2%}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最近编辑应用总数: 48969\n",
      "应用使用自定义指令总数: 20169\n",
      "应用使用市场自定义指令总数: 18920\n",
      "应用使用市场自定义指令占比: 38.64%\n",
      "市场自定义指令在使用自定义指令占比: 93.81%\n"
     ]
    }
   ],
   "source": [
    "activity_code_keys = set(market_activity_detail_data.keys())\n",
    "\n",
    "used_market_activity_robot_count = df[\"extension_names\"].apply(lambda x: any(key in str(x) for key in activity_code_keys)).sum()\n",
    "print(f\"最近编辑应用总数: {len(df)}\")\n",
    "print(f\"应用使用自定义指令总数: {non_empty_count}\")\n",
    "print(f\"应用使用市场自定义指令总数: {used_market_activity_robot_count}\")\n",
    "print(f\"应用使用市场自定义指令占比: {used_market_activity_robot_count/len(df):.2%}\")\n",
    "print(f\"市场自定义指令在使用自定义指令占比: {used_market_activity_robot_count/non_empty_count:.2%}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 分析市场自定义指令使用情况"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 市场指令在应用中的占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Excel扩展操作:\t7887\t41.69%\t16.11%\n",
      "列表扩展操作:\t2460\t13.00%\t5.02%\n",
      "日期时间扩展:\t2181\t11.53%\t4.45%\n",
      "网页扩展操作:\t2125\t11.23%\t4.34%\n",
      "登录扩展操作:\t1870\t9.88%\t3.82%\n",
      "弹窗智能侦测器:\t1722\t9.10%\t3.52%\n",
      "文件扩展功能:\t1447\t7.65%\t2.95%\n",
      "飞书电子表格(RPA):\t1301\t6.88%\t2.66%\n",
      "通用日期选择:\t1205\t6.37%\t2.46%\n",
      "数据库扩展操作:\t1058\t5.59%\t2.16%\n",
      "文本扩展功能:\t984\t5.20%\t2.01%\n",
      "XPath跨域获取网页元素:\t867\t4.58%\t1.77%\n",
      "滑块拼图验证码(免费):\t815\t4.31%\t1.66%\n",
      "个人微信:\t782\t4.13%\t1.60%\n",
      "并发调用子流程:\t772\t4.08%\t1.58%\n",
      "WPS 在线表格:\t713\t3.77%\t1.46%\n",
      "获取进程or杀进程:\t701\t3.71%\t1.43%\n",
      "应用帐号信息:\t696\t3.68%\t1.42%\n",
      "json数据提取:\t684\t3.62%\t1.40%\n",
      "紫鸟控制台操作:\t649\t3.43%\t1.33%\n",
      "图片扩展操作:\t635\t3.36%\t1.30%\n",
      "【付费】验证码识别(web):\t579\t3.06%\t1.18%\n",
      "飞书多维表格:\t552\t2.92%\t1.13%\n",
      "云码验证码扩展:\t546\t2.89%\t1.11%\n",
      "Cookie登录操作:\t364\t1.92%\t0.74%\n",
      "抖店操作:\t359\t1.90%\t0.73%\n",
      "数学函数操作:\t321\t1.70%\t0.66%\n",
      "钉钉在线表格操作:\t320\t1.69%\t0.65%\n",
      "懒加载操作:\t282\t1.49%\t0.58%\n",
      "SAP 扩展:\t259\t1.37%\t0.53%\n",
      "字典扩展操作:\t253\t1.34%\t0.52%\n",
      "拼多多数据获取:\t253\t1.34%\t0.52%\n",
      "MySQL扩展操作:\t250\t1.32%\t0.51%\n",
      "企业微信:\t229\t1.21%\t0.47%\n",
      "WPS 扩展:\t224\t1.18%\t0.46%\n",
      "钉钉云盘操作:\t221\t1.17%\t0.45%\n",
      "亚马逊卖家后台扩展操作:\t214\t1.13%\t0.44%\n",
      "计算机扩展:\t210\t1.11%\t0.43%\n",
      "Photoshop 操作:\t181\t0.96%\t0.37%\n",
      "钉钉机器人操作:\t176\t0.93%\t0.36%\n",
      "影刀离线OCR:\t164\t0.87%\t0.33%\n",
      "等待任意元素:\t161\t0.85%\t0.33%\n",
      "广告杀手:\t160\t0.85%\t0.33%\n",
      "消息推送:\t159\t0.84%\t0.32%\n",
      "PDF 扩展操作:\t150\t0.79%\t0.31%\n",
      "腾讯文档扩展:\t137\t0.72%\t0.28%\n",
      "动态调用:\t135\t0.71%\t0.28%\n",
      "(新版)生意参谋数据获取:\t132\t0.70%\t0.27%\n",
      "手机系统操作:\t130\t0.69%\t0.27%\n",
      "语音助手-edge-tts:\t129\t0.68%\t0.26%\n",
      "拼多多时间选择:\t121\t0.64%\t0.25%\n",
      "手机文件操作:\t117\t0.62%\t0.24%\n",
      "进度条扩展操作:\t104\t0.55%\t0.21%\n",
      "鼠标扩展操作:\t91\t0.48%\t0.19%\n",
      "DataFrame表格扩展指令集:\t90\t0.48%\t0.18%\n",
      "京东商智扩展操作:\t89\t0.47%\t0.18%\n",
      "获取图表元素数据:\t87\t0.46%\t0.18%\n",
      "生意参谋常用操作:\t76\t0.40%\t0.16%\n",
      "Word 扩展:\t76\t0.40%\t0.16%\n",
      "万相台无界版:\t73\t0.39%\t0.15%\n",
      "浏览器扩展:\t72\t0.38%\t0.15%\n",
      "隐藏/显示浏览器窗口:\t72\t0.38%\t0.15%\n",
      "视频操作操作:\t69\t0.36%\t0.14%\n",
      "旺店通自定义操作:\t68\t0.36%\t0.14%\n",
      "Outlook 扩展操作:\t67\t0.35%\t0.14%\n",
      "抖音时间选择:\t66\t0.35%\t0.13%\n",
      "淘宝时间选择:\t63\t0.33%\t0.13%\n",
      "链接编码和解码:\t60\t0.32%\t0.12%\n",
      "罗盘.经营:\t54\t0.29%\t0.11%\n",
      "赤兔名品_时间选择:\t50\t0.26%\t0.10%\n",
      "Sqlite3 扩展操作:\t48\t0.25%\t0.10%\n",
      "翻译扩展操作:\t48\t0.25%\t0.10%\n",
      "生意参谋时间选择操作:\t43\t0.23%\t0.09%\n",
      "京东商智数据获取-商家版:\t43\t0.23%\t0.09%\n",
      "维格表操作:\t40\t0.21%\t0.08%\n",
      "HubStudio浏览器扩展操作:\t39\t0.21%\t0.08%\n",
      "软件(win)扩展:\t35\t0.18%\t0.07%\n",
      "天猫宝贝上架:\t35\t0.18%\t0.07%\n",
      "阿里妈妈多平台数据获取:\t34\t0.18%\t0.07%\n",
      "Sql Server 扩展:\t33\t0.17%\t0.07%\n",
      "断言:\t31\t0.16%\t0.06%\n",
      "二维码条形码:\t30\t0.16%\t0.06%\n",
      "PPT 扩展:\t30\t0.16%\t0.06%\n",
      "Redis 扩展:\t29\t0.15%\t0.06%\n",
      "免费OCR:\t29\t0.15%\t0.06%\n",
      "京东商智数据获取-品牌版:\t29\t0.15%\t0.06%\n",
      "打印机扩展:\t28\t0.15%\t0.06%\n",
      "异步下载图片:\t27\t0.14%\t0.06%\n",
      "唯品会指令集:\t26\t0.14%\t0.05%\n",
      "巨量千川:\t25\t0.13%\t0.05%\n",
      "京准通操作:\t23\t0.12%\t0.05%\n",
      "电子发票识别:\t21\t0.11%\t0.04%\n",
      "生意参谋扩展操作(win):\t21\t0.11%\t0.04%\n",
      "RAR解压缩操作:\t21\t0.11%\t0.04%\n",
      "汇率查询与转换:\t20\t0.11%\t0.04%\n",
      "物流信息分析:\t20\t0.11%\t0.04%\n",
      "紫鸟浏览器API:\t18\t0.10%\t0.04%\n",
      "PostgreSQL 扩展:\t18\t0.10%\t0.04%\n",
      "CSV扩展操作:\t18\t0.10%\t0.04%\n",
      "获取Windows凭据:\t17\t0.09%\t0.03%\n",
      "淘系推广时间选择:\t15\t0.08%\t0.03%\n",
      "引入指定应用python模块:\t15\t0.08%\t0.03%\n",
      "网银安全控件输入:\t15\t0.08%\t0.03%\n",
      "物流信息查询:\t14\t0.07%\t0.03%\n",
      "云码验证码扩展(win):\t13\t0.07%\t0.03%\n",
      "巨量百应数据扩展 (达人版):\t12\t0.06%\t0.02%\n",
      "京东商智数据获取-品牌店铺版:\t12\t0.06%\t0.02%\n",
      "Oracle 扩展操作:\t11\t0.06%\t0.02%\n",
      "飞鸽客服系统扩展:\t11\t0.06%\t0.02%\n",
      "云码验证码扩展(手机):\t11\t0.06%\t0.02%\n",
      "精选联盟数据扩展 (商家版):\t11\t0.06%\t0.02%\n",
      "谷歌recaptcha验证码:\t11\t0.06%\t0.02%\n",
      "淘系推广数据获取:\t9\t0.05%\t0.02%\n",
      "企业微信-服务端:\t9\t0.05%\t0.02%\n",
      "POST文件上传:\t9\t0.05%\t0.02%\n",
      "快手扩展:\t8\t0.04%\t0.02%\n",
      "巨量纵横:\t8\t0.04%\t0.02%\n",
      "京东金融时间选择:\t8\t0.04%\t0.02%\n",
      "千牛扩展:\t7\t0.04%\t0.01%\n",
      "天猫字体解密扩展:\t7\t0.04%\t0.01%\n",
      "直播弹幕扩展:\t6\t0.03%\t0.01%\n",
      "Matplotlib:\t5\t0.03%\t0.01%\n",
      "影刀离线NLP:\t5\t0.03%\t0.01%\n",
      "AI拓展操作:\t4\t0.02%\t0.01%\n",
      "旺店通数据查询—旗舰版接口:\t4\t0.02%\t0.01%\n",
      "WPS 多维表格:\t4\t0.02%\t0.01%\n",
      "SSH 隧道:\t4\t0.02%\t0.01%\n",
      "万象隐藏:\t4\t0.02%\t0.01%\n",
      "京准通数据获取:\t4\t0.02%\t0.01%\n",
      "旺店通数据查询—企业版接口:\t3\t0.02%\t0.01%\n",
      "电商罗盘数据获取:\t3\t0.02%\t0.01%\n",
      "百度智能云指令:\t3\t0.02%\t0.01%\n",
      "巨量纵横通用时间选择:\t3\t0.02%\t0.01%\n",
      "批量数据抓取(Java):\t2\t0.01%\t0.00%\n",
      "Hana数据库扩展:\t2\t0.01%\t0.00%\n",
      "淘宝直播数据获取:\t1\t0.01%\t0.00%\n",
      "Canvas表格处理:\t1\t0.01%\t0.00%\n",
      "数电开票扩展(蒙柏):\t1\t0.01%\t0.00%\n",
      "大众点评解密操作:\t1\t0.01%\t0.00%\n",
      "阿普斯usb封装指令集:\t1\t0.01%\t0.00%\n"
     ]
    }
   ],
   "source": [
    "# 遍历 activity_code_keys, 统计应用的使用次数\n",
    "\n",
    "# 确保 extension_names 列为字符串类型\n",
    "df[\"extension_names_str\"] = df[\"extension_names\"].astype(str)\n",
    "\n",
    "# 初始化计数字典\n",
    "market_activity_map_robot_count = {}\n",
    "\n",
    "# 使用矢量化操作统计每个 key 的出现次数\n",
    "for key in activity_code_keys:\n",
    "    count = df[\"extension_names_str\"].str.contains(key).sum()\n",
    "    if count > 0:\n",
    "        market_activity_map_robot_count[key] = count\n",
    "\n",
    "# 按照使用次数从大到小排序\n",
    "market_activity_map_robot_count = dict(\n",
    "    sorted(market_activity_map_robot_count.items(), key=lambda x: x[1], reverse=True)\n",
    ")\n",
    "\n",
    "# print(f\"\\t\\t市场指令应用占比\\t最近编辑应用占比\")\n",
    "for package_code, package_code_count in market_activity_map_robot_count.items():\n",
    "    # if package_code_count < 500:\n",
    "    #     continue\n",
    "    print(\n",
    "        f\"\"\"{market_activity_detail_data.get(package_code)}:\\t{package_code_count}\\t{package_code_count / used_market_activity_robot_count:.2%}\\t{package_code_count / len(df):.2%}\"\"\"\n",
    "    )\n",
    "    # print(f\"{market_activity_detail_data.get(package_code)}:\\t{package_code_count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "引用超过 1000个应用的指令集\n",
      "Excel扩展操作:\t7887\n",
      "列表扩展操作:\t2460\n",
      "日期时间扩展:\t2181\n",
      "网页扩展操作:\t2125\n",
      "登录扩展操作:\t1870\n",
      "弹窗智能侦测器:\t1722\n",
      "文件扩展功能:\t1447\n",
      "飞书电子表格(RPA):\t1301\n",
      "通用日期选择:\t1205\n",
      "数据库扩展操作:\t1058\n"
     ]
    }
   ],
   "source": [
    "# market_activity_map_robot_count 超过 1000个的指令集\n",
    "print(\"引用超过 1000个应用的指令集\")\n",
    "activity_over_1000 = {k: v for k, v in market_activity_map_robot_count.items() if v > 1000}\n",
    "for package_code, package_code_count in market_activity_map_robot_count.items():\n",
    "    if package_code_count < 1000:\n",
    "        continue\n",
    "    print(f\"{market_activity_detail_data.get(package_code)}:\\t{package_code_count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "引用超过 100 - 1000应用的指令集\n",
      "文本扩展功能:\t984\n",
      "XPath跨域获取网页元素:\t867\n",
      "滑块拼图验证码(免费):\t815\n",
      "个人微信:\t782\n",
      "并发调用子流程:\t772\n",
      "WPS 在线表格:\t713\n",
      "获取进程or杀进程:\t701\n",
      "应用帐号信息:\t696\n",
      "json数据提取:\t684\n",
      "紫鸟控制台操作:\t649\n",
      "图片扩展操作:\t635\n",
      "【付费】验证码识别(web):\t579\n",
      "飞书多维表格:\t552\n",
      "云码验证码扩展:\t546\n",
      "Cookie登录操作:\t364\n",
      "抖店操作:\t359\n",
      "数学函数操作:\t321\n",
      "钉钉在线表格操作:\t320\n",
      "懒加载操作:\t282\n",
      "SAP 扩展:\t259\n",
      "字典扩展操作:\t253\n",
      "拼多多数据获取:\t253\n",
      "MySQL扩展操作:\t250\n",
      "企业微信:\t229\n",
      "WPS 扩展:\t224\n",
      "钉钉云盘操作:\t221\n",
      "亚马逊卖家后台扩展操作:\t214\n",
      "计算机扩展:\t210\n",
      "Photoshop 操作:\t181\n",
      "钉钉机器人操作:\t176\n",
      "影刀离线OCR:\t164\n",
      "等待任意元素:\t161\n",
      "广告杀手:\t160\n",
      "消息推送:\t159\n",
      "PDF 扩展操作:\t150\n",
      "腾讯文档扩展:\t137\n",
      "动态调用:\t135\n",
      "(新版)生意参谋数据获取:\t132\n",
      "手机系统操作:\t130\n",
      "语音助手-edge-tts:\t129\n",
      "拼多多时间选择:\t121\n",
      "手机文件操作:\t117\n",
      "进度条扩展操作:\t104\n"
     ]
    }
   ],
   "source": [
    "print(\"引用超过 100 - 1000应用的指令集\")\n",
    "activity_over_1000 = {\n",
    "    k: v for k, v in market_activity_map_robot_count.items() if v > 1000\n",
    "}\n",
    "for package_code, package_code_count in market_activity_map_robot_count.items():\n",
    "    if 100 < package_code_count < 1000:\n",
    "        print(f\"{market_activity_detail_data.get(package_code)}:\\t{package_code_count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "业务指令主要占 100 - 1000 的引用量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "引用小于 100 应用的指令集\n",
      "鼠标扩展操作:\t91\n",
      "DataFrame表格扩展指令集:\t90\n",
      "京东商智扩展操作:\t89\n",
      "获取图表元素数据:\t87\n",
      "生意参谋常用操作:\t76\n",
      "Word 扩展:\t76\n",
      "万相台无界版:\t73\n",
      "浏览器扩展:\t72\n",
      "隐藏/显示浏览器窗口:\t72\n",
      "视频操作操作:\t69\n",
      "旺店通自定义操作:\t68\n",
      "Outlook 扩展操作:\t67\n",
      "抖音时间选择:\t66\n",
      "淘宝时间选择:\t63\n",
      "链接编码和解码:\t60\n",
      "罗盘.经营:\t54\n",
      "赤兔名品_时间选择:\t50\n",
      "Sqlite3 扩展操作:\t48\n",
      "翻译扩展操作:\t48\n",
      "生意参谋时间选择操作:\t43\n",
      "京东商智数据获取-商家版:\t43\n",
      "维格表操作:\t40\n",
      "HubStudio浏览器扩展操作:\t39\n",
      "软件(win)扩展:\t35\n",
      "天猫宝贝上架:\t35\n",
      "阿里妈妈多平台数据获取:\t34\n",
      "Sql Server 扩展:\t33\n",
      "断言:\t31\n",
      "二维码条形码:\t30\n",
      "PPT 扩展:\t30\n",
      "Redis 扩展:\t29\n",
      "免费OCR:\t29\n",
      "京东商智数据获取-品牌版:\t29\n",
      "打印机扩展:\t28\n",
      "异步下载图片:\t27\n",
      "唯品会指令集:\t26\n",
      "巨量千川:\t25\n",
      "京准通操作:\t23\n",
      "电子发票识别:\t21\n",
      "生意参谋扩展操作(win):\t21\n",
      "RAR解压缩操作:\t21\n",
      "汇率查询与转换:\t20\n",
      "物流信息分析:\t20\n",
      "紫鸟浏览器API:\t18\n",
      "PostgreSQL 扩展:\t18\n",
      "CSV扩展操作:\t18\n",
      "获取Windows凭据:\t17\n",
      "淘系推广时间选择:\t15\n",
      "引入指定应用python模块:\t15\n",
      "网银安全控件输入:\t15\n",
      "物流信息查询:\t14\n",
      "云码验证码扩展(win):\t13\n",
      "巨量百应数据扩展 (达人版):\t12\n",
      "京东商智数据获取-品牌店铺版:\t12\n",
      "Oracle 扩展操作:\t11\n",
      "飞鸽客服系统扩展:\t11\n",
      "云码验证码扩展(手机):\t11\n",
      "精选联盟数据扩展 (商家版):\t11\n",
      "谷歌recaptcha验证码:\t11\n",
      "淘系推广数据获取:\t9\n",
      "企业微信-服务端:\t9\n",
      "POST文件上传:\t9\n",
      "快手扩展:\t8\n",
      "巨量纵横:\t8\n",
      "京东金融时间选择:\t8\n",
      "千牛扩展:\t7\n",
      "天猫字体解密扩展:\t7\n",
      "直播弹幕扩展:\t6\n",
      "Matplotlib:\t5\n",
      "影刀离线NLP:\t5\n",
      "AI拓展操作:\t4\n",
      "旺店通数据查询—旗舰版接口:\t4\n",
      "WPS 多维表格:\t4\n",
      "SSH 隧道:\t4\n",
      "万象隐藏:\t4\n",
      "京准通数据获取:\t4\n",
      "旺店通数据查询—企业版接口:\t3\n",
      "电商罗盘数据获取:\t3\n",
      "百度智能云指令:\t3\n",
      "巨量纵横通用时间选择:\t3\n",
      "批量数据抓取(Java):\t2\n",
      "Hana数据库扩展:\t2\n",
      "淘宝直播数据获取:\t1\n",
      "Canvas表格处理:\t1\n",
      "数电开票扩展(蒙柏):\t1\n",
      "大众点评解密操作:\t1\n",
      "阿普斯usb封装指令集:\t1\n"
     ]
    }
   ],
   "source": [
    "print(\"引用小于 100 应用的指令集\")\n",
    "activity_over_1000 = {\n",
    "    k: v for k, v in market_activity_map_robot_count.items() if v > 1000\n",
    "}\n",
    "for package_code, package_code_count in market_activity_map_robot_count.items():\n",
    "    if package_code_count < 100:\n",
    "        print(f\"{market_activity_detail_data.get(package_code)}:\\t{package_code_count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 不同指令在市场指令的占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "总计使用市场自定义指令的数量: 75642\n",
      "使用市场指令在使用自定义指令应用的平均数: 4.00\n"
     ]
    }
   ],
   "source": [
    "used_activity_data = df['extension_names'].explode().dropna().tolist()\n",
    "used_market_activity_data = [\n",
    "    extension_name\n",
    "    for extension_name in used_activity_data\n",
    "    if any(key in extension_name for key in activity_code_keys)\n",
    "]\n",
    "print(f\"总计使用市场自定义指令的数量: {len(used_market_activity_data)}\")\n",
    "print(f\"使用市场指令在使用自定义指令应用的平均数: {len(used_market_activity_data)/used_market_activity_robot_count:.2f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对原始数据进行解析\n",
    "used_market_activity_detail_data = []\n",
    "for item in used_market_activity_data:\n",
    "    # print(item)\n",
    "    _, package_code, activity_code=item.split(\".\", 2)\n",
    "    used_market_activity_detail_data.append({\"package_code\": package_code, \"activity_code\": activity_code})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "自定指令使用的条数分析\n",
      "Excel扩展操作: 23339\n",
      "列表扩展操作: 4208\n",
      "飞书电子表格(RPA): 3352\n",
      "网页扩展操作: 2424\n",
      "日期时间扩展: 2396\n",
      "登录扩展操作: 2305\n",
      "XPath跨域获取网页元素: 2075\n",
      "弹窗智能侦测器: 2035\n",
      "飞书多维表格: 1977\n",
      "WPS 在线表格: 1949\n",
      "文件扩展功能: 1675\n",
      "数据库扩展操作: 1581\n",
      "紫鸟控制台操作: 1503\n",
      "通用日期选择: 1489\n",
      "个人微信: 1302\n",
      "SAP 扩展: 1114\n",
      "文本扩展功能: 1104\n",
      "钉钉在线表格操作: 1055\n",
      "图片扩展操作: 1035\n"
     ]
    }
   ],
   "source": [
    "# 通过 package_code 统计 activity_details 中的活动数量\n",
    "from collections import Counter\n",
    "\n",
    "# 汇总 package_code 数量\n",
    "summary = Counter(item[\"package_code\"] for item in used_market_activity_detail_data)\n",
    "\n",
    "# 输出结果\n",
    "result = dict(summary)\n",
    "\n",
    "# 将结果按数量从大到小排序\n",
    "sorted_result = summary.most_common()\n",
    "\n",
    "\n",
    "print(\"自定指令使用的条数分析\")\n",
    "for package_code, package_code_count in sorted_result:\n",
    "    if package_code_count < 1000: continue\n",
    "    print(f\"{market_activity_detail_data.get(package_code)}: {package_code_count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "? 业务指令占比使用率比较低, 但是占用了大量的元素维护工作, 比如: 生意参谋操作, 京东生意参谋操作\n",
    "(也有可能数据失真, 无法准确反映实际情况)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 高频指令的热区分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Excel 扩展指令"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C_01_筛选: 3463\n",
      "A_01_单元格公式向下填充: 2857\n",
      "D_03_查找数据所在行: 2230\n",
      "C_03_清除筛选: 1864\n",
      "C_05_删除筛选内容: 1710\n",
      "C_04_读取筛选内容: 1661\n",
      "B_02_内容替换: 1213\n",
      "A_03_自动向下填充: 1211\n",
      "D_02_查找数据所在列: 1209\n",
      "B_04_区域文本转数字: 825\n",
      "B_01_分列: 739\n",
      "B_03_区域截图: 560\n",
      "A_06_公式转换成值: 552\n",
      "D_01_数字列名转换: 526\n",
      "B_07_取消合并单元格: 398\n",
      "A_04_单元格填充图片: 342\n",
      "D_04_合成字典_列表拼接: 316\n",
      "B_05_区域数字转文本: 285\n",
      "A_02_单元格公式向右填充: 253\n",
      "A_05_空白单元格填充: 185\n",
      "D_12_冻结首行: 176\n",
      "D_11_自动换行: 155\n",
      "D_05_合成字典_数字累加: 91\n",
      "C_02_筛选颜色: 75\n",
      "B_14_获取合并单元格区域: 62\n",
      "B_08_设置单元格超链接: 59\n",
      "D_07_隐藏取消隐藏工作表: 58\n",
      "D_06_删除所有图片: 48\n",
      "B_10_导出单元格图片: 44\n",
      "B_09_获取单元格超链接: 32\n",
      "D_10_设置密码: 31\n",
      "refresh_pivot_table: 28\n",
      "B_11_删除单元格图片: 20\n",
      "A_07_新建注释: 19\n",
      "B_13_获取背景色: 14\n",
      "D_08_获取隐藏的工作表名: 11\n",
      "D_09_合并计算: 9\n",
      "B_06_读取单元格注释: 6\n",
      "D_13_设置切片器: 2\n"
     ]
    }
   ],
   "source": [
    "# 获取 activity_details中每一项 package_code 为activity_excel_v2 的item\n",
    "activity_details_activity_excel_v2 = [item for item in used_market_activity_detail_data if item[\"package_code\"] == \"activity_excel_v2\"]\n",
    "\n",
    "# 根据 activity_code 统计数量\n",
    "summary_activity_code = Counter(item[\"activity_code\"] for item in activity_details_activity_excel_v2)\n",
    "\n",
    "# 按顺序输出结果\n",
    "sorted_summary_activity_code = summary_activity_code.most_common()\n",
    "# 打印出来\n",
    "for activity_code, used_market_activity_robot_count in sorted_summary_activity_code:\n",
    "    print(f\"{activity_code}: {used_market_activity_robot_count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 云码验证码指令"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "std_slider_captcha: 199\n",
      "ali_slider_captcha: 110\n",
      "dy_slider_captcha: 90\n",
      "std_alphanum_captcha: 68\n",
      "jd_rotate_captcha: 66\n",
      "xhs_rotate_captcha: 47\n",
      "jd_click_select_captcha: 45\n",
      "std_trace_captcha: 44\n",
      "std_click_select_captcha: 11\n",
      "cs_word_order_click_select_captcha: 11\n",
      "jd_click_select_captcha_original: 10\n",
      "dy_double_rotate_captcha: 9\n",
      "cs_space_same_object_captcha: 8\n",
      "jd_trace_captcha: 6\n",
      "chinatax_captcha: 4\n",
      "ali_click_select_captcha: 3\n",
      "cs_oocl_captcha: 3\n",
      "cs_temu_captcha: 2\n",
      "tx_six_squares_captcha: 2\n",
      "orangebank_slider_captcha: 1\n"
     ]
    }
   ],
   "source": [
    "# 获取 activity_details中每一项 package_code 为activity_excel_v2 的item\n",
    "activity_details_activity_excel_v2 = [\n",
    "    item\n",
    "    for item in used_market_activity_detail_data\n",
    "    if item[\"package_code\"] == \"activity_jfbym\"\n",
    "]\n",
    "\n",
    "# 根据 activity_code 统计数量\n",
    "summary_activity_code = Counter(\n",
    "    item[\"activity_code\"] for item in activity_details_activity_excel_v2\n",
    ")\n",
    "\n",
    "# 按顺序输出结果\n",
    "sorted_summary_activity_code = summary_activity_code.most_common()\n",
    "# 打印出来\n",
    "for activity_code, used_market_activity_robot_count in sorted_summary_activity_code:\n",
    "    print(f\"{activity_code}: {used_market_activity_robot_count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 下一步\n",
    "\n",
    "自定义指令分布的版本信息, 推测指令对应用的影响范围(暂无数据)\n",
    "\n",
    "下架指令的依据\n",
    "\n",
    "进步探索新的指令集"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}