行级c2至c6列平均值计算需求：忽略NR但计入列数

Got it, let's work through this problem step by step. We need to calculate the average for columns c2 through c6 in each row, with two key rules: ignore any "NR" strings when summing values, but still count those "NR" columns when figuring out the total number of columns for the average.

先明确计算规则 #

• Columns c2 to c6 make up 5 total columns (that's fixed, even if some have "NR")
• For each row: sum up all numerical values in c2-c6, then divide that sum by 5 (the total number of columns in this range)

处理后的最终表格 #

验证计算过程 #

Let's double-check the math to make sure:

• First row: Sum of valid values = 4 + 6 + 8 + 6 = 24. Average = 24 / 5 = 4.8
• Second row: Sum of valid values = 8 + 2 + 5 + 3 + 2 = 20. Average = 20 / 5 = 4.0
• Third row: Sum of valid values = 2 + 0.4 + 3 = 5.4. Average = 5.4 / 5 = 1.08

If you want to automate this (say, with Python), here's a quick script that does the job:

# Raw input data
raw_data = [
    ["para1", "para2", "c1", "c2", "c3", "c4", "c5", "c6"],
    ["ast", "abc", "3", "4", "NR", "6", "8", "6"],
    ["ast", "pqr", "4", "8", "2", "5", "3", "2"],
    ["bc", "sd", "-0.3", "2", "0.4", "NR", "NR", "3"]
]

# Set up the processed data with the new "mean" header
processed_data = [raw_data[0] + ["mean"]]

# Calculate mean for each data row
for row in raw_data[1:]:
    # Grab values from c2 to c6 (indices 3 to 7, 0-based)
    c_range_values = row[3:8]
    total_sum = 0.0
    total_columns = len(c_range_values)  # Always 5
    
    for val in c_range_values:
        if val != "NR":
            total_sum += float(val)
    
    row_mean = round(total_sum / total_columns, 2)
    processed_data.append(row + [row_mean])

# Print the Markdown table
for line in processed_data:
    print("| " + " | ".join(str(item) for item in line) + " |")

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