4

I have a long CSV file with two columns, which includes runs of consecutive duplicates like this:

...
1500,1533
1554,1678
1554,1703
1554,1728
1593,1766
...

I need to delete all of these duplicates except for the last one - so the output for the example above would be:

...
1500,1533
1554,1728
1593,1766
...

Also I need to keep the rest of the lines in the file in their original order.

I tried tac file.csv | sort -k1,1 -r -u -t,

but this did not give the desired result, and sort based functions messed up my line order.

  • 1
    Did you forget to specify the sort delimiter -t,? Also you will need to tac the result to get the desired output. – steeldriver Feb 24 '16 at 18:38
  • @steeldriver Thank you for noticing that, I will edit the question! Unfortunately I still get the duplicates though. – knovice Feb 24 '16 at 19:09
  • 1
    you need to drop the -r from the sort – iruvar Feb 24 '16 at 21:01
3

With sed:

sed '$!N;/\(.*,\).*\n\1/!P;D' infile

N means there are always two consecutive lines in the pattern space and sed Prints the first one of them only if the first field in that line is not the same as the first field in the second line. Then D removes the first line from the pattern space and restarts the cycle.


Another way with gnu datamash (assuming your file is sorted as datamash requires sorted input):

datamash -t ',' -g 1 last 2 <infile

This groups the , delimited input by 1st field, printing only the last value (from 2nd column) of each group.


If your file isn't sorted datamash can sort it via -s:

datamash -t ',' -s -g 1 last 2 <infile

but that means the initial order of lines will not be preserved. So this might not do what you want. In that case you could use sed/awk/perl etc...

  • 2
    Thank you very much @don_crissti - I like your sed solution, so I will accept that as an answer (concise and does the job) – knovice Feb 24 '16 at 20:02
2

And an alternative awk:

 awk -F, 'NR==1{old=$0;check=$1}NR>1 && $1 != check {print old}{old=$0;check=$1}END{print old}' knovice
1500,1533
1554,1728
1593,1766
  • Any reason you chose $1 !~ check instead of $1 != check ? – glenn jackman Feb 24 '16 at 21:21
  • Nope ... call that a mistake =} – tink Feb 24 '16 at 21:25
1

Here's another awk approach (thanks @Glenn):

 tac file | awk -F, 'awk -F, '!seen[$1]++' | tac

The -F, sets the delimiter. In awk, the default action when an expression evaluates to true is to print the current line. !seen[$1] will be true when the first field doesn't exist in the array seen. However, since we are also creating it with seen[$1]++, that will only be false the 1st time it is seen. The result is that only the first of the duplicates will be printed.

Since the script above will keep the first and not the last of each run of duplicates, the two tac calls are an ugly hack to reverse the order and make it keep the last. Since there are two, the final order will be unchanged.

  • 1
    But that prints the first of the lines with the repeats, not the last ... – tink Feb 24 '16 at 19:34
  • @tink ah, I was wondering why you'd made yours so complex. Now I see :) Thanks, I fixed it with a couple of tacs but yours is better now that I understand the question. – terdon Feb 24 '16 at 19:38
  • 1
    tac file | awk -F, '!seen[$1]++' | tac is what I would have written – glenn jackman Feb 24 '16 at 21:20
  • @glennjackman d'oh, of course! Much nicer, thanks. I'm on mobile now but I'll add that in when I get the chance. – terdon Feb 24 '16 at 21:35

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.