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

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 302979535 by Don Cragun on Tuesday 16th of August 2016 03:47:15 AM

08-16-2016

Registered User

Quote:

Originally Posted by aachave1

Okay, I apologize Don, I am having a hard time getting this across. Hopefully I can answer some the of questions.

1. Will you guarantee that all timestamps in both of the files that will ever be processed on the same calendar date? Or, are there two date fields that have to be processed? Yes, both files will always be processed with the same calendar date because they are ran almost simultaneously. Yes, only because fields 16 thru 21 (some fields are duplicated because of the RAW field) fields are the time (epoch) that our telemetry extractor converts and then creates field 1 timestamp. My example stopped at field 18 because my real files have about 600 fields and data points. So technically there are 3 time fields - Day Of Year, time in milliseconds, time in microseconds. I said filed 21 in an earlier post because there were 3 added fields in newer files. In these particular files, they don't have these extra fields, but as long as I chose the correct msec field, my sort works properly.
CCSDS_DOY,CCSDS_DOY(RAW),CCSDS_MSEC
20550,20550,67522104

2. If there are two date fields (presumably fields 17 and 18 in the sample files in post #29), are those two date fields always adjacent in the input files? Yes, all the date fields 16-21 are always adjacent in both files.

3. And, repeating a question that has already been asked twice: Will the date field (or fields) used in file1 be the same as the field (or fields) used in file2? Yes, both files use the same time fields.

4. Will the milliseconds field in your files be set to the string 3600000 corresponding the exactly 1:00:00am or to the string 03600000 (i.e., are all values leading 0 padded to 8 digits, or are the values just the decimal number of milliseconds since midnight with no leading 0 fill)? (Note that the sort you were using in your examples sorting on field 21 would not work if that field does not have leading 0 fill.) It is an 8 digit decimal number.

5. Will you supply the field number(s) as parameters to your script, or are the field headings for the date field(s) in the two files constants that the script is supposed to find when reading the header lines? I only used the field numbers when I sorted off of the �msec� field (i.e sort -t -k,18,18 file1 file2) and that provided and accurate sort.

6. And, since at least one of the date fields is the last field in all of your sample input files, I will ask again: Are your input files in UNIX text file format or DOS text file format? (This might not matter on your system, but it does matter on the system I'm using to test my code.). These files are .csv files processed on a Linux platform.

7. If your input files are in DOS text file format, do you want output in DOS format or UNIX format? (DOS, UNIX, and don't care are valid answers to this question.) Unix format, but they will end up being .csv files after processing (not DOS).

8. And, obviously, supply us with the complete contents of your latest sample files (including some with different dates if the data in your real files won't always be for a single date) along with the expected output from those sample inputs. I will provide more when I get to a PC later or tomorrow. A couple of days ago, I tried sending my �real� files , but this site kept giving me errors when uploading.

For question #5: You didn't answer the question. You have now told us that we can't use field #1 and must use a field containing milliseconds. If you won't tell us how you expect your script to work, it is extremely hard to suggest how to write a script that will do what you want. From the sample data you have provided, I know that the names of the fields in your header line varies (sometimes with leading spaces, sometimes without; and different prefixes depending on what file is being processed). And, at least in file2 in post #1 in this thread, at least one of the millisecond fields does not have a value that matches the data in field 1 in that record (not only are the least significant 3 digits ignored, the high order 5 digits do not correspond to the HH:MM:SS part of field #1). Please be sure that the sample data you will be providing does not suffer from this same malady, and please explain how your script is supposed to determine which field contains the milliseconds data in the first input file and how your script is supposed to determine which field contains the milliseconds data i the second input file. Furthermore, in question #4 I said the way you were using sort would not work IF the milliseconds field being sorted was a variable length field. Since it is an 8 digit fixed width field with leading 0 fill, the sort command you were using should work. From the data you had shown us, we had no way to determine whether or not that would be true.

For question #6: Where a file is processed does not determine the format of a file. The format of a file is determine by the process that creates the file and the data used by that process when it is creating that file.

For quoins #7: A .csv file is a type of text file. Knowing that a file is a .csv file does not determine whether the file is a DOS text file or a UNIX text file. The difference is whether the file contains DOS line separators or UNIX line terminators. That is why I asked you to show us the output form the command:

Code:

tail -n 3 filename | od -bc

for your input files (which would show us the line terminators or separators used in those files), but you ignored that request both times I asked you to provide that information.

For question #8: This site doesn't allow .csv files to be uploaded, but it does allow .txt files to be uploaded. If you have a file named something.csv, change its name to something.txt and upload the .txt files.

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

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