How to separate column containing dates and times by a space in R

Question

I have two columns, one of start dates and times and one of end dates and times. I want to split those into four columns: Start_date, Start_time, End_date, End_time. They are separated by a space (each column is formatted such as "12/04/2017 05:25 PM"). Ultimately I need to find the difference between the start date and time and the end date and time. I am a beginner at R so I really appropriate your help.

Answer 1

For the purposes of this question I am assuming that you are in the United States and thus that the example date which you provided refers to December 4th, 2017.

The first step is for you to convert the two date columns into dates instead of strings. The pattern of the elements in the datetime object must be echoed in the formatting command. Based on the example you provided, I have created a toy dataframe.

df <- data.frame(Start = c("12/04/2017 05:25 PM","05/05/2017 06:25 PM"), End = c("12/09/2018 05:15 PM","05/05/2019 06:24 PM"))
df
            Start                 End
1 12/04/2017 05:25 PM 12/09/2018 05:15 PM
2 05/05/2017 06:25 PM 05/05/2019 06:24 PM

Now to convert these strings into date objects:

library(lubridate)

df$Start <- strptime(df$Start,format="%m/%d/%Y %I:%M %p")
df$End <- strptime(df$End,format="%m/%d/%Y %I:%M %p")
df
            Start                 End
1 2017-12-04 17:25:00 2018-12-09 17:15:00
2 2017-05-05 18:25:00 2019-05-05 18:24:00

You will note that the spaces you indicated are included in the format pattern, along with symbols which indicate which parts of the date appear where as well as whether solo digits are padded with zeros (as all of yours appear to be). For a reference on which symbols/patterns to use in which situation, I recommend this page: https://www.stat.berkeley.edu/~s133/dates.html

If you wish to determine the difference between the two datetimes, it is now a simple matter of subtracting one from the other.

df$diff <- df$End - df$Start
            Start                 End          diff
1 2017-12-04 17:25:00 2018-12-09 17:15:00 369.9931 days
2 2017-05-05 18:25:00 2019-05-05 18:24:00 729.9993 days

In your question you asked about splitting up into pieces. Just in case that is still something that you need to do, creating the date time is still going to help you out. Now that we have datetime objects instead of strings, we can easily split the column into pieces.

df$Start_Day <- day(df$Start)
df$Start_Month<- month(df$Start)
df$Start_Year <- year(df$Start)

and so on.