在KSH中使用AWK按第三列分组汇总CSV文件的两列数据

Hey there! I get that you've been digging through similar examples but haven't been able to tweak them for your specific CSV subtotaling task—let's fix that right now. And don't stress about any grammar slips since English is your second language; I’m focused on getting you a working solution for KSH and AWK.

Your goal is to calculate subtotals for two columns, grouped by the third column (c3) in your CSV file. Let's break this down into a reusable, adaptable solution.

Basic AWK Script Template #

This script will read your CSV, group rows by column 3, and sum two target numeric columns. I'll use your sample data as a reference to make it concrete:

awk -F ',' '
    # Optional: Print a header for your output
    BEGIN {
        print "Group(c3),Subtotal_Column5,Subtotal_Column6"
    }

    # Optional: Skip the header row if your CSV has one (uncomment below)
    # NR == 1 { next }

    # Process each row: accumulate sums per group
    {
        # Skip rows where c3 is empty (adjust if you want to include these)
        if ($3 == "") next
        
        # Use 0+$COL to treat empty cells as 0 (avoids errors from blank values)
        group_totals[$3]["col5"] += 0+$5
        group_totals[$3]["col6"] += 0+$6  # Replace 6 with your second target column number
    }

    # After processing all rows, print the subtotals
    END {
        for (group in group_totals) {
            # Format output to 2 decimal places (adjust %.2f if you don't need decimals)
            printf "%s,%.2f,%.2f\n", group, group_totals[group]["col5"], group_totals[group]["col6"]
        }
    }
' your_csv_file.csv

Adapted to Your Sample Data #

Looking at your sample line: c1,c2,c3,c4,c5,134.6,,c8,c9,SERVER1,c11 (I’m assuming column 5 has the numeric value 134.6 here). If you want to sum column 5 and column 6 (even when column 6 is empty), the script above will work directly—just replace your_csv_file.csv with your actual file name.

Key Details to Customize: #

• Target Columns: Change the numbers in 0+$5 and 0+$6 to match the two columns you want to sum.
• Header Handling: If your CSV starts with a header row, uncomment the NR == 1 { next } line to skip it.
• Sort Output: To sort the results by the c3 group, pipe the script's output to sort:

  awk -F ',' '...' your_csv_file.csv | sort
  

• Filter Rows: Add a condition if you only want to include specific rows (e.g., $10 == "SERVER1" to only process rows where column 10 is SERVER1).

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