8

im trying to find duplicate ids from a large csv file, there is just on record per line but the condition to find a duplicate will be the first column. <id>,<value>,<date>

example.csv

11111111,high,6/3/2019
22222222,high,6/3/2019
33333333,high,6/3/2019
11111111,low,5/3/2019
11111111,medium,7/3/2019

Desired output:

11111111,high,6/3/2019
11111111,low,5/3/2019
11111111,medium,7/3/2019

No order is required for the output.

0

4 Answers 4

6

Using AWK:

awk -F, 'data[$1] && !output[$1] { print data[$1]; output[$1] = 1 }; output[$1]; { data[$1] = $0 }'

This looks at every line, and behaves as follows:

  • if we’ve seen the value in the first column already, note that we should output any line matching that, and output the memorised line;
  • output the current line if its first column matches one we want to output;
  • store the current line keyed on the first column.
0
5

If all of your IDs ae the same length (8 characters in your example), you can do the whole thing using sort and GNU uniq:

$ sort file | uniq -Dw 8
11111111,high,6/3/2019
11111111,low,5/3/2019
11111111,medium,7/3/2019

If they aren't the same length, you can still use this approach but it gets a bit more complicated:

$ tr ',' ' ' < file | sort  | rev | uniq -f2 -D | rev | tr ' ' ','
11111111,high,6/3/2019
11111111,low,5/3/2019
11111111,medium,7/3/2019
0
0
awk -F, '$1 in m { print m[$1]$0; m[$1]=""; next } 
                 { m[$1]=$0 "\n" }' ex
0

This can be done using GNU sed by making use of it's extended regex constructs. We first load the file in pattern space and then remove any nonrepeating line(s) from the beginning of pattern space. Also, a flag, \n\n, is placed at the end of pattern space, wherein we throw over the repeating lines. So once this flag bubbles up to the beginning of the pattern space => the operation is over and we may now go ahead and remove the markers from the pattern space and print to stdout.

$ sed -Ee '
   $!{
      N;s/^/\n/
      $s/$/\n\n/;D
   }
   /^([^,\n]*),[^\n]*\n(.*\n)?\1,/!D
   s/^([^\n]*)(.*)/\2\1\n/;/^\n\n/!D
   s/^\n\n//;s/\n$//
' inp

This is a POSIX-sed version AND another way of approaching the problem where we do not maintain the whole file in at any point in time in either the pattern or hold spaces. As soon as a duplicate line is seen, then it is printed to stdout AND the reference line is marked and printed, marked becoz we don't want to print it the next time it's duplicate is seen.

$ sed -ne '
   H;g;y/\n_/_\n/
   /.*_\([^,_]*\)\(,[^_]*\)\[0]_\(.*_\)\{0,1\}\1,[^_]*$/{
      s//\1\2/;y/_\n/\n_/;p
      g;s/.*\n//p;g;y/\n_/_\n/
      s/\(.*_\([^,_]*\),[^_]*\)\[0]\(_\(.*_\)\{0,1\}\)\2,[^_]*$/\1[1]\3/
      s/_$//;y/_\n/\n_/;bh
   }
   /.*_\([^,_]*\)\(,[^_]*\)\[1]_\(.*_\)\{0,1\}\1,[^_]*$/{
      s/.*_//;y/_\n/\n_/;p
      g;s/\(.*\)\n.*/\1/;bh
   }
   y/_\n/\n_/;s/$/[0]/;:h;h
' inp

This is a Perl based solution to the problem where we maintain the lines in a hash of array. As soon as we see a repeating line, we print the array and also empty it, and also print the duplicated line.

$ perl -F, -lane '
   push(@{$h{$F[0]}},$_),next if ! exists $h{$F[0]};
   print for splice(@{$h{$F[0]}}),$_;
' inp

Output:

11111111,high,6/3/2019
11111111,low,5/3/2019
11111111,medium,7/3/2019

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .