Reformat text file using awk

Question

I've this kind of data in file :

2012-01-01  43.00   1   50.00   1   53.00   1   49.00   1   49.00   1   45.00   1   53.00   1   44.00   1   42.00   1   52.00   1   54.00   1   52.00   1   51.00   1   53.00   1   53.00   1   52.00   1   -999.00 0   38.00   1   21.00   1   19.00   1   31.00   1   37.00   1   36.00   1   32.00   1
2012-01-02  14.00   1   27.00   1   36.00   1   40.00   1   32.00   1   31.00   1   16.00   1   3.00    1   21.00   1   36.00   1   42.00   1   44.00   1   44.00   1   46.00   1   47.00   1   -999.00 0   36.00   1   24.00   1   28.00   1   33.00   1   36.00   1   40.00   1   47.00   1   48.00   1   
2012-01-03  46.00   1   48.00   1   49.00   1   50.00   1   51.00   1   51.00   1   43.00   1   37.00   1   34.00   1   43.00   1   43.00   1   39.00   1   39.00   1   43.00   1   -999.00 0   32.00   1   34.00   1   19.00   1   18.00   1   21.00   1   22.00   1   40.00   1   46.00   1   46.00   1

Each line contain the date (YYYY-MM-DD) and one value by hour (seperate by " 1 "). But If the value equal '-999.00', the separator began " 0 ".

I'ld like to obtain a file like this with awk :

 2012010100 43.00
 2012010101 50.00
 2012010102 53.00
  ...
 2012010123 32.00
 2012010200 14.00
  ...
 2012010223 48.00
 2012010300 46.00
  ...
 2012010323 46.00
  ... (for 1 year)

So, each line contain the date (with a new format YYYYMMDDHH) and the value corresponding to the hour.

Someone have an idea ? thanks

where does 2012010100 come from? that is, what are the last two zeros? Also, please show what you tried so far, since an awk loop seems to be quite simple — fedorqui
– fedorqui, Commented Apr 24, 2015 at 15:00
the last two digits of the dates are for the hour of the day . The date is coded as follows: YYYYMMDDHH — Phlegyas
– Phlegyas, Commented Apr 24, 2015 at 15:05
and where do you get this hour from? It is not clear in your input data. Also, what are those 1 and 0 in between numbers? — fedorqui
– fedorqui, Commented Apr 24, 2015 at 15:05

fedorqui · Accepted Answer · 2015-04-24 15:11:52Z

4

I would say:

awk '{t=0; gsub(/-/,"",$1)
      for (i=2; i<=NF; i+=2) printf "%s%02i %.2f\n", $1, t++, $i}' file

Explanation

t=0 reset counter of hours
gsub(/-/,"",$1) remove dashes from the first field.
for (i=2; i<=NF; i+=2) printf "%s%02i %.2f\n", $1, t++, $i} loop through the even elements and print them together with the first field. Note we use %02i to make sure the hour is HH and not just H if below 10. Also, %.2f to get numbers with two decimal digits.

Test

$ awk '{t=0; gsub(/-/,"",$1); for (i=2; i<=NF; i+=2) printf "%s%02i %.2f\n", $1, t++, $i}' a
2012010100 43.00
2012010101 50.00
2012010102 53.00
2012010103 49.00
2012010104 49.00
2012010105 45.00
2012010106 53.00
2012010107 44.00
2012010108 42.00
2012010109 52.00
2012010110 54.00
2012010111 52.00
2012010112 51.00
2012010113 53.00
2012010114 53.00
2012010115 52.00
2012010116 -999.00
2012010117 38.00
2012010118 21.00
2012010119 19.00
2012010120 31.00
2012010121 37.00
2012010122 36.00
2012010123 32.00
2012010200 14.00
2012010201 27.00
2012010202 36.00
2012010203 40.00
2012010204 32.00
2012010205 31.00
2012010206 16.00
2012010207 3.00
2012010208 21.00
2012010209 36.00
2012010210 42.00
2012010211 44.00
2012010212 44.00
2012010213 46.00
2012010214 47.00
2012010215 -999.00
2012010216 36.00
2012010217 24.00
2012010218 28.00
2012010219 33.00
2012010220 36.00
2012010221 40.00
2012010222 47.00
2012010223 48.00
2012010300 46.00
2012010301 48.00
2012010302 49.00
2012010303 50.00
2012010304 51.00
2012010305 51.00
2012010306 43.00
2012010307 37.00
2012010308 34.00
2012010309 43.00
2012010310 43.00
2012010311 39.00
2012010312 39.00
2012010313 43.00
2012010314 -999.00
2012010315 32.00
2012010316 34.00
2012010317 19.00
2012010318 18.00
2012010319 21.00
2012010320 22.00
2012010321 40.00
2012010322 46.00
2012010323 46.00

edited Apr 24, 2015 at 15:11

answered Apr 24, 2015 at 15:09

fedorqui

293k113 gold badges592 silver badges640 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wintermute Over a year ago

I was about to post the same with i / 2 instead of t.

Tom Fenech Over a year ago

I was about to suggest changing the format specifier. It looks like you could just use %s instead of %.2f in this case, although I guess they do look like floating point numbers. Any reason to have gone for %02i over %02d, out of interest?

fedorqui Over a year ago

@TomFenech I didn't recall how to print 2 digits so I googled it... and it was the first hit :D

Phlegyas Over a year ago

it works like a charm ! Thanks a lot ! I just remplace %d by %.2f for a future post-process Many thanks ;)

Tom Fenech Over a year ago

Fair enough, it's just that you then used %d for the next one! Anyway, nice explanation.

Collectives™ on Stack Overflow

Reformat text file using awk

1 Answer 1

Explanation

Test

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation

Test

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related