Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

How to get remove duplicate of a file based on many conditions

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers How to get remove duplicate of a file based on many conditions
# 1  
Old 02-04-2010
Question How to get remove duplicate of a file based on many conditions
Hii Friends.. I have a huge set of data stored in a file.Which is as shown below
a.dat:
HTML Code:
 RAO   1869 12 19  0  0  0.00  17.9000  82.3000  10.0   0  0.00   0  3.70  0.00  0.00   0  0.00  3.70   4   NULL
 LEE   1870  4 11  1  0  0.00  30.0000  99.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  6.75  6.75   9   NULL
 SIG   1870  4 11  1  0  0.00  30.0000  99.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  0.00  6.75   9   NULL
 SIG   1870  4 11  1  0  0.00  30.0000  99.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  6.70  6.70   0   NULL
 RAO   1870 10 19  0  0  0.00  17.7000  83.4000  10.0   0  0.00   0  3.70  0.00  0.00   0  0.00  3.70   4   NULL
 SSR   1896  3  4  5  5  0.00  37.0000  76.0000  40.0   0  0.00   0  7.10  0.00  0.00   0  0.00  7.10   8   NULL
 SSR   1896  6 17 12  0  0.00  37.0000  68.0000  15.0   0  0.00   0  5.20  0.00  0.00   0  0.00  5.20   7   2.23e+23
 SIG   1899  9 23 23 24  0.00  37.0000  71.0000 160.0   0  0.00   0  0.00  0.00  0.00   0  7.50  7.50   6   NULL
 SSR   1899  9 23 23 20  0.00  37.0000  71.0000 160.0   0  0.00   0  7.50  0.00  0.00   0  0.00  7.50   6   NULL
 SIG   1902  8 30 21 50  0.00  37.0000  71.0000 200.0   0  0.00   0  0.00  0.00  0.00   0  7.70  7.70   7   NULL
 SSR   1902  8 30 21 50  0.00  37.0000  71.0000 200.0   0  0.00   0  7.70  0.00  6.90   0  0.00  7.70   7   NULL
 BDA   1905  4  4  2 50  0.00  33.0000  76.0000  60.0   0  0.00   0  5.00  8.00  0.00   0  8.60  8.60   0   NULL
 G-R   1905  4  4  0 50  0.00  33.0000  76.0000  25.0   0  0.00   0  5.00  8.00  0.00   0  8.60  8.60   0   1.23e+11
 SIG   1905  4  4  2 50  0.00  33.0000  76.0000  25.0   0  0.00   0  0.00  0.00  0.00   0  8.60  8.60   0   NULL
 SIG   1950  8 15  0  0  0.00  28.5000  96.7000   0.0   0  0.00   0  0.00  0.00  0.00   0  8.60  8.60   0   NULL
 BDA   1950  8 15 14  9 30.00  28.5000  96.5000  60.0   0  0.00   0  0.00  0.00  0.00   0  8.70  8.70   0   NULL
 G-R   1913  3  6  2  9  0.00  30.0000  83.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  6.20  6.20   0   NULL
Output for this file should be like b.dat:
HTML Code:
 RAO   1869 12 19  0  0  0.00  17.9000  82.3000  10.0   0  0.00   0  3.70  0.00  0.00   0  0.00  3.70   4   NULL
 LEE   1870  4 11  1  0  0.00  30.0000  99.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  6.75  6.75   9   NULL
 RAO   1870 10 19  0  0  0.00  17.7000  83.4000  10.0   0  0.00   0  3.70  0.00  0.00   0  0.00  3.70   4   NULL
 SSR   1896  3  4  5  5  0.00  37.0000  76.0000  40.0   0  0.00   0  7.10  0.00  0.00   0  0.00  7.10   8   NULL
 SSR   1896  6 17 12  0  0.00  37.0000  68.0000  15.0   0  0.00   0  5.20  0.00  0.00   0  0.00  5.20   7   NULL
 SIG   1899  9 23 23 24  0.00  37.0000  71.0000 160.0   0  0.00   0  0.00  0.00  0.00   0  7.50  7.50   6   NULL
 SSR   1902  8 30 21 50  0.00  37.0000  71.0000 200.0   0  0.00   0  7.70  0.00  6.90   0  0.00  7.70   7   NULL
 BDA   1905  4  4  2 50  0.00  33.0000  76.0000  60.0   0  0.00   0  5.00  8.00  0.00   0  8.60  8.60   0   NULL
 BDA   1950  8 15 14  9 30.00  28.5000  96.5000  60.0   0  0.00   0  0.00  0.00  0.00   0  8.70  8.70   0   NULL
 G-R   1913  3  6  2  9  0.00  30.0000  83.0000   0.0   0  0.00   0  0.00  0.00  0.00   0  6.20  6.20   0   NULL
