Python爬虫抓热搜榜前50条：JSON接口清洗与Excel自动化落盘

简介：围绕微博热搜采集与Excel落盘的Python爬虫练习包，面向希望掌握网页请求、HTML解析和表格导出的入门到进阶学习者。它聚焦微博前50条热门话题的抓取与整理，借助requests获取页面、BeautifulSoup定位热搜列表并提取标题与链接，再用pandas写入Excel，同时兼顾抓取时间戳记录、异常处理与重试等实用细节，帮助读者理解一个可复用的小型数据采集流程。压缩包共2个文件，以1个py脚本和1个xlsx示例表为主，整体约7KB，便于快速解压查看代码与结果。已有478人学习，说明该案例具备一定参考热度。读者可从中获得完整脚本、示例表格和采集思路，对照代码理解请求发送、字段抽取、数据落盘及基础容错处理，适合用作课程练习、爬虫入门示例或后续扩展数据分析的起点，并需注意遵守网站规则与相关法律法规。

1. 从一次“截图 50 条、脚本只跑出 47 条”说起

上周帮同事对一份榜单数据，他手动截图数了 50 条，我脚本跑出来只有 47 条。差的三个里，两个是夹在榜单中间的推广位，还有一个词带高亮标签，被正则在第一个尖括号处截断了。这个场景说出来不复杂：用 Python 爬虫 抓热搜榜前 50 条，清洗成结构化字段，写进一个能直接发给同事、能排序能看出热值差距的 Excel。真正消耗时间的部分从来不在requests.get那一行，而在接口字段会变、热值单位会飘、写盘之后还要经得起人眼看。

这份资源面向的就是这个具体需求：一次抓取、一次清洗、一次落盘，跑完就能拿到 xlsx。适合三类人：刚学完 requests 想找个真实接口练手的新手、需要每天盯榜单做选题或舆情观察的运营、以及想把“抓数据”这一步塞进现有报表流水线的开发者。下面按“找接口 → 洗字段 → 写表格 → 排故障”的顺序拆开，每一步都给出能直接抄的代码和参数说明。

2. 找对数据入口：JSON 接口、SSR 页面与 HTML 榜单页的三选一

同一份榜单，站点通常同时提供三种可抓形态，选错入口会让后面的清洗工作翻倍。我一般先花五分钟把三种都看一遍，再决定写哪一套解析。

2.1 三个入口的代价对比

第一种是榜单页的 HTML，服务端直出，BeautifulSoup抓li节点就能拿到词和排名。优点是稳健、不依赖接口鉴权；缺点是页面结构一改版就全废，而且热值经常被渲染在 JS 里，抓不到。

第二种是移动端或小程序侧走的 JSON 接口，返回{"ok":1,"data":{"realtime":[...]}}这种结构，字段名直接就是word、num、label_name、rank。这是我优先选的形态，因为 JSON 自带类型语义，清洗成本最低。

第三种是 PC 页面的 SSR 数据，通常以一大段脚本变量的形式内嵌在 HTML 里，需要正则或json.loads截取。它比纯 HTML 稳一点，但比接口脏，遇到转义字符容易翻车。

定位方法很土但有效：打开榜单页，按 F12 进 Network 面板，筛选 XHR 或 Fetch，刷新一次，按响应大小排序，找返回体里带榜单词条的那个请求。右键复制为 cURL，把 URL、查询参数、请求头三样东西抄下来，接口就找到了。这里要提醒一句，接口路径和参数名在不同端之间会不一样，抄的时候连Referer一起抄，别只抄 URL。

2.2 请求头与参数的最小可用集合

很多人卡在“浏览器能打开、脚本返回空”，九成是请求头缺项。我用的是一个最小集合：User-Agent用桌面版 Chrome 的完整串，Referer设成榜单页地址，Accept声明接受 JSON，Accept-Language给zh-CN。这四项凑齐，绝大多数公开榜单接口就会正常返回。

参数侧通常只有两三个：一个是时间戳_，用来绕开中间层缓存，值取毫秒级当前时间；另一个可能是page或limit，控制返回条数；有的接口还会带一个type区分总榜和分类榜。参数名各端不统一，抄 cURL 时原样保留，别自作聪明改名字。

超时和重试要一次性配好，不要等线上跑挂了再补。连接超时给 3 秒左右、读取超时给 8 秒，是比较通用的组合；重试只对 429、500、502、503、504 这几个状态码生效，并且要带退避系数，否则连续重试只会把限流踩得更死。

