使用Bash基于另一CSV的标识符替换主CSV指定列

Got it, let's solve this problem—you need to update specific columns in your Main.csv using values from replacement.csv, matching on the third column of Main. Here are two straightforward approaches depending on whether you prefer Python or command-line tools:

Pandas is perfect for this kind of CSV manipulation—it’s readable, handles edge cases (like missing matches) smoothly, and works with both space-separated and comma-separated files.

First, install pandas if you haven’t already:

pip install pandas

Then use this code. I’ll include comments to explain each step, and you can tweak the column indices to match your exact needs:

import pandas as pd

# Read your CSV files. Adjust sep to ',' if your files use commas instead of spaces
main_df = pd.read_csv('Main.csv', sep=' ', header=None)
replacement_df = pd.read_csv('replacement.csv', sep=' ', header=None)

# Create a lookup dictionary: key = identifier (from replacement's first column), value = replacement value
replacement_lookup = dict(zip(replacement_df[0], replacement_df[1]))

# Define which columns to work with
identifier_col_in_main = 2  # 3rd column (0-based index)
target_col_to_replace = 3   # 4th column (change this to your desired column)

# Replace values: use the lookup for matches, keep original values if no match exists
main_df[target_col_to_replace] = main_df[identifier_col_in_main].map(replacement_lookup).fillna(main_df[target_col_to_replace])

# Save the modified data to a new CSV (so we don't overwrite your original Main.csv)
main_df.to_csv('Modified_Main.csv', sep=' ', index=False, header=False)

Quick Notes: #

• If your CSVs have column headers, remove header=None from the read_csv lines and use column names instead of indices (e.g., main_df['identifier_col'] instead of main_df[2]).
• The fillna part ensures rows without a matching identifier stay unchanged.

If you prefer a no-install, terminal-based solution, awk is fast and lightweight for this task.

Run this command in your terminal. Again, adjust field numbers and separators as needed:

# For space-separated files
awk 'NR==FNR {lookup[$1]=$2; next} $3 in lookup {$4=lookup[$3]} 1' replacement.csv Main.csv > Modified_Main.csv

# For comma-separated files, add -F ',' at the start:
# awk -F ',' 'NR==FNR {lookup[$1]=$2; next} $3 in lookup {$4=lookup[$3]} 1' replacement.csv Main.csv > Modified_Main.csv

How it works: #

• NR==FNR: This runs only for the first file (replacement.csv), storing each identifier and its replacement value in the lookup array.
• next: Skips to the next line so we don’t process the replacement file with the main logic.
• $3 in lookup: Checks if the 3rd field of Main.csv exists in our lookup array.
• $4=lookup[$3]: If there’s a match, replaces the 4th field with the corresponding value from replacement.csv.
• 1: Prints every line (modified or original) to the output file.

Example Result: #

Using your sample data, the modified Main.csv will have:

• Rows with bbb in the 3rd column: 4th column changes from 222 to abc
• Rows with jjj in the 3rd column: 4th column changes from 888 to def
• Rows with eee in the 3rd column: 4th column changes from 555 and 201 to ghi

Content of the question来源于stack exchange，提问作者Visahan