use awk to delete all lines by uniq on certain column if there are more than 2 values in other colum-CodePudding

Good morning. I've a big ASCII file with 6 columns. This file have a number of rows multiple of 24 (the fourth column is a date, %Y%m%d%H%M : 24 rows-->1 day) and represent a unique (same values of column 1,2,5,6 in those 24 rows: is a measurement station). That's a cut example with 2x24 rows, that is 2 different stations:

1_200061208 0 0.000000 202202150000 36.680573 15.094369
1_200061208 0 0.000000 202202150100 36.680573 15.094369
1_200061208 0 -99999 202202150200 36.680573 15.094369
1_200061208 0 0.000000 202202150300 36.680573 15.094369
1_200061208 0 0.000000 202202150400 36.680573 15.094369
1_200061208 0 0.000000 202202150500 36.680573 15.094369
1_200061208 0 0.000000 202202150600 36.680573 15.094369
1_200061208 0 0.000000 202202150700 36.680573 15.094369
1_200061208 0 -99999 202202150800 36.680573 15.094369
1_200061208 0 0.000000 202202150900 36.680573 15.094369
1_200061208 0 0.000000 202202151000 36.680573 15.094369
1_200061208 0 0.000000 202202151100 36.680573 15.094369
1_200061208 0 0.000000 202202151200 36.680573 15.094369
1_200061208 0 0.000000 202202151300 36.680573 15.094369
1_200061208 0 0.000000 202202151400 36.680573 15.094369
1_200061208 0 0.000000 202202151500 36.680573 15.094369
1_200061208 0 0.000000 202202151600 36.680573 15.094369
1_200061208 0 0.000000 202202151700 36.680573 15.094369
1_200061208 0 0.000000 202202151800 36.680573 15.094369
1_200061208 0 0.000000 202202151900 36.680573 15.094369
1_200061208 0 0.000000 202202152000 36.680573 15.094369
1_200061208 0 0.000000 202202152100 36.680573 15.094369
1_200061208 0 0.000000 202202152200 36.680573 15.094369
1_200061208 0 0.000000 202202152300 36.680573 15.094369
1_200061190 0 0.000000 202202150000 36.728195 14.993018
1_200061190 0 0.000000 202202150100 36.728195 14.993018
1_200061190 0 0.000000 202202150200 36.728195 14.993018
1_200061190 0 0.000000 202202150300 36.728195 14.993018
1_200061190 0 0.000000 202202150400 36.728195 14.993018
1_200061190 0 0.000000 202202150500 36.728195 14.993018
1_200061190 0 0.000000 202202150600 36.728195 14.993018
1_200061190 0 0.000000 202202150700 36.728195 14.993018
1_200061190 0 0.000000 202202150800 36.728195 14.993018
1_200061190 0 0.000000 202202150900 36.728195 14.993018
1_200061190 0 0.000000 202202151000 36.728195 14.993018
1_200061190 0 0.000000 202202151100 36.728195 14.993018
1_200061190 0 0.000000 202202151200 36.728195 14.993018
1_200061190 0 0.000000 202202151300 36.728195 14.993018
1_200061190 0 0.000000 202202151400 36.728195 14.993018
1_200061190 0 -99999 202202151500 36.728195 14.993018
1_200061190 0 0.000000 202202151600 36.728195 14.993018
1_200061190 0 0.000000 202202151700 36.728195 14.993018
1_200061190 0 0.000000 202202151800 36.728195 14.993018
1_200061190 0 0.000000 202202151900 36.728195 14.993018
1_200061190 0 0.000000 202202152000 36.728195 14.993018
1_200061190 0 0.000000 202202152100 36.728195 14.993018
1_200061190 0 0.000000 202202152200 36.728195 14.993018
1_200061190 0 0.000000 202202152300 36.728195 14.993018

My goal is to check if in column three there are more than 1 occurrence of -9999 per day (24 rows) for the same station (column 1,2,5,6); in that case, I want to delete the entire 24 rows (in other words, I want to remove the entire measurement day for this station).

