How to remove duplicate value in a tab-delimited text file

Question

I have a tab delimited column text like below

A      B1      B1     C1
B      B2      D2 
C      C12     C13    C13
D      D3      D5      D9
G      F2      F2

how could I convert the above table like below

A      B1     C1
B      B2     D2 
C      C12    C13
D      D3     D5     D9
G      F2

I have extracted my real data file, it is a tab delimited file and I have tried the command line you (Stéphane Chazelas?) posted it works fine but it couldn't remove the duplicate on the last column

A  CD274    PDCD1LG2  CD276   PDCD1LG2  CD274
B  NEK2     NEK6      NEK10   NEK10     NEKL-4
C  TNFAIP3  OTUD7B    OTUD7B  TNFAIP3   TNFAIP3
D  DUSP16   DUSP4     DUSP8   VHP-1     DUSP8
E  AGO2     AGO2      AGO2    AGO2      AGO2

output need to be as below

A  CD274    CD276   PDCD1LG2
B  NEK2     NEK6    NEK10     NEKL-4
C  TNFAIP3  OTUD7B
D  DUSP16   DUSP4   DUSP8     VHP-1
E  AGO2

Does order of fields in a line in output is important ? like `AGO2 E` or `C OTUD7B TNFAIP3` — αғsнιη, Sep 27 '17 at 09:09
@αғsнιη `A` `B` `C` seems to be the line numbering, I think at least they should stay there. — dessert, Sep 27 '17 at 10:01
If you're happy with one or several of the answers, upvote them. If one is solving your issue, [accepting it](https://unix.stackexchange.com/help/someone-answers) would be the best way of saying "Thank You!" :-) — Kusalananda, Sep 27 '17 at 10:50

Kusalananda · Answer 1 · 2017-09-26T23:22:27.697

First set of example data:

$ awk -vOFS='\t' '{ r=""; delete t; for (i=1;i<=NF;++i) { if (!t[$i]++) { r = r ? r OFS $i : $i } } print r }' file
A       B1      C1
B       B2      D2
C       C12     C13
D       D3      D5      D9
G       F2

Second set of example data (same awk script):

$ awk -vOFS='\t' '{ r=""; delete t; for (i=1;i<=NF;++i) { if (!t[$i]++) { r = r ? r OFS $i : $i } } print r }' file
A       CD274   PDCD1LG2        CD276
B       NEK2    NEK6    NEK10   NEKL-4
C       TNFAIP3 OTUD7B
D       DUSP16  DUSP4   DUSP8   VHP-1
E       AGO2

The script reads the input file file line by line, and for each line it goes through each field, building up the output line, r. If the value in a field has already been added to the output line (determined by a lookup table, t, of used field values), then the field is ignored, otherwise it's added.

When all the fields of an input line have been processed, the constructed line is outputted.

The output field delimiter is set to tab through -vOFS='\t' on the command line.

The awk script unravelled:

{
    r = ""
    delete t

    for (i = 1; i <= NF; ++i) {
        if (!t[$i]++) {
            r = r ? r OFS $i : $i
        }
    }

    print r
}

See `split("", t)` for the POSIX equivalent to `delete t` – Stéphane Chazelas Sep 27 '17 at 06:45 — Stéphane Chazelas, Sep 27 '17 at 06:45

dessert · Answer 2 · 2017-09-26T22:19:50.810

sed/tr, uniq and paste

while read -r l; do sed 's/\t/\n/g' <<< "$l" | uniq | paste -s; done < test

or POSIX compliant:

while read -r l; do echo "$l" | tr '\t' '\n' | uniq | paste -s -; done < test

For the file test this will line by line replace all Tab characters with linebreaks, run uniq to delete dupes and replace the linebreaks with Tab characters again.

$ cat test
A       B1      B1      C1
B       B2      D2
C       C12     C13     C13
D       D3      D5      D9
G       F2      F2