2.3 一次抓取的完整骨架

下面这段是抓取层，做了会话复用、重试退避、结构校验和随机间隔，直接换掉域名和字段名就能跑。

import time import random import requests from requests.adapters import HTTPAdapter from urllib3.util.retry import Retry BASE = "https://<平台域名>" ENDPOINT = BASE + "/ajax/side/hotSearch" HEADERS = { "User-Agent": ("Mozilla/5.0 (Windows NT 10.0; Win64; x64) " "AppleWebKit/537.36 (KHTML, like Gecko) " "Chrome/124.0.0.0 Safari/537.36"), "Accept": "application/json, text/plain, */*", "Accept-Language": "zh-CN,zh;q=0.9", "Referer": BASE + "/", } def build_session(): s = requests.Session() retry = Retry( total=3, # 最多重试 3 次 backoff_factor=0.8, # 退避系数：0.8s, 1.6s, 3.2s status_forcelist=[429, 500, 502, 503, 504], allowed_methods=["GET"], ) s.mount("https://", HTTPAdapter(max_retries=retry, pool_maxsize=4)) s.headers.update(HEADERS) return s def fetch_hot(session, timeout=(3.05, 8)): params = {"_": int(time.time() * 1000)} # 时间戳，绕开缓存 resp = session.get(ENDPOINT, params=params, timeout=timeout) resp.raise_for_status() # 4xx/5xx 直接抛，交给重试或上层 payload = resp.json() # 非 JSON 会抛 ValueError，等于一次隐式校验 if payload.get("ok") != 1: raise RuntimeError(f"接口业务码异常: {payload.get('ok')}") return payload["data"]["realtime"] if __name__ == "__main__": sess = build_session() rows = fetch_hot(sess) time.sleep(random.uniform(0.6, 1.4)) # 随机间隔，别做成固定节拍 print(len(rows), rows[0])

几个参数值得单独说。timeout=(3.05, 8)是元组形式，第一个是连接超时、第二个是读取超时，写成元组比写单值更可控，因为连接慢和响应慢是两类故障，排查时看哪个先触发就能定位。backoff_factor=0.8决定重试间隔的增长曲线，太小等于没退避，太大在批量任务里会拖垮整体耗时。pool_maxsize=4是连接池上限，抓单接口不需要开大，开到几十反而容易在对端留下明显特征。

raise_for_status()和resp.json()这两行是隐式校验，很多人省掉，结果拿到一个 200 的 HTML 风控页，json()抛错的位置离真正原因很远，排查时容易走偏。最后那个sleep是必须的，别把单次抓取写成循环里不间断的请求，公开接口对高频访问的反应通常不是报错，而是安静地返回空列表，那种“看起来成功其实没数据”的失败最难查。

3. 字段清洗：把 ranking、word、hot_value 压成一张能排序的表

接口返回的是嵌套字典列表，直接塞进 Excel 也能打开，但列顺序乱、热值是字符串、广告位混在里面，交给同事基本没法用。清洗这一步的目标很明确：每条记录变成一行，字段类型收敛到位，排序规则写在代码里而不是靠人眼。

3.1 字段映射与类型收敛

先把原始字段和目标列对一遍。常见的原始键名有word（词条文本）、num或raw_hot（热值）、rank（排名）、label_name（标签，比如“热”“新”）、category（分类）、is_ad（是否推广位）。不是每个接口都有全部字段，写代码时用.get()加默认值，比键不存在直接 KeyError 稳。

类型收敛的关键是热值。这个字段最不稳：有时是纯整数，有时是"1234567"这样的字符串，有时直接给你"123.4万"。三种混在一列里，Excel 会把它当文本，排序结果就成了 9 排在 100 后面那种荒谬情况。所以解析函数必须一次写全。

3.2 高亮标签、实体字符与控制字符

热搜词里常见<em>词</em>这种高亮包裹，还有&、"之类的 HTML 实体。不去掉的话，写进 Excel 会带尖括号，做关键词匹配时永远匹配不上。用html.unescape先解实体，再用正则去标签，顺序不能反，反了会把<em>解出来的尖括号又当成标签剥掉一部分。

更隐蔽的是控制字符。有些词条里带了\x0b、\x0c这类不可见字符，openpyxl写盘时不一定报错，但用 Excel 打开会提示“发现不可读取的内容”，点修复之后部分单元格被清空。我一般会在清洗阶段就按白名单把非法控制字符剔掉，宁可丢一个字符，也别让整个文件打不开。

import re import html TAG_RE = re.compile(r"<[^>]+>") CTRL_RE = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]") NUM_RE = re.compile(r"^(\d+(?:\.\d+)?)\s*(万|亿)?$") def clean_text(raw: str) -> str: if not raw: return "" text = html.unescape(str(raw)) # 先解实体：&amp; -> & text = TAG_RE.sub("", text) # 再去标签：<em>x</em> -> x text = CTRL_RE.sub("", text) # 剔控制字符，防止 xlsx 打不开 return re.sub(r"\s+", " ", text).strip() def parse_hot_value(raw) -> int: if isinstance(raw, (int, float)): return int(raw) if not raw: return 0 m = NUM_RE.match(str(raw).replace(",", "").strip()) if not m: return 0 scale = {"万": 10_000, "亿": 100_000_000}.get(m.group(2), 1) return int(float(m.group(1)) * scale)

