如何用Pandas批量合并双目录对应Excel文件至第三方目录

我懂你现在的痛点——已经搞定了两类CSV的清洗转Excel，就差自动把同州的文件配对合并，批量存到目标目录对吧？咱们一步步来解决这个问题，先修复原代码里的小问题，再实现自动匹配合并的逻辑。

第一步：优化原清洗函数，规范文件输出路径 #

原代码里读取文件的时候有个小bug：直接用pd.read_csv(pop_file)会找不到文件，因为os.listdir返回的只是文件名，不是完整路径。另外，把清洗后的文件放到单独的目录里，后续合并会更方便。咱们先修改这两个函数：

import requests, re, logging, sys
from os import path
import os
import pandas as pd
import numpy as np
import matplotlib
import matplotlib.pyplot as plt
from pathlib import Path
import shutil

# 定义所有路径，用Pathlib管理更直观，跨平台友好
POP_INPUT_DIR = Path("Path A")  # 你的人口普查CSV目录
ETHNIC_INPUT_DIR = Path("Path B")  # 你的族裔CSV目录
POP_CLEANED_DIR = Path("temp_pop_cleaned")  # 人口清洗后的Excel临时目录
ETHNIC_CLEANED_DIR = Path("temp_ethnic_cleaned")  # 族裔清洗后的Excel临时目录
OUTPUT_MASTER_DIR = Path("Path C")  # 最终Master文件保存目录

# 创建所有需要的目录，不存在则自动创建
for dir_path in [POP_CLEANED_DIR, ETHNIC_CLEANED_DIR, OUTPUT_MASTER_DIR]:
    dir_path.mkdir(parents=True, exist_ok=True)

def pop_data_clean():
    for pop_file in os.listdir(POP_INPUT_DIR):
        pop_filename = os.fsdecode(pop_file)
        if pop_filename.endswith('.csv'):
            # 修复文件路径问题：拼接完整路径读取CSV
            full_file_path = POP_INPUT_DIR / pop_file
            population = pd.read_csv(full_file_path)
            # 选择需要的列
            population = population[[ 'STATE', 'COUNTY', 'STNAME', 'CTYNAME' ,'YEAR' ,'POPESTIMATE', 'POPEST_MALE','POPEST_FEM' ,'AGE18PLUS_TOT', 'AGE18PLUS_MALE', 'AGE18PLUS_FEM','AGE65PLUS_TOT', 'AGE65PLUS_MALE','AGE65PLUS_FEM', 'MEDIAN_AGE_TOT', 'MEDIAN_AGE_MALE','MEDIAN_AGE_FEM']]
            # 重命名列
            population = population.rename(columns={
                'STNAME':'STATE NAME',
                'CTYNAME':'CITY NAME',
                'POPESTIMATE':'POPULATION ESTIMATE',
                'POPEST_MALE':'TOTAL MALE POPULATION',
                'POPEST_FEM':'TOTAL FEMALE POPULATION',
                'AGE18PLUS_TOT':'TOTAL POPULATION ABOVE 18',
                'AGE18PLUS_MALE':'TOTAL MALE POPULATION ABOVE 18',
                'AGE18PLUS_FEM':'TOTAL FEMALE POPULATION ABOVE 18',
                'AGE65PLUS_TOT':'TOTAL POPULATION ABOVE 65',
                'AGE65PLUS_MALE':'TOTAL MALE POPULATION ABOVE 65',
                'AGE65PLUS_FEM':'TOTAL FEMALE POPULATION ABOVE 65',
                'MEDIAN_AGE_TOT':'MEDIAN AGE',
                'MEDIAN_AGE_MALE':'MEDIAN MALE AGE',
                'MEDIAN_AGE_FEM':'MEDIAN FEMALE AGE'})
            # 提取州缩写：匹配文件名开头的两个大写字母（比如AL_CensusData.csv里的AL）
            state_match = re.match(r'^([A-Z]{2})_', pop_filename)
            if state_match:
                state_code = state_match.group(1)
                # 保存到指定临时目录，命名为[州代码]_PopCleaned.xlsx
                output_path = POP_CLEANED_DIR / f"{state_code}_PopCleaned.xlsx"
                population.to_excel(output_path, index=False)
                print(f"已生成人口清洗文件：{output_path}")