$ while read -r l; do sed 's/\t/\n/g' <<< "$l" | uniq | paste -s; done < test
A       B1      C1
B       B2      D2
C       C12     C13
D       D3      D5      D9
G       F2

NB: This solution will not work for duplicates over multiple rows, e.g. C1 in

A       B1      B1      C1
C1      B       B2      D2

Stéphane Chazelas · Answer 3 · 2017-09-27T06:48:55.637

6

Maybe something like:

gawk -vRS='\\s*\\S*' -vORS= '{$0=RT};$1!=prev;{prev=$1}'

The RS=pattern...{$0=RT} trick lets you process records defined as the parts that match the pattern.

So here, we're slicing the input into <whitespace><non-whitespace> $0 records, <non-whitespace> goes in $1 (the first and only field). We're printing the records whose $1 is not equal to the previous one.

On an input like:

A      B1      B1     C1
B      B2      D2 
C      C12     C13    C13
D      D3      D5      D9
G      F2      F2

The records are:

[A][      B1][      B1][     C1][
B][      B2][      D2][ 
C][      C12][     C13][    C13][
D][      D3][      D5][      D9][
G][      F2][      F2][
]

Doesn't work for your second example though and note that it could remove some newline characters.

edited Sep 27 '17 at 06:48

answered Sep 26 '17 at 21:34

Stéphane Chazelas

522,931
91
1,010
1,501

What if a row begins with a dupe from the preceding line, e.g. if we add `C1` at the beginning of row 2? The linebreak clearly should not get removed even then. – dessert Sep 26 '17 at 22:00
A CD274 PDCD1LG2 CD276 PDCD1LG2 CD274 B NEK2 NEK6 NEK10 NEK10 NEKL-4 C TNFAIP3 OTUD7B OTUD7B TNFAIP3 TNFAIP3 D DUSP16 DUSP4 DUSP8 VHP-1 DUSP8 E AGO2 AGO2 AGO2 AGO2 AGO2 – desu Sep 26 '17 at 22:13
3

@desu, whatever you're trying to say to clarify your question, please edit it in your question. You may want to take the [tour](/tour) for some advise on how to ask great questions. – Stéphane Chazelas Sep 26 '17 at 22:17
@desu add `-F'\n'` to separate each input lines, so `gawk -F'\n' -vRS='\\s*\\S*' -vORS= '{$0=RT};$1!=prev;{prev=$1}'` – αғsнιη Sep 27 '17 at 09:48
ping to @dessert too ^^ – αғsнιη Sep 27 '17 at 09:49
1

@αғsнιη, not sure what you mean. `\n` is already included in the default FS. The problem here is that if that `\n` is part of a record that is deleted, it will be deleted. Anyway, that answer doesn't answer the OP's question any more with their updated requirements. I'm only leaving it in for the _trick_ which may be useful in other situations. – Stéphane Chazelas Sep 27 '17 at 09:57
@StéphaneChazelas I'm referring to [this comment](https://unix.stackexchange.com/questions/394634/how-to-remove-duplicate-value-in-a-tab-delimited-text-file#comment704101_394636). and it since it's removing `\n` within records because when `RS` is defined, so to keeping a line like `C1 CDXWS2 D2` after first line `A B1 B1 C1`, you need re-enable the `FS` to `\n` – αғsнιη Sep 27 '17 at 10:16
@αғsнιη, I understand you refer to dessert's comment. But I don't understand how you suggest to address it. To address it, we'd need to preserve the newline in the record being deleted and also remove leading blanks in the next non-deleted record. Too much effort, better to go with a different approach like Kusalananda's – Stéphane Chazelas Sep 27 '17 at 10:26

score 2 · Answer 4 · answered Sep 27 '17 at 07:08

2

This is more of a code-golf / freak challenge solution:

xargs -L1 -I{} echo '; {}' < ./test.txt | \
      xargs -n1 | \
      uniq | \
      xargs | \
      sed -e 's/; /\n/g' -e 's/ \+/\t/g'

