awk to Fill Empty Column value with Previous Non-Empty Column value:

Question

Would like to read the first column then Fill downward Empty Column value with Previous Non-Empty Column value.

Input.txt

20                        0 ABC          1   N   DEFABC       0     CHARGE      
                          1 ABC          1   N   GHIABC       0     CHARGE      
                          2 ABC          1   N   JKLABC       0     CHARGE      
                          3 ABC          1   N   MNOABC       0     CHARGE      
                          4 ABC          1   N   PQRABC       0     CHARGE      
210&&-2                   0 ABC          1   N   DEFABC       0     CHARGE      
                          1 ABC          1   N   GHIABC       0     CHARGE      
                          2 ABC          1   N   JKLABC       0     CHARGE      
                          3 ABC          1   N   MNOABC       0     CHARGE      
                          4 ABC          1   N   PQRABC       0     CHARGE      
2130&&-4&-6&&-9           0 ABC          1   N   DEFABC       0     CHARGE      
                          1 ABC          1   N   GHIABC       0     CHARGE      
                          2 ABC          1   N   JKLABC       0     CHARGE      
                          3 ABC          1   N   MNOABC       0     CHARGE      
                          4 ABC          1   N   PQRABC       0     CHARGE

Have tried below command script and it is working fine if the file separted "," de-limiter and it is not working for FS="" and FS ="\t" for the above sample input.

$ awk -f FillEmpty.awk Input.txt

$ cat FillEmpty.awk

BEGIN { FS = "" }

$1 != "" { print }

$1 == "" {
        # fill in blanks
        for (i = 1; i <= NR; i++)
                if ($i == "")
                        $i = Saved[i]

        print
}

{
        # save all fields
        for (i = 1; i <= NR; i++)
                Saved[i] = $i
}

Desired Output:

20                        0 ABC          1   N   DEFABC       0     CHARGE      
20                        1 ABC          1   N   GHIABC       0     CHARGE      
20                        2 ABC          1   N   JKLABC       0     CHARGE      
20                        3 ABC          1   N   MNOABC       0     CHARGE      
20                        4 ABC          1   N   PQRABC       0     CHARGE      
210&&-2                   0 ABC          1   N   DEFABC       0     CHARGE      
210&&-2                   1 ABC          1   N   GHIABC       0     CHARGE      
210&&-2                   2 ABC          1   N   JKLABC       0     CHARGE      
210&&-2                   3 ABC          1   N   MNOABC       0     CHARGE      
210&&-2                   4 ABC          1   N   PQRABC       0     CHARGE      
2130&&-4&-6&&-9           0 ABC          1   N   DEFABC       0     CHARGE      
2130&&-4&-6&&-9           1 ABC          1   N   GHIABC       0     CHARGE      
2130&&-4&-6&&-9           2 ABC          1   N   JKLABC       0     CHARGE      
2130&&-4&-6&&-9           3 ABC          1   N   MNOABC       0     CHARGE      
2130&&-4&-6&&-9           4 ABC          1   N   PQRABC       0     CHARGE

Any suggestions ...!

score 4 · Answer 1 · 2014-08-08T09:53:02.490

Awk way with formatting preserved

awk '/^ /{$0=(x)substr($0,21)}{x=substr($0,0,20)}1' file

And another way without needing the length of fields(very similar to tom feneches answer)

awk '/^ /{$0=(x)substr($0,length(x)+1)}{x=$1}1' file

Output of both

20                        0 ABC          1   N   DEFABC       0     CHARGE
20                        1 ABC          1   N   GHIABC       0     CHARGE
20                        2 ABC          1   N   JKLABC       0     CHARGE
20                        3 ABC          1   N   MNOABC       0     CHARGE
20                        4 ABC          1   N   PQRABC       0     CHARGE
210&&-2                   0 ABC          1   N   DEFABC       0     CHARGE
210&&-2                   1 ABC          1   N   GHIABC       0     CHARGE
210&&-2                   2 ABC          1   N   JKLABC       0     CHARGE
210&&-2                   3 ABC          1   N   MNOABC       0     CHARGE
210&&-2                   4 ABC          1   N   PQRABC       0     CHARGE
2130&&-4&-6&&-9           0 ABC          1   N   DEFABC       0     CHARGE
2130&&-4&-6&&-9           1 ABC          1   N   GHIABC       0     CHARGE
2130&&-4&-6&&-9           2 ABC          1   N   JKLABC       0     CHARGE
2130&&-4&-6&&-9           3 ABC          1   N   MNOABC       0     CHARGE
2130&&-4&-6&&-9           4 ABC          1   N   PQRABC       0     CHARGE

