2

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.

2 Answers 2

2
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]
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.