How do I convert 12-hour to 24-hour time without info on AM or PM?

I received from data from a stream flow data logger but the time is recorded in 12-hour time without information on AM or PM. I can infer by looking at the order of the times whether it is AM or PM but I need to convert them to 24-hour time.

I have other logger data that uses 24-hour time so I need to make sure they match. I used the as.POSIXct() to format all the other data but I am having issues with this particular set.

I am using R for this analysis.

Here is what the data look like:

          Date_Time    PT.Level
2008-11-21 11:40:00      0.7502
2008-11-21 11:45:00      0.7502
2008-11-21 11:50:00      0.7480
2008-11-21 11:55:00      0.7458
2008-11-22 12:00:00      0.7458
2008-11-22 12:05:00      0.7436
2008-11-22 12:05:42          NA
2008-11-22 12:10:00      0.7436
2008-11-22 12:15:00      0.7414
#             [...]       [...]
2008-11-22 11:45:00      0.7304
2008-11-22 11:50:00      0.7304
2008-11-22 11:55:00      0.7304
2008-11-22 12:00:00      0.7282
2008-11-22 12:00:43          NA
2008-11-22 12:05:00      0.7282
2008-11-22 12:10:00      0.7282
2008-11-22 12:15:00      0.7282

Any suggestions?

Solution

Using ave with cumsum. If there's no switch within a day, we need case handling using table. For duplicated hours we may set diff == 0 to FALSE.

I don't know how complete your data is, but this should work if there are no dupes and always 00:00 and 12:00 is available each day.

v2 <- ave(as.numeric(substr(v1, 12, 13)) %% 12 == 0, as.Date(v1), FUN=function(x) {
  if (length(table(x)) == 1) 2
  else {
    x[c(1, diff(x)) == 0] <- FALSE
    cumsum(x)
  }
})
v2 <- c("AM", "PM")[v2]

Result

cbind.data.frame(v, v1, v2)

#                      v                  v1 v2
# 1  2020-05-22 22:00:00 2020-05-22 10:00:00 PM
# 2  2020-05-22 23:00:00 2020-05-22 11:00:00 PM
# 3  2020-05-23 00:00:00 2020-05-23 12:00:00 AM
# 4  2020-05-23 00:01:00 2020-05-23 12:01:00 AM  ## duplicated 12 stays AM
# 5  2020-05-23 00:59:00 2020-05-23 12:59:00 AM  ## duplicated 12 stays AM
# 6  2020-05-23 01:00:00 2020-05-23 01:00:00 AM
# 7  2020-05-23 02:00:00 2020-05-23 02:00:00 AM
# 8  2020-05-23 03:00:00 2020-05-23 03:00:00 AM
# 9  2020-05-23 04:00:00 2020-05-23 04:00:00 AM
# 10 2020-05-23 05:00:00 2020-05-23 05:00:00 AM
# 11 2020-05-23 06:00:00 2020-05-23 06:00:00 AM
# 12 2020-05-23 07:00:00 2020-05-23 07:00:00 AM
# 13 2020-05-23 08:00:00 2020-05-23 08:00:00 AM
# 14 2020-05-23 09:00:00 2020-05-23 09:00:00 AM
# 15 2020-05-23 10:00:00 2020-05-23 10:00:00 AM
# 16 2020-05-23 11:00:00 2020-05-23 11:00:00 AM
# 17 2020-05-23 12:00:00 2020-05-23 12:00:00 PM
# 18 2020-05-23 13:00:00 2020-05-23 01:00:00 PM
# 19 2020-05-23 14:00:00 2020-05-23 02:00:00 PM
# 20 2020-05-23 15:00:00 2020-05-23 03:00:00 PM
# 21 2020-05-23 16:00:00 2020-05-23 04:00:00 PM
# 22 2020-05-23 17:00:00 2020-05-23 05:00:00 PM
# 23 2020-05-23 18:00:00 2020-05-23 06:00:00 PM
# 24 2020-05-23 19:00:00 2020-05-23 07:00:00 PM
# 25 2020-05-23 20:00:00 2020-05-23 08:00:00 PM
# 26 2020-05-23 21:00:00 2020-05-23 09:00:00 PM
# 27 2020-05-23 22:00:00 2020-05-23 10:00:00 PM
# 28 2020-05-23 23:00:00 2020-05-23 11:00:00 PM
# 29 2020-05-24 00:00:00 2020-05-24 12:00:00 AM
# 30 2020-05-24 01:00:00 2020-05-24 01:00:00 AM
# 31 2020-05-24 02:00:00 2020-05-24 02:00:00 AM
# 32 2020-05-24 03:00:00 2020-05-24 03:00:00 AM
# 33 2020-05-24 04:00:00 2020-05-24 04:00:00 AM
# 34 2020-05-24 05:00:00 2020-05-24 05:00:00 AM
# 35 2020-05-24 06:00:00 2020-05-24 06:00:00 AM
# 36 2020-05-24 07:00:00 2020-05-24 07:00:00 AM
# 37 2020-05-24 08:00:00 2020-05-24 08:00:00 AM
# 38 2020-05-24 09:00:00 2020-05-24 09:00:00 AM
# 39 2020-05-24 10:00:00 2020-05-24 10:00:00 AM
# 40 2020-05-24 11:00:00 2020-05-24 11:00:00 AM
# 41 2020-05-24 12:00:00 2020-05-24 12:00:00 PM
# 42 2020-05-24 13:00:00 2020-05-24 01:00:00 PM
# 43 2020-05-24 14:00:00 2020-05-24 02:00:00 PM
# 44 2020-05-24 15:00:00 2020-05-24 03:00:00 PM
# 45 2020-05-24 16:00:00 2020-05-24 04:00:00 PM
# 46 2020-05-24 17:00:00 2020-05-24 05:00:00 PM
# 47 2020-05-24 18:00:00 2020-05-24 06:00:00 PM
# 48 2020-05-24 19:00:00 2020-05-24 07:00:00 PM
# 49 2020-05-24 20:00:00 2020-05-24 08:00:00 PM
# 50 2020-05-24 21:00:00 2020-05-24 09:00:00 PM

