Turning non-military time to military time


#1

Hello,
hopefully this is an easy and basic qproblem to be solved... I have a dataframe with date as:
Date Day Time AM/PM
1/1/18 Mon 9:23 AM
1/1/18 Mon 4:02 PM
1/1/18 Mon 10:01 PM

I want to turn Time into military time (0:00 through 23:59)--how do I do that?
Thanks!


#2

Can you put this into reproducible example? It would help to understand what you have and what you are trying to achieve.


#3

Sure and thanks, I have a tide chart and I want to subset it so that I only have dates when low or high tides that happen between the hours of 1100 and 1300. (I then have another dataframe that I want to subset based those dates):
Date Day Time AM/PM Pred.FT Pred,CM High/Low
1/1/18 Mon 9:23 AM 12.44 379 H
1/1/18 Mon 4:02 PM -1.72 -52 L
1/1/18 Mon 10:01 PM 11.15 340 H
1/2/18 Tue 4:16 AM -0.69 -21 L
1/2/18 Tue 10:16 AM 12.74 388 H
1/2/18 Tue 4:56 PM -1.99 -61 L
1/2/18 Tue 10:55 PM 11.37 347 H
1/3/18 Wed 5:10 AM -0.8 -24 L
1/3/18 Wed 11:10 AM 12.82 391 H
1/3/18 Wed 5:49 PM -2.03 -62 L
1/3/18 Wed 11:48 PM 11.43 348 H
1/4/18 Thu 6:05 AM -0.76 -23 L
1/4/18 Thu 12:04 PM 12.63 385 H
1/4/18 Thu 6:42 PM -1.84 -56 L
1/5/18 Fri 12:42 AM 11.34 346 H
1/5/18 Fri 7:00 AM -0.57 -17 L
1/5/18 Fri 12:59 PM 12.22 372 H
1/5/18 Fri 7:36 PM -1.47 -45 L
1/6/18 Sat 1:37 AM 11.13 339 H
1/6/18 Sat 7:57 AM -0.27 -8 L
1/6/18 Sat 1:55 PM 11.64 355 H
1/6/18 Sat 8:31 PM -0.97 -30 L
1/7/18 Sun 2:34 AM 10.84 330 H
1/7/18 Sun 8:56 AM 0.07 2 L
1/7/18 Sun 2:54 PM 10.97 334 H
1/7/18 Sun 9:28 PM -0.41 -12 L
1/8/18 Mon 3:31 AM 10.55 322 H
1/8/18 Mon 9:58 AM 0.39 12 L
1/8/18 Mon 3:55 PM 10.31 314 H
1/8/18 Mon 10:25 PM 0.12 4 L
1/9/18 Tue 4:30 AM 10.3 314 H
1/9/18 Tue 11:00 AM 0.61 19 L
1/9/18 Tue 4:58 PM 9.77 298 H
1/9/18 Tue 11:24 PM 0.57 17 L
1/10/18 Wed 5:29 AM 10.14 309 H
1/10/18 Wed 12:02 PM 0.7 21 L


#4

I'm going to say this with the caveat that 99/100 times I will say to just clean your data and do all analysis inside of R. However, if you can pull your data into Excel, this is a super easy fix, use this code inside of a new cell:

=TEXT(CELL, "[hh]:mm")

Where CELL is whatever cell the time is in (e.g. A1).


#5

Here is a link that explains what reproducible example is and how to make one:

The problem right now is that I can't copy-paste your example to R and work with it there. So, you should build an example of a dataframe (with 5 rows, for example) that I can copy and try different things that might help in your case.

Anyways, I would say that if all you need to do is to find times between 11 and 13 then it is easiest to extract hour out of the column Time (?) and add 12 if it is PM in column AM/PM. You can use dplyr::case_when for that inside of dplyr::mutate.


#6

I think the most robust method would be to convert the dates and times into datetimes (rather than strings) with strptime (or something similar from lubridate). At this point you can then convert to any format you'd like, including 24 hour time.

See example:

library(tidyverse) 

times <- tibble::tribble(
     ~date,  ~day,   ~time, ~am_or_pm,
  "1/1/18", "Mon",  "9:23",      "AM",
  "1/1/18", "Mon",  "4:02",      "PM",
  "1/1/18", "Mon", "10:01",      "PM",
)

# For datetime formats see: https://www.stat.berkeley.edu/~s133/dates.html
times %>%
  mutate(datetime_str = paste(date, time, am_or_pm),
         datetime = as.POSIXct(strptime(datetime_str, format = "%d/%m/%y %I:%M %p")),
         datetime_military = strftime(datetime, format = "%Y-%m-%d %H:%M")) %>%
  select(-datetime_str)  # Trim output...

#> # A tibble: 3 x 6
#>   date   day   time  am_or_pm datetime            datetime_military
#>   <chr>  <chr> <chr> <chr>    <dttm>              <chr>
#> 1 1/1/18 Mon   9:23  AM       2018-01-01 09:23:00 2018-01-01 09:23
#> 2 1/1/18 Mon   4:02  PM       2018-01-01 16:02:00 2018-01-01 16:02
#> 3 1/1/18 Mon   10:01 PM       2018-01-01 22:01:00 2018-01-01 22:01

#7

Thanks, clearly I have a lot to learn!
Have a great day, Hannah


#8

A bit quicker (if you only want to convert your Time variable):

library(tidyverse)

times <- tribble(
  ~Date,  ~Day,   ~Time, ~"AM/PM",
  "1/1/18", "Mon",  "9:23",      "AM",
  "1/1/18", "Mon",  "4:02",      "PM",
  "1/1/18", "Mon", "10:01",      "PM",
  )

times %>%
  mutate(Time = paste(Time, `AM/PM`) %>% parse_time("%I:%M %p"))

#9

This simply replaces your old Time variable by the new one with the format you want. If you also wanted to replace your Date variable by the ISO_8601 format, then you could use:

times %>%
  mutate(Time = paste(Time, `AM/PM`) %>% parse_time("%I:%M %p")) %>%
  mutate(Date = parse_date(Date, "%m/%d/%y"))

#10

Output of the first code:

# A tibble: 3 x 4
  Date   Day   Time   `AM/PM`
  <chr>  <chr> <time> <chr>  
1 1/1/18 Mon   09:23  AM     
2 1/1/18 Mon   16:02  PM     
3 1/1/18 Mon   22:01  PM  

And the second:

# A tibble: 3 x 4
  Date       Day   Time   `AM/PM`
  <date>     <chr> <time> <chr>  
1 2018-01-01 Mon   09:23  AM     
2 2018-01-01 Mon   16:02  PM     
3 2018-01-01 Mon   22:01  PM