Awk / sed: replace all fields if any field matches the pattern

Question

I have a tab delimited file with columns of at least 16 (but maybe more), where the first column is a unique identifier; and> 10,000 lines (only 6x6 shown in the example), for example:

ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    4    4     4     -9    4
5    5    5     5     5     5
6    6    -9    6     6     6

I need to change all VAR1-5 values ​​to "-9" if one of the values ​​is already "-9"

So, the desired result:

ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    -9   -9    -9    -9    -9
5    5    5     5     5     5
6    -9   -9    -9    -9    -9

So far I have tried doing this in awk as follows:

awk -F'\t' '
BEGIN{OFS="\t"}
{for(i=2;i<=NF;i++){if ($i=="-9"){for(j=2;j<=NF;j++){$j="-9"};continue}}};1
' < file1.tab

Which works, but very slowly when applied to the actual dataset. Is there a faster way to do this? Perhaps something with a combination of grepand sed?

Answer 1

Here's a variation that doesn't hardcode the number of columns.

awk -F '\t' '/(^|\t)-9(\t|$)/ {
    printf $1; for(i=2; i<=NF; ++i) printf "\t-9"; printf "\n"
    next }
  1' file1 file2

, Awk , , , .

, , , , Awk , . , , , Awk . , .

Answer 2

awk , .

awk 'FNR==1{print;next} /(^|\t)-9(\t|$)/{print $1,"-9   -9    -9    -9    -9";next} 1' OFS="    "   Input_file

, OP 5 Input_file, , , sir, , , -9, -9.

awk 'FNR==1{print;next} /(^|\t)-9(\t|$)/{for(i=2;i<=NF;i++){$i=-9};} 1' OFS="\t\t"   Input_file

.

ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    -9   -9    -9    -9    -9
5    5    5     5     5     5
6    -9   -9    -9    -9    -9

: .

awk '
FNR==1{                ##Checking condition here if line number is 1 then do following:
  print;               ##Printing the current line then which will be very first line of Input_file.
  next                 ##next is awk out of the box keyword which will skip all further statements for program.
}
/(^|\t)-9(\t|$)/{        ##Checking here if -9 is coming in a line either with spaces or without spaces, if yes then do following:
  print $1,"-9   -9    -9    -9    -9";  ##printing the first field of current line along with 5 -9 values as per OPs request to do so.
  next                 ##next will skip all further statements.
}
1                      ##awk works on method of condition then action, so I am making condition TRUE here by mentioning 1 here and not mentioning action here so by default print of the current line will happen.
' OFS="    " Input_file   ##Setting OFS(output field separator) value to spaces and mentioning the Input_file name here.

Answer 3

sed -r '/-9/s/[^ ]+/-9/2g' input.txt

Output

ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    -9    -9     -9     -9    -9
5    5    5     5     5     5
6    -9    -9    -9     -9     -9

Answer 4

Another way to use GNU awk

Single line:

awk '/(^|[ \t]+)-9([ \t]+|$)/{for(i=2; i<=NF; i++)$0=gensub (/[^[:blank:]]+/,-9,i)}1' infile

Better readable:

awk '/(^|[ \t]+)-9([ \t]+|$)/{
       for(i=2; i<=NF; i++)
            $0=gensub (/[^[:blank:]]+/,-9,i)
     }1
    ' infile

Test results:

Input:

$ cat infile
ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    4    4     4     -9    4
5    5    5     5     5     5
6    6    -9    6     6     6

Conclusion:

(due to interval shift -)

$ awk '/(^|[ \t]+)-9([ \t]+|$)/{for(i=2; i<=NF; i++)$0 = gensub (/[^[:blank:]]+/, -9 , i)}1' infile  
ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4    -9    -9     -9     -9    -9
5    5    5     5     5     5
6    -9    -9    -9     -9     -9

If you want the result to look better, try this: (not recommended)

awk '/(^|[ \t]+)-9([ \t]+|$)/{for(i=2; i<=NF; i++){ if($i==-9)continue; $0 = gensub (/[^[:blank:]]+/, "\b-9" , i)}}1' infile  
ID  VAR1  VAR2  VAR3  VAR4  VAR5
1    1    1     1     1     1
2    -9   -9    -9    -9    -9
3    3    3     3     3     3
4   -9   -9    -9     -9   -9
5    5    5     5     5     5
6   -9    -9   -9    -9    -9

More readable version above:

awk '/(^|[ \t]+)-9([ \t]+|$)/{
          for(i=2; i<=NF; i++)
          { 
            if($i==-9)continue; 
            $0 = gensub(/[^[:blank:]]+/, "\b-9" , i)
          }
     }1
    ' infile 

Answer 5

awk 'BEGIN{IFS=OFS="    "}/-9/{for(i=2;i<=NF;i++){$i=-9}}1' filename