0

I have two sets of files which attempting to merge into a single file and filling in associated missing data.

Files are all , delimited

The first file contains 13 columns with the 8th column containing the date in YYYY-MM-DD format (Note: this file is missing 44 days. The second file is 2 columns with the first being the full calendar year (366 days leap year) in YYYY-MM-DD format, and the second being the associated Julian date value.

Sample file with missing days #1

06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-26,Parts-per-million,24,100.0,0.379167,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-27,Parts-per-million,24,100.0,0.2875,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-28,Parts-per-million,11,46.0,0.163636,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-30,Parts-per-million,20,83.0,0.23,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-31,Parts-per-million,24,100.0,0.195833,10

I have attempted using the following command in attempt to merge the files together and create a new file with 14 columns as the missing dates are filled in and the Julian dates added. I also looking for the code to auto populate the fixed values for Columns 1-7 and 9 from the initial file and fill columns 10-13 with -999 given this data is missing.

awk -F ',' 'NR==FNR {h[$1] = $14; next} {print $1,$2,$3,$4,$5,$6,$7,$8,h[$2],$9,$10,$11,$12,$13}' temp2.tmp temp1.tmp  > temp3.tmp

06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-26,26,Parts-per-million,24,100.0,0.379167,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-27,27,Parts-per-million,24,100.0,0.2875,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-28,28,Parts-per-million,11,46.0,0.163636,10
**06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-29,29,Parts-per-million,-999,-999,-999,-999**
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-30,30,Parts-per-million,20,83.0,0.23,10
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-31,31,Parts-per-million,24,100.0,0.195833,10
1
  • "NR==FNR {h[$1] = $14; next}" If the first file only has 13 columns, you are storing zilch in the h array with index "06". What were you hoping to achieve? Jan 16, 2022 at 15:01

1 Answer 1

0

Now, datetime calculations always are a ... difficult thing. Esp. if the sequence of datetimes crosses midnight, month or year end, or daylight saving time switches. Here, we work with epoch seconds, to be on the safe side. Conversion back to datetime with the date command may not be available on all *nix flavours. And, we set the TZ variable to "UTC" to avoid the DST problem. Try without, and you will see. Here we go, try:

export TZ=UTC                                                       # get rid of side effects, e.g. DST switching

cut -d, -f8 samplefile | date -f- +%s | paste - samplefile > TMP1   # prepend epoch seconds to the input file

{ read MIN DUMMY                                                    # get file´s MIN and MAX dates
  while read TMP DUMMY           
     do MAX=$TMP
     done                                                           # and calculate a sequence of days between them
     eval echo @{$MIN..$MAX..86400} | tr ' ' $'\n' | date -f- +$'%s\t%Y-%m-%d\t%y%j'
} < TMP1 > TMP2                                                     # in epoch, yyyy-mm-dd, and julian format

join -a1 -a2 -- TMP1 TMP2 | awk -F"[, ]" '                          # join first and second intermediate files
NF == 3         {split($0, TMPINS)                                  # orig. files line missing; fill temp array with epoch etc. data
                 $0  = SAVED                                        # get last saved complete line
                 $9  = TMPINS[2]                                    # overwrite "yesterday´s" date 
                 $NF = TMPINS[3]                                    # append julian date
                 $11 = $12 = $13 = $14 = -999                       # set invalid indicator
                }
NF >= 13        {SAVED = $0                                         # correct line? save it
                 $1 = $1                                            # recreate line with OFS char
                }

                {sub($1",",_)                                       # for all lines: remove leading epoch field
                 $14 = $15                                          # put julian date into right place
                 NF--                                               # get rid of last field; may not work in ALL awks
                }
1                                                                   # default action: print
' OFS=","
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-26,Parts-per-million,24,100.0,0.379167,10,20026
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-27,Parts-per-million,24,100.0,0.2875,10,20027
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-28,Parts-per-million,11,46.0,0.163636,10,20028
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-29,Parts-per-million,-999,-999,-999,-999,20029
06,037,0016,42101,34.14435,-117.85036,1-HOUR,2020-01-30,Parts-per-million,20,83.0,0.23,10,20030

That entire thing can, with the help of a FIFO, be written on one looong pipe of commands:

mkfifo TMPFIFO
cut -d, -f8 samplefile | date -f- +%s | tee -a >(read MIN; while read TMP; do MAX=$TMP; done; eval echo @{$MIN..$MAX..86400} | tr ' ' $'\n' > TMPFIFO) | paste - samplefile | join -a1 -a2 -- - <(date -fTMPFIFO +$'%s\t%Y-%m-%d\t%y%j') | awk -F"[, ]" 'NF == 3 {split($0, TMPINS); $0 = SAVED; $9 = TMPINS[2]; $NF = TMPINS[3]; $11 = $12 = $13 = $14 = -999} NF >= 13 {SAVED = $0; $1 = $1} {sub($1",",_); $14 = $15; NF--} 1' OFS=","
1
  • Don't use all upper case names for awk or non-exported shell variables to avoid clashing with builtin-names and so your code isn't obfuscated by looking like you're using builtin names when you aren't. See stackoverflow.com/questions/673055/….
    – Ed Morton
    Jan 17, 2022 at 17:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .