I am searching for a command to get from a file in this format:

hello 32
hello 67
hi    2
ho    1212
ho    1390
ho    3000

To this format (deduplicate by taking the last row of a "group"):

hello 67
hi    2
ho    3000

At the moment I am using a Python and pandas snippet:

    df = pd.read_csv(self.input().path, sep='\t', names=('id', 'val'))

    # how to replace this logic with shell commands?
    surface = df.drop_duplicates(cols=('id'), take_last=True)

    with self.output().open('w') as output:
        surface.to_csv(output, sep='\t', cols=('id', 'val'))

Update: Thanks for the great answers. Here are some benchmarks:

Input file is 246M and contains 8583313 lines. Order does not matter. First column has a fixed size of 9 chars.

Example of the input file:

000000027       20131017023259.0        00
000000027       20131017023259.0        11
000000035       20130827104320.0        01
000000035       20130827104320.0        04
000000043       20120127083412.0        01

                              time        space complexity

tac .. | sort -k1,1 -u        27.43682s   O(log(n))
Python/Pandas                 11.76063s   O(n)
awk '{c[$1]=$0;} END{for(...  11.72060s   O(n)

Since the first column has a fixed length, uniq -w can also be used:

tac {input} | uniq -w 9        3.25484s   O(1)
  • How tac {input} | uniq -w 9 work? – cuonglm Jun 19 '14 at 17:32
  • @Gnouc, -w N will only consider the first N chars. For my local file I had a 9 char ID in the first column, so uniq -w 9. – miku Jun 19 '14 at 17:34
  • I tested with your input and the result is not correct. – cuonglm Jun 19 '14 at 17:35
  • @Gnouc, Yes for the small input, uniq -w 5should work. I think your and Mikels answers are better, since they do not make an assumption about the number of chars in the first column. However, if the input follows such a constraint, then uniq -w is the fastest. – miku Jun 19 '14 at 17:36

This seems crazy, and hopefully there's a better way, but:

tac foo | sort -k 1,1 -u

tac is used to reverse the file, so you get the last rather than the first.

-k 1,1 says use only the first field for comparison.

-u makes it unique.

|improve this answer|||||
  • ah lol, tac was the solution to my answer haha – polym Jun 19 '14 at 16:16
  • +1, this is the most elegant command line solution that I have seen so far. :) – Ramesh Jun 19 '14 at 16:36
  • This is really good, and it was the first thing I thought of, but couldn't you just sort -ruk1,1 foo? Maybe I'm not reading it right, though. – mikeserv Jun 19 '14 at 17:29
  • Thanks, I like that. It's actually a bit slower than Python/Pandas, but clear and concise. And the memory overhead is much better than the Python and other hash-based solutions. – miku Jun 19 '14 at 17:50

If you don't mind the order of output, here's an awk solution:

$ awk '
    {a[$1] = !a[$1] ? $2 : a[$1] < $2 ? $2 : a[$1]}
    END {
        for (i in a) { print i,a[i] }
' file
hi 2
hello 67
ho 3000
|improve this answer|||||

Some more options:

  1. perl, if you don't care about the order of the lines.

    perl -lane '$k{$F[0]}=$F[1]; END{print "$_ $k{$_}" for keys(%k)}' file
  2. A simpler awk

    awk '{c[$1]=$0;} END{for(i in c){print c[i]}}' file
  3. A silly shell one

    while read a b; do grep -w ^"$a" file | tail -n1 ; done < file | uniq
|improve this answer|||||

Well you can do it with sort

sort -u -k1,1 test

EDIT: tac is the solution

|improve this answer|||||

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.