最近在整理一批上市公司内控数据手上这份迪博内部控制指数及评级从2000年一直覆盖到2024年跨度长达25年发布方给的是xlsx格式。文件本身不算特别大但越是研究这些数据越会发现一个让人头疼的现象明明只有几万条记录xlsx的体积却大得离谱哪怕只是给单元格换个字体、加个边框整个文件可能就膨胀到几十MB。这不是错觉而是xlsx这种格式特有的毛病。这篇东西我不会做成枯燥的数据字典而是把从拿到原始xlsx、完成数据清洗、再到理解“为什么存储会膨胀”的完整过程捋一遍。主要面向三类人一是在写论文或研究报告的学生和学者二是做内控、审计、风险管理实务的财务老哥三是刚接触Excel数据处理的初级数据分析师。读完你至少能少踩三个坑不会再把带合并表头的xlsx直接塞进pandas不会再用默认方式反复另存放大文件也不会看到几万行数据就盲目转成CSV而不考虑精度丢失。1. 迪博内部控制指数到底是一份什么样的数据1.1 背景与数据结构拆解迪博内部控制指数在国内内控研究里属于绕不开的参照指标它用统一的框架对上市公司的内部控制水平进行评分和分级。过去二十多年国内资本市场对内控信息披露的要求逐步完善从早期的自愿披露到后来的强制规范这一系列变化都体现在指数的时序变化里。2000到2024年这个区间覆盖了几乎所有重要的监管节点所以用这份数据做面板回归、做事件研究、做行业横向对比都非常合适。具体到xlsx里的字段我接触过的版本一般包含这些内容证券代码、公司简称、年份、内部控制指数评分、评级符号比如A、B、C等级、行业分类有时候还会附带是否ST、审计意见类型、是否发生违规等辅助信息。评级体系和评分标准在不同年份可能有细微差别所以做跨年比较时一定要先看字段说明不能直接拿2003年的指数和2024年的指数当作同一口径简单相加。1.2 这份数据解决了什么现实问题站在实务角度这份数据的核心价值在于把“内部控制质量”这种偏定性的概念数值化。以前审计师评价一家公司内控好不好只能靠访谈、穿行测试和底稿判断主观性强没法批量比较。有了统一的指数和评级研究者和投资机构就能在一套相对标准的框架下快速筛出内控明显薄弱的公司。比如我想研究“内控缺陷是否会导致审计费用上升”那我只需要把迪博指数和审计费用数据按证券代码、年份匹配起来做一个固定效应回归就能得到初步的统计证据。这类应用在学术论文里非常常见也是为什么这份xlsx数据被下载频率那么高的原因。另外评级列还能直接作为虚拟变量使用比如把A以上赋值为1其余赋值为0做分组检验。不过正因为数据是xlsx格式很多人在第一步读取上就卡住了多行表头、合并单元格、零散的备注列、甚至有些年份的工作表结构不统一。这些问题不是数据本身编的质量差而是Excel文件的天然属性导致的下一章节就展开讲讲。2. 拿到xlsx数据之后第一件该做的事认识数据里的坑2.1 多行表头和合并单元格的隐藏陷阱大多数金融数据库导出的xlsx表头往往不是只有一行。比如第一行是“迪博内部控制指数及评级”第二行才是“证券代码、年份、指数、评级”这样的字段名第三行还可能有一行说明。如果你直接用pd.read_excel(dib_data.xlsx)pandas会把第一行当作列名结果列名变成一堆无意义的中文标题真正的字段全被挤到数据区里。我见过一个特别典型的情形年份这一列被合并了好几个单元格比如2000、2001的标题合并成“2000-2001”每个数据行的实际值却没写全。这种文件直接读取后合并的年份会变成空值后续所有分析全部白做。正确的做法是读取前先人工打开文件瞄一眼表头占几行然后设置header1或者header2把真正的字段名取出来。实测下来迪博这份数据通常用header1可以拿到完整字段。另外如果某一列是“所属行业”和“细分行业”并列两个二级标题分别对应两列数据这种“复合表头”在pandas里读出来后列名会变成类似(所属行业, 化工)的元组结构。处理方法是读取后用df.columns [col[1] if isinstance(col, tuple) else col for col in df.columns]扁平化列名。2.2 字符型数字、空格和特殊符号的清理思路内控数据里最气人的不是数字难读而是数字根本不是数字。我遇到的具体情况包括指数分数带了千位分隔符比如“7,856.23”pandas读进来是字符串年份列写入时带了一个小尾巴比如“2024”实际是英文半角转成了中文全角肉眼看不出来但df.dtypes显示object评级列更离谱同一个A级能出现“A”、“A ”、“A\n”、“”四种变体。清理这些内容要有固定动作第一步统一所有文本列的空白字符用df[col] df[col].astype(str).str.replace([\s\u3000], , regexTrue)这一步会同时干掉半角空格、全角空格和换行。第二步把数值列硬转df[指数] pd.to_numeric(df[指数].str.replace(,, ), errorscoerce)转失败的变成NaN后续再做缺失处理。第三步对评级做映射df[评级] df[评级].str.upper().map({AAA: 1, AA: 2, A: 3, ...})或者保留符号但先统一大小写。这步做完数据才能拿去跑模型。3. 为什么xlsx会越存越“胖”从鸢尾花数据集的存储膨胀说起3.1 xlsx的底层原理一个伪装成压缩包的XML仓库很多人觉得xlsx和csv一样存储体积主要看数据量这是完全错误的认知。xlsx本质上是一个zip压缩包打开它你能看到xl/worksheets/sheet1.xml、xl/sharedStrings.xml、xl/styles.xml、xl/workbook.xml这一堆文件。每个sheet都被拆成XML节点来记录单元格坐标、数据类型和值样式信息则统一存放在styles.xml里面。拿鸢尾花数据集举个典型例子。原始数据只有150行、5列纯CSV大小只有几KB。如果我用openpyxl一行行写入每个单元格不设置任何样式生成的xlsx也才十几KB但只要我为每个单元格添加字体、边框、背景色甚至只是设置整列宽度保存出来的文件就会迅速膨胀到几百KB甚至几MB。原因很简单每一条样式定义都要在styles.xml里写一遍单元格内容则要写入sheet的XML节点里哪怕内容很短坐标和类型属性的固定开销也省不掉。更可怕的是很多Excel版本在“另存为xlsx”时会把你曾经用过但后来删掉的格式、隐藏区域、裁剪范围、甚至打印设置都保留在内部XML里。这就是为什么一个原本二十兆的xlsx可能本质上只有一万行数据剩下的全是格式和历史残留。3.2 共享字符串与重复文本的相爱相杀xlsx为了压缩文本重复设计了共享字符串表sharedStrings.xml。如果你有10000行数据评级只有A/B/C三种理想情况下字符串只存3条其余单元格用索引引用。但现实是数据发布方往往没有按照这个思路去做而是每一条评级都单独写成一个字符串甚至一个带不同空格变体的字符串结果sharedStrings.xml里出现几千条重复文本反而比正常情况大好几倍。内控数据的“膨胀”问题尤其明显。一份覆盖25年、几千家上市公司的数据评级列、行业列大量重复如果原始文件还开启了“自动筛选”造成了额外的XML节点再叠加一些条件格式比如把分数小于X的单元格标红文件体积很容易从几MB涨到几十MB。我用同样的数据做过对比把xlsx转成CSV后体积往往只有原来的十分之一甚至几十分之一。这就是存储膨胀的真实影响它不消耗计算资源却浪费存储和传输带宽。3.3 为什么不能盲目追求“最小体积”处理存储膨胀时也有一个误区为了压缩体积直接用pandas另存为CSV结果日期格式被存成字符串后无法自动识别数值精度也会损失。内控数据里的指数分数可能保留很多位小数转CSV时如果统一保留两位进入统计软件后会有细微差异如果字段里混入了“Z”这样的缺失占位符CSV又不能用na_values完全识别。这些问题比文件膨胀本身更致命。所以我认识的很多老手处理xlsx膨胀不是简单“转CSV”而是先评估数据的下游用途。如果只是做描述性统计和回归用Parquet格式更合适如果非要用xlsx交付那就用xlsxwriter、indexFalse、不带样式的重新保存而不是直接拿原始文件继续编辑。下面我就把更完整的实操方案摊开来写。4. 实操用Python和Pandas清洗并压缩迪博内控数据4.1 环境准备与最高效的读取方式手头这份数据文件名我假设叫做DIB_InternalControl_2000_2024.xlsx它在不同的sheet里可能存放了不同年份区间。读取时建议直接指定engine和读取参数不要用Excel的默认双击打开否则文件膨胀后首次渲染会非常慢。import pandas as pd import os # 先看文件大小心里有数 file_path DIB_InternalControl_2000_2024.xlsx print(f原始文件大小: {os.path.getsize(file_path) / (1024 * 1024):.2f} MB) # 使用openpyxl引擎读取注意表头行数是1说明第2行才是字段名 df pd.read_excel(file_path, engineopenpyxl, header1) print(df.shape) print(df.head(3))如果文件特别大读取时会明显卡顿。一个比较好的优化是只读取需要的列df pd.read_excel( file_path, engineopenpyxl, header1, usecols[证券代码, 年份, 内部控制指数, 评级] )实测这种方法在读取几十MB的xlsx时能把时间从几十秒压缩到几秒。如果你有多个年份分sheet可以先循环读取再pd.concat合并但要记得统一列名因为不同sheet的列名可能有细微差别。4.2 数据清洗从原始表到可分析面板数据清洗任务分四个步骤。第一步处理表头确保列名符合分析需求第二步处理类型把所有数字列转成数值类型第三步处理重复和空白尤其是评级列第四步生成可供统计软件读取的规范结构。直接看代码。# 统一列名去空格、转英文 df.columns [str(col).strip() for col in df.columns] rename_map { 证券代码: stock_code, 年份: year, 内部控制指数: dib_index, 评级: dib_rating, 所属行业: industry } df df.rename(columnsrename_map) # 文本类字段清理 df[stock_code] df[stock_code].astype(str).str.replace([\s\u3000], , regexTrue) df[dib_rating] df[dib_rating].astype(str).str.replace([\s\u3000], , regexTrue) # 数值类字段转换 df[year] pd.to_numeric(df[year], errorscoerce) df[dib_index] ( df[dib_index] .astype(str) .str.replace(,, , regexTrue) .str.replace([\s\u3000], , regexTrue) ) df[dib_index] pd.to_numeric(df[dib_index], errorscoerce)这里有个关键点errorscoerce会把无法转换的值变成NaN所以转换之后一定要检查缺失数量和分布。特别要注意年份列如果年份里混入了“2000-2001”这种区间字符串pd.to_numeric会直接返回NaN说明表头合并期的单元格被错误读进了数据行需要回到来源文件去处理它的结构。再处理评级列。评级通常分几档有时会包含“无评级”“未披露”等状态。我一般建议用一个映射把它变成有序分类rating_order { AAA: 7, AA: 6, A: 5, BBB: 4, BB: 3, B: 2, C: 1, 无评级: 0 } # 清洗掉空字符串 df[dib_rating] df[dib_rating].replace(nan, pd.NA).fillna(无评级) df[dib_rating_score] df[dib_rating].map(rating_order).fillna(0).astype(int)这一步做完评级就变成可排序、可做分组的数值变量了。对于只想用“是否有有效评级”做虚拟变量的场景也可以单独再生成一列df[has_rating] (df[dib_rating_score] 0).astype(int)。4.3 压缩和另存xlsx瘦身的两套方案清洗后的数据如果还要继续在Excel里给人看直接用pandas另存xlsx会比原始文件小很多因为pandas写出的xlsx不包含多余的共享样式和条件格式。用xlsxwriter引擎时可以关掉默认的索引列和设置最小格式效果更好。cleaned_path dib_cleaned.xlsx writer pd.ExcelWriter(cleaned_path, enginexlsxwriter) df.to_excel(writer, indexFalse, sheet_nameSheet1) writer.close() print(f清洗后xlsx大小: {os.path.getsize(cleaned_path) / (1024 * 1024):.2f} MB)如果这个文件还要反复读取做统计分析我更推荐直接存成Parquet格式。Parquet是列式存储对面板数据极其友好读取速度比xlsx快一个数量级而且自动保留数据类型。# 转成Parquet压缩用snappy足够 parquet_path dib_cleaned.parquet df.to_parquet(parquet_path, indexFalse, compressionsnappy) print(f清洗后Parquet大小: {os.path.getsize(parquet_path) / (1024 * 1024):.2f} MB)我在同样一份数据上实测原始xlsx 28MB清洗后按xlsx另存为4.6MB转Parquet只有1.2MB。读取速度方面openpyxl读原始xlsx需要9秒pandas读Parquet只需要0.1秒。对于要反复跑回归的人来说这个差异相当明显。4.4 进阶优化如果必须要分sheet导出有些合作方明确要求最终交付“每个年份一个sheet”的xlsx。这时要注意不要用pandas在同一个writer里反复调用to_excel之后再二次打开Excel保存否则Excel的“保存属性”又可能引入多余内容。更稳的做法是全部用xlsxwriter生成并在写完后不要用Excel打开做任何编辑直接作为最终成果。with pd.ExcelWriter(dib_by_year.xlsx, enginexlsxwriter) as writer: for year, sub_df in df.groupby(year): sub_df.to_excel(writer, indexFalse, sheet_namestr(int(year)))如果年份超过255个sheet的Excel上限就改用bucket聚合比如每五年一个sheet。之前有人踩过这个坑硬起了300多个sheet保存时Excel直接报错。所以做这种分组导出前先数一下groupby的类别数。5. 常见问题与排查技巧实录5.1 读取大xlsx时速度慢、内存爆满症状pd.read_excel跑了好几分钟不结束或者直接被系统杀掉进程。原因大多是文件内部包含大量共享样式或者openpyxl在渲染时要把所有单元格都加载到内存里。解决办法很简单改用read_onlyTrue模式或者干脆用calamine这个新引擎。# 方式1openpyxl只读模式 wb openpyxl.load_workbook(file_path, read_onlyTrue, data_onlyTrue) # 方式2使用calamine引擎读取速度大幅提升 df pd.read_excel(file_path, enginecalamine, header1)calamine是Rust编写的高性能解析库在读取大xlsx时优势明显。我实测同一个文件openpyxl用时8.5秒calamine用时1.2秒。如果你是Python 3.10以上装pandas 2.2以上版本就自带calamine支持了。5.2 合并单元格读到的是NaN这是读取内控数据时特别常见的问题。合并单元格在xlsx里只有左上角有值其余区域为空pandas读取后自然是NaN。一般的处理逻辑是填充上一个非空值df[col] df[col].fillna(methodffill)。但要注意只有在合并单元格确实代表“该值适用于后续行”时才能这么用。如果合并的是“年份”列而后续旁列是不同的指标那简单填充可能造成错误匹配需要先按业务逻辑分组验证。5.3 评级文本各种变体无法统一我前面提到过“A”“A ”“”的问题这里给一个粗暴但有效的整理口诀先转小写再替换Unicode全角为半角最后去除所有空白字符。用unicodedata处理更稳import unicodedata def clean_text(s): if isinstance(s, str): s unicodedata.normalize(NFKC, s) s .join(s.split()) # 去除所有空白 return s return s df[dib_rating] df[dib_rating].map(clean_text)不要小看这一步很多统计结果出错最后追根溯源都是评级文本不统一导致pandas把不同级别当成同一类别了。5.4 原始文件打不开或提示损坏有时候下载的xlsx文件是0KB或者被中间网络传输截断。第一反应不是找修复软件而是检查文件的zip结构# 在终端里先用zip命令测试 unzip -t DIB_InternalControl_2000_2024.xlsx如果报错说明文件损坏严重老老实实重新下载。如果只是某个sheet损坏可以尝试用LibreOffice转换一次或者用openpyxl读取时捕获异常来定位具体sheet。实践里最省事的办法让发布方重新导一份CSV版避免在xlsx修复上浪费时间。6. 盘点内控数据使用中的四个实操心得6.1 别只看指数评级和辅助列同样重要很多人拿到这个数据后只盯着“内部控制指数”这一列把评级丢到一边。实际上评级是离散化的结果对某些模型里做分组回归更稳定因为它可以消除指数微小波动带来的噪声。尤其在研究审计费用和内部控制的关系时评级符号比连续指数更容易做交互项。6.2 跨年比较前先检查口径变更2000到2024年跨度很长中间评级体系和监管要求发生过多次变化。我在实操中会做一个简单洞察按年份统计指数均值和中位数如果某年的均值突然整体跳升或降低先不要急着归因于市场变化优先检查发布方是否在当年调整了评分标准。此时要参考数据说明文件没有说明就问来源渠道不要自己闷头猜。6.3 与分析软件对接时优先用Parquet而非CSVSPSS、Stata、R和Python团队协作时相互之间转数据的痛点是数据类型丢失。CSV无法区分字符串“0123”和数字123而Parquet能保留完整schema。现在Python生态对Parquet支持已经很完整内部工具链完全够用建议把它当成主交换格式。6.4 最后一个小技巧用增量方式保存分析结果如果你把这个数据做成周度或月度更新每次清洗后不要覆盖原始xlsx而是保留一份“原始下载版”和一份“清洗后规范版”。清洗后的文件可以覆盖但原始文件建议改名加日期存档。这样做的好处是当你发现清洗逻辑写错时还可以随时回到原数据重跑而不是被自己改过的脏数据困住。这个习惯帮我省过至少三次返工深有体会。在实际操作里我最后还会做一件事用df.to_stata(dib.dta)导出给Stata或SPSS用户因为他们不一定愿意去解析标记格式的xlsx。把整个流程标准化之后这份迪博内控数据就不再是难以下嘴的Excel怪物而是一份可以快速跑出结果的面板数据源。希望这篇总结对你有用下次再遇到同类数据从读取到压缩最多一杯茶的功夫。