compare files basis two columns and add field

Question

I have files with column-wise dates and time in "YYYY MM DD HHMM" format plus a variable (temperature) and want to convert them into YYYY DDD format (and keep hour and temperature as is). They look like this but same date appears several times in file:

1980 01 01 0100 3.3
1982 04 11 0400 2.2
1985 12 04 0700 1.7
1995 12 31 1000 2.2

I have created an index file (1980-2017) with the number of days to be added to each date of the first file to get the cumulative day of year DDD (last column). First year looks like this (1980 was a leap year):

1980 01 31  000
1980 02 29  031
1980 03 31  060
1980 04 30  090
1980 05 31  121
1980 06 30  152
1980 07 31  182
1980 08 31  213
1980 09 30  244
1980 10 31  274
1980 11 30  305
1980 12 31  335

I am trying to compare the two files based on first two columns and if they match to add the fourth column of file2 to third column of file 1 and end up with something like this:

1980 001 0100 3.3 
1982 101 0400 2.2 
1985 346 0700 1.7 
1995 365 1000 2.2

I managed to compare the two columns of the files and add the two columns with awk below:

awk -F' ' 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' junktemp matrix_sample | awk '{print $1, $3+$4}' 

but this way I lose $4 and $5 (hour and temperature). Is there a way to combine the two awk functions and get $4 and $5 of file1 in the result as well? Any help much appreciated.

Answer 1

It's hard to test since you've only provided potential matches for a single year, but

awk 'NR==FNR{c[$1$2]=$4; next} ($1$2 in c) {$3 = sprintf("%03d", $3 + c[$1$2])} {print $1, $3, $4, $5}' file2 file1
1980 001 0100 3.3
1982 11 0400 2.2
1985 04 0700 1.7
1995 31 1000 2.2

Answer 2

Assuming GNU date and bash (or any shell that does process substitution with <(...)) and that the data is stored in the file file as in the first example data in the question:

$ paste -d ' ' <( date -f <( cut -d ' ' -f1-3 file | tr ' ' '-' ) +"%Y %j" ) \
               <( cut -d ' ' -f4-5 file )
1980 001 0100 3.3
1982 101 0400 2.2
1985 338 0700 1.7
1995 365 1000 2.2

• The first cut is used together with tr to transform the dates in the first three columns of the input data to the form YYYY-MM-DD.
• This is passed to GNU date for batch processing through its -f option. As output we request dates using the format %Y %j. %Y is the year on the form YYYY and %j is the day of the year on the form DDD.
• This is pasted together with the last two columns from the original file (produced by the second cut) using a single space as a delimiter.

This is a direct transformation from the original data to the end result, bypassing the need for the index file that you created.