parse_hot_value里有两个细节。一是先replace(",", "")，因为有些返回会带千分位逗号，正则匹配不上就直接归零，数据会莫名少掉一截。二是int(float(...) * scale)，先转 float 再乘再取整，避免"1.2亿"在整数运算里被截成 1。这个函数最好单独写几个测试用例跑一遍，三种格式各来一条，比线上发现热值全零要省事得多。

3.3 去重、排名校准与前 50 截断

接口给的rank不一定可靠：可能从 0 开始、可能有并列、可能在过滤掉广告位之后出现空洞。我的做法是不信任原始排名，直接按热值降序重排，然后reset_index生成连续名次。这样出来的表，第 1 名到第 50 名是连续的，同事拿去直接引用不会出现“第 7 名去哪了”的追问。

去重要放在重排之前还是之后，取决于业务口径。如果同一个词条在榜上出现了两次（比如话题和词条形式不同），按word去重保留第一条就够了；但如果要做词频统计，就得保留重复并另开一列计数。这里按前者处理，因为目标是“前 50 条榜单”，一条占一行才符合阅读习惯。

import pandas as pd def to_frame(items, limit=50): rows = [] for it in items: if it.get("is_ad"): # 推广位不进表 continue word = clean_text(it.get("word", "")) if not word: continue rows.append({ "word": word, "hot_value": parse_hot_value(it.get("num") or it.get("raw_hot")), "label": clean_text(it.get("label_name")), "category": clean_text(it.get("category")), }) df = pd.DataFrame(rows) if df.empty: return df df = df.drop_duplicates(subset=["word"], keep="first") df = df.sort_values("hot_value", ascending=False).reset_index(drop=True) df["rank"] = df.index + 1 return df.head(limit)

df.empty那个判断不是洁癖。接口正常返回但列表为空的情况确实会出现，不判空的话，下一行drop_duplicates不报错，但sort_values在空表上再reset_index之后df["rank"]赋值会得到一个空列，最后head(50)出来是空表，日志上看不出任何异常。加一行判断，至少能在日志里留下“本次返回 0 条”这种可追溯的记录。

4. 写入 Excel：openpyxl 在 pandas.to_excel 之外补的四个细节

df.to_excel()一行就能出文件，为什么还要绕到openpyxl？因为这个文件是要给人看的。列宽默认 8 字符，热搜词全被截成####；首行不冻结，滚到第 30 行就忘了哪列是热值；热值没有千分位，一眼分不清 12345 和 123456。这些不是“美化”，是可用性。

4.1 表头、冻结窗格与列宽

表头用深底白字加粗，是为了在投影或截图里也能看清边界。冻结首行配合自动筛选，是让同事拿到文件后第一件事就能自己按热值排序。列宽按内容估：排名 6 字符够用，热搜词给 42，热值 12，标签和分类各 10，时间列 20。给宽了浪费屏，给窄了变形。

4.2 数值格式与热值数据条

热值列必须同时做两件事：设成数值类型，加上千分位格式#,##0。只设格式不设类型，单元格里还是字符串，Excel 的排序会按字典序走；只设类型不设格式，五位六位数挤在一起，肉眼看不出量级差。数据条是可选加分项，用DataBarRule挂在热值列上，一眼就能看出头部词条和尾部词条的差距有多大。

