使用Bash/Awk筛选与多基因TSV文件有共同列的样本TSV行

Here's a robust bash/awk solution that handles both of your matching conditions efficiently. It first builds lookup indexes from your gene reference files, then uses those indexes to filter your sample TSV:

#!/bin/bash

# Update these paths to match your actual file/folder locations
SAMPLE_FILE="Sample_1.tsv"
GENES_DIR="./genes_folder"
OUTPUT_FILE="Sample_1_ok.tsv"

# Step 1: Build temporary lookup indexes from gene reference files
awk -F'\t' '
NR==1 {next}  # Skip header rows in gene files
{
    # Extract start/end coordinates from the Coordinates column
    split($1, coord_parts, /[:-]/)
    start = coord_parts[2]
    end = coord_parts[3]
    
    # Extract reference and alternate bases from the Change column
    split($3, change_parts, /[.]/)
    sub(/^g[.]/, "", change_parts[2])
    split(change_parts[2], ra_parts, /[>]/)
    ref_base = ra_parts[1]
    alt_base = ra_parts[2]
    
    # Extract gene name and HGVps suffix from Consequence column
    split($5, conseq_parts, " ")
    gene_name = conseq_parts[2]
    hgvps_suffix = conseq_parts[3]
    
    # Write position-based match key to temp index
    print gene_name "_" start "_" end "_" ref_base "_" alt_base > "pos_index.tmp"
    # Write full HGVps string (matching sample file format) to temp index
    print "p." hgvps_suffix > "hgvps_index.tmp"
}
' "$GENES_DIR"/*.tsv

# Step 2: Filter the sample file using the lookup indexes
awk -F'\t' '
BEGIN {
    # Load position index into associative array
    while ((getline pos_key < "pos_index.tmp") > 0) {
        pos_matches[pos_key] = 1
    }
    # Load HGVps index into associative array
    while ((getline hgvps_val < "hgvps_index.tmp") > 0) {
        hgvps_matches[hgvps_val] = 1
    }
    close("pos_index.tmp")
    close("hgvps_index.tmp")
    OFS="\t"  # Ensure output uses tab separators
}
NR==1 {
    # Print header to output file
    print > "'"$OUTPUT_FILE"'"
    next
}
{
    # Build position match key from sample row
    pos_key = $1 "_" $3 "_" $4 "_" $6 "_" $7
    # Get HGVps value from sample row
    sample_hgvps = $9
    
    # Check if either condition is met
    if (pos_matches[pos_key] || hgvps_matches[sample_hgvps]) {
        print > "'"$OUTPUT_FILE"'"
    }
}
' "$SAMPLE_FILE"

# Clean up temporary index files
rm pos_index.tmp hgvps_index.tmp

echo "Filtering complete! Results saved to $OUTPUT_FILE"

How It Works #

1. Index Building:

   • We first parse all your gene reference files to create two temporary lookup indexes:
     • A position-based index (pos_index.tmp) using the format Gene_Start_End_Ref_Alt to match your first condition
     • An HGVps-based index (hgvps_index.tmp) using the full p.XXXX format to match your second condition
   • This pre-processing step ensures fast lookups later, even with large datasets.

2. Sample Filtering:

   • We load both indexes into awk associative arrays for O(1) lookups
   • For each row in your sample file:
     • We build the position match key using the Gene, Start, End, Ref, and Sample columns
     • We grab the HGVps column value directly
     • If either the position key exists in our position index, or the HGVps value exists in our HGVps index, we write the row to the output file

Important Notes #

• Column Adjustments: Double-check the column numbers in the script (e.g., $9 for HGVps in the sample file) to match your actual TSV structure. If your columns are in different positions, update these values.
• TSV Format: Ensure your files use strict tab separators (no spaces within columns) for accurate parsing.
• Performance: This approach is efficient even for large files, as awk handles associative array lookups quickly and avoids repeated disk reads.
• Path Updates: Don't forget to update the SAMPLE_FILE, GENES_DIR, and OUTPUT_FILE variables to match your file system locations.

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