5

I'd like to remove from a given column ($2 in the example) the duplicate fields (comma separated).

Input file:

A    1,2,3,4   
B    4,5,6,3
C    2,15

Expected output:

A    1,2,3,4
B    5,6
C    15
7
perl -lpe 's/\s\K\S+/join ",", grep {!$seen{$_}++} split ",", $&/e'

You can run the above like so:

$ perl -lpe 's/\s\K\S+/join ",", grep {!$seen{$_}++} split ",", $&/e' afile 
A    1,2,3,4
B    5,6
C    15

How it works

First calling perl with -lpe does the following 3 things.

  • -l[octal] enable line ending processing, specifies line terminator
  • -p assume loop like -n but print line also, like sed
  • -e program one line of program (several -e's allowed, omit programfile)

This essentially take the file in, strips off the newlines, operates on a line, and then tacks a newline character back onto it when it's done. So it's just looping through the file and executing our Perl code against each in turn.

As for the actual Perl code:

  • \s means a spacing character (the five characters [ \f\n\r\t] and \v in newer versions of perl, like [[:space:]]).
  • \K Keep the stuff left of the \K, don't include it in $&
  • \S+ one or more characters not in the set [ \f\n\r\t\v]

The join ",", is going to take the results and rejoin each field so that it's separated by a comma.

The split ",", $& will take the matches that were found by the \S+ and split them into just the fields, without the comma.

The grep {!$seen{$_}++} will take each field's number, add it to the hash, $seen{} where each field's number is $_ as we go through each of them. Each time a field number is "seen" it's counted via the ++ operator, $seen{$_}++.

The grep{!$seen{$_}++} will return a field value if it's only been seen once.

Modified to see what's happening

If you use this modified abomination you can see what's going on as this Perl one liner moves across the lines from the file.

$ perl -lpe 's/\s\K\S+/join ",", grep {!$seen{$_}++} split ",", $&/e; @a=keys %seen; @b=values %seen; print "keys: @a | vals: @b"' afile 
keys: 4 1 3 2 | vals: 1 1 1 1
A    1,2,3,4
keys: 6 4 1 3 2 5 | vals: 1 2 1 2 1 1
B    5,6
keys: 6 4 1 3 2 15 5 | vals: 1 2 1 2 2 1 1
C    15

This is showing you the contents of $seen{} at the end of processing a line from the file. Let's take the 2nd line of the file.

B    4,5,6,3

And here's what my modified version shows that line as:

keys: 6 4 1 3 2 15 5 | vals: 1 2 1 2 2 1 1

So this is saying that we've seen field # 6 (1 time), field # 4 (2 times), etc. and field # 5 (1 time). So when grep{...} returns the results it will only return results from this array if it was present in this line (4,5,6,3) and if we've seen it only 1 time (6,1,15,5). The intersection of these 2 lists is (5,6) and so that's what gets returned by grep.

References

  • 1
    Sometimes we need just a bit more info than the code. If you explain things a little it makes it easier to understand and in future people can solve issue for themselves rather than just copy and past an answer they might not understand. (just my opinion) – AquaAlex Aug 27 '14 at 7:45
  • 1
    @AquaAlex - see if that helps make it more clear. – slm Aug 27 '14 at 8:45
  • @sim WOW! If someone did not understand the post now he should not be asking the question :D – AquaAlex Aug 27 '14 at 8:54
5
{ 
    tr -cs '0-9ABC' '[\n*]' | 
    nl -w1 -s: |
    sort -t: -uk2,2 | sort -t: -k1,1n |
    sed 's/[^:]*://;/^[ABC]/!{H;$!d
        };x;y/\n/,/;s/,/\t/'

} <<\FILE
A       1,2,3,4
B       4,5,6,3
C       2,15
FILE

Basically this just takes some measures to ensure that the data can be run through sort -u and still come back again in relatively the same shape it went out.

First tr transforms any byte in its input that is not a digit or ABC into a newline and squeezes any repeats. So each field gets its own line.

nl then numbers each line in its input. Its output looks like:

1:A
2:1
3:2

...and so on.

Next the data is sorted - twice. The first sort removes all of the duplicate fields - it operates only on its second data field per line and separates those fields with a :. The second sort restores original order - nl's numbered order - by sorting on the first field this time.

Last sed puts it all back together again. It gathers its input, removing all of nl's inserts, and exchanges its hold and pattern buffers for lines ABC and the $last. On each of those it ytransforms all \newlines into ,commas and last substitutes a <tab> for the first of these per line.

And the results?

OUTPUT

A       1,2,3,4
B       5,6
C       15

And that, friends, is what makes Unix awesome.

And, because I was curious, here it is with sed alone:

sed 's/\t\(.*\)/,\1,/;H;$!d;x;:t
     s/,\([^,]*,\)\(.*,\)\1/,\1\2/;tt
     s/,\(\n.\),/\1\t/g;s/,\(.*\),/\t\1/'
' <<\FILE
A       1,2,3,4
B       4,5,6,3
C       2,15
FILE

The first and third lines are like, prep/cleanup. The first one pulls the entire file into pattern space and ensures that every field is comma delimited, so, the first line, for instance:

A,1,2,3,4,

looks like that after it is through.

The second line is where all the work is done. It's actually a recursive replacement function - it continually replaces any comma-delimited field that can be matched elsewhere with only itself until there are no more to be found. This is the function of the test command.

The third line, as I said, just cleans it all up. It puts the commas back where they belong and the tabs and etc, and the end result is:

OUTPUT

A       1,2,3,4
B       5,6
C       15

It was fun, but doing this with any sizable chunk of data is computer murder. It's recursive regex - the worst kind of recursion, probably. So this is a fine kind of thing to do with just a few lines, but doing a spreadsheet in this way is probably ill-advised.

  • 1
    +1 for even thinking of doing this without perl or awk – Volker Siegel Aug 27 '14 at 10:55
  • @VolkerSiegel - I don't even know how to use either of those things. I like sed - sed is predictable. And crabby. We're well-matched, sed and I. – mikeserv Aug 27 '14 at 10:57
  • @Dovah - - I am pleased it pleased you. If you liked the technique, you might also like to look at this. That's the day I discovered it - and I really liked it. But that's not meant like I invented it or whatever - I didn't discover it in the way Fleming discovered penicillin - people have been doing it for a long, long time. I just stumbled over it. It's not my technique, but it's a cool one. – mikeserv Sep 13 '14 at 2:36
4

With awk, this is easy enough using an array, split, and a regular loop:

{
    split($2, elements, ",")
    out = ""
    for (i in elements) {
        el = elements[i]
        if (!(el in used)) {
            out = out el ","
        }
        used[el] = 1
    }
    sub(/,$/, "", out)
    $2 = out
}
1

For each line, we split the second column by commas and save the bits into an array elements. We then build up the new value for that column with a loop, checking whether we've seen the value before or not. We're keeping the set of values we've already seen in the (associative) array used. If el in used, we've seen this one before and shouldn't put it in the output; otherwise, it's new, and we concatenate it to out and add it to our set of seen values so we don't use it again. Finally, we put the constructed list back into the second column. This is essentially the approach you'd take in any other language.

Put the code above into a file and run it with awk -f, or single-quote all of it as an argument on the command line.

  • 1
    +1 Well written, code easy to follow and excellent explanation. – AquaAlex Aug 27 '14 at 7:42

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.