How to alter a column in a row in a tab delimited text file if it's duplicated

Question

Lets say I have these many rows

chr1    66999638    66999638    SGIP1   1   +
chr1    66999251    66999251    SGIP1   1   +
chr1    33545778    33549778    AZIN2   1   +
chr1    8376144 8380144 SLC45A1 1   +
chr1    16765166    16769166    NECAP2  1   +
chr1    33544713    33548713    AZIN2   1   +
chr1    25069759    25073759    CLIC4   1   +
chr1    33544729    33548729    AZIN2   1   +
chr1    50487626    50491626    AGBL4   1   -
chr1    92349836    92353836    TGFBR3  1   -

Expected output:

chr1    66999638    66999638    SGIP1   1   +
chr1    66999251    66999251    SGIP1_1 1   +
chr1    33545778    33549778    AZIN2   1   +
chr1    83761448380144  SLC45A1 1   +
chr1    16765166    16769166    NECAP2  1   +
chr1    33544713    33548713    AZIN2_1 1   +
chr1    25069759    25073759    CLIC4   1   +
chr1    33544729    33548729    AZIN2_2 1   +
chr1    50487626    50491626    AGBL4   1   -
chr1    92349836    92353836    TGFBR3  1   -

I would like a way to alter the second row (and 3rd, 4th, etc.) which is duplicated in column $4. I simply want to add a "_1" string so it reads "SGIP_1" or "WhateverGeneName_1".

Preferably an awk or sed solution would be best. Thanks so much in advance.

Answer 1

In the simplest case where you want all gene names to have _N appended even if they only appear once, you can do:

$ awk '$4=$4"_"++a[$4];' file.gff 
chr1 66999638 66999638 SGIP1_1 1 +
chr1 66999251 66999251 SGIP1_2 1 +
chr1 33545778 33549778 AZIN2_1 1 +
chr1 8376144 8380144 SLC45A1_1 1 +
chr1 16765166 16769166 NECAP2_1 1 +
chr1 33544713 33548713 AZIN2_2 1 +
chr1 25069759 25073759 CLIC4_1 1 +
chr1 33544729 33548729 AZIN2_3 1 +
chr1 50487626 50491626 AGBL4_1 1 -
chr1 92349836 92353836 TGFBR3_1 1 -

Note that that will change the field separator to a single space. To keep it tab separated (as GFF files should be), use:

$ awk -vOFS="\t" '$4=$4"_"++a[$4];' file.gff 
chr1    66999638    66999638    SGIP1_1 1   +
chr1    66999251    66999251    SGIP1_2 1   +
chr1    33545778    33549778    AZIN2_1 1   +
chr1    8376144 8380144 SLC45A1_1   1   +
chr1    16765166    16769166    NECAP2_1    1   +
chr1    33544713    33548713    AZIN2_2 1   +
chr1    25069759    25073759    CLIC4_1 1   +
chr1    33544729    33548729    AZIN2_3 1   +
chr1    50487626    50491626    AGBL4_1 1   -
chr1    92349836    92353836    TGFBR3_1    1   -

If you only want to modify the names of those genes that appear more than once, it gets a little bit more complex:

$ awk -vOFS="\t" '(++a[$4]>1){$4=$4"_"a[$4]-1}1;' file.gff
chr1    66999638    66999638    SGIP1   1   +
chr1    66999251    66999251    SGIP1_1 1   +
chr1    33545778    33549778    AZIN2   1   +
chr1    8376144 8380144 SLC45A1 1   +
chr1    16765166    16769166    NECAP2  1   +
chr1    33544713    33548713    AZIN2_1 1   +
chr1    25069759    25073759    CLIC4   1   +
chr1    33544729    33548729    AZIN2_2 1   +
chr1    50487626    50491626    AGBL4   1   -
chr1    92349836    92353836    TGFBR3  1   -

Answer 2

Using awk

awk -vOFS="\t" '{$4=a[$4]++?$4"_"a[$4]-1:$4}1' file

chr1    66999638        66999638        SGIP1   1       +
chr1    66999251        66999251        SGIP1_1 1       +
chr1    33545778        33549778        AZIN2   1       +
chr1    8376144 8380144 SLC45A1 1       +
chr1    16765166        16769166        NECAP2  1       +
chr1    33544713        33548713        AZIN2_1 1       +
chr1    25069759        25073759        CLIC4   1       +
chr1    33544729        33548729        AZIN2_2 1       +
chr1    50487626        50491626        AGBL4   1       -
chr1    92349836        92353836        TGFBR3  1       -

Sets $4 to equal itself if only one occurence has been seen or adds _ then the number of occurences seen minus 1.