1

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?

  • Change the spaces separating the data into tabs, then you can use tabs as your delimiter and see the empty column more easily. – DopeGhoti Aug 4 '17 at 16:54
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
  • Thanks, I am trying to understand the command. If my column is not the third, but the 10th for example, the command will be: awk -F'[[:space:]]' '$9 && !$10{ $10="NA" }1' ? – Alex Aug 4 '17 at 17:17
  • @Paul, presumably, yes – RomanPerekhrest Aug 4 '17 at 20:52
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
  • nice. i didn't even know about FIELDWIDTHS until now, and i've been writing awk/gawk scripts for decades. – cas Aug 5 '17 at 2:12
1

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
  • I do not need to include the info of the third column here? – Alex Aug 4 '17 at 17:18
  • Umm... I'm not sure I follow. I used your example file, and this is the result. – maulinglawns Aug 4 '17 at 17:22
  • My question is if I can apply the same logic to another column. I included here just an example, I have many columns. The one that I need to replace is not the third actually. – Alex Aug 4 '17 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.