2

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
  • I recommend asking this question on stackoverflow.com as this site is askubuntu – NerdOfCode Mar 19 '18 at 20:56
  • 2
    @NerdOfCode: Realtively simple text processing questions are typically on topic here. – David Foerster Mar 19 '18 at 20:57
  • 1
    @NerdOfCode: … and text processing is one of the common tasks performed with Ubuntu. There's even a text-processing tag. – David Foerster Mar 19 '18 at 20:59
  • 1
    @NerdOfCode: That questions must be unique to to Ubuntu was never a requirement on Ask Ubuntu. Please look at the abundance of various text-processing questions that the reviewers accepted in the past. There's also a related Meta question. – David Foerster Mar 19 '18 at 21:04
  • 2
    If you'd sorted them in decreasing order of the 4th field, you could simply have taken the first row of each 2nd field value e.g. awk -F, '!seen[$2]++' file – steeldriver Mar 19 '18 at 21:33
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.1

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

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

  • waltinator's approach does NOT keep the whole dataset in memory, handles records not sorted by Cell_Id, and doesn't get confused by the 1st header. – waltinator Mar 19 '18 at 22:22
  • This worked! Thankyou so much. I opted for this answer as I know a little bit of python and this approach made sense to me and worked like magic! Another reason for opting this answer was I don't have a memory restrictions as I am working on a cluster :) – Sami Mar 20 '18 at 0:24
1

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
-1

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
  • Not bad but unfortunately requires O(n) in-memory storage even for data sets pre-sorted by the 2nd column. It would be better to discard info on past keys that are no longer required. – David Foerster Mar 19 '18 at 22:33
  • Oh and more importantly it doesn't return the record with the largest value in column 4 for each group of identical values in column 2. – David Foerster Mar 19 '18 at 22:36
  • I have more than a million Cell_ID's. So, I will go with other answer. Thanks a lot – Sami Mar 20 '18 at 0:25

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.