Keep only the closet match of timestamped row (include headers) from file1 to precede file2 row/s Post: 302979506

Sponsored Content

Top Forums UNIX for Beginners Questions & Answers Keep only the closet match of timestamped row (include headers) from file1 to precede file2 row/s Post 302979506 by aachave1 on Monday 15th of August 2016 05:47:06 PM

08-15-2016

Registered User

Quote:

Originally Posted by RudiC

Would this come close to what you want (may need some polishing):

Code:

awk '
NR == 1         {getline HD1 < F1
                 HD2 = $0
                 next
                }

$1 >= T[1]      {do     {LAST = TMP
                         ST = getline TMP < F1
                         split (TMP, T, FS)
                        }
                 while (($1 >= T[1]) && (ST == 1))
                 if (ST == 0)   {LAST = TMP
                                 T[1] = "ZZZ"
                                }
                 print HD1
                 print LAST
                 print HD2
                 print
                 next
                }
                {print 
                }

' FS="," F1=file1 file2
TIMEFORMATTED,G_CCSDS_VERSION,G_CCSDS_TYPE,G_CCSDS_2HDR_FLAG,G_CCSDS_APID,G_CCSDS_GRP_FLAGS,G_CCSDS_SEQ_COUNT,G_CCSDS_PKT_LEN,G_CCSDS_DOY,G_CCSDS_MSEC
2014/04/07 16:03:10,0,0,1,572,3,0,1917,20550,57790339
TIMEFORMATTED,CCSDS_VERSION,CCSDS_TYPE,CCSDS_2HDR_FLAG,CCSDS_APID,CCSDS_GRP_FLAGS,CCSDS_SEQ_COUNT,CCSDS_PKT_LEN,CCSDS_DOY,CCSDS_MSEC
2014/04/07 16:03:12,0,0,1,544,3,0,985,20550,57788894
2014/04/07 16:03:13,0,0,1,544,3,0,985,20550,57793894
2014/04/07 16:03:14,0,0,1,544,3,0,985,20550,57794894
TIMEFORMATTED,G_CCSDS_VERSION,G_CCSDS_TYPE,G_CCSDS_2HDR_FLAG,G_CCSDS_APID,G_CCSDS_GRP_FLAGS,G_CCSDS_SEQ_COUNT,G_CCSDS_PKT_LEN,G_CCSDS_DOY,G_CCSDS_MSEC
2014/04/07 16:03:15,0,0,1,572,3,0,1917,20550,57795339
TIMEFORMATTED,CCSDS_VERSION,CCSDS_TYPE,CCSDS_2HDR_FLAG,CCSDS_APID,CCSDS_GRP_FLAGS,CCSDS_SEQ_COUNT,CCSDS_PKT_LEN,CCSDS_DOY,CCSDS_MSEC
2014/04/07 16:03:15,0,0,1,544,3,0,985,20550,57795894
2014/04/07 16:03:16,0,0,1,544,3,0,985,20550,57796894
2014/04/07 16:03:17,0,0,1,544,3,0,985,20550,57797894

