Turning non-military time to military time

hmwebber · April 12, 2018, 11:45am

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!

mishabalyasin · April 12, 2018, 12:14pm

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

hmwebber · April 12, 2018, 12:29pm

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

dlsweet · April 12, 2018, 12:30pm

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).

mishabalyasin · April 12, 2018, 12:44pm

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

FAQ: What's a reproducible example (`reprex`) and how do I create one? meta

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

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.

torvaney · April 12, 2018, 1:29pm

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

hmwebber · April 12, 2018, 2:49pm

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

prosoitos · April 17, 2018, 6:50am

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"))

prosoitos · April 17, 2018, 6:59am

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"))

prosoitos · April 17, 2018, 7:05am

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