基于列合并多个CSV文件的高性能实现需求

数据老陈

2026-5-12

Got it, let's tackle this CSV merging task where the first two columns form a composite unique key—performance is a top priority here, so I'll share two robust approaches depending on your preference for scripting languages or command-line tools.

1. Python + Pandas (Balanced Readability & Performance)

Pandas is great if you want a maintainable script that handles edge cases (like missing keys, data type inconsistencies) out of the box, while still delivering solid performance with a few optimizations.

Key Optimizations for Performance:

Specify explicit data types to avoid memory-heavy auto-inference
Use composite indices to leverage fast lookups on the unique key
Skip unnecessary memory overhead with low_memory=False

Code Example:

import pandas as pd

def read_csv_with_composite_key(file_path):
    # Define data types for the first two key columns (adjust based on your actual data)
    dtype_spec = {0: 'string', 1: 'string'}
    return pd.read_csv(
        file_path,
        index_col=[0, 1],  # Treat first two columns as the unique composite key
        dtype=dtype_spec,
        low_memory=False
    )

# Load all three CSV files
df1 = read_csv_with_composite_key("file1.csv")
df2 = read_csv_with_composite_key("file2.csv")
df3 = read_csv_with_composite_key("file3.csv")

# Merge all DataFrames using the composite index (outer join retains all keys)
merged_df = df1.join([df2, df3], how="outer")

# Convert the composite index back to regular columns and save output
merged_df.reset_index().to_csv("merged_output.csv", index=False)

For Extra Large Files:

If you're dealing with multi-GB CSVs, swap Pandas with dask.dataframe—it uses lazy loading and parallel processing to avoid loading the entire dataset into memory.

2. AWK (Extreme Performance for Massive Files)

AWK is a command-line workhorse for text processing, and it’s unbeatable for speed with large CSV files. It processes data line-by-line, so memory usage stays minimal even for TB-scale files.

Code Example (save as `merge_csv.awk`):

BEGIN {
    FS = ","; OFS = ","  # Set input/output delimiter to comma
}

# Process first CSV: store all rows using the composite key as the lookup
NR == FNR {
    key = $1 "," $2
    row[key] = $0
    next
}

# Skip headers for the second and third CSVs
FNR == 1 { next }

# Process second CSV: append columns to existing keys, or add new keys
{
    key = $1 "," $2
    if (key in row) {
        # Remove the first two columns (since they're already in the stored row)
        sub(/^[^,]+,[^,]+,/, "", $0)
        row[key] = row[key] OFS $0
    } else {
        row[key] = $0
    }
}

# Process third CSV (same logic as second)
NR > FNR + FNR_PREV {
    key = $1 "," $2
    if (key in row) {
        sub(/^[^,]+,[^,]+,/, "", $0)
        row[key] = row[key] OFS $0
    } else {
        row[key] = $0
    }
    FNR_PREV = FNR
}

# Print all merged rows
END {
    for (key in row) {
        print row[key]
    }
}

Run the Script:

awk -f merge_csv.awk file1.csv file2.csv file3.csv > merged_output.csv

Quick Validation

Both approaches will produce output matching your test format—for example:

abc,xxx,a1,b1,c1,p1,q1,r1,x3,y3,z3
abc,yyy,a2,b2,c2,p2,q2,r2,x4,y4,z4
def,zzz,a3,b3,c3,p3,q3,r3,x1,y1,z1
def,pqr,a4,b4,c4,p4,q4,r4,x2,y2,z2

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

基于列合并多个CSV文件的高性能实现需求

Key Optimizations for Performance: #

Code Example: #

For Extra Large Files: #

Code Example (save as merge_csv.awk): #

Run the Script: #