def ethnic_data_clean():
    for ethn_file in os.listdir(ETHNIC_INPUT_DIR):
        ethn_filename = os.fsdecode(ethn_file)
        if ethn_filename.endswith('.csv'):
            # 修复文件路径问题
            full_file_path = ETHNIC_INPUT_DIR / ethn_file
            Ethnic = pd.read_csv(full_file_path)
            # 简化AGEGRP过滤逻辑
            Ethnic = Ethnic[~Ethnic['AGEGRP'].isin(range(1, 19))]
            # 选择需要的列
            Ethnic = Ethnic[['WA_MALE', 'WA_FEMALE', 'BA_MALE', 'BA_FEMALE', 'IA_MALE', 'IA_FEMALE', 'AA_MALE', 'AA_FEMALE', 'NA_MALE', 'NA_FEMALE', 'NH_MALE', 'NH_FEMALE', 'H_MALE', 'H_FEMALE']]
            # 重命名列
            Ethnic = Ethnic.rename(columns={
                'WA_MALE': 'White Alone Male Population',
                'WA_FEMALE':'White Alone Female Population',
                'BA_MALE': 'Black or African American Male Population',
                'BA_FEMALE':'Black or African American Female Population',
                'IA_MALE':'American Indian and Alaska Native Male Population',
                'IA_FEMALE':'American Indian and Alaska Native Female Population',
                'AA_MALE':'Asian Male Population',
                'AA_FEMALE':'Asian Female Population',
                'NA_MALE':'Native Hawaiian and Other Pacific Islander Male Population',
                'NA_FEMALE':'Native Hawaiian and Other Pacific Islander Female Population',
                'NH_MALE': 'Non-Hispanic Male population',
                'NH_FEMALE': 'Non-Hispanic Female population',
                'H_MALE': 'Hispanic Male population',
                'H_FEMALE':'Hispanic Female population'})
            # 提取州缩写
            state_match = re.match(r'^([A-Z]{2})_', ethn_filename)
            if state_match:
                state_code = state_match.group(1)
                # 保存到指定临时目录，命名为[州代码]_EthnicCleaned.xlsx
                output_path = ETHNIC_CLEANED_DIR / f"{state_code}_EthnicCleaned.xlsx"
                Ethnic.to_excel(output_path, index=False)
                print(f"已生成族裔清洗文件：{output_path}")

第二步：实现批量匹配合并逻辑 #

现在咱们写一个函数，遍历人口清洗后的文件，自动匹配对应州的族裔文件，合并后保存到目标目录：

def merge_master_files():
    # 遍历人口清洗后的所有Excel文件
    for pop_cleaned_file in POP_CLEANED_DIR.glob("*.xlsx"):
        # 提取文件名中的州代码（比如AL_PopCleaned.xlsx里的AL）
        filename = pop_cleaned_file.stem
        state_code = filename.split('_')[0]
        # 拼接对应州的族裔清洗文件路径
        ethnic_cleaned_file = ETHNIC_CLEANED_DIR / f"{state_code}_EthnicCleaned.xlsx"
        
        # 检查族裔文件是否存在，避免报错
        if ethnic_cleaned_file.exists():
            # 读取两个DataFrame
            pop_df = pd.read_excel(pop_cleaned_file)
            ethnic_df = pd.read_excel(ethnic_cleaned_file)
            
            # 合并DataFrame（因为你已经确保行数一致，直接按列合并）
            master_df = pd.concat([pop_df, ethnic_df], axis=1)
            
            # 保存Master文件到目标目录
            master_output_path = OUTPUT_MASTER_DIR / f"{state_code}_Master.xlsx"
            master_df.to_excel(master_output_path, index=False)
            print(f"已生成Master文件：{master_output_path}")
        else:
            print(f"警告：未找到州代码{state_code}对应的族裔文件，跳过合并")

第三步：执行完整流程 #

最后，调用这三个函数就能完成整个批量处理：

# 先执行两类数据的清洗
pop_data_clean()
ethnic_data_clean()
# 再执行同州文件的合并
merge_master_files()

关键细节说明 #

• 路径管理：用Pathlib替代传统的os.path，路径拼接更直观，还能自动处理Windows和Linux的路径分隔符差异。
• 州代码提取：用正则表达式^([A-Z]{2})_匹配文件名开头的州缩写，比固定截取字符更鲁棒，就算文件名后续格式有小变化也能正常提取。
• 合并逻辑：因为你已经确保两行数一致，用pd.concat(axis=1)直接按列合并就好；如果后续需要按特定列匹配，可以换成pd.merge。
• 容错处理：检查族裔文件是否存在，避免因文件缺失导致报错，还会打印提示信息方便排查。

内容的提问来源于stack exchange，提问作者Ryan Adams