CSV文件合并需求：基于Cliente与Fecha字段匹配合并两个CSV

The core issue with your current code is that it merges rows by their position in the list (index i), which only works if both CSVs are sorted exactly the same way by Cliente and Fecha—and you explicitly stated this isn't guaranteed. We need to instead create a lookup based on the two matching keys to correctly pair rows.

Step-by-Step Solution #

1. Clarify Data Structures #

• list1 columns: Cliente, Fecha, Status
• list2 columns: Fecha, Cliente, Subastas, Impresiones_exchange, Fill_rate, Importe_a_pagar_a_medio, ECPM_medio
• Target output: Cliente, Fecha, Subastas, Impresiones_exchange, Fill_rate, Importe_a_pagar_a_medio, ECPM_medio, Status

2. Revised Code #

This code uses a dictionary to map each (Cliente, Fecha) pair from list2 to its associated data, then merges it with the corresponding row in list1:

import csv

# Helper function to load CSV files (Python 3 compatible)
def load_csv(file_path):
    with open(file_path, 'r', newline='', encoding='utf-8') as f:
        reader = csv.reader(f)
        return list(reader)

# Load both source CSVs
list1 = load_csv('list1.csv')
list2 = load_csv('list2.csv')

# Create a lookup dictionary for list2: key = (Cliente, Fecha), value = relevant data fields
list2_lookup = {}
# Skip the header row when building the lookup
for row in list2[1:]:
    fecha = row[0]
    cliente = row[1]
    # Grab all columns after Fecha and Cliente
    data_fields = row[2:]
    list2_lookup[(cliente, fecha)] = data_fields

# Prepare the output data
output = []

# Build the output header: combine list1 header with list2's non-key columns
output_header = list1[0] + list2[0][2:]
output.append(output_header)

# Merge rows by matching Cliente and Fecha
for row in list1[1:]:
    cliente = row[0]
    fecha = row[1]
    status = row[2]
    # Get matching data from list2 (use empty values if no match exists)
    list2_data = list2_lookup.get((cliente, fecha), [''] * len(list2[0][2:]))
    # Construct the merged row in your desired order
    merged_row = [cliente, fecha] + list2_data + [status]
    output.append(merged_row)

# Write the final merged CSV
with open('output.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerows(output)

3. Key Improvements #

• Lookup Dictionary: Uses (Cliente, Fecha) as a unique key to store list2 data, so we can find matches quickly regardless of row order.
• Header Handling: Automatically builds the correct output header by combining relevant columns from both CSVs.
• Missing Match Handling: If a list1 row has no corresponding entry in list2, it fills empty values for the list2 columns (you can adjust this to skip rows or add a placeholder if needed).
• Python 3 Compatibility: Uses modern file modes and encoding settings to avoid character encoding issues.

Quick Notes #

• For Python 2, adjust file modes back to rb/wb and remove the encoding parameter.
• If your CSVs use a delimiter other than comma, add delimiter='your_delimiter' to both csv.reader and csv.writer.

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