Identifying data gaps

I have files such as the one attached which include a series of time observations, which are stopped and then restarted for another set of readings. The sequence is interrupted when more than 5 minutes occurs between readings.

I would like to have a transform identify when a run is ended and place a blank row in the output file before continuing to write the data to the same file.

I wonder if you have any suggestions about how I might be able to do this?

Thanks for any help with this.

Common area.csv (4.4 KB)

You can do it like this:

add-blank.transform (5.2 KB)

The basic steps are:

  • copy and rename the date and time columns (Copy Cols and Rename Cols)
  • offset the new columns by 1 (Offset)
  • convert the date and times into datetime strings that Javascript understand (should have done this before copying the columns in retrospect!) (Javascript, could have used Concat Cols)
  • calculate the difference between the datetime and the offset datetime in seconds using javascript (Javascript)
  • decide whether to add a new row based on whether the difference between the datetime and next (offset) datetime is > 300 seconds (If)
  • add the blank rows where required (New rows)
  • remove the extra columns added (Remove Cols)

Out of curiosity, how did you format / reformat the time field?

@DanFeliciano

I used some javascript in a Javascript transform:

return $(Date) + 'T' + $(Time);

E.g. to turn:

2021-09-14,13:03:28

Into:

2021-09-14T13:03:28

This is then in a form you can pass to a javascript Date object:

// difference in seconds
return ( new Date( $(OffsetDatetime) ) - new Date( $(Datetime) ) ) / ( 1000 );

I could have also used Concat Cols with T as the delimiter for the concatenation.

You can download the .transform (above) for more details.