Linux下用Bash命令为CSV数据添加NTILE五分位列求助

Alright, let's figure out how to add a quintile column (matching SQL's NTILE(5) behavior) to your CSV using bash tools. Since you already know how to add an incrementing column, we can build on that logic to split your data into five roughly equal groups—just like NTILE does, where any leftover rows get distributed to the first few quintiles.

Basic Case: CSV Without Commas in Quoted Fields #

First, let's handle the simple scenario where your CSV doesn't have commas inside quoted values (e.g., no "Smith, Jane" entries). We'll use awk, which is perfect for this kind of text manipulation.

With a Header Row #

If your CSV has a header, run this command to add a Quintile column:

awk -F ',' -v OFS=',' '
    BEGIN { quintile_col = "Quintile" }
    # Print header with new column name
    NR == 1 { print $0, quintile_col; next }
    # Store all data rows in an array
    { lines[NR] = $0 }
    END {
        # Calculate total data rows (subtract header)
        total_rows = NR - 1
        # Base size per quintile
        quintile_size = int(total_rows / 5)
        # Remainder rows to distribute to first N quintiles
        remainder = total_rows % 5
        
        current_quintile = 1
        row_count = 0
        
        # Loop through stored rows and assign quintiles
        for (i = 2; i <= NR; i++) {
            row_count++
            print lines[i], current_quintile
            
            # Move to next quintile when we hit the threshold
            if (current_quintile <= remainder) {
                # First 'remainder' quintiles get an extra row
                if (row_count == quintile_size + 1) {
                    current_quintile++
                    row_count = 0
                }
            } else {
                # Remaining quintiles get base size
                if (row_count == quintile_size) {
                    current_quintile++
                    row_count = 0
                }
            }
        }
    }
' your_data.csv > your_data_with_quintile.csv

Without a Header Row #

If your CSV doesn't have a header, adjust the script to skip adding a header column:

awk -F ',' -v OFS=',' '
    { lines[NR] = $0 }
    END {
        total_rows = NR
        quintile_size = int(total_rows / 5)
        remainder = total_rows % 5
        
        current_quintile = 1
        row_count = 0
        
        for (i = 1; i <= NR; i++) {
            row_count++
            print lines[i], current_quintile
            
            if (current_quintile <= remainder) {
                if (row_count == quintile_size + 1) {
                    current_quintile++
                    row_count = 0
                }
            } else {
                if (row_count == quintile_size) {
                    current_quintile++
                    row_count = 0
                }
            }
        }
    }
' your_data.csv > your_data_with_quintile.csv

Handling CSVs with Quoted Commas #

If your CSV has values with commas inside quotes (e.g., "Doe, John",45), the basic awk script will break because it treats all commas as separators. For this, use csvawk from the csvkit package—it handles CSV parsing correctly.

1. Install csvkit first:

   # Debian/Ubuntu
   sudo apt install csvkit
   # Or via pip
   pip install csvkit
   

2. Run this command (adjust for header/non-header as needed):

   csvawk -v OFS=',' '
       BEGIN { quintile_col = "Quintile" }
       NR == 1 { print $0, quintile_col; next }
       { lines[NR] = $0 }
       END {
           total_rows = NR - 1
           quintile_size = int(total_rows / 5)
           remainder = total_rows % 5
           
           current_quintile = 1
           row_count = 0
           
           for (i = 2; i <= NR; i++) {
               row_count++
               print lines[i], current_quintile
               
               if (current_quintile <= remainder && row_count == quintile_size + 1) {
                   current_quintile++
                   row_count = 0
               } else if (current_quintile > remainder && row_count == quintile_size) {
                   current_quintile++
                   row_count = 0
               }
           }
       }
   ' your_data.csv > your_data_with_quintile.csv
   

Adding Quintiles to Sorted Data #

Just like SQL's NTILE, you'll usually want to sort your data first before assigning quintiles (e.g., sort by a numeric column). Here's how to combine sorting and quintile assignment:

Example: Sort by Column 2 (Numeric) and Add Quintiles #

# Sort CSV by column 2 (numeric sort), then add quintiles
sort -t ',' -k2,2n your_data.csv | awk -F ',' -v OFS=',' '
    BEGIN { quintile_col = "Quintile" }
    NR == 1 { print $0, quintile_col; next }
    { lines[NR] = $0 }
    END {
        total_rows = NR - 1
        quintile_size = int(total_rows / 5)
        remainder = total_rows % 5
        
        current_quintile = 1
        row_count = 0
        
        for (i = 2; i <= NR; i++) {
            row_count++
            print lines[i], current_quintile
            
            if (current_quintile <= remainder && row_count == quintile_size + 1) {
                current_quintile++
                row_count = 0
            } else if (current_quintile > remainder && row_count == quintile_size) {
                current_quintile++
                row_count = 0
            }
        }
    }
' > sorted_data_with_quintile.csv

The sort command uses -t ',' to set the delimiter, -k2,2n to sort by column 2 (numeric order).

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