按第二列匹配汇总多列行数据的awk/sed类工具方案咨询

Alright, let's break down how to solve this with AWK—it's the perfect tool for tabular data aggregation tasks like this. First, let's start with a concrete example to make sure we're aligned on the problem.

Example Input #

Let's assume your data looks something like this (comma-separated; adjust delimiter if yours is different):

ID,Category,Sample1,Sample2,Sample3
001,A,10,20,30
002,B,5,15,25
001,A,5,10,15
003,C,8,16,24
002,B,3,6,9

Your goal: Group rows by ID + Category to remove duplicates, then sum up the values in each Sample column for each group.

AWK Solution #

Here's a script that handles this cleanly:

BEGIN {
    FS = ","          # Set input field separator to comma
    OFS = ","          # Set output field separator to match
    # Print our custom header for aggregated results
    print "ID,Category,Sample1_Total,Sample2_Total,Sample3_Total"
}

# Skip the header line (NR = number of records processed so far)
NR > 1 {
    # Create a unique key from ID and Category to avoid duplicate groups
    unique_key = $1 "," $2
    # Add each sample value to its respective sum for the key
    sum_sample1[unique_key] += $3
    sum_sample2[unique_key] += $4
    sum_sample3[unique_key] += $5
}

END {
    # Loop through all unique keys and print the aggregated data
    for (key in sum_sample1) {
        # Split the key back into ID and Category
        split(key, parts, ",")
        print parts[1], parts[2], sum_sample1[key], sum_sample2[key], sum_sample3[key]
    }
}

How to Run It #

1. Save the script as aggregate_data.awk
2. Run it against your input file (e.g., input.csv) with:

   awk -f aggregate_data.awk input.csv
   

Example Output #

For the sample input above, you'll get:

ID,Category,Sample1_Total,Sample2_Total,Sample3_Total
001,A,15,30,45
002,B,8,21,34
003,C,8,16,24

Customization Tips #

• Different Delimiters: If your file uses tabs, change FS = "," to FS = "\t". For spaces, use FS = " " (or FS = "[[:space:]]+" for multiple spaces).
• More Sample Columns: If you have Sample4, Sample5, etc., just add lines like sum_sample4[unique_key] += $6 (since ID is $1, Category $2, so Sample4 would be $6).
• Sorted Output: To sort results by Category or ID, pipe the output to sort:

  awk -f aggregate_data.awk input.csv | sort -t',' -k2,2  # Sort by Category
  

Why Not Sed? #

Sed is fantastic for text editing and line-level substitutions, but it doesn't handle stateful operations like summing values across lines. AWK was built exactly for this kind of grouped arithmetic, so it's the right tool for the job.

内容的提问来源于stack exchange，提问作者Cristbal Alejandro Hernndez lv