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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging 2 text files when there is a common time stamp column in them
# 1  
Old 04-25-2013
Merging 2 text files when there is a common time stamp column in them

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:
Code:
   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:
Code:
         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.

Code:
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

Last edited by ks_reddy; 04-25-2013 at 04:00 AM.. Reason: Column names and data aligned
# 2  
Old 04-25-2013
Taking the data as presented and outputting as a csv.
Code:
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  
Old 04-25-2013
Thanks

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  
Old 04-25-2013
Quote:
By the way my files are of .csv type.
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 ----------

Code:
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). Smilie
# 5  
Old 04-25-2013
Quote:
Originally Posted by ks_reddy
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
Try adapting my scriptlet to your data and come back with (suitably anonimised) examples of where you are having difficulty.
# 6  
Old 04-25-2013
My Required output

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
Code:
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

Code:
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

Code:
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.

Last edited by ks_reddy; 04-25-2013 at 06:34 AM.. Reason: Formatted input and output files
# 7  
Old 04-25-2013
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add current time stamp column in existing csv file

Hi , I want to add a new column 'current_time stamp' in my existing csv file with current time stamp for all the records.I tried something this but this is printing 0 with date & time and printed date one line above header.Please help awk -F "," 'BEGIN{ OFS="," } {$6=system("date... (5 Replies)
Discussion started by: netdbaind
5 Replies

2. Shell Programming and Scripting

Files with date and time stamp

Hi Folks, Need a clarification on files with date and time stamp. Here is my requirement. There is a file created everyday with the following format "file.txt.YYYYMMDDHHMMSS". Now i need to check for this file and if it is available then i need to do some task to the file. I tried... (6 Replies)
Discussion started by: jayadanabalan
6 Replies

3. Shell Programming and Scripting

Merging two files without any common pattern

Hi I have file1 as IJU_NSOMOW; SOWWOD_TWUIQ; and file2 as how are you?; fine there; Now my problem is i need the output file as IJU_NSOMOW; how are you?; SOWWOD_TWUIQ; fine there; (2 Replies)
Discussion started by: Priya Amaresh
2 Replies

4. Shell Programming and Scripting

Merging files with common IDs without JOIN

Hi, I am trying to merge information across 2 files. The first file is a "master" file, with all IDS. File 2 contains a subset of IDs of those in File 1. I would like to match up individuals in File 1 and File 2, and add information in File 2 to that of File 1 if they appear. However, if an... (3 Replies)
Discussion started by: hubleo
3 Replies

5. UNIX for Dummies Questions & Answers

Merging two text files by a column and filling in the missing values

Hi, I have to text files that I want to merge by the first column. The values in the first column pretty much match for the first part. However there are some values that are present in column 1 and not present in column 2 or vice versa. For such values I would like to substitute X for the... (9 Replies)
Discussion started by: evelibertine
9 Replies

6. Shell Programming and Scripting

Select files by time stamp

Hi, I need help to read file in a directory on basis of time stamp. e.g. If file access in last 2 minutes it should not be copy to remote directory. Below is my script. +++++++++++++++++++++++++ #!/bin/ksh DATE=`date +"%Y-%m-%d_%H%M"` SEPARATER=" " exec < out_interfaces.cfg... (1 Reply)
Discussion started by: qamar.alam
1 Replies

7. UNIX for Dummies Questions & Answers

Merging two text files by a column

I have two text files. One has two columns and looks like below: rs# otherallele_freq rs10399749 0 rs4030303 0 rs4030300 0 rs940550 1.000 rs13328714 0 rs11490937 0 rs6683466 0 rs12025928 1.000 rs6650104 0 rs11240781 0... (5 Replies)
Discussion started by: evelibertine
5 Replies

8. UNIX for Dummies Questions & Answers

Merging two text files by a column

So I have two text files. The first one looks like this: refsnp_id chr_name chrom_start 1 rs1000000 12 126890980 2 rs10000010 4 21618674 3 rs10000012 4 1357325 4 rs10000013 4 37225069 5 rs1000002 3 183635768 And the second one looks like this: AUC rs1000000 0.03 0.1240 AUC ... (4 Replies)
Discussion started by: evelibertine
4 Replies

9. Shell Programming and Scripting

Merging 2 files based on a common column

Hi All, I do have 2 files file 1 has 4 tab delimited columns 234 a c dfgyu 294 b g fih 302 c h jzh 328 z c san 597 f g son File 2 has 2 tab delimted columns 234 23 302 24 597 24 I want to merge file 2 with file 1 based on the data common in both files which is the first column so... (6 Replies)
Discussion started by: Lucky Ali
6 Replies

10. Shell Programming and Scripting

Merging two files with a common column

Hi, I have two files file1 and file2. I have to merge the columns of those two files into file3 based on common column of two files. To be simple. file1: Row-id name1 13456 Rahul 16789 Vishal 18901 Karan file2 : Row-id place 18901 Mumbai ... (2 Replies)
Discussion started by: manneni prakash
2 Replies
Login or Register to Ask a Question