Python+Pandas高效处理434份人口普查Excel文件的实战指南

当面对434个结构相似但细节各异的人口普查Excel文件时，传统的手动处理方式不仅耗时耗力，还容易出错。本文将带你用Python的Pandas库，在5分钟内完成从数据清洗到合并的全流程操作。

1. 环境准备与数据概览

在开始之前，确保已安装Python 3.7+版本和必要的库。打开终端或命令提示符，执行以下安装命令：

pip install pandas openpyxl xlrd

Pandas是数据处理的核心库，openpyxl和xlrd则是处理Excel文件所需的引擎。建议使用Jupyter Notebook进行交互式操作，方便实时查看每一步的结果。

434个文件按照"编号-子编号 描述性标题.xlsx"的格式命名，例如"1-1 各地区户数、人口数和性别比.xlsx"。观察文件名可以发现几个特点：

• 主编号(1-9)代表不同普查大类
• 子编号(a-c)表示城乡分类(城市/镇/乡村)
• 文件名包含关键指标信息

常见文件结构问题 包括：

• 表头可能出现在不同行
• 部分文件包含合并单元格
• 城乡分类可能体现在文件名或表格内
• 指标单位可能不一致

2. 批量读取与初步清洗

首先创建一个Python脚本，批量读取所有Excel文件。这里使用 glob 模块进行文件遍历：

import pandas as pd
import glob
from pathlib import Path

# 设置文件路径
data_folder = Path("人口普查数据/")
excel_files = glob.glob(str(data_folder / "*.xlsx"))

# 创建空列表存储数据
all_data = []

for file in excel_files:
    # 从文件名提取分类信息
    file_name = Path(file).stem
    main_cat = file_name.split()[0].split('-')[0]
    sub_cat = file_name.split()[0].split('-')[1] if '-' in file_name.split()[0] else ''
    
    # 读取Excel，跳过可能的空行
    df = pd.read_excel(file, header=None)
    
    # 定位真实表头（通常在第一非空行）
    header_row = df.dropna(how='all').index[0]
    df = pd.read_excel(file, header=header_row)
    
    # 添加文件来源标记
    df['数据来源'] = file_name
    df['主分类'] = main_cat
    df['子分类'] = sub_cat
    
    all_data.append(df)

这段代码处理了几个关键问题：

1. 自动识别真实表头位置
2. 保留原始文件名信息
3. 添加分类标记便于后续分析

提示：对于特别大的Excel文件，可以添加 usecols 参数只读取需要的列，节省内存。

3. 数据标准化处理

不同文件间的列名和格式需要统一。我们定义一个标准化函数：

def standardize_data(df):
    # 列名标准化
    df.columns = df.columns.str.strip().str.lower()
    
    # 处理常见列名变体
    col_mapping = {
        '地区': 'region',
        '性别': 'gender',
        '年龄': 'age',
        '人口数': 'population',
        '户数': 'households'
    }
    
    df.rename(columns=col_mapping, inplace=True)
    
    # 统一城乡分类
    if '城乡分类' not in df.columns:
        if 'a' in df['子分类'].iloc[0]:
            df['urban_rural'] = 'urban'
        elif 'b' in df['子分类'].iloc[0]:
            df['urban_rural'] = 'town'
        else:
            df['urban_rural'] = 'rural'
    
    # 处理特殊值
    df.replace(['-', 'NA', 'NULL'], pd.NA, inplace=True)
    
    # 数值列转换
    num_cols = ['population', 'households', 'age']
    for col in num_cols:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors='coerce')
    
    return df

标准化过程中需要特别注意：

• 不同文件可能使用"性别"/"sex"/"gender"等不同列名
• 城乡分类可能体现在列中或文件名中
• 特殊符号需要统一转换为NA值
• 数值列可能被误读为字符串

4. 智能合并与数据验证

将所有处理好的DataFrame合并为一个统一的数据集：

# 应用标准化函数
standardized_data = [standardize_data(df) for df in all_data]

# 智能合并：自动对齐相同列名
combined = pd.concat(standardized_data, ignore_index=True)

# 保存完整数据集
combined.to_parquet('combined_population.parquet', engine='pyarrow')

合并后建议进行数据质量检查：

# 检查各分类数据量
print(combined.groupby(['主分类', 'urban_rural']).size())

# 检查缺失值
missing_stats = combined.isnull().sum()
print(missing_stats[missing_stats > 0])

# 检查数值范围
print(combined.describe())

对于大型数据集，Parquet格式比CSV更节省空间且读取更快。如果仍需Excel输出，可以分卷保存：

# 分卷保存（每卷100万行）
for i, chunk in enumerate(np.array_split(combined, len(combined)//1000000 + 1)):
    chunk.to_excel(f'population_part_{i+1}.xlsx', index=False)

5. 高级技巧与性能优化

处理400+文件时，性能至关重要。以下是几个优化建议：

并行处理加速 ：

from concurrent.futures import ThreadPoolExecutor

def process_file(file):
    df = pd.read_excel(file)
    return standardize_data(df)

with ThreadPoolExecutor(max_workers=4) as executor:
    standardized_data = list(executor.map(process_file, excel_files))

内存优化技巧 ：

• 使用 dtype 参数指定列类型
• 对于分类变量，使用 category 类型
• 分块处理超大文件

# 指定数据类型示例
dtype_spec = {
    'region': 'category',
    'gender': 'category',
    'population': 'float32'
}

df = pd.read_excel(file, dtype=dtype_spec)

自动化数据质量报告 ：

def generate_data_report(df):
    report = {
        'total_records': len(df),
        'complete_cases': df.dropna().shape[0],
        'column_stats': {}
    }
    
    for col in df.columns:
        col_stats = {
            'type': str(df[col].dtype),
            'unique_values': df[col].nunique(),
            'missing': df[col].isnull().sum(),
            'sample_values': list(df[col].dropna().sample(3).values)
        }
        report['column_stats'][col] = col_stats
    
    return pd.DataFrame.from_dict(report, orient='index')

report = generate_data_report(combined)
report.to_html('data_quality_report.html')

这套方法不仅适用于人口普查数据，也可迁移到其他多文件数据整理场景。关键在于：

1. 系统化的文件命名规范
2. 灵活的列名映射策略
3. 严谨的数据验证流程
4. 适当的性能优化措施

实际项目中，我通常会先抽样检查几个代表性文件，确定处理逻辑后再批量应用。遇到特殊文件时，可以单独处理后再合并。