1

I have 2 files in the exactly the same format and the same data except for 1 column.
Example row of file 1:

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295    

Example row of file 2:

"1/26/2017 8:02:01 PM",Valid customer,jim.smith,NY,1485457321  

Of course the files have other rows but what I am interested in is the following:
I want to remove from file 2 all rows that are about the same entity i.e. $3 and exist in file 1 with a later timestamp and keep the rest.
In the example lines you can see that the row in file 1 is newer as we see from the date string of column 1. Now the last integer in the row is the actual epoch of column 1 so this column can be used for e.g. comparing the dates and sorting.
I can accomplish this by scripting e.g in perl something like the following:

#!/usr/bin/perl  

use strict;  
use warnings;  
my $file_a = "file1";
my $file_b = "file2";

open my $file_a_h, $file_a or die "Could not open $file_a";  

sub timestamp_users {  
    my ($fh) = @_;  
    my %recs;   

   while ( my $line =<$fh> ) {    
        my @items = split ",", $line;  
        my $user = $items[3];  
        $recs{$user} = $items[5];    
    }  
    return \%recs;    
}    

my $file_a_recs = timestamp_users($file_a_h);  

close $file_a_h;  

open my $file_b_h, $file_b or die "Could not open $file_b";  

my $file_b_recs = timestamp_users($file_b_h);    
close $file_b_h;  

my $count = 0;
while (my ($user, $last_time) = each %$file_b_recs) {  
    if(exists $file_a_recs->{$user} && $last_time >= $file_a_recs->{$user}) {
        ++$count;  
        `echo $user >> result.txt`;    

    }
}
print "count: $count\n";    

In this case I just output the users and then I would need to do a grep -v on file_b to figure out the rows I need.

But is there a way to do this using command line tools?
This approach seems too complicated for me.

Update:

Example row of file 1:

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295        
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571    
"1/30/2017 4:14:30 AM",New customer,tim.jones,CO,1485746070    

Example row of file 2:

"1/26/2017 8:02:01 PM",Valid customer,jim.smith,NY,1485457321    
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976   
"1/30/2017 11:14:03 AM",New customer,john.doe,CA,1485771243  
"1/30/2017 11:13:53 AM",New customer,bill.smith,CA,1485771233  

Expected output:

"1/30/2017 11:14:03 AM",New customer,john.doe,CA,1485771243    
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976   
"1/30/2017 11:13:53 AM",New customer,bill.smith,CA,1485771233  
  • If you've already done this with file1 so for each ID ($3) there's just one line in file1 then you can simply run awk -F, 'NR==FNR{z[$3]=$5;next}$5>=z[$3]' file1 file2 – don_crissti Feb 4 '17 at 13:59
  • @don_crissti:Yes I have done that pre processing. What does that awk line do? – Jim Feb 4 '17 at 14:02
  • 1
    Well, it's quite straightforward: it saves the value of $5 for each $3 while reading file1 then it reads file2 and it prints the line if the condition $5>=z[$3] is true (that is if the current $5 is ge than the value of $5 from file1 corresponding to the same $3) - iow it removes the lines for which that condition is false. – don_crissti Feb 4 '17 at 14:05
  • @don_crissti:So NR==FNR is a logical condition then {z[$3]=$5;next} is the code block and because we have a second file we can have after the code block another logical condition? – Jim Feb 4 '17 at 20:14
  • 1
    Just to clear things up: you can have as many conditions as you want... It's the next statement that prevents the 2nd condition to be evaluated while processing the 1st file. Basically NR==FNR means as long as File line NumbeR is equal to combined/aggregate input line NumbeR (i.e. this condition is true only for the 1st file) do {something;next} and next skips the commands that follow and reads in the next line, starting from the beginning. So the 2nd condition $5>=z[$3] is evaluated only for the lines in 2nd file and when true the default action is executed (print) – don_crissti Feb 4 '17 at 20:27
1

To get the newest version of each row in both files:

$  cat file1 file2 | sort -t',' -k3,3 -k5,5nr | sort -t',' -u -k3,3 -o newest

This concatenates the files and sorts the records with the fields from field 3 and 5 as the sorting key. This sorts the concatenated file so that the newest record for each person comes first (thanks to the timestamp in the last column). The last sort uses field 3 as the sorting key and does a unique sort based on this field. This will leave only the newest record for each person in the file newest.

Then we create the complement of the lines in newest, i.e. all records in the two files that are older than the newest record for each person:

$ cat file1 file2 | grep -v -F -x -f newest >older

The grep does a fixed string match (-F) on complete lines (-x) and returns all lines not matching (-v) anything in newest. These lines are stored in older.

The last step is to remove any line from file2 that is present in the older file:

$ grep -v -F -x -f older file2 >new-file2
  • I am not sure I understand. Why do k2r for sort? The second field is a random string Valid Customer. Why sort on that? – Jim Feb 4 '17 at 22:32
  • @Jim I've changed it so that it only care about the third and fifth columns. The -k3,3 -k5,5nr will sort all the names (-k3,3) and then for each name reverse numeric sort on the last field (-k5,5nr). I initially thought that all fields except for the first and last one one uniquely identified a person. – Kusalananda Feb 4 '17 at 23:13
  • When we run the first snippet newest will have the line with the latest timestamp per user but we don't know in which file it exists. Then in the second snippet older would contain all the the other rows of the 2 files. But what I am interested in is to keep the rows in file 2 for which either there is no matching user in file 1 or the timestamp if file 1 is older. Somehow I am not sure these 3 commands do this. TBH I understood the first and the second but not all 3 combined as to what they do together – Jim Feb 5 '17 at 12:15
  • @Jim There is precious little data to test with in the problem description. If you could add more sample data together with what you'd expect as output, that would make it possible to come up with a solution. Also add the clarification of the problem from your last comment to the actual text in the question. – Kusalananda Feb 5 '17 at 12:19
  • Please see updated OP – Jim Feb 5 '17 at 17:49

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.