Now in this file i have to remove duplicates lines based these conditions.
We check for column 19
1)if its value is between 0 to 7.00.Then
we check for 2,3,4,5,6 columns if they are same if so then remove one of the duplicate rows & retain the row which has its largest value in column 19 & Which has large set of columns with values in that row. and
2)If its value is between 7.00 to 8.00. Then
we check for 2,3,4,5 columns if they are same if so then remove one of the duplicate rows & retain the row which has its largest value in column 19 & Which has large set of columns with values in that row. and
3)if its value is between 8.00 to 9.00. Then
we check for 2,3,4 columns if they are same if so then remove one of the duplicate rows & retain the row which has its largest value in column 19 & Which has large set of columns with values in that row.Smilie
Help me out Smilie
# 2  
Old 02-04-2010
CPU & Memory
write a perl script like
Code:
#!/usr/bin/perl -w

use strict;

open (IN, "<data.in") || die "Cannot open data.in: $!\n";
my @lines = <IN>;
close (IN);

my @old; # original data items
my @new; # filtered data items

OUTER_LOOP:
foreach my ( $line )
{
  my @item = split (/\s+/, $line);

  # apply your conditions
  if ( item[18] > 0.00 && 
       item[18] < 7.00 )
  {
     # check if we have that item already
     # note that $have is an array reference
     INNER_LOOP: 
     foreach my $have ( @new )
     {
        if ( $item[1] == $have->[1] && 
             $item[2] == $have->[2] && 
              $item[3] == $have->[3] && 
              $item[4] == $have->[4] && 
              $item[5] == $have->[5] )
        {
           # found it.  So, replace new array entry 
           # with the one with larger #19
           if ( $item[18] > $have->[18] )
           {
               # foreach passes array values by reference, so we can 
               # simply swap the item
               $have = \@item;
            } 
          }
       
         # we should not find that item again, right?  So, 
         # finish that inner foreach here
         last INNER_LOOP; 
      }
   }
   # add similar tests for the other conditions below
   else if ( ... )
   {
   }  
} # all lines

open (OUT, ">data.out") || die "Cannot open data.out: $!\n";
foreach my $out ( @new )
{
  # print data tab separated
  print OUT join ("\t", @{$out});
  print OUT "\n";
}

The above is not tested and likely has typos, and intendation is hard to do in a form (sorry) - but I hope you get the idea. If you intent to go that route, let me know if you have trouble with the code etc.

Best, Andre
# 3  
Old 02-04-2010
Question
No i am not getting exact output..I dont know much perl..Can you tell me the same using awk .Smilie
# 4  
Old 02-04-2010
Error
After completing the script, I still can't reproduce your output: those seems wrong. Fir example, I can't find the line ending not in NULL, but in 2.23e+23 -- that value is simply gone, but according to your filter algrithm, that line should remain I think.

Otherwise the attached script should now be able to reproduce the data you show. It reads a data.in file, and writes a data.out.
data_filter.pl (4.4 KB) 
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Remove sections based on duplicate first line