##Result

    cbind.data.frame(v, v1, v2)

[]()

    #                      v               v1 v2
    # 1  2020-05-22 22:00:00 2020-05-22 10:00 PM
    # 2  2020-05-22 23:00:00 2020-05-22 11:00 PM
    # 3  2020-05-23 00:00:00 2020-05-23 12:00 AM
    # 4  2020-05-23 01:00:00 2020-05-23 01:00 AM
    # 5  2020-05-23 02:00:00 2020-05-23 02:00 AM
    # 6  2020-05-23 03:00:00 2020-05-23 03:00 AM
    # 7  2020-05-23 04:00:00 2020-05-23 04:00 AM
    # 8  2020-05-23 05:00:00 2020-05-23 05:00 AM
    # 9  2020-05-23 06:00:00 2020-05-23 06:00 AM
    # 10 2020-05-23 07:00:00 2020-05-23 07:00 AM
    # 11 2020-05-23 08:00:00 2020-05-23 08:00 AM
    # 12 2020-05-23 09:00:00 2020-05-23 09:00 AM
    # 13 2020-05-23 10:00:00 2020-05-23 10:00 AM
    # 14 2020-05-23 11:00:00 2020-05-23 11:00 AM
    # 15 2020-05-23 12:00:00 2020-05-23 12:00 PM
    # 16 2020-05-23 13:00:00 2020-05-23 01:00 PM
    # 17 2020-05-23 14:00:00 2020-05-23 02:00 PM
    # 18 2020-05-23 15:00:00 2020-05-23 03:00 PM
    # 19 2020-05-23 16:00:00 2020-05-23 04:00 PM
    # 20 2020-05-23 17:00:00 2020-05-23 05:00 PM
    # 21 2020-05-23 18:00:00 2020-05-23 06:00 PM
    # 22 2020-05-23 19:00:00 2020-05-23 07:00 PM
    # 23 2020-05-23 20:00:00 2020-05-23 08:00 PM
    # 24 2020-05-23 21:00:00 2020-05-23 09:00 PM
    # 25 2020-05-23 22:00:00 2020-05-23 10:00 PM
    # 26 2020-05-23 23:00:00 2020-05-23 11:00 PM
    # 27 2020-05-24 00:00:00 2020-05-24 12:00 AM
    # 28 2020-05-24 01:00:00 2020-05-24 01:00 AM
    # 29 2020-05-24 02:00:00 2020-05-24 02:00 AM
    # 30 2020-05-24 03:00:00 2020-05-24 03:00 AM
    # 31 2020-05-24 04:00:00 2020-05-24 04:00 AM
    # 32 2020-05-24 05:00:00 2020-05-24 05:00 AM
    # 33 2020-05-24 06:00:00 2020-05-24 06:00 AM
    # 34 2020-05-24 07:00:00 2020-05-24 07:00 AM
    # 35 2020-05-24 08:00:00 2020-05-24 08:00 AM
    # 36 2020-05-24 09:00:00 2020-05-24 09:00 AM
    # 37 2020-05-24 10:00:00 2020-05-24 10:00 AM
    # 38 2020-05-24 11:00:00 2020-05-24 11:00 AM
    # 39 2020-05-24 12:00:00 2020-05-24 12:00 PM
    # 40 2020-05-24 13:00:00 2020-05-24 01:00 PM
    # 41 2020-05-24 14:00:00 2020-05-24 02:00 PM
    # 42 2020-05-24 15:00:00 2020-05-24 03:00 PM
    # 43 2020-05-24 16:00:00 2020-05-24 04:00 PM
    # 44 2020-05-24 17:00:00 2020-05-24 05:00 PM
    # 45 2020-05-24 18:00:00 2020-05-24 06:00 PM
    # 46 2020-05-24 19:00:00 2020-05-24 07:00 PM
    # 47 2020-05-24 20:00:00 2020-05-24 08:00 PM
    # 48 2020-05-24 21:00:00 2020-05-24 09:00 PM

I think this can easily be scaled up to minutes and seconds, I don't want to spoil your fun:)

Data:

v <- as.POSIXct(sapply(1:48, function(x) 1590174000 + x*60*60),
           origin="1970-01-01")
v <- c(v[1:3], v[3]+60, v[3]+60*59, v[4:length(v)])  ## duplicate some 12 o'clocks
v1 <- format(v, "%Y-%m-%d %I:%M:%S")