merge multiple rows in specific columns

Question

I have a file like this:

xxx ax1 bx1
xxx ax2 bx2 cx2
xxx ax3 bx3
yyy ay1     cy1
zzz az1     cz1
zzz az1 bz2
...

I want the output like this:

xxx ax1,ax2,ax3 bx1,bx2,bx3 cx2
yyy ay1                     cy1
zzz az1         bz2         cz1
...

I tried to use awk awk -F'\t' -v OFS='\t' '{x=$1;$1="";a[x]=a[x]$0}END{for(x in a)print x,a[x]}' file. but this command ignore the column which they belongs, the output is not what I want.

Answer 1

awk '
BEGIN{ FS=OFS="\t" }
     { for(i=2; i<=NF;i++)
       if (!seen[$1, $i, i]++)
           grp[$1, i]=(grp[$1, i]==""?"":grp[$1, i] ($i!=""?",":"")) $i
       else
           grp[$1, i]= grp[$1, i]
     }

END{ for(x in grp) {
         split(x, tmp, SUBSEP);
         join[tmp[1]]=(join[tmp[1]]==""?"":join[tmp[1]] OFS) grp[x]
     }
     for (x in join) print x, join[x]
}' infile

Output:

yyy     ay1             cy1
xxx     ax1,ax2,ax3     bx1,bx2,bx3     cx2
zzz     az1     bz2     cz1

Enclosing fields within brackets to check that their positions are correct:

awk '
BEGIN{ FS=OFS="\t" }
     { for(i=2; i<=NF;i++)
       if (!seen[$1, $i, i]++)
           grp[$1, i]=(grp[$1, i]==""?"":grp[$1, i] ($i!=""?",":"")) $i
       else
           grp[$1, i]= grp[$1, i]
     }

END{ for(x in grp) {
         split(x, tmp, SUBSEP);
         join[tmp[1]]=(join[tmp[1]]==""?"":join[tmp[1]] OFS) "["grp[x]"]"
     }
     for (x in join) print x, join[x]
}' infile

Output:

yyy     [ay1]   []      [cy1]
xxx     [ax1,ax2,ax3]   [bx1,bx2,bx3]   [cx2]
zzz     [az1]   [bz2]   [cz1]

---

Notes:

• this answer will work regardless of if your input is sorted or not.
• this answer will shuffle the records when outputting.
• The code !seen[$1, $i, i]++ is used to unique the entries for each Id within columns. If you want to remove the duplicated entries within all columns regardless of the Id, then change that to !seen[$1, $i]++);

for an example:

$ cat infile
xxx     ax1     ax1
xxx     ax1     bx2     ax3
xxx     ax3     bx2
yyy     ay1     ay1     cy3
zzz     az1     bz3     cz1
zzz     az1     bz2     bz3

Output (when !seen[$1, $i, i]++); Duplicated entries are removed only within each columns belonging to that Ids:

yyy     [ay1]   [ay1]   [cy3]
xxx     [ax1,ax3]       [ax1,bx2]       [ax3]
zzz     [az1]   [bz3,bz2]       [cz1,bz3]

Output (when seen[$1, $i]++); All the duplicated entries are removed regardless of that entry's column position:

yyy     [ay1]   []      [cy3]
xxx     [ax1]   [bx2]   [ax3]
zzz     [az1]   [bz3,bz2]       [cz1]

Answer 2

$ cat tst.awk
BEGIN { FS=OFS="\t" }
$1 != prev {
    if ( NR > 1 ) {
        prt()
    }
    prev = $1
}
{
    for ( i=1; i<=NF; i++ ) {
        if ( ($i == "") || (seen[i,$i]++) ) {
            uniq[i]
        }
        else {
            uniq[i] = (uniq[i] == "" ? "" : uniq[i] ",") $i
        }
    }
}
END {
    prt()
}

function prt(   i) {
    for ( i=1; i in uniq; i++ ) {
        printf "%s%s", (i>1 ? OFS : ""), uniq[i]
    }
    print ""
    delete uniq
    delete seen
}

$ awk -f tst.awk file
xxx     ax1,ax2,ax3     bx1,bx2,bx3     cx2
yyy     ay1             cy1
zzz     az1     bz2     cz1

$ awk -f tst.awk file | column -s$'\t' -t
xxx  ax1,ax2,ax3  bx1,bx2,bx3  cx2
yyy  ay1                       cy1
zzz  az1          bz2          cz1