如何按规则从表格ErrorDescr提取ErrorType并按ID统计出现次数

Problem Statement #

You have input data structured like this:

--- ID ---- ErrorDescr ---
1 Error: ERROR1 - ESK - motor problem
1 Error: ERROR13 - EPN - window problem
1 Human problem

And you want to transform it into this output, which groups by ID, extracts the appropriate error type, and counts occurrences:

--ID--ErrorType---Count
1 ESK 1
1 EPN 1
1 Human problem 1

The rules are:

• If the ErrorDescr field starts with Error:, extract the 3-character code after the first -
• If it doesn't start with Error:, use the full ErrorDescr text directly
• Count how many times each error type appears per ID

Solution 1: Using AWK (Command-Line) #

AWK is perfect for this kind of text processing and aggregation. Here's a one-liner that does exactly what you need:

awk '
NR > 1 {
    id = $1;
    if ($2 == "Error:") {
        # Find the first "-" and extract the next 3 characters
        for (i=1; i<=NF; i++) {
            if ($i == "-") {
                err_type = $(i+1);
                break;
            }
        }
    } else {
        # Join all fields from $2 onwards as the error type
        err_type = "";
        for (i=2; i<=NF; i++) err_type = err_type " " $i;
        err_type = substr(err_type, 2); # Remove leading space
    }
    # Increment count for this ID + error type pair
    count[id, err_type]++;
}
END {
    print "--ID--ErrorType---Count";
    for (key in count) {
        split(key, k, SUBSEP);
        printf "%s %s %d\n", k[1], k[2], count[key];
    }
}' input.txt

How it works:

• NR > 1 skips the header line of the input
• For each line, we first grab the ID ($1)
• If the second field is Error:, we loop through fields to find the first -, then take the next field as the error type
• If not, we join all fields from the second one onwards to get the full error description
• We use a multi-dimensional associative array count[id, err_type] to track occurrences
• Finally, in the END block, we print the header and iterate through the count array to output results

Solution 2: Using Python (Scriptable) #

If you prefer a Python script for more flexibility, here's a straightforward implementation:

from collections import defaultdict

# Initialize a counter: key is (id, error_type), value is count
error_counts = defaultdict(int)

with open("input.txt", "r") as f:
    # Skip the header line
    next(f)
    for line in f:
        line = line.strip()
        if not line:
            continue
        parts = line.split(maxsplit=1)
        if len(parts) < 2:
            continue
        id_val, error_descr = parts[0], parts[1]
        
        if error_descr.startswith("Error:"):
            # Split on "-" and take the second part, strip whitespace
            segments = error_descr.split("-")
            if len(segments) >= 2:
                err_type = segments[1].strip()
            else:
                # Fallback if format is unexpected
                err_type = error_descr
        else:
            err_type = error_descr
        
        error_counts[(id_val, err_type)] += 1

# Print the results
print("--ID--ErrorType---Count")
for (id_val, err_type), count in error_counts.items():
    print(f"{id_val} {err_type} {count}")

How it works:

• We use defaultdict to easily track counts for each (ID, error type) pair
• We skip the header line, then process each line by splitting into ID and error description
• For descriptions starting with Error:, we split on - and extract the trimmed second segment
• For other descriptions, we use the full text directly
• Finally, we print the header and all aggregated counts

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