Text Processing – How to Replace Missing Value Blank Space with Zero

awkperlsedtext processing

I have input.txt tab-delimited text file around 30K lines, I would like to check each row (s1..s30K lines) for missing value (i.e blank space) and fill the missing value with zero value.See out.txt

input.txt

 id  no1  no2  no3  no4
 s1  23   34   45   12
 s2       4    4      
 s3  4         8    0

out.txt

id  no1  no2  no3  no4
s1  23   34   45   12
s2  0     4    4    0  
s3  4     0    8    0

Best Answer

You could do it like this with awk:

awk 'BEGIN { FS = OFS = "\t" } { for(i=1; i<=NF; i++) if($i ~ /^ *$/) $i = 0 }; 1' file

Explanation

Setting FS and OFS to tab ensures the output is correctly delimited. The for-loop looks at every field and sets it to zero if it is empty. The one at the end is a shorthand for { print $0 }.

input

$ cat input.txt
Id  sno1  lc1  sno2  lc2    sno3  lc3  sno4 lc4
RM1  98   ss1   88   ms1    78    gs1   45  rs1
RM2  23   ss2   44   ms2    98    gs2   15  rs2
RM3  45   ss3   100  ms3    33    gs3   10  rs3
RM4  45   ss4   45   ms4    12    gs4   11  rs4

awk script

$ cat row_max.awk
NR == 1 {
        for (i = 1; i <= NF; i++) headers[i] = $i;
        next
}

{
        # find maximum value
        max = $2
        for (i = 4; i <= NF; i += 2) if ($i > max) max = $i;
        # print row id
        printf "%s", $1
        # print all lc# column values (assuming the column 
        # after the max value sno# column)
        sep = OFS
        for (i = 2; i <= NF; i += 2) {
                if ($i == max) {
                        printf "%s%s", sep, $(i + 1);
                        sep = ","
                }
        }
        # print all column headers of the max value columns
        sep = OFS
        for (i = 2; i <= NF; i += 2) {
                if ($i == max) {
                        printf "%s%s", sep, headers[i];
                        sep = ","
                }
        }
        printf "\n"
}

output

$ awk -f row_max.awk input.txt
RM1 ss1 sno1
RM2 gs2 sno3
RM3 ms3 sno2
RM4 ss4,ms4 sno1,sno2

Lum – Column mismatch and substituting

awk -v 'OFS=\t' 'NF == 2 { print $1, "none", $2; next } 1' input.txt > output.txt

Adjust depending on the characteristics of your input file. I assume every line with only 2 fields should have a "none" inserted. Otherwise, all other lines are just passed through unchanged (the purpose of the 1 at the end).

Best Answer

Explanation

Related Solutions

AWK-Find maximum value in a row & print with header

input

awk script

output

Lum – Column mismatch and substituting

Related Question