1995-2021年省级财政数据清洗实战：从混乱文本到规整面板数据（以转移支付为例）
1995-2021年省级财政数据清洗实战：从混乱文本到规整面板数据（以转移支付为例）
当我们第一次拿到省级财政数据时，往往会被原始文本的混乱格式所困扰——年份和数值紧密相连，省份之间没有明确分隔，数字串长得让人无从下手。这种"原始食材"状态的数据，就像未经处理的生鲜食材，需要经过清洗、切割、分类才能成为可用的"料理原料"。本文将以1995-2021年省级转移支付数据为例，手把手教你如何将一团乱麻的文本数据转化为规整的面板数据格式。
1. 理解原始数据的混乱本质
原始财政数据最常见的格式问题，往往源于数据发布方的技术限制或历史原因。以我们拿到的转移支付数据为例，主要存在三大"顽疾"：
- 年份粘连：不同年份的数字直接相连，如"199519961997"而非"1995,1996,1997"
- 缺乏分隔符：省份与数值之间、不同指标之间没有统一的分隔符号
- 格式不一致：部分年份带"年"字（如"2005年"），部分则没有
这种数据如果直接导入Excel，所有内容会挤在一个单元格里，完全无法分析。我们需要先理解数据的潜在结构：
省份名称 + 连续年份数字 + 各年数值注：实际数据中，省份名称后可能还包含"自治区"、"市"等行政级别标识，需要统一处理
2. 数据预处理：文本规范化
2.1 基础文本清洗
首先用文本编辑器（如VS Code、Notepad++）进行初步处理：
- 统一年份格式：用正则表达式将"1995"替换为"1995,"
- 查找：
(\d{4})年? - 替换：
$1,
- 查找：
- 标记省份边界：在省份名前添加特殊分隔符（如"||"）
- 查找：
(\w+省|\w+自治区|\w+市) - 替换：
||$1
- 查找：
提示：处理前建议备份原始文件，每个替换步骤单独保存不同版本
2.2 结构化分割
清洗后的文本可以按以下逻辑分割：
raw_text = "||北京市519799,508135,569283...||天津市327405,303992..." provinces = [p for p in raw_text.split("||") if p]得到列表结构：
[ "北京市519799,508135,569283...", "天津市327405,303992...", ... ]3. 使用Python进行自动化清洗
对于跨年度的省级数据，手动处理效率太低。下面用Python的Pandas库实现自动化清洗。
3.1 基础数据框架搭建
import pandas as pd import re def parse_province_data(text): # 提取省份名称 province = re.match(r"^(\w+省|\w+自治区|\w+市)", text).group(1) # 提取数值部分 numbers = re.sub(r"^\w+", "", text) values = [int(x) for x in numbers.split(",") if x] return province, values # 示例用法 province, values = parse_province_data("北京市519799,508135,569283")3.2 构建完整面板数据
假设我们有1995-2021共27年的数据：
years = list(range(1995, 2022)) all_data = [] for province_text in provinces: province, values = parse_province_data(province_text) if len(values) == len(years): for year, value in zip(years, values): all_data.append({ "省份": province, "年份": year, "转移支付": value }) df = pd.DataFrame(all_data)3.3 数据验证与修正
检查数据质量时需特别注意：
- 异常值检测：
df[df["转移支付"] < 0]找出负值 - 缺失值检查：
df.isnull().sum() - 跨年比对：同一省份相邻年份不应有剧烈波动
常见问题处理方案：
| 问题类型 | 检测方法 | 解决方案 |
|---|---|---|
| 数值缺失 | df.isnull() | 线性插值或标记为NA |
| 异常波动 | 年度增长率>50% | 核对原始文件或设为缺失 |
| 省份名称不一致 | df["省份"].unique() | 统一替换为标准名称 |
4. Excel替代方案：Power Query清洗
对于不熟悉编程的研究人员，Excel的Power Query提供了可视化清洗方案：
导入文本数据：
- 数据 → 获取数据 → 从文件 → 从文本/CSV
- 选择"分隔符"为"自定义"，输入"||"
拆分省份与数值：
- 添加"提取文本之前分隔符"列，分隔符为第一个数字
- 使用"拆分列"功能按逗号分隔数值
转置为面板格式：
- 选择所有年份列 → 转换 → 逆透视列
- 重命名列为"年份"和"数值"
关键操作截图要点：
- 使用"示例文件"功能定义拆分规则
- 在"高级编辑器"中直接修改M公式：
= Table.SplitColumn(#"上一步", "Column1", Splitter.SplitTextByEachDelimiter({","}, null, true), List.TransformMany({1..27}, each {"Y" & Text.From(_)}, (x,y) => y))
5. 进阶处理：多指标数据整合
当数据包含多个指标（如专项转移支付、一般转移支付）时，需要更复杂的处理：
- 识别指标标记：在原始文本中定位"专项"、"一般"等关键词
- 建立层级索引：使用MultiIndex处理"省份-年份-指标"三维数据
- 合并同类数据：对不同来源但同指标的数据进行一致性校验
示例代码结构：
multi_index = pd.MultiIndex.from_arrays( [df["省份"], df["年份"], df["指标"]], names=("省份", "年份", "指标") ) panel_data = df.set_index(multi_index)["数值"].unstack()6. 成果输出与分析准备
清洗后的数据应保存为标准格式：
- CSV格式：
df.to_csv("cleaned_transfer_payments.csv", index=False) - Excel多表工作簿：不同指标存为不同sheet
- Stata/dta格式：保留标签信息供计量分析
最终数据结构示例：
| 省份 | 年份 | 专项转移支付 | 一般转移支付 | 税收返还 |
|---|---|---|---|---|
| 北京市 | 1995 | 519799 | 302145 | 125487 |
| 北京市 | 1996 | 508135 | 298745 | 130228 |
7. 实际应用中的经验分享
在处理过数十个省级财政数据集后，我总结出几个避坑要点：
- 保留原始版本：每个处理步骤保存单独文件，命名如"01_raw.txt"、"02_cleaned.csv"
- 制作数据字典：记录每个字段的单位、含义、处理方式
- 自动化校验：编写检查脚本验证行数、唯一值等基本属性
- 特殊字符处理：注意全角字符、不可见字符等问题
一个实用的数据校验函数：
def validate_data(df): assert len(df) == 31 * 27, "省份×年份数量不匹配" # 31省份×27年 assert df["转移支付"].min() > 0, "存在非正数值" assert df["省份"].nunique() == 31, "省份数量异常" print("基本校验通过")