基于第三方文件指定列的两个CSV文件列级对比需求

Got it, let's work through this problem step by step. You need to compare File 1 and File 2 column-by-column, focusing only on rows where Column A matches the values in File 3. The key constraint here is that column counts can vary (but stay consistent between File 1 and 2), so our solution needs to be flexible enough to adapt.

First, let's define sample input files to make this concrete:

Sample File 1 (file1.csv):

A,B,C,D
1,apple,red,round
2,banana,yellow,long
3,grape,purple,small

Sample File 2 (file2.csv):

A,B,C,D
1,apple,green,round
2,banana,yellow,short
3,grape,purple,small

Sample File 3 (file3.csv):

A
1
2

Solution 1: Python Script (Flexible & Easy to Modify) #

Python is great here because it handles dynamic column structures seamlessly using dictionaries. This script will generate a structured report of all mismatched columns for the target rows.

import csv

def compare_files(file1_path, file2_path, file3_path, output_report_path):
    # Step 1: Load all target A values from File 3
    with open(file3_path, 'r', newline='') as f3:
        csv_reader = csv.DictReader(f3)
        target_a_values = {row['A'] for row in csv_reader}

    # Step 2: Load File 1 data, keyed by Column A (only for target values)
    file1_data = {}
    with open(file1_path, 'r', newline='') as f1:
        csv_reader = csv.DictReader(f1)
        column_headers = csv_reader.fieldnames  # Capture all headers (works for any column count)
        for row in csv_reader:
            a_val = row['A']
            if a_val in target_a_values:
                file1_data[a_val] = row

    # Step 3: Compare File 2 rows against File 1 and generate report
    with open(file2_path, 'r', newline='') as f2, open(output_report_path, 'w', newline='') as report_file:
        csv_reader = csv.DictReader(f2)
        # Set up report headers
        report_writer = csv.DictWriter(
            report_file,
            fieldnames=['A', 'Mismatched_Columns', 'File1_Values', 'File2_Values']
        )
        report_writer.writeheader()

        for row in csv_reader:
            a_val = row['A']
            # Skip rows not in File 3's target list
            if a_val not in target_a_values:
                continue
            # Handle case where row exists in File 2 but not File 1
            if a_val not in file1_data:
                report_writer.writerow({
                    'A': a_val,
                    'Mismatched_Columns': 'NOT_FOUND_IN_FILE1',
                    'File1_Values': 'N/A',
                    'File2_Values': ', '.join([row[col] for col in column_headers if col != 'A'])
                })
                continue

            # Compare each column (skip Column A)
            mismatched_cols = []
            file1_values = []
            file2_values = []
            for col in column_headers:
                if col == 'A':
                    continue
                val1 = file1_data[a_val][col]
                val2 = row[col]
                if val1 != val2:
                    mismatched_cols.append(col)
                    file1_values.append(f"{col}:{val1}")
                    file2_values.append(f"{col}:{val2}")

            # Write mismatch to report if any found
            if mismatched_cols:
                report_writer.writerow({
                    'A': a_val,
                    'Mismatched_Columns': ', '.join(mismatched_cols),
                    'File1_Values': ', '.join(file1_values),
                    'File2_Values': ', '.join(file2_values)
                })

# Example usage (update paths as needed)
compare_files('file1.csv', 'file2.csv', 'file3.csv', 'mismatch_report.csv')

Key Features: #

• Automatically adapts to any number of columns (as long as File 1 and 2 match)
• Skips rows not specified in File 3
• Handles cases where a row exists in File 2 but not File 1
• Generates a human-readable report with clear mismatch details

Sample Output (mismatch_report.csv): #

A,Mismatched_Columns,File1_Values,File2_Values
1,C,C:red,C:green
2,D,D:long,D:short

Solution 2: AWK Script (Command-Line Friendly) #

If you're working in a Linux/Unix environment and prefer a command-line solution, AWK is lightweight and efficient for this task.

BEGIN {
    FS = ","
    OFS = ","
    # Load target A values from File 3
    while ((getline < file3) > 0) {
        if (NR == 1) next  # Skip header row
        target_a[$1] = 1
    }
    close(file3)

    # Load File 1 headers and rows (keyed by Column A)
    getline < file1
    split($0, headers, ",")
    while ((getline < file1) > 0) {
        a_val = $1
        if (target_a[a_val]) {
            file1_rows[a_val] = $0
        }
    }
    close(file1)

    # Print report header
    print "A,Mismatched_Columns,File1_Values,File2_Values"

    # Compare File 2 rows against File 1
    getline < file2
    while ((getline < file2) > 0) {
        a_val = $1
        if (!target_a[a_val]) continue  # Skip non-target rows
        if (!(a_val in file1_rows)) {
            # Row exists in File 2 but not File 1
            print a_val ",NOT_FOUND_IN_FILE1,N/A," substr($0, index($0, ",")+1)
            continue
        }

        # Split rows into values for comparison
        split(file1_rows[a_val], f1_vals, ",")
        split($0, f2_vals, ",")
        
        mismatches = ""
        f1_str = ""
        f2_str = ""
        # Loop through all columns (skip Column A at index 1)
        for (i=2; i<=length(headers); i++) {
            if (f1_vals[i] != f2_vals[i]) {
                if (mismatches != "") {
                    mismatches = mismatches ","
                    f1_str = f1_str ","
                    f2_str = f2_str ","
                }
                mismatches = mismatches headers[i]
                f1_str = f1_str headers[i] ":" f1_vals[i]
                f2_str = f2_str headers[i] ":" f2_vals[i]
            }
        }

        # Write report entry if there are mismatches
        if (mismatches != "") {
            print a_val "," mismatches "," f1_str "," f2_str
        }
    }
    close(file2)
}

How to Use: #

Save the script as compare_files.awk, then run it with:

awk -v file1="file1.csv" -v file2="file2.csv" -v file3="file3.csv" -f compare_files.awk > mismatch_report.csv

This script will produce the same output as the Python version, and it's just as flexible with variable column counts.

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