Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a problem comparing two files. I'll try my best to explain. I have two files f1 and f2 which I have to compare on basis of two sets Parameter(p) and Value(v).

Basically f1 contains a list of Values and Time Stamp for each of the Parameters

  f1.csv
  P,V,TS
  p1,12,10:10:00
  p2,34,10:21:00
  p1,12,10:21:00
  p2,34,10:22:00 
  p1,60,10:35:00
  p3,60,10:36:00
  p4,22,10:38:00
  p4,42,10:40:00    
  p1,60,10:41:00
  p3,58,10:42:00
  p2,55,10:45:00
  p3,58,10:55:00

File f2 contains the previous/latest value and time stamp for each parameter. Every parameter has only one occurrence in this file. For clarity, RTS(Real Time Stamp) & UTS(Updated Time Stamp).

  f2.csv
  P,V,RTS,UTS
  p1,12,10:00:00,10:05:00
  p2,34,10:07:00,10:15:00
  p3,60,10:25:00,10:30:00
  p4,22,10:30:00,10:32:00

Now I will try to explain the output in two parts. The first part is easy: for similar (P,V) in f1 & f2, change the UTS with the latest TS. Here is pseudo code:

  for each (P, V) in f1
  {
  #if value exists in f2
  if ((P, V) exists in f2)
  { 
   f2.RTS(P,V)=f2.RTS(P,V)
   f2.UTS(P, V) = f1.TS(P, V)
  } 
  }

For the value which exists in f2 and f1 first part of the o/p file f3 in []:-

 f3.csv

 [ P,V,RTS,UTS
   p1,12,10:00:00,10:10:00
   p2,34,10:07:00,10:21:00
   p1,12,10:00:00,10:21:00
   p2,34,10:07:00,10:22:00 
   p3,60,10:25:00,10:36:00
   p4,22,10:30:00,10:38:00]

The second part is difficult. It deals only with f1 file: Values which do not exist in f2.

For values from f1 (p4,42,10:40:00)(p2,55,10:45:00) which occur only once and are not in f2 should have RTS=UTS=TS as given in output shown below.

Example:-For a single occurrence

   p4,42,10:40:00,10:40:00

For values (p3,58,10:42:00),(p3,58,10:55:00),(p1,60,10:35:00)(p1,60,10:41:00) which occur twice and are not in f2,the first occurrence should have RTS=UTS=TS and the second occurrence for same (P,V) should have RTS=(TS of first occurrence of (P,V)) and UTS=TS of second occurrence of (P,V).

Example: First occurrence (p1,60) in f1.

 p1,60,10:35:00,10:35:00

Second occurrence (p1,60) in f1

 p1,60,10:35:00,10:41:00

The second part expected output:

  f3.csv
 [ P,V,RTS,UTS
   p1,60,10:35:00,10:35:00
   p4,42,10:40:00,10:40:00
   p1,60,10:35:00,10:41:00
   p3,58,10:42:00,10:42:00
   p2,55,10:45:00,10:45:00
   p3,58,10:42:00,10:55:00]

Final output is appending both the output CSV files.

Thank you any help will be appreciated

share|improve this question
    
Can you explain this any better? Please? – mikeserv Jun 9 '14 at 12:49
    
@mikeserv I am sorry which part did you not understand.I'll try to elaborate. – user71914 Jun 9 '14 at 12:53

I used perl here, as it has more flexible data structures (hash of hashes of arrays).

use strict;
use warnings;
use v5.10;
use autodie;

my (%f1, @order);
open my $fh, "<", "f1.csv";
while (<$fh>) {
    next if $. == 1;
    chomp;
    my ($p, $v, $ts) = split /,/;
    push @{ $f1{$p}{$v} }, $ts;
    push @order, [$p, $v];
}
close $fh;

my %f2;
open $fh, "<", "f2.csv";
while (<$fh>) {
    print if $. == 1;
    chomp;
    my ($p, $v, $rts, $uts) = split /,/;
    $f2{$p}{$v} = [$rts, $uts];
}
close $fh;

for my $key (@order) {
    my ($p, $v) = @$key;
    if (exists $f2{$p}{$v}) {
        my $uts = shift @{$f1{$p}{$v}}
        say join(",", $p, $v, $f2{$p}{$v}[0], $uts);
    }
}

my @remaining;
for my $p (keys %f1) {
    for my $v (keys %{$f1{$p}}) {
        my ($rts, $uts) = @{$f1{$p}{$v}};
        push @remaining, [$p, $v, $rts, $rts] if $rts;
        push @remaining, [$p, $v, $rts, $uts] if $rts and $uts;
    }
}
say for map {join ",", @{$_->[1]}} 
        sort {$a->[0] cmp $b->[0]} 
        map {[$_->[3], $_]} 
        @remaining;

Running it produces:

P,V,RTS,UTS
p1,12,10:00:00,10:10:00
p2,34,10:07:00,10:21:00
p1,12,10:00:00,10:21:00
p2,34,10:07:00,10:22:00
p3,60,10:25:00,10:36:00
p4,22,10:30:00,10:38:00
p1,60,10:35:00,10:35:00
p4,42,10:40:00,10:40:00
p1,60,10:35:00,10:41:00
p3,58,10:42:00,10:42:00
p2,55,10:45:00,10:45:00
p3,58,10:42:00,10:55:00
share|improve this answer
    
i dont know much about perl but i'll try.thank you very much. – user71914 Jun 10 '14 at 5:59
    
give it try. This isn't the easiest perl to read. You can start with perldoc perl but perldoc perlreftut will help you with my example. – glenn jackman Jun 10 '14 at 13:57

Here the code with some help form my friend.Just for some reference.Cheers

  #!/bin/awk -f
  BEGIN{
      FS=","; 
      OFS=",";
       }
      {
             if(NR==FNR)
             {
                a[var]=$1" "$2
                ts[var]=$3
                var++   
              }else if(NR>FNR)
            {
            b[sec]=$1" "$2
            rt[sec]=$3
            ut[sec]=$4
            sec++
            } 
     }
  END{  
  #Code for first part of the o/p
         for(i=0;i<var;i++)
           {
          for(j=0;j<sec;j++)
           {
          if(!(a[i]<b[j]||a[i]>b[j]))
          {   m[a[i]]=1
             print a[i]" "rt[j]" "ts[i]
           }
             }      
           }
  #Code for second part of the o/p
         for(i=0;i<var;i++)
         {
         if(m[a[i]]!=1)
         {
             h[a[i]]++  

            if(h[a[i]]==1)
            {
                rts[a[i]]=ts[i]
                print rts[a[i]]
                print a[i]" "ts[i]" "ts[i]
            }
           else{
                print a[i]" "rts[a[i]]" "ts[i]
                }
          }
          }
      }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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