Insert new lines with missing values (NA)

Question

I would like to insert new lines in text files if there are missing values. I have for example the following text file (A.txt), for which line 5 is missing. In addition, as the file should have 12 lines the lines 11-12 are also missing.

1 2.30
2 3.01
3 3.22
4 3.34
6 3.01
7 2.90
8 2.99
9 3.00
10 3.02

My expected output is the following. For missing cases a line should be added with the number and NA. As you see, this happened as desired at line 5, 11 and 12:

1 2.30
2 3.01
3 3.22
4 3.34
5 NA
6 3.01
7 2.90
8 2.99
9 3.00
10 3.02
11 NA
12 NA

I am able to do this by using the following script:

f1=/my-directory/
echo "new file" > "$f1"/newfile.txt  

for i in {1..12}; do
l=$(awk '{print $1}' /"$f1"/A.txt | grep -wE ^$i /"$f1"/A.txt)
if grep --quiet -wE ^$i /"$f1"/A.txt; then echo "$l" >> "$f1"/newfile.txt; else echo "$i NA" >> "$f1"/newfile.txt; fi

done

This works fine. The problem is however that I need to do this for about 600 files containing more than about 160000 lines. The loop solution would therefore take too much time searching through all lines. My question is: is there a simpler solution that could do this?

Answer 1

join works well here:

join -a 1 -o 0,2.2 -e NA  <(seq 12)  A.txt  2>/dev/null

I throw away stderr because join complains if the join field is not lexically sorted.

Answer 2

You can do this with an awk script:

awk '{ while (NR + shift < $1) { print (NR + shift) " NA"; shift++ }; print } END { shift++; while (NR + shift < 13) { print (NR + shift) " NA"; shift++ } }' /tmp/test1

will produce the required output for /tmp/test1 (replace that with each file you wish to process).

In a more readable form:

#!/usr/bin/awk -f
{
    while (NR + shift < $1) {
        print (NR + shift) " NA"
        shift++
    }
    print
}
END {
    shift++
    while (NR + shift < 13) {
        print (NR + shift) " NA"
        shift++
    }
}

Save this as a file, say fill-missing, make it executable, then you can simply run

./fill-missing /tmp/test1

The script processes each line, keeping track of the expected delta with the current line number in shift. So for every line, if the current line adjusted doesn't match the first number in the line, it prints the appropriate line number followed by NA and increments the delta; once the line numbers match, it prints the current line. At the end of the process, it prints any missing lines required to reach 12.

Answer 3

the awk file

BEGIN { i=1 ; }
function upto(x) { while (i<x) printf "%d NA\n",i++ ;}
 { if ( $1 == i ) print ; upto($1) ; i++ ;}
END { upto(final+1) ;}

to be called with

awk -f nl.awk -v final=12 /tmp/test1

your whole loop

cd /my/directory
ls | while read f
do
      awk -f ~/nl.awk -v final=12 $f > /an/other/dir/$f
done

where

• you put awk program in your $HOME dir (~/nl.awk )

Answer 4

You can also try Python:

#!/usr/bin/env python2
with open('file.txt') as f:
    check = 0
    for line in f:
        if int(line.split()[0]) == check + 1:
            check = int(line.split()[0])
            print line.rstrip()
        else:
            check = int(line.split()[0])
            print int(line.split()[0]) - 1, 'NA'
            print line.rstrip()
    print int(line.split()[0]) + 1, 'NA'
    print int(line.split()[0]) + 2, 'NA'

Here we are comparing the line number int(line.split()[0]) with check + 1, we have set initial value of check as 0. If the values are equal we have printed the line, otherwise we have printed the desired content i.e. missing line number and NA. The last two lines are used to print the 11th and 12th lines.

Answer 5

Glenn Jackman's bash and join method with a few tweaks, this function gets the count from the input file, and uses that count as a default:

# Usage: inlwmv file [ missing_value [ extra_lines ] ]
#      if unset, missing_value="NA", and extra_lines=0
inlwmv() { join -a 1  -o 0,2.2  -e "${2:-NA}" \
           <(seq $((${3:-0} + $(tail -n 1 "$1" | cut -d ' ' -f1))) | sort -k 1b,1)  \
           <(sort -k 1b,1 "$1") | \
           sort -g ; }

For the OP question:

inlwmv A.file "" 2

For this duplicate question the Jackman version fails on the last value of afile, (join is quite finicky about ordering, but using sort three times makes it behave as required):

inlwmv afile 0