4.3 按日期分 sheet 与原子写盘

每天抓一次的话，建议按日期建 sheet，而不是每次重写同一个。这样一周下来，同一个文件里就有一个可横向对比的历史。写入时先删同名 sheet 再新建，避免重复运行导致数据叠加。

文件被占用是 Windows 上的高频故障：Excel 开着文件时，wb.save()会抛PermissionError。稳的做法是先写到临时文件，再os.replace覆盖，这样即使中途失败，原文件也不会变成半截损坏状态。

import os import datetime as dt from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.formatting.rule import DataBarRule def dump_sheet(df, path, fetched_at=None): fetched_at = fetched_at or dt.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sheet_name = fetched_at[:10] # 一天一个 sheet，便于横向对比 wb = load_workbook(path) if os.path.exists(path) else Workbook() if "Sheet" in wb.sheetnames and len(wb.sheetnames) == 1: wb.remove(wb["Sheet"]) # 清掉默认空表 if sheet_name in wb.sheetnames: del wb[sheet_name] ws = wb.create_sheet(sheet_name, 0) headers = ["排名", "热搜词", "热值", "标签", "分类", "抓取时间"] ws.append(headers) for cell in ws[1]: cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="2F5597") cell.alignment = Alignment(horizontal="center", vertical="center") for _, r in df.iterrows(): ws.append([int(r["rank"]), r["word"], int(r["hot_value"]), r["label"], r["category"], fetched_at]) ws.freeze_panes = "A2" # 冻结表头 ws.auto_filter.ref = f"A1:F{ws.max_row}" for col, width in {"A": 6, "B": 42, "C": 12, "D": 10, "E": 10, "F": 20}.items(): ws.column_dimensions[col].width = width for row in ws.iter_rows(min_row=2, min_col=3, max_col=3): for cell in row: cell.number_format = "#,##0" # 千分位，别让热值变成科学计数 ws.conditional_formatting.add( f"C2:C{ws.max_row}", DataBarRule(start_type="min", end_type="max", color="63BE7B", showValue=True), ) tmp = path + ".tmp" wb.save(tmp) os.replace(tmp, path) # 原子替换，规避文件占用 return path

os.replace在 Windows 上是原子操作，目标文件被 Excel 打开时会直接抛错而不是写坏，这个行为正好符合预期：宁可报错让人关掉文件，也不要生成一个打不开的 xlsx。ws.auto_filter.ref的范围要用ws.max_row动态算，写死行数在数据不满 50 条时会多出一个空筛选区。

4.4 拼起来的入口

抓取、清洗、落盘串起来只要十几行，fetched_at只取一次，保证抓取时间和文件命名对得上：

if __name__ == "__main__": sess = build_session() items = fetch_hot(sess) df = to_frame(items, limit=50) if df.empty: raise SystemExit("本次未取到有效榜单数据，跳过写盘") path = dump_sheet(df, "hot50.xlsx") print(f"写入 {len(df)} 条 -> {path}")

raise SystemExit而不是静默返回，是为了让定时任务的返回值能反映真实状态。巡检脚本读退出码就知道今天有没有正常出数，不用再打开文件数行数。

5. 避坑与排查：五个把脚本跑挂的现场记录

这一节全是踩过的坑，按“现象 → 原因 → 解决”记，遇到同样的报错可以对着翻。

5.1 状态码 200，返回体却是空 JSON

现象：raise_for_status()不报错，resp.json()成功，但payload["data"]["realtime"]是空列表，或者ok字段直接不见了。

原因：请求特征太像脚本。缺Referer、缺Accept-Language、或者循环里没有间隔，服务端不一定给你 403，而是返回一个结构合法但内容为空的响应，这是最难查的一类失败。

解决：先把resp.text[:300]打出来看真实返回，再对照浏览器 cURL 逐项补头。同时把请求频率降下来，单次任务加 0.6 到 1.4 秒的随机间隔；批量抓多页时按页递增延迟。另外在解析前加一次结构断言，data和realtime都在才继续，避免后续代码在空数据上跑出一堆无意义告警。

5.2 Excel 提示“发现不可读取的内容”，点修复后部分单元格变空

现象：脚本正常结束，文件大小也对，双击打开时 Excel 弹修复提示，修复完发现某些热搜词只剩半截。

原因：单元格里写进了 XML 非法字符，典型是\x0b、\x0c这类从原始文本里带过来的控制符。openpyxl写盘阶段不校验，读到 Excel 才暴露。

