Merging 2 text files when there is a common time stamp column in them

Shell Programming and Scripting
1

Dear Unix experts and users

I have 2 kinds of files like below, of which I need to merge them in the order of time.
File1:

   Date_Time            Context  D1  D2
 04/19/2013_23:48:54.819    ABCD   x     x
 04/19/2013_23:48:55.307    ABCD   x     x
 04/19/2013_23:48:55.823    ABCD   x     x
 04/19/2013_23:48:56.309    ABCD   x     x

Data columns from file1 are indicated with 'x'.

File2:

         Date_Time       Context D3  D4
 04/19/2013_23:48:54.819     ABCD   y      y
 04/19/2013_23:48:55.821     ABCD   y      y
 04/19/2013_23:48:56.799     ABCD   y      y
 04/19/2013_23:48:57.819     ABCD   y      y

Data columns from file2 are indicated with 'y'.

Here except the first entry from Date_Time column in both files all rest of the time stamp entries are different.
My result (or merged) file should be like below.

Required output

            Date_Time          Context       D1    D2    D3    D4
04/19/2013_23:48:54.819    ABCD    x    x    blank    blank
04/19/2013_23:48:54.819    ABCD    blank    blank    y    y
04/19/2013_23:48:55.307    ABCD    x    x    blank    blank
04/19/2013_23:48:55.821    ABCD    blank    blank    y    y
04/19/2013_23:48:55.823    ABCD    x    x    blank    blank
04/19/2013_23:48:56.309    ABCD    x    x    blank    blank
04/19/2013_23:48:56.799    ABCD    blank    blank    y    y
04/19/2013_23:48:57.819    ABCD    blank    blank    y    y

So describe the output it is sorted by time stamp but the sorting should preserve the position of other column data cells and we should include blanks where where the other file data doesn't exist. The context data field(s) should be as it is between and print int he output.
By the way my files are of .csv type.
Please revert to me if my problem description is not clear.

Thanks
Sidda

2

Taking the data as presented and outputting as a csv.

skrynesaver@busybox ~/tmp$ cat tmp.dat tmp_2.dat
Date_Time            Context  D1  D2
04/19/2013_23:48:54.819    ABCD   x     x
04/19/2013_23:48:55.307    ABCD   x     x
04/19/2013_23:48:55.823    ABCD   x     x
04/19/2013_23:48:56.309    ABCD   x     x
Date_Time       Context D3  D4
04/19/2013_23:48:54.819     ABCD   y      y
04/19/2013_23:48:55.821     ABCD   y      y
04/19/2013_23:48:56.799     ABCD   y      y
04/19/2013_23:48:57.819     ABCD   y      y

skrynesaver@busybox ~/tmp$ perl -Mstrict -e ' 
my %record;
open (DAT_1 , "<", $ARGV[0]);
while(<DAT_1>){
chomp;
  if (/^\d/){
    my @record=split/\s+/,$_;
    $record{reorder($record[0])}=join(",",@record," "," ");
  }
}
close(DAT_1);
open(DAT_2,"<",$ARGV[1]);
while(<DAT_2>){
  chomp;
  if(/^\d/){
    my @record=split/\s+/,$_;
    $record{reorder($record[0])}=$record{reorder($record[0])} 
                   ?sublast($record{reorder($record[0])},@record[2,3])
                   :join(",",@record[0,1]," "," ",@record[2,3]);
  }
}
END{
  for my $timestamp (sort keys %record){
    print $record{$timestamp},"\n";
  }
}
sub reorder{
  my $date=shift;
  my @date=split/\/|_/,$date;
  return join("_",@date[2,1,0,3]);
}
sub sublast{
  my ($record,@fields)=@_;
  my @record=split/,/,$record;
  @record[4,5]=@fields;
  return join",",@record
}' tmp.dat tmp_2.dat
04/19/2013_23:48:54.819,ABCD,x,x,y,y
04/19/2013_23:48:55.307,ABCD,x,x, ,
04/19/2013_23:48:55.821,ABCD, , ,y,y
04/19/2013_23:48:55.823,ABCD,x,x, ,
04/19/2013_23:48:56.309,ABCD,x,x, ,
04/19/2013_23:48:56.799,ABCD, , ,y,y
04/19/2013_23:48:57.819,ABCD, , ,y,y
3

Thank you so much Skrynesaver for your quick reply.
But my data has many context columns(10) in each pair of files and also many data columns in both files.
The total pairs of files I have is around 5000.
So any generic script to couple every 2 similar context files will be highly helpful.

Thanks

4

There are no commas in the files. Just double checking. The input and output should have spaces between the fields?

---------- Post updated at 03:07 AM ---------- Previous update was at 03:00 AM ----------

04/19/2013_23:48:55.823    ABCD    x    x    blank    blank

Do you want the literal word "blank" in the output (as you posted), or do you want x,x,,, or maybe x,x,, (or something else). It would help if you would post the sample output exactly the way you want it (if different from the sample you provided). :slight_smile:

5

Try adapting my scriptlet to your data and come back with (suitably anonimised) examples of where you are having difficulty.

6

Hi Hanson,

Find below my input and output formats.

The input files are actually comma separated files and also instead of blank words I need comma(,) s.

File1

Date_Time, Context,D1,D2  
04/19/2013_23:48:54.819,ABCD,x,x  
04/19/2013_23:48:55.307,ABCD,x,x  
04/19/2013_23:48:55.823,ABCD,x,x  
04/19/2013_23:48:56.309,ABCD,x,x

File2

Date_Time,Context,D3,D4  
04/19/2013_23:48:54.819 , ABCD , y, y  
04/19/2013_23:48:55.821,  ABCD, y , y  
04/19/2013_23:48:56.799, ABCD, y , y
 04/19/2013_23:48:57.819, ABCD,y, y

Required Output

Date_Time, Context, D1,D2, D3, D4 
04/19/2013_23:48:54.819 , ABCD ,  x , x, ,
04/19/2013_23:48:54.819 , ABCD ,  , , y , y 
04/19/2013_23:48:55.307 , ABCD ,  x , x , , 
04/19/2013_23:48:55.821 , ABCD ,  ,  ,  y , y 
04/19/2013_23:48:55.823 , ABCD ,  x ,x ,, 
04/19/2013_23:48:56.309 , ABCD ,  x, x ,, 
04/19/2013_23:48:56.799 , ABCD ,  ,  ,  y , y 
04/19/2013_23:48:57.819 , ABCD ,  , ,   y , y

Also as I mentioned already I have many context columns and also many data columns.
Hope now it is clear.

7

It didn't post well (all glommed together). Please go back and edit with the code tags, like you had on the initial post. It makes it so much easier to communicate. Thanks.

8 
awk 'BEGIN { FS="," }
           { a1=$1; a2=$2; a3=$3; a4=$4
             getline < "file2";
             printf "%s , %s , %s , %s , ,\n", a1,a2,a3,a4
             printf "%s , %s , , , %s , %s\n", $1, $2, $3, $4  }' file1

outputs

04/19/2013_23:48:54.819 , ABCD , x , x , ,
04/19/2013_23:48:54.819 , ABCD , , , y , y  
04/19/2013_23:48:55.307 , ABCD , x , x , ,
04/19/2013_23:48:55.821 , ABCD , , , y , y  
04/19/2013_23:48:55.823 , ABCD , x , x , ,
04/19/2013_23:48:56.799 , ABCD , , , y , y
04/19/2013_23:48:56.309 , ABCD , x , x , ,
04/19/2013_23:48:57.819 , ABCD , , , y , y