But it avoids using loops and all other heavy machinery seen in other answers.

It also builds on an assumption your data doesn't contain ; character.

answered Sep 27 '17 at 07:08

wvxvw

619
2
6
14

It also assumes no `"`, `'` backslash characters and that none of the words look like `-n`, `-e`, `-nEne`... (depending on the `echo` implementation) It also assume GNU `sed`. It still spawns one `echo` process per line. But it's true that it's less heavy than some of the `while` loops seen around. It doesn't work for the updated requirements where the duplicated words may no longer be contiguous. – Stéphane Chazelas Sep 27 '17 at 10:43
@StéphaneChazelas the argument to `echo` is quoted, so that the values that look like options won't be interpreted as such. What part of `sed` call isn't POSIX? (I honestly don't know). – wvxvw Sep 27 '17 at 10:53
No quoting doesn't prevent option processing. Try `printf '%s\n' -n -ne foo | xargs`. Note that `xargs -n1` means that one `echo` is being run for each word which is quite _heavy_ actually. `\n`, `\+` and `\t` are GNU extensions, though you do find some other implementations supporting it nowadays. – Stéphane Chazelas Sep 27 '17 at 12:29
@StéphaneChazelas Well, maybe it's `echo` implementation issue, but for me `echo "-n 'foo'" | xargs -L1 -I{} echo '; {}'` prints `; -n foo`, i.e. `-n` wasn't treated as an option. Or, do you mean this will propagate to `uniq`? I think I see your point now. – wvxvw Sep 27 '17 at 13:19
Yes, it doesn't apply to the first `echo` as the argument starts with `;`, it applies to the other ones (the ones implictely run by `xargs` upon `xargs` or `xargs -n1` alone). – Stéphane Chazelas Sep 27 '17 at 13:53

Stéphane Chazelas · Answer 5 · 2017-09-27T10:45:02.840

1

With perl:

unique words on each line:

perl -MList::Util=uniq -lape '$_ = join "\t", uniq @F'

unique words globally:

perl -lape '$_ = join "\t", grep {!$count{$_}++} @F'

Or to only consider words of each line starting with the 2^nd one:

perl -lape '$_ = join "\t", shift(@F), grep {!$count{$_}++} @F'

edited Sep 27 '17 at 10:45

answered Sep 27 '17 at 10:08

Stéphane Chazelas

522,931
91
1,010
1,501

αғsнιη · Answer 6 · 2017-09-27T10:46:01.977

With bash v4.3 (if you don't mind the order of fields as it's sorted except first)

while IFS='\n' read -r line; 
    do aline=( $line );
    echo ${aline[0]} $(sort -u <(printf "%s\n" ${aline[@]:1}));
done < infile

Explanation:

aline=( $line ) this make the line save into an array 'aline'
${aline[0]} prints first element of an array 'aline' (array index is starting with zero in bash)
printf "%s\n" ${aline[@]:1} prints each element of array 'aline' in separate lines and ignore first element; Then
sort -u sorts each line and remove duplicates entries
echo this also combine splited line elements after sort into one linear.

Please see below example to have better view of this step:
```
printf "C\n4\nB\nC" |sort -u 
4
B
C
echo $(printf "C\n4\nB\nC" |sort -u)
4 B C
```

This will give output as:

A CD274 CD276 PDCD1LG2
B NEK10 NEK2 NEK6 NEKL-4
C OTUD7B TNFAIP3
D DUSP16 DUSP4 DUSP8 VHP-1
E AGO2

score 0 · Answer 7 · answered Sep 27 '17 at 11:36

0

sed substitution with back reference

sed -re 's/\s+$//; s/(\t[^\t]+)\1+$/\1/'

(s/\s+$// gets rid of trailing white-space like in your example.)

answered Sep 27 '17 at 11:36

David Foerster

1,505
1
11
18

How to remove duplicate value in a tab-delimited text file

7 Answers7

sed/tr, uniq and paste

sed substitution with back reference