RudiC, this seems to work on my "real" files in different scenarios (i.e different file1 and file2 sizes, header sizes, header names etc..

I will do some more testing since my real files are very large and I have to make sure all data is intact, but so far so good!! Maybe I'm being too optimistic at the moment Smilie

I will test some more and get back to this forum with results soon.

Thanks to all of you (Stomp, RudiC, and Don Cragun) for your time on this!!

---------- Post updated at 05:47 PM ---------- Previous update was at 01:31 PM ----------

Quote:

Originally Posted by aachave1

I will test some more and get back to this forum with results soon.

Thanks to all of you (Stomp, RudiC, and Don Cragun) for your time on this!!

Oops, I found that it didn't quite sort completely accurate on my "real" files (small snippet below) because even though the times in column 1 that appeared equal (18:45:22), were actually different when it came to the msec column 19 (highlighted in red). So basically the first file2 row would have been with the previous file1 timestamp since it is less than the file1 time according to msec time.

I guess I need to find a way to sort on column 19 so that it is accurate down to milliseconds.

Code:

Output from RudiC code where 67522104 is less than 67522431 :

TIMEFORMATTED,G_CCSDS_VERSION, G_CCSDS_VERSION(RAW),G_CCSDS_TYPE, G_CCSDS_TYPE(RAW),G_CCSDS_2HDR_FLAG, G_CCSDS_2HDR_FLAG(RAW),G_CCSDS_APID, G_CCSDS_APID(RAW),G_CCSDS_GRP_FLAGS,G_CCSDS_GRP_FLAGS(RAW),G_CCSDS_SEQ_COUNT, G_CCSDS_SEQ_COUNT(RAW),G_CCSDS_PKT_LEN,G_CCSDS_PKT_LEN(RAW),G_CCSDS_DOY,G_CCSDS_DOY(RAW),G_CCSDS_MSEC
2014/04/07 18:45:22,0,0,0,0,1,1,572,572,3,3,0,0,1917,1917,20550,20550,67522431
TIMEFORMATTED,CCSDS_VERSION,CCSDS_VERSION(RAW),CCSDS_TYPE,CCSDS_TYPE(RAW),CCSDS_2HDR_FLAG,CCSDS_2HDR_FLAG(RAW),CCSDS_APID,CCSDS_APID(RAW),CCSDS_GRP_FLAGS,CCSDS_GRP_FLAGS(RAW),CCSDS_SEQ_COUNT,CCSDS_SEQ_COUNT(RAW),CCSDS_PKT_LEN,CCSDS_PKT_LEN(RAW),CCSDS_DOY,CCSDS_DOY(RAW),CCSDS_MSEC
2014/04/07 18:45:22,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67522104
2014/04/07 18:45:23,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67523104
2014/04/07 18:45:24,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67524104
2014/04/07 18:45:25,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67525104
2014/04/07 18:45:26,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67526104


Should be like this since 67522104 is greater than 67517432:

TIMEFORMATTED,G_CCSDS_VERSION, G_CCSDS_VERSION(RAW),G_CCSDS_TYPE, G_CCSDS_TYPE(RAW),G_CCSDS_2HDR_FLAG, G_CCSDS_2HDR_FLAG(RAW),G_CCSDS_APID, G_CCSDS_APID(RAW),G_CCSDS_GRP_FLAGS,G_CCSDS_GRP_FLAGS(RAW),G_CCSDS_SEQ_COUNT, G_CCSDS_SEQ_COUNT(RAW),G_CCSDS_PKT_LEN,G_CCSDS_PKT_LEN(RAW),G_CCSDS_DOY,G_CCSDS_DOY(RAW),G_CCSDS_MSEC
2014/04/07 18:45:17,0,0,0,0,1,1,572,572,3,3,0,0,1917,1917,20550,20550,67517432
TIMEFORMATTED,CCSDS_VERSION,CCSDS_VERSION(RAW),CCSDS_TYPE,CCSDS_TYPE(RAW),CCSDS_2HDR_FLAG,CCSDS_2HDR_FLAG(RAW),CCSDS_APID,CCSDS_APID(RAW),CCSDS_GRP_FLAGS,CCSDS_GRP_FLAGS(RAW),CCSDS_SEQ_COUNT,CCSDS_SEQ_COUNT(RAW),CCSDS_PKT_LEN,CCSDS_PKT_LEN(RAW),CCSDS_DOY,CCSDS_DOY(RAW),CCSDS_MSEC
2014/04/07 18:45:21,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67521104
2014/04/07 18:45:22,0,0,0,0,1,1,544,544,3,3,0,0,985,985,20550,20550,67522104

aachave1

View Public Profile for aachave1

Find all posts by aachave1

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

match value from file1 in file2

Hi, i've two files (file1, file2) i want to take value (in column1) and search in file2 if the they match print the value from file2. this is what i have so far. awk 'FILENAME=="file1"{ arr=$1 } FILENAME=="file2" {print $0} ' file1 file2

2. Shell Programming and Scripting

Match one column of file1 with that of file2

Hi, I have file1 like this aaa ggg ddd vvv eeeand file2 aaa 2 aaa 443 xxx 76 aaa 34 ggg 33 wee 99 ggg 33 ddd 1 ddd 10 ddd 98 sds 23

3. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number...

4. Shell Programming and Scripting

Match part of string in file2 based on column in file1

I have a file containing texts and indexes. I need the text between (and including ) INDEX and number "1" alone in line. I have managed this: awk '/INDEX/,/1$/{if (!/1$/)print}' file1.txt It works for all indexes. And then I have second file with years and indexes per year, one per line...

5. Shell Programming and Scripting

Get row number from file1 and print that row of file2

Hi. How can we print those rows of file2 which are mentioned in file1. first character of file1 is a row number.. for eg file1 1:abc 3:ghi 6:pqr file2 a abc b def c ghi d jkl e mno f pqr ...

6. Shell Programming and Scripting

Match single line in file1 to groups of lines in file2

I have two files. File 1 is a two-column index file, e.g. comp11084_c0_seq6:130-468(-) comp12746_c0_seq3:140-478(+) comp11084_c0_seq3:201-539(-) comp12746_c0_seq2:191-529(+) File 2 is a sequence file with headers named with the same terms that populate file 1. ...

7. Shell Programming and Scripting

Print sequences from file2 based on match to, AND in same order as, file1

I have a list of IDs in file1 and a list of sequences in file2. I can print sequences from file2, but I'm asking for help in printing the sequences in the same order as the IDs appear in file1. file1: EN_comp12952_c0_seq3:367-1668 ES_comp17168_c1_seq6:1-864 EN_comp13395_c3_seq14:231-1088...

8. Shell Programming and Scripting

Reading and appending a row from file1 to file2 using awk or sed

Hi, I wanted to add each row of file2.txt to entire length of file1.txt given the sample data below and save it as new file. Any idea how to efficiently do it. Thank you for any help. input file file1.txt file2.txt 140 30 200006 141 32 140 32 200006 142 33 140 35 200006 142...

9. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range...

10. UNIX for Beginners Questions & Answers