如何通过条件判断与循环实现两个文件的关联合并？

Hey there! Let's work through this file join problem together. From your sample data, it’s clear the shared key we need to match on is the date field (like 23032018) — it’s the second column in both files. Our goal is to merge rows where this date matches, pairing each line from File A with all corresponding entries from File B (we can adjust for different join types if needed).

Below are two practical, easy-to-implement solutions using common tools:

Solution 1: Use AWK (Shell/Command Line) #

AWK is perfect for quick text processing tasks like this. We’ll first load File B into a map (dictionary) where the date is the key, and the value is a list of matching IDs. Then we’ll iterate through File A and merge in the matching IDs.

AWK Script #

Create a file named join_files.awk with this code:

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

# Process File B first: build a map of date to IDs
NR == FNR {
    # If date isn't in the map yet, add it with the current ID
    if (!($2 in date_map)) date_map[$2] = $1;
    # If date exists, append the new ID (using | as a separator)
    else date_map[$2] = date_map[$2] "|" $1;
    next;  # Skip to next line without processing further
}

# Now process File A and merge with matching IDs from File B
{
    current_date = $2;
    # Check if we have matching IDs for this date
    if (current_date in date_map) {
        # Split the stored IDs into an array
        split(date_map[current_date], ids, "|");
        # Print each combination of File A line + matching ID
        for (i in ids) {
            print $0, ids[i];
        }
    } else {
        # Optional: print lines with no match (remove if you want only matches)
        print $0, "NO_MATCH";
    }
}

Run the Script #

In your terminal, run:

awk -f join_files.awk FileB.csv FileA.csv > merged_output.csv

Solution 2: Use Python (More Flexible for Complex Logic) #

If you need more control (like handling edge cases, transforming data mid-join, or working with very large files), Python is a great choice. We’ll use a dictionary to map dates to IDs from File B, then loop through File A to merge the data.

Python Code #

Save this as merge_files.py:

# Step 1: Build a dictionary mapping dates to their corresponding IDs from File B
date_to_ids = {}
with open("FileB.csv", "r") as file_b:
    for line in file_b:
        line = line.strip()
        if not line:  # Skip empty lines
            continue
        # Split line into ID and date (adjust separator if your data uses spaces instead of commas)
        id_value, date_value = line.split(",")
        # Add ID to the list for this date
        if date_value not in date_to_ids:
            date_to_ids[date_value] = []
        date_to_ids[date_value].append(id_value)

# Step 2: Merge File A with matching IDs from File B
with open("FileA.csv", "r") as file_a, open("merged_output.csv", "w") as output_file:
    for line in file_a:
        line = line.strip()
        if not line:  # Skip empty lines
            continue
        parts = line.split(",")
        current_date = parts[1]
        # Check for matching IDs
        if current_date in date_to_ids:
            # Print each combination of File A line + ID
            for id_value in date_to_ids[current_date]:
                output_file.write(f"{line},{id_value}\n")
        else:
            # Optional: handle lines with no matching date (remove if needed)
            output_file.write(f"{line},NO_MATCH\n")

Run the Code #

Execute it with:

python merge_files.py

Key Notes to Adjust for Your Needs #

• Separator Check: If your files use spaces instead of commas, update the split(",") calls to split() (for any whitespace) or split(" ").
• Join Type:
  • For an inner join (only keep lines where dates exist in both files), remove the else blocks that print NO_MATCH.
  • For a right join (keep all lines from File B even if no match in File A), you’ll need to add an extra loop to process File B entries with no File A matches.
• Large Files: Both solutions process files line-by-line, so they’ll handle 800+ lines (or even millions) without memory issues.

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