The expected output is the same file without the 24xn lines that satisfy my check. In the presented example, the expected output is:

1_200061190 0 0.000000 202202150000 36.728195 14.993018
1_200061190 0 0.000000 202202150100 36.728195 14.993018
1_200061190 0 0.000000 202202150200 36.728195 14.993018
1_200061190 0 0.000000 202202150300 36.728195 14.993018
1_200061190 0 0.000000 202202150400 36.728195 14.993018
1_200061190 0 0.000000 202202150500 36.728195 14.993018
1_200061190 0 0.000000 202202150600 36.728195 14.993018
1_200061190 0 0.000000 202202150700 36.728195 14.993018
1_200061190 0 0.000000 202202150800 36.728195 14.993018
1_200061190 0 0.000000 202202150900 36.728195 14.993018
1_200061190 0 0.000000 202202151000 36.728195 14.993018
1_200061190 0 0.000000 202202151100 36.728195 14.993018
1_200061190 0 0.000000 202202151200 36.728195 14.993018
1_200061190 0 0.000000 202202151300 36.728195 14.993018
1_200061190 0 0.000000 202202151400 36.728195 14.993018
1_200061190 0 -99999 202202151500 36.728195 14.993018
1_200061190 0 0.000000 202202151600 36.728195 14.993018
1_200061190 0 0.000000 202202151700 36.728195 14.993018
1_200061190 0 0.000000 202202151800 36.728195 14.993018
1_200061190 0 0.000000 202202151900 36.728195 14.993018
1_200061190 0 0.000000 202202152000 36.728195 14.993018
1_200061190 0 0.000000 202202152100 36.728195 14.993018
1_200061190 0 0.000000 202202152200 36.728195 14.993018
1_200061190 0 0.000000 202202152300 36.728195 14.993018

Any kind of suggestion are welcome! :-) Many thanks in advance!

CodePudding user response：

One awk idea using 2 passes of the input file:

awk '
FNR==NR { if ($3 == "-99999")             # 1st pass: collect count of "-99999" instances
             a[$1 FS $2 FS $5 FS $6]  
          next
        }

 a[$1 FS $2 FS $5 FS $6] 0 <= 1           # 2nd pass: print current line if "-99999" count <= 1; 
                                          # " 0" ==> force non-existent array entry to be processed as a numeric having value of "0"
' filename.txt filename.txt

This generates:

1_200061190 0 0.000000 202202150000 36.728195 14.993018
1_200061190 0 0.000000 202202150100 36.728195 14.993018
1_200061190 0 0.000000 202202150200 36.728195 14.993018
1_200061190 0 0.000000 202202150300 36.728195 14.993018
1_200061190 0 0.000000 202202150400 36.728195 14.993018
1_200061190 0 0.000000 202202150500 36.728195 14.993018
1_200061190 0 0.000000 202202150600 36.728195 14.993018
1_200061190 0 0.000000 202202150700 36.728195 14.993018
1_200061190 0 0.000000 202202150800 36.728195 14.993018
1_200061190 0 0.000000 202202150900 36.728195 14.993018
1_200061190 0 0.000000 202202151000 36.728195 14.993018
1_200061190 0 0.000000 202202151100 36.728195 14.993018
1_200061190 0 0.000000 202202151200 36.728195 14.993018
1_200061190 0 0.000000 202202151300 36.728195 14.993018
1_200061190 0 0.000000 202202151400 36.728195 14.993018
1_200061190 0 -99999 202202151500 36.728195 14.993018
1_200061190 0 0.000000 202202151600 36.728195 14.993018
1_200061190 0 0.000000 202202151700 36.728195 14.993018
1_200061190 0 0.000000 202202151800 36.728195 14.993018
1_200061190 0 0.000000 202202151900 36.728195 14.993018
1_200061190 0 0.000000 202202152000 36.728195 14.993018
1_200061190 0 0.000000 202202152100 36.728195 14.993018
1_200061190 0 0.000000 202202152200 36.728195 14.993018
1_200061190 0 0.000000 202202152300 36.728195 14.993018