5

I have a .tsv (tab-separated columns) file on a Linux system with the following columns that contain different types of values (strings, numbers) separated by a comma:

col1    col2    
.       NS,NS,NS,true,true      
.       12,12,12,13 
1,1,1,2 door,door,1,1   

I would like to keep the unique values (unfortunately I tried but couldn't). This would be the output:

col1 col2   
.    NS,true        
.    12,13  
1,2  door,1 
0

6 Answers 6

7

Here's a Miller variant that splits the value string into an integer-indexed map, then uniquifies it by swapping the map's keys and values (joining the keys back into a comma-separated string):

mlr --tsv put 'for (k,v in $*) {
  $[k] =  joink(apply(splitnvx(v,","), func(k,v) {return{v:k}}),",")
}' file.tsv

Alternatively, in perl5 with help from the List::Util module:

perl -MList::Util=uniq -F'\t' -lpe '
  $_ = join "\t", map { join ",", uniq split /,/, $_ } @F
' file.tsv
2
  • The mlr --tsv works perfect! What about if I want to the same only on specific columns? Let's say col2 and col3 in a tsv dataset with 100 columns. Many thanks!
    – df_v
    Oct 23, 2023 at 13:17
  • @df_v you can restrict the outer loop to specific columns by key using mapselect ex. for (k,v in mapselect($*,"col2","col3")) { ... } Oct 23, 2023 at 14:06
6

Using Miller (mlr) to loop over the tab-delimited fields in each input record, split the field's values on commas, add each generated string as a key in a map called seen, and finally rewrite the field by joining the keys from the seen map using commas as delimiters:

$ mlr --tsv put 'for (k,v in $*) { seen={}; for (i in splitax(v,",")) { seen[i]=1 } $[k] = joink(seen,",") }' file
col1    col2
.       NS,true
.       12,13
1,2     door,1

The Miller put expression pretty-printed:

for (k, v in $*) {
    seen = {};
    for (i in splitax(v, ",")) {
        seen[i] = 1
    }
    $[k] = joink(seen, ",")
}

Adding the split-up values as keys in the map deduplicates them. The splitax() function splits a string on a delimiter into an array without inferring the type of each generated item (they will be strings). The joink() function joins the keys of a map together into a string delimited by the given delimiter. The values of k and v in the outer loop are the field names and their values, respectively.

4

Here's a clunky Perl approach:

$ perl -F'\t' -le '
%k=%l={}; 
print join("\t", 
  join(",",grep{++$k{$_}==1}split(/,/,$F[0])), 
  join(",",grep{++$l{$_}==1}split(/,/,$F[1]))
)' file.tsv
col1    col2
.   NS,true
.   12,13
1,2 door,1
4

Using Raku (formerly known as Perl_6)

~$ raku -ne '.split("\t")  \
                .map: *.split(",").unique.join(",") andthen  \
             .join("\t").put;'   file

Linewise non-autoprinting (-ne) command-line flags are used. Starting with a TSV file, you split on tabs, then map into each resulting element to again split on commas. Here Raku's unique routine will remove duplicates (you join back again on commas at this point).

A nice feature of the Raku language are what I call "connector" codephrases such as andthen, which can simply be thought of as re-loading the return from the LHS into the $_ topic variable on the RHS. On the RHS the columns are joined back together on tabs and then output.

Sample Input:

col1    col2    
.   NS,NS,NS,true,true  
.   12,12,12,13 
1,1,1,2 door,door,1,1   

Sample Output:

col1    col2    
.   NS,true 
.   12,13   
1,2 door,1

Note: Raku's unique routine takes an as named argument (also known in Raku lingo as an "adverb"). So if you'd like to compare strings for example, you can write .unique(as => *.fc) to compare based upon "foldcase" (i.e. a caseless string comparison).

https://docs.raku.org/routine/unique
https://docs.raku.org/type/Cool#routine_fc
https://docs.raku.org
https://raku.org

4
  • 1
    Nice! And this one is significantly shorter than the perl5 ones I'm coming up with.
    – terdon
    Oct 20, 2023 at 16:12
  • 2
    @terdon if you don't mind cheating with List::Util you could do perl -MList::Util=uniq -F'\t' -pe '$_ = join "\t", map { join ",", uniq split /,/, $_ } @F' file.tsv Oct 20, 2023 at 17:07
  • 1
    Yeah, I was thinking of doing something like that, but I tend to avoid using extra modules for one liners since that requires installing the modules and most people who don't live in Perl-land won't be willing to. Still, that's a really neat approach, please go ahead and post it, @steeldriver, it's much nicer than the monstrosity I came up with.
    – terdon
    Oct 20, 2023 at 17:11
  • 1
    @terdon it looks like (at least on Debian based systems), List::Util is packaged as part of libperl5.xx standard library - so I've posted the solution (along with a Miller variant for fun) Oct 21, 2023 at 16:40
4

The following awk program would do (should work on all moderately recent Awk implementations that support the delete operation on arrays(1)):

BEGIN{FS=OFS="\t"}

{
    for (i=1;i<=NF;i++)
    {
        n=split($i,sf,",")
        delete seen
        fld=""

        for (j=1;j<=n;j++)
        {
            if (!seen[sf[j]]++)
            {
                fld=fld (fld?",":"") sf[j]
            }
        }
        $i=fld
    }
    print
}

Store this as e.g. dedup.awk and call it via

awk -f dedup.awk input.tsv

to act on your input file.

This will set \t as input and output field separator. It will then

  • iterate over all fields of the line (for (i=1;i<=NF;i++))
  • split each field into sub-fields (sf) at the ,
  • iterate over all sub-fields and re-assemble the field in a temporary variable fld, but only add those sub-field values not yet seen in this field. Keep a record in an array seen.
  • In the end, assign the re-assembled field fld to the current field $i
  • Print the entire line including all modifications made so far

(1) Thanks to @EdMorton for the hint that support is rather wide-spread nowadays.

0
1

Here is a one line perl solution that

  • Does not assume duplicate values are adjacent (a property exhibited by your sample data, but not exploited by others)
  • works on any number of columns
$ perl -p -e 's{([^\t\n]+)}{my %u;join ",", grep {!$u{$_}++} split m/,/, $1 }ge' file.tsv

If you want sorted order values, add the word sort before the word grep.

If you want this on files that might have carriage returns (i.e. from MS-Windows), add \r after \n.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .