1

I have a pipe-delimited file which can have millions of records. What I want to do is sort the file and remove if there are any duplicate lines there.

Then I need to check the first 32 columns and see if the content from first line matches with second line. If yes, then delete the second line and again check the first line with third line(which now will become second line due to removal of previous line since it was same). if this is also same then remove the third line as well and continue to compare first line with subsequent lines from file until we get a mismatch. For example: Input File:

a|a1|a2|a3|a4|...|a32|acb@sma.com
a|a1|a2|a3|a4|...|a32|acd@sm.com$1553:2015-02-14 
a|a1|a2|a3|a4|...|a32|axwer@xi.com30:2015-03-01 
a|a1|a2|a3|a4|...|a32|acbw@ma.com$121:2015-01-31 
a|a1|a2|a3|a4|...|a32|art@ma.com$293:2015-02-28 
b|b1|b2|b3|b4|...|b32|asmi@g.in$542:2013:05:24 
b|b1|b2|b3|b4|...|b32|kasmi@g.in$542:2013:05:24 
b|b1|b2|b3|b4|...|b32|asmi@g.in14:2013:05:24
b|b1|b2|b3|b4|...|b32|asmi@g.in$542:2013:05:24 
b|b1|b2|b3|b4|...|b32|asmi@g.in232:2014:05:24 
c|c1|c2|c3|c4|...|c32|Asce@ita.in 
c|c1|c2|c3|c4|...|c32|$200:2011:12:06 
c|c1|c2|c3|c4|...|c32|kst@gre.in$214:2001:01:31 

There are 33 fields in above example and "..." is used to represent the continuity. So here as you see, first 4 lines are same (comparing only till field number 32 which is shown in example having a value of "a32"). So I need to keep just the 1st one and delete others. Similarly for columns starting with b, we have 5 records which are the same, so again delete the last 4 and keep the first one. For records starting with c, we just have 3 records so we need to delete last 2 and keep 1st one. Hence the output file would be some thing like this:

Required Output file:

a|a1|a2|a3|a4|...|a32|acb@sma.com
b|b1|b2|b3|b4|...|b32|asmi@g.in$542:2013:05:24 
c|c1|c2|c3|c4|...|c32|Asce@ita.in 

I was thinking of using a regular loop and sed command to get this done but it slows the process significantly. So, is there a way where we can use ack and make this search/manipulation faster?

  • @Mohsen Thans for formatting. – mdx May 8 '15 at 4:17
  • 1
    There is community and everone help to community, So no needed to thanks. – PersianGulf May 8 '15 at 4:19
  • Are there always exactly 33 fields, or can there be more? I'm asking because it's easier to split and compare lines if we only have to consider two fields per line: everything before the rightmost pipe and everything after it. – Mark Plotnick May 8 '15 at 6:46
3

Sort on keys 1 to 32 (-k1,32), with vertical bar as delimiter -t'|'.
-u means: output only the first line of an equal run (of combined keys).
See man sort for details of sort's options.

sort -t'|' -k1,32 -u infile

The above code, of course, results in a sorted output.
However, if you want to maintain the same sequence of first-found lines as per the input file, then use:

nl -s'|' -ba infile | sort -t'|' -k2,5 -u | sort -t'|' -k1 -n | cut -d'|' -f2-

nl prefixes sequential line numbers. These line numbers are cut after the sort steps.
Note that the output of this second method is the same as terdon's Perl solution.

| improve this answer | |
  • nl -s'|' -ba infile | sort -t'|' -k2,33 -u | sort -n | cut -d'|' -f2- cool +1 – JJoao May 9 '15 at 10:20
1

Peter's sort approach is almost certainly going to be the most efficient, I suggest you use it. For the sake of diversity, here's a Perl solution:

perl -F"\|" -lane  'push @G,$_ unless ++$k{join("\|",@F[0..31])}>1;
                    END{print join "\n",@G}' file 
| 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.