Python多文件正确合并问题：合并后数据混乱排查

问题现象 #

使用Python合并多个CSV文件后，生成的文件出现数据错位、空值冗余、列数不一致的混乱情况：

期望的合并格式 #

737224975,69450.10000000,0.00002000,1.38900200,1716854400003,False,True
737224976,69450.10000000,0.00010000,6.94501000,1716854400003,False,True
737224977,69450.10000000,0.00010000,6.94501000,1716854400003,False,True

实际混乱的输出格式 #

735704412,69065.44000000,0.00291000,200.98043040,1716819665729,False,True,737224975,69450.10000000,0.00002000,1.38900200,1716854400003
735704413.0,69065.47,0.02,1381.3094,1716819665839.0,True,True,,,,,
735704414.0,69065.46,0.0001,6.906546,1716819665839.0,True,True,,,,,
735704415.0,69065.46,0.03294,2275.0162524,1716819665839.0,True,True,,,,,
735704416.0,69065.44,0.07247,5005.1724368,1716819665839.0,True,True,,,,,
735704417.0,69065.42,0.0001,6.906542,1716819665839.0,True,True,,,,,
735704418.0,69065.24,0.0001,6.906524,1716819665839.0,True,True,,,,,
735704419.0,69064.68,0.02,1381.2936,1716819665844.0,True,True,,,,,
735704420.0,69064.68,0.02588,1787.3939184,1716819665844.0,True,True,,,,,
735704421.0,69064.55,0.0001,6.906455,1716819665844.0,True,True,,,,,
,,,,,False,True,737224976.0,69450.1,0.0001,6.94501,1716854400003.0

现有合并代码 #

import os
import re
import pandas as pd

# Define the directory where your CSV files are located
current_directory = os.getcwd()
print(current_directory)

# Construct the path to the "data" folder in the parent directory
directory = os.path.join(current_directory, "static\data")
print(directory)

# Define the naming scheme pattern
pattern = re.compile(r'BTCFDUSD-trades-(\d{4}-\d{2}-\d{2}).csv')

# Function to extract date from filename
def extract_date(filename):
    match = pattern.search(filename)
    if match:
        return match.group(1)
    else:
        return None

# Get list of CSV files in the directory
csv_files = [f for f in os.listdir(directory) if f.endswith('.csv')]

# Sort the files based on date
csv_files_sorted = sorted(csv_files, key=lambda x: extract_date(x))

# Process the files in sorted order
for filename in csv_files_sorted:
    # Your processing logic goes here, for example:
    # with open(os.path.join(directory, filename), 'r') as file:
    #     data = file.read()
    print(filename)

# Check if there are any files to process
if not csv_files_sorted:
    print("No CSV files found to process.")
else:
    # Read and concatenate the CSV files in sorted order
    merged_data = pd.DataFrame()
    for filename in csv_files_sorted:
        file_path = os.path.join(directory, filename)
        df = pd.read_csv(file_path)
        merged_data = pd.concat([merged_data, df], ignore_index=True)

    # Extract the first and last date for the new file name
    first_date = extract_date(csv_files_sorted[0])
    last_date = extract_date(csv_files_sorted[-1])
    
    # Define the new filename
    new_filename = f"BTCFDUSD-trades_{first_date}_to_{last_date}.csv"
    new_file_path = os.path.join(directory, new_filename)
    
    # Save the merged dataframe to the new CSV file
    merged_data.to_csv(new_file_path, index=False)
    
    print(f"Merged file saved as: {new_file_path}")

问题原因 #

核心问题是待合并的CSV文件列结构不一致：

• 部分CSV存在列数不同、列名拼写/大小写差异，或部分文件带表头、部分不带表头的情况。
• Pandas的concat默认按列名对齐数据，列名不匹配时，缺失列会填充空值，多余列会被保留，最终导致输出数据错位、空值冗余。

解决方法 #

1. 统一所有CSV的列结构 #

先检查所有待合并CSV的列数、列名是否完全一致，若部分文件无表头，读取时手动指定列名：

# 替换为实际的列名，确保与所有CSV的列对应
COLUMNS = ["id", "price", "amount", "total", "timestamp", "flag1", "flag2"]

# 读取无表头的CSV
df = pd.read_csv(file_path, header=None, names=COLUMNS)
# 读取有表头的CSV（确保表头与COLUMNS一致）
df = pd.read_csv(file_path, usecols=COLUMNS)

2. 优化合并逻辑 #

避免循环中反复拼接DataFrame，先收集所有DataFrame到列表再一次性合并，效率更高且稳定：

dfs = []
COLUMNS = ["id", "price", "amount", "total", "timestamp", "flag1", "flag2"]

for filename in csv_files_sorted:
    file_path = os.path.join(directory, filename)
    # 根据实际情况选择是否指定header
    df = pd.read_csv(file_path, names=COLUMNS, header=None)
    dfs.append(df)

# 一次性合并所有DataFrame
merged_data = pd.concat(dfs, ignore_index=True)

3. 提前验证数据一致性 #

合并前打印每个文件的列信息，快速定位不一致的文件：

for filename in csv_files_sorted:
    file_path = os.path.join(directory, filename)
    df = pd.read_csv(file_path)
    print(f"文件 {filename}：列数={len(df.columns)}，列名={list(df.columns)}")

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