CODEWITHSUNDEEP
awk
Phlegyas
Phlegyas

Reputation: 37

Reformat text file using awk

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

Upvotes: 0

Views: 45

Answers (1)

fedorqui
fedorqui

Reputation: 289565

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

Upvotes: 4

Related Questions