1

I have a problem in selecting rows that have the same value. My data is too huge to go row by row to do that. I want you guys to let me know scripts that can perform this.

My data looks like as below:

file name: temp

Start day   hour    end day        hour Value
01/04/2000  22:00   01/05/2000  09:00   -9
01/05/2000  09:00   01/06/2000  09:00   -9
01/06/2000  09:00   01/07/2000  09:00   -9
01/07/2000  09:00   01/08/2000  09:00   -9
01/08/2000  09:00   01/09/2000  09:00   -9
01/09/2000  09:00   01/10/2000  09:00   -9
01/10/2000  09:00   01/11/2000  09:00   -9
01/11/2000  09:00   01/11/2000  21:30   -9
01/11/2000  22:30   01/12/2000  09:00   -9
01/12/2000  09:00   01/13/2000  09:00   -9
01/15/2000  09:00   01/16/2000  09:00   -9
01/16/2000  09:00   01/17/2000  09:00   -9
01/17/2000  09:00   01/18/2000  09:00   -9
01/18/2000  09:00   01/18/2000  22:45   -9
01/18/2000  22:50   01/19/2000  09:00   0.15
01/19/2000  09:00   01/20/2000  09:00   -9
01/20/2000  09:00   01/21/2000  09:00   -9
01/21/2000  09:00   01/22/2000  09:00   -9
01/22/2000  09:00   01/23/2000  09:00   -9
01/23/2000  09:00   01/24/2000  09:00   -9
01/24/2000  09:00   01/25/2000  09:00   -9
01/25/2000  09:00   01/26/2000  00:35   -9
01/26/2000  00:35   01/26/2000  09:00   -9
01/26/2000  09:00   01/27/2000  09:00   -9

Above 01/18/2000 for instance appears twice as the "Start day", and twice as the "end day". Therefore, I want to include the lines that have 01/18/2000 as the "Start day" or "end day".

I want my output from the above data be:

Start day   hour    end day        hour Value
01/10/2000  09:00   01/11/2000  09:00   -9
01/11/2000  09:00   01/11/2000  21:30   -9
01/11/2000  22:30   01/12/2000  09:00   -9
01/17/2000  09:00   01/18/2000  09:00   -9
01/18/2000  09:00   01/18/2000  22:45   -9
01/18/2000  22:50   01/19/2000  09:00   0.15
01/25/2000  09:00   01/26/2000  00:35   -9
01/26/2000  00:35   01/26/2000  09:00   -9
01/26/2000  09:00   01/27/2000  09:00   -9

closed as unclear what you're asking by Gilles, slm, msw, Anthon, manatwork Aug 24 '13 at 11:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Your example output doesn't always have the same start and end dates. – jordanm Aug 23 '13 at 23:31
  • @jordanm, I want to select row that have the same start date and end date. start and end hours doesn't matter. what I want the script to do is looking my start date column and select row that have the same value and the looking end day column and select rows that have the same value. Thanks – AiB Aug 23 '13 at 23:44
  • It sounds like you want all rows. – jordanm Aug 24 '13 at 0:51
  • As jordanm has pointed out rows 4 and 6 in your sample output do not have the same end date as start date (is that a mistake?) – Drav Sloan Aug 24 '13 at 1:25
  • I think he wants the rows where the date spans from the prior rows end date, till the next row with the same date as that end date. Atleast that's how I would interpret the example output. – slm Aug 24 '13 at 1:28
1

If I understand correctly, you want lines whose start or end dates are duplicated. Then maybe something like:

awk 'NR==FNR{s[$1]++;e[$3]++;next}
     FNR == 1 || s[$1]>1 || e[$3]>1' temp temp

That is make two passes in the file. In the first pass, count the number of occurrences of the start and end dates, and in the second pass, output the lines where the number of occurrences of the start or end date is greater than 1.

  • @terddon and Stephane, Your script is work and give me the output on the command line. Could you please let me know how i could get the output in a text file? Thank you – AiB Aug 27 '13 at 3:17
  • @Abraham, redirect the output, by appending > result.txt to that command. – Stéphane Chazelas Aug 27 '13 at 5:13
0

If it simply is just lines with the same start and end date (and no reference to the previous line):

perl -ne 'print if(m!^(\d{2}/\d{2}/\d{4})\s+\d{2}:\d{2}\s+\1!);' < file

^ begining of line

(\d{2}/\d{2}/\d{4}) match date and store (so we can reference it with \1)

\s+\d{2}:\d{2}\s+ 1 or more spaces 2 digits a colon 2 digits and then 1 or more spaces

\1 "backreference" the stored date

If that matches, print the line.

0

I've put together a Perl script which hopefully does what you're looking for. It assumes the data you provided in your example is in a file named temp.

#!/usr/bin/perl

### ./timetract.pl

## 01/10/2000  09:00   01/11/2000  09:00   -9
## 01/11/2000  09:00   01/11/2000  21:30   -9
## 01/11/2000  22:30   01/12/2000  09:00   -9
## ...
## 01/17/2000  09:00   01/18/2000  09:00   -9
## 01/18/2000  09:00   01/18/2000  22:45   -9
## 01/18/2000  22:50   01/19/2000  09:00   0.15
#  ...
## 01/25/2000  09:00   01/26/2000  00:35   -9
## 01/26/2000  00:35   01/26/2000  09:00   -9
## 01/26/2000  09:00   01/27/2000  09:00   -9
## 01/27/2000  09:00   01/28/2000  09:00   -9

use strict;
use warnings;
use feature qw( say );

open (my $fh, "<", "temp") || die "Can't open temp: $!";

my ($prevEndDate, @middleRow, $s1, $s2, $mRow) = "";

for my $cRow (<$fh>) {
  chomp($cRow);

  my @currentRow = split(/\s+/, $cRow);
  next if $currentRow[0] =~ /Start/;  # skip first row

  ## col1        col2    col3        col4    col5
  ## ----        ----    ----        ----    ----
  ## 01/27/2000  09:00   01/28/2000  09:00   -9

  # identify that we're on the last row of a block that
  # we're interested in, print it, reset & go to the next row
  if ($currentRow[0] eq $prevEndDate && $s2) {
    say $cRow;
    $s1 = $s2 = 0; # reset states, get ready for next block
    next;
  }

  # identify that we're in the middle of a block that
  # we're interested in, so save current row as a middle row
  if ($currentRow[0] ne $currentRow[2]) {
    $prevEndDate = $currentRow[2];  
    @middleRow   = @currentRow;
    $mRow        = $cRow;
    next;
  }

  # identified beginning row of a block of rows that we're interested in
  $s1 = 1 if ($prevEndDate eq $currentRow[0]);
  # identified middle row of a block of rows that we're interested in
  $s2 = 1 if ($s1 == 1 && $currentRow[0] eq $currentRow[2]);

  say $mRow;
  say $cRow;
}

close ($fh);

# vim: set ts=2 nolist :

When you run it you'll see the following output:

$ ./timeextract.pl 
01/10/2000  09:00   01/11/2000  09:00   -9
01/11/2000  09:00   01/11/2000  21:30   -9
01/11/2000  22:30   01/12/2000  09:00   -9
01/17/2000  09:00   01/18/2000  09:00   -9
01/18/2000  09:00   01/18/2000  22:45   -9
01/18/2000  22:50   01/19/2000  09:00   0.15
01/25/2000  09:00   01/26/2000  00:35   -9
01/26/2000  00:35   01/26/2000  09:00   -9
01/26/2000  09:00   01/27/2000  09:00   -9

Not the answer you're looking for? Browse other questions tagged or ask your own question.