0

Consider the below extract of a file:

19610101   060000  0.4  G
19610101   120000  2.3  G
19610101   180000  ...  .
19610102   150000   
19610102   180000
19610103   060000
........   ......
20150901

The leftmost column specifies a date. If one would like to insert a column specifying the day of the year as a number between 1-365/366, how would one go about doing that?

If one were to extract the last four digits of each date, that is 0101,0102, ..., one would have a recursive and increasing sequence of numbers. Where would one go from there? Or is there a simpler implementation?

4
  • That first column is the date in yyyymmdd format, right? Does it contain every calendar day in order, or can there be gaps?
    – ilkkachu
    Nov 3 '19 at 21:33
  • Correct, it is the date in yyyymmdd and the calendar days are ordered. There could be potential gaps of missing days.
    – schn
    Nov 3 '19 at 21:36
  • Linux-based platform (or somewhere else with GNU date)?
    – roaima
    Nov 3 '19 at 22:44
  • Linux-based platform.
    – schn
    Nov 3 '19 at 22:45
4

Given the file file with contents

19610101   060000  0.4  G
19610101   120000  2.3  G
19610101   180000  ...  .
19610102   150000  ...  .
19610102   180000  ...  .
19610103   060000  ...  .
20150901   ......  ...  .

we may use GNU awk or mawk (both of these have mktime() and strftime()):

awk '
    {
        tspec = sprintf("%4d %.2d %.2d 00 00 00", substr($1,1,4), substr($1,5,2), substr($1,7,2))
        t = mktime(tspec)
        $(NF+1) = strftime("%j",t)
    } { print }' file

This creates a Unix timestamp, t, from the date parsed from the file's first column (midnight is used as the time). It then formats the timestamp using strftime() with the %j format, which will give us the day of the year as a zero-filled integer (see man strftime). This number is inserted as the new column, and then the line is printed.

The result:

19610101 060000 0.4 G 001
19610101 120000 2.3 G 001
19610101 180000 ... . 001
19610102 150000 ... . 002
19610102 180000 ... . 002
19610103 060000 ... . 003
20150901 ...... ... . 244

To get rid of the zero filling, use $(NF+1) = 0 + strftime(...).

15
  • Thanks for the reply. The file has four columns and doesn't actually include the "Date Time" line. How would one modify the posted awk command in that regard? What would be the full command to get rid of the zero filling?
    – schn
    Nov 3 '19 at 22:42
  • @schn To get rid of the zero filling, just insert 0+ before strftime. To not skip the header line remove the NR>1 (leave the { though). The code will always insert a new column, so if you have four columns, you'll get a new fifth one.
    – Kusalananda
    Nov 3 '19 at 22:51
  • 1
    @schn Also, when giving sample data in a question that requires parsing and modifying that data, do consider posting data that is actually representative of the real data.
    – Kusalananda
    Nov 3 '19 at 22:54
  • @Kusalananda The question has been edited to include what is actually in the file. The code gives mixed results. It produces another column with the number of the day (with the zero filling removed), but sometimes these are out of sync. It also seems to loop through the file several times, with different outputs. See attached output.
    – schn
    Nov 3 '19 at 23:20
  • 19740519 120000 22.8 G 129 19740519 180000 17.4 G 129 19740520 060000 11.5 G 120 and
    – schn
    Nov 3 '19 at 23:20
0

It is possible to print the day of the year with %j (similar to julian day).

That is available in most date implementations.

So, your input string, which has a format of %Y%m%d %H%M%S should be modified to be %Y%m%d %j %H%M%S. That is easy with busybox date:

$ busybox date -D '%Y%m%d %H%M%S' -d '19610101 060000' +'%Y%m%d %j %H%M%S'
19610101 001 060000

But busybox date doesn't have an -f option to process all lines of a file directly. The options are to add an awk wrapper to call busybox date for each line, or to modify the source file to have dates as 1961/01/01 06:00:00, something GNU date could read.

$ date -d '1961/01/01     06:00:00' +'%Y%m%d %j %H%M%S'
19610101 001 060000

so, use sed to convert the file (removing empty lines and text lines so date doesn't choke on the input):

$ sed -E '/^[^0-9]|^$/d;
          s#(....)(..)(..)[[:blank:]]*(..)(..)(..)#\1/\2/\3 \4:\5:\6#' \
          datefile >newdatefile

And then process it with (one single instance of) date.

$ date -f newdatefile +'%Y%m%d %j %H%M%S' > outputfile
0

If your data are homogeneous, ex.

$ cat file
Date       Time
19610101   060000
19610101   120000
19610101   060000
19610102   120000
19610102   060000
19610102   120000
20150901   060000

then Miller (mlr) might be a good choice:

$ mlr --pprint --fs " " --repifs put -S '
    $Day = strftime(strptime($Date,"%Y%m%d"),"%j")
' file
Date     Time   Day
19610101 060000 001
19610101 120000 001
19610101 060000 001
19610102 120000 002
19610102 060000 002
19610102 120000 002
20150901 060000 244

Note the use of -S to coerce the undelimited YYYYmmmdd field into string type for strptime (by default, it is parsed as an integer).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.