Filling in missing columns

Shell Programming and Scripting
1

Hi all,

I have a file that contains about 1000 rows and 800 columns. Nearly every row has 800 columns but some DONT. I want to extend the rows that dont have values with NA's. Here is an example:

my file

bob  2  4  5  6  8  9  4  5
tar   2  4  5  4  3  2  9  1
bro  3  5  3  4
yar  2  6  2  6  9  8

So I want to fill in each row so that they all have values up to 800 columns

file after

bob  2  4  5  6  8  9  4  5
tar   2  4  5  4  3  2  9  1
bro  3  5  3  4 NA NA NA NA
yar  2  6  2  6  9  8 NA NA

hope I was clear in what I wanted to do

thank you

2

Try awk :wink:

# awk 'NR==FNR{max=(NF>max)?NF:max;next}{for(i=0;++i<=max;)$i=($i)?$i:"NA"}1' file file
bob 2 4 5 6 8 9 4 5
tar 2 4 5 4 3 2 9 1
bro 3 5 3 4 NA NA NA NA
yar 2 6 2 6 9 8 NA NA
3

Add a -v max=(some value) after the awk keyword to tell Dan's command to set the number of columns if the initial line is short and set the max number of columns if the input has more than expected..

4

Hmm I tried but a blank document comes out. Maybe I forgot to mention that the file is tab delimited. Also does it need to be sorted in a particular order?

5 
# awk '{FS=OFS="\t"}NR==FNR{max=(NF>max)?NF:max;next}{for(i=0;++i<=max;)$i=$i?$i:"NA"}1'  file file
bob     2       4       5       6       8       9       4       5
tar     2       4       5       4       3       2       9       1
bro     3       5       3       4       NA      NA      NA      NA
yar     2       6       2       6       9       8       NA      NA
6

hi

I am still getting a blank document. hmm

7

NB:not tab separated

awk 'NF<8{ a=""; for(i=1;i<=8-NF;i++){a=a"NA " }$0=$0" "a}1' file
8

To solve your problem faster please provide the following information:

  1. What is your operating system?
  2. Post the full command line that result in a blank file
  3. Post(copy/paste) a sample data from your original file, head -5 should be OK.