如何用AWK筛选含大于0值的CSV行？避免重复输出

问题背景 #

需要处理一份CSV文件，要求跳过表头，仅保留第4列及以后存在大于0值的行，且每行仅输出一次。

原始CSV文件 #

DATE,TAG,ID,METRIC_1,METRIC_2,METRIC_3,METRIC_4,METRIC_5,METRIC_6,METRIC_7,METRIC_8,METRIC_9,METRIC_A,METRIC_B,METRIC_C,METRIC_D,METRIC_E,METRIC_F,METRIC_G
2000-01-29,3PXI1,37681,1.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI2,37682,20.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI3,37683,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI4,37684,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI5,37685,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,22.37,23.91,0.00,0.00,0.00,0.00
2000-01-29,3PXI6,37686,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,30.00,40.14,0.00,0.00,0.00,0.00
2000-01-29,3PXI7,37687,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI8,37688,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI9,37689,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXJ0,37690,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXJ1,37691,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,25.00,51.13,0.00,0.00,0.00,0.00

期望输出 #

2000-01-29,3PXI1,37681,1.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI2,37682,20.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00
2000-01-29,3PXI5,37685,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,22.37,23.91,0.00,0.00,0.00,0.00
2000-01-29,3PXI6,37686,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,30.00,40.14,0.00,0.00,0.00,0.00
2000-01-29,3PXJ1,37691,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,25.00,51.13,0.00,0.00,0.00,0.00

尝试的错误命令 #

执行以下命令后，出现重复行（每有一个大于0的列就输出一次整行）：

awk -v FS=, 'NR!=1 {for(i=4; i<NF; i++) if($i>0)print$0;next}' file.csv

格式化后的脚本：

NR != 1 {
        for (i = 4; i < NF; i++) {
                if ($i > 0) {
                        print $0
                }
        }
        next
}

错误输出（部分重复行示例）：

2000-01-29,3PXI5,37685,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,22.37,23.91,0.00,0.00,0.00,0.00
2000-01-29,3PXI5,37685,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,22.37,23.91,0.00,0.00,0.00,0.00
2000-01-29,3PXI5,37685,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,0.00,1,22.37,23.91,0.00,0.00,0.00,0.00

解决方案 #

核心问题是原命令遍历每一列时，每次匹配到大于0的值就输出整行，导致重复。以下几种方法可解决：

方法1：找到符合条件的列后立即输出并终止循环 #

awk -v FS=, 'NR!=1 {for(i=4; i<=NF; i++) if($i>0) {print $0; break}}' file.csv

• 逻辑说明：当找到第一个大于0的列时，输出当前行，然后用break跳出for循环，避免后续列继续触发输出。
• 注意：将原命令的i<NF改为i<=NF，确保最后一列（METRIC_G）也被检查。

方法2：用标记变量控制输出 #

awk -v FS=, 'NR!=1 {flag=0; for(i=4; i<=NF; i++) if($i>0) {flag=1; break} if(flag) print $0}' file.csv

• 逻辑说明：
  1. 初始化flag=0，表示当前行无符合条件的列。
  2. 遍历列时，若找到大于0的值，设置flag=1并跳出循环。
  3. 遍历结束后，根据flag的值决定是否输出，确保每行最多输出一次。

方法3：利用变量跟踪状态的简化写法 #

awk -v FS=, 'NR!=1 {for(i=4; i<=NF && !found; i++) found=($i>0)} found{print; found=0}' file.csv

• 逻辑说明：用found变量跟踪是否找到符合条件的列，循环在找到匹配项后自动停止；最后根据found状态输出行，然后重置变量处理下一行。

以上三种方法均可得到符合期望的输出，且无重复行。

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