0

Consider a bunch of almost identical csv files. They all have in common the part shown, i.e. a line with Date;Time;... followed by four columns of data (some of the first lines have six columns, where the fifth column is empty and the sixth is mere descriptive text). There is also descriptive text separated into various columns preceding the line Date;Time;...

...
...
Date;Time;Airtemp;Quality;;Other info
1961-01-01;06:00:00;0.4;G;;...
1961-01-01;12:00:00;2.3;G;;...
1961-01-01;18:00:00;...;.;;...
1961-01-02;15:00:00;...;.  
..........;........;...;.
2015-09-01;........;...;.

Using the following commands (*)

awk -F ';' 'x==1 {print $1 " " $2 " " $3 " " $4} /Date/ {x=1}' file >> new_file

sed -i '' 's/[-:,]//g' new_file

creates a new_file with the following format

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

Now, using the command (**)

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

creates another column in new_file with a numbering of days.

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

Is there a way to combine commands (*) and (**) in one script? Currently these are run in two separate ones.

  • Is the day counting to be consecutive just how many days, or days since day 1? (i.e. if days are missing data, are they to be skipped in the numbering or not?) – Fiximan Nov 4 '19 at 13:47
  • @Fiximan The day counting is consecutive until the date hits a new year. See unix.stackexchange.com/q/550197/380342 – schn Nov 4 '19 at 14:18
0

Combining the two awk commands (and the sed):

awk -F ';' '
    x == 1 {
        gsub("[:,-]", "")
        tspec = sprintf("%4d %.2d %.2d 00 00 00", substr($1,1,4), substr($1,5,2), substr($1,7,4))
        t = mktime(tspec)
        print $1, $2, $3, $4, 0 + strftime("%j", t)
    }
    /Date/ { x = 1 }' file

This assumes that you are using either GNU awk or mawk (not BSD awk, which may not implement mktime() or strftime()).

The code combines the two awk commands and the sed (using gsub()), and I've also taken the liberty to correct the parsing of the date for setting tspec.

Given a file,

...
...
Date;Time;Airtemp;Quality;;Other info
1961-01-01;06:00:00;0.4;G;;...
1961-01-01;12:00:00;2.3;G;;...
1961-01-01;18:00:00;...;.;;...
1961-01-02;15:00:00;...;.
2015-09-01;........;...;.

this would produce

19610101 060000 0.4 G 1
19610101 120000 2.3 G 1
19610101 180000 ... . 1
19610102 150000 ... . 2
20150901 ........ ... . 244

Note that space is the default output field separator (OFS), so if you are not setting OFS to anything explicitly you don't (shouldn't) have to output the spaces using " " between each output field.

| improve this answer | |
  • Probably one final tweak. If one were to replace gsub("-","",$1) with gsub("-"," ",$1) so that the date formats to 1961 01 01, how could one remove the leading zeros from the month and day? Would gensubbe an option, and if so, how would one specify the "how" argument? – schn Nov 5 '19 at 11:55
  • @schn By changing the second parameter in the last two substr() calls so that they look at the correct offset in the string. The second needs to be substr($1,6,2) (to pick up two characters one step further along the string) and the last one substr($1,9,2) (to pick up two characters two steps further along the string). I've never ever had to use gensub for anything. – Kusalananda Nov 5 '19 at 11:58
  • This would preserve the correct day count, but would it remove the leading 0s in the months and days? That is, the leading zeros in 01 and 01in 1961 01 01. – schn Nov 5 '19 at 12:08
  • @schn Did you try it? The answer is that it would preserve the leading zeroes. The whole date, 1961 01 01 is a single string, and it's just copied without further modifications. – Kusalananda Nov 5 '19 at 12:11
  • 1
    Thank you. It worked. – schn Nov 5 '19 at 12:27
0

This is hard to answer since you're not showing us real data. But, if I understand you correctly, you don't need to consider the timestamp at all. based on your desired output, you just want to remove the - and : from the input and add the extra column:

$ awk -F';' '{day=substr($1,9,2); gsub(/[:-]/,""); printf "%s;%.1d\n",$0,day}' file
19610101;060000;0.4;G;...;1
19610101;120000;2.3;G;...;1
19610101;180000;...;.;1
19610102;150000;...;.  ;2
..........;........;...;.;0
20150901;........;...;.;1

Or, if you want tab-separated columns as you show (I think) in your desired output:

 $ awk -F';' -vOFS="\t" '{day=substr($1,9,2); gsub(/[:-]/,""); print $1,$2,$3,$4, sprintf("%.1d",day)}' file
19610101    060000  0.4 G   1
19610101    120000  2.3 G   1
19610101    180000  ... .   1
19610102    150000  ... .   2
..........  ........    ... .   0
20150901    ........    ... .   1
| improve this answer | |
  • The question has been edited. – schn Nov 4 '19 at 12:26
0

Try changing $4 to $5 to show the day of year

{print $1 " " $2 " " $3 " " $4}

| improve this answer | |

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.