解决：在clean_text里用CTRL_RE按范围剔除，保留\t、\n、\r之外的不可见字符一个不留。修完之后建议手动打开一次验证，别只看脚本退出码，这类问题是写盘成功、读取失败的典型。

5.3 热值列排序乱套，9 排在 100 后面

现象：同事在 Excel 里按热值降序排，结果98万排在120万前面，或者1,234,567被当文本靠左对齐。

原因：热值以字符串形式写入，或者混了万/亿单位没统一。文本排序按字符逐位比较，第一位是9的自然排在第一位是1的后面。

解决：入库前统一走parse_hot_value转成整数，写盘时int(r["hot_value"])再套#,##0格式。如果数据源里确实混了单位，宁可多写一个unit列保留原始单位，也不要把带单位的字符串直接塞进数值列。

5.4 PermissionError：文件被 Excel 占着

现象：本地跑没问题，定时任务里偶发PermissionError: [Errno 13]，第二天发现当天的数据没写进去。

原因：有人开着昨天的文件在看，或者上一轮任务没释放句柄。Windows 对打开中的 xlsx 有文件锁，wb.save()会直接失败。

解决：改成写临时文件再os.replace，失败时原文件保持完整；同时在任务外层加一次重试，间隔几十秒，多数情况下人已经把文件关了。文件名里带日期也能减少争抢，hot50_20240115.xlsx比固定的hot50.xlsx冲突概率低得多。

5.5 榜单里混进推广位，前 50 变成前 47

现象：抓回来 50 条，清洗完只剩 47 条，或者第 3 名是个明显的推广词。

原因：接口返回的列表里包含了推广条目，用is_ad之类的字段标记，也可能完全没有标记，只靠位置区分。

解决：优先用标记字段过滤；没有标记时，保留原始索引列，把置顶和推广位单独落一个 sheet 备查，主表只放真实榜单。过滤之后一定要重排名次，别沿用原始rank，否则表里会出现 1、2、4、5 这种断号，同事第一眼就会问。

6. 快照流水线：让每天 50 行数据能横向对比的两个技巧

单次抓取跑通之后，真正有价值的动作是把它变成每天固定的快照。我的做法是：目录按snapshots/2024/01/分层，每次落两个文件，一份hot50_20240115.xlsx给人看，一份同名.csv给脚本读。CSV 不带样式，读写都快，做对比时不用碰 openpyxl。

第一个技巧是行数校验加熔断。写入前先断言len(df) >= 40，低于阈值直接退出并保留上一份文件，不覆盖。榜单偶尔会返回残缺数据，如果不校验就写盘，历史快照里会混进一批只有十几行的脏记录，后面做趋势分析时得逐天回看，非常难受。

第二个技巧是涨榜对比。读取昨天和今天的 CSV，按word做外连接，算出hot_value的差值和新进榜标记：

import pandas as pd def diff_rank(today_csv, yest_csv, topn=10): today = pd.read_csv(today_csv)[["word", "hot_value", "rank"]] yest = pd.read_csv(yest_csv)[["word", "hot_value", "rank"]] merged = today.merge(yest, on="word", how="left", suffixes=("", "_y")) merged["delta"] = merged["hot_value"] - merged["hot_value_y"].fillna(0) merged["is_new"] = merged["rank_y"].isna() return merged.sort_values("delta", ascending=False).head(topn)

how="left"保证只保留今天在榜的词，is_new用rank_y是否为空判断，比比较热值可靠，因为新词条昨天的热值本来就是 NaN，直接相减会得到 NaN 而漏判。delta用fillna(0)兜底，新词条就等价于“从零涨到当前值”，排序时自然靠前，正好是运营最想看的那批。这套对比我一般放在抓取之后自动跑，输出前十行打印到日志里，出数当天就能看出有没有异常词条冲榜。

说个教训。早前我图省事，直接把df.to_excel()塞进定时脚本，结果某天接口返回的热值全是字符串，文件里一列文本，同事排序排了半小时才发现不对，回头找我核对。从那以后我每次改完清洗逻辑，都强制走一遍“抓取 → 落盘 → 手动打开文件排序一次”的完整链路，不管代码改得多小。榜单类数据的坑基本都藏在落盘之后，脚本退出码为 0 不代表文件能用。希望帮到你。

本文还有配套的精品资源，点击获取