如何用Python合并CSV中4列相同、1列不同的重复行

Hey there! Let's tackle this problem where you need to merge rows in a CSV that are identical except for one column, combining the differing values with a | separator. I'll show you two approaches: one using pandas (great for larger datasets) and a pure Python method (no external libraries needed).

Approach 1: Using Pandas (Recommended for Scalability) #

Pandas makes grouping and merging rows straightforward. Here's how to do it:

import pandas as pd

# Read the input CSV (no header row, so we use header=None)
df = pd.read_csv('input.csv', header=None)
merged_rows = []

# Iterate over each column, treating it as the potential differing column
for diff_col in df.columns:
    # Group rows by all columns except the current "differing" column
    grouped = df.groupby([col for col in df.columns if col != diff_col])
    for group_key, group_data in grouped:
        # If the group has multiple rows, merge the differing column values
        if len(group_data) > 1:
            combined_vals = '|'.join(group_data[diff_col].astype(str))
            # Build the merged row: combine the group key with the merged values
            merged_row = list(group_key[:diff_col]) + [combined_vals] + list(group_key[diff_col:])
            merged_rows.append(merged_row)

# Remove duplicate merged rows (in case the same merge was detected via different columns)
merged_df = pd.DataFrame(merged_rows).drop_duplicates()

# Write the result to output.csv (no header, no index)
merged_df.to_csv('output.csv', header=None, index=False)

How This Works: #

• We read the CSV without assuming a header (since your input rows start with data immediately).
• For each column, we group rows by every other column. If a group has multiple rows, those rows are identical except for the current column.
• We combine the differing values with |, construct the merged row, and collect all such rows.
• Finally, we remove duplicates (to handle edge cases where a merge might be detected via multiple columns) and write the result.

Approach 2: Pure Python (No External Libraries) #

If you don't want to install pandas, this pure Python solution uses the built-in csv module:

import csv

def check_almost_duplicate(row1, row2):
    """Check if two rows are identical except for exactly one column, return (result, differing_column_index)"""
    diff_count = 0
    diff_col = -1
    for idx, (val1, val2) in enumerate(zip(row1, row2)):
        if val1 != val2:
            diff_count += 1
            diff_col = idx
            if diff_count > 1:
                return False, -1
    return diff_count == 1, diff_col

# Read all rows from input.csv
with open('input.csv', 'r', newline='') as infile:
    reader = csv.reader(infile)
    rows = list(reader)
    processed = [False] * len(rows)
    merged_rows = []

    for i in range(len(rows)):
        if processed[i]:
            continue
        current_row = rows[i]
        duplicates = [current_row]
        processed[i] = True

        # Find all rows that are almost duplicates of the current row
        for j in range(i + 1, len(rows)):
            if processed[j]:
                continue
            is_dup, diff_col = check_almost_duplicate(current_row, rows[j])
            if is_dup:
                duplicates.append(rows[j])
                processed[j] = True

        # Merge the duplicates if there are any
        if len(duplicates) == 1:
            merged_rows.append(current_row)
        else:
            _, diff_col = check_almost_duplicate(duplicates[0], duplicates[1])
            combined_vals = '|'.join([row[diff_col] for row in duplicates])
            merged_row = current_row.copy()
            merged_row[diff_col] = combined_vals
            merged_rows.append(merged_row)

# Write the merged rows to output.csv
with open('output.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    writer.writerows(merged_rows)

How This Works: #

• The check_almost_duplicate function checks if two rows differ in exactly one column.
• We read all rows and track which ones have been processed to avoid merging the same rows multiple times.
• For each unprocessed row, we find all rows that are almost duplicates, then merge the differing column values into a single entry separated by |.
• Finally, we write the merged rows to the output file.

Testing the Solution #

For your input CSV:

abc,fff,v1,hhh,jjj
abc,fff,v2,hhh,jjj
efg,ooo,h1,ppp,www
efg,ooo,h2,ppp,www

Both solutions will produce this output in output.csv:

abc,fff,v1|v2,hhh,jjj
efg,ooo,h1|h2,ppp,www

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