Removing duplicate rows based on column value

Question

I have a text file of size approx. 25 GB. I want to delete the duplicate rows based on the value in second column. If duplicates are found in a file then, I want to delete all rows with that value in column and keep only one row with highest value in the fourth column. The file is in CSV format and is already sorted.

storm_id,Cell_id,Windspeed,Storm_Surge,-1
2,10482422,45,0.06,-1
2,10482422,45,0.18,-1
2,10482422,45,0.4,-1
2,10482423,45,0.15,-1
2,10482423,45,0.43,-1
2,10482424,45,0.18,-1
2,10482424,45,0.49,-1
2,10482425,45,0.21,-1
2,10482425,45,0.52,-1
2,10482426,45,0.27,-1
2,10482426,45,0.64,-1
2,10482427,45,0.09,-1
2,10482427,45,0.34,-1
2,10482427,45,0.73,-1

In the above example, I just want one maximum surge value for each Cell_Id by deleting other duplicate rows

Expected output is:

2,10482422,45,0.4,-1
2,10482423,45,0.43,-1
2,10482424,45,0.49,-1
2,10482425,45,0.52,-1
2,10482426,45,0.64,-1
2,10482427,45,0.73,-1

Answer 1

Since the input appears to be grouped/sorted by the 2nd column already this should be quite simple and doesn’t require to keep and sort the entire data set in memory, only two records at a time.¹

I first thought of an Awk solution but found it to clumsy to deal with arrays and non-blank field delimiters. Then I decided on a short-ish Python program:

#!/usr/bin/python3
import sys
DELIMITER = ','

def remove_duplicates(records):
    prev = None
    for r in records:
        r = (int(r[0]), int(r[1]), int(r[2]), float(r[3]), int(r[4]))
        if prev is None:
            prev = r
        elif r[1] != prev[1]:
            yield prev
            prev = r
        elif r[3] > prev[3]:
            prev = r
    if prev is not None:
        yield prev

def main():
    for r in remove_duplicates(
        l.rstrip('\n').rsplit(DELIMITER) for l in sys.stdin
    ):
        print(*r, sep=',')

if __name__ == '__main__':
    main()

On my system it has a throughput of ~250,000 records or 5 MB per CPU second.

Usage

python3 remove-duplicates.py < input.txt > output.txt

The program can’t deal with column headers, so you need to strip them off:

tail -n +2 < input.txt | python3 remove-duplicates.py > output.txt

If you want to add them back to the result:

{ read -r header && printf '%s\n' "$header" && python3 remove-duplicates.py; } < input.txt > output.txt

---

¹ This is one major advantage over waltinator’s and steeldriver’s approaches for data sets that don’t fit into main memory.

Answer 2

If you'd sorted them in decreasing order of the 4th field, you could simply have taken the first occurrence of each 2nd field value using an associative array or hash e.g. awk -F, '!seen[$2]++' file or perl -F, -ne 'print $_ unless $seen{$F[1]}++'

With the values in increasing order, it's a little trickier to do it in an efficient single pass - you can do so (with a little bit of setup) by printing the previous line each time the key value changes:

awk -F, '
  NR==1 {print; next}        # print the header line
  NR==2 {key=$2; next}       # initialize the comparison
  $2 != key {
    print lastval; key = $2  # print the last (largest) value of the previous key group
  } 
  {lastval = $0}             # save the current line
  END {print lastval}        # clean up
' file
storm_id,Cell_id,Windspeed,Storm_Surge,-1
2,10482422,45,0.4,-1
2,10482423,45,0.43,-1
2,10482424,45,0.49,-1
2,10482425,45,0.52,-1
2,10482426,45,0.64,-1
2,10482427,45,0.73,-1

Answer 3

If you don't have too many unique Cell_ids, you could keep track of the already-seen ones in a Perl associative array. If you do have too many (and my Perl script runs out of memory), write a C program to keep unique ones in a bit field. Here's the Perl.

#!/usr/bin/perl -w
use strict;
my %seen = ();          # key=Cell_ID, value=1
my @cols=();            # for splitting input

while( <> ) {           # read STDIN
  @cols = split ',',$_;
  next if ( defined $seen{$cols[1]}); # skip if we already saw this Cell_Id
  $seen{$cols[1]} = 1;
  print;
}

Here's my test:

walt@bat:~(0)$ cat u.dat
storm_id,Cell_id,Windspeed,Storm_Surge,-1
2,10482422,45,0.06,-1
2,10482422,45,0.18,-1
2,10482422,45,0.4,-1
2,10482423,45,0.15,-1
2,10482423,45,0.43,-1
2,10482424,45,0.18,-1
2,10482424,45,0.49,-1
2,10482425,45,0.21,-1
2,10482425,45,0.52,-1
2,10482426,45,0.27,-1
2,10482426,45,0.64,-1
2,10482427,45,0.09,-1
2,10482427,45,0.34,-1
2,10482427,45,0.73,-1
walt@bat:~(0)$ perl ./unique.pl u.dat
storm_id,Cell_id,Windspeed,Storm_Surge,-1
2,10482422,45,0.06,-1
2,10482423,45,0.15,-1
2,10482424,45,0.18,-1
2,10482425,45,0.21,-1
2,10482426,45,0.27,-1
2,10482427,45,0.09,-1