1

I have data that I need to change before running it through the pandas library in python.

It is currently in a format that stores midnight values as 2400 and that should be changed to 0000. The format also does not pad either hour nor minute, and I think I need to do so in order to convert the 2 parameters into a desired zero padded 2400hr (0000-2359) format.

Now that's the easy part!

The hard part is that each time it rolls over from 2359 to 0000 it should also change the date (which is in 'dayoftheyear' format, which actually makes it easier I assume, i.e.: %j +1, aside from Dec 31).

So here is a sample of my data (last 2 columns are non-date values stored) at a day's rollover (it is a csv file, but I am showing it with a single space to delimit for visual clarity):

1,2014,361,2340,0,0
1,2014,361,2341,0,0
1,2014,361,2342,0,0
1,2014,361,2343,0,0
1,2014,361,2344,0,0
1,2014,361,2345,0,0
1,2014,361,2346,0,0
1,2014,361,2347,0,0
1,2014,361,2348,0,0
1,2014,361,2349,0,0
1,2014,361,2350,0,0
1,2014,361,2351,0,0
1,2014,361,2352,0,0
1,2014,361,2353,0,0
1,2014,361,2354,0,0
1,2014,361,2355,0,0
1,2014,361,2356,0,0
1,2014,361,2357,0,0
1,2014,361,2358,0,0
1,2014,361,2359,0,0
1,2014,361,2400,0,0
24,2014,361,2400,12.34
1,2014,365,2359,0,9
1,2014,365,2400,089.343,3
1,2015,1,1,234,456
1,2015,1,2,090,99
1,2015,365,2359,0,0
1,2015,365,2400,xx,xxx
1,2016,1,1,0,0
1,2016,1,2,0,0
1,2016,1,3,0,0

I assume the solution is a bunch of sed/awk nested in a for loop, but i'll leave that up to you code ninjas. Thanks in advance.

Ok, here is the same question but extended to include the "what if" once the new year rolls around. So I assume that the $2 column will get incremented come 365 to 366, and this is obviously not desirable.

How do I then extend the same incrementing/formatting to include a roll over come 366 to increment the year by 1?

I am going to take a blind stab at it:

#!/bin/bash


filename="${1/.dat/_prepped.dat}"

awk '/^1/{print $0}' $1  |cut -d "," -f2,3,4,5,6 |

awk 'BEGIN{FS=OFS=","}
$3 == "2400" {$2 = $2 + 1; $3 = 0}
$2 == "366" {$1 = $1 + 1; $2 = 1}
{ $3 = sprintf("%04i", $3) }
1' >$filename

I took a stab at integrating everything into a script that i feed the raw data (ex: home.dat) into in order to output the file (ex: home_prepped.dat).

Results of above data running through above script:

2014,361,2340,0,0
2014,361,2341,0,0
2014,361,2342,0,0
2014,361,2343,0,0
2014,361,2344,0,0
2014,361,2345,0,0
2014,361,2346,0,0
2014,361,2347,0,0
2014,361,2348,0,0
2014,361,2349,0,0
2014,361,2350,0,0
2014,361,2351,0,0
2014,361,2352,0,0
2014,361,2353,0,0
2014,361,2354,0,0
2014,361,2355,0,0
2014,361,2356,0,0
2014,361,2357,0,0
2014,361,2358,0,0
2014,361,2359,0,0
2014,362,0000,0,0
2014,365,2359,0,9
2015,1,0000,089.343,3
2015,1,0001,234,456
2015,1,0002,090,99
2015,365,2359,0,0
2016,1,0000,xx,xxx
2016,1,0001,0,0
2016,1,0002,0,0
2016,1,0003,0,0
  • woohoo, it worked! I made a couple of fake rows that were 2014,365,2359,0,0 and 2014,365,2400,0,0 and they both rolled over and updated the year/day column as desired. I am very stoked, and want to thank you for your help! Now up vote this question so I can enough reputation to do the same :) – geokrowding Dec 29 '14 at 0:16
  • It looks like you made it there, so that's this question dealt to, but I have a follow-up for your own consideration: what about 2016? – Michael Homer Dec 29 '14 at 0:29
  • You need to put the pattern $3 == 2400 and the corresponding action {$2 = ... on the same line - otherwise the pattern triggers the default action (printing) and the action is run unconditionally. – Michael Homer Dec 29 '14 at 2:02
  • Awk has regular variables that you can use to maintain state between records. I don't think I understand the use case any more though. – Michael Homer Dec 29 '14 at 2:20
  • All good, ran some tests and i think it's working as desired. I will edit to illustrate before/after. Thanks again. – geokrowding Dec 29 '14 at 2:35
1

awk does all of this by itself. sprintf does the formatting, ordinary patterns and assignments do the rest.

$3 == "2400" {$2 = $2 + 1; $3 = 0}
{ $3 = sprintf("%04i", $3) }
1

If you put that into dates.awk and then run your sample data through:

$ awk -F, -vOFS=, -f dates.awk < data

then you will get:

...
2014,344,2359,0,0
2014,345,0000,0,0
2014,345,0001,0,0
...

The first line of the script checks whether the third field is "2400" using an expression pattern and zeros and increments appropriately. The second pads the field to four digits with sprintf. The last ensures the line is printed.

You can squash that all into a single line to give a script to awk on the command line, and also put the field separators into the body by prepending {FS=OFS=","}.

You can deal with year rollover yourself; you should be able to pattern it on the above easily, but making the effort yourself will do you good.

  • great scot, awk is awesome! Though I haven't quite figured out the -vOFS yet (working on it). I appreciate the descriptions too, as I learn by illustrated example and this helps me out a lot more than struggling on my own. Truly appreciated, thanks. – geokrowding Dec 28 '14 at 23:47
  • -v VAR=value is the option for predefining a variable (just like setting it in the body). OFS is the output field separator variable. -vOFS=, sets the output separator to a comma, rather than the default (space), so that what you get out is still CSV. – Michael Homer Dec 28 '14 at 23:52
  • I am editing my question to extend it a bit to include the inevitable 'what to do come new year' question. I figure I should keep the question in this thread as asking it in another seems redundant. – geokrowding Dec 29 '14 at 0:00
0

So if third field is equal to 2400 you want to change it to 0000 and increase second field by one? Try awk then:

awk 'BEGIN{FS=OFS=","}$3==2400{$2++;$3="0000"}1'
  • very nice one-liner Jimmyj, cheers :) – geokrowding Dec 28 '14 at 23:58

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.