0
0

【Selenium&办公自动化Excel】视频网站国漫热度数据分析

一、引言

图一乐

在抖音上看到大家都引用云合数据作为国漫热度的数据来源，数据来源还是太过于单一，无法体现“统计学的魅力”，故突发奇想，自己做一个数据统计，抓取多个网站排行观看数据，综合对比。

不同网站统计方式会有所不同，因此将抓取多个网站，采用加权平均合并法（AI提供的思路），最终合成一个排行榜。

理想很丰满，现实很骨感，因为各个网站的资源不同，名称、分类不统一，导致最终实现的效果非常难看，大家只看获取数据部分就行了。

为什么不直接使用优爱腾？

简单来说就是独占作品太多，无法统一计算

二、网站

1.直接百度热门作品在线观看网站

  1.     ![](https://i-blog.csdnimg.cn/direct/016553e4115d463d8f5c13c6619e7c39.png)

2.多人推荐的视频网站

  1.     ![](https://i-blog.csdnimg.cn/direct/03c83652dff44a2f97fbacb33782f14f.png)

3.分类：内地、国漫、国产动漫、大陆等，

4.排序：按热度、按人气、按观看数等，

三、数量

目前仅做了抓取第一页数据（一般一页为20~50多部作品）的抓取逻辑

考虑到网站页面不一样，尽量选择打开链接就是筛选好的分类页面，获取第一页数据

四、技术

  1. Python
  2. Selenium
  3. 办公自动化Excel 1. openpyxl2. pandas

五、存储

Excel（便于后面直接插入公式计算）

六、思路

省流（该思路并不严谨）

获取单个作品在各个榜单中的排名，计算不同榜单排名的平均值，最后排序，数值越小排名越高

AI详细步骤：

步骤 1: 准备工作

确保你的数据如下所示：

  • Sheet1（榜单1）：A列为“排名”，B列为“名称”
  • Sheet2（榜单2）：A列为“排名”，B列为“名称”

步骤 2: 创建一个综合榜单

在新的工作表（如Sheet3）中创建一个包含所有可能作品名称的列表。这个列表应该包含出现在任一榜单中的所有作品。为了确保不会遗漏任何作品，你可以在Sheet3中先合并两个榜单的作品名称，然后去除重复项。

合并并去除重复项的方法：
  1. 在Sheet3中选择一个单元格，例如A1。
  2. 输入公式 =Sheet1!$B$2:$B$100 来引用Sheet1中的名称列，然后将此区域复制到另一个位置（如C列），再用 =Sheet2!$B$2:$B$100 引用Sheet2的名称列。
  3. 选中这两个引用区域，转到“数据”选项卡，点击“删除重复项”。

这将会给你一个不重复的作品名称列表。

步骤 3: 获取两个榜单的排名

在新工作表中添加两列用于存放来自两个榜单的排名信息。使用 

  1. VLOOKUP

或更推荐的 

  1. XLOOKUP

函数来查找并填充这两个榜单中每个作品的排名。

示例公式（假设你的作品名称在Sheet3的A2开始）

对于榜单1（Sheet1）的排名，在B列输入：

  1. =IFERROR(XLOOKUP($A2, Sheet1!$B$2:$B$100, Sheet1!$A$2:$A$100), "")

对于榜单2（Sheet2）的排名，在C列输入：

  1. =IFERROR(XLOOKUP($A2, Sheet2!$B$2:$B$100, Sheet2!$A$2:$A$100), "")

步骤 4: 计算平均排名

在新工作表中再添加一列用于计算平均排名。使用 

  1. AVERAGE

函数来计算每个作品的平均排名。

示例公式（假设榜单1排名在B列，榜单2排名在C列）
  1. =AVERAGE(B2:C2)

如果你希望忽略空值（即如果某个作品只出现在一个榜单中），可以使用以下公式：

  1. =AVERAGEIF(B2:C2, "<>")

步骤 5: 排序

最后，根据计算出的平均排名对作品进行排序。选择平均排名所在的列，然后使用“数据”菜单下的“排序”功能，按照平均排名升序或降序排列。

通过以上步骤，你应该能够成功地使用Excel将两个榜单的数据合成一个新的榜单，并根据平均排名对作品进行排序。如果你有任何问题或者需要进一步的帮助，请随时告知。

七、Python

Selenium

使用Selenium，已做通用爬取设计，只需要增加里面字典参数即可，3个参数

url: 网站分类排行的URL（直接打开之后就是已经筛选为国漫并按热度、人气等分类排序的页面）

xpath: 作品名称XPath表达式，应匹配到全部的作品名称

sheet_name： 保存到Excel文件的工作表名称

因为涉及到后面的公式计算，所以只用了Excel保存

  1. from selenium import webdriver
  2. from selenium.webdriver.common.by import By
  3. from selenium.webdriver.chrome.options import Options
  4. from selenium.webdriver.chrome.service import Service
  5. from webdriver_manager.chrome import ChromeDriverManager
  6. import pandas as pd
  7. import time
  8. import logging
  10. # 设置Selenium WebDriver
  11. def setup_driver(headless=True):
  12.     print('setup_driver')
  14.     if headless == True:
  15.         print('已启用无头模式，将不显示浏览器窗口')
  17.     # 设置Chrome选项
  18.     options = Options()
  20.     if headless:
  21.         options.add_argument('--headless')  # 启用无头模式
  22.         options.add_argument('--disable-gpu')  # 在无头模式下通常需要禁用GPU加速
  24.     # options.add_argument('--no-sandbox')  # 解决DevToolsActivePort文件不存在的报错
  25.     # options.add_argument('--disable-dev-shm-usage')  # 解决资源受限问题
  26.     # options.add_argument('--ignore-certificate-errors')  # 忽略证书错误
  27.     # options.add_argument('--window-size=1920x1080')  # 设置窗口大小，对于某些网站可能会影响页面加载
  28.     #
  29.     # # 设置日志级别以减少输出信息
  30.     # options.add_argument('--log-level=3')  # FATAL 级别，仅显示严重错误
  32.     try:
  33.         # 初始化WebDriver
  34.         driver = webdriver.Chrome(options=options)
  36.         print('谷歌打开成功')
  38.         return driver
  39.     except Exception as e:
  40.         logging.error(f"Failed to initialize WebDriver: {e}")
  41.         raise
  43. # 打开网站并获取数据
  44. def scrape_data(driver, url, xpath, max_elements=None):
  45.     if max_elements is not None:
  46.         print(f'抓取数量：{max_elements}')
  48.     try:
  49.         # 设置页面加载的最大时间为30秒
  50.         driver.set_page_load_timeout(30)
  52.         driver.get(url)
  53.         time.sleep(3)  # 等待页面加载完成，可以根据实际情况调整等待时间或使用显式等待
  55.         elements = driver.find_elements(By.XPATH, xpath)
  57.         # 如果指定了最大抓取数量，则限制抓取数量
  58.         if max_elements is not None and len(elements) > max_elements:
  59.             elements = elements[:max_elements]
  61.         # 提取文本内容
  62.         data = [element.text for element in elements]
  64.         return data
  65.     except Exception as e:
  66.         print(f"Failed to scrape data: {e}")
  68. def save_to_excel_multiple_sheets(data_dict, filename='output.xlsx'):
  69.     """
  70.     将数据保存到Excel文件的不同工作表，并添加序号列。
  72.     :param data_dict: 包含多个工作表数据的字典，键为工作表名称，值为数据列表
  73.     :param filename: 输出的Excel文件名，默认为'output.xlsx'
  74.     """
  75.     with pd.ExcelWriter(filename, engine='openpyxl') as writer:
  76.         for sheet_name, data in data_dict.items():
  77.             # 为每条数据添加序号
  78.             if data:
  79.                 df = pd.DataFrame(data, columns={'名称'})
  80.                 df.insert(0, '序号', range(1, len(df) + 1))
  81.             else:
  82.                 df = pd.DataFrame(columns=['序号', '名称'])  # 如果数据为空，创建空的DataFrame
  84.             df.to_excel(writer, sheet_name=sheet_name, index=False)
  86. if __name__ == '__main__':
  87.     # 保存路径
  88.     filename = r'E:\test\数据.xlsx'
  90.     # 抓取数量
  91.     max_elements = 30
  93.     # url: 网站分类排行的URL（直接打开之后就是已经筛选为国漫并按热度、人气等分类排序的页面）
  94.     # xpath: 作品名称XPath表达式，应匹配到全部的作品名称
  95.     # sheet_name： 保存到Excel文件的工作表名称
  96.     websites = [
  97.         {
  98.             'url': 'https://www.kuyoo.com/vodshow/32--hits---------.html',
  99.              'xpath': '//ul[@class="myui-vodlist clearfix"]/li//h4',
  100.              'sheet_name': '天空影视'
  101.         },
  102.         {
  103.             'url': 'https://www.sxzqsw.com/vodshow/32-%E5%A4%A7%E9%99%86-hits---------.html',
  104.              'xpath': '//ul[@class="stui-vodlist clearfix"]/li//h4',
  105.              'sheet_name': '淘剧影院'
  106.         },
  107.         {
  108.             'url': 'https://nyafun.cc/index.php/vod/show/area/%E5%A4%A7%E9%99%86/by/hits/id/24.html',
  109.             'xpath': '//ul[@class="hl-vod-list clearfix"]/li//div[@class="hl-item-text"]',
  110.             'sheet_name': 'NyaFun动漫'
  111.         },
  112.         {
  113.             'url': 'https://www.ssrfun.com/vodshow/1/area/%E5%A4%A7%E9%99%86/by/hits/lang/%E5%9B%BD%E8%AF%AD.html',
  114.             'xpath': '//div[@class="module"]/a//div[@class="module-poster-item-title"]',
  115.             'sheet_name': 'SSRFun动漫'
  116.         },
  117.         {
  118.             'url': 'https://www.ziyedm.com/show/anime-%E4%B8%AD%E5%9B%BD-hits--%E5%9B%BD%E8%AF%AD-------/',
  119.             'xpath': '//ul[contains(@class,"mt4 animate-in fade-in")]/li//h3',
  120.             'sheet_name': '子夜动漫'
  121.         },
  122.     ]
  124.     # 无头参数
  125.     headless = True
  127.     # 初始化WebDriver
  128.     driver = setup_driver(headless=headless)
  130.     try:
  131.         # 创建字典来保存每个网站的数据
  132.         data_dict = {}
  133.         data_list = []
  135.         websites_len = len(websites)
  136.         if websites_len == 0:
  137.             print('没有网站')
  138.             exit()
  140.         print(f'网站数量：{websites_len}')
  142.         for site in websites:
  143.             print(f"正在抓取:{site['sheet_name']} - {site['url']}")
  144.             data = scrape_data(driver, site['url'], site['xpath'], max_elements)
  145.             print('data：', data)
  147.             if data is None:
  148.                 print('抓取错误')
  149.                 continue
  151.             # 保存到不同工作表
  152.             data_dict[site['sheet_name']] = data
  154.             # 或者保存到同一工作表
  155.             for item in data:
  156.                 data_list.append([site['url'], item])
  158.             print('')
  160.         if data_dict:
  161.             # 选择一种保存方式
  162.             save_to_excel_multiple_sheets(data_dict, filename)
  164.             print(f'保存路径：{filename}')
  165.     finally:
  166.         # 关闭浏览器
  167.         driver.quit()

计算

新增“综合排行”工作表，存放全部数据，删除重复数据，在该工作表中插入公式，

因为涉及到不同的工作表计算，所以Excel的格式不能修改，还有工作表名称也不能修改，否则计算会出错。

  1. from openpyxl import load_workbook
  2. from openpyxl.utils import get_column_letter
  3. import pandas as pd
  5. # 创建新的工作表
  6. def create_sheet(workbook, sheet_name):
  7.     if sheet_name in workbook.sheetnames:
  8.         print(f"工作表：'{sheet_name}' 已存在")
  9.         return
  11.     workbook.create_sheet(title=sheet_name)
  12.     # workbook.save(file_path)
  13.     #
  14.     # print(f"已成功添加工作表 '{sheet_name}' 到文件 '{file_path}'")
  16. # 获取所有榜单名称数据，删除重复数据
  17. def get_all_data(workbook, order_sheet_name):
  18.     all_data = []
  19.     for sheet in workbook.worksheets:
  20.         sheet_name = sheet.title
  22.         # 跳过排行工作表
  23.         if sheet_name == order_sheet_name:
  24.             continue
  26.         rows = sheet.iter_rows(min_row=2, values_only=True)
  27.         for row in rows:
  28.             name = row[1]  # 注意：column_index是从1开始的，而row索引是从0开始的
  29.             if name is not None and isinstance(name, str):  # 只添加非空且为字符串类型的名称
  30.                 all_data.append(name.strip())
  32.     # 删除重复数据
  33.     all_data = list(set(all_data))
  34.     return all_data
  36. # 将数据写入到指定的工作表中
  37. def write_to_sheet(workbook, data, target_sheet_name, column_index=1):
  38.     try:
  39.         # 检查目标工作表是否存在，不存在则创建
  40.         if target_sheet_name not in workbook.sheetnames:
  41.             workbook.create_sheet(title=target_sheet_name)
  43.         target_sheet = workbook[target_sheet_name]
  45.         # 清空目标工作表（可选）
  46.         target_sheet.delete_rows(1, target_sheet.max_row)
  48.         # 写入标题行（可选）
  49.         target_sheet[f'{get_column_letter(column_index)}1'] = '名称'
  51.         # 写入数据
  52.         for idx, item in enumerate(data, start=2):  # 从第二行开始写入
  53.             target_sheet[f'{get_column_letter(column_index)}{idx}'] = item
  55.     except Exception as e:
  56.         print(f'写入排行工作表失败：{e}')
  58. # 获取对应榜单的排名
  59. def get_rank_from_sheet(workbook, order_sheet_name):
  60.     # 获取所有榜单名称
  61.     all_sheet = []
  62.     for sheet in workbook.worksheets:
  63.         sheet_name = sheet.title
  64.         all_sheet.append(sheet_name)
  65.     print(all_sheet)
  67.     # 写入表头
  68.     sheet = workbook[order_sheet_name]
  69.     for col_num, header in enumerate(all_sheet, start=2):
  70.         col_letter = get_column_letter(col_num)
  71.         sheet[f'{col_letter}1'] = header
  73.     workbook.save(file_path)
  74.     print('写入工作表表头成功')
  76.     # 获取最大行数
  77.     first_sheet_name = workbook.sheetnames[0]
  78.     sheet = workbook[first_sheet_name]
  79.     max_row = sheet.max_row
  80.     effective_rows = 0
  81.     for row in sheet.iter_rows(min_row=1, max_row=max_row, values_only=True):
  82.         if any(cell is not None for cell in row):
  83.             effective_rows += 1
  84.     print(f'第一个工作表最大行数：{effective_rows}')
  86.     # 获取最大列数
  87.     sheet = workbook[order_sheet_name]
  88.     max_col = 0
  89.     for col_idx, cell in enumerate(sheet[1], start=1):
  90.         max_col = col_idx
  91.     print('有效列数：', max_col)
  93.     # 获取综合排行工作表最大行数
  94.     sheet = workbook[order_sheet_name]
  95.     max_row = sheet.max_row
  96.     order_rows = 0
  97.     for row in sheet.iter_rows(min_row=1, max_row=max_row, values_only=True):
  98.         if any(cell is not None for cell in row):
  99.             order_rows += 1
  100.     print(f'综合排行工作表最大行数：{order_rows}')
  102.     # 获取单个名称在对应工作表中的排名
  103.     for col in range(2, max_col):
  104.         sheet_name = sheet.cell(row=1, column=col).value
  106.         # 遍历每行
  107.         for row_num in range(2, order_rows + 1):
  108.             # 单元格
  109.             target_cell = sheet.cell(row=row_num, column=col)
  110.             print(f'单元格：{target_cell}')
  112.             # 拼接公式
  113.             formula = f'=IFERROR(XLOOKUP($A{row_num},{sheet_name}!$B$2:$B${effective_rows},{sheet_name}!$A$2:$A${effective_rows}),"")'
  114.             print(formula)
  116.             target_cell.value = formula
  118.     print('')
  120.     # 综合排名计算
  121.     # 查找表头"综合排行"的列索引（基于字母）
  122.     header_row = 1  # 假设表头在第一行
  123.     column_index = 1
  124.     for cell in sheet[header_row]:
  125.         if cell.value == "综合排行":
  126.             column_letter = cell.column_letter  # 获取列字母
  127.             column_index = cell.column  # 获取列索引（数字形式）
  128.             print(f"找到表头'综合排行'位于: 列 {column_letter} (索引 {column_index})")
  130.     # 获取统计结束列字母
  131.     AVERAGEIF_end_col = get_column_letter(column_index - 1)
  133.     for row_num in range(2, order_rows):
  134.         # 单元格
  135.         target_cell = sheet.cell(row=row_num, column=column_index)
  136.         print(f'单元格：{target_cell}')
  138.         # 拼接公式
  139.         formula = f'=AVERAGEIF(B{row_num}:{AVERAGEIF_end_col}{row_num},"<>")'
  140.         print(formula)
  142.         target_cell.value = formula
  144. if __name__ == '__main__':
  145.     file_path = r'E:\test\数据.xlsx'
  146.     order_sheet_name = '综合排行'
  147.     # order_file = 'E:\test\综合排行.xlsx'
  149.     try:
  150.         # 加载现有的工作簿
  151.         workbook = load_workbook(filename=file_path)
  153.         # 创建排序用的工作表
  154.         create_sheet(workbook, order_sheet_name)
  156.         # 获取唯一数据
  157.         unique_names = get_all_data(workbook, order_sheet_name)
  158.         # print(unique_names)
  160.         # 将唯一数据写入到指定工作表
  161.         write_to_sheet(workbook, unique_names, order_sheet_name, column_index=1)
  163.         # 添加计算公式
  164.         get_rank_from_sheet(workbook, order_sheet_name)
  166.         # 保存
  167.         workbook.save(file_path)
  169.         print(f'保存路径：{file_path}')
  170.     except Exception as e:
  171.         print(f'保存失败：{e}')

排序

就差最后一步，但是使用openpyxl库读取Excel公式计算的值都是None，导致排序有问题

换了别的库也不行，这个排序只能手动了

八、效果

获取效果

公式计算结果

  1. 综合排行列需手动排序，空白为其网站在指定排名没有找到，该公式会把空白忽略。
  2. 本来想自动排序好的，但是Python无法获取公式的计算值，只能手动了

九、注意

  1. 每个网站的资源不一样，作品A可能在平台1上可以观看，但在平台2上就没有，会导致最终排名与实际不符
  2. 热度不代表质量
  3. 作品更新日该作品的热度会快速提升
  4. 部分网站会区分动画和动漫，部分不会区分，所以会出现诸如：猪猪侠、熊出没等子供向作品，目前并没有对其做区分操作
  5. 名称不一样会出现重复，如诛仙、诛仙 动画版
  6. 我对算法一窍不通，只能通过AI提供的思路进行排序，所以这个算法有非常大的缺陷，要求各个榜单的作品都存在，如果某一作品只在其中一个榜单，会导致最终的排名变高。但是考虑到各个网站的资源不同，这一现象应该普遍存在，所以最终的排名还需进一步优化。
  7. 没有做反爬措施，部分网站可能无法获取
  8. 图一乐
标签： python selenium 爬虫
本文转载自: https://blog.csdn.net/qq_48180611/article/details/144248013
版权归原作者 vdoi 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【Selenium&办公自动化Excel】视频网站国漫热度数据分析”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？