Hi, I have a file with many sections in it. Each section is separated by a blank line. The first line of each section would determine if the section is duplicate or not. if the section is duplicate then remove the entire section from the file. below is the example of input and output.... (5 Replies)
Discussion started by: ahmedwaseem2000
5 Replies

2. Shell Programming and Scripting

Remove duplicate lines from file based on fields

Dear community, I have to remove duplicate lines from a file contains a very big ammount of rows (milions?) based on 1st and 3rd columns The data are like this: Region 23/11/2014 09:11:36 41752 Medio 23/11/2014 03:11:38 4132 Info 23/11/2014 05:11:09 4323... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

3. Shell Programming and Scripting

Remove duplicate rows based on one column

Dear members, I need to filter a file based on the 8th column (that is id), and does not mather the other columns, because I want just one id (1 line of each id) and remove the duplicates lines based on this id (8th column), and does not matter wich duplicate will be removed. example of my file... (3 Replies)
Discussion started by: clarissab
3 Replies

4. Shell Programming and Scripting

Remove duplicate entries based on the range

I have file like this: chr start end chr15 99874874 99875874 chr15 99875173 99876173 aa1 chr15 99874923 99875923 chr15 99875173 99876173 aa1 chr15 99874962 99875962 chr15 99875173 99876173 aa1 chr1 ... (7 Replies)
Discussion started by: raj_k
7 Replies

5. Shell Programming and Scripting

How To Remove Duplicate Based on the Value?

Hi , Some time i got duplicated value in my files , bundle_identifier= B Sometext=ABC bundle_identifier= A bundle_unit=500 Sometext123=ABCD bundle_unit=400 i need to check if there is a duplicated values or not if yes , i need to check if the value is A or B when Bundle_Identified ,... (2 Replies)
Discussion started by: OTNA
2 Replies

6. Shell Programming and Scripting

Remove duplicate value based on two field $4 and $5

Hi All, i have input file like below... CA009156;20091003;M;AWBKCA72;123;;CANADIAN WESTERN BANK;EDMONTON;;2300, 10303, JASPER AVENUE;;T5J 3X6;; CA009156;20091003;M;AWBKCA72;321;;CANADIAN WESTERN BANK;EDMONTON;;2300, 10303, JASPER AVENUE;;T5J 3X6;; CA009156;20091003;M;AWBKCA72;231;;CANADIAN... (2 Replies)
Discussion started by: mohan sharma
2 Replies

7. Shell Programming and Scripting

Remove duplicate based on Group

Hi, How can I remove duplicates from a file based on group on other column? for example: Test1|Test2|Test3|Test4|Test5 Test1|Test6|Test7|Test8|Test5 Test1|Test9|Test10|Test11|Test12 Test1|Test13|Test14|Test15|Test16 Test17|Test18|Test19|Test20|Test21 Test17|Test22|Test23|Test24|Test5 ... (2 Replies)
Discussion started by: yale_work
2 Replies

8. UNIX for Dummies Questions & Answers

remove duplicate lines based on two columns and judging from a third one

hello all, I have an input file with four columns like this with a lot of lines and for example, line 1 and line 5 match because the first 4 characters match and the fourth column matches too. I want to keep the line that has the lowest number in the third column. So I discard line 5.... (5 Replies)
Discussion started by: TheTransporter
5 Replies

9. Shell Programming and Scripting

Remove duplicate files based on text string?

Hi I have been struggling with a script for removing duplicate messages from a shared mailbox. I would like to search for duplicate messages based on the “Message-ID” string within the messages files. I have managed to find the duplicate “Message-ID” strings and (if I would like) delete... (1 Reply)
Discussion started by: spangberg
1 Replies

10. UNIX for Dummies Questions & Answers

Remove duplicate rows of a file based on a value of a column

Hi, I am processing a file and would like to delete duplicate records as indicated by one of its column. e.g. COL1 COL2 COL3 A 1234 1234 B 3k32 2322 C Xk32 TTT A NEW XX22 B 3k32 ... (7 Replies)
Discussion started by: risk_sly
7 Replies
Login or Register to Ask a Question