Merge columns within a file based on column header

Question

I want to merge different columns within my file that share the same column header. The file looks like this and can be tab-separated or something else:

AaBbN    CcDdEeN    FfN     AaBbN    FfN
1        5          4   
3        1          2
2        NA         1
                            1        3
                            3        2
                            NA       4

So there are numbers or the string "NA" in the fields. The result would look like this:

AaBbN    CcDdEeN    FfN
1        5          4
3        1          2
2        NA         1
1                   3
3                   2
NA                  4

There are a lot of columns that are not ordered, so the title headers would need to be read automatically instead of manually specifying every single one. There are also a lot of empty fields. I've been looking into the paste and join commands to do the job. Especially join seems to do what I need, except it works with separate files, whereas my columns are within the same file.

So I tried to separate the columns into separate files and then combine them with join. I used an awk command that I derived from here:

https://www.unix.com/unix-for-dummies-questions-and-answers/204303-splitting-up-text-file-into-multiple-files-columns.html

awk ' { for( i = 1; i <= NF; i++ ) printf( "%s\n", $(i) ) >i ".txt"; } ' file.txt

which gives me separate columns, but here I ran into the first problem. All columns with empty space between the header and data were not processed correctly. Instead, only the column header was present in these files.

My second problem is with join: When I try to merge files back again, I get errors because the input is not sorted, which is of course impossible to do. Any sorting would destroy the relationship I am looking after.

So here I am at a dead end. Is there a more convenient way to merge the columns directly within a file?

---

Edit:

AdminBees solution comes closest to solving the problem, but the result is not quite right. Here is the result of the awk script applied to the example above. I made sure that all entries are tab separated with sed -i "s/[[:space:]]/ /g" (tab inserted with CTRL+V and TAB).

AaBbN   CcDdEeN FfN     FfN
1   5   4   

3   1   2

2   NA  1

            1
            3
            NA

Answer 1

If your input is tab-separated:

awk -F"\t" '
NR == 1 {for (i=1; i<=NF; i++)  COL[i] = $i
        }
        {for (i=1; i<=NF; i++) OUT[NR, COL[i]] = $i
        }
END     {for (n=1; n<=NR; n++)  {split ("", DUP)
                                 for (i=1; i<=NF; i++)  if (!DUP[COL[i]]++) printf "%s" FS, OUT[n,COL[i]]
                                 printf RS
                                }
        }
' file
A   B   C   
1   5   4   
3   1   2   
2   2   1   
1       3   
3       2   
1       4   

It saves column headers for use as partial indices later, then for each line collects values into an array indexed by line No. and header partial index. In the END section, it prints that array in the original sequence taking care of duplicate column headers.

Duplicate handling may become a major effort for more intricate file structures.

Answer 2

A slightly different approach which doesn't require "buffering" the entire file:

AWK script colmerge.awk:

FNR==1{
    for (i=1; i<=NF; i++)
    {
    hdr[i]=$i;
    if (map[$i]==0) {map[$i]=i; uniq_hdr[++u]=$i; printf("%s",$i);}
    if (i==NF) printf("%s",ORS); else printf("%s",OFS);
    }
}

FNR>1{
    delete linemap;
    for (i=1; i<=NF; i++) if ($i!="") linemap[hdr[i]]=$i;
    for (i=1; i<=u; i++)
    {
    printf("%s",linemap[uniq_hdr[i]]);
    if (i==u) printf("%s",ORS); else printf("%s",OFS);
    }
}

Use as

awk -F'\t' -v OFS='\t' -f colmerge.awk file

This will gather all headers and identify the "unique" headers and their first occurence on line 1, and for each successive line create a map between headers and non-empty values, which is then printed out in the order of the "unique" headers as identified while processing the first line.

This only works, however, if your input file is tab-separated, as this is the only way to reliably detect "empty" fields.

Note also that the delete statement for the entire array linemap may not be supported by all awk implementations (should work on gawk, mawk and nawk, however).

Answer 3

for the tab-separated input.

read header and the corresponding columns number(s) into an array where they appeared in input file; then splitting the input file on each column into the same filename headerName.txt having same headerName. after all paste them together and column command used for beautifying output.

awk -F'\t' '
    ## find all the column number(s) when same header found and store in `h` array
    ## key is the column number and value is header name. for an example:
    ## for the header value 'A', keys will be columns 1 &4
    NR==1{ while (++i<=NF) h[i]=$i; next; }

         { for (i=1; i<=NF; i++) {

    ## save the field content to a file which its key column matches with the column 
    ## number of the current field. for an example:
    ## for the first field in column 1; the column number is 1, and so 1 is the key  
    ## column for header value A, so this will be written to "A.txt" filename
    ## only if it was not empty.
               if ($i!=""){ print $i> h[i]".txt" };
         }; }

    ## at the end paste those all files and beautify output with `column` command.
    ## number of .txt files above is limit to the number of uniq headers in your input. 
END{ system("paste *.txt |column \011 -tn") }' infile

comments-free command:

awk -F'\t' '
    NR==1{ while (++i<=NF) h[i]=$i; next; }
         { for (i=1; i<=NF; i++) {
               if ($i!=""){ print $i> h[i]".txt" };
         }; }
END{ system("paste *.txt |column \011 -tn") }' infile