项目介绍
研发投入、上市公司和专利数据来自不同文件，表头可能出现在不同位置，年份和公司名称也不完全统一。手工修一份表并不难，麻烦在于下一年、下一批文件还会出现新的“只差一点”。如果规则只对当前文件有效，合并完成也不能算真正完成。
项目过程
- 01
确认研发主表与年度专利文件的关系
研发投入主表记录上市公司及统计截止日期，专利资料按年度分文件保存。项目希望把同一家公司同一年的专利信息补充到研发记录旁边，让后续分析可以在公司与年份一致的条件下比较投入和产出。
查看处理方法与依据收起技术细节
- 02
识别两种表头格式
不同年份的表格不一定把字段名放在相同位置。脚本先检查当前列名中有没有必需字段；如果没有，就检查第一行是否存放了真正的字段名。识别成功后，将这一行设为表头，再保留下面的数据。
查看处理方法与依据收起技术细节
- 03
统一证券代码，并提取对应年份
证券代码先去除首尾空格和交易所字母后缀，再补足六位。研发记录的年份从统计截止日期提取，专利文件的年份从文件名提取；无法识别的日期或文件年份会在处理时报告。
查看处理方法与依据收起技术细节
- 04
以研发记录为主表合并专利信息
各年度专利表先纵向合并，再按“证券代码＋年份”与研发主表连接。这里使用左连接：研发记录全部保留，找到对应专利记录时补充字段，未匹配到的专利字段保持缺失。
查看处理方法与依据收起技术细节
- 05
核对匹配结果，再导出可分析的数据
脚本统计合并结果的总行数，以及“股票代码”非空的输出行数作为成功匹配数，再按指定格式保存 CSV 或 Excel。原课程任务另外进行了汇总核验：研发主表 24,078 条记录，成功匹配 21,605 条，匹配率约 89.73%；相关汇总记录单独保留。
查看处理方法与依据收起技术细节
滚轮缩放 · 拖动空白处移动 · 点击节点阅读
确认研发主表与年度专利文件的关系
研发投入主表记录上市公司及统计截止日期，专利资料按年度分文件保存。项目希望把同一家公司同一年的专利信息补充到研发记录旁边，让后续分析可以在公司与年份一致的条件下比较投入和产出。
查看处理方法与依据收起技术细节
把不同年份的专利资料整理成相同格式，再按公司与年份补充到研发表。项目交付的是合并数据与匹配说明。
1确认研发主表与年度专利文件的关系
- 读取研发投入主表
- 读取 2010—2021 年的 12 份年度专利文件
- 确认证券代码、日期与年度来源
- 准备按公司和年份匹配
明确的输入文件与关联字段，以及可重复执行的运行入口。
工具与方法：Python · Pandas · CSV / Excel · 命令行参数
查看处理方法与依据收起处理方法与依据
- 读取研发投入主表
- 读取 2010—2021 年的 12 份年度专利文件
- 确认证券代码、日期与年度来源
- 准备按公司和年份匹配
| 输入材料 | 记录内容 | 在合并中承担什么作用 |
|---|---|---|
| 研发主表 | 读取指定研发文件；要求证券代码和统计截止日期 | 由 --rd-file 指定 |
| 专利目录 | 按文件名排序读取目录中的全部 .xlsx 和 .csv；本次原任务共 12 年 | 由 --patent-dir 指定，代码没有写死只能读取 12 份 |
| 读取类型 | CSV 与 Excel 均按字符串读取字段 | 保留股票代码前导零，后续只单独解析需要的日期 |
| 输出位置 | 由 --output 指定文件路径，必要时创建上级目录 | 扩展名为 .csv 时导出 CSV，否则导出 Excel |
原课程任务使用 2010—2021 年共 48,468 条专利记录，与研发主表匹配。脚本本身按目录中的文件逐个读取，新增符合格式的年度文件后可以继续使用同样流程。
2识别两种表头格式
- 读取当前表格列名
- 列名包含必需字段 → 直接使用
- 否则检查第一行是否包含字段名
- 确认后调整表头；仍不符合要求则提示原因
字段名称一致、可以继续读取公司代码的年度表格。
工具与方法：Pandas · 必需字段检查 · 两类表头识别
查看处理方法与依据收起处理方法与依据
- 读取当前表格列名
- 列名包含必需字段 → 直接使用
- 否则检查第一行是否包含字段名
- 确认后调整表头；仍不符合要求则提示原因
| 遇到什么情况 | 程序检查什么 | 随后怎样处理 |
|---|---|---|
| 已有正确列名 | 研发表查找“证券代码”，专利表查找“股票代码” | 找到后直接复制表格继续处理 |
| 当前列名不符合要求 | 读取第一条数据行，将各项转为字符串并去首尾空格 | 若含必需字段，把这一行改为列名，并从第二条数据行开始保留 |
| 空表或仍找不到字段 | 抛出带具体字段名的错误 | 停止本次处理，不猜测字段位置 |
| 研发日期字段 | 表头处理后另外检查“统计截止日期” | 缺少时报告错误 |
例如专利表当前列名不含“股票代码”，但第一条数据行中包含该名称，程序就将这条数据行设为表头。代码只识别这两种位置，不扫描整份文件寻找任意行的表头。
3统一证券代码，并提取对应年份
- 证券代码去空格、去交易所后缀并补足六位
- 从研发统计截止日期提取年份
- 从专利文件名提取年份
- 用证券代码＋年份作为共同匹配条件
格式统一的六位证券代码和年份字段。
工具与方法：Pandas 字符串处理 · 正则表达式 · 日期解析
查看处理方法与依据收起处理方法与依据
- 证券代码去空格、去交易所后缀并补足六位
- 从研发统计截止日期提取年份
- 从专利文件名提取年份
- 用证券代码＋年份作为共同匹配条件
| 匹配字段 | 来源或处理规则 | 示意 |
|---|---|---|
| 证券代码 | 转字符串 → 去首尾空格 → 删除末尾“点＋英文字母”后缀 → 左侧补 0 至六位 | “ 000001.SZ ”得到“000001”；“1”得到“000001” |
| 研发年份 | 将“统计截止日期”解析为日期，再取年份；无效日期会报错 | 示例 2020-12-31 得到 2020 |
| 专利年份 | 在不含扩展名的文件名中寻找第一个 20xx 年份 | 示例“专利_2020.xlsx”得到 2020；找不到则报错 |
| 匹配条件 | 研发“证券代码＋年份”对应专利“股票代码＋年份” | 代码和年份必须同时相同 |
表中的代码、日期和文件名均为格式示例。补零处理不会把超过六位的字符串截断，脚本也没有验证代码是否真实属于上市公司；这一步完成的是字段格式统一。
4以研发记录为主表合并专利信息
- 将 12 份年度专利表纵向合并
- 以研发表作为左侧主表
- 按证券代码＋年份进行左连接
- 匹配成功时补入专利字段，未匹配时保留缺失
保留研发记录并补充专利字段的合并表，同时保留未匹配状态。
工具与方法：Pandas concat · 按公司与年份左连接 · 匹配结果检查
查看处理方法与依据收起处理方法与依据
- 将 12 份年度专利表纵向合并
- 以研发表作为左侧主表
- 按证券代码＋年份进行左连接
- 匹配成功时补入专利字段，未匹配时保留缺失
| 执行环节 | 实际规则 | 对结果的影响 |
|---|---|---|
| 年度表追加 | 把各年度专利表按行 concat，重新生成行索引 | 先形成一张含年份的专利表 |
| 匹配字段 | 左侧为证券代码、年份；右侧为股票代码、年份 | 使用左连接，保留研发表记录 |
| 未匹配 | 专利侧字段保持缺失，不填 0 | 区分没有匹配到资料与已记录的零专利 |
| 重复匹配 | 代码允许 many_to_many，没有在合并前自动去重或汇总 | 同一条件左侧 2 行、右侧 3 行，会产生 6 行匹配结果 |
假设研发表有某公司 2020 年的一条记录，专利表有同公司 2020 年的一条记录，输出将两边字段放在同一行。若专利表只有该公司 2019 年记录，则 2020 年研发记录保留，专利字段为空。
左连接保留主表记录，但输出行数不一定等于主表行数。重复匹配例子用于说明执行规则；原课程统计结果另外列在下一步。
5核对匹配结果，再导出可分析的数据
- 对合并结果统计总行数
- 统计“股票代码”非空的输出行数，作为匹配行数
- 按输出扩展名写入 CSV 或 Excel
- 对照原课程汇总记录，说明匹配数量与比例
合并数据文件、匹配数量与汇总验证说明。
工具与方法：行数与匹配率核验 · Pandas 导出 · 合成样本复现
查看处理方法与依据收起处理方法与依据
- 对合并结果统计总行数
- 统计“股票代码”非空的输出行数，作为匹配行数
- 按输出扩展名写入 CSV 或 Excel
- 对照原课程汇总记录，说明匹配数量与比例
| 核对或导出内容 | 怎样计算或执行 | 阅读时怎样理解 |
|---|---|---|
| 脚本总行数 | 直接统计合并后的行数 | 重复匹配会影响这个数字 |
| 脚本成功匹配数 | 统计输出中“股票代码”非空的行数 | 这是匹配输出行数，不是自动去重后的公司数量 |
| 文件格式 | CSV 使用 utf-8-sig 编码；Excel 不保存 DataFrame 行索引 | 均按指定路径保存 |
| 原任务汇总 | 研发主表 24,078 条，成功匹配 21,605 条，约 89.73% | 数字来自原课程汇总核验记录 |
| 公开样本 | 用于复现两类表头、代码统一与左连接 | 不用于重现原任务规模与统计总量 |
当前公开脚本自动打印的是合并输出总行数和匹配行数。匹配率、研发主表原始行数和 12 年汇总数字来自单独保存的原课程核验记录，网页将两部分分别说明。
结果
- 01
统一不同文件的表头、证券代码与年份，并将各年度专利数据纵向合并。
- 02
以研发数据为左表，按证券代码和年份补充专利字段，保留全部研发记录及未匹配状态。
- 03
输出合并表与匹配统计，完成后续分析所需的数据准备；本项目未开展统计分析或因果分析。
数据处理中的判断与操作
本项目完成的是数据处理。我先统一表头、证券代码和年份，再以研发数据为左表，按“证券代码＋年份”左连接专利数据：保留研发表的全部记录，匹配成功时补充专利字段，未匹配时保留缺失值。合并后检查匹配情况与记录数量，输出供后续研究使用的表格；本项目没有开展研发投入与专利产出之间的数据分析。
我与 AI 如何完成项目
Trae
个人完成。本人定义匹配键、数据质量规则与输出结构，审核自动识别结果并完成最终验证。
任务怎样被拆开
我把课程要求、各批Excel文件和希望得到的公司—年度面板交给Trae，先讨论哪些字段必须统一、哪些键适合连接，以及需要怎样记录匹配失败。
Trae负责哪些工作
Trae生成文件扫描、表头探测、字段别名、批量读取、双键匹配和质量报告代码。我负责决定证券代码与年份作为主要匹配键，并检查自动识别是否误读说明行、合并单元格和多级表头。
我怎样确认批量结果可以继续分析
我检查重复键、缺失值、匹配率、异常年份和未匹配清单。程序能够输出合并表只代表代码完成运行；只有数据损失能够被定位，结果才适合进入后续分析。
公开版本怎样保护原始资料
真实课程数据受到授权限制，公开仓库使用结构一致的合成样例。代码、字段规则和测试可以公开，真实公司数据不随仓库发布。
参考与使用说明
结果适用范围
- 真实原始数据受授权限制不公开，仓库只提供结构一致的合成样例。
- 专利数量不直接等于创新质量，后续研究仍需加入专利类型、引用与行业背景。
- 数据整合建立的是相关分析基础，不自动证明研发投入带来专利增长。