How to replace empty cells of a specific column

Question

I have a file with many columns and some empty cells in different columns. I would like to replace the empty cells for NA only in the third column. My file:

1 id1 info 
2     otherinfo
3 id2
4     noinfo
5 id3
6 id4 info2 

So the output should be:

1 id1 info 
2     otherinfo
3 id2 NA
4     noinfo
5 id3 NA
6 id4 info2

I tried this command (Replacing missing value blank space with zero), but it replaced all empty cells. Any suggestions?

Answer 1

Short awk solution:

awk -F'[[:space:]]' '$2 && !$3{ $3="NA" }1' file

The output:

1 id1 info 
2     otherinfo
3 id2 NA
4     noinfo
5 id3 NA
6 id4 info2

Answer 2

If your file has fixed-width columns, you can parse them in GNU awk with FIELDWIDTHS, e.g.:

awk -v FIELDWIDTHS='1 1 3 1 99' -v OFS="" '!$5 { $5 = " NA" } 1' infile

Output:

1 id1 info
2     otherinfo
3 id2 NA
4     noinfo
5 id3 NA
6 id4 info2

Answer 3

This sed works for me:

sed -E 's/(.*id[0-9]{1,}$)/\1 NA/'

Example:

sed -E 's/(.*id[0-9]{1,}$)/\1 NA/' NA.txt 
1 id1 info 
2     otherinfo
3 id2 NA
4     noinfo
5 id3 NA
6 id4 info2

Where NA.txt is this file:

cat NA.txt 
1 id1 info 
2     otherinfo
3 id2
4     noinfo
5 id3
6 id4 info2