如何使用Python基于IP和Protocol列匹配两个CSV文件并更新目标CSV的Exists列值

Hey there! Let's work through this together since you're new to Python and pandas. Your goal is to update the Exists column in input.csv to 'Yes' whenever a row matches both the IP and Protocol columns with entries in search.csv. Let's start by breaking down what was off with your original code, then walk through a working solution.

What Was Wrong with Your Original Code? #

A few small issues were blocking it from working:

• The column order in search.csv is different (Protocol comes before IP), so when you converted rows to tuples, the values didn't line up for matching.
• You tried to write to result.csv but forgot to quote the filename, and your logic was checking for rows that don't match instead of updating the Exists column.
• There were undefined variables (like inp) that would throw errors.

Working Solution 1: Simple Match with Tuple Sets #

This method is easy to understand and works great for small to medium-sized datasets:

import pandas as pd

# Step 1: Load both CSV files into pandas DataFrames
input_df = pd.read_csv("input.csv")
search_df = pd.read_csv("search.csv")

# Step 2: Initialize the Exists column (set to 'No' if it's empty)
input_df['Exists'] = input_df['Exists'].fillna('No')

# Step 3: Create a set of (IP, Protocol) tuples from search.csv for fast lookup
match_pairs = set(zip(search_df['IP'], search_df['Protocol']))

# Step 4: Update Exists to 'Yes' where the (IP, Protocol) pair exists in search.csv
input_df['Exists'] = input_df.apply(
    lambda row: 'Yes' if (row['IP'], row['Protocol']) in match_pairs else row['Exists'],
    axis=1
)

# Step 5: Save the updated DataFrame to a new CSV
input_df.to_csv("result.csv", index=False)

print("Done! Check result.csv for the updated data.")

Working Solution 2: Using Pandas Merge (Better for Large Datasets) #

If you're working with big CSV files, using pandas' merge function is more efficient than row-by-row checks:

import pandas as pd

input_df = pd.read_csv("input.csv")
search_df = pd.read_csv("search.csv")

# Initialize Exists column if empty
input_df['Exists'] = input_df['Exists'].fillna('No')

# Create a simplified DataFrame from search.csv with just the matching columns
search_matches = search_df[['IP', 'Protocol']].copy()
search_matches['is_match'] = True  # Add a flag to identify matches

# Left join the two DataFrames on IP and Protocol
merged_df = pd.merge(input_df, search_matches, on=['IP', 'Protocol'], how='left')

# Update Exists to 'Yes' where there's a match, keep original value otherwise
merged_df['Exists'] = merged_df.apply(
    lambda row: 'Yes' if pd.notna(row['is_match']) else row['Exists'],
    axis=1
)

# Remove the temporary 'is_match' column and save
final_df = merged_df.drop(columns=['is_match'])
final_df.to_csv("result.csv", index=False)

print("Success! Updated data saved to result.csv.")

Key Notes to Remember #

• Column Names Matter: Make sure the IP and Protocol column names are exactly the same in both CSVs (no extra spaces, consistent capitalization).
• IP Format Consistency: Ensure IP addresses are written identically in both files (e.g., 192.132.16.02 vs 192.132.16.2 won't match).
• File Paths: If your CSVs aren't in the same folder as your Python script, use full paths (e.g., C:/data/input.csv on Windows or /home/user/data/input.csv on Linux/macOS).

内容的提问来源于stack exchange，提问作者fatima ghaiyur hayat