+1 and I'm deleting mine as yours is very similar, just get rid of the `a` variable completely as it's adding no value. — Ed Morton, Aug 07 '14 at 14:24
@EdMorton How should i get rid of the a ? Feel free to edit my answer if you think it can be improved. — , Aug 07 '14 at 14:35
Just remove all assignments to and tests of `a`, e.g.: `awk '/^ /{$0=x substr($0,21)} {x=substr($0,0,20)}1' file` — Ed Morton, Aug 07 '14 at 15:05

score 1 · Answer 2 · answered Aug 07 '14 at 12:35

1

You can use this:

awk 'NF==8{m=$1}NF!=8{$0=m$0}1' the.file

However it will break the output formatting.

answered Aug 07 '14 at 12:35

hek2mgl

152,036
28
249
266

1

similar to mine `awk 'a=/^ /{$0=x$0}!a{x=$1}1' file` – Aug 07 '14 at 12:41
@Jidder Yours looks better (because it is generic and doesn't depends on a field count of 8) – hek2mgl Aug 07 '14 at 12:43

Tom Fenech · Accepted Answer · 2014-08-07T15:26:21.230

This works for fixed width:

awk 'substr($0,0,24) ~ $1 { f=$1 }{ $0=f substr($0, length(f)+1) } 1' file

If there is something in the first column, save the value to f. Either way, substitute the value into the line. The 1 at the end ensures that the line is printed.

Testing it out:

$ awk 'substr($0,0,24) ~ $1 { f=$1 }{ $0=f substr($0, length(f)+1) } 1' file
20                        0 ABC          1   N   DEFABC       0     CHARGE      
20                        1 ABC          1   N   GHIABC       0     CHARGE      
20                        2 ABC          1   N   JKLABC       0     CHARGE      
20                        3 ABC          1   N   MNOABC       0     CHARGE      
20                        4 ABC          1   N   PQRABC       0     CHARGE      
210&&-2                   0 ABC          1   N   DEFABC       0     CHARGE      
210&&-2                   1 ABC          1   N   GHIABC       0     CHARGE      
210&&-2                   2 ABC          1   N   JKLABC       0     CHARGE      
210&&-2                   3 ABC          1   N   MNOABC       0     CHARGE      
210&&-2                   4 ABC          1   N   PQRABC       0     CHARGE      
2130&&-4&-6&&-9           0 ABC          1   N   DEFABC       0     CHARGE      
2130&&-4&-6&&-9           1 ABC          1   N   GHIABC       0     CHARGE      
2130&&-4&-6&&-9           2 ABC          1   N   JKLABC       0     CHARGE      
2130&&-4&-6&&-9           3 ABC          1   N   MNOABC       0     CHARGE      
2130&&-4&-6&&-9           4 ABC          1   N   PQRABC       0     CHARGE

This leaves out the 0 records. Think its due to the next skipping the record it sets. — , Aug 07 '14 at 12:50

score 1 · Answer 4 · answered Aug 07 '14 at 12:51

awk '/^[ \t]/ { sub(/^[ \t]+/, ""); print t $0; next }
    { match($0, /^[^ \t]+[ \t]+/); t = substr($0, RSTART, RLENGTH) }
    1' file

Output:

20                        0 ABC          1   N   DEFABC       0     CHARGE      
20                        1 ABC          1   N   GHIABC       0     CHARGE      
20                        2 ABC          1   N   JKLABC       0     CHARGE      
20                        3 ABC          1   N   MNOABC       0     CHARGE      
20                        4 ABC          1   N   PQRABC       0     CHARGE      
210&&-2                   0 ABC          1   N   DEFABC       0     CHARGE      
210&&-2                   1 ABC          1   N   GHIABC       0     CHARGE      
210&&-2                   2 ABC          1   N   JKLABC       0     CHARGE      
210&&-2                   3 ABC          1   N   MNOABC       0     CHARGE      
210&&-2                   4 ABC          1   N   PQRABC       0     CHARGE      
2130&&-4&-6&&-9           0 ABC          1   N   DEFABC       0     CHARGE      
2130&&-4&-6&&-9           1 ABC          1   N   GHIABC       0     CHARGE      
2130&&-4&-6&&-9           2 ABC          1   N   JKLABC       0     CHARGE      
2130&&-4&-6&&-9           3 ABC          1   N   MNOABC       0     CHARGE      
2130&&-4&-6&&-9           4 ABC          1   N   PQRABC       0     CHARGE

awk to Fill Empty Column value with Previous Non-Empty Column value:

